[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
C_LINES_PER_INSERT CONSTANT NUMBER :=1000;
SELECT application_short_name, application_id
FROM FND_APPLICATION
WHERE application_id = l_appl_id;
LAST_UPDATED_BY AR_SYSTEM_PARAMETERS_ALL.LAST_UPDATED_BY%TYPE,
LAST_UPDATE_DATE AR_SYSTEM_PARAMETERS_ALL.LAST_UPDATE_DATE%type,
LAST_UPDATE_LOGIN AR_SYSTEM_PARAMETERS_ALL. LAST_UPDATE_LOGIN%type,
ACCOUNTING_METHOD AR_SYSTEM_PARAMETERS_ALL.ACCOUNTING_METHOD%TYPE,
ACCRUE_INTEREST AR_SYSTEM_PARAMETERS_ALL.ACCRUE_INTEREST%TYPE,
UNEARNED_DISCOUNT AR_SYSTEM_PARAMETERS_ALL.UNEARNED_DISCOUNT%TYPE,
PARTIAL_DISCOUNT_FLAG AR_SYSTEM_PARAMETERS_ALL.PARTIAL_DISCOUNT_FLAG%type,
PRINT_REMIT_TO AR_SYSTEM_PARAMETERS_ALL. PRINT_REMIT_TO%type,
DEFAULT_CB_DUE_DATE AR_SYSTEM_PARAMETERS_ALL. DEFAULT_CB_DUE_DATE%type,
AUTO_SITE_NUMBERING AR_SYSTEM_PARAMETERS_ALL.AUTO_SITE_NUMBERING%type,
CASH_BASIS_SET_OF_BOOKS_ID AR_SYSTEM_PARAMETERS_ALL.CASH_BASIS_SET_OF_BOOKS_ID%type,
CODE_COMBINATION_ID_GAIN AR_SYSTEM_PARAMETERS_ALL.CODE_COMBINATION_ID_GAIN%type,
AUTOCASH_HIERARCHY_ID AR_SYSTEM_PARAMETERS_ALL. AUTOCASH_HIERARCHY_ID%type,
RUN_GL_JOURNAL_IMPORT_FLAG AR_SYSTEM_PARAMETERS_ALL.RUN_GL_JOURNAL_IMPORT_FLAG%type,
CER_SPLIT_AMOUNT AR_SYSTEM_PARAMETERS_ALL. CER_SPLIT_AMOUNT%type,
CER_DSO_DAYS AR_SYSTEM_PARAMETERS_ALL.CER_DSO_DAYS%type,
POSTING_DAYS_PER_CYCLE AR_SYSTEM_PARAMETERS_ALL.POSTING_DAYS_PER_CYCLE%type,
ADDRESS_VALIDATION AR_SYSTEM_PARAMETERS_ALL. ADDRESS_VALIDATION%type,
ATTRIBUTE1 AR_SYSTEM_PARAMETERS_ALL.ATTRIBUTE1%type,
ATTRIBUTE2 AR_SYSTEM_PARAMETERS_ALL.ATTRIBUTE2%type,
ATTRIBUTE_CATEGORY AR_SYSTEM_PARAMETERS_ALL.ATTRIBUTE_CATEGORY%type,
ATTRIBUTE3 AR_SYSTEM_PARAMETERS_ALL.ATTRIBUTE3%type,
ATTRIBUTE4 AR_SYSTEM_PARAMETERS_ALL.ATTRIBUTE4%type,
ATTRIBUTE5 AR_SYSTEM_PARAMETERS_ALL.ATTRIBUTE5%type,
ATTRIBUTE6 AR_SYSTEM_PARAMETERS_ALL.ATTRIBUTE6%type,
ATTRIBUTE7 AR_SYSTEM_PARAMETERS_ALL.ATTRIBUTE7%type,
ATTRIBUTE8 AR_SYSTEM_PARAMETERS_ALL.ATTRIBUTE8%type,
TAX_CODE AR_SYSTEM_PARAMETERS_ALL.TAX_CODE%type,
TAX_CURRENCY_CODE AR_SYSTEM_PARAMETERS_ALL.TAX_CURRENCY_CODE%type,
TAX_HEADER_LEVEL_FLAG AR_SYSTEM_PARAMETERS_ALL.TAX_HEADER_LEVEL_FLAG%type,
TAX_MINIMUM_ACCOUNTABLE_UNIT AR_SYSTEM_PARAMETERS_ALL.TAX_MINIMUM_ACCOUNTABLE_UNIT%type,
TAX_PRECISION AR_SYSTEM_PARAMETERS_ALL.TAX_PRECISION%type,
TAX_ROUNDING_RULE AR_SYSTEM_PARAMETERS_ALL.TAX_ROUNDING_RULE%type,
GLOBAL_ATTRIBUTE1 AR_SYSTEM_PARAMETERS_ALL.GLOBAL_ATTRIBUTE1%type,
GLOBAL_ATTRIBUTE2 AR_SYSTEM_PARAMETERS_ALL.GLOBAL_ATTRIBUTE2%type,
GLOBAL_ATTRIBUTE3 AR_SYSTEM_PARAMETERS_ALL.GLOBAL_ATTRIBUTE3%type,
GLOBAL_ATTRIBUTE4 AR_SYSTEM_PARAMETERS_ALL.GLOBAL_ATTRIBUTE4%type,
GLOBAL_ATTRIBUTE5 AR_SYSTEM_PARAMETERS_ALL.GLOBAL_ATTRIBUTE5%type,
GLOBAL_ATTRIBUTE6 AR_SYSTEM_PARAMETERS_ALL.GLOBAL_ATTRIBUTE6%type,
GLOBAL_ATTRIBUTE7 AR_SYSTEM_PARAMETERS_ALL.GLOBAL_ATTRIBUTE7%type,
GLOBAL_ATTRIBUTE8 AR_SYSTEM_PARAMETERS_ALL.GLOBAL_ATTRIBUTE8%type,
GLOBAL_ATTRIBUTE9 AR_SYSTEM_PARAMETERS_ALL.GLOBAL_ATTRIBUTE9%type,
GLOBAL_ATTRIBUTE10 AR_SYSTEM_PARAMETERS_ALL.GLOBAL_ATTRIBUTE10%type,
GLOBAL_ATTRIBUTE11 AR_SYSTEM_PARAMETERS_ALL.GLOBAL_ATTRIBUTE11%type,
GLOBAL_ATTRIBUTE12 AR_SYSTEM_PARAMETERS_ALL.GLOBAL_ATTRIBUTE12%type,
GLOBAL_ATTRIBUTE13 AR_SYSTEM_PARAMETERS_ALL.GLOBAL_ATTRIBUTE13%type,
GLOBAL_ATTRIBUTE14 AR_SYSTEM_PARAMETERS_ALL.GLOBAL_ATTRIBUTE14%type,
GLOBAL_ATTRIBUTE15 AR_SYSTEM_PARAMETERS_ALL. GLOBAL_ATTRIBUTE15%type,
GLOBAL_ATTRIBUTE16 AR_SYSTEM_PARAMETERS_ALL.GLOBAL_ATTRIBUTE16%type,
GLOBAL_ATTRIBUTE17 AR_SYSTEM_PARAMETERS_ALL.GLOBAL_ATTRIBUTE17%type,
GLOBAL_ATTRIBUTE18 AR_SYSTEM_PARAMETERS_ALL.GLOBAL_ATTRIBUTE18%type,
GLOBAL_ATTRIBUTE19 AR_SYSTEM_PARAMETERS_ALL.GLOBAL_ATTRIBUTE19%type,
GLOBAL_ATTRIBUTE20 AR_SYSTEM_PARAMETERS_ALL.GLOBAL_ATTRIBUTE20%type,
GLOBAL_ATTRIBUTE_CATEGORY AR_SYSTEM_PARAMETERS_ALL.GLOBAL_ATTRIBUTE_CATEGORY%TYPE,
TAX_ROUNDING_ALLOW_OVERRIDE AR_SYSTEM_PARAMETERS_ALL.TAX_ROUNDING_ALLOW_OVERRIDE%TYPE,
RULE_SET_ID AR_SYSTEM_PARAMETERS_ALL.RULE_SET_ID%type,
TAX_USE_ACCOUNT_EXC_RATE_FLAG AR_SYSTEM_PARAMETERS_ALL.TAX_USE_ACCOUNT_EXC_RATE_FLAG%type,
TAX_USE_SYSTEM_EXC_RATE_FLAG AR_SYSTEM_PARAMETERS_ALL.TAX_USE_SYSTEM_EXC_RATE_FLAG%type,
TAX_HIER_SITE_EXC_RATE AR_SYSTEM_PARAMETERS_ALL.TAX_HIER_SITE_EXC_RATE%type,
TAX_HIER_CUST_EXC_RATE AR_SYSTEM_PARAMETERS_ALL. TAX_HIER_CUST_EXC_RATE%type,
TAX_HIER_PROD_EXC_RATE AR_SYSTEM_PARAMETERS_ALL.TAX_HIER_PROD_EXC_RATE%type,
TAX_HIER_ACCOUNT_EXC_RATE AR_SYSTEM_PARAMETERS_ALL.TAX_HIER_ACCOUNT_EXC_RATE%type,
TAX_HIER_SYSTEM_EXC_RATE AR_SYSTEM_PARAMETERS_ALL. TAX_HIER_SYSTEM_EXC_RATE%type,
TAX_DATABASE_VIEW_SET AR_SYSTEM_PARAMETERS_ALL. TAX_DATABASE_VIEW_SET%type,
INCLUSIVE_TAX_USED AR_SYSTEM_PARAMETERS_ALL. INCLUSIVE_TAX_USED%type,
CODE_COMBINATION_ID_ROUND AR_SYSTEM_PARAMETERS_ALL. CODE_COMBINATION_ID_ROUNd%type,
TRX_HEADER_LEVEL_ROUNDING AR_SYSTEM_PARAMETERS_ALL.TRX_HEADER_LEVEL_ROUNDING%type,
TRX_HEADER_ROUND_CCID AR_SYSTEM_PARAMETERS_ALL.TRX_HEADER_ROUND_CCID%type,
FINCHRG_RECEIVABLES_TRX_ID AR_SYSTEM_PARAMETERS_ALL.FINCHRG_RECEIVABLES_TRX_ID%type,
SALES_TAX_GEOCODE AR_SYSTEM_PARAMETERS_ALL.SALES_TAX_GEOCODE%type,
BILLS_RECEIVABLE_ENABLED_FLAG AR_SYSTEM_PARAMETERS_ALL.BILLS_RECEIVABLE_ENABLED_FLAG%type,
TA_INSTALLED_FLAG AR_SYSTEM_PARAMETERS_ALL.TA_INSTALLED_FLAG%type,
REV_TRANSFER_CLEAR_CCID AR_SYSTEM_PARAMETERS_ALL.REV_TRANSFER_CLEAR_CCID%type,
SALES_CREDIT_PCT_LIMIT AR_SYSTEM_PARAMETERS_ALL.SALES_CREDIT_PCT_LIMIT%TYPE);
SELECT org_id,
def_option_hier_1_code,
def_option_hier_2_code,
def_option_hier_3_code,
def_option_hier_4_code,
def_option_hier_5_code,
def_option_hier_6_code,
def_option_hier_7_code,
home_country_default_flag,
tax_classification_code,
tax_method_code,
inclusive_tax_used_flag,
tax_use_customer_exempt_flag,
tax_use_product_exempt_flag,
tax_use_loc_exc_rate_flag,
tax_allow_compound_flag,
tax_rounding_rule,
tax_precision,
tax_minimum_accountable_unit,
use_tax_classification_flag,
allow_tax_rounding_ovrd_flag
FROM zx_product_options_all
WHERE org_id = c_org_id
AND application_id = c_application_id;
trx_type_tbl.DELETE;
SELECT
DEFAULT_GROUPING_RULE_ID ,
SALESREP_REQUIRED_FLAG ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
AUTO_REC_INVOICES_PER_COMMIT ,
AUTO_REC_RECEIPTS_PER_COMMIT ,
PAY_UNRELATED_INVOICES_FLAG, -- TAX_CACHE
PRINT_HOME_COUNTRY_FLAG ,
LOCATION_TAX_ACCOUNT ,
FROM_POSTAL_CODE ,
TO_POSTAL_CODE ,
TAX_REGISTRATION_NUMBER ,
POPULATE_GL_SEGMENTS_FLAG ,
UNALLOCATED_REVENUE_CCID ,
ORG_ID ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
CALC_DISCOUNT_ON_LINES_FLAG ,
CHANGE_PRINTED_INVOICE_FLAG ,
CODE_COMBINATION_ID_LOSS ,
CREATE_RECIPROCAL_FLAG ,
DEFAULT_COUNTRY ,
DEFAULT_TERRITORY ,
GENERATE_CUSTOMER_NUMBER ,
INVOICE_DELETION_FLAG ,
LOCATION_STRUCTURE_ID ,
SITE_REQUIRED_FLAG ,
TAX_ALLOW_COMPOUND_FLAG ,
TAX_INVOICE_PRINT ,
TAX_METHOD ,
TAX_USE_CUSTOMER_EXEMPT_FLAG ,
TAX_USE_CUST_EXC_RATE_FLAG ,
TAX_USE_LOC_EXC_RATE_FLAG ,
TAX_USE_PRODUCT_EXEMPT_FLAG ,
TAX_USE_PROD_EXC_RATE_FLAG ,
TAX_USE_SITE_EXC_RATE_FLAG ,
AI_LOG_FILE_MESSAGE_LEVEL ,
AI_MAX_MEMORY_IN_BYTES ,
AI_ACCT_FLEX_KEY_LEFT_PROMPT ,
AI_MTL_ITEMS_KEY_LEFT_PROMPT ,
AI_TERRITORY_KEY_LEFT_PROMPT ,
AI_PURGE_INTERFACE_TABLES_FLAG ,
AI_ACTIVATE_SQL_TRACE_FLAG ,
SET_OF_BOOKS_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN ,
ACCOUNTING_METHOD ,
ACCRUE_INTEREST ,
UNEARNED_DISCOUNT ,
PARTIAL_DISCOUNT_FLAG ,
PRINT_REMIT_TO ,
DEFAULT_CB_DUE_DATE ,
AUTO_SITE_NUMBERING ,
CASH_BASIS_SET_OF_BOOKS_ID ,
CODE_COMBINATION_ID_GAIN ,
AUTOCASH_HIERARCHY_ID ,
RUN_GL_JOURNAL_IMPORT_FLAG ,
CER_SPLIT_AMOUNT ,
CER_DSO_DAYS ,
POSTING_DAYS_PER_CYCLE ,
ADDRESS_VALIDATION ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
TAX_CODE ,
TAX_CURRENCY_CODE ,
TAX_HEADER_LEVEL_FLAG ,
TAX_MINIMUM_ACCOUNTABLE_UNIT ,
TAX_PRECISION ,
TAX_ROUNDING_RULE ,
GLOBAL_ATTRIBUTE1 ,
GLOBAL_ATTRIBUTE2 ,
GLOBAL_ATTRIBUTE3 ,
GLOBAL_ATTRIBUTE4 ,
GLOBAL_ATTRIBUTE5 ,
GLOBAL_ATTRIBUTE6 ,
GLOBAL_ATTRIBUTE7 ,
GLOBAL_ATTRIBUTE8 ,
GLOBAL_ATTRIBUTE9 ,
GLOBAL_ATTRIBUTE10 ,
GLOBAL_ATTRIBUTE11 ,
GLOBAL_ATTRIBUTE12 ,
GLOBAL_ATTRIBUTE13 ,
GLOBAL_ATTRIBUTE14 ,
GLOBAL_ATTRIBUTE15 ,
GLOBAL_ATTRIBUTE16 ,
GLOBAL_ATTRIBUTE17 ,
GLOBAL_ATTRIBUTE18 ,
GLOBAL_ATTRIBUTE19 ,
GLOBAL_ATTRIBUTE20 ,
GLOBAL_ATTRIBUTE_CATEGORY ,
TAX_ROUNDING_ALLOW_OVERRIDE ,
RULE_SET_ID ,
TAX_USE_ACCOUNT_EXC_RATE_FLAG ,
TAX_USE_SYSTEM_EXC_RATE_FLAG ,
TAX_HIER_SITE_EXC_RATE ,
TAX_HIER_CUST_EXC_RATE ,
TAX_HIER_PROD_EXC_RATE ,
TAX_HIER_ACCOUNT_EXC_RATE ,
TAX_HIER_SYSTEM_EXC_RATE ,
TAX_DATABASE_VIEW_SET ,
INCLUSIVE_TAX_USED ,
CODE_COMBINATION_ID_ROUND ,
TRX_HEADER_LEVEL_ROUNDING ,
TRX_HEADER_ROUND_CCID ,
FINCHRG_RECEIVABLES_TRX_ID ,
SALES_TAX_GEOCODE ,
BILLS_RECEIVABLE_ENABLED_FLAG ,
TA_INSTALLED_FLAG ,
REV_TRANSFER_CLEAR_CCID ,
SALES_CREDIT_PCT_LIMIT
--MAX_WRTOFF_AMOUNT ,
--IREC_CC_RECEIPT_METHOD_ID ,
--SHOW_BILLING_NUMBER_FLAG ,
--CROSS_CURRENCY_RATE_TYPE ,
--DOCUMENT_SEQ_GEN_LEVEL ,
--CALC_TAX_ON_CREDIT_MEMO_FLAG ,
--IREC_BA_RECEIPT_METHOD_ID
into l_ar_sys_param_rec from ar_system_parameters_all
where org_id = p_event_class_rec.internal_organization_id;
SELECT sob.chart_of_accounts_id,
sob.currency_code,
c.precision,
c.minimum_accountable_unit
INTO l_chart_of_accounts_id,
l_functional_currency,
l_base_precision,
l_base_min_acc_unit
FROM gl_sets_of_books sob, fnd_currencies c
WHERE sob.set_of_books_id = sysinfo.sysparam.set_of_books_id
AND sob.currency_code = c.currency_code;
select sob.set_of_books_id
into l_sob_test
from gl_sets_of_books sob
where sob.set_of_books_id = sysinfo.sysparam.set_of_books_id;
sysinfo.insert_tax_lines := 'Y';
IF ((ZX_GLOBAL_STRUCTURES_PKG.g_update_event_process_flag = 'Y') OR
(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_level_action(l_index) = 'UPDATE') OR
(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.tax_event_type_code(l_index) ='UPDATE'
AND
(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_level_action(l_index) = 'LINE_INFO_TAX_ONLY'
OR ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_level_action(l_index) = 'CREATE_WITH_TAX')))
THEN
fetch_manual_tax_lines (
p_event_class_rec => p_event_class_rec,
p_index => l_index,
x_return_status => x_return_status);
SELECT INV_TYPE.TAX_CALCULATION_FLAG
INTO l_tax_calculation_flag
FROM RA_CUST_TRX_TYPES_ALL INV_TYPE
WHERE INV_TYPE.CUST_TRX_TYPE_ID = zx_global_structures_pkg.trx_line_dist_tbl.receivables_trx_type_id(l_index)
AND ORG_ID = zx_global_structures_pkg.trx_line_dist_tbl.internal_organization_id(l_index);
tax_rec_tbl.delete;
tax_info_out_rec_tbl.delete;
SELECT INV_TYPE.TAX_CALCULATION_FLAG
INTO l_tax_calculation_flag
FROM RA_CUST_TRX_TYPES_ALL INV_TYPE
WHERE INV_TYPE.CUST_TRX_TYPE_ID = zx_global_structures_pkg.trx_line_dist_tbl.receivables_trx_type_id(l_index)
AND ORG_ID = zx_global_structures_pkg.trx_line_dist_tbl.internal_organization_id(l_index);
tax_rec_tbl.delete;
tax_info_out_rec_tbl.delete;
SELECT /*+ ORDERED
INDEX(headergt ZX_TRX_HEADERS_GT_U1)
INDEX(taxgt ZX_IMPORT_TAX_LINES_GT_U1)
INDEX(linegt ZX_TRANSACTION_LINES_GT_U1)*/
taxgt.summary_tax_line_number,
taxgt.internal_organization_id,
taxgt.tax_regime_code,
taxgt.tax,
taxgt.tax_status_code,
taxgt.tax_rate_code,
taxgt.tax_rate,
taxgt.tax_amt,
taxgt.tax_jurisdiction_code,
taxgt.tax_amt_included_flag,
taxgt.tax_rate_id,
taxgt.tax_provider_id,
taxgt.tax_exception_id,
taxgt.tax_exemption_id,
taxgt.exempt_reason_code,
taxgt.exempt_certificate_number,
linegt.trx_line_id,
linegt.line_amt,
headergt.trx_date,
headergt.minimum_accountable_unit,
headergt.precision,
linegt.trx_level_type,
linegt.trx_line_date,
linegt.adjusted_doc_date,
linegt.line_level_action,
taxgt.interface_entity_code,
taxgt.interface_tax_line_id,
headergt.related_doc_date,
headergt.provnl_tax_determination_date,
linegt.tax_date ,
linegt.tax_determine_date,
linegt.tax_point_date
FROM zx_trx_headers_gt headergt,
zx_import_tax_lines_gt taxgt,
zx_transaction_lines_gt linegt
WHERE headergt.application_id = p_event_class_rec.application_id
AND headergt.event_class_code = p_event_class_rec.event_class_code
AND headergt.entity_code = p_event_class_rec.entity_code
AND headergt.trx_id = p_event_class_rec.trx_id
AND taxgt.application_id = headergt.application_id
AND taxgt.entity_code = headergt.entity_code
AND taxgt.event_class_code = headergt.event_class_code
AND taxgt.trx_id = headergt.trx_id
AND taxgt.tax_line_allocation_flag = 'N'
AND linegt.application_id = taxgt.application_id
AND linegt.entity_code = taxgt.entity_code
AND linegt.event_class_code = taxgt.event_class_code
AND linegt.trx_id = taxgt.trx_id
AND linegt.trx_line_id = taxgt.trx_line_id
AND linegt.trx_line_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_id(p_id_dist_tbl)
ORDER BY taxgt.summary_tax_line_number;
IF ( ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl.LAST >= C_LINES_PER_INSERT) THEN
ZX_TDS_CALC_SERVICES_PUB_PKG.dump_detail_tax_lines_into_gt (x_return_status);
select
trx_business_category,
product_fisc_classification,
product_category
from
zx_lines_det_factors
where application_id = c_application_id
and entity_code = c_entity_code
and event_class_code = c_event_class_code
and trx_id = c_trx_id
and trx_line_id = c_trx_line_id;
select
tax_line_id,
trx_date,
tax_regime_code,
tax,
tax_status_code,
tax_rate_code,
tax_rate_id,
tax_rate,
tax_exemption_id,
tax_exception_id,
tax_currency_conversion_date,
tax_currency_conversion_rate,
tax_currency_conversion_type
from ZX_LINES
where application_id = c_application_id
and entity_code = c_entity_code
and event_class_code = c_event_class_code
and trx_level_type = c_trx_level_type
and trx_id = c_trx_id
and trx_line_id = c_trx_line_id
and NVL(manually_entered_flag,'N') = 'N' -- Added the manually_entered_flag = N, cancel_flag <> Y
and NVL(cancel_flag,'N') <> 'Y' -- and mrc_tax_line_flag = N to fetch only those tax lines
and NVL(mrc_tax_line_flag,'N') = 'N'; -- from adjusted doc that are neither manual nor canceled
select TXC.tax_category_id
from JL_ZZ_AR_TX_GROUPS_ALL TGR ,
JL_ZZ_AR_TX_CATEG TXC
where TGR.group_tax_id = c_group_tax_id
and TGR.tax_category_id = TXC.tax_category_id
and TXC.tax_category = c_tax
and TGR.org_id = c_org_id
and TGR.start_date_active <= c_trx_date
and TGR.end_date_active >= c_trx_date;
select type
from ra_cust_trx_types_all
where cust_trx_type_id = c_trx_type_id
and org_id = sysinfo.sysparam.org_id;
tax_rec_tbl.delete;
tax_info_out_rec_tbl.delete;
sysinfo.insert_tax_lines := 'N';
IF ( ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl.LAST >= C_LINES_PER_INSERT) THEN
ZX_TDS_CALC_SERVICES_PUB_PKG.dump_detail_tax_lines_into_gt (x_return_status);
IF (nvl(ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl.LAST,0) >= C_LINES_PER_INSERT) THEN
ZX_TDS_CALC_SERVICES_PUB_PKG.dump_detail_tax_lines_into_gt (x_return_status);
select type
from ra_cust_trx_types_all
where cust_trx_type_id = c_trx_type_id
and org_id = sysinfo.sysparam.org_id;
select tax_calculation_flag
from ra_cust_trx_types_all ct
where ct.cust_trx_type_id = c_cust_trx_type_id
and org_id = sysinfo.sysparam.org_id;
select cm.extended_amount, cm.quantity_credited
into cm_line_amount, cm_line_qty
from ra_customer_trx_lines_all cm
where cm.customer_trx_line_id = tax_info_rec.customer_trx_line_id;
select line.extended_amount,
tax.extended_amount,
line.quantity_invoiced
into orig_line_amount,
orig_tax_amount,
orig_line_qty
from ra_customer_trx_lines_all line,
ra_customer_trx_lines_all tax
where tax.customer_trx_line_id = prev_ctlid
and tax.link_to_cust_trx_line_id = line.customer_trx_line_id;
select decode(nvl(prev_line.extended_amount,0), 0, 0,
(line.extended_amount * nvl(prev_tax.taxable_amount,1))/ prev_line.extended_amount)
from ra_customer_trx_lines_all line,
ra_customer_trx_lines_all prev_line,
ra_customer_trx_lines_all prev_tax
where prev_tax.customer_trx_line_id = prev_ctlid and
nvl(prev_tax.tax_vendor_return_code,tax_no_vendor) =
tax_no_vendor
and prev_tax.link_to_cust_trx_line_id = prev_line.customer_trx_line_id
and line.customer_trx_line_id = tax_info_rec.customer_trx_line_id
and line.previous_customer_trx_line_id = prev_line.customer_trx_line_id;
select net_amount into tax_balance
from ar_net_revenue_amount
where customer_trx_line_id = prev_ctlid
and customer_trx_id = prev_ctid;
select inv_trx.trx_date
into l_inv_trx_date
from ra_customer_trx_all inv_trx,
ra_cust_trx_types_all trx_type,
ra_customer_trx_all trx
where trx.cust_trx_type_id = trx_type.cust_trx_type_id
and trx_type.type = 'CM'
and trx_type.org_id = trx.org_id
and trx.previous_customer_trx_id = inv_trx.customer_trx_id
and trx.customer_trx_id = tax_info_rec.customer_trx_id;
select inv_trx.trx_date
into l_inv_trx_date
from ra_customer_trx_all inv_trx,
ra_cust_trx_types_all trx_type,
ra_customer_trx_all trx,
ra_customer_trx_lines_all line
where trx.cust_trx_type_id = trx_type.cust_trx_type_id
and trx_type.type = 'CM'
and trx_type.org_id = trx.org_id
and trx.previous_customer_trx_id = inv_trx.customer_trx_id
and trx.customer_trx_id = line.customer_trx_id
and line.customer_trx_line_id = tax_info_rec.customer_trx_line_id;
tax_rec_tbl.delete;
tax_info_rec_tbl.delete;
SELECT TAXABLE_BASIS,
TAX_CALCULATION_PLSQL_BLOCK,
TAX_TYPE,
decode(tax_type,'TAX_GROUP',vat_tax_id,null),
decode(tax_type,'TAX_GROUP',NULL,
decode (length(translate(global_attribute1,
'0123456789 ', '0123456789')),
length(translate(global_attribute1, '0123456789
ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_-,:.',
'0123456789')), global_attribute1, -99))
INTO tax_info_rec.taxable_basis, --l_taxable_basis,
tax_info_rec.tax_calculation_plsql_block, --l_tax_calculation_plsql_block,
tax_info_rec.userf7, --l_tax_type,
tax_info_rec.usern2, --l_vat_tax_id,
tax_info_rec.usern1 --l_tax_category_id
FROM ar_vat_tax_all_b
WHERE set_of_books_id = sysinfo.sysparam.SET_OF_BOOKS_ID
AND tax_code = tax_info_rec.tax_code
AND tax_info_rec.trx_date BETWEEN start_date
AND NVL(end_date, TO_DATE( '31122199', 'DDMMYYYY'))
AND NVL(enabled_flag,'Y') = 'Y'
AND NVL(tax_class,'O') = 'O'
AND ORG_ID = sysinfo.sysparam.org_id;
-- normally happen during a delete of a tax line, since deleted
-- tax records must be reversded from any audit.
dump_tax_info_rec('I');
-- arp_tax.insert_error(nvl(tax_info_rec.customer_trx_line_id, 0));
| tax_info_rec updated with tax rate, amount, vendor and other info. |
| exceptions |
| app_exception.raise_exception when an exception is found along with |
| the error message. |
| |
| CALLED FROM |
| process_tax_rec_f_sql_lte |
| |
| HISTORY |
| 28-DEC-2004 Nilesh Patel Created. |
| |
*----------------------------------------------------------------------------*/
PROCEDURE arp_tax_calculate IS
l_tax_vendor_return_code ra_customer_trx_lines.tax_vendor_return_code%TYPE;
SELECT vat_tax_id,
decode(tax_type,
'LOCATION', TAX_TYPE_LOCATION,
'SALES_TAX', TAX_TYPE_SALES,
TAX_TYPE_VAT ) tax_type
FROM ar_vat_tax_all_b
WHERE set_of_books_id = c_sob_id
AND tax_code = c_tax_code
AND c_trx_date between start_date and nvl(end_date, c_trx_end_date)
AND nvl(enabled_flag,'Y') = 'Y'
AND nvl(TAX_CLASS,'O') = 'O'
AND org_id = c_org_id;
SELECT invalid_tax_rate_rule
FROM ra_batch_sources_all bsrc, ra_customer_trx_all trx
WHERE trx.batch_source_id = bsrc.batch_source_id
AND trx.org_id = bsrc.org_id
AND trx.customer_trx_id = c_trx_id;
SELECT HRL.LOCATION_ID,
HRL.GLOBAL_ATTRIBUTE1
FROM HR_LOCATIONS_ALL HRL ,
HR_ORGANIZATION_UNITS ORG
WHERE ORG.LOCATION_ID = HRL.LOCATION_ID
AND ORG.ORGANIZATION_ID = c_org_id;
SELECT INV_TYPE.CUST_TRX_TYPE_ID,
INV_TYPE.TAX_CALCULATION_FLAG
INTO l_cust_trx_type_id,
l_tax_calculation_flag
FROM RA_CUST_TRX_TYPES_ALL INV_TYPE
WHERE INV_TYPE.CUST_TRX_TYPE_ID = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.receivables_trx_type_id(p_index)
AND ORG_ID = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.internal_organization_id(p_index);
SELECT
output_tax_classification_code,
trx_business_category,
product_fisc_classification,
product_category,
user_defined_fisc_class,
default_taxation_country,
document_sub_type,
line_intended_use,
assessable_value,
product_type
INTO
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.OUTPUT_TAX_CLASSIFICATION_CODE(p_index),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_BUSINESS_CATEGORY(p_index),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_FISC_CLASSIFICATION(p_index),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_CATEGORY(p_index),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.USER_DEFINED_FISC_CLASS(p_index),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.DEFAULT_TAXATION_COUNTRY(p_index),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.DOCUMENT_SUB_TYPE(p_index),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_INTENDED_USE(p_index),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ASSESSABLE_VALUE(p_index),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_TYPE(p_index)
FROM ZX_LINES_DET_FACTORS
WHERE application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_application_id(p_index)
AND entity_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_entity_code(p_index)
AND event_class_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_event_class_code(p_index)
AND trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_id(p_index)
AND trx_line_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_line_id(p_index)
AND trx_level_type = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_level_type(p_index);
SELECT HRL.LOCATION_ID,
NVL(HRL.GLOBAL_ATTRIBUTE1, 'DEFAULT')
INTO l_location_id,
l_org_class
FROM HR_LOCATIONS_ALL HRL ,
HR_ORGANIZATION_UNITS ORG
WHERE ORG.LOCATION_ID = HRL.LOCATION_ID
--++ nipatel verify this join condition
--++ Condition changed for Bug#7438620
AND ORG.ORGANIZATION_ID = NVL(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.product_org_id(p_index),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.internal_organization_id(p_index));
SELECT TAXABLE_BASIS,
TAX_CALCULATION_PLSQL_BLOCK,
TAX_TYPE,
decode(tax_type,'TAX_GROUP',vat_tax_id,null),
decode(tax_type,'TAX_GROUP',NULL,
decode (length(translate(global_attribute1,
'0123456789 ', '0123456789')),
length(translate(global_attribute1, '0123456789
ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_-,:.',
'0123456789')), global_attribute1, -99))
INTO l_taxable_basis,
l_tax_calculation_plsql_block,
l_tax_type,
l_vat_tax_id,
l_tax_category_id
FROM ar_vat_tax_all_b
WHERE set_of_books_id = l_set_of_books_id
AND tax_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.OUTPUT_TAX_CLASSIFICATION_CODE(p_index)
AND tax_info_rec.trx_date BETWEEN start_date
AND NVL(end_date, TO_DATE( '31122199', 'DDMMYYYY'))
AND NVL(enabled_flag,'Y') = 'Y'
AND NVL(tax_class,'O') = 'O'
AND ORG_ID = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.internal_organization_id(p_index) ;
| Bulk insert of the tax_info_rec output lines into detail_tax_lines_gt. |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| CALLED FROM |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
+===========================================================================*/
PROCEDURE prepare_detail_tax_line(
p_event_class_rec IN zx_api_pub.event_class_rec_type,
p_id_dist_tbl IN NUMBER,
p_new_row_num IN NUMBER,
p_tax_out_rec IN tax_info_rec_TYPE
) IS
l_user_id NUMBER;
SELECT zx_lines_s.NEXTVAL
INTO ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(p_new_row_num).tax_line_id
FROM dual;
ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(p_new_row_num).LAST_UPDATED_BY := l_user_id ;
ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(p_new_row_num).LAST_UPDATE_DATE := l_date ; -- update_date
ZX_TDS_CALC_SERVICES_PUB_PKG.g_detail_tax_lines_tbl(p_new_row_num).DELETE_FLAG := NVL(p_tax_out_rec.DELETE_FLAG ,'N');
SELECT tax_line_id FROM zx_lines
WHERE application_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_application_id(p_id_dist_tbl)
AND entity_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_entity_code(p_id_dist_tbl)
AND event_class_code =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_event_class_code(p_id_dist_tbl)
AND trx_id =
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_id( p_id_dist_tbl)
AND trx_line_id =
NVL(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_line_id(p_id_dist_tbl), trx_line_id)
AND trx_level_type =
NVL(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_level_type(p_id_dist_tbl), trx_level_type)
AND (tax_provider_id IS NULL
OR ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_level_action(p_id_dist_tbl) = 'ALLOCATE_TAX_ONLY_ADJUSTMENT')
AND Cancel_Flag <> 'Y'
AND offset_link_to_tax_line_id IS NULL
AND mrc_tax_line_flag = 'N'
AND tax = c_tax
AND tax_regime_code = c_tax_regime_code
AND tax_apportionment_line_number = c_apportionment_line_number;
SELECT tax_rate_id
FROM ZX_SCO_RATES
WHERE tax_regime_code = p_tax_regime_code
AND tax = p_tax
AND tax_status_code = p_tax_status_code
AND tax_rate_code = p_tax_rate_code
AND active_flag = 'Y'
AND ( p_tax_determine_date >= effective_from AND
(p_tax_determine_date <= effective_to OR effective_to IS NULL));
SELECT vat_tax_id, tax_rate,
TAX_TYPE_VAT,
nvl(validate_flag, 'N')
FROM ar_vat_tax
WHERE tax_code = tax_info_rec.tax_code
AND set_of_books_id = sysinfo.sysparam.set_of_books_id
AND trunc(tax_info_rec.trx_date) between start_date and
nvl(end_date, trunc(tax_info_rec.trx_date))
AND nvl(enabled_flag, 'Y') = 'Y'
AND nvl(tax_class, 'O') = 'O';
SELECT 1
FROM DUAL
WHERE EXISTS ( SELECT tax_code
FROM ar_vat_tax
WHERE tax_code = tax_info_rec.tax_code
AND set_of_books_id = sysinfo.sysparam.set_of_books_id
AND nvl(enabled_flag, 'Y') = 'Y'
AND nvl(tax_class, 'O') = 'O');
SELECT delimiter
FROM zx_fc_types_b
WHERE classification_type_code ='TRX_BUSINESS_CATEGORY';
USING (SELECT
ratrxlines.global_attribute_category,
ratrxlines.global_attribute1,
ratrxlines.global_attribute2 product_category,
ratrxlines.global_attribute3 trx_business_category,
Event.tax_event_class_code tax_event_class_code,
Lines.trx_line_id trx_line_id
FROM
zx_transaction_lines_gt Lines,
--zx_trx_headers_gt Headers,
zx_evnt_cls_mappings event,
ra_customer_trx_lines_all ratrxlines
WHERE
lines.application_id = 222
AND lines.application_id = event.application_id
AND lines.entity_code = event.entity_code
AND lines.event_class_code = event.event_class_code
AND Lines.trx_id = ratrxlines.customer_Trx_id
AND Lines.trx_line_id = ratrxlines.customer_Trx_line_id
AND ratrxlines.line_type = 'LINE'
AND ratrxlines.memo_line_id is NOT NULL
) Temp
ON ( Lines_gt.trx_line_id = Temp.trx_line_id)
WHEN MATCHED THEN
UPDATE SET
trx_business_category = nvl(Lines_gt.trx_business_category,
DECODE(Temp.trx_business_category,NULL,Temp.trx_business_category,
Temp.tax_event_class_code||l_delimiter||Temp.trx_business_category)),
product_category = nvl(Lines_gt.product_category, Temp.product_category),
global_attribute1 = Temp.global_attribute1,
global_attribute_category = Temp.global_attribute_category
WHEN NOT MATCHED THEN
INSERT (LINE_AMT) VALUES(NULL);
USING (SELECT
ratrxlines.global_attribute_category,
ratrxlines.global_attribute1,
ratrxlines.global_attribute2 product_fiscal_class,
ratrxlines.global_attribute3 trx_business_category,
Event.tax_event_class_code tax_event_class_code,
Lines.trx_line_id trx_line_id
FROM
zx_transaction_lines_gt Lines,
--zx_trx_headers_gt Headers,
zx_evnt_cls_mappings event,
ra_customer_trx_lines_all ratrxlines
WHERE
lines.application_id = 222
AND lines.application_id = event.application_id
AND lines.entity_code = event.entity_code
AND lines.event_class_code = event.event_class_code
--AND lines.trx_id = headers.trx_id
AND Lines.trx_id = ratrxlines.customer_Trx_id
AND Lines.trx_line_id = ratrxlines.customer_Trx_line_id
AND ratrxlines.line_type = 'LINE'
AND ratrxlines.inventory_item_id is NOT NULL
) Temp
ON ( Lines_gt.trx_line_id = Temp.trx_line_id)
WHEN MATCHED THEN
UPDATE SET
trx_business_category = nvl(Lines_gt.trx_business_category,
DECODE(Temp.trx_business_category,NULL,Temp.trx_business_category,
Temp.tax_event_class_code||l_delimiter||Temp.trx_business_category)),
product_fisc_classification = nvl(Lines_gt.product_fisc_classification, Temp.product_fiscal_class),
global_attribute1 = Temp.global_attribute1,
global_attribute_category = Temp.global_attribute_category
WHEN NOT MATCHED THEN
INSERT (LINE_AMT) VALUES(NULL);
SELECT * FROM zx_lines
WHERE trx_id = p_event_class_rec.trx_id
AND application_id = p_event_class_rec.application_id
AND event_class_code = p_event_class_rec.event_class_code
AND entity_code = p_event_class_rec.entity_code
AND trx_line_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_line_id(p_index)
AND trx_level_type = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.trx_level_type(p_index)
AND manually_entered_flag = 'Y'
AND cancel_flag <> 'Y'
AND mrc_tax_line_flag = 'N'
AND tax_provider_id IS NULL;
SELECT * FROM zx_lines
WHERE application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_application_id(p_index)
AND entity_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_entity_code(p_index)
AND event_class_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_event_class_code(p_index)
AND trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_id( p_index)
AND trx_line_id = NVL(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_line_id(p_index), trx_line_id)
AND trx_level_type = NVL(ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_level_type(p_index), trx_level_type)
AND NVL(manually_entered_flag,'N') = 'Y'
AND NVL(cancel_flag,'N') <> 'Y'
AND NVL(mrc_tax_line_flag,'N') = 'N';