[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cust_acct_site_id address_id
FROM hz_cust_site_uses_all A -- Removed ra_site_uses_all from Bug# 4434287
WHERE A.site_use_id = p_ship_to_site_use_id;
SELECT tax_category_list
FROM JAI_CMN_CUS_ADDRESSES a
WHERE A.customer_id = p_customer_id
AND A.address_id = p_address_id;
SELECT tax_category_id
FROM JAI_INV_ITM_TAXCTG_DTLS a
WHERE a.tax_category_list = p_tax_category_list
AND a.inventory_item_id = p_inventory_item_id;
SELECT tax_category_list
FROM JAI_CMN_VENDOR_SITES A
WHERE a.vendor_id = p_vendor_id
AND a.vendor_site_id = p_vendor_site_id;
SELECT tax_category_id
FROM JAI_INV_ITM_TAXCTG_DTLS a
WHERE a.tax_category_list = p_tax_category_list
AND a.inventory_item_id = p_inventory_item_id;
updates the same before pick release. However this is currently not allowed by localization even though base
apps allows this feature.
Reason:-
The trigger ja_in_oe_order_lines_aiu_trg raises an error of warehouse not found when the value of warehouse_id goes as
null from the form.
Solution:-
Removed this part from the trigger ja_in_oe_order_lines_aiu_trg. The procedure jai_cmn_tax_defaultation_pkg.ja_in_org_default_taxes has been modified
for the same.
During tax defaultation, if the tax category list is not found in the customer/customer site level then it is being picked up from the
item class level. Now in cases where the warehouseid is left blank in the base apps sales order form, the tax category id from the
master organization set for the default operating unit is picked up for further processing
Dependency Due to this Bug:-
Functional dependency with the trigger ja_in_oe_order_lines_aiu_trg.sql version 115.4
2 31/10/2006 SACSETHI for bug 5228046, File version 120.3
1. Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
This bug has datamodel and spec changes.
2. Forward porting of bug 5219225
3 25/04/2007 cbabu for BUG#6012570 (5876390 )version = 120.5 (115.29 )
FP: Project Billing
4 05/06/2007 bduvarag for the bug#6081966 and 5989740, File version 120.8
forward porting the 11i bugs 6074792 and 5907436
5. 01-08-2007 rchandan for bug#6030615 , Version 120.10
Issue : Inter org Forward porting
----------------------------------------------------------------------------------------------------------------------------------------------------*/
/* Added by Ramananda for bug#4407165 */
lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_tax_defaultation_pkg.ja_in_org_default_taxes';
SELECT
org_id operating_unit,
master_organization_id
FROM
oe_system_parameters ;
SELECT operating_unit
FROM org_organization_definitions
WHERE organization_id = NVL(c_org_id, 0);
SELECT b.tax_category_id
FROM JAI_INV_ITM_SETUPS a , JAI_CMN_TAX_CTGS_ALL b -- redundant, org_organization_definitions c
WHERE a.item_class = b.item_class_cd
AND a.inventory_item_id = v_inventory_item_id
AND a.organization_id = v_org_id
AND b.org_id = v_operating_unit;
p_last_update_date DATE,
p_last_updated_by NUMBER,
p_last_update_login NUMBER,
p_operation_flag NUMBER DEFAULT NULL , -- for CRM this is used to hold aso_shipments.shipment_id
p_vat_assessable_value NUMBER DEFAULT 0
/** bgowrava for forward porting bug#5631784,Following parameters are added for TCS enh.*/
, p_thhold_cat_base_tax_typ JAI_CMN_TAXES_ALL.tax_type%type default null -- tax type to be considered as base when calculating threshold taxes
, p_threshold_tax_cat_id JAI_AP_TDS_THHOLD_TAXES.tax_category_id%type default null
, p_source_trx_type jai_cmn_document_taxes.source_doc_type%type default null
, p_source_table_name jai_cmn_document_taxes.source_table_name%type default null
, p_action varchar2 default null
/** End bug 5631784 */
, pv_retroprice_changed IN VARCHAR2 DEFAULT 'N' --Added by Kevin Cheng for Retroactive Price 2008/01/13
) IS
--TYPE num_tab IS TABLE OF NUMBER(30,3) INDEX BY BINARY_INTEGER; -- sriram - bug # 2812781 was 14 eaerler changed to 30
select 1
from JAI_CMN_TAX_CTG_LINES
where tax_category_id in (p_tax_category_id, nvl(p_threshold_tax_cat_id,-1))
group by tax_id
having count(tax_id) > 1;
select max(line_no) max_tax_line
from JAI_CMN_TAX_CTG_LINES cat
where cat.tax_category_id = p_tax_category_id;
select max(line_no) max_rgm_tax_line
from JAI_CMN_TAX_CTG_LINES cat, JAI_CMN_TAXES_ALL taxes
where cat.tax_category_id = p_tax_category_id
and taxes.tax_id = cat.tax_id
and taxes.tax_type = p_thhold_cat_base_tax_typ;
SELECT a.tax_category_id, a.tax_id, a.line_no lno,
a.precedence_1 p_1, a.precedence_2 p_2,
a.precedence_3 p_3, a.precedence_4 p_4,
a.precedence_5 p_5, a.precedence_6 p_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
a.precedence_7 p_7, a.precedence_8 p_8,
a.precedence_9 p_9, a.precedence_10 p_10,
b.tax_rate, b.tax_amount, b.uom_code, b.end_date valid_date,
DECODE(rgm_tax_types.regime_Code,jai_constants.vat_regime, 4, /* added by ssumaith - bug# 4245053*/
DECODE(UPPER(b.tax_type),
'EXCISE', 1,
'ADDL. EXCISE', 1,
'OTHER EXCISE', 1,
'TDS', 2,
'EXCISE_EDUCATION_CESS' ,1,
'CVD_EDUCATION_CESS' ,1,
0
)
) tax_type_val,
b.mod_cr_percentage, b.vendor_id, b.tax_type,nvl(b.rounding_factor,0) rounding_factor
, inclusive_tax_flag --added by walton for inclusive tax 08-Dev-07
FROM JAI_CMN_TAX_CTG_LINES a, JAI_CMN_TAXES_ALL b ,
jai_regime_tax_types_v rgm_tax_types /* added by ssumaith - bug# 4245053*/
WHERE a.tax_category_id = p_tax_category_id
AND rgm_tax_types.tax_type (+) = b.tax_type /* added by ssumaith - bug# 4245053*/
AND a.tax_id = b.tax_id
-- AND (b.end_date >= sysdate OR b.end_date IS NULL)
ORDER BY A.line_no;
SELECT a.uom_class
FROM mtl_units_of_measure A, mtl_units_of_measure B
WHERE a.uom_code = p_line_uom_code
AND b.uom_code = p_tax_line_uom_code
AND a.uom_class = b.uom_class;
SELECT organization_id, location_id, batch_source_id
FROM JAI_AR_TRXS
WHERE customer_trx_id = p_header_id;
SELECT register_code
FROM JAI_OM_OE_BOND_REG_HDRS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND register_id IN (SELECT register_id
FROM JAI_OM_OE_BOND_REG_DTLS
WHERE order_type_id = p_batch_source_id
AND order_flag = 'N');
2. Package should provide an API for inserting taxes in to new table jai_cmn_document_taxes
Fix:
To support above functionalities the following approach is used.
1. New parameters are added to this procedure to get the tax category defined for threshold limit.
(Please refer the procedure signature)
2. Whenever p_threshold_tax_cat_id is not NULL then it means taxes from two categories needs to be merged.
one using p_tax_category_id and other is p_threshold_tax_cat_id
3. current driving cursor (tax_cur) is modified to handle multiple tax categories.
3.1 For all the tax lines defined in the p_tax_category_id there is no change
3.2 For all the tax lines defined in the p_threshold_tax_cat_id, line_no will be changed
to ln_max_tax_line + line_no where ln_max_tax_line is the maximum of line numbers for
tax lines defined in p_tax_category_id
3.3 All the precedences defined in p_threshold_tax_cat_id will be changed as following
- If precedence refers to base precedence (i.e. 0) it will be changed to ln_max_rgm_tax_line
where ln_max_rgm_tax_line is maximum of the line numbers of taxes having
tax_type = p_thhold_cat_base_tax_typ (i.e. tax type to be considered as a base tax
when calculating threshold taxes defined using p_threshold_tax_cat_id)
- All other precedences will be changed to precedence_N + ln_max_tax_line
6 04/june/2007 ssumaith - bug#6109941 - review comments for TCS .
TCS enhancement forward porting has some minor issues that were resolved.
7. 05-Jun-2007 CSahoo for bug#6077133, File version- 120.7
Issue: The Taxes at header and the Line level does not
tally for the Manually created AR Transaction.
Fix: added a rounding factor round_factor_tab.
8. 16-Oct-2007 CSahoo for bug#6498072, File Version 120.12
R12RUP04-ST1: TCS TAXES ARE WRONG ON ADDING SURCHARGE
On creating a sales order and after delivery the taxes are taken only for a Single Quantity which is wrong.
so made changes in the code so that the taxes are taken for the whole quantity
9 01-Dev-2007 Walton for Inclusive Tax
10. 20-Nov-2008 JMEENA for bug#6488296( FP of 6475430)
Added OR condition in procedure ja_in_calc_prec_taxes as we are passing p_action null in case of 'CASH' Receipt.
Future Dependencies For the release Of this Object:-
(Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
A datamodel change )
----------------------------------------------------------------------------------------------------------------------------------------------------
Current Version Current Bug Dependent Files Version Author Date Remarks
Of File On Bug/Patchset Dependent On
jai_cmn_tax_defaultation_pkg.sql
----------------------------------------------------------------------------------------------------------------------------------------------------
115.1 2977185 IN60105D2 None -- Aiyer 13/07/2004 Row introduces to start dependency tracking
115.3 4245053 IN60106 + ssumaith Service Tax and VAT Infrastructure are created
4146708 + based on the bugs - 4146708 and 4545089 respectively.
4245089
*************************************************************************************************************************/
--Added by Kevin Cheng for Retroactive Price 2008/01/13
--=====================================================
IF pv_retroprice_changed = 'N'
THEN
--=====================================================
--Ramananda for File.Sql.35
bsln_amt := p_tax_amount ;
$$EXTRA_SELECT_COLUMN_LIST$$ - Use this place holder to select additional columns in the sql.
You must also change corrosponding fetch statements and the record being used for fetch.
SELECT statement above should also be changed to include the newly added columns
as they are sharing a common cursor and fetch record.
$$TAX_SOURCE_TABLE$$ - At runtime this placeholder must be replaced with name of
source table to be used for recalculation
$$SOURCE_TABLE_FILTER$$ - At runtime, this place holder must represent a boolean condition
which can filter required rows from the source table
for recalculation. It must be the first condition and should never
start with either AND or OR
$$ADDITIONAL_WHERE_CLAUSE$$ - Replace the placeholder with additional conditions if any.
The condition must start with either AND or OR keyword
$$ADDITIONAL_ORDER_BY$$ - Replace the placeholder with list of columns and order sequence, if required.
Column list must start with comma (,)
If any of this placeholder is not required to be used it must be replaced with a null value as below
replace ( lv_recalculation_sql
, '$$EXTRA_SELECT_COLUMN_LIST$$'
, ''
);
' select a.tax_id
, a.tax_line_no lno
, a.precedence_1 p_1
, a.precedence_2 p_2
, a.precedence_3 p_3
, a.precedence_4 p_4
, a.precedence_5 p_5
, a.precedence_6 p_6
, a.precedence_7 p_7
, a.precedence_8 p_8
, a.precedence_9 p_9
, a.precedence_10 p_10
, a.tax_rate
, a.tax_amount
, b.uom_code
, b.end_date valid_date
, DECODE(rttv.regime_code, '''||jai_constants.vat_regime||''', 4, /* added by ssumaith - bug# 4245053*/
DECODE(UPPER(b.tax_type), ''EXCISE'' , 1
, ''ADDL. EXCISE'', 1
, ''OTHER EXCISE'', 1
, ''TDS'' , 2
, ''EXCISE_EDUCATION_CESS'',6 --modified by walton for inclusive tax
, '''||JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS||''' , 6 /*bduvarag for the bug#5989740*/ --modified by walton for inclusive tax
, ''CVD_EDUCATION_CESS'' ,6 --modified by walton for inclusive tax
, '''||JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS||''' , 6 /*bduvarag for the bug#5989740*/ --modified by walton for inclusive tax
, 0
)
) tax_type_val
, b.mod_cr_percentage
, b.vendor_id
, b.tax_type
, nvl(b.rounding_factor,0) rounding_factor
, b.adhoc_flag
, b.inclusive_tax_flag --added by walton for inclusive tax on 08-Dev-07
$$EXTRA_SELECT_COLUMN_LIST$$
from $$TAX_SOURCE_TABLE$$ a
, JAI_CMN_TAXES_ALL b
, jai_regime_tax_types_v rttv
where $$SOURCE_TABLE_FILTER$$
and rttv.tax_type (+) = b.tax_type
and a.tax_id = b.tax_id $$ADDITIONAL_WHERE_CLAUSE$$
order by a.tax_line_no $$ADDITIONAL_ORDER_BY$$';
, '$$EXTRA_SELECT_COLUMN_LIST$$'
, ',null tax_category_id'
);
open a dynamic select statement using OPEN-FOR statement
*/
/* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'TAX RECALCULATION SQL STATEMENT');
lt_tax_table.delete;
/** Add current record in the lt_tax_table for future use at the time of either UPDATE or INSERT into the tables*/
--FOR rec IN tax_cur(p_tax_category_id) LOOP
lt_tax_table(lt_tax_table.count+1) := rec;
$$EXTRA_SELECT_COLUMN_LIST$$ - Use this place holder to select additional columns in the sql.
You must also change corrosponding fetch statements and the record being used for fetch.
SELECT statement above should also be changed to include the newly added columns
as they are sharing a common cursor and fetch record.
$$TAX_SOURCE_TABLE$$ - At runtime this placeholder must be replaced with name of
source table to be used for recalculation
$$SOURCE_TABLE_FILTER$$ - At runtime, this place holder must represent a boolean condition
which can filter required rows from the source table
for recalculation. It must be the first condition and should never
start with either AND or OR
$$ADDITIONAL_WHERE_CLAUSE$$ - Replace the placeholder with additional conditions if any.
The condition must start with either AND or OR keyword
$$ADDITIONAL_ORDER_BY$$ - Replace the placeholder with list of columns and order sequence, if required.
Column list must start with comma (,)
If any of this placeholder is not required to be used it must be replaced with a null value as below
replace ( lv_recalculation_sql
, '$$EXTRA_SELECT_COLUMN_LIST$$'
, ''
);
' select a.tax_id
, a.tax_line_no lno
, a.precedence_1 p_1
, a.precedence_2 p_2
, a.precedence_3 p_3
, a.precedence_4 p_4
, a.precedence_5 p_5
, a.precedence_6 p_6
, a.precedence_7 p_7
, a.precedence_8 p_8
, a.precedence_9 p_9
, a.precedence_10 p_10
, a.tax_rate
, a.tax_amount
, b.uom_code
, b.end_date valid_date
, DECODE(rttv.regime_code, '''||jai_constants.vat_regime||''', 4, /* added by ssumaith - bug# 4245053*/
DECODE(UPPER(b.tax_type), ''EXCISE'' , 1
, ''ADDL. EXCISE'', 1
, ''OTHER EXCISE'', 1
, ''TDS'' , 2
, ''EXCISE_EDUCATION_CESS'',1
, '''||JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS||''' , 1 /*bduvarag for the bug#5989740*/
, ''CVD_EDUCATION_CESS'' ,1
, '''||JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS||''' , 1 /*bduvarag for the bug#5989740*/
, 0
)
) tax_type_val
, b.mod_cr_percentage
, b.vendor_id
, b.tax_type
, nvl(b.rounding_factor,0) rounding_factor
, b.adhoc_flag
$$EXTRA_SELECT_COLUMN_LIST$$
from $$TAX_SOURCE_TABLE$$ a
, JAI_CMN_TAXES_ALL b
, jai_regime_tax_types_v rttv
where $$SOURCE_TABLE_FILTER$$
and rttv.tax_type (+) = b.tax_type
and a.tax_id = b.tax_id $$ADDITIONAL_WHERE_CLAUSE$$
order by a.tax_line_no $$ADDITIONAL_ORDER_BY$$';
, '$$EXTRA_SELECT_COLUMN_LIST$$'
, ',null tax_category_id'
);
open a dynamic select statement using OPEN-FOR statement
*/
/* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'TAX RECALCULATION SQL STATEMENT');
lt_tax_table.delete;
/** Add current record in the lt_tax_table for future use at the time of either UPDATE or INSERT into the tables*/
--FOR rec IN tax_cur(p_tax_category_id) LOOP
lt_tax_table(lt_tax_table.count+1) := rec;
SELECT
original_tax_amount
INTO
tax_amt_tab(row_count)
FROM
Jai_Retro_Tax_Changes jrtc
WHERE jrtc.tax_id = rec.tax_id
AND jrtc.line_change_id = (SELECT
line_change_id
FROM
Jai_Retro_Line_Changes jrlc
WHERE jrlc.line_location_id = lv_line_loc_id
AND jrlc.doc_type IN ( 'RELEASE'
, 'RECEIPT'
, 'STANDARD PO'
)
AND jrlc.doc_version_number = (SELECT
MAX(jrlc1.doc_version_number)
FROM
Jai_Retro_Line_Changes jrlc1
WHERE jrlc1.line_location_id = lv_line_loc_id
AND jrlc1.doc_type IN ( 'RELEASE'
, 'RECEIPT'
, 'STANDARD PO'
)
)
);
if v_debug then fnd_file.put_line(fnd_file.log, 'Before insert into of tr_name -> '||transaction_name); end if;
INSERT INTO JAI_CRM_QUOTE_TAXES(quote_line_id, quote_header_id, shipment_id, tax_line_no,
precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
precedence_6, precedence_7, precedence_8, precedence_9, precedence_10, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
tax_id, tax_amount,
base_tax_amount,
func_tax_amount,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login)
VALUES ( p_line_id, p_header_id, p_operation_flag, row_count,
rec.p_1, rec.p_2, rec.p_3, rec.p_4, rec.p_5,
rec.p_6, rec.p_7, rec.p_8, rec.p_9, rec.p_10,
rec.tax_id, ROUND(nvl(tax_amt_tab(row_count),0),REC.ROUNDING_FACTOR),
decode(nvl(base_tax_amt_tab(row_count), 0), 0, nvl(tax_amt_tab(row_count),0), nvl(base_tax_amt_tab(row_count), 0)),
(nvl(func_tax_amt_tab(row_count),0) * v_currency_conv_factor),
nvl(p_creation_date, SYSDATE), nvl(p_created_by,1), nvl(p_last_update_date,SYSDATE) ,
nvl(p_last_updated_by,1), nvl(p_last_update_login,1) );
INSERT INTO JAI_OM_OE_SO_TAXES(
line_id, header_id, tax_line_no,
precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
precedence_6, precedence_7, precedence_8, precedence_9, precedence_10, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
tax_id, tax_rate, qty_rate, uom,
tax_amount,
base_tax_amount,
func_tax_amount,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login,
tax_category_id -- cbabu for EnhancementBug# 2427465
) VALUES (
p_line_id, p_header_id, row_count,
rec.p_1, rec.p_2, rec.p_3, rec.p_4, rec.p_5,
rec.p_6, rec.p_7, rec.p_8, rec.p_9, rec.p_10,
rec.tax_id, rec.tax_rate, rec.tax_amount, rec.uom_code,
ROUND(NVL(tax_amt_tab(row_count),0),REC.ROUNDING_FACTOR),
DECODE(NVL(base_tax_amt_tab(row_count), 0), 0, NVL(tax_amt_tab(row_count),0), NVL(base_tax_amt_tab(row_count), 0)),
(NVL(func_tax_amt_tab(row_count),0) * v_currency_conv_factor),
p_creation_date, p_created_by, p_last_update_date,
p_last_updated_by, p_last_update_login,
p_tax_category_id -- cbabu for EnhancementBug# 2427465
);
INSERT INTO jai_cmn_document_taxes(
DOC_TAX_ID,
tax_line_no,
tax_id ,
tax_type,
currency_code ,
tax_rate ,
qty_rate ,
uom ,
tax_amt ,
func_tax_amt,
modvat_flag,
tax_category_id,
source_doc_type ,
source_doc_id ,
source_doc_line_id ,
source_table_name ,
TAX_MODIFIED_BY ,
adhoc_flag ,
precedence_1 ,
precedence_2 ,
precedence_3 ,
precedence_4 ,
precedence_5 ,
precedence_6 ,
precedence_7 ,
precedence_8 ,
precedence_9 ,
precedence_10 ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login )
VALUES (
jai_cmn_document_taxes_s.nextval ,
v_line_num,
rec.tax_id,
rec.tax_type,
p_currency,
rec.tax_rate,
rec.tax_amount,
rec.uom_code,
round(nvl(tax_amt_tab(row_count),0),rec.rounding_factor),
round(NVL(func_tax_amt_tab(row_count),0) * v_currency_conv_factor,rec.rounding_factor),
v_modvat_flag,
p_tax_category_id ,
'INTERORG_XFER',
p_header_id,
p_line_id,
'MTL_MATERIAL_TRANSACTIONS_TEMP',
NULL,
rec.adhoc_flag,
rec.p_1,
rec.p_2,
rec.p_3,
rec.p_4,
rec.p_5,
rec.p_6,
rec.p_7,
rec.p_8,
rec.p_9,
rec.p_10,
p_creation_date,
p_created_by,
p_last_update_date,
p_last_updated_by,
p_last_update_login
);
ELSIF transaction_name = 'RMA_LEGACY_INSERT' THEN
-- This elsif added by Aparajita on 31-may-2002 for bug 2381492
INSERT INTO JAI_OM_OE_RMA_TAXES (
rma_line_id, tax_line_no,
precedence_1, precedence_2, precedence_3, precedence_4,precedence_5,
precedence_6, precedence_7, precedence_8, precedence_9,precedence_10, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
tax_id, tax_rate,
qty_rate, uom, tax_amount,
base_tax_amount,
func_tax_amount, creation_date, created_by,
last_update_date, last_updated_by, last_update_login
) VALUES (
p_line_id, row_count,
rec.p_1,rec.p_2, rec.p_3,rec.p_4, rec.p_5,
rec.p_6,rec.p_7, rec.p_8,rec.p_9, rec.p_10,
rec.tax_id, rec.tax_rate,
rec.tax_amount, rec.uom_code, ROUND(nvl(tax_amt_tab(row_count),0), rec.rounding_factor),
DECODE( NVL(base_tax_amt_tab(row_count), 0), 0, NVL(tax_amt_tab(row_count),0), NVL(base_tax_amt_tab(row_count), 0) ),
nvl(func_tax_amt_tab(row_count),0) * v_currency_conv_factor, p_creation_date, p_created_by,
p_last_update_date, p_last_updated_by, p_last_update_login
);
ELSIF transaction_name = 'SO_LINES_UPDATE' THEN
UPDATE JAI_OM_OE_SO_TAXES
SET tax_amount = ROUND(NVL(tax_amt_tab(row_count),0), REC.ROUNDING_FACTOR),
base_tax_amount = DECODE(NVL(base_tax_amt_tab(row_count), 0), 0, NVL(tax_amt_tab(row_count),0), NVL(base_tax_amt_tab(row_count), 0)),
func_tax_amount = NVL(func_tax_amt_tab(row_count),0) * v_currency_conv_factor,
last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login
WHERE line_id = P_line_id
AND header_id = p_header_id
AND tax_line_no = row_count;
INSERT INTO JAI_AR_TRX_TAX_LINES(
customer_trx_line_id, link_to_cust_trx_line_id, tax_line_no,
precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
precedence_6, precedence_7, precedence_8, precedence_9, precedence_10, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
tax_id, tax_rate, qty_rate, uom,
tax_amount,
base_tax_amount,
func_tax_amount,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login
) VALUES(
ra_customer_trx_lines_s.NEXTVAL, p_line_id, row_count,
rec.p_1, rec.p_2, rec.p_3, rec.p_4, rec.p_5,
rec.p_6, rec.p_7, rec.p_8, rec.p_9, rec.p_10,
rec.tax_id, rec.tax_rate, rec.tax_amount, rec.uom_code,
ROUND(NVL(tax_amt_tab(row_count), 0), REC.ROUNDING_FACTOR),
DECODE(NVL(base_tax_amt_tab(row_count), 0), 0, NVL(tax_amt_tab(row_count),0), NVL(base_tax_amt_tab(row_count), 0)),
NVL(func_tax_amt_tab(row_count),0) * v_currency_conv_factor,
p_creation_date, p_created_by, p_last_update_date,
p_last_updated_by, p_last_update_login
);
INSERT INTO JAI_AR_TRX_TAX_LINES(customer_trx_line_id,
link_to_cust_trx_line_id,
tax_line_no,
precedence_1,precedence_2, precedence_3, precedence_4,precedence_5,
precedence_6,precedence_7, precedence_8, precedence_9,precedence_10, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
tax_id, tax_rate, qty_rate, uom,
tax_amount,
base_tax_amount,
func_tax_amount,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login)
VALUES(ra_customer_trx_lines_s.nextval, p_line_id, row_count,
rec.p_1, rec.p_2, rec.p_3, rec.p_4, rec.p_5,
rec.p_6, rec.p_7, rec.p_8, rec.p_9, rec.p_10,
rec.tax_id, rec.tax_rate, rec.tax_amount, rec.uom_code,
ROUND(nvl(tax_amt_tab(row_count), 0), REC.ROUNDING_FACTOR),
decode(nvl(base_tax_amt_tab(row_count), 0), 0, nvl(tax_amt_tab(row_count),0), nvl(base_tax_amt_tab(row_count), 0)),
(nvl(func_tax_amt_tab(row_count),0) * v_currency_conv_factor),
p_creation_date, p_created_by, p_last_update_date,
p_last_updated_by, p_last_update_login);
ELSIF transaction_name = 'AR_LINES_UPDATE' THEN
--2001/03/30 Manohar Mishra
-- Added the following IF condition
--if ((v_register_code<>'BOND_REG') and (rec.tax_type_val<>1)) then
UPDATE JAI_AR_TRX_TAX_LINES
SET tax_amount = ROUND(NVL(tax_amt_tab(row_count), 0), REC.ROUNDING_FACTOR),
base_tax_amount = DECODE(NVL(base_tax_amt_tab(row_count), 0), 0, NVL(tax_amt_tab(row_count),0), NVL(base_tax_amt_tab(row_count), 0)),
func_tax_amount = NVL(func_tax_amt_tab(row_count),0) * v_currency_conv_factor,
last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login
WHERE link_to_cust_trx_line_id = P_line_id
AND tax_line_no = row_count;
SELECT Tax_Type, Mod_Cr_Percentage, Vendor_Id
FROM JAI_CMN_TAXES_ALL
WHERE Tax_Id = taxid;
SELECT vendor_id
FROM JAI_PO_REQ_LINE_TAXES
WHERE Requisition_Line_Id = ( SELECT Requisition_Line_Id
FROM Po_Requisition_Lines_All
WHERE Line_Location_Id = p_line_id );
SELECT Line_Focus_Id
FROM JAI_PO_LINE_LOCATIONS
WHERE Po_Line_Id = p_line_id
AND Po_Header_Id = p_header_id
AND Line_Location_Id IS NULL;
SELECT Line_Focus_Id
FROM JAI_PO_LINE_LOCATIONS
WHERE Po_Line_Id = p_line_id
AND Po_Header_Id = p_header_id
AND Line_Location_Id = line_id;
INSERT INTO JAI_PO_TAXES(
line_focus_id, line_location_id, po_line_id, po_header_id,
tax_line_no,
precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
precedence_6, precedence_7, precedence_8, precedence_9, precedence_10, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
tax_id, tax_type, tax_rate, qty_rate, uom, tax_amount, tax_target_amount,
currency, modvat_flag, vendor_id,
creation_date, created_by,
last_update_date, last_updated_by, last_update_login,
tax_category_id -- cbabu for EnhancementBug# 2427465
) VALUES (
v_seq_val, v_line_id, p_line_id, p_header_id,
row_count,
rec.p_1, rec.p_2,rec.p_3, rec.p_4, rec.p_5,
rec.p_6, rec.p_7,rec.p_8, rec.p_9, rec.p_10,
rec.tax_id, rec.tax_type, rec.tax_rate, rec.tax_amount, rec.uom_code,
ROUND(NVL(tax_amt_tab(row_count),0), REC.ROUNDING_FACTOR),
DECODE(NVL(base_tax_amt_tab(row_count), 0), 0, NVL(tax_amt_tab(row_count),0),
NVL(base_tax_amt_tab(row_count), 0)) * p_currency_conv_factor ,
p_currency, v_modvat_flag, v_vendor_id,
p_creation_date, p_created_by,
p_last_update_date, p_last_updated_by, p_last_update_login,
p_tax_category_id -- cbabu for EnhancementBug# 2427465
);
JAI_RETRO_PRC_PKG.Update_Price_Changes( pn_tax_amt => ROUND(NVL(tax_amt_tab(row_count),0), REC.ROUNDING_FACTOR)
, pn_line_no => row_count
, pn_line_loc_id => v_line_id
, pv_process_flag => lv_process_flag
, pv_process_message => lv_process_message
);
INSERT INTO JAI_PO_TAXES(
Line_Focus_Id, Line_Location_Id, Po_Line_Id, Po_Header_Id,
Tax_Line_No,
Precedence_1, Precedence_2, Precedence_3, Precedence_4, Precedence_5,
Precedence_6, Precedence_7, Precedence_8, Precedence_9, Precedence_10, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
Tax_Id, Tax_Type, Tax_Rate, Qty_Rate, UOM, Tax_Amount,
Tax_Target_Amount,
Currency, Modvat_Flag, Vendor_Id,
Creation_Date, Created_By,
Last_Update_Date, Last_Updated_By, Last_Update_Login,
tax_category_id -- cbabu for EnhancementBug# 2427465
) VALUES (
v_seq_val, NULL, p_line_id, p_header_id,
row_count,
rec.p_1, rec.p_2, rec.p_3, rec.p_4, rec.p_5,
rec.p_6, rec.p_7, rec.p_8, rec.p_9, rec.p_10,
rec.tax_id, rec.tax_type, rec.tax_rate, rec.tax_amount, rec.uom_code, NULL,
DECODE( NVL(base_tax_amt_tab(row_count), 0), 0, NVL(tax_amt_tab(row_count),0),
NVL(base_tax_amt_tab(row_count), 0)) * p_currency_conv_factor ,
p_currency, v_modvat_flag, v_vendor_id,
p_creation_date, p_created_by,
p_last_update_date, p_last_updated_by, p_last_update_login,
p_tax_category_id -- cbabu for EnhancementBug# 2427465
);
UPDATE JAI_PO_TAXES
SET Tax_Amount = ROUND(NVL( tax_amt_tab(row_count), 0 ),REC.ROUNDING_FACTOR),
tax_target_amount = DECODE(NVL(base_tax_amt_tab(row_count), 0), 0, NVL(tax_amt_tab(row_count),0),
NVL(base_tax_amt_tab(row_count), 0)) * p_currency_conv_factor ,
last_updated_by = p_last_updated_by,
last_update_date = p_last_update_date,
last_update_login = p_last_update_login
WHERE po_line_id = p_line_id
AND line_location_id = v_line_id;
UPDATE JAI_PO_TAXES
SET Tax_Amount = ROUND(NVL( tax_amt_tab(row_count), 0 ), REC.ROUNDING_FACTOR),
tax_target_amount = DECODE(NVL(base_tax_amt_tab(row_count), 0), 0, NVL(tax_amt_tab(row_count),0),
NVL(base_tax_amt_tab(row_count), 0)) * p_currency_conv_factor ,
last_updated_by = p_last_updated_by,
last_update_date = p_last_update_date,
last_update_login = p_last_update_login
WHERE Po_Line_Id = p_line_id
AND Line_Location_Id IS NULL;
INSERT INTO JAI_PO_REQ_LINE_TAXES(
requisition_line_id, requisition_header_id, tax_line_no,
precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
precedence_6, precedence_7, precedence_8, precedence_9, precedence_10, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
tax_id, tax_rate, qty_rate, uom, tax_amount, Tax_Target_Amount,
tax_type, modvat_flag, vendor_id, currency,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login,
tax_category_id -- cbabu for EnhancementBug# 2427465
) VALUES (
p_line_id, p_header_id, row_count,
rec.p_1, rec.p_2, rec.p_3,rec.p_4, rec.p_5,
rec.p_6, rec.p_7, rec.p_8,rec.p_9, rec.p_10,
rec.tax_id, rec.tax_rate, rec.tax_amount, rec.uom_code,
ROUND( NVL(tax_amt_tab(row_count),0), rec.rounding_factor), -- v_currency_conv_factor ),
NVL( base_tax_amt_tab(row_count), 0) * ( v_currency_conv_factor), rec.tax_type, v_modvat_flag, v_vendor_id, v_currency,
p_creation_date, p_created_by, p_last_update_date,
p_last_updated_by, p_last_update_login,
p_tax_category_id -- cbabu for EnhancementBug# 2427465
);
,'Values before insert into jai_cmn_document_taxes' ||chr(10)
|| ',tax_line_no -> '||row_count ||CHR(10)
|| ',tax_id -> '||rec.tax_id ||CHR(10)
|| ',tax_type -> '||rec.tax_type ||CHR(10)
|| ',currency_code -> '||p_currency ||CHR(10)
|| ',tax_rate -> '||rec.tax_rate ||CHR(10)
|| ',qty_rate -> '||rec.tax_amount ||CHR(10)
|| ',uom -> '||rec.uom_code ||CHR(10)
|| ',tax_amt -> '||round( nvl(tax_amt_tab(row_count),0) , rec.rounding_factor ) ||CHR(10)
|| ',func_tax_amt -> '||nvl(func_tax_amt_tab(row_count),0) * v_currency_conv_factor ||CHR(10)
|| ',modvat_flag -> '||v_modvat_flag ||CHR(10)
|| ',adhoc_flag -> '||rec.adhoc_flag ||CHR(10)
|| ',tax_category_id -> '||rec.tax_category_id ||CHR(10)
|| ',source_doc_type -> '||p_source_trx_type ||CHR(10)
|| ',source_doc_id -> '||p_header_id ||CHR(10)
|| ',source_doc_line_id -> '||p_line_id ||CHR(10)
|| ',source_table_name -> '||p_source_table_name ||CHR(10)
|| ',tax_modified_by -> '||jai_constants.tax_modified_by_system ||CHR(10)
|| ',precedence_1 -> '||rec.p_1 ||CHR(10)
|| ',precedence_2 -> '||rec.p_2 ||CHR(10)
|| ',precedence_3 -> '||rec.p_3 ||CHR(10)
|| ',precedence_4 -> '||rec.p_4 ||CHR(10)
|| ',precedence_5 -> '||rec.p_5 ||CHR(10)
|| ',precedence_6 -> '||rec.p_6 ||CHR(10)
|| ',precedence_7 -> '||rec.p_7 ||CHR(10)
|| ',precedence_8 -> '||rec.p_8 ||CHR(10)
|| ',precedence_9 -> '||rec.p_9 ||CHR(10)
|| ',precedence_10 -> '||rec.p_10 ||CHR(10)
|| ',creation_date -> '||p_creation_date ||CHR(10)
|| ',created_by -> '||p_created_by ||CHR(10)
|| ',last_update_date -> '||p_last_update_date ||CHR(10)
|| ',last_updated_by -> '||p_last_updated_by ||CHR(10)
|| ',last_update_login -> '||p_last_update_login ||CHR(10)
); */ --commented by bgowrava for bug#5631784
INSERT INTO jai_cmn_document_taxes
( doc_tax_id
, tax_line_no
, tax_id
, tax_type
, currency_code
, tax_rate
, qty_rate
, uom
, tax_amt
, func_tax_amt
, modvat_flag
, adhoc_flag
, tax_category_id
, source_doc_type
, source_doc_id
, source_doc_line_id
, source_doc_parent_line_no --added by Eric Ma,Sep 27,2007
, source_table_name
, tax_modified_by
, precedence_1
, precedence_2
, precedence_3
, precedence_4
, precedence_5
, precedence_6
, precedence_7
, precedence_8
, precedence_9
, precedence_10
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
VALUES
( jai_cmn_document_taxes_s.nextval -- doc_tax_id
, row_count -- tax_line_no
, rec.tax_id -- tax_id
, rec.tax_type -- tax_type
, p_currency -- currency
, rec.tax_rate -- tax_rate
, rec.tax_amount -- qty_rate
, rec.uom_code -- uom
, round( nvl(tax_amt_tab(row_count),0) -- tax_amount
, rec.rounding_factor
)
, nvl(func_tax_amt_tab(row_count),0)
* v_currency_conv_factor -- func_tax_amount
, v_modvat_flag -- modvat_flag
, rec.adhoc_flag -- adhoc_flag
, rec.tax_category_id -- tax_category_id
, p_source_trx_type -- source_doc_type
, p_header_id -- source_doc_id
, p_line_id -- source_doc_line_id
, p_line_id -- source_doc_parent_line_no,added by Eric Ma
, p_source_table_name -- source_table_name
, jai_constants.tax_modified_by_system
--tax_modified_by(SYSTEM=system defaulted, MANUAL=User Modified)
, rec.p_1 -- precedence_1
, rec.p_2 -- precedence_2
, rec.p_3 -- precedence_3
, rec.p_4 -- precedence_4
, rec.p_5 -- precedence_5
, rec.p_6 -- precedence_6
, rec.p_7 -- precedence_7
, rec.p_8 -- precedence_8
, rec.p_9 -- precedence_9
, rec.p_10 -- precedence_10
, p_creation_date -- creation_date
, p_created_by -- created_by
, p_last_update_date -- last_update_date
, p_last_updated_by -- last_updated_by
, p_last_update_login -- last_update_login
);
insert into jai_cmn_document_taxes
( doc_tax_id
, tax_line_no
, tax_id
, tax_type
, currency_code
, tax_rate
, qty_rate
, uom
, tax_amt
, func_tax_amt
, modvat_flag
, adhoc_flag
, tax_category_id
, source_doc_type
, source_doc_id
, source_doc_line_id
, source_table_name
, tax_modified_by
, precedence_1
, precedence_2
, precedence_3
, precedence_4
, precedence_5
, precedence_6
, precedence_7
, precedence_8
, precedence_9
, precedence_10
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
values
(
jai_cmn_document_taxes_s.nextval -- doc_tax_id
, row_count -- tax_line_no
, rec.tax_id -- tax_id
, rec.tax_type -- tax_type
, p_currency -- currency
, rec.tax_rate -- tax_rate
, rec.tax_amount -- qty_rate
, rec.uom_code -- uom
, round( nvl(tax_amt_tab(row_count),0) -- tax_amount
, rec.rounding_factor
)
, nvl(func_tax_amt_tab(row_count),0)
* v_currency_conv_factor -- func_tax_amount
, v_modvat_flag -- modvat_flag
, rec.adhoc_flag -- adhoc_flag
, rec.tax_category_id -- tax_category_id
, p_source_trx_type -- source_doc_type
, p_header_id -- source_doc_id
, p_line_id -- source_doc_line_id
, p_source_table_name -- source_table_name
, jai_constants.tax_modified_by_system -- tax_modified_by (SYSTEM=system defaulted, MANUAL=User Modified)
, rec.p_1 -- precedence_1
, rec.p_2 -- precedence_2
, rec.p_3 -- precedence_3
, rec.p_4 -- precedence_4
, rec.p_5 -- precedence_5
, rec.p_6 -- precedence_6
, rec.p_7 -- precedence_7
, rec.p_8 -- precedence_8
, rec.p_9 -- precedence_9
, rec.p_10 -- precedence_10
, p_creation_date -- creation_date
, p_created_by -- created_by
, p_last_update_date -- last_update_date
, p_last_updated_by -- last_updated_by
, p_last_update_login -- last_update_login
);
/* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Record inserted into jai_cmn_document_taxes');*/ --commented by bgowrava for bug#5631784
update jai_cmn_document_taxes
set tax_amt = tax_amt_tab(row_count)
,func_tax_amt = nvl(func_tax_amt_tab(row_count),0) * nvl(v_currency_conv_factor ,1)
,last_update_date = p_last_update_date
,last_updated_by = p_last_updated_by
,last_update_login = p_last_update_login
where source_doc_line_id = p_line_id
and tax_id = rec.tax_id
and source_doc_type = jai_constants.pa_draft_invoice;
update JAI_OM_WSH_LINE_TAXES
set tax_amount = tax_amt_tab(row_count)
,func_tax_amount = nvl(func_tax_amt_tab(row_count),0) * nvl(v_currency_conv_factor ,1)
,base_tax_amount = round( nvl(tax_amt_tab(row_count),0), rec.rounding_factor)
,last_update_date = p_last_update_date
,last_updated_by = p_last_updated_by
,last_update_login = p_last_update_login
where delivery_detail_id = p_line_id
and tax_id = rec.tax_id;
update JAI_AR_TRX_TAX_LINES
set tax_amount = tax_amt_tab(row_count)
,func_tax_amount = nvl(func_tax_amt_tab(row_count),0) * nvl(v_currency_conv_factor ,1)
,base_tax_amount = round( nvl(tax_amt_tab(row_count),0), rec.rounding_factor)
,last_update_date = p_last_update_date
,last_updated_by = p_last_updated_by
,last_update_login = p_last_update_login
where link_to_cust_trx_line_id = p_line_id
and tax_id = rec.tax_id;
UPDATE JAI_PO_LINE_LOCATIONS
SET tax_category_id = p_tax_category_id
WHERE line_focus_id = v_line_focus_id_holder;
UPDATE JAI_PO_REQ_LINES
SET tax_category_id = p_tax_category_id
WHERE requisition_line_id = p_line_id;
for select a.tax_id
, decode (a.tax_category_id, p_tax_category_id, a.line_no
, (p_max_tax_line + a.line_no)
) lno
, decode (a.tax_category_id, p_tax_category_id, a.precedence_1
, decode (a.precedence_1, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_1))
) p_1
, decode (a.tax_category_id, p_tax_category_id, a.precedence_2
, decode (a.precedence_2, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_2))
) p_2
, decode (a.tax_category_id, p_tax_category_id, a.precedence_3
, decode (a.precedence_3, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_3))
) p_3
, decode (a.tax_category_id, p_tax_category_id, a.precedence_4
, decode (a.precedence_4, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_4))
) p_4
, decode (a.tax_category_id, p_tax_category_id, a.precedence_5
, decode (a.precedence_5, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_5))
) p_5
/* Bug 5094130. Added by Lakshmi Gopalsami Included precedences 6 to 10*/
, decode (a.tax_category_id, p_tax_category_id, a.precedence_6
, decode (a.precedence_6, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_6))
) p_6
, decode (a.tax_category_id, p_tax_category_id, a.precedence_7
, decode (a.precedence_7, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_7))
) p_7
, decode (a.tax_category_id, p_tax_category_id, a.precedence_8
, decode (a.precedence_8, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_8))
) p_8
, decode (a.tax_category_id, p_tax_category_id, a.precedence_9
, decode (a.precedence_9, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_9))
) p_9
, decode (a.tax_category_id, p_tax_category_id, a.precedence_10
, decode (a.precedence_10, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_10))
) p_10
, b.tax_rate
, b.tax_amount
, b.uom_code
, b.end_date valid_date
, DECODE(rgm_tax_types.regime_Code,jai_constants.vat_regime, 4, /* added by ssumaith - bug# 4245053*/
DECODE(UPPER(b.tax_type),
'EXCISE', 1,
'ADDL. EXCISE', 1,
'OTHER EXCISE', 1,
'TDS', 2,
'EXCISE_EDUCATION_CESS' ,6, --modified by walton for inclusive tax
JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS , 6 , /*bduvarag for the bug#5989740*/ --modified by walton for inclusive tax
'CVD_EDUCATION_CESS' ,6, --modified by walton for inclusive tax
JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS, 6 , /*bduvarag for the bug#5989740*/--modified by walton for inclusive tax
0
)
) tax_type_val
, b.mod_cr_percentage
, b.vendor_id
, b.tax_type
, nvl(b.rounding_factor,0) rounding_factor
, b.adhoc_flag
, a.tax_category_id
, b.inclusive_tax_flag --added by walton for inclusive tax on 08-Dev-07
from JAI_CMN_TAX_CTG_LINES a
, JAI_CMN_TAXES_ALL b
, jai_regime_tax_types_v rgm_tax_types /* added by ssumaith - bug# 4245053*/
where a.tax_category_id in (p_tax_category_id, nvl(p_threshold_tax_cat_id,-1))
and rgm_tax_types.tax_type (+) = b.tax_type /* added by ssumaith - bug# 4245053*/
and a.tax_id = b.tax_id
order by decode (a.tax_category_id, p_tax_category_id, a.line_no
, (p_max_tax_line + a.line_no)
);
for select a.tax_id
, decode (a.tax_category_id, p_tax_category_id, a.line_no
, (p_max_tax_line + a.line_no)
) lno
, decode (a.tax_category_id, p_tax_category_id, a.precedence_1
, decode (a.precedence_1, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_1))
) p_1
, decode (a.tax_category_id, p_tax_category_id, a.precedence_2
, decode (a.precedence_2, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_2))
) p_2
, decode (a.tax_category_id, p_tax_category_id, a.precedence_3
, decode (a.precedence_3, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_3))
) p_3
, decode (a.tax_category_id, p_tax_category_id, a.precedence_4
, decode (a.precedence_4, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_4))
) p_4
, decode (a.tax_category_id, p_tax_category_id, a.precedence_5
, decode (a.precedence_5, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_5))
) p_5
/* Bug 5094130. Added by Lakshmi Gopalsami Included precedences 6 to 10*/
, decode (a.tax_category_id, p_tax_category_id, a.precedence_6
, decode (a.precedence_6, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_6))
) p_6
, decode (a.tax_category_id, p_tax_category_id, a.precedence_7
, decode (a.precedence_7, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_7))
) p_7
, decode (a.tax_category_id, p_tax_category_id, a.precedence_8
, decode (a.precedence_8, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_8))
) p_8
, decode (a.tax_category_id, p_tax_category_id, a.precedence_9
, decode (a.precedence_9, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_9))
) p_9
, decode (a.tax_category_id, p_tax_category_id, a.precedence_10
, decode (a.precedence_10, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_10))
) p_10
, b.tax_rate
, b.tax_amount
, b.uom_code
, b.end_date valid_date
, DECODE(rgm_tax_types.regime_Code,jai_constants.vat_regime, 4, /* added by ssumaith - bug# 4245053*/
DECODE(UPPER(b.tax_type),
'EXCISE', 1,
'ADDL. EXCISE', 1,
'OTHER EXCISE', 1,
'TDS', 2,
'EXCISE_EDUCATION_CESS' ,1,
JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS , 1 , /*bduvarag for the bug#5989740*/
'CVD_EDUCATION_CESS' ,1,
JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS, 1 , /*bduvarag for the bug#5989740*/
0
)
) tax_type_val
, b.mod_cr_percentage
, b.vendor_id
, b.tax_type
, nvl(b.rounding_factor,0) rounding_factor
, b.adhoc_flag
, a.tax_category_id
, b.inclusive_tax_flag --Add by Kevin Cheng
from JAI_CMN_TAX_CTG_LINES a
, JAI_CMN_TAXES_ALL b
, jai_regime_tax_types_v rgm_tax_types /* added by ssumaith - bug# 4245053*/
where a.tax_category_id in (p_tax_category_id, nvl(p_threshold_tax_cat_id,-1))
and rgm_tax_types.tax_type (+) = b.tax_type /* added by ssumaith - bug# 4245053*/
and a.tax_id = b.tax_id
order by decode (a.tax_category_id, p_tax_category_id, a.line_no
, (p_max_tax_line + a.line_no)
);