[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select jat.created_from into p_created_from
from JAI_AR_TRXS jat
where jat.customer_trx_id = p_header_id;
select quantity into p_prev_quantity
from po_requisition_lines_all
where requisition_line_id = p_req_line_id;
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
, p_modified_by_agent_flag po_requisition_lines_all.modified_by_agent_flag%type default NULL /*Added for Bug 8241905*/
, p_parent_req_line_id po_requisition_lines_all.parent_req_line_id%type default NULL /*Added for Bug 8241905*/
, p_max_tax_line NUMBER DEFAULT 0 /*Added for Bug 8371741*/
, p_max_rgm_tax_line NUMBER DEFAULT 0 /*Added for Bug 8371741*/
--, pn_gst_assessable_value NUMBER DEFAULT 0 -- Added by Jia for GST Bug#10043656 on 2010/09/10
, pn_customs_assessable_value NUMBER DEFAULT 0 --Added by Bo Li for Bug#11684111 BOE Ehancement
) 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*/
/*jai_constants.cgst_regime, 7, --Added by Jia for GST Bug#10043656 on 2010/09/10
jai_constants.sgst_regime, 7, --Added by Jia for GST Bug#10043656 on 2010/09/10*/
jai_constants.customs_regime, 8, --Added by Bo Li for Bug#11684111 BOE Ehancement
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.
11. 14-Sep-2009 JMEENA for bug#8905076
Modified the update statement of table JAI_OM_WSH_LINE_TAXES and used base_tax_amt_tab instead of
tax_amt_tab to update the column base_tax_amount.
12. 4-Nov-2009 Xiao Lv for bug#8789761
Added variable base_tax_amount_nr_tab with type of tax_amt_num_tab to calculate base tax amount.
base_tax_amt_tab(I) := ln_exclusive_price*base_tax_amt_tab(I) + base_tax_amount_nr_tab.
13. 22-Dec-2009 CSahoo for bug#9214366, File Version 120.13.12010000.9
Issue: UNABLE TO SAVE AR TRANSACTION WITH VAT + TCS OR CST +TCS TYPE OF TAXES
Fix: Modified the code in the procedure ja_in_calc_prec_taxes. Added a new table type variable line_no_tab
to store the line number of the tax lines. Further added a code to initialize the tax_amt_tab table
for surcharge taxes.
14. 19-Mar-2010 Walton for bug#9288016, File Version 120.13.12010000.10
Issue: Function tax amount column is not populated correctly
Fix: in the old code, function tax amount only cover rate amount, when the taxes is computed based on assessable
value, non-rate amount is not getting summed, so the fix is to re-assign function base amount once tax amount
is computed.
15. 19-Mar-2010 JMEENA for bug#9489492
Modified the dynamic query lv_recalculation_sql and changed the order of columns $$EXTRA_SELECT_COLUMN_LIST$$ and inclusive_tax_flag
as inclusive_tax_flag is last column in the record type.
16 10-JUN-2010 Bo Li for Bug#9780751
Issue - Round factor of vat tax is 0. But the accounting entry amount inserted into gl_interface table has 2 bit demical.
Fix - Set the same round factor to the functional tax amount which is used in the gl_interface table
17. 27-aug-2010 vkaranam for bug#9692478
Issue : EXCISE CESS AND SHECESS TAXES NOT GETTING CALCULATED CORRECTLY ON ORDER SPLIT
Issue is due to the base tax amount not calculated correctly.
Fix:
base_tax_amount_nr_tab has been initalized correctly .
18. 10-Sep-2010 Jia for GST Bug#10043656.
19. 11-Mar-2011 Bo Li for BOE Enhancement bug#11684111
20. 16-Jan-2012 Qinglei for Bug#13547239
Issue: FUNCTIONAL TAX AMOUNT IN INDIA LOCALIZATION TAXES UI IS INACCURATE
Fix: Rounded the functional tax amount before insert into jai_cmn_document_taxes.
21. 07-Jun-2012 mmurtuza for bug 14591926
Issue: TAX CALCUALTION IS GOING WRONG WITH INCLUSIVE TAXES
Fix: While calculating the tax amount for exclusive taxes, added condition to check if the corresponding tax line no of precedence of
exclusive tax is inclusive.
22. 27-Sep-2012 mmurtuza for bug 14675629
Issue: IN SHIP CONFIRM LOCALIZED FORM, WRONG TCS TAX AMOUNTS GETTING DEFAULTED
Fix: Added b.tax_amount in lv_recalculation_sql and assigned it to qty_rate_tab(row_count)
23. 17-Jan-2012 mmurtuza for bug 16172861
Issue: INDIA LOC:INCLUSIVE CST TAX AMOUNT CALCULATED WRONGLY IN MANY SALES ORDER
Fix: Added condition while calcualting ln_vamt_nr. If ln_bsln_amt_nr = 0, only then p_tax_amount is added
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 ;
select tax_modified_flag into ln_tax_modified_flag
from JAI_PO_REQ_LINES
where requisition_line_id = p_parent_req_line_id;
for c_req_tax_lines in (select * from JAI_PO_REQ_LINE_TAXES
where requisition_line_id = p_parent_req_line_id) loop
/*Fetch quantity of parent line to populate taxes in child lines*/
ja_in_po_get_reqline_p(p_parent_req_line_id, ln_prev_quantity);
select rounding_factor into ln_rounding_factor
from JAI_CMN_TAXES_ALL
where tax_id = c_req_tax_lines.tax_id;
/*Insert Tax Lines for child lines based on Tax lines of the parent*/
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,
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
) VALUES (
p_line_id, c_req_tax_lines.requisition_header_id, c_req_tax_lines.tax_line_no,
c_req_tax_lines.precedence_1, c_req_tax_lines.precedence_2, c_req_tax_lines.precedence_3,
c_req_tax_lines.precedence_4, c_req_tax_lines.precedence_5,
c_req_tax_lines.precedence_6, c_req_tax_lines.precedence_7, c_req_tax_lines.precedence_8,
c_req_tax_lines.precedence_9, c_req_tax_lines.precedence_10,
c_req_tax_lines.tax_id, c_req_tax_lines.tax_rate, c_req_tax_lines.qty_rate,
c_req_tax_lines.uom,
round((c_req_tax_lines.tax_amount * p_line_quantity)/ln_prev_quantity, ln_rounding_factor),
round((c_req_tax_lines.Tax_Target_Amount * p_line_quantity)/ln_prev_quantity, ln_rounding_factor),
c_req_tax_lines.tax_type, c_req_tax_lines.modvat_flag, c_req_tax_lines.vendor_id,
c_req_tax_lines.currency,
c_req_tax_lines.creation_date, c_req_tax_lines.created_by, c_req_tax_lines.last_update_date,
c_req_tax_lines.last_updated_by, c_req_tax_lines.last_update_login,
c_req_tax_lines.tax_category_id
);
$$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.tax_amount qty_rate /*Added b.tax_amount qty_rate by murtuza for bug 14675629*/
, b.uom_code
, b.end_date valid_date
, DECODE(rttv.regime_code, '''||jai_constants.vat_regime||''', 4, /* added by ssumaith - bug# 4245053*/
/*'''||jai_constants.cgst_regime||''', 7, *//* Added by Jia for GST Bug#10043656 on 2010/09/10 */
/*'''||jai_constants.sgst_regime||''', 7, *//* Added by Jia for GST Bug#10043656 on 2010/09/10 */
'''||jai_constants.customs_regime||''', 8, /* Added by Bo Li for Bug#11684111 BOE Ehancement */
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
$$EXTRA_SELECT_COLUMN_LIST$$
,b.inclusive_tax_flag --added by walton for inclusive tax on 08-Dev-07,--Added inclusive_tax_flag in end as it is last column in record type. by JMEENA for bug#9489492
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'
);
ELSIF transaction_name = 'BOE_INVOICE_UPDATE'
THEN
lv_recalculation_sql :=
replace ( lv_recalculation_sql
, '$$TAX_SOURCE_TABLE$$'
, 'jai_boe_detail_taxes'
);
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*/
/*'''||jai_constants.cgst_regime||''', 7, *//* Added by Jia for GST Bug#10043656 on 2010/09/10 */
/*'''||jai_constants.sgst_regime||''', 7, *//* Added by Jia for GST Bug#10043656 on 2010/09/10 */
'''||jai_constants.customs_regime||''', 8, /* Added by Bo Li for Bug#11684111 BOE Ehancement */
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;
SELECT tax_type
INTO lv_tax_type
FROM jai_cmn_taxes_all
WHERE tax_id = rec.tax_id;
INSERT INTO jai_boe_detail_taxes
(boe_detail_tax_id,
boe_detail_id,
tax_line_no,
precedence_1,
precedence_2,
precedence_3,
precedence_4,
precedence_5,
precedence_6,
precedence_7,
precedence_8,
precedence_9,
precedence_10,
tax_id,
tax_type,-- Add by Qiong Liu for bug#11684111 BOE Ehancement
boe_flag,-- Add by Wenqiong for bug#12611347
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
(jai_boe_detail_taxes_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,
lv_tax_type,-- Add by Qiong Liu for bug#11684111 BOE Ehancement
lv_boe_flag,-- Add by Wenqiong for bug#12611347
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
);
ELSIF transaction_name = 'BOE_INVOICE_UPDATE' THEN
UPDATE jai_boe_detail_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 boe_detail_id = p_line_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, rec.lno,
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)),
ROUND(NVL(func_tax_amt_tab(row_count),0), REC.ROUNDING_FACTOR) * v_currency_conv_factor, --Modified by Bo Li for Bug#9780751 on 11-JUN-2010
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)),
ROUND(NVL(func_tax_amt_tab(row_count),0), REC.ROUNDING_FACTOR) * v_currency_conv_factor, --Modified by Bo Li for Bug#9780751 on 11-JUN-2010D:\Workplace\Source\BUG\bug9780751\jai_cmn_tax_dflt.plb
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*/
, round(round(nvl(func_tax_amt_tab(row_count),0)
,rec.rounding_factor) * v_currency_conv_factor,
rec.rounding_factor) -- func_tax_amount
--Modified by Qinglei on 16-Jan-2012 for bug#13547239
, 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(base_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_AP_TDS_INV_TAXES
set TAX_AMOUNT = tax_amt_tab(row_count)
,calc_tax_amount = tax_amt_tab(row_count)
,base_tax_amount = round(nvl(tax_amt_tab(row_count),0)/nvl(v_currency_conv_factor ,1),2) --Added By zhiwei hou for Bug#13787158 on 20120305
,func_tax_amount = tax_amt_tab(row_count) --Added By zhiwei hou for Bug#13787158 on 20120305
--,func_tax_amount = nvl(func_tax_amt_tab(row_count),0) * nvl(v_currency_conv_factor ,1) --Commented By zhiwei hou for Bug#13787158 on 20120305
,last_update_date = p_last_update_date
,last_updated_by = p_last_updated_by
,last_update_login = p_last_update_login
where invoice_id = p_header_id
and invoice_distribution_id = p_line_id
and actual_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.tax_amount qty_rate /*Added b.tax_amount qty_rate by murtuza for bug 14675629*/
, b.uom_code
, b.end_date valid_date
, DECODE(rgm_tax_types.regime_Code,jai_constants.vat_regime, 4, /* added by ssumaith - bug# 4245053*/
/*jai_constants.cgst_regime, 7,*/ /* Added by Jia for GST Bug#10043656 on 2010/09/10 */
/*jai_constants.sgst_regime, 7, *//* Added by Jia for GST Bug#10043656 on 2010/09/10 */
jai_constants.customs_regime, 8, /* Added by Bo Li for Bug#11684111 BOE Ehancement */
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*/
/*jai_constants.cgst_regime, 7, *//* Added by Jia for GST Bug#10043656 on 2010/09/10 */
/*jai_constants.sgst_regime, 7, *//* Added by Jia for GST Bug#10043656 on 2010/09/10 */
jai_constants.customs_regime, 8, /* Added by Bo Li for Bug#11684111 BOE Ehancement */
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)
);