The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_org_id NUMBER(15); -- added by sriram because the orgid value is not going into temp_lines_insert table
SELECT NVL(minimum_accountable_unit,NVL(precision,2)) curr_precision
FROM fnd_currencies
WHERE currency_code = cp_currency_code;
SELECT
a.tax_id taxid ,
a.tax_rate ,
a.uom uom ,
a.tax_amount tax_amt ,
b.tax_type t_type ,
a.customer_trx_line_id line_id ,
a.tax_line_no tax_line_no -- added by sriram - 10/4/2003 - bug # 2769439
FROM
JAI_AR_TRX_TAX_LINES a ,
JAI_CMN_TAXES_ALL b
WHERE
link_to_cust_trx_line_id = v_customer_trx_line_id AND
a.tax_id = b.tax_id
AND NVL(b.inclusive_tax_flag,'N') = 'N' --Added by Jia Li for Tax inclusive Computations on 2007/11/22, TD11
ORDER BY
1;
SELECT A.paddr
FROM JAI_CMN_LOCATORS_T A , v$session s
WHERE A.PADDR = s.paddr
AND s.audsid = USERENV('SESSIONID');
SELECT set_of_books_id, primary_salesrep_id, org_id , invoice_currency_code, exchange_rate_type,
exchange_date, exchange_rate
--commented by kunkumar for Bug#6066813
,interface_header_context /* 6012570 (5876390) */
FROM RA_CUSTOMER_TRX_ALL
WHERE customer_trx_id = v_customer_trx_id;
SELECT MAX(vat_tax_id)
FROM AR_VAT_TAX_ALL
WHERE tax_code = cp_tax_code ;
SELECT ORG_ID
FROM RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = pr_new.customer_trx_id;
SELECT tax_account_id
FROM JAI_CMN_TAXES_ALL B
WHERE B.tax_id = p_tax_id ;
SELECT created_from, interface_header_attribute1,interface_header_attribute2
FROM ra_customer_trx_all
WHERE customer_trx_id = v_customer_trx_id;
SELECT organization_id, location_id, batch_source_id
FROM JAI_AR_TRXS
WHERE Customer_Trx_ID = v_customer_trx_id;
SELECT register_code
FROM JAI_OM_OE_BOND_REG_HDRS
WHERE organization_id = p_org_id AND location_id = p_loc_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');
SELECT A.register_code
FROM JAI_OM_OE_BOND_REG_HDRS A,
JAI_OM_OE_BOND_REG_DTLS b,
oe_transaction_types_tl ott
WHERE A.organization_id = p_organization_id
AND A.location_id = p_location_id
AND A.register_id = b.register_id
AND b.order_flag = 'Y'
AND b.order_type_id = ott.transaction_type_id
AND ott.NAME = p_order_type;
CURSOR trx_num(v_cust_trx_id NUMBER) IS SELECT
trx_number FROM ra_customer_trx_all WHERE
customer_trx_id = v_cust_trx_id;
select ORDER_PRICE_EXCISE_INCLUSIVE
from JAI_CMN_INVENTORY_ORGS
where organization_id = p_organization_id
and location_id = p_location_id;
SELECT
regime_id ,
regime_code
FROM
jai_regime_tax_types_v jrttv
WHERE
upper(jrttv.tax_type) = upper(cp_tax_type);
FILENAME: JA_IN_APPS_AR_LINES_INSERT_TRG.sql
CHANGE HISTORY:
S.No Date Author and Details
1. 10-Jun-01 Jagdish / Subbu
To populate Tax_rate column in RA_CUSTOMER_TRX_LINES_ALL
table as receipts with discounts doesn't get saved.
2. 2001/06/22 Anuradha Parthasarathy
Commented and Added code for better performance.
3. 2002/03/22 RPK
added the column error_flag to the table 'JAI_AR_TRX_INS_LINES_T'
added the cursor trx_num to get the invoice num.
4 2002/04/22 RPK
BUG#2334972.
Code modified to allow the taxes to be inserted into
the table JAI_AR_TRX_INS_LINES_T.When an order line is having 2 tax amounts
one with a positive value and the other with a negative value(eg:100 and -100)
then these lines were not getting transferred to AR Tables and the same is
required to be transferred to have the proper accounting of the GL/AR.For this,
the when_clause of this trigger is commented to facilitate the execution of this
code.This is required to facilitate the functionality of having discounts in the
OM/AR tax flow.
5. 2002/05/04 Added the If Condition to ensure that the concurrent request to.
AR Tax and Freight Defaultation gets invoked only for manual invoices
6. 2002/05/06 Added the Source Column in the insert statement of Ja-In_temp_lines_insert
table.
8. 2003/04/07 SSUMAITH.Bug # 2779967
column Org id was not inserted in the JAI_AR_TRX_INS_LINES_T table. This needs to be
inserted in the table , because when processing the records using the 'India Local Concurrent'
only records that belong to the orgid of the current responsiblity needs to be picked up.
9. 2003/04/10 SSUMAITH - Bug # 2769439
Also inserting the tax line number in the JAI_AR_TRX_INS_LINES_T table.
This is necessary for creating links between OM and AR.
10. 2003/11/20 SSUMAITH - Bug # 3266982 File Version 617.1
The cursor which fetches order number was still pointing to So_headers_all table.
This caused the query to fetch no records, causing the code not to execute based on
register types.
This bug introduces dependency . Because as part of this bug , a new column ORDER_PRICE_EXCISE_INCLUSIVE
is added into the table - ja_in_hr_organzization_units.
If the flag value of ORDER_PRICE_EXCISE_INCLUSIVE becomes 'Y' then the excise tax will go as zero
else , the normal excise value will go to the base apps.
Also , a join between oe_order_headers_all and oe_transaction_types_tl has been added.
Without this join, if the same sales order number is associated to multiple order types,
there is scope for the wrong order number to be chosen and comparison done on that basis.
With this join condition, the value in the ra_customer_trx_all.interface_header_attribute2 is
compared to the 'Name' field in the oe_transaction_types_tl.
11. 2005/27/01 aiyer - Bug # 4089440 File Version 115.1
Issue:-
In case of service invoices having service/service_education type of taxes the code combination id should be picked
up from regime tax setup. This is being done as a part of the service tax enhancement
Solution:-
The check that, for service type of taxes ccid should be picked up from regime tax setup has been impletemented.
Called the procedure jai_ar_rgm_processing_pkg.get_regime_info is being called for regime setup validation.
Aslo called the function jai_cmn_rgm_recording_pkg.get_account to get the ccid in such cases.
Dependency introduced as a part of this bug:-
This file should be released on top of Bug 4146708.
12. 01-Mar-2005 aiyer - Bug # 4212816 File Version 115.2
Issue:-
In case of invoices having any of the taxes setup for VAT REGIME, the code combination should get picked up from the
VAT Regime setup. This is being done as a part of the VAT enhancement
Solution:-
The check that, for taxes belonging to the VAT regime, ccid should be picked up from VAT regime setup has been impletemented.
The account info is fetched using the function jai_cmn_rgm_recording_pkg.get_account.
Dependency introduced as a part of this bug:-
This file should be released on top of Bug 4245089.
Datamodel changes for VAT
13. 02-Apr-2005 aiyer - Bug # 4279702 File Version 115.3
Issue:-
VAT regime code does not get reinitialized for any tax type which does not fall in the VAT / Service TAx regime
Solution:-
Changed the triggers ja_in_apps_lines_insert_trg and ja_in_apps_ar_lines_update_trg to reinitialize the regime codes
every time processing happens for a tax type.
Dependency introduced as a part of this bug:-
This file should be released on top of Bug 4245089.
14 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
DB Entity as required for CASE COMPLAINCE. Version 116.1
15. 13-Jun-2005 File Version: 116.2
Ramananda for bug#4428980. Removal of SQL LITERALs is done
16 06-Jul-2005 rallamse for bug# PADDR Elimination
1. Commented use of v_paddr and cursor PADDR_CUR
17 04-Aug-2005 Bug4535701. Added by Lakshmi Gopalsami Version 120.2
Commented the cursor which is used for PADDR
18 25-Jan-2007 CSahoo for Bug#5631784, File Version 120.3
Forward Porting of BUG#4742259
Accounting information popluated for TCS regime
19 14-FEB-2007 CSahoo - bug# 5364120 - file version 120.4
Forward Porting Of 11i BUG 5346489.
The transaction amounts are rounded based on the currency precision of the invoice currency code
when the insert id done in the JAI_AR_TRX_INS_LINES_T.
Added the cursor c_inv_curr_precision.
Dependency due to this bug :- None
19. 15-Feb-2007 CSahoo Bug# 5390583, File Version 120.5
Forward Porting of 11i Bug 5357400
When taxes are added manually to the ar invoice from the transactions localised screen and the
batch source is set to bond register, the excise taxes are going to base AR. This should not go.
Fix :
The code to take care of this was in a trading loop and would never reach there.
Modified the code appropriately.
20 24/04/2007 cbabu for bug#6012570 (5876390), File Version 120.7 (115.14 )
FP: For project billing bond register functionality is not required hence added a check to see if
the invoice is created by projects then bond register related logic should not be executed.
21 25/05/2007 sacsethi for bug 6072461 file version 120.11
Problem - MANUAL AR TRANSACTION GIVES PROBLEM WITH VAT TAXES
when we creating transactions in receivalbles , at that time at time of saving , it was
giving error , vat setup is not defined but vat setup was defined
Solution - At time of get account of vat , we were passing wrong value of organzation id and location id .
22. 31/05/2007 csahoo for bug#6081806, File version 120.13
added the sh cess types.
23. 14/06/2007 sacsethi for bug 6072461 for file version - 120.15
This bug is used to fp 11i bug 5183031 for vat reveresal
Problem - Vat Reversal Enhancement not forward ported
Solution - Changes has been done to make it compatible for vat reversal functioanlity also.
==========================================================================================================================================================
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
ja_in_apps_ar_lines_insert_trg.sql
617.1 3266982 IN60105D1 617.1 ssumaith 03/12/2003
115.1 4089440 IN60105D2 + Aiyer 27-Jan-2005 4146708 is the release bug for SERVICE/CESS
4146708 enhancement release
115.2 4247989 IN60106 + 4146708 + 4245089 Aiyer 01-Mar-2005 4245089 is the Base bug for VAT.
This has datamodel changes for VAT.
24. 22/11/2007 Added by Jia Li
for Tax inclusive Computations
--------------------------------------------------------------------------------------------------------------------------------------------------------*/
v_customer_trx_line_id := pr_new.customer_trx_line_id; --Ramananda for File.Sql.35
/* DO not delete this code, enable this code while doing the messageing project
app_exception.raise_exception( EXCEPTION_TYPE => 'APP',
EXCEPTION_CODE => NULL ,
EXCEPTION_TEXT => 'Cannot attach VAT type of taxes to non vatable items.'
);
INSERT INTO JAI_AR_TRX_INS_LINES_T ( -- paddr,
-- 6842749
extended_amount,
customer_trx_line_id,
customer_trx_id,
set_of_books_id,
link_to_cust_trx_line_id,
line_type,
uom_code,
vat_tax_id,
acctd_amount,
amount,
CODE_COMBINATION_ID,
cust_trx_line_sales_rep_id,
insert_update_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
tax_rate, --Tax_rate column added by Jagdish/Subbu 10-Jun-01
error_flag ,--added on 22-Mar-2002 by RPK to store the error_flag.Initially it --will be NULL
source, -- column added by sriram on 6th May 2002.
org_id, -- Added by sriram bug # 2779967
line_number ) -- Added by sriram Bug # 2769439
VALUES ( -- NULL, /* Previously passing v_paddr. Replaced with NULL by rallamse bug#4448789 */
round( TAX_TYPE_REC.tax_amt, ln_inv_curr_precision ), /* rounding based on inv currency precision - bug# 5364120*/
TAX_TYPE_REC.LINE_ID,
v_customer_trx_id,
v_books_id,
v_customer_trx_line_id,
v_line_type,
TAX_TYPE_REC.uom,
ln_tax_rate_id, --v_vat_tax, /* Modified by Ramananda for bug#4468353 due to ebtax uptake by AR */
v_converted_rate * TAX_TYPE_REC.tax_amt,
round(TAX_TYPE_REC.tax_amt, ln_inv_curr_precision ) , /* rounding based on inv currency precision - bug# 5364120*/
v_ccid,
v_salesrep_id,
'U',
pr_new.last_update_date,
pr_new.last_updated_by,
pr_new.creation_date,
pr_new.created_by,
pr_new.last_update_login,
TAX_TYPE_REC.tax_rate, --Tax_rate column added by Jagdish/Subbu 10-Jun-01
'P',
v_created_from,
v_org_id , -- added by sriram bug # 2779967
tax_type_rec.tax_line_no ); -- added by sriram - bug # 2769439
v_insert_update_flag VARCHAR2(1) ;
select interface_header_context
from ra_customer_trx_all
where customer_trx_id = pr_new.customer_trx_id;*/
select distinct interface_line_context
from ra_customer_trx_lines_all
where customer_trx_id = pr_new.customer_trx_id
and customer_trx_line_id = pr_new.customer_trx_line_id --added for bug#5597146
and interface_line_context is not null
and rownum = 1;
select ORDER_PRICE_EXCISE_INCLUSIVE
from JAI_CMN_INVENTORY_ORGS
where organization_id = p_organization_id
and location_id = p_location_id;
SELECT
a.tax_id taxid ,
a.tax_rate ,
a.uom uom ,
a.tax_amount tax_amt ,
b.tax_type t_type ,
a.customer_trx_line_id line_id ,
a.tax_line_no tax_line_no
FROM
JAI_AR_TRX_TAX_LINES a ,
JAI_CMN_TAXES_ALL b
WHERE
link_to_cust_trx_line_id = pr_old.customer_trx_line_id and
a.tax_id = b.tax_id
AND NVL(b.inclusive_tax_flag,'N') = 'N' --Added by Jia Li for Tax inclusive Computations on 2007/11/22
ORDER BY
1;
SELECT paddr
FROM v$session
WHERE audsid = USERENV('SESSIONID');
SELECT set_of_books_id, primary_salesrep_id, invoice_currency_code, exchange_rate_type, exchange_date, exchange_rate
FROM JAI_AR_TRXS
WHERE customer_trx_id = v_customer_trx_id;
SELECT DISTINCT vat_tax_id
FROM AR_VAT_TAX_ALL
WHERE UPPER(tax_code) = cp_tax_code ;
SELECT ORG_ID
FROM RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = pr_new.customer_trx_id;*/
SELECT ORG_ID
FROM RA_CUST_TRX_LINE_GL_DIST_ALL
WHERE CUSTOMER_TRX_ID = pr_new.customer_trx_id
AND account_class ='REC'
AND latest_rec_flag ='Y';
SELECT tax_account_id
FROM JAI_CMN_TAXES_ALL B
WHERE B.tax_id = p_tax_id ;
SELECT gl_date
FROM RA_CUST_TRX_LINE_GL_DIST_ALL
WHERE CUSTOMER_TRX_LINE_ID = pr_old.customer_trx_line_id;
SELECT created_from
FROM JAI_AR_TRXS -- table reference was previously RA_CUSTOMER_TRX_ALL - using JA_IN_RA_CUSTOMER_TRX instead - bug# 2728636
WHERE customer_trx_id = v_customer_trx_id;
CURSOR Insert_Update_Cur(p_customer_trx_line_id IN NUMBER) IS
SELECT INSERT_UPDATE_FLAG
FROM JAI_AR_TRX_INS_LINES_T
WHERE customer_trx_id = V_CUSTOMER_TRX_ID AND
Customer_trx_line_id = p_customer_trx_line_id
ORDER BY CUSTOMER_TRX_LINE_ID;
SELECT organization_id, location_id, batch_source_id
FROM JAI_AR_TRXS
WHERE Customer_Trx_ID = v_customer_trx_id;
SELECT register_code
FROM JAI_OM_OE_BOND_REG_HDRS
WHERE organization_id = p_org_id AND location_id = p_loc_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');
SELECT
regime_id ,
regime_code
FROM
jai_regime_tax_types_v jrttv
WHERE
upper(jrttv.tax_type) = upper(cp_tax_type);
SELECT NVL(minimum_accountable_unit,NVL(precision,2)) curr_precision
FROM fnd_currencies
WHERE currency_code = cp_currency_code;
FILENAME: JA_IN_APPS_AR_LINES_UPDATE_TRG.sql
CHANGE HISTORY:
S.No Date Author and Details
1. 2001/04/09 Changed the Cases for Register Codes
2. 2001/06/11 Jagdish
Added tax_rate for Receipts Discounts Issue
3. 2001/06/22 Anuradha Parthasarathy
Code commented and added to improve performance.
4. 2001/12/13 Anuradha Parthasarathy
Code commented because if the updated tax amount is zero the tax lines
need to be corrected in the Base Tables as well.
5. 2002/05/09 Sriram
Added the Source Column in the Column list in the insert statement
This Column was added in the JAI_AR_TRX_INS_LINES_T table because the
AR Tax and Freight Defaultation Concurrent - was split into 2 concurrents
doing the same functionality - one being called from the AR side for manual invoice
from this trigger and another from OM side which is scheduled to run on a periodid basis
6. 2002/05/09 Sriram
Added the if condition at the bottom of the trigger to conditionally call the concurrent
only if is a manual invoice.
7. 2003/01/02 Ssumaith Bug # 2728636 File Version 615.2
Reference to table RA_CUSTOMER_TRX_ALL was present in the cursor Created_from_cur
Which was causing a mutating error and causing the transaction to error out
with unhandled exception.This has been changed to point to JAI_AR_TRXS
table instead which takes care of the issue.
8. 2003/04/07 SSUMAITH.Bug # 2779967
column Org id was not inserted in the JAI_AR_TRX_INS_LINES_T table. This needs to be
inserted in the table , because when processing the records using the 'India Local Concurrent'
only records that belong to the orgid of the current responsiblity needs to be picked up.
9. 2003/04/10 SSUMAITH - Bug # 2769439
Also inserting the tax line number in the JAI_AR_TRX_INS_LINES_T table.
This is necessary for creating links between OM and AR.
10. 2005/27/01 aiyer - Bug # 4089440 File Version 115.1
Issue:-
In case of service invoices having service/service_education type of taxes the code combination id should be picked
up from regime tax setup. This is being done as a part of the service tax enhancement
Solution:-
The check that, for service type of taxes ccid should be picked up from regime tax setup has been impletemented.
Called the procedure jai_ar_rgm_processing_pkg.get_regime_info is being called for regime setup validation.
Aslo called the function jai_cmn_rgm_recording_pkg.get_account to get the ccid in such cases.
Dependency introduced as a part of this bug:-
This file should be released on top of Bug 4146708.
11. 01-Mar-2005 aiyer - Bug # 4212816 File Version 115.2
Issue:-
In case of invoices having any of the taxes setup for VAT REGIME, the code combination should get picked up from the
VAT Regime setup. This is being done as a part of the VAT enhancement
Solution:-
The check that, for taxes belonging to the VAT regime, ccid should be picked up from VAT regime setup has been impletemented.
The account info is fetched using the function jai_cmn_rgm_recording_pkg.get_account.
Dependency introduced as a part of this bug:-
This file should be released on top of Bug 4245089.
Datamodel changes for VAT
12. 02-Apr-2005 aiyer - Bug # 4279702 File Version 115.3
Issue:-
VAT regime code does not get reinitialized for any tax type which does not fall in the VAT / Service TAx regime
Solution:-
Changed the triggers ja_in_apps_ar_lines_insert_trg and ja_in_apps_ar_lines_update_trg to reinitialize the regime codes
every time processing happens for a tax type.
Dependency introduced as a part of this bug:-
This file should be released on top of Bug 4245089.
13. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
DB Entity as required for CASE COMPLAINCE. Version 116.1
14. 13-Jun-2005 File Version: 116.2
Ramananda for bug#4428980. Removal of SQL LITERALs is done
15. 25-Jan-2007 CSahoo for Bug#5631784, File Version 120.3
Forward Porting of BUG#4742259
Accounting information popluated for TCS regime
15. 14-Feb-2007 CSahoo BUG#5364120, File Version - 120.4
Forward Porting of 11i BUG#5346489
The transaction amounts are rounded based on the currency precision of the invoice currency code
when the insert is done in the JAI_AR_TRX_INS_LINES_T.
Added the cursor c_inv_curr_precision.
Dependency due to this bug :- None
16. 15-Feb-2007 CSahoo Bug#5390583, File Version - 120.5
Forward Porting of 11i bug#5357400
When taxes are added manually to the ar invoice from the transactions localised screen and the
batch source is set to bond register, the excise taxes are going to base AR. This should not go.
Fix :
The code to take care of this was in a trading loop and would never reach there.
17. 05-APR-2007 bduvarag for bug#5671400,File version 120.6
Forward porting the changes done in 11i bug#4648231
Modified the code appropriately.
18 24/04/2007 cbabu for bug#5876390, File Version 120.7 (115.17 )
FP: For project billing bond register functionality is not required hence added a check to see if
the invoice is created by projects then bond register related logic should not be executed.
19. 31/05/2007 CSahoo for bug#6081806, File Version 120.13
added the sh cess tax types.
20. 14/06/2007 sacsethi for bug 6072461 for file version - 120.15
This bug is used to fp 11i bug 5183031 for vat reveresal
Problem - Vat Reversal Enhancement not forward ported
Solution - Changes has been done to make it compatible for vat reversal functioanlity also.
21. 15/06/2007 sacsethi for bug 6131957 for file version - 120.16
R12RUP03-ST1: TRADING TAKES EXCISE PRICE INCLUSIVE EVEN WHEN FLAG IS UNCHECKED
Variable v_order_price_excise_inclusive and cursor c_order_price_excise_inclusive is defined
22. 10/07/2007 CSahoo for bug#5597146, FileVersion 120.17
modified the cursor ORG_CUR.
23. 10/10/2007 CSahoo for bug#5597146, FileVersion 120.19
modified the cursor c_get_hdr_ctx to remove the mutating trigger error.
24. 11/10/2007 CSahoo for bug#5597146, File Version 120.20
Added the following AND condition in the cursor c_get_hdr_ctx
"and customer_trx_line_id = pr_new.customer_trx_line_id "
==========================================================================================================================================================
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
ja_in_apps_ar_lines_update_trg.sql
115.1 4089440 IN60105D2 + Aiyer 27-Jan-2005 4146708 is the release bug for SERVICE/CESS
4146708 enhancement release
115.2 4247989 IN60106 + 4146708 + 4245089 Aiyer 01-Mar-2005 4245089 is the Base bug for VAT.
This has datamodel changes for VAT.
25. 22/11/2007 Added by Jia Li
for Tax inclusive Computations
-------------------------------------------------------------------------------------------------------------------------------------------------------------*/
-- added by sriram - bug # 2779967
v_org_id := FND_PROFILE.VALUE('ORG_ID');
SELECT COUNT(*) INTO v_counter
FROM JAI_AR_TRX_INS_LINES_T b
WHERE b.LINK_TO_CUST_TRX_LINE_ID = pr_new.Customer_Trx_Line_Id
AND b.customer_trx_line_id = Tax_Type_Rec.LINE_ID;
OPEN Insert_Update_Cur(TAX_TYPE_REC.line_id);
FETCH Insert_Update_Cur INTO v_insert_update_flag;
CLOSE Insert_Update_Cur;
IF NVL(v_insert_update_flag,'I') <> 'X' THEN
IF TAX_TYPE_REC.t_type = 'Freight' THEN
v_line_type := 'FREIGHT';
/* DO not delete this code, enable this code while doing the messageing project
app_exception.raise_exception( EXCEPTION_TYPE => 'APP',
EXCEPTION_CODE => NULL ,
EXCEPTION_TEXT => 'Cannot attach VAT type of taxes to non vatable items.'
);
INSERT INTO JAI_AR_TRX_INS_LINES_T (
-- paddr,
-- 6842749
extended_amount,
customer_trx_line_id,
customer_trx_id,
set_of_books_id,
link_to_cust_trx_line_id,
line_type,
uom_code,
vat_tax_id,
acctd_amount,
amount,
CODE_COMBINATION_ID,
cust_trx_line_sales_rep_id,
insert_update_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
tax_rate, -- Tax_rate column added by Jagdish/Subbu 10-Jun-01
Source, -- Source Column added by Sriram / Pavan
org_id , -- org_id column added by sriram - bug # 2779967
line_number ) -- added by sriram - bug # 2769439
VALUES(
-- v_paddr,
-- 6842749
round(TAX_TYPE_REC.tax_amt,ln_inv_curr_precision), /* rounding based on inv currency precision - bug# 5364120*/
TAX_TYPE_REC.line_id,
v_customer_trx_id,
v_books_id,
pr_new.customer_trx_line_id,
v_line_type,
TAX_TYPE_REC.uom,
ln_tax_rate_id, --v_vat_tax, /* Modified by Ramananda for bug#4468353 due to ebtax uptake by AR */
v_converted_rate * TAX_TYPE_REC.tax_amt,
round(TAX_TYPE_REC.tax_amt,ln_inv_curr_precision), /* rounding based on inv currency precision - bug# 5364120*/
v_ccid,
v_salesrep_id,
'U',
pr_new.last_update_date,
pr_new.last_updated_by,
pr_new.creation_date,
pr_new.created_by,
pr_new.last_update_login,
TAX_TYPE_REC.tax_rate, --- Tax_rate column added by Jagdish/Subbu 10-Jun-01
v_Created_from, -- v_created_from column added by Sriram - 09-MAY-2002
v_org_id , -- added by sriram bug # 2779967
tax_type_rec.tax_line_no) ; -- added by sriram - bug # 2769439
UPDATE JAI_AR_TRX_INS_LINES_T
SET extended_amount = TAX_TYPE_REC.tax_amt,
set_of_books_id = v_books_id,
line_type = v_line_type,
uom_code = TAX_TYPE_REC.uom,
acctd_amount = v_converted_rate * TAX_TYPE_REC.tax_amt,
amount = TAX_TYPE_REC.tax_amt,
insert_update_flag = 'U',
tax_rate=TAX_TYPE_REC.tax_rate -- Tax_rate column added by Jagdish/Subbu 10-Jun-01
WHERE customer_trx_id = v_customer_trx_id
AND customer_trx_line_id = TAX_TYPE_REC.line_id;