The following lines contain the word 'select', 'insert', 'update' or 'delete':
added the who columns in the insert into tables JAI_AP_ETDS_REQUESTS and JAI_AP_ETDS_T.
Dependencies Due to this bug:-
None
01/11/2006 SACSETHI for bug 5228046, File version 120.4
Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
This bug has datamodel and spec changes.
16/04/2007 KUNKUMAR for bugno 5989740
Forward porting to R12 from 11i version 115.3.6107.2
--------- ---------- -------------------------------------------------------------
08-Jun-2005 Version 116.2 jai_ar_utils -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
as required for CASE COMPLAINCE.
*/
PROCEDURE recalculate_tax(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_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
)
IS
TYPE num_tab IS TABLE OF number
INDEX BY BINARY_INTEGER;
SELECT a.tax_id, a.tax_line_no lno, b.adhoc_flag, a.base_tax_amount, a.func_tax_amount,
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,
b.tax_rate, b.tax_amount, b.uom_code, b.end_date valid_date, b.rounding_factor,
DECODE(rgm_tax_types.regime_Code,jai_constants.vat_regime, 4, /* added by ssumaith - bug# 4245053*/
decode(upper(b.tax_type),
'EXCISE', 1,
'ADDL. EXCISE', 1,
'OTHER EXCISE', 1,
'CVD', 1,
'TDS', 2,
-- Modified by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
-- change tax_type_val
-------------------------------------------------------------------------
'EXCISE_EDUCATION_CESS' , 6,
'CVD_EDUCATION_CESS' , 6,
'SH_EXCISE_EDUCATION_CESS' , 6,--Added by kundan kumar for bug#5907436
'SH_CVD_EDUCATION_CESS' , 6, --Added by kundan kumar for bug#5907436 Added by kunkumar for forward porting
-------------------------------------------------------------------------
-- Modified by Jia Li for Tax Inclusive Computations on 2007/12/11, End
0
)
)tax_type_val,
b.mod_cr_Percentage, b.vendor_id, b.tax_type
, b.inclusive_tax_flag -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
FROM JAI_AR_TRX_TAX_LINES a, JAI_CMN_TAXES_ALL b ,
jai_regime_tax_types_v rgm_tax_types /* added by ssumaith - bug# 4245053*/
WHERE a.link_to_cust_trx_line_id = p_line_id
AND a.tax_id = b.tax_id
AND rgm_tax_types.tax_type (+) = b.tax_type /* added by ssumaith - bug# 4245053*/
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;
insert into xc values( ' Tax Amt from I loop in bkend Line ' || to_char( i ) || ' is ' || to_char( tax_amt_tab( I ) ) );
insert into xc values( ' Tax Amt from II loop in bkend Line ' || to_char( i ) || ' is ' || to_char( tax_amt_tab( I ) ) );
IF transaction_name = 'AR_LINES_UPDATE'
THEN
IF NVL(rec.adhoc_flag,'N') = 'Y' THEN
UPDATE JAI_AR_TRX_TAX_LINES
SET tax_amount = nvl(rec.tax_amount,0),
base_tax_amount = nvl(rec.base_tax_amount,0),
func_tax_amount = nvl(rec.func_tax_amount,0),
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 = rec.lno;
UPDATE JAI_AR_TRX_TAX_LINES
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 link_to_cust_trx_line_id = P_line_id
AND tax_line_no = rec.lno;
SELECT count(*)
FROM JAI_AR_TRX_UPDATE_T
WHERE trx_id = p_trx_id;
Deadlocks being caused due to JAI_AR_TRX_UPDATE_T table as the same row of table is being updated from different forms and triggers.
To resolve this, procedure is made to execute in AUTONOMOUS_TRANSACTION (Nested TRANSACTION CYCLE with COMMIT) mode
This whole procedure is modified as an Table Handler for JAI_AR_TRX_UPDATE_T table. for this purpose a new parameter named p_flag
is added that takes care of INSERT/UPDATE/DELETE on the table
HIGH DEPENDENCY for future bugs
------------------------------------------------------------------------------------------ */
-- 3985561
If p_flag = 'N' THEN
UPDATE JAI_AR_TRX_UPDATE_T
SET modified_flag = p_flag
WHERE trx_id = p_trx_id;
DELETE FROM JAI_AR_TRX_UPDATE_T
WHERE trx_id = p_trx_id;
UPDATE JAI_AR_TRX_UPDATE_T
SET modified_flag = p_flag
WHERE trx_id = p_trx_id;
INSERT INTO JAI_AR_TRX_UPDATE_T(
trx_ID, modified_flag,
-- added, Harshita for Bug 4866533
created_by, creation_date, last_updated_by, last_update_date
) VALUES (
p_trx_id, p_flag,
-- added, Harshita for Bug 4866533
fnd_global.user_id, sysdate, fnd_global.user_id, sysdate
);
CURSOR cur IS SELECT item_class
FROM JAI_OPM_ITM_MASTERS
WHERE ITEM_ID = i_item_id;
PROCEDURE apps_rel_insert(p_org_id IN NUMBER, p_loc_id IN NUMBER, p_rg_flag IN Varchar2,
p_reg_name IN Varchar2 ,p_complete_flag IN Varchar2, p_cretaed_by IN Number,
last_updated_by IN Number, p_last_update_login IN Number, p_creation_date IN Date, last_update_date IN Date
) IS
-- v_paddr v$session.paddr%type;
lv_object_name CONSTANT VARCHAR2(61) := 'jai_ar_utils_pkg.apps_rel_insert';
FILENAME: apps_rel_insert.sql
CHANGE HISTORY:
S.No Date Author and Details
--------------------------------------------------------------------------------------------*/
/*
Select paddr INTO v_paddr From v$session
Where sid = (Select sid From v$mystat Where Rownum = 1);
/*Select paddr into v_paddr
From v$session
Where audsid = userenv('SESSIONID');
Insert Into JAI_AR_TRX_APPS_RELS_T(
Organization_ID,Location_ID,RG_Update_Flag,Register_Type,
Once_completed_flag
/* Bug 4535701. Commented by Lakshmi Gopalsami
* As part of global temporary table
* WHO columns has been removed
, paddr,
created_by, last_updated_by, last_update_login, creation_date, last_update_date */
) Values (
p_org_id,p_loc_id,p_rg_flag,p_reg_name,
p_complete_flag/*, v_paddr,
p_cretaed_by, last_updated_by, p_last_update_login, p_creation_date, last_update_date */
);
END apps_rel_insert;