[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.
------------------------------------------------------------------------------------------*/
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*/
) 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*/
, 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*/
, 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 ;
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.
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
) 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
, 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
===============================================================================
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
);
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;