[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT trx_types.type
FROM
ra_customer_trx_lines_all trxl
, ra_customer_trx_all trx
, ra_cust_trx_types_all trx_types
WHERE trxl.customer_trx_id = trx.customer_trx_id
AND trx_types.cust_trx_type_id = trx.cust_trx_type_id
AND trxl.customer_trx_line_id = pn_customer_trx_line_id;
v_org_id NUMBER(15); -- added by sriram because the orgid value is not going into temp_lines_insert table
SELECT to_date(attribute_value, 'DD/MM/YYYY')
FROM jai_rgm_registrations
WHERE regime_id = p_regime_id
AND attribute_code = 'EFF_DATE_ST_PT'
AND attribute_type_code = 'OTHERS'
AND registration_type = 'OTHERS';
SELECT trx_date
FROM ra_customer_trx_all
WHERE customer_trx_id = v_customer_trx_id;
select to_date(attribute_value, 'DD/MM/YYYY')
from JAI_RGM_ORG_REGNS_V
where regime_id = p_regime_id
and organization_id = p_organization_id
and location_id = p_location_id
AND attribute_code = 'EFF_DATE_ST_PT'
AND attribute_type_code = 'OTHERS'
AND registration_type = 'OTHERS'
AND (NOT EXISTS
(select '1'
from JAI_RGM_ORG_REGNS_V
where regime_id = p_regime_id
and attribute_code IN 'INV_ORG_CLASSIFICATION'
and attribute_value <> 'ORGANIZATION'
and organization_id = p_organization_id
and location_id = p_location_id)
OR
NOT EXISTS
(select '1'
from JAI_RGM_ORG_REGNS_V
where regime_id = p_regime_id
and attribute_code IN 'SERVICE TYPE'
and attribute_value <> 'OTHER'
and organization_id = p_organization_id
and location_id = p_location_id)
);
SELECT gl_date --Xiao for POT changes, reg bug#12533434
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = v_customer_trx_id
AND customer_trx_line_id = v_customer_trx_line_id
AND account_class = 'REV';--Xiao for POT changes, reg bug#12533434
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.reverse_charge_flag,'N') = 'N' --Added by Qiong for reverse charge bug#16001407
/*Bug 11936630 - Inclusive taxes are not interfaced in GL if Order contains only Inclusive Taxes*/
--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 /*uncommented for bug#8775345*/
-- SELECT batch_source_id
FROM JAI_AR_TRXS
WHERE Customer_Trx_ID = v_customer_trx_id;
SELECT batch_source_id
FROM JAI_AR_TRXS
WHERE Customer_Trx_ID = v_customer_trx_id;
select warehouse_id,interface_line_attribute6
from ra_interfacE_lines_all
where interfacE_line_context= 'ORDER ENTRY'
and line_type='LINE'
and interface_line_id=v_customer_trx_line_id;
SELECT a.organization_id, a.location_id
FROM jai_pa_draft_invoices a, ra_interface_lines_all b, pa_projects_all c
WHERE b.interface_line_context = 'PROJECTS INVOICES'
AND b.line_type = 'LINE'
AND b.interface_line_attribute1 = c.segment1
AND c.project_id = a.project_id
AND a.draft_invoice_num = b.interface_line_attribute2
AND interface_line_id=v_customer_trx_line_id;
select location_id
from jai_om_wsh_lines_all
where order_line_id=cp_order_line_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);
SELECT count(1)
FROM OE_ORDER_HEADERS_ALL oh,
OE_ORDER_LINES_ALL ol,
OE_TRANSACTION_TYPES_TL ot,
oe_workflow_assignments owf
WHERE oh.header_id = ol.header_id
AND oh.order_type_id = ot.transaction_type_id
AND oh.order_type_id = owf.order_type_id
AND ol.line_type_id = owf.line_type_id
-- AND oh.order_number = pn_order_number /*Commented order number condition by mmurtuza for bug 14846750*/
AND ot.language = userenv('LANG')
AND ol.line_id = pn_order_line_id
AND owf.process_name IN ('R_RMA_CREDIT_APP_HDR_INV',
'R_RMA_CREDIT_WO_SHIP_APPROVE',
'R_RMA_CREDIT_WO_SHIP_HDR_INV',
'R_RMA_FOR_CREDIT_WO_SHIPMENT',
'R_RMA_FOR_OTA_CREDIT');
SELECT COUNT(1)
FROM MTL_SYSTEM_ITEMS msi,
JAI_OM_OE_RMA_LINES l
WHERE msi.inventory_item_id = pr_new.inventory_item_id
AND msi.inventory_item_id = l.inventory_item_id
AND l.rma_line_id = pn_order_line_id
AND msi.shippable_item_flag = 'N' ;
SELECT ORGANIZATION_ID,LOCATION_ID
FROM JAI_AR_TRXS
WHERE CUSTOMER_TRX_ID = cn_customer_trx_id;
SELECT
COUNT(1)
FROM
JAI_AR_TRX_TAX_LINES
WHERE
link_to_cust_trx_line_id = cn_customer_trx_line_id ;
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.
24 06-jan-2009 vkaranam for bug# 7661892
Issue:
Auto Invoice is landing into error if the Order having the delivery from different warehouses
Fix:
Changed the query logic to get the organization ,location details.
25 08-sep-2009 vkaranam for bug#8775345
Issue: Manual AR invoice is giving an error "Organization Cannot be Null".
Fix:
Changed the query logic to get the organization ,location details.
26. 26-Nov-2009 CSahoo for bug#9151886
Issue: AUTO INVOICE IMPORT ERROR
Fix: Added the code to fetch the organization_id and location_id for project invoices.
27 11-May-2010 Bug 9705313 Added by Bo Li
For nonshippable RMA flow
Generate the account for the nonshippable item Tax
28 29-Oct-2010 abezgam for bug10176878
Issue: When attempting to run Autoinvoice, the following error occurs.
'Invalid Code combination ,please check the Service Tax - Tax Accounting Setup '
Fix: Added an if condition to check if the location_id going into ' jai_ar_rgm_processing_pkg.get_regime_info' is null.
If the location_id null, it is fetched using the SO_AR_HDR_INFO cursor.
29 04-Mar-2011 Enhancement 11821537
Description: Notification # 18/2011 proposes change in Point of Taxation for Service Tax.
Service Tax Liability arises on creation of Invoice and not on Receipt of Payment from Customer
i.e. Accounting changes to Accrual basis from Cash Basis
Fix: Service Tax Libility Account will be hit directly instead of Interim Liablity as there
will be no set off during Receipt/CM Application.
30 12-Apr-2011 Bug 11936630
Description: Data was inserted into JAI_AR_TRX_INS_LINES_T via tax_type_cur only if
there was an Exclusive Tax Line. Hence for Order Imported AR Invoice with only
Inclusive Taxes accounting was not happening.
Fix: Data to be inserted into JAI_AR_TRX_INS_LINES_T irrespective of Tax Types.
31 18-May-2011 Xiao for POT changes, reg bug#12533434.
Issue: Compare transaction date with POT date.
fixed: Compare Accounting Date with POT date. Cursor c_get_trx_date is modified. --Added by Chong.Lei for POT code port
32. 03-Oct-2011 mmurtuza for bug 13042890
Issue: INDIA-AR TAX AND FREIGHT DEFAULTATION IS FIRING WHEN TRANSACTION RAISED IN BASE AR Transaction witout IOL Taxes
Fix: Added a variable v_tax_exists and Added condition in the trigger to check if taxes exists and the concurrent should fire only if taxes exists.
33. 28-Mar-2012 mmurtuza for bug 13649994
Issue: AUTOINVOICE FAILING FOR VAT INVOICES INTERFACED FORM PROJECTS
Fix: Added if condition to skip the cursors check_rma_credit_cur and check_shippable_item_cur in procedure ARI_T1 if the invoices are
imported from projects.
34. 18-May-2012 mmurtuza for bug 14089006
Issue: ERROR ENCOUNTERED IN TRANSACTION WORKBENCH
Fix: Mdofied the definition of variable v_order_number from varchar2(30) to ra_customer_trx_all.interface_header_attribute1%type
35. 10-Dec-2012 mmurtuza for bug 14846750
Issue: INDIA LOCALIZATION ERROR IN AUTO INVOICE IMPORT FOR SUPPLEMENTARY INVOICES.
Fix: Removed parameter and condition of order_number in cursor check_rma_credit_cur
==========================================================================================================================================================
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 to_date(attribute_value, 'DD/MM/YYYY')
FROM jai_rgm_registrations
WHERE regime_id = p_regime_id
AND attribute_code = 'EFF_DATE_ST_PT'
AND attribute_type_code = 'OTHERS'
AND registration_type = 'OTHERS';
SELECT trx_date
FROM ra_customer_trx_all
WHERE customer_trx_id = v_customer_trx_id;
select to_date(attribute_value, 'DD/MM/YYYY')
from JAI_RGM_ORG_REGNS_V
where regime_id = p_regime_id
and organization_id = p_organization_id
and location_id = p_location_id
AND attribute_code = 'EFF_DATE_ST_PT'
AND attribute_type_code = 'OTHERS'
AND registration_type = 'OTHERS'
AND (NOT EXISTS
(select '1'
from JAI_RGM_ORG_REGNS_V
where regime_id = p_regime_id
and attribute_code IN 'INV_ORG_CLASSIFICATION'
and attribute_value <> 'ORGANIZATION'
and organization_id = p_organization_id
and location_id = p_location_id)
OR
NOT EXISTS
(select '1'
from JAI_RGM_ORG_REGNS_V
where regime_id = p_regime_id
and attribute_code IN 'SERVICE TYPE'
and attribute_value <> 'OTHER'
and organization_id = p_organization_id
and location_id = p_location_id)
);
SELECT gl_date --Xiao for POT changes, reg bug#12533434
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = v_customer_trx_id
AND customer_trx_line_id = pr_new.customer_trx_line_id
AND account_class = 'REV';--Xiao for POT changes, reg bug#12533434
SELECT REGIME_ID
FROM JAI_RGM_DEFINITIONS
WHERE REGIME_CODE = CP_REGIME_CODE;
Select a.type
From RA_CUST_TRX_TYPES_ALL a, RA_CUSTOMER_TRX_ALL b
Where a.cust_trx_type_id = b.cust_trx_type_id
And b.customer_trx_id = v_customer_trx_id
And NVL(a.org_id,0) = NVL(b.org_id,0);
SELECT RCTA.org_id,
RCTA.bill_to_customer_id,
NVL(RCTA.bill_to_site_use_id,0),
RCTA.trx_date
FROM RA_CUSTOMER_TRX_ALL RCTA
WHERE RCTA.customer_trx_id = v_customer_trx_id;
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 ORGANIZATION_ID,LOCATION_ID
FROM JAI_AR_TRXS
WHERE CUSTOMER_TRX_ID = cn_customer_trx_id;
SELECT
COUNT(1)
FROM
JAI_AR_TRX_TAX_LINES
WHERE
link_to_cust_trx_line_id = cn_customer_trx_line_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.reverse_charge_flag,'N') = 'N' --Added by Qiong for reverse charge bug#16001407
/*Bug 12895321 - Inclusive taxes are not interfaced in GL if Order contains only Inclusive Taxes*/
--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;
select distinct interface_line_context
from ra_customer_trx_lines_all
where customer_trx_id = p_customer_trx_id
and customer_trx_line_id = p_customer_trx_line_id --added for bug#5597146
and interface_line_context is not null
and rownum = 1;
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 "
25. 23/04/2009 CSahoo for bug#8310220, File Version 120.22.12010000.4,120.22.12010000.5
Commented the cursor c_get_hdr_ctx. added the function get_hdr_ctx
26. 30-aug-2010 vkaranam for bug#10048317
Issue:EXCISE TAXES NOT TRANSFERRING FROM SALES ORDER TO RECEIVABLE for trading organization.
issue is in jai_jar_tl_t.plb:
IF NVL(v_register_code,'N') IN
('23D_EXPORT_WITHOUT_EXCISE','23D_EXPORT_EXCISE',
'23D_DOMESTIC_EXCISE','23D_DOM_WITHOUT_EXCISE','BOND_REG') THEN
--jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_ces
s added by csahoo for bug#6081806
IF Tax_Type_Rec.T_Type IN ('Excise','Addl. Excise','Other
Excise','EXCISE_EDUCATION_CESS','CVD_EDUCATION_CESS',jai_constants.tax_type_sh
_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess) THEN
TAX_TYPE_REC.tax_amt := 0;
When update the AR transaction that are imported by Open Interface, procedure
JAI_JAR_TL_TRIGGER_PKG.ARU_T1 will not be invoked, and the same logic is implemented
in Open Interface Procedure.
29 12-Apr-2011 Bug 11936630
Description: Data was inserted into JAI_AR_TRX_INS_LINES_T via tax_type_cur only if
there was an Exclusive Tax Line. Hence for Order Imported AR Invoice with only
Inclusive Taxes accounting was not happening.
Fix: Data to be inserted into JAI_AR_TRX_INS_LINES_T irrespective of Tax Types.
29 18-May-2011 Xiao for POT changes, reg bug#12533434.
Issue: Compare transaction date with POT date.
fixed: Compare Accounting Date with POT date. Cursor c_get_trx_date is modified. --Added by Chong.Lei for POT code port
30. 03-Oct-2011 mmurtuza for bug 13042890
Issue: INDIA-AR TAX AND FREIGHT DEFAULTATION IS FIRING WHEN TRANSACTION RAISED IN BASE AR Transaction witout IOL Taxes
Fix: Added a variable v_tax_exists and Added condition in the trigger to check if taxes exists and the concurrent should fire only if taxes exists.
31. 27-Dec-2011 mmurtuza for bug 13532483
Issue: TAXES ARE NOT REFLECT IN THE AR DISTRIBUTIONS when sum of taxes is zero
Fix: Commented the part of code which returns the control when the old and new tax amoutns are same.
32. 30-Dec-2011 mmurtuza for bug 13532483
Fix: reverted the cahnegs done for bug 13532483
33. 27-Aug-2012 mmurtuza for bug 14525160
Issue: TAXES ARE NOT REFLECT IN THE AR DISTRIBUTIONS when sum of taxes is zero
Fix: Commented the part of code which returns the control when the old and new tax amoutns are same.
33. 14-Feb-2013 mmurtuza for bug 16052256
Issue: UNABLE TO APPLY TCS TAXES ON AR INVOICES
Description: Added paramter p_called_from while calling jai_rgm_thhold_proc_pkg.get_threshold_slab_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;
SELECT max(jattl.tax_line_no) INTO l_tcs_sur_line_num
FROM JAI_AR_TRX_TAX_LINES jattl, jai_cmn_taxes_all jcta
WHERE jattl.link_to_cust_trx_line_id = pr_new.customer_trx_line_id
AND jattl.tax_id = jcta.tax_id
AND jcta.tax_type = jai_constants.tax_type_tcs_surcharge
GROUP BY jcta.tax_type;
SELECT count(jattl.tax_line_no) INTO l_tot_tax_lines
FROM JAI_AR_TRX_TAX_LINES jattl
WHERE jattl.link_to_cust_trx_line_id = pr_new.customer_trx_line_id;
SELECT trx_date INTO c_conversion_date
FROM ra_customer_trx_all
WHERE customer_trx_id = v_customer_trx_id;
SELECT max(jattl.tax_line_no) INTO l_tcs_line_num
FROM JAI_AR_TRX_TAX_LINES jattl, jai_cmn_taxes_all jcta
WHERE jattl.link_to_cust_trx_line_id = pr_new.customer_trx_line_id
AND jattl.tax_id = jcta.tax_id
AND jcta.tax_type = jai_constants.tax_type_tcs
GROUP BY jcta.tax_type;
SELECT organization_id into ln_organization_id
FROM JAI_AR_TRXS
WHERE customer_trx_id = pr_new.customer_trx_id;
select max(jattl.tax_line_no) into l_max_tax_line_no
from JAI_AR_TRX_TAX_LINES jattl
where jattl.link_to_cust_trx_line_id = pr_new.customer_trx_line_id;
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.user_id,
p_last_update_login => FND_GLOBAL.login_id,
p_operation_flag => NULL,
p_vat_assessable_value => pr_new.vat_assessable_value,
p_thhold_cat_base_tax_typ => 'TCS' ,
p_threshold_tax_cat_id => ln_threshold_tax_cat_id,
p_source_trx_type => null,
p_source_table_name => null,
p_action => 'DEFAULT_TAXES',
p_max_tax_line => l_max_tax_line_no ,
p_max_rgm_tax_line => l_tcs_line_num
);