[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
This would enable the SRS Operating Unit field. User would then be able to select operating unit values related to the
security profile.
2. Remove the default value for the parameter p_org_id and make it Required false, Display false. This would ensure that null value gets passed
to the called procedures/ reports.
3. Change the called procedures/reports. Remove the use of p_org_id and instead derive the org_id using the function mo_global.get_current_org_id
This change has been made many procedures and reports.
The procedure recalculate_excise_taxes has been changed for the same.
01/11/2006 SACSETHI for bug 5228046, File version 120.6
Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
This bug has datamodel and spec changes.
16/04/2007 KUNKUMAR for bug 5989740 file version 115.11.6107.2 of file ja_in_calc_taxes_ato_p.sql;
Issue:Interface Trip Stop concurrent was running into a warning when the shipped qty field was updated
to less than the original ordered qty given during creation of sales order.
Fix: The value of an un-initialized variable was being accessed.
Made the appropriate assignment.
15/06/2007 bduvarag for the bug#6072461, Forward porting of 11i bug#5183031
6/18/2007 ssawant for bug 6134057
row_count Changed to rec.lno in calculate_ato_taxes procedure.
12/10/2007 Kevin Cheng Update the logic for inclusive tax calculation
05-Mar-08 Jia Li Added clause logic for bug# 6846048
Issue: When unit selling price is changed because of discounts,
inclusive taxes are getting added to the tax amount in the JAI_OM_OE_SO_LINES table.
So at ship confirm time, comparing the tax amount in the lines table with the sum of exclusive taxes
in the taxes table was not matching and hence the trigger was returning an error.
Fix: Ensuring that only exclusive taxes are added to the tax amount in the jai_om_oe_so_lines table.
22-Oct-2008 CSahoo - bug#4918667, File Version 120.14.12010000.2
Issue :- In case of retrobilling functionality, there was a divide by zero error which is caused.
The parameter p_line_quantity is being used as a denominator, and in case it is zero
it was causing a zero divide error.
Resolution :- made the change that in case the p_line_quantity is zero , the tax amt is also zero.
March 01. 2010 Bug 9382657
Base Tax Amount is not calculated correctly.
Calculated by multiplying the line amount with effective rate of the precedences
base_tax_amt_tab(I) := ln_exclusive_price*base_tax_amt_tab(I) + base_tax_amount_nr_tab(I)
31-Mar-2010 Bug 9327049
Issue - Could not create receipt for RMA for some transactions.
Cause - Problematic code in procedure calculate_ato_taxes. The
pl sql array tax_rate_zero_tab is not not populated for RMA,
but is being used for comparison in tax calculation. This raises
a No Data Found error.
Fix - Added logic to populate the tax_rate_zero_tab array in case of
RMA also.
07-May-2010 Bug 9674771
Issue - Interface trip stop errors out for partial shipment.
Cause - Similar to that of bug 9327049. Here, the base_tax_amount_nr_tab table is not initialized
for zero rate taxes.
Fix - Added code to initialize the pl sql table for zero rate tax lines also.
20 Feb2012 mmurtuza for bug13473763
Description: WRONG TAX CALCULATION ON SALES ORDER WHEN TAXES APPLIED THROUGH ITEM CATEGORY.
Fix: Modified the procedure calculate_ato_taxes to perform calculation of inclusive and exclsuive taxes.
(Ported from ja_in_calc_prec_taxes procedure)
28-Aug-2012 mmurtuza for bug 14031594
Description: TAXES CALCULATION RESULT IS WRONG FOR IL RMA WHEN THERE IS ADHOC TAX INVOLVED.
Fix: Added code in calculate_ato_taxes procedure to calcualte tax for RMA when tax type is adhoc.
------------------------------------------------------------------------------------------*/
PROCEDURE calculate_ato_taxes
(
transaction_name VARCHAR2,
P_tax_category_id NUMBER,
p_header_id NUMBER,
p_line_id NUMBER,
p_assessable_value NUMBER default 0,
p_tax_amount IN OUT NOCOPY NUMBER,
p_currency_conv_factor NUMBER,
p_inventory_item_id NUMBER,
p_line_quantity NUMBER,
p_quantity NUMBER,
p_uom_code VARCHAR2,
p_vendor_id NUMBER,
p_currency VARCHAR2,
p_creation_date DATE,
p_created_by NUMBER,
p_last_update_date DATE,
p_last_updated_by NUMBER,
p_last_update_login NUMBER,
p_vat_assessable_Value NUMBER DEFAULT 0,
p_vat_reversal_price NUMBER DEFAULT 0 /*Bug#6072461, bduvarag*/
--,pn_gst_assessable_Value NUMBER DEFAULT 0 -- Added by zhiwei for bug10043656 GST enhancement 2010/09/14 /*Commented by mmurtuza for removing GST Changes*/
) IS
TYPE num_tab IS TABLE OF number
INDEX BY BINARY_INTEGER;
SELECT c.tax_line_no lno, c.tax_id, c.tax_rate, c.qty_rate, c.uom uom_code, c.func_tax_amount, c.base_tax_amount,
c.precedence_1 p_1, c.precedence_2 p_2, c.precedence_3 p_3,c.precedence_4 p_4, c.precedence_5 p_5,
c.precedence_6 p_6, c.precedence_7 p_7, c.precedence_8 p_8,c.precedence_9 p_9, c.precedence_10 p_10, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
c.tax_amount, d.tax_type, d.end_date valid_date, d.rounding_factor,
DECODE(rgm_tax_types.regime_Code,jai_constants.vat_regime, 4, \* added by ssumaith - bug# 4245053*\
decode(upper(d.tax_type),
'EXCISE', 1,
'ADDL. EXCISE', 1,
'OTHER EXCISE', 1,
'CVD', 1,
'TDS', 2,
'EXCISE_EDUCATION_CESS', 1,
'CVD_EDUCATION_CESS', 1,
'SH_EXCISE_EDUCATION_CESS' ,1,--Added by kundan kumar for bug#5907436
'SH_CVD_EDUCATION_CESS' , 1, --Added by kundan kumar for bug#5907436
'VAT REVERSAL', 5,\*Bug#6072461, bduvarag*\
0
)
)
tax_type_val,
d.adhoc_flag
FROM JAI_OM_OE_SO_TAXES c ,
JAI_CMN_TAXES_ALL d ,
jai_regime_tax_types_v rgm_tax_types \* added by ssumaith - bug# 4245053*\
WHERE c.line_id = p_line_id
AND c.header_id = p_header_id
AND c.tax_id = d.tax_id
AND rgm_tax_types.tax_type (+) = d.tax_type \* added by ssumaith - bug# 4245053*\
ORDER BY c.tax_line_no;*/
SELECT
c.tax_line_no lno
, c.tax_category_id
, c.tax_id
, c.tax_rate
, c.qty_rate
, c.uom uom_code
, c.func_tax_amount
, c.base_tax_amount
, c.precedence_1 p_1
, c.precedence_2 p_2
, c.precedence_3 p_3
, c.precedence_4 p_4
, c.precedence_5 p_5
, c.precedence_6 p_6
, c.precedence_7 p_7
, c.precedence_8 p_8
, c.precedence_9 p_9
, c.precedence_10 p_10 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
, c.tax_amount
, d.tax_type
, d.end_date valid_date
, nvl(d.rounding_factor,0) rounding_factor
, DECODE( rgm_tax_types.regime_Code
, jai_constants.vat_regime ,4 /* added by ssumaith - bug# 4245053*/
--, jai_constants.cgst_regime ,7 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 /*Commented by mmurtuza for removing GST Changes*/
--, jai_constants.sgst_regime ,7 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 /*Commented by mmurtuza for removing GST Changes*/
, decode( upper(d.tax_type)
, 'EXCISE' ,1
, 'ADDL. EXCISE' ,1
, 'OTHER EXCISE' ,1
, jai_constants.tax_type_cvd ,1
, jai_constants.tax_type_tds ,2
, jai_constants.tax_type_exc_edu_cess ,6
, jai_constants.tax_type_cvd_edu_cess ,6
, jai_constants.tax_type_sh_exc_edu_cess ,6
, jai_constants.tax_type_sh_cvd_edu_cess ,6
, 'VAT REVERSAL' ,5/*Bug#6072461, bduvarag*/
, 0
)
) tax_type_val
, d.adhoc_flag
, d.vendor_id
, d.mod_cr_percentage
, d.inclusive_tax_flag
FROM
Jai_Om_Oe_So_Taxes c
, Jai_Cmn_Taxes_All d
, Jai_Regime_Tax_Types_V rgm_tax_types /* added by ssumaith - bug# 4245053*/
WHERE c.line_id = p_line_id
AND c.header_id = p_header_id
AND c.tax_id = d.tax_id
AND rgm_tax_types.tax_type(+) = d.tax_type /* added by ssumaith - bug# 4245053*/
ORDER BY
c.tax_line_no;
Select 1
from JAI_OM_OE_RMA_LINES
where rma_header_id = v_header_id
and rma_line_id = v_line_id;
SELECT c.tax_line_no lno, c.tax_id, c.tax_rate, c.qty_rate, c.uom uom_code, c.func_tax_amount, c.base_tax_amount,
c.precedence_1 p_1, c.precedence_2 p_2, c.precedence_3 p_3,c.precedence_4 p_4, c.precedence_5 p_5,
c.precedence_6 p_6, c.precedence_7 p_7, c.precedence_8 p_8,c.precedence_9 p_9, c.precedence_10 p_10, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
c.tax_amount, d.tax_type, d.end_date valid_date, d.rounding_factor,
DECODE(rgm_tax_types.regime_Code,jai_constants.vat_regime, 4, \* added by ssumaith - bug# 4245053*\
decode(upper(d.tax_type),'EXCISE', 1, 'ADDL. EXCISE', 1, 'OTHER EXCISE', 1, 'CVD',1, 'TDS', 2, 0
)
) tax_type_val,
d.adhoc_flag
FROM JAI_OM_OE_RMA_TAXES c,
JAI_CMN_TAXES_ALL d ,
jai_regime_tax_types_v rgm_tax_types \* added by ssumaith - bug# 4245053*\
WHERE c.rma_line_id = p_line_id
AND c.tax_id = d.tax_id
AND rgm_tax_types.tax_type(+) = d.tax_type \* added by ssumaith - bug# 4245053*\
ORDER BY c.tax_line_no;*/
SELECT
c.tax_line_no lno
, c.tax_id
, c.tax_rate
, c.qty_rate
, c.uom uom_code
, c.func_tax_amount
, c.base_tax_amount
, c.precedence_1 p_1
, c.precedence_2 p_2
, c.precedence_3 p_3
, c.precedence_4 p_4
, c.precedence_5 p_5
, c.precedence_6 p_6
, c.precedence_7 p_7
, c.precedence_8 p_8
, c.precedence_9 p_9
, c.precedence_10 p_10 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
, c.tax_amount
, d.tax_type
, d.end_date valid_date
, nvl(d.rounding_factor,0) rounding_factor
, DECODE( rgm_tax_types.regime_Code
, jai_constants.vat_regime, 4 /* added by ssumaith - bug# 4245053*/
--, jai_constants.cgst_regime ,7 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 /*Commented by mmurtuza for removing GST Changes*/
--, jai_constants.sgst_regime ,7 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 /*Commented by mmurtuza for removing GST Changes*/
, decode( upper(d.tax_type)
, 'EXCISE' ,1
, 'ADDL. EXCISE' ,1
, 'OTHER EXCISE' ,1
, jai_constants.tax_type_cvd ,1
, jai_constants.tax_type_tds ,2
, jai_constants.tax_type_exc_edu_cess ,6
, jai_constants.tax_type_cvd_edu_cess ,6
, jai_constants.tax_type_sh_exc_edu_cess ,6
, jai_constants.tax_type_sh_cvd_edu_cess ,6
, 'VAT REVERSAL' ,5/*Bug#6072461, bduvarag*/
, 0
)
) tax_type_val
, d.adhoc_flag
, d.vendor_id
, d.mod_cr_percentage
, d.inclusive_tax_flag
FROM
Jai_Om_Oe_Rma_Taxes c
, Jai_Cmn_Taxes_All d
, Jai_Regime_Tax_Types_V rgm_tax_types /* added by ssumaith - bug# 4245053*/
WHERE c.rma_line_id = p_line_id
AND c.tax_id = d.tax_id
AND rgm_tax_types.tax_type(+) = d.tax_type /* added by ssumaith - bug# 4245053*/
ORDER BY
c.tax_line_no;
SELECT
'1'
FROM
JAI_OM_OE_SO_LINES jsl
WHERE
jsl.excise_exempt_type IS NOT NULL AND
jsl.line_id = p_line_id ;
SELECT adhoc_flag
FROM jai_cmn_taxes_all
WHERE tax_id = pn_tax_id;
select vat_exemption_flag from jai_om_oe_so_lines
where line_id = cp_line_id;
select wdd.source_line_id from wsh_delivery_details wdd, jai_om_oe_rma_lines joorl
where wdd.delivery_detail_id = joorl.delivery_detail_id
and joorl.rma_line_id = cp_line_id;
select excise_exempt_type from jai_om_oe_so_lines
where excise_exempt_type IS NOT NULL AND
line_id = cp_line_id;
or rma order . if it corresponds to an rma order , update the
JAI_OM_OE_RMA_TAXES table .
4. 26-Feb-2004 Aiyer For the Bug #3590208 File Version 619.1
Issue:-
Excise Duty Recalculation happens for excise exempted tax lines if the order line quantity
is changed.
Fix:-
Code has been added to set the tax_rate, tax_amount and base_tax_amount to 0 when the Order Line has Excise
exemptions and tax is of "Excise" type. The cursor c_excise_exemption has been added for the purpose.
Dependency Due to this code :-
None
5. 09-Aug-2004 Aiyer - Bug #3802074 File Version 115.2
Issue:-
Uom based taxes do not get calculated correctly if the qty or price is changed.
Reason:-
--------
Previous to this fix the uom currency uom conversion would happen only when the transaction uom code and the tax level uom code where
in the same uom class. This was not required.
This was happening because the UOM calculation was previously happening only for cases of exact match
between transaction uom and setup UOM.
Fix:-
----
Removed the uom_class_cur for loop which used to check for the same uom class match condition. Now the uom conversion happens at all time.
Now if an exact match is not found then the conversion rate between the two uom's is determined and tax amounts are calculated for defaultation.
Dependency Due to This Bug:-
----------------------------
The refresh in the India Localization sales order form was not happening properly leading to taxes getting set incorrectly
when the tax apply button is clicked (in JAINEORD) post updation of qty in Base apps form. To resolve this the locator logic
was removed from the triggers ja_in_oe_order_lines_aiu_trg and ja_in_oe_order_lines_au_trg and a new trigger was created
(ja_in_om_locator_aiu_trg). Some fix was also done in JAINEORD.fmb for the same.
Hence all these objects should be displatched along with this object, due to functional dependency
As this fix is also being taken in the current Due to this the tax was not getting bug
6. 2005/03/10 ssumaith - bug# 4245053 - File version 115.3
Taxes under the vat regime needs to be calculated based on the vat assessable value setup done.
In the vendor additional information screen and supplier additional information screen, a place
has been given to capture the vat assessable value.
This needs to be used for the calculation of the taxes registered under vat regime.
This change has been done by using the view jai_regime_tax_types_v - and outer joining it with the
JAI_CMN_TAXES_ALL table based on the tax type column.
A parameter p_vat_assessable_Value NUMBER DEFAULT 0 has been added to the procedure.
Dependency due to this bug - Huge
This patch should always be accompanied by the VAT consolidated patch - 4245089
7. 08-Jun-2005 Version 116.3 jai_om_tax -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
as required for CASE COMPLAINCE.
8. 13-Jun-2005 File Version: 116.4
Ramananda for bug#4428980. Removal of SQL LITERALs is done
9. 01-Jun-2006 Aiyer for bug# 4691616. File Version 120.3
Issue:-
UOM based taxes do not get calculated correctly.
Solution:-
Fwd ported the fix for the bug 4729742.
Changed the files JAINTAX1.pld, jai_cmn_tax_dflt.plb and jai_om_tax.plb.
10. 10-Dec-007 Kevin Cheng Update the logic for inclusive tax calculation
11. 05-Mar-08 Jia Li Added clause logic for bug# 6846048
Issue: When unit selling price is changed because of discounts,
inclusive taxes are getting added to the tax amount in the JAI_OM_OE_SO_LINES table.
So at ship confirm time, comparing the tax amount in the lines table with the sum of exclusive taxes
in the taxes table was not matching and hence the trigger was returning an error.
Fix: Ensuring that only exclusive taxes are added to the tax amount in the jai_om_oe_so_lines table.
12 22-sep-2010 vkaranam for bug#10114559
Issue:
TAXES ARE NOT CALCULATED AUTOMATICALLY ON A SPLIT ORDER
Reason:
Cess and shecess taxe are caluclated as 0.
This is due to the base_tax_amt before the tax claculation was 0 due to the rounding function.
Fix: -- removed round function for base_tax_amount calculation in calculate_ato_taxes procedure.
13. 12-Jun-2012 mmurtuza for bug 14140327
Issue: EXCISE DUTY IS WRONGLY CALCULATING FOR ISO ORDERS when line is splitted
Description: Added condition while calcualting ln_vamt_nr. If ln_bsln_amt_nr = 0, only then p_tax_amount is added
14. 31-Jul-2012 mmurtuza for bug 14358278
Issue: RATE PER QUANTITY TAX AMT POPULATED AS ZERO IN SALES ORDER LOCALIZED SCREEN.
Fix: Added a block in calculate_ato_taxes to check if tax_amount for that particular tax is defined or not and assigned the value of
tax_amount * qunatity to tax_amt_tab(i)
15. 27-Sep-2012 mmurtuza for bug 14559561
Issue: RMA COPIED ORDER IS INCORRECTLY CALCULATING TAXES
Fix: Added cursors c_get_vat_exempt_flag, c_get_base_line_id, c_excise_exemption_rma to check if the base ordre of RMA is excise / vat
exempted. Also added code to ensure that vat taxes remain zero for vat exempted sales order even if sales order is updated
16. 28-feb-2013 mmurtuza for bug 16389308
Issue: TAXES ARE NOT GETTING APPLIED PROPERLY WHEN THE SALE ORDER LINE IS SPLITTED
Fix: Moved the code to calcualte adhoc taxes in the bgining of procedure arther than calcaulting at the end.
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
calculate_ato_taxes_p.sql
----------------------------------------------------------------------------------------------------------------------------------------------------
115.2 3802074 IN60105D2 ja_in_om_locator_aiu_trg.sql 115.1 Aiyer 26-oct-2004 Functional Dependency
jai_cmn_utils_pkg.ja_in_set_locator 115.0
ja_in_oe_order_lines_aiu_trg 115.5
ja_in_oe_order_lines_aiu_trg 115.5
JAINEORD.fmb 115.3
calculate_ato_taxes_p.sql 115.2 Aiyer
115.3 4245053 IN60106 + ssumaith Service Tax and VAT Infrastructure are created
4146708 + based on the bugs - 4146708 and 4545089 respectively.
4245089
*************************************************************************************************************************************************************/
bsln_amt := p_tax_amount; --Ramananda for File.Sql.35
p8(rec.lno) := nvl(rec.p_8,-1);/*Added by kunkumar,for bugno5604375,deleted second assignment of p7 */
IF transaction_name = 'OE_LINES_UPDATE'
THEN
UPDATE JAI_OM_OE_SO_TAXES
SET tax_amount = nvl(tax_amt_tab(rec.lno),0),
base_tax_amount = decode(nvl(base_tax_amt_tab(rec.lno), 0), 0, nvl(tax_amt_tab(rec.lno),0), nvl(base_tax_amt_tab(rec.lno), 0)),
func_tax_amount = ROUND(nvl(tax_amt_tab(rec.lno),0) * v_currency_conv_factor, rounding_factor_tab(rec.lno) ),
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 = rec.lno;
IF transaction_name = 'OE_LINES_UPDATE'
THEN
UPDATE JAI_OM_OE_RMA_TAXES
SET tax_amount = nvl(tax_amt_tab(rec.lno),0),
base_tax_amount = decode(nvl(base_tax_amt_tab(rec.lno), 0), 0, nvl(tax_amt_tab(rec.lno),0), nvl(base_tax_amt_tab(rec.lno), 0)),
func_tax_amount = ROUND(nvl(tax_amt_tab(rec.lno),0) * v_currency_conv_factor, rounding_factor_tab(rec.lno) ),
last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login
WHERE rma_line_id = P_line_id
AND tax_line_no = rec.lno;
p_last_updated_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER
--,pn_gst_assess_value IN NUMBER --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 /*Commented by mmurtuza for removing GST Changes*/
) IS
-- P_TAX_AMOUNT input parameter will contain the line_tax_amount after successful completion of the procedure which
-- can be used to update the line amount in JAI_OM_OE_SO_LINES
TYPE num_tab IS TABLE OF NUMBER(20,3) INDEX BY BINARY_INTEGER;
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, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
b.tax_amount, b.tax_rate, b.uom_code, b.end_date valid_date,
a.tax_amount tax_line_amt,
DECODE(aa.regime_code,'VAT',4, decode(upper(b.tax_type),
'EXCISE', 1,
'ADDL. EXCISE', 1,
'OTHER EXCISE', 1,
JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,1,jai_constants.tax_type_exc_edu_Cess,1, \*Bug 5989740 bduvarag*\
'TDS', 2, 0)) tax_type_val,
b.tax_type,nvl(b.rounding_factor,0) rounding_factor
FROM JAI_OM_OE_SO_TAXES a, JAI_CMN_TAXES_ALL b,jai_regime_tax_types_v aa
WHERE a.header_id = p_header_id
AND a.line_id = p_line_id
AND a.tax_id = b.tax_id
AND aa.tax_type(+) = b.tax_type
ORDER BY a.tax_line_no;*/
SELECT
a.tax_id
, a.tax_category_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 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
, b.tax_amount
, b.tax_rate
, b.uom_code
, b.end_date valid_date
, a.tax_amount tax_line_amt
, DECODE( aa.regime_code
, JAI_CONSTANTS.VAT_REGIME
, 4
--, JAI_CONSTANTS.cgst_regime --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 /*Commented by mmurtuza for removing GST Changes*/
--, 7
--, JAI_CONSTANTS.sgst_regime --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 /*Commented by mmurtuza for removing GST Changes*/
--, 7
, decode( upper(b.tax_type)
, 'EXCISE' ,1
, 'ADDL. EXCISE' ,1
, 'OTHER EXCISE' ,1
, jai_constants.tax_type_tds ,2
, jai_constants.tax_type_exc_edu_cess ,6
, jai_constants.tax_type_cvd_edu_cess ,6
, jai_constants.tax_type_sh_exc_edu_cess ,6
, jai_constants.tax_type_sh_cvd_edu_cess ,6
, 0
)
) tax_type_val
, b.inclusive_tax_flag
, b.mod_cr_percentage
, b.vendor_id
, b.tax_type
, nvl(b.rounding_factor,0) rounding_factor
FROM
Jai_Om_Oe_So_Taxes a
, Jai_Cmn_Taxes_All b
, Jai_Regime_Tax_Types_V aa
WHERE a.header_id = p_header_id
AND a.line_id = p_line_id
AND a.tax_id = b.tax_id
AND aa.tax_type(+) = b.tax_type
ORDER BY
a.tax_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
'1'
FROM
JAI_OM_OE_SO_LINES jsl
WHERE
jsl.excise_exempt_type IS NOT NULL AND
jsl.line_id = p_line_id ;
6. 11-Dec-2007 Kevin Cheng Update the logic for inclusive tax calculation
7. 03-Mar-2011 Abezgam Bug: 11779012
Description: 'India Excise Duty Recalculation' is not updating the pending sales order
transactions with the latest Excise Assessable value as per the price list.
Fix: Initialized the parameter base_tax_amount_nr_tab in the procedure recalculate_oe_taxes
8. 09-Mar-2012 mmurtuza for bug 13819477
Description: IL12.1.1 INDIA MASS TAX RECALCULATION CONCURRENT CALCULATES INCORRECT TAXES.
Fix: Modified the procedure recalculate_oe_taxes to perform calculation of taxes correctly.
(Ported from ja_in_calc_prec_taxes procedure)
===============================================================================
Dependencies
Version Dependencies Comments
115.1 IN60106 + 4146708 Service and eduation cess functionality
115.2 4245089 VAT implementation
--------------------------------------------------------------------------------------------------------------------------*/
bsln_amt := p_tax_amount; --Ramananda for File.Sql.35
UPDATE JAI_OM_OE_SO_TAXES
SET tax_amount = v_rounded_tax,
base_tax_amount = decode(nvl(base_tax_amt_tab(rec.lno), 0), 0, nvl(tax_amt_tab(rec.lno),0), nvl(base_tax_amt_tab(rec.lno), 0)),
func_tax_amount = nvl(func_tax_amt_tab(rec.lno),0) * p_currency_conv_factor,
last_update_date= p_last_updated_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 = rec.lno;
v_last_update_date oe_order_lines_all.last_update_date%TYPE;
v_last_updated_by oe_order_lines_all.last_updated_by%TYPE;
v_last_update_login oe_order_lines_all.last_update_login%TYPE;
v_transaction_name varchar2(30) := 'SO_LINES_UPDATE';
SELECT nvl(cust_acct_site_id , 0) address_id
FROM hz_cust_site_uses_all A -- Removed ra_site_uses_all for Bug# 4434287
WHERE A.site_use_id = p_ship_to_site_use_id; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
Select sum(a.tax_amount)
From JAI_OM_OE_SO_TAXES a, JAI_CMN_TAXES_ALL b
Where a.Header_ID = p_header_id
and a.line_id = p_line_id
and b.tax_id = a.tax_id
and b.tax_type != 'TDS';
SELECT b.operand list_price,
c.product_uom_code list_price_uom_code
FROM JAI_CMN_CUS_ADDRESSES a, QP_LIST_LINES b, qp_pricing_attributes c
WHERE a.customer_id = p_customer_id
AND a.address_id = p_address_id
AND a.price_list_id = b.LIST_header_ID
AND c.list_line_id = b.list_line_id
AND c.product_attr_value = p_inventory_item_id /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
AND trunc(p_ordered_date) BETWEEN trunc(nvl( start_date_active, p_ordered_date)) -- cbabu for Bug#2496494
AND trunc(nvl( end_date_active, SYSDATE));
Select order_source_id
From oe_order_headers_all
Where header_id = v_header_id;
Select Name
from OE_ORDER_SOURCES
where Order_Source_Id=P_Source_Document_Type_Id;
SELECT base.header_id, base.line_id, base.line_number, base.SHIPMENT_NUMBER,
base.ship_to_ORG_id, base.inventory_item_id, base.ordered_quantity, base.order_quantity_uom,
base.SHIP_FROM_ORG_ID, base.creation_date, base.created_by, base.last_update_date,
base.last_updated_by, base.last_update_login, base.ORIG_SYS_LINE_REF,
base.Line_Category_Code, base.reference_line_id, base.item_type_code, base.split_from_line_id,
base.unit_selling_price, base.ORG_ID, base.SOURCE_DOCUMENT_ID, base.SOURCE_DOCUMENT_LINE_ID,
base.SOURCE_DOCUMENT_TYPE_ID, ja.assessable_value,
NVL(head.org_id,0) org_id1, head.SOLD_TO_ORG_ID, head.SOURCE_DOCUMENT_ID hsdi, head.order_number,
head.price_list_id, head.ORDER_CATEGORY_CODE, head.ORIG_SYS_DOCUMENT_REF, head.TRANSACTIONAL_CURR_CODE,
head.conversion_type_code, head.conversion_rate, head.CONVERSION_RATE_DATE, nvl(head.ORDERED_DATE, head.creation_date) ordered_date
FROM oe_order_headers_all head, oe_order_lines_all base, JAI_OM_OE_SO_LINES ja
WHERE head.header_id = base.header_id
and base.line_id = ja.line_id
and base.OPEN_FLAG = 'Y'
and base.line_category_code = 'ORDER'
and base.ship_from_org_id = p_ship_from_org_id
and base.org_id = p_org_id
and head.order_type_id = p_order_type_id
and head.order_number between p_order_number1 and p_order_number2;
SELECT order_line_id
FROM JAI_OM_WSH_LINES_ALL
WHERE order_line_id = p_line_id
AND order_header_id = p_header_id;
v_last_update_date := SYSDATE;
v_last_updated_by := FND_GLOBAL.USER_ID;
v_last_update_login := FND_GLOBAL.LOGIN_ID;
v_last_update_date := rec.last_update_date;
v_last_updated_by := rec.last_updated_by;
v_last_update_login := rec.last_update_login;
v_creation_date, v_created_by, v_last_update_date, v_last_updated_by, v_last_update_login);
v_last_update_date,
v_last_updated_by,
v_last_update_login
--,ln_gst_assessable_value --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 /*Commented by mmurtuza for removing GST Changes*/
);
UPDATE JAI_OM_OE_SO_LINES
SET assessable_value = v_assessable_value,
tax_amount = nvl(v_line_tax_amount,0),
line_amount = v_line_amount,
line_tot_amount = v_line_amount + nvl(v_line_tax_amount,0),
last_update_date = v_last_update_date,
last_updated_by = v_last_updated_by,
last_update_login = v_last_update_login
WHERE line_id = v_line_id and header_id = v_header_id;