[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_tax_info;
c_lines_per_insert CONSTANT NUMBER := 1000;
GT_LAST_UPDATED_BY ZX_EXTRACT_PKG.LAST_UPDATED_BY_TBL;
GT_LAST_UPDATE_DATE ZX_EXTRACT_PKG.LAST_UPDATE_DATE_TBL;
GT_LAST_UPDATE_LOGIN ZX_EXTRACT_PKG.LAST_UPDATE_LOGIN_TBL;
G_LAST_UPDATED_BY NUMBER(15);
G_LAST_UPDATE_DATE DATE;
G_LAST_UPDATE_LOGIN NUMBER(15);
| INSERT_TAX_DATA |
| |
| DESCRIPTION |
| This procedure takes the input parameters from ZX_EXTRACT_PKG |
| and builds a dynamic SQL statement clauses based on the parameters, |
| supplies them as output parameters. |
| |
| SCOPE - Public |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 11-Jan-2005 Srinivasa Rao Korrapati Created |
+===========================================================================*/
PROCEDURE insert_tax_data (
p_trl_global_variables_rec IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
)
IS
BEGIN
g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_EXTRACT_PKG.INSERT_TAX_DATA.BEGIN',
'ZX_AP_ACTG_EXTRACT_PKG: INSERT_TAX_DATA(+)');
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_EXTRACT_PKG.INSERT_TAX_DATA.END',
'ZX_AP_ACTG_EXTRACT_PKG: INSERT_TAX_DATA(-)');
FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','insert_tax_data- '|| g_error_buffer);
'ZX.TRL.ZX_AP_ACTG_EXTRACT_PKG.insert_tax_data',
g_error_buffer);
END insert_tax_data;
L_SELECT_ACCOUNT_SEG VARCHAR2(500);
L_WHERE_REGISTER_TYPE :=' AND EXISTS (SELECT ''Fully Recoverable''' ||
' FROM zx_rec_nrec_dist zx_dist1 WHERE ' ||
' zx_dist1.trx_id = zx_dist.trx_id ' ||
' AND zx_dist1.recoverable_flag = ''Y'' '||
' AND zx_dist1.tax_rate_id = zx_dist.tax_rate_id )';
L_WHERE_REGISTER_TYPE :=' AND EXISTS (SELECT ''Fully Recoverable''' ||
' FROM zx_rec_nrec_dist zx_dist1 WHERE ' ||
' zx_dist1.trx_id = zx_dist.trx_id ' ||
' AND zx_dist1.recoverable_flag = ''N'' '||
' AND zx_dist1.tax_rate_id = zx_dist.tax_rate_id )';
'SELECT
zx_det.application_id,
zx_line.event_class_code ,
zx_det.internal_organization_id,
zx_det.doc_event_status,
zx_det.application_doc_status,
zx_det.line_class,
zx_det.doc_seq_id ,
zx_det.doc_seq_name ,
zx_det.doc_seq_value,
zx_det.establishment_id,
zx_det.batch_source_id,
zx_det.currency_conversion_date,
zx_det.currency_conversion_rate,
zx_det.currency_conversion_type,
zx_det.minimum_accountable_unit,
zx_det.precision,
zx_det.trx_communicated_date ,
zx_det.trx_currency_code,
zx_line.trx_id ,
zx_det.trx_number ,
zx_det.trx_date,
zx_det.trx_description,
zx_det.trx_due_date,
zx_det.trx_line_description,
zx_line.trx_line_id,
zx_line.trx_line_number,
zx_line.trx_line_quantity,
zx_line.line_amt,
zx_det.trx_line_type,
zx_det.trx_shipping_date,
zx_det.uom_code,
zx_det.related_doc_date,
zx_det.related_doc_entity_code,
zx_det.related_doc_event_class_code,
zx_det.related_doc_number,
zx_det.related_doc_trx_id,
zx_det.applied_from_application_id,
zx_line.applied_from_entity_code,
zx_line.applied_from_event_class_code,
zx_det.applied_from_line_id,
zx_line.applied_from_trx_id,
zx_line.applied_from_trx_number,
zx_det.applied_to_application_id,
zx_line.applied_to_entity_code,
zx_line.applied_to_event_class_code,
zx_line.applied_to_trx_id,
zx_det.applied_to_trx_line_id,
zx_det.applied_to_trx_number,
zx_det.adjusted_doc_application_id,
zx_det.adjusted_doc_date,
zx_det.adjusted_doc_entity_code,
zx_det.adjusted_doc_event_class_code,
--zx_det.country_of_supply,
zx_det.default_taxation_country,
TO_CHAR(NULL), --ZX_LINE.MERCHANT_PARTY_DOCUMENT_NUMBER,
TO_CHAR(NULL), --ZX_LINE.MERCHANT_PARTY_NAME,
TO_CHAR(NULL), --ZX_LINE.MERCHANT_PARTY_REFERENCE,
TO_CHAR(NULL), --ZX_LINE.MERCHANT_PARTY_TAX_REG_NUMBER,
TO_CHAR(NULL), --ZX_LINE.MERCHANT_PARTY_TAXPAYER_ID,
zx_det.ref_doc_application_id,
zx_det.ref_doc_entity_code,
zx_det.ref_doc_event_class_code,
zx_det.ref_doc_line_id,
zx_det.ref_doc_line_quantity,
zx_det.ref_doc_trx_id,
zx_det.start_expense_date,
zx_det.assessable_value,
zx_det.document_sub_type,
zx_det.line_intended_use,
zx_det.product_category,
zx_det.product_description,
zx_det.product_fisc_classification,
zx_det.product_id,
zx_det.supplier_exchange_rate,
zx_det.supplier_tax_invoice_date,
zx_det.supplier_tax_invoice_number,
zx_det.tax_invoice_date,
zx_det.tax_invoice_number,
zx_det.trx_business_category,
zx_det.user_defined_fisc_class,
zx_dist.rec_nrec_tax_amt_tax_curr,
zx_line.tax_rate_code,
zx_dist.orig_rec_nrec_tax_amt,
zx_line.orig_tax_amt,
zx_line.orig_tax_amt_tax_curr ,
zx_line.orig_taxable_amt,
zx_line.orig_taxable_amt_tax_curr,
zx_dist.orig_rec_nrec_tax_amt_tax_curr,
TO_CHAR(NULL), --ZX_DIST.RECOVERY_RATE_CODE,
TO_CHAR(NULL), --ZX_DIST.RECOVERY_TYPE_CODE,
zx_line.tax,
zx_line.tax_amt,
zx_line.tax_amt_funcl_curr,
zx_line.tax_amt_tax_curr,
zx_line.tax_apportionment_line_number,
zx_line.tax_currency_code,
zx_line.tax_date,
zx_line.tax_determine_date,
zx_line.tax_jurisdiction_code,
TO_NUMBER(NULL), --ZX_LINE.TAX_LINE_ID ,
TO_NUMBER(NULL), --ZX_LINE.TAX_LINE_NUMBER ,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE1 ,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE10,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE11,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE12,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE13,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE14,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE15,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE2,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE3,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE4,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE5,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE6,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE7,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE8,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE9,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_CATEGORY,
zx_line.tax_rate,
zx_line.tax_rate_code,
zx_line.tax_rate_id,
TO_NUMBER(NULL), --ZX_DIST.REC_NREC_RATE,
zx_line.tax_regime_code,
zx_line.tax_status_code,
zx_line.taxable_amt,
zx_line.taxable_amt_funcl_curr,
-- zx_det.billing_trading_partner_name,
-- zx_det.billing_trading_partner_number,
zx_det.bill_from_party_tax_prof_id,
zx_det.bill_from_site_tax_prof_id,
-- zx_det.billing_tp_taxpayer_id,
zx_det.ship_to_site_tax_prof_id,
zx_det.ship_from_site_tax_prof_id,
zx_det.ship_to_party_tax_prof_id,
zx_det.ship_from_party_tax_prof_id,
zx_line.hq_estb_reg_number,
zx_line.legal_entity_tax_reg_number,
zx_det.own_hq_site_tax_prof_id,
zx_det.own_hq_party_tax_prof_id,
zx_det.port_of_entry_code,
zx_line.registration_party_type,
zx_line.cancel_flag,
zx_line.historical_flag,
zx_line.mrc_tax_line_flag,
zx_line.offset_flag,
zx_line.reporting_only_flag,
zx_dist.self_assessed_flag,
zx_line.tax_amt_included_flag,
zx_line.tax_only_line_flag,
zx_dist.recoverable_flag,
xla_event.event_type_code, -- Accounting Columns
xla_event.event_number,
xla_event.event_status_code,
xla_head.je_category_name,
xla_head.accounting_date,
xla_head.gl_transfer_status_code,
xla_head.description,
to_number(NULL), --xla_line.ae_line_num,
to_char(NULL), --xla_dist.accounting_line_code,
to_char(NULL), --xla_line.description,
to_number(NULL), --xla_line.statistical_amount,
xla_event.process_status_code,
xla_head.gl_transfer_status_code,
xla_head.doc_sequence_id,
xla_head.doc_sequence_value,
to_number(NULL), --xla_line.party_id,
to_number(NULL), --xla_line.party_site_id,
to_char(NULL), --xla_line.party_type_code,
xla_event.event_id,
xla_head.ae_header_id,
to_number(NULL), --xla_line.code_combination_id,
xla_head.period_name,
zx_det.trx_id
FROM zx_lines zx_line,
zx_lines_det_factors zx_det,
zx_rec_nrec_dist zx_dist,
zx_taxes_vl zx_tax,
zx_rates_vl zx_rate,
xla_transaction_entities xla_ent,
xla_events xla_event,
xla_ae_headers xla_head,
xla_ae_lines xla_line,
xla_acct_class_assgns acs,
xla_assignment_defns_b asd
-- xla_distribution_links xla_dist
WHERE zx_det.internal_organization_id = zx_line.internal_organization_id
AND zx_det.application_id = zx_line.application_id
AND zx_det.application_id = 200
AND zx_det.entity_code = zx_line.entity_code
AND zx_det.event_class_code = zx_line.event_class_code
AND zx_det.trx_id = zx_line.trx_id
AND zx_det.trx_line_id = zx_line.trx_line_id
AND zx_det.trx_id = xla_ent.source_id_int_1 -- Accounting Joins
AND zx_det.application_id = xla_ent.application_id
AND xla_ent.entity_code = ''AP_INVOICES''
AND xla_ent.entity_id = xla_event.entity_id
AND xla_event.event_id = xla_head.event_id
AND xla_head.ae_header_id = xla_line.ae_header_id
AND acs.program_code = ''TAX_REP_LEDGER_PROCUREMENT''
AND acs.program_code = asd.program_code
AND asd.assignment_code = acs.assignment_code
AND asd.enabled_flag = ''Y''
AND acs.accounting_class_code = xla_line.accounting_class_code
-- AND xla_dist.event_id = xla_event.event_id -- Accounting Joins End
AND zx_line.tax_line_id = zx_dist.tax_line_id
AND zx_det.tax_reporting_flag = ''Y''
AND zx_line.tax_id = zx_tax.tax_id (+)
AND zx_line.tax_rate_id = NVL(zx_rate.source_id,zx_rate.tax_rate_id)
AND (zx_rate.source_id IS NOT NULL)' --BugFix:4750020
||L_WHERE_TRX_DATE|| ' '
||L_WHERE_REGISTER_TYPE|| ' '
||L_WHERE_GL_DATE|| ' '
||L_WHERE_TRX_NUM|| ' '
||L_WHERE_VAT_TRANSACTION_TYPE|| ' '
||L_WHERE_DOCUMENT_SUB_TYPE|| ' '
||L_WHERE_TRX_BUSINESS_CATEGORY|| ' '
||L_WHERE_TAX_INVOICE_DATE|| ' '
||L_WHERE_TAX_JURISDICTION_CODE|| ' '
||L_WHERE_FIRST_PTY_TAX_REG_NUM|| ' '
||L_WHERE_TAX_REGIME_CODE|| ' '
||L_WHERE_TAX|| ' '
||L_WHERE_TAX_STATUS_CODE|| ' '
||L_WHERE_TAX_RATE_CODE|| ' '
||L_WHERE_TAX_TYPE_CODE|| ' '
||L_WHERE_CURRENCY_CODE|| ' '
||L_WHERE_TRX_CLASS|| ' '
||L_WHERE_LEGAL_ENTITY_ID|| ' '
||L_WHERE_LEDGER_ID|| ' '
||L_WHERE_REPORT_CONTEXT||' '
|| 'GROUP BY
zx_det.application_id,
zx_line.event_class_code ,
zx_det.internal_organization_id,
zx_det.doc_event_status,
zx_det.application_doc_status,
zx_det.line_class,
zx_det.doc_seq_id,
zx_det.doc_seq_name ,
zx_det.doc_seq_value,
zx_det.establishment_id,
zx_det.batch_source_id,
zx_det.currency_conversion_date,
zx_det.currency_conversion_rate,
zx_det.currency_conversion_type,
zx_det.minimum_accountable_unit,
zx_det.precision,
zx_det.trx_communicated_date,
zx_det.trx_currency_code,
zx_line.trx_id,
zx_det.trx_number,
zx_det.trx_date,
zx_det.trx_description,
zx_det.trx_due_date,
zx_det.trx_line_description,
zx_line.trx_line_id,
zx_line.trx_line_number,
zx_line.trx_line_quantity,
zx_line.line_amt,
zx_det.trx_line_type,
zx_det.trx_shipping_date,
zx_det.uom_code,
zx_det.related_doc_date,
zx_det.related_doc_entity_code,
zx_det.related_doc_event_class_code,
zx_det.related_doc_number,
zx_det.related_doc_trx_id,
zx_det.applied_from_application_id,
zx_line.applied_from_entity_code,
zx_line.applied_from_event_class_code,
zx_det.applied_from_line_id,
zx_line.applied_from_trx_id,
zx_line.applied_from_trx_number,
zx_det.applied_to_application_id,
zx_line.applied_to_entity_code,
zx_line.applied_to_event_class_code,
zx_line.applied_to_trx_id,
zx_det.applied_to_trx_line_id,
zx_det.applied_to_trx_number,
zx_det.adjusted_doc_application_id,
zx_det.adjusted_doc_date,
zx_det.adjusted_doc_entity_code,
zx_det.adjusted_doc_event_class_code,
--zx_det.country_of_supply,
zx_det.default_taxation_country,
zx_det.ref_doc_application_id,
zx_det.ref_doc_entity_code,
zx_det.ref_doc_event_class_code,
zx_det.ref_doc_line_id,
zx_det.ref_doc_line_quantity,
zx_det.ref_doc_trx_id,
zx_det.start_expense_date,
zx_det.assessable_value,
zx_det.document_sub_type,
zx_det.line_intended_use,
zx_det.product_category,
zx_det.product_description,
zx_det.product_fisc_classification,
zx_det.product_id,
zx_det.supplier_exchange_rate,
zx_det.supplier_tax_invoice_date,
zx_det.supplier_tax_invoice_number,
zx_det.tax_invoice_date,
zx_det.tax_invoice_number,
zx_det.trx_business_category,
zx_det.user_defined_fisc_class,
zx_dist.rec_nrec_tax_amt_tax_curr,
zx_line.tax_rate_code,
zx_dist.orig_rec_nrec_tax_amt,
zx_line.orig_tax_amt,
zx_line.orig_tax_amt_tax_curr,
zx_line.orig_taxable_amt,
zx_line.orig_taxable_amt_tax_curr,
zx_dist.orig_rec_nrec_tax_amt_tax_curr,
zx_line.tax,
zx_line.tax_amt,
zx_line.tax_amt_funcl_curr,
zx_line.tax_amt_tax_curr,
zx_line.tax_apportionment_line_number,
zx_line.tax_currency_code,
zx_line.tax_date,
zx_line.tax_determine_date,
zx_line.tax_jurisdiction_code,
zx_line.tax_rate,
zx_line.tax_rate_code,
zx_line.tax_rate_id,
to_number(null), --zx_dist.rec_nrec_rate,
zx_line.tax_regime_code,
zx_line.tax_status_code,
zx_line.taxable_amt,
zx_line.taxable_amt_funcl_curr ,
-- zx_det.billing_trading_partner_name,
-- zx_det.billing_trading_partner_number,
zx_det.bill_from_party_tax_prof_id,
zx_det.bill_from_site_tax_prof_id,
-- zx_det.billing_tp_taxpayer_id,
zx_det.ship_to_site_tax_prof_id,
zx_det.ship_from_site_tax_prof_id,
zx_det.ship_to_party_tax_prof_id,
zx_det.ship_from_party_tax_prof_id ,
zx_line.hq_estb_reg_number,
zx_line.legal_entity_tax_reg_number,
zx_det.own_hq_site_tax_prof_id,
zx_det.own_hq_party_tax_prof_id,
zx_det.port_of_entry_code,
zx_line.registration_party_type,
zx_line.cancel_flag,
zx_line.historical_flag,
zx_line.mrc_tax_line_flag,
zx_line.offset_flag,
zx_line.reporting_only_flag,
zx_dist.self_assessed_flag,
zx_line.tax_amt_included_flag,
zx_line.tax_only_line_flag,
zx_dist.recoverable_flag,
xla_event.event_type_code, -- Accounting Columns
xla_event.event_number,
xla_event.event_status_code,
xla_head.je_category_name,
xla_head.accounting_date,
xla_head.gl_transfer_status_code,
xla_head.description,
-- xla_line.ae_line_num,
-- xla_dist.accounting_line_code,
-- xla_line.description,
-- xla_line.statistical_amount,
xla_event.process_status_code,
xla_head.gl_transfer_status_code,
xla_head.doc_sequence_id,
xla_head.doc_sequence_value,
--xla_line.party_id,
-- xla_line.party_site_id,
-- xla_line.party_type_code,
xla_event.event_id,
xla_head.ae_header_id,
-- xla_line.code_combination_id,
xla_head.period_name,
zx_det.trx_id ';
'SELECT
zx_det.application_id,
zx_line.event_class_code ,
zx_det.internal_organization_id,
zx_det.doc_event_status,
zx_det.application_doc_status,
zx_det.line_class,
zx_det.doc_seq_id ,
zx_det.doc_seq_name ,
zx_det.doc_seq_value,
zx_det.establishment_id,
zx_det.batch_source_id,
zx_det.currency_conversion_date,
zx_det.currency_conversion_rate,
zx_det.currency_conversion_type,
zx_det.minimum_accountable_unit,
zx_det.precision,
zx_det.trx_communicated_date ,
zx_det.trx_currency_code,
zx_line.trx_id,
zx_det.trx_number,
zx_det.trx_date,
zx_det.trx_description,
zx_det.trx_due_date,
zx_det.trx_line_description,
zx_line.trx_line_id,
zx_line.trx_line_number,
zx_line.trx_line_quantity,
zx_line.line_amt,
zx_det.trx_line_type,
zx_det.trx_shipping_date,
zx_det.uom_code,
zx_det.related_doc_date,
zx_det.related_doc_entity_code,
zx_det.related_doc_event_class_code,
zx_det.related_doc_number,
zx_det.related_doc_trx_id,
zx_det.applied_from_application_id,
zx_line.applied_from_entity_code,
zx_line.applied_from_event_class_code,
zx_det.applied_from_line_id,
zx_line.applied_from_trx_id,
zx_line.applied_from_trx_number,
zx_det.applied_to_application_id,
zx_line.applied_to_entity_code,
zx_line.applied_to_event_class_code,
zx_line.applied_to_trx_id,
zx_det.applied_to_trx_line_id,
zx_det.applied_to_trx_number,
zx_det.adjusted_doc_application_id,
zx_det.adjusted_doc_date,
zx_det.adjusted_doc_entity_code,
zx_det.adjusted_doc_event_class_code,
--zx_det.country_of_supply,
zx_det.default_taxation_country,
TO_CHAR(NULL), --ZX_LINE.MERCHANT_PARTY_DOCUMENT_NUMBER,
TO_CHAR(NULL), --ZX_LINE.MERCHANT_PARTY_NAME,
TO_CHAR(NULL), --ZX_LINE.MERCHANT_PARTY_REFERENCE,
TO_CHAR(NULL), --ZX_LINE.MERCHANT_PARTY_TAX_REG_NUMBER,
TO_CHAR(NULL), --ZX_LINE.MERCHANT_PARTY_TAXPAYER_ID,
zx_det.ref_doc_application_id,
zx_det.ref_doc_entity_code,
zx_det.ref_doc_event_class_code,
zx_det.ref_doc_line_id,
zx_det.ref_doc_line_quantity,
zx_det.ref_doc_trx_id,
zx_det.start_expense_date,
zx_det.assessable_value,
zx_det.document_sub_type,
zx_det.line_intended_use,
zx_det.product_category,
zx_det.product_description,
zx_det.product_fisc_classification,
zx_det.product_id,
zx_det.supplier_exchange_rate,
zx_det.supplier_tax_invoice_date,
zx_det.supplier_tax_invoice_number,
zx_det.tax_invoice_date,
zx_det.tax_invoice_number,
zx_det.trx_business_category,
zx_det.user_defined_fisc_class,
zx_dist.rec_nrec_tax_amt_tax_curr,
zx_line.tax_rate_code,
zx_dist.orig_rec_nrec_tax_amt,
zx_line.orig_tax_amt,
zx_line.orig_tax_amt_tax_curr ,
zx_line.orig_taxable_amt,
zx_line.orig_taxable_amt_tax_curr,
zx_dist.orig_rec_nrec_tax_amt_tax_curr,
zx_dist.recovery_rate_code,
zx_dist.recovery_type_code,
zx_line.tax,
zx_line.tax_amt,
zx_line.tax_amt_funcl_curr,
zx_line.tax_amt_tax_curr,
zx_line.tax_apportionment_line_number,
zx_line.tax_currency_code,
zx_line.tax_date,
zx_line.tax_determine_date,
zx_line.tax_jurisdiction_code,
zx_line.tax_line_id,
zx_line.tax_line_number ,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE1 ,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE10,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE11,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE12,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE13,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE14,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE15,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE2,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE3,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE4,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE5,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE6,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE7,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE8,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE9,
TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_CATEGORY,
zx_line.tax_rate,
zx_line.tax_rate_code,
zx_line.tax_rate_id,
zx_dist.rec_nrec_rate,
zx_line.tax_regime_code,
zx_line.tax_status_code,
zx_line.taxable_amt,
zx_line.taxable_amt_funcl_curr,
-- zx_det.billing_trading_partner_name,
-- zx_det.billing_trading_partner_number,
zx_det.bill_from_party_tax_prof_id,
zx_det.bill_from_site_tax_prof_id,
-- zx_det.billing_tp_taxpayer_id,
zx_det.ship_to_site_tax_prof_id,
zx_det.ship_from_site_tax_prof_id,
zx_det.ship_to_party_tax_prof_id ,
zx_det.ship_from_party_tax_prof_id ,
zx_line.hq_estb_reg_number,
zx_line.legal_entity_tax_reg_number,
zx_det.own_hq_site_tax_prof_id,
zx_det.own_hq_party_tax_prof_id,
zx_det.port_of_entry_code,
zx_line.registration_party_type,
zx_line.cancel_flag,
zx_line.historical_flag,
zx_line.mrc_tax_line_flag,
zx_line.offset_flag,
zx_line.reporting_only_flag,
zx_dist.self_assessed_flag,
zx_line.tax_amt_included_flag,
zx_line.tax_only_line_flag,
zx_dist.recoverable_flag,
xla_event.event_type_code, -- Accounting Columns
xla_event.event_number,
xla_event.event_status_code,
xla_head.je_category_name,
xla_head.accounting_date,
xla_head.gl_transfer_status_code,
xla_head.description,
xla_line.ae_line_num,
xla_dist.accounting_line_code,
xla_line.description,
xla_line.statistical_amount,
xla_event.process_status_code,
xla_head.gl_transfer_status_code,
xla_head.doc_sequence_id,
xla_head.doc_sequence_value,
xla_line.party_id,
xla_line.party_site_id,
xla_line.party_type_code,
xla_event.event_id,
xla_head.ae_header_id,
xla_line.code_combination_id,
xla_head.period_name,
zx_dist.rec_nrec_tax_dist_id
FROM zx_lines zx_line,
zx_lines_det_factors zx_det,
zx_rec_nrec_dist zx_dist,
zx_taxes_vl zx_tax,
zx_rates_vl zx_rate,
xla_transaction_entities xla_ent,
xla_events xla_event,
xla_ae_headers xla_head,
xla_ae_lines xla_line,
xla_distribution_links xla_dist,
xla_acct_class_assgns acs,
xla_assignment_defns_b asd
WHERE zx_det.internal_organization_id = zx_line.internal_organization_id
AND zx_det.application_id = zx_line.application_id
AND zx_det.application_id = 200
AND zx_det.entity_code = zx_line.entity_code
AND zx_det.event_class_code = zx_line.event_class_code
AND zx_det.trx_id = zx_line.trx_id
-- AND zx_det.application_id = zx_dist.application_id
-- AND zx_det.entity_code = zx_dist.entity_code
-- AND zx_det.event_class_code = zx_dist.event_class_code
-- AND zx_det.event_type_code = zx_dist.event_type_code
-- AND zx_det.trx_id = zx_dist.trx_id
AND zx_line.trx_id = xla_ent.source_id_int_1 -- Accounting Joins
AND zx_det.application_id = xla_ent.application_id
AND xla_ent.entity_code = ''AP_INVOICES''
AND xla_ent.entity_id = xla_event.entity_id
AND xla_event.event_id = xla_head.event_id
AND xla_head.ae_header_id = xla_line.ae_header_id
AND xla_dist.event_id = xla_event.event_id
AND acs.program_code = ''TAX_REP_LEDGER_PROCUREMENT''
AND acs.program_code = asd.program_code
AND asd.assignment_code = acs.assignment_code
AND asd.enabled_flag = ''Y''
AND acs.accounting_class_code = xla_line.accounting_class_code -- Accounting Joins Enda
AND xla_dist.tax_rec_nrec_dist_ref_id = zx_dist.rec_nrec_tax_dist_id
AND xla_dist.source_distribution_type = ''AP_INVOICE_DISTRIBUTIONS''
AND zx_line.tax_line_id = zx_dist.tax_line_id
AND zx_det.tax_reporting_flag = ''Y''
AND zx_line.tax_id = zx_tax.tax_id
AND zx_line.tax_rate_id = nvl(zx_rate.source_id,zx_rate.tax_rate_id)
AND (zx_rate.source_id IS NOT NULL)' --BugFix:4750020
||L_WHERE_TRX_DATE|| ' '
||L_WHERE_REGISTER_TYPE|| ' '
||L_WHERE_GL_DATE|| ' '
||L_WHERE_TRX_NUM|| ' '
||L_WHERE_VAT_TRANSACTION_TYPE|| ' '
||L_WHERE_DOCUMENT_SUB_TYPE|| ' '
||L_WHERE_TRX_BUSINESS_CATEGORY|| ' '
||L_WHERE_TAX_INVOICE_DATE|| ' '
||L_WHERE_TAX_JURISDICTION_CODE|| ' '
||L_WHERE_FIRST_PTY_TAX_REG_NUM|| ' '
||L_WHERE_TAX_REGIME_CODE|| ' '
||L_WHERE_TAX|| ' '
||L_WHERE_TAX_STATUS_CODE|| ' '
||L_WHERE_TAX_RATE_CODE|| ' '
||L_WHERE_TAX_TYPE_CODE|| ' '
||L_WHERE_CURRENCY_CODE|| ' '
||L_WHERE_TRX_CLASS|| ' '
||L_WHERE_LEGAL_ENTITY_ID|| ' '
||L_WHERE_LEDGER_ID|| ' ';
'SELECT
zx_det.application_id,
zx_line.event_class_code,
zx_det.internal_organization_id,
zx_det.doc_event_status,
zx_det.application_doc_status,
zx_det.line_class,
zx_det.doc_seq_id,
zx_det.doc_seq_name,
zx_det.doc_seq_value,
zx_det.establishment_id,
zx_det.batch_source_id,
zx_det.currency_conversion_date,
zx_det.currency_conversion_rate,
zx_det.currency_conversion_type,
zx_det.minimum_accountable_unit,
zx_det.precision,
zx_det.trx_communicated_date,
zx_det.trx_currency_code,
zx_line.trx_id,
zx_det.trx_number,
zx_det.trx_date,
zx_det.trx_description,
zx_det.trx_due_date,
zx_det.trx_line_description,
zx_line.trx_line_id,
zx_line.trx_line_number,
zx_line.trx_line_quantity,
zx_line.line_amt,
zx_det.trx_line_type,
zx_det.trx_shipping_date,
zx_det.uom_code,
zx_det.related_doc_date,
zx_det.related_doc_entity_code,
zx_det.related_doc_event_class_code,
zx_det.related_doc_number,
zx_det.related_doc_trx_id,
zx_det.applied_from_application_id,
zx_line.applied_from_entity_code,
zx_line.applied_from_event_class_code,
zx_det.applied_from_line_id,
zx_line.applied_from_trx_id,
zx_line.applied_from_trx_number,
zx_det.applied_to_application_id,
zx_line.applied_to_entity_code,
zx_line.applied_to_event_class_code,
zx_line.applied_to_trx_id,
zx_det.applied_to_trx_line_id,
zx_det.applied_to_trx_number,
zx_det.adjusted_doc_application_id,
zx_det.adjusted_doc_date,
zx_det.adjusted_doc_entity_code,
zx_det.adjusted_doc_event_class_code,
--zx_det.country_of_supply,
zx_det.default_taxation_country,
zx_det.merchant_party_document_number,
zx_det.merchant_party_name,
zx_det.merchant_party_reference,
zx_det.merchant_party_tax_reg_number,
zx_det.merchant_party_taxpayer_id,
zx_det.ref_doc_application_id,
zx_det.ref_doc_entity_code,
zx_det.ref_doc_event_class_code,
zx_det.ref_doc_line_id,
zx_det.ref_doc_line_quantity,
zx_det.ref_doc_trx_id,
zx_det.start_expense_date,
zx_det.assessable_value,
zx_det.document_sub_type,
zx_det.line_intended_use,
zx_det.product_category,
zx_det.product_description,
zx_det.product_fisc_classification,
zx_det.product_id,
zx_det.supplier_exchange_rate,
zx_det.supplier_tax_invoice_date,
zx_det.supplier_tax_invoice_number,
zx_det.tax_invoice_date,
zx_det.tax_invoice_number,
zx_det.trx_business_category,
zx_det.user_defined_fisc_class,
zx_dist.rec_nrec_tax_amt_tax_curr,
zx_line.tax_rate_code,
zx_dist.orig_rec_nrec_tax_amt,
zx_line.orig_tax_amt,
zx_line.orig_tax_amt_tax_curr ,
zx_line.orig_taxable_amt,
zx_line.orig_taxable_amt_tax_curr,
zx_dist.orig_rec_nrec_tax_amt_tax_curr,
zx_dist.recovery_rate_code,
zx_dist.recovery_type_code,
zx_line.tax,
zx_line.tax_amt,
zx_line.tax_amt_funcl_curr,
zx_line.tax_amt_tax_curr,
zx_line.tax_apportionment_line_number,
zx_line.tax_currency_code,
zx_line.tax_date,
zx_line.tax_determine_date,
zx_line.tax_jurisdiction_code,
zx_line.tax_line_id ,
zx_line.tax_line_number ,
zx_line.attribute1 ,
zx_line.attribute2 ,
zx_line.attribute3 ,
zx_line.attribute4 ,
zx_line.attribute5 ,
zx_line.attribute6 ,
zx_line.attribute7 ,
zx_line.attribute8 ,
zx_line.attribute9 ,
zx_line.attribute10,
zx_line.attribute11,
zx_line.attribute12,
zx_line.attribute13,
zx_line.attribute14,
zx_line.attribute15,
zx_line.attribute_category ,
zx_line.tax_rate,
zx_line.tax_rate_code,
zx_line.tax_rate_id,
zx_dist.rec_nrec_rate,
zx_line.tax_regime_code,
zx_line.tax_status_code,
zx_line.taxable_amt,
zx_line.taxable_amt_funcl_curr ,
-- zx_det.billing_trading_partner_name,
-- zx_det.billing_trading_partner_number,
zx_det.bill_from_party_tax_prof_id,
zx_det.bill_from_site_tax_prof_id,
-- zx_det.billing_tp_taxpayer_id,
zx_det.ship_to_site_tax_prof_id,
zx_det.ship_from_site_tax_prof_id,
zx_det.ship_to_party_tax_prof_id ,
zx_det.ship_from_party_tax_prof_id ,
zx_line.hq_estb_reg_number ,
zx_line.legal_entity_tax_reg_number,
zx_det.own_hq_site_tax_prof_id,
zx_det.own_hq_party_tax_prof_id,
zx_det.port_of_entry_code,
zx_line.registration_party_type,
zx_line.cancel_flag,
zx_line.historical_flag,
zx_line.mrc_tax_line_flag,
zx_line.offset_flag,
zx_line.reporting_only_flag,
zx_dist.self_assessed_flag,
zx_line.tax_amt_included_flag,
zx_line.tax_only_line_flag,
zx_dist.recoverable_flag,
xla_event.event_type_code, -- Accounting Columns
xla_event.event_number,
xla_event.event_status_code,
xla_head.je_category_name,
xla_head.accounting_date,
xla_head.gl_transfer_status_code,
xla_head.description,
xla_line.ae_line_num,
xla_line.accounting_class_code,
xla_line.description,
xla_line.statistical_amount,
xla_event.process_status_code,
xla_head.gl_transfer_status_code,
xla_head.doc_sequence_id,
xla_head.doc_sequence_value,
xla_line.party_id,
xla_line.party_site_id,
xla_line.party_type_code,
xla_event.event_id,
xla_head.ae_header_id,
xla_line.code_combination_id,
xla_head.period_name,
zx_line.trx_line_id
FROM zx_lines zx_line,
zx_lines_det_factors zx_det,
zx_rec_nrec_dist zx_dist,
zx_taxes_vl zx_tax,
zx_rates_vl zx_rate,
xla_transaction_entities xla_ent,
xla_events xla_event,
xla_ae_headers xla_head,
xla_ae_lines xla_line,
xla_acct_class_assgns acs,
xla_assignment_defns_b asd
-- xla_distribution_links xla_dist
WHERE zx_det.internal_organization_id = zx_line.internal_organization_id
AND zx_det.application_id = zx_line.application_id
AND zx_det.entity_code = zx_line.entity_code
AND zx_det.event_class_code = zx_line.event_class_code
AND zx_det.trx_id = zx_line.trx_id
-- AND zx_det.application_id = zx_dist.application_id
-- AND zx_det.entity_code = zx_dist.entity_code
-- AND zx_det.event_class_code = zx_dist.event_class_code
-- AND zx_det.event_type_code = zx_dist.event_type_code
-- AND zx_det.trx_id = zx_dist.trx_id
AND zx_line.trx_id = xla_ent.source_id_int_1 -- Accounting Joins
AND xla_ent.entity_code = ''AP_INVOICES''
AND xla_ent.entity_id = xla_event.entity_id
AND xla_event.event_id = xla_head.event_id
AND xla_head.ae_header_id = xla_line.ae_header_id
AND acs.program_code = ''TAX_REP_LEDGER_PROCUREMENT''
AND acs.program_code = asd.program_code
AND asd.assignment_code = acs.assignment_code
AND asd.enabled_flag = ''Y''
AND acs.accounting_class_code = xla_line.accounting_class_code
-- AND xla_dist.event_id = xla_event.event_id -- Accounting Joins Enda
-- AND xla_dist.source_distribution_id_num_1 =
-- AND xla_dist.source_distribution_type = ''AP_INVOICE_DISTRIBUTIONS''
AND zx_line.tax_line_id = zx_dist.tax_line_id
AND zx_line.tax_id = zx_tax.tax_id
AND zx_line.tax_rate_id = nvl(zx_rate.source_id,zx_rate.tax_rate_id)
AND zx_det.tax_reporting_flag = ''Y''
AND (zx_rate.source_id IS NOT NULL)' --BugFix:4750020
||L_WHERE_TRX_DATE|| ' '
||L_WHERE_REGISTER_TYPE|| ' '
||L_WHERE_GL_DATE|| ' '
||L_WHERE_TRX_NUM|| ' '
||L_WHERE_VAT_TRANSACTION_TYPE|| ' '
||L_WHERE_DOCUMENT_SUB_TYPE|| ' '
||L_WHERE_TRX_BUSINESS_CATEGORY|| ' '
||L_WHERE_TAX_INVOICE_DATE|| ' '
||L_WHERE_TAX_JURISDICTION_CODE|| ' '
||L_WHERE_FIRST_PTY_TAX_REG_NUM|| ' '
||L_WHERE_TAX_REGIME_CODE|| ' '
||L_WHERE_TAX|| ' '
||L_WHERE_TAX_STATUS_CODE|| ' '
||L_WHERE_TAX_RATE_CODE|| ' '
||L_WHERE_TAX_TYPE_CODE|| ' '
||L_WHERE_CURRENCY_CODE|| ' '
||L_WHERE_TRX_CLASS|| ' '
||L_WHERE_LEGAL_ENTITY_ID|| ' '
||L_WHERE_LEDGER_ID|| ' '
||'GROUP BY
zx_det.application_id,
zx_line.event_class_code ,
zx_det.internal_organization_id,
zx_det.doc_event_status,
zx_det.application_doc_status,
zx_det.line_class,
zx_det.doc_seq_id ,
zx_det.doc_seq_name ,
zx_det.doc_seq_value,
zx_det.establishment_id,
zx_det.batch_source_id,
zx_det.currency_conversion_date,
zx_det.currency_conversion_rate,
zx_det.currency_conversion_type,
zx_det.minimum_accountable_unit,
zx_det.precision,
zx_det.trx_communicated_date ,
zx_det.trx_currency_code,
zx_line.trx_id ,
zx_det.trx_number ,
zx_det.trx_date,
zx_det.trx_description,
zx_det.trx_due_date,
zx_det.trx_line_description,
zx_line.trx_line_id,
zx_line.trx_line_number,
zx_line.trx_line_quantity,
zx_line.line_amt,
zx_det.trx_line_type,
zx_det.trx_shipping_date,
zx_det.uom_code,
zx_det.related_doc_date,
zx_det.related_doc_entity_code,
zx_det.related_doc_event_class_code,
zx_det.related_doc_number,
zx_det.related_doc_trx_id,
zx_det.applied_from_application_id,
zx_line.applied_from_entity_code,
zx_line.applied_from_event_class_code,
zx_det.applied_from_line_id,
zx_line.applied_from_trx_id,
zx_line.applied_from_trx_number,
zx_det.applied_to_application_id,
zx_line.applied_to_entity_code,
zx_line.applied_to_event_class_code,
zx_line.applied_to_trx_id,
zx_det.applied_to_trx_line_id,
zx_det.applied_to_trx_number,
zx_det.adjusted_doc_application_id,
zx_det.adjusted_doc_date,
zx_det.adjusted_doc_entity_code,
zx_det.adjusted_doc_event_class_code,
--zx_det.country_of_supply,
zx_det.default_taxation_country,
zx_det.default_taxation_country,
zx_det.merchant_party_document_number,
zx_det.merchant_party_name,
zx_det.merchant_party_reference,
zx_det.merchant_party_tax_reg_number,
zx_det.merchant_party_taxpayer_id,
zx_det.ref_doc_application_id,
zx_det.ref_doc_entity_code,
zx_det.ref_doc_event_class_code,
zx_det.ref_doc_line_id,
zx_det.ref_doc_line_quantity,
zx_det.ref_doc_trx_id,
zx_det.start_expense_date,
zx_det.assessable_value,
zx_det.document_sub_type,
zx_det.line_intended_use,
zx_det.product_category,
zx_det.product_description,
zx_det.product_fisc_classification,
zx_det.product_id,
zx_det.supplier_exchange_rate,
zx_det.supplier_tax_invoice_date,
zx_det.supplier_tax_invoice_number,
zx_det.tax_invoice_date,
zx_det.tax_invoice_number,
zx_det.trx_business_category,
zx_det.user_defined_fisc_class,
zx_dist.rec_nrec_tax_amt_tax_curr,
zx_line.tax_rate_code,
zx_dist.orig_rec_nrec_tax_amt,
zx_line.orig_tax_amt,
zx_line.orig_tax_amt_tax_curr ,
zx_line.orig_taxable_amt,
zx_line.orig_taxable_amt_tax_curr,
zx_dist.orig_rec_nrec_tax_amt_tax_curr,
zx_dist.recovery_rate_code,
zx_dist.recovery_type_code,
zx_line.tax,
zx_line.tax_amt,
zx_line.tax_amt_funcl_curr,
zx_line.tax_amt_tax_curr,
zx_line.tax_apportionment_line_number,
zx_line.tax_currency_code,
zx_line.tax_date,
zx_line.tax_determine_date,
zx_line.tax_jurisdiction_code,
zx_line.tax_line_id,
zx_line.tax_line_number,
zx_line.attribute1 ,
zx_line.attribute2 ,
zx_line.attribute3 ,
zx_line.attribute4 ,
zx_line.attribute5 ,
zx_line.attribute6 ,
zx_line.attribute7 ,
zx_line.attribute8 ,
zx_line.attribute9 ,
zx_line.attribute10,
zx_line.attribute11,
zx_line.attribute12,
zx_line.attribute13,
zx_line.attribute14,
zx_line.attribute15,
zx_line.attribute_category ,
zx_line.tax_rate,
zx_line.tax_rate_code,
zx_line.tax_rate_id,
zx_dist.rec_nrec_rate,
zx_line.tax_regime_code,
zx_line.tax_status_code,
zx_line.taxable_amt,
zx_line.taxable_amt_funcl_curr ,
-- zx_det.billing_trading_partner_name,
-- zx_det.billing_trading_partner_number,
zx_det.bill_from_party_tax_prof_id,
zx_det.bill_from_site_tax_prof_id,
-- zx_det.billing_tp_taxpayer_id,
zx_det.ship_to_site_tax_prof_id,
zx_det.ship_from_site_tax_prof_id,
zx_det.ship_to_party_tax_prof_id,
zx_det.ship_from_party_tax_prof_id,
zx_line.hq_estb_reg_number,
zx_line.legal_entity_tax_reg_number,
zx_det.own_hq_site_tax_prof_id,
zx_det.own_hq_party_tax_prof_id,
zx_det.port_of_entry_code,
zx_line.registration_party_type,
zx_line.cancel_flag,
zx_line.historical_flag,
zx_line.mrc_tax_line_flag,
zx_line.offset_flag,
zx_line.reporting_only_flag,
zx_dist.self_assessed_flag,
zx_line.tax_amt_included_flag,
zx_line.tax_only_line_flag,
zx_dist.recoverable_flag,
xla_event.event_type_code, -- Accounting Columns
xla_event.event_number,
xla_event.event_status_code,
xla_head.je_category_name,
xla_head.accounting_date,
xla_head.gl_transfer_status_code,
xla_head.description,
xla_line.ae_line_num,
xla_line.accounting_class_code,
xla_line.description,
xla_line.statistical_amount,
xla_event.process_status_code,
xla_head.gl_transfer_status_code,
xla_head.doc_sequence_id,
xla_head.doc_sequence_value,
xla_line.party_id,
xla_line.party_site_id,
xla_line.party_type_code,
xla_event.event_id,
xla_head.ae_header_id,
xla_line.code_combination_id,
xla_head.period_name ';
| Called from INSERT_TAX_DATA |
| |
| SCOPE - Private |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 11-Jan-2005 Srinivasa Rao Korrapati Created |
+===========================================================================*/
PROCEDURE execute_sql_stmt IS
l_sql1 VARCHAR2(3500);
g_last_updated_by := fnd_global.user_id;
g_last_update_login := fnd_global.login_id;
g_last_update_date := sysdate;
'Before insert Call :' ||to_char(GT_TRX_ID.count));
IF (i >= c_lines_per_insert) THEN
insert_tax_info;
'After insert_tax_info Call :' );
'Before INSERT_TAX_INFO 2 Call :' );
insert_tax_info;
'After INSERT_TAX_INFO 2 :' );
gt_extract_rep_line_num.delete;
gt_application_id.delete;
gt_event_class_code.delete;
gt_internal_organization_id.delete;
gt_doc_event_status.delete;
gt_application_doc_status.delete;
gt_line_class.delete;
gt_doc_seq_id.delete;
gt_doc_seq_name.delete;
gt_doc_seq_value.delete;
gt_establishment_id.delete;
gt_batch_source_id.delete;
gt_currency_conversion_date.delete;
gt_currency_conversion_rate.delete;
gt_currency_conversion_type.delete;
gt_minimum_accountable_unit.delete;
gt_precision.delete;
gt_trx_communicated_date.delete;
gt_trx_currency_code.delete;
gt_trx_id.delete;
gt_trx_number.delete;
gt_trx_date.delete;
gt_trx_description.delete;
gt_trx_due_date.delete;
gt_trx_line_description.delete;
gt_trx_line_id.delete;
gt_trx_line_number.delete;
gt_trx_line_quantity.delete;
gt_trx_line_amt.delete;
gt_trx_line_type.delete;
gt_trx_shipping_date.delete;
gt_uom_code.delete;
gt_related_doc_date.delete;
gt_related_doc_entity_code.delete;
gt_related_doc_event_cls_code.delete;
gt_related_doc_number.delete;
gt_related_doc_trx_id.delete;
gt_applied_from_appl_id.delete;
gt_applied_from_entity_code.delete;
gt_applied_from_event_cls_code.delete;
gt_applied_from_line_id.delete;
gt_applied_from_trx_id.delete;
gt_applied_from_trx_number.delete;
gt_applied_to_appl_id.delete;
gt_applied_to_entity_code.delete;
gt_applied_to_event_cls_code.delete;
gt_applied_to_trx_id.delete;
gt_applied_to_trx_line_id.delete;
gt_applied_to_trx_number.delete;
gt_adjusted_doc_appl_id.delete;
gt_adjusted_doc_date.delete;
gt_adjusted_doc_entity_code.delete;
gt_adjusted_doc_event_cls_code.delete;
gt_default_taxation_country.delete;
gt_merchant_party_doc_num.delete;
gt_merchant_party_name.delete;
gt_merchant_party_reference.delete;
gt_merchant_party_tax_reg_num.delete;
gt_merchant_party_taxpayer_id.delete;
gt_ref_doc_application_id.delete;
gt_ref_doc_entity_code.delete;
gt_ref_doc_event_cls_code.delete;
gt_ref_doc_line_id.delete;
gt_ref_doc_line_quantity.delete;
gt_ref_doc_trx_id.delete;
gt_start_expense_date.delete;
gt_assessable_value.delete;
gt_document_sub_type.delete;
gt_line_intended_use.delete;
gt_product_category.delete;
gt_product_description.delete;
gt_prod_fisc_classification.delete;
gt_product_id.delete;
gt_supplier_exchange_rate.delete;
gt_supplier_tax_invoice_date.delete;
gt_supplier_tax_invoice_num.delete;
gt_tax_invoice_date.delete;
gt_tax_invoice_number.delete;
gt_trx_business_category.delete;
gt_user_defined_fisc_class.delete;
gt_nrec_tax_amt_tax_curr.delete;
gt_offset_tax_rate_code.delete;
gt_orig_rec_nrec_tax_amt.delete;
gt_orig_tax_amt.delete;
gt_orig_tax_amt_tax_curr.delete;
gt_orig_taxable_amt.delete;
gt_orig_taxable_amt_tax_curr.delete;
gt_rec_tax_amt_tax_curr.delete;
gt_recovery_rate_code.delete;
gt_recovery_type_code.delete;
gt_tax.delete;
gt_tax_amt.delete;
gt_tax_amt_funcl_curr.delete;
gt_tax_amt_tax_curr.delete;
gt_tax_apportionment_line_num.delete;
gt_tax_currency_code.delete;
gt_tax_date.delete;
gt_tax_determine_date.delete;
gt_tax_jurisdiction_code.delete;
gt_tax_line_id.delete;
gt_tax_line_number.delete;
gt_tax_line_user_attribute1.delete;
gt_tax_line_user_attribute2.delete;
gt_tax_line_user_attribute3.delete;
gt_tax_line_user_attribute4.delete;
gt_tax_line_user_attribute5.delete;
gt_tax_line_user_attribute6.delete;
gt_tax_line_user_attribute7.delete;
gt_tax_line_user_attribute8.delete;
gt_tax_line_user_attribute9.delete;
gt_tax_line_user_attribute10.delete;
gt_tax_line_user_attribute11.delete;
gt_tax_line_user_attribute12.delete;
gt_tax_line_user_attribute13.delete;
gt_tax_line_user_attribute14.delete;
gt_tax_line_user_attribute15.delete;
gt_tax_line_user_category.delete;
gt_tax_rate.delete;
gt_tax_rate_code.delete;
gt_tax_rate_id.delete;
gt_tax_recovery_rate.delete;
gt_tax_regime_code.delete;
gt_tax_status_code.delete;
gt_taxable_amt.delete;
gt_taxable_amt_funcl_curr.delete;
gt_bill_from_pty_tax_prof_id.delete;
gt_bill_from_site_tax_prof_id.delete;
gt_ship_to_site_tax_prof_id.delete;
gt_ship_from_site_tax_prof_id.delete;
gt_ship_to_pty_tax_prof_id.delete;
gt_ship_from_pty_tax_prof_id.delete;
gt_hq_estb_reg_number.delete;
gt_legal_entity_tax_reg_num.delete;
gt_own_hq_pty_site_prof_id.delete;
gt_own_hq_pty_tax_prof_id.delete;
gt_port_of_entry_code.delete;
gt_registration_party_type.delete;
gt_cancel_flag.delete;
gt_historical_flag.delete;
gt_mrc_tax_line_flag.delete;
gt_offset_flag.delete;
gt_reporting_only_flag.delete;
gt_self_assessed_flag.delete;
gt_tax_amt_included_flag.delete;
gt_tax_only_flag.delete;
gt_tax_recoverable_flag.delete;
gt_actg_ext_line_id.delete;
gt_detail_tax_line_id.delete;
gt_actg_event_type_code.delete;
gt_actg_event_number.delete;
gt_actg_event_status_flag.delete;
gt_actg_category_code.delete;
gt_accounting_date.delete;
gt_gl_transfer_flag.delete;
gt_gl_transfer_run_id.delete;
gt_actg_header_description.delete;
gt_actg_line_num.delete;
gt_actg_line_type_code.delete;
gt_actg_line_description.delete;
gt_actg_stat_amt.delete;
gt_actg_error_code.delete;
gt_gl_transfer_code.delete;
gt_actg_doc_sequence_id.delete;
gt_actg_doc_sequence_name.delete;
gt_actg_doc_sequence_value.delete;
gt_actg_party_id.delete;
gt_actg_party_site_id.delete;
gt_actg_party_type.delete;
gt_actg_event_id.delete;
gt_actg_header_id.delete;
gt_actg_source_id.delete;
gt_actg_source_table.delete;
gt_actg_line_ccid.delete;
gt_period_name.delete;
/* gt_created_by.delete;
gt_creation_date.delete;
gt_last_updated_by.delete;
gt_last_update_date.delete;
gt_last_update_login.delete;
gt_program_application_id.delete;
gt_program_id.delete;
gt_program_login_id.delete;
| insert_tax_info |
| DESCRIPTION |
| This procedure inserts payables tax data into ZX_REP_TRX_DETAIL_T table|
| |
| SCOPE - Private |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 11-Jan-2005 Srinivasa Rao Korrapati Created |
| |
+===========================================================================*/
PROCEDURE insert_tax_info
IS
l_count NUMBER;
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_EXTRACT_PKG.insert_tax_info.BEGIN',
'ZX_AP_ACTG_EXTRACT_PKG: insert_tax_info(+)');
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_EXTRACT_PKG.insert_tax_info',
' Record Count = ' ||to_char(GT_TRX_ID.COUNT));
INSERT INTO ZX_REP_TRX_DETAIL_T(
DETAIL_TAX_LINE_ID,
APPLICATION_ID,
EXTRACT_REPORT_LINE_NUMBER,
EVENT_CLASS_CODE,
INTERNAL_ORGANIZATION_ID,
DOC_EVENT_STATUS,
APPLICATION_DOC_STATUS,
TRX_LINE_CLASS,
DOC_SEQ_ID,
DOC_SEQ_NAME,
DOC_SEQ_VALUE,
ESTABLISHMENT_ID,
TRX_BATCH_SOURCE_ID,
CURRENCY_CONVERSION_DATE,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_TYPE,
MINIMUM_ACCOUNTABLE_UNIT,
PRECISION,
TRX_COMMUNICATED_DATE ,
TRX_CURRENCY_CODE,
TRX_ID,
TRX_NUMBER,
TRX_DATE,
TRX_DESCRIPTION,
TRX_DUE_DATE,
TRX_LINE_DESCRIPTION,
TRX_LINE_ID,
TRX_LINE_NUMBER,
TRX_LINE_QUANTITY,
TRX_LINE_AMT,
TRX_LINE_TYPE,
TRX_SHIPPING_DATE,
UOM_CODE,
RELATED_DOC_DATE,
RELATED_DOC_ENTITY_CODE,
RELATED_DOC_EVENT_CLASS_CODE,
RELATED_DOC_NUMBER,
RELATED_DOC_TRX_ID,
APPLIED_FROM_APPLICATION_ID,
APPLIED_FROM_ENTITY_CODE,
APPLIED_FROM_EVENT_CLASS_CODE,
APPLIED_FROM_LINE_ID,
APPLIED_FROM_TRX_ID,
APPLIED_FROM_TRX_NUMBER,
APPLIED_TO_APPLICATION_ID,
APPLIED_TO_ENTITY_CODE,
APPLIED_TO_EVENT_CLASS_CODE,
APPLIED_TO_TRX_ID,
APPLIED_TO_TRX_LINE_ID,
APPLIED_TO_TRX_NUMBER ,
ADJUSTED_DOC_APPLICATION_ID,
ADJUSTED_DOC_DATE,
ADJUSTED_DOC_ENTITY_CODE, --ok
ADJUSTED_DOC_EVENT_CLASS_CODE,
-- COUNTRY_OF_SUPPLY,
DEFAULT_TAXATION_COUNTRY,
MERCHANT_PARTY_DOCUMENT_NUMBER,
MERCHANT_PARTY_NAME,
MERCHANT_PARTY_REFERENCE,
MERCHANT_PARTY_TAX_REG_NUMBER,
MERCHANT_PARTY_TAXPAYER_ID,
REF_DOC_APPLICATION_ID,
REF_DOC_ENTITY_CODE,
REF_DOC_EVENT_CLASS_CODE,
REF_DOC_LINE_ID,
REF_DOC_LINE_QUANTITY,
REF_DOC_TRX_ID, --ok
START_EXPENSE_DATE,
ASSESSABLE_VALUE,
DOCUMENT_SUB_TYPE,
LINE_INTENDED_USE,
PRODUCT_CATEGORY,
PRODUCT_DESCRIPTION,
PRODUCT_FISC_CLASSIFICATION,
PRODUCT_ID,
SUPPLIER_EXCHANGE_RATE,
SUPPLIER_TAX_INVOICE_DATE,
SUPPLIER_TAX_INVOICE_NUMBER,
TAX_INVOICE_DATE,
TAX_INVOICE_NUMBER,
TRX_BUSINESS_CATEGORY,
USER_DEFINED_FISC_CLASS,
NREC_TAX_AMT_TAX_CURR,
OFFSET_TAX_RATE_CODE,
ORIG_REC_NREC_TAX_AMT,
ORIG_TAX_AMT,
ORIG_TAX_AMT_TAX_CURR ,
ORIG_TAXABLE_AMT,
ORIG_TAXABLE_AMT_TAX_CURR,
REC_TAX_AMT_TAX_CURR,
RECOVERY_RATE_CODE,
RECOVERY_TYPE_CODE, --ok
TAX,
TAX_AMT,
TAX_AMT_FUNCL_CURR,
TAX_AMT_TAX_CURR ,
TAX_APPORTIONMENT_LINE_NUMBER,
TAX_CURRENCY_CODE,
TAX_DATE,
TAX_DETERMINE_DATE,
TAX_JURISDICTION_CODE,
TAX_LINE_ID,
TAX_LINE_NUMBER,
TAX_LINE_USER_ATTRIBUTE1,
TAX_LINE_USER_ATTRIBUTE2,
TAX_LINE_USER_ATTRIBUTE3,
TAX_LINE_USER_ATTRIBUTE4,
TAX_LINE_USER_ATTRIBUTE5,
TAX_LINE_USER_ATTRIBUTE6,
TAX_LINE_USER_ATTRIBUTE7,
TAX_LINE_USER_ATTRIBUTE8 ,
TAX_LINE_USER_ATTRIBUTE9 ,
TAX_LINE_USER_ATTRIBUTE10 ,
TAX_LINE_USER_ATTRIBUTE11 ,
TAX_LINE_USER_ATTRIBUTE12 ,
TAX_LINE_USER_ATTRIBUTE13 ,
TAX_LINE_USER_ATTRIBUTE14 ,
TAX_LINE_USER_ATTRIBUTE15 ,
TAX_LINE_USER_CATEGORY , --ok
TAX_RATE,
TAX_RATE_CODE,
TAX_RATE_ID ,
TAX_RECOVERY_RATE,
TAX_REGIME_CODE,
TAX_STATUS_CODE,
TAXABLE_AMT,
TAXABLE_AMT_FUNCL_CURR ,
-- BILLING_TP_NAME,
-- BILLING_TP_NUMBER,
BILL_FROM_PARTY_TAX_PROF_ID,
BILL_FROM_SITE_TAX_PROF_ID,
--BILLING_TP_TAXPAYER_ID,
SHIP_TO_SITE_TAX_PROF_ID ,
SHIP_FROM_SITE_TAX_PROF_ID,
SHIP_TO_PARTY_TAX_PROF_ID ,
SHIP_FROM_PARTY_TAX_PROF_ID ,
HQ_ESTB_REG_NUMBER ,
LEGAL_ENTITY_TAX_REG_NUMBER,
OWN_HQ_PARTY_SITE_PROF_ID,
OWN_HQ_PARTY_TAX_PROF_ID,
PORT_OF_ENTRY_CODE,
REGISTRATION_PARTY_TYPE,
CANCEL_FLAG,
HISTORICAL_FLAG,
MRC_TAX_LINE_FLAG,
OFFSET_FLAG,
REPORTING_ONLY_FLAG,
SELF_ASSESSED_FLAG,
TAX_AMT_INCLUDED_FLAG,
TAX_ONLY_FLAG,
TAX_RECOVERABLE_FLAG,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
REQUEST_ID,
EXTRACT_SOURCE_LEDGER)
VALUES(
ZX_REP_TRX_DETAIL_T_S.NEXTVAL,
gt_application_id(i),
gt_extract_rep_line_num(i),
gt_event_class_code(i),
gt_internal_organization_id(i),
gt_doc_event_status(i),
gt_application_doc_status(i),
gt_line_class(i),
gt_doc_seq_id(i),
gt_doc_seq_name(i),
gt_doc_seq_value(i),
gt_establishment_id(i),
gt_batch_source_id(i),
gt_currency_conversion_date(i),
gt_currency_conversion_rate(i),
gt_currency_conversion_type(i),
gt_minimum_accountable_unit(i),
gt_precision(i),
gt_trx_communicated_date(i),
gt_trx_currency_code(i),
gt_trx_id(i),
gt_trx_number(i),
gt_trx_date(i),
gt_trx_description(i),
gt_trx_due_date(i),
gt_trx_line_description(i),
gt_trx_line_id(i),
gt_trx_line_number(i),
gt_trx_line_quantity(i),
gt_trx_line_amt(i),
gt_trx_line_type(i),
gt_trx_shipping_date(i),
gt_uom_code(i),
gt_related_doc_date(i),
gt_related_doc_entity_code(i),
gt_related_doc_event_cls_code(i),
gt_related_doc_number(i),
gt_related_doc_trx_id(i),
gt_applied_from_appl_id(i),
gt_applied_from_entity_code(i),
gt_applied_from_event_cls_code(i),
gt_applied_from_line_id(i),
gt_applied_from_trx_id(i),
gt_applied_from_trx_number(i),
gt_applied_to_appl_id(i),
gt_applied_to_entity_code(i),
gt_applied_to_event_cls_code(i),
gt_applied_to_trx_id(i),
gt_applied_to_trx_line_id(i),
gt_applied_to_trx_number(i),
gt_adjusted_doc_appl_id(i),
gt_adjusted_doc_date(i),
gt_adjusted_doc_entity_code(i),
gt_adjusted_doc_event_cls_code(i),
-- gt_country_of_supply(i),
gt_default_taxation_country(i),
gt_merchant_party_doc_num(i),
gt_merchant_party_name(i),
gt_merchant_party_reference(i),
gt_merchant_party_tax_reg_num(i),
gt_merchant_party_taxpayer_id(i),
gt_ref_doc_application_id(i),
gt_ref_doc_entity_code(i),
gt_ref_doc_event_cls_code(i),
gt_ref_doc_line_id(i),
gt_ref_doc_line_quantity(i),
gt_ref_doc_trx_id(i),
gt_start_expense_date(i),
gt_assessable_value(i),
gt_document_sub_type(i),
gt_line_intended_use(i),
gt_product_category(i),
gt_product_description(i),
gt_prod_fisc_classification(i),
gt_product_id(i),
gt_supplier_exchange_rate(i),
gt_supplier_tax_invoice_date(i),
gt_supplier_tax_invoice_num(i),
gt_tax_invoice_date(i),
gt_tax_invoice_number(i),
gt_trx_business_category(i),
gt_user_defined_fisc_class(i),
gt_nrec_tax_amt_tax_curr(i),
gt_offset_tax_rate_code(i),
gt_orig_rec_nrec_tax_amt(i),
gt_orig_tax_amt(i),
gt_orig_tax_amt_tax_curr(i),
gt_orig_taxable_amt(i),
gt_orig_taxable_amt_tax_curr(i),
gt_rec_tax_amt_tax_curr(i),
gt_recovery_rate_code(i),
gt_recovery_type_code(i),
gt_tax(i),
gt_tax_amt(i),
gt_tax_amt_funcl_curr(i),
gt_tax_amt_tax_curr(i),
gt_tax_apportionment_line_num(i),
gt_tax_currency_code(i),
gt_tax_date(i),
gt_tax_determine_date(i),
gt_tax_jurisdiction_code(i),
gt_tax_line_id(i),
gt_tax_line_number(i),
gt_tax_line_user_attribute1(i),
gt_tax_line_user_attribute2(i),
gt_tax_line_user_attribute3(i),
gt_tax_line_user_attribute4(i),
gt_tax_line_user_attribute5(i),
gt_tax_line_user_attribute6(i),
gt_tax_line_user_attribute7(i),
gt_tax_line_user_attribute8(i),
gt_tax_line_user_attribute9(i),
gt_tax_line_user_attribute10(i),
gt_tax_line_user_attribute11(i),
gt_tax_line_user_attribute12(i),
gt_tax_line_user_attribute13(i),
gt_tax_line_user_attribute14(i),
gt_tax_line_user_attribute15(i),
gt_tax_line_user_category(i),
gt_tax_rate(i),
gt_tax_rate_code(i),
gt_tax_rate_id(i),
gt_tax_recovery_rate(i),
gt_tax_regime_code(i),
gt_tax_status_code(i),
gt_taxable_amt(i),
gt_taxable_amt_funcl_curr(i),
-- gt_billing_tp_name(i),
-- gt_billing_tp_number(i),
gt_bill_from_pty_tax_prof_id(i),
gt_bill_from_site_tax_prof_id(i),
-- gt_billing_tp_taxpayer_id(i),
gt_ship_to_site_tax_prof_id(i) ,
gt_ship_from_site_tax_prof_id(i),
gt_ship_to_pty_tax_prof_id(i),
gt_ship_from_pty_tax_prof_id(i),
gt_hq_estb_reg_number(i),
gt_legal_entity_tax_reg_num(i),
gt_own_hq_pty_site_prof_id(i),
gt_own_hq_pty_tax_prof_id(i),
gt_port_of_entry_code(i),
gt_registration_party_type(i),
gt_cancel_flag(i),
gt_historical_flag(i),
gt_mrc_tax_line_flag(i),
gt_offset_flag(i),
gt_reporting_only_flag(i),
gt_self_assessed_flag(i),
gt_tax_amt_included_flag(i),
gt_tax_only_flag(i),
gt_tax_recoverable_flag(i),
g_created_by ,
g_creation_date ,
g_last_updated_by,
g_last_update_date,
g_last_update_login,
g_request_id,
'AP')
RETURNING detail_tax_line_id bulk collect into GT_DETAIL_TAX_LINE_ID ;
INSERT INTO ZX_REP_ACTG_EXT_T(
actg_ext_line_id,
detail_tax_line_id,
actg_event_type_code,
actg_event_number,
actg_event_status_flag,
actg_category_code,
accounting_date,
gl_transfer_flag,
-- gl_transfer_run_id,
actg_header_description,
actg_line_num,
actg_line_type_code,
actg_line_description,
actg_stat_amt,
actg_error_code,
gl_transfer_code,
actg_doc_sequence_id,
--actg_doc_sequence_name,
actg_doc_sequence_value,
actg_party_id,
actg_party_site_id,
actg_party_type,
actg_event_id,
actg_header_id,
actg_source_id,
--actg_source_table,
actg_line_ccid,
period_name,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_application_id,
program_id,
program_login_id)
VALUES (zx_rep_actg_ext_t_s.nextval,
gt_detail_tax_line_id(i),
gt_actg_event_type_code(i),
gt_actg_event_number(i),
gt_actg_event_status_flag(i),
gt_actg_category_code(i),
gt_accounting_date(i),
gt_gl_transfer_flag(i),
-- gt_gl_transfer_run_id(i),
gt_actg_header_description(i),
gt_actg_line_num(i),
gt_actg_line_type_code(i),
gt_actg_line_description(i),
gt_actg_stat_amt(i),
gt_actg_error_code(i),
gt_gl_transfer_code(i),
gt_actg_doc_sequence_id(i),
-- gt_actg_doc_sequence_name(i),
gt_actg_doc_sequence_value(i),
gt_actg_party_id(i),
gt_actg_party_site_id(i),
gt_actg_party_type(i),
gt_actg_event_id(i),
gt_actg_header_id(i),
gt_actg_source_id(i),
-- gt_actg_source_table(i),
gt_actg_line_ccid(i),
gt_period_name(i),
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login,
g_program_application_id,
g_program_id,
g_program_login_id);
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_EXTRACT_PKG.insert_tax_info',
'Number of Tax Lines successfully inserted = '||TO_CHAR(l_count));
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_EXTRACT_PKG.INSERT_TAX_INFO.END',
'ZX_AP_ACTG_EXTRACT_PKG: INIT_GT_VARIABLES(-)');
'ZX.TRL.ZX_AP_ACTG_EXTRACT_PKG.INSERT_TAX_INFO',
g_error_buffer);
END insert_tax_info;