[Home] [Help]
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
4/11/2009 walton for bug no 9080017
Issue: base tax amount is not correct in IL table after tax calcualtion
Anaysis: base_tax_amt_tab(i) is hoding rate instead of amount as part of inclusive ER due to new arithmetic
Fix: re-compute base_tax_amt_tab(i) after tax calculation, formula is:
base_tax_amt_tab(I):=ln_exclusive_price*base_tax_amt_tab(I)+base_tax_amount_nr_tab.
and fix a existing tax re-calculation issue that tax amount is zero after saved changes on AR transaction line
03/12/2009 Modified by Xiao for bug#9109910
Issue: tax_amount is Zero after Trx Line amount changed.
Analysis: for adhoc tax, tax_amount is updated as JAI_CMN_TAXES_ALL.tax_amount, which is always Zero
when adhoc tax is defined in table JAI_CMN_TAXES_ALL.
Fix: Modified cursor tax_cur, get JAI_AR_TRX_TAX_LINES.tax_amount instead of JAI_CMN_TAXES_ALL.tax_amount
to update tax_amount.
02-Sep-2010 Bug 10043656 Modified by Peng Zheng
New Tax Category Default logic for GST
28-Jan-2011 bug 10358786 shujuan Yan Modified
Inclusive Tax
--------- ---------- -------------------------------------------------------------
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,
pn_gst_assessable_Value NUMBER Default 0--added by peng.zheng for bug 10043656
)
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,
a.tax_amount, --Modified by Xiao for bug#9109910 on 3-Dec-09, /*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*/
jai_constants.cgst_regime, 7, /*Added by Peng Zheng for GST Bug#10043656*/
jai_constants.sgst_regime, 7, /*Added by Peng Zheng for GST Bug#10043656*/
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;
SELECT COUNT(*)
FROM JAI_RGM_REGISTRATIONS a,
JAI_RGM_DEFINITIONS b
WHERE a.attribute_code = 'AR_RECEIPT_CLASS'
AND a.registration_type = 'OTHERS'
AND a.attribute_type_code = 'OTHERS'
AND a.regime_id = b.regime_id
AND b.regime_code IN( 'TCS','SERVICE') ;
SELECT '1' flag
FROM ar_receipt_classes a,
ar_receipt_methods b
WHERE a.receipt_class_id = b.receipt_class_id
AND a.name IN(SELECT a.attribute_value receipt_class
FROM JAI_RGM_REGISTRATIONS a,
JAI_RGM_DEFINITIONS b
WHERE a.attribute_code = 'AR_RECEIPT_CLASS'
AND a.registration_type = 'OTHERS'
AND a.attribute_type_code = 'OTHERS'
AND a.regime_id = b.regime_id
AND b.regime_code IN ('TCS','SERVICE'))
AND b.receipt_method_id = cp_receipt_method_id;
SELECT '1' flag
FROM jai_cmn_document_taxes
WHERE source_doc_id = cp_cash_receipt_id
AND source_doc_type = 'CASH';