[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
added the who columns in the insert of JAI_CMN_ERRORS_T
Dependencies Due to this bug:-
None
2 24/04/1005 cbabu for bug#6012570 (5876390) Version: 120.11
Projects Billing Enh.
forward ported from R11i to R12
3 26/04/2007 CSahoo for bug#5989740 File Version 120.12
Forward porting of 11i BUG#5907436
ENH: Handling Secondary and Higher Education Cess
Added the new cess types jai_constants.tax_type_sh_exc_edu_cess, jai_constants.tax_type_sh_cvd_edu_cess
4. 27/04/2007 CSahoo for bug#5879769, File Version 120.12
Forward porting of 11i bug#5694855
Added a function get_service_type to get the service_type_code
added the cursor c_get_address_details to get the customer_id and Customer_site_id
5. 14/05/2007 bduvarag for bug 5879769 File Version 120.14
Removed the Project Billing Code
6. 04/07/2007 brathod, File Version 120.17, bug#6012570 (5876390)
Reintroduced the project billing related changes.
7. 11-10-07 JMEENA for bug# 6493501 File Version 120.7.12000000.5
Issue: AUTOINVOICE PROGRAM GOING IN ERROR
Reason: IL doesn't processes the data which is being imported into Receivables,
if interface_line_context is any of the following :-
('PROJECTS INVOICES', 'OKS CONTRACTS','LEGACY', 'Property-Projects','CLAIM').
Fix: Trigger jai_ractl_ariud_trg:-
IL sucessfully processes the data which is being imported into Receivables,
if interface_line_context is any of the following:-
('ORDER ENTRY', 'SUPPLEMENT CM', 'SUPPLEMENT DM', 'SUPPLEMENT INVOICE',
'TCS Debit Memo', 'TCS Credit Memo' )
and interface_header_context is any of the following
('PROJECTS INVOICES', 'PA INVOICES') --'PA INTERNAL INVOICES'
(jai_ractl_trg_pkg) Function is_this_projects_context:-
Commented 'PA INTERNAL INVOICES'
It can be used to support interproject or intercompany billing in future
8 06-nov-08 vkaranam for bug#7539258, File Version 120.7.12000000.6
forwardported the changes done in 115 bug#7536069
----------------------------------------------------------------------------------------
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
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
*/
PROCEDURE ARD_T1 ( pr_old t_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
v_excise Number := 0;
SELECT A.tax_id, A.tax_rate, A.tax_amount tax_amt,b.tax_type t_type
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
order by 1;
SELECT line_amount ,tax_amount --added tax_amount for bug#7539258
FROM JAI_AR_TRX_LINES
WHERE CUSTOMER_TRX_ID = v_customer_trx_id AND
CUSTOMER_TRX_LINE_ID = v_customer_trx_line_id;
SELECT once_completed_flag
FROM JAI_AR_TRXS
WHERE customer_trx_id = v_customer_trx_id;
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(pr_old.org_id,0);
Select a.type
From RA_CUST_TRX_TYPES_ALL a
Where cust_trx_type_id
in
(
SELECT cust_trx_type_id
FROM RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = v_customer_trx_id
AND org_id = pr_old.ORG_ID /* Modified by Ramananda for removal of SQL LITERALs */
--AND org_id = NVL(pr_old.ORG_ID,0)
)
AND ORG_ID = pr_old.ORG_ID ; /* Modified by Ramananda for removal of SQL LITERALs */
select 1
from JAI_OM_OE_SO_LINES
where line_id = to_number(pr_old.interface_line_attribute6);
select 1
from JAI_OM_OE_RMA_LINES
where rma_line_id = to_number(pr_old.interface_line_attribute6);
1 Sriram - Bug # 2590650 - Added delete statements to delete from the
JAI_AR_TRX_INS_LINES_T for customer trx id and trx line id and also the
JAI_AR_TRXS table
2 Sriram - Bug # 2618503
If the line being deleted corresponds to a return order or a sales order then
delete the lines related to localization ar tables.
3. 29-nov-2004 ssumaith - bug# 4037690 - File version 115.1
Check whether india localization is being used was done using a INR check in every trigger.
This check has now been moved into a new package and calls made to this package from this trigger
If the function jai_cmn_utils_pkg.check_jai_exists returns true it means INR is the set of books currency ,
Hence if this function returns FALSE , control should return
4. 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
5. 13-Jun-2005 File Version: 116.2
Ramananda for bug#4428980. Removal of SQL LITERALs is done
6. 16-Jan-06 rallamse Bug#4926736 , Version # 120.2
Removed cursor location_cur in ARI_T2 as it is not being used
7. 17-feb-2007 sacsethi for bug#5228046 , version 120.8
for tax precedence
8. 27/02/2007 bduvarag for the bug#4694650 File version 120.9
Forward porting the changes done in 11i bug#4644152
9. 18/04/2007 bduvarag for the Bug#4881426, file version 120.10
Forward porting the changes done in 11i bug#4862976
10. 20/04/2007 bduvarag for the Bug#5684363, file version 120.10
Forward porting the changes done in 11i bug#5682531
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_ar_lines_delete_trg.sql
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
115.1 4037690 IN60105D2 ja_in_util_pkg_s.sql 115.0 ssumaith 29-Nov-2004 Call to this function.
ja_in_util_pkg_s.sql 115.0 ssumaith
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
BEGIN
pv_return_code := jai_constants.successful ;
UPDATE JAI_AR_TRXS
SET line_amount = nvl(line_amount,0) - nvl(v_old_line_amount,0),
tax_amount = nvl(tax_amount,0) - nvl(v_old_tax_tot,0),
total_amount = nvl(total_amount,0) - (nvl(v_old_line_amount,0) + nvl(v_old_tax_tot,0))
WHERE customer_trx_id = v_customer_trx_id;
DELETE FROM
JAI_AR_TRXS trx
WHERE customer_trx_id = v_customer_trx_id
AND EXISTS
(SELECT 1
FROM ra_interface_lines_all il
WHERE il.customer_trx_id = v_customer_trx_id
AND NVL(il.interface_status , '~') <> 'P'
);
DELETE JAI_AR_TRX_TAX_LINES
WHERE LINK_TO_CUST_TRX_LINE_ID = v_customer_trx_line_id;
DELETE JAI_AR_TRX_INS_LINES_T
WHERE CUSTOMER_TRX_ID = v_customer_trx_id
AND
LINK_TO_CUST_TRX_LINE_ID = v_customer_trx_line_id;
DELETE JAI_AR_TRX_LINES
WHERE CUSTOMER_TRX_ID = v_customer_trx_id AND
CUSTOMER_TRX_LINE_ID = v_customer_trx_line_id;
v_last_update_date Date; -- := pr_new.last_update_date;
v_last_updated_by Number; -- := pr_new.last_updated_by;
v_last_update_login Number; -- := pr_new.last_update_login;
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_header_id
And NVL(a.org_id,0) = NVL(pr_new.org_id,0);
SELECT DISTINCT gl_date
FROM RA_CUST_TRX_LINE_GL_DIST_ALL
WHERE CUSTOMER_TRX_LINE_ID = v_prev_customer_trx_line_id;
Select assessable_value, tax_category_id, service_type_code --service_type_code added by csahoo for bug#5879769
From JAI_AR_TRX_LINES
Where customer_trx_line_id = v_prev_customer_trx_line_id;
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,
a.tax_rate, a.tax_amount, a.uom uom_code, a.qty_rate,
decode(upper(b.tax_type),'EXCISE', 1, 'ADDL. EXCISE', 1, 'OTHER EXCISE', 1, 'CVD',1, 'TDS', 2, 0) tax_type_val,
b.tax_type
FROM JAI_AR_TRX_TAX_LINES a, JAI_CMN_TAXES_ALL b
WHERE a.customer_trx_line_id = v_prev_customer_trx_line_id
AND a.tax_id = b.tax_id;
SELECT created_from, set_of_books_id, invoice_currency_code, exchange_rate_type, nvl(exchange_date,trx_date), exchange_rate
FROM RA_CUSTOMER_TRX_ALL
WHERE customer_trx_id = v_header_id;
SELECT organization_id
FROM JAI_AR_TRXS
WHERE customer_trx_id = v_header_id;
FILENAME: JA_IN_AR_CM_LINES_INSERT_TRG.sql
CHANGE HISTORY:
S.No Date Author and Details
1. 2001/07/14 Anuradha Parthasarathy
Check added to ensure non firing of trigger for Non Indian OU.
2. 2004/10/18 ssumaith - bug# 3957682- File version 115.1
when a manual credit memo is created , taxes are not getting defaulted.
The reason this was happening is because this trigger has code to copy the
taxes from the invoice against which the credit memo is applied.
Added code to return control when a manual credit memo without reference is created.
Manual Credit memo with reference to invoice has ra_customer_trx_all.created_from as ARXTWCMI
Manual Credit memo without reference to invoice has ra_customer_trx_all.created_from as ARXTWMAI
3. 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
4. 10-Jun-2005 File Version: 116.2
Removal of SQL LITERALs is done
--------------------------------------------------------------------------------------------*/
/* --Ramananda for File.Sql.35, start */
v_header_id := pr_new.customer_trx_id;
v_last_update_date := pr_new.last_update_date;
v_last_updated_by := pr_new.last_updated_by;
v_last_update_login := pr_new.last_update_login;
place from the trigger ja_in_ar_lines_insert_trg.
*/
if v_created_from = 'ARXTWMAI' and v_trans_type = 'CM' then
return;
INSERT INTO JAI_AR_TRX_LINES
(customer_trx_line_id,
line_number,
customer_trx_id,
description,
inventory_item_id,
unit_code,
quantity,
tax_category_id,
auto_invoice_flag ,
unit_selling_price,
line_amount,
gl_date,
tax_amount,
total_amount,
assessable_value,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
service_type_code -- added by csahoo for bug#5879769
)
VALUES(
v_customer_trx_line_id,
pr_new.line_number,
v_header_id,
pr_new.description,
v_inventory_item_id,
pr_new.uom_code,
NVL(NVL(pr_new.quantity_credited,pr_new.quantity_invoiced) ,0)
, v_tax_category_id,
'N',
pr_new.unit_selling_price,
v_line_amount,
v_gl_date,
0,
v_line_amount,
v_price_list,
v_creation_date,
v_created_by,
v_last_update_date,
v_last_updated_by,
v_last_update_login,
lv_service_type_code -- added by csahoo for bug#5879769
);
Update JAI_AR_TRXS
Set line_amount = nvl(line_amount,0) + nvl(v_line_amount,0)
Where Customer_Trx_Id = v_header_id;
INSERT INTO JAI_AR_TRX_TAX_LINES
(customer_trx_line_id,
link_to_cust_trx_line_id,
tax_line_no,
precedence_1,
precedence_2,
precedence_3,
precedence_4,
precedence_5,
precedence_6,
precedence_7,
precedence_8,
precedence_9,
precedence_10,
tax_id,
tax_rate,
qty_rate,
uom,
tax_amount,
base_tax_amount,
func_tax_amount,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
VALUES
(v_customer_trx_line_id,
v_link_to_cust_id,
rec.lno,
rec.p_1,
rec.p_2,
rec.p_3,
rec.p_4,
rec.p_5,
rec.p_6,
rec.p_7,
rec.p_8,
rec.p_9,
rec.p_10,
rec.tax_id,
rec.tax_rate,
rec.qty_rate,
rec.uom_code,
pr_new.extended_amount,
pr_new.extended_amount,
pr_new.extended_amount * v_converted_rate,
v_creation_date,
v_created_by,
v_last_update_date,
v_last_updated_by,
v_last_update_login
);
Update JAI_AR_TRXS
Set total_amount = nvl(total_amount,0) + nvl(pr_new.extended_amount,0),
tax_amount = nvl(tax_amount,0) + nvl(pr_new.extended_amount,0)
Where Customer_Trx_Id = v_header_id;
Update JAI_AR_TRX_LINES
Set total_amount = nvl(total_amount,0) + nvl(pr_new.extended_amount,0),
tax_amount = nvl(tax_amount,0) + nvl(pr_new.extended_amount,0)
Where Customer_Trx_Id = v_header_id
and Customer_Trx_Line_Id = v_link_to_cust_id;
v_last_update_date DATE; -- := pr_new.last_update_date;
v_last_updated_by NUMBER; -- := pr_new.last_updated_by;
v_last_update_login NUMBER; -- := pr_new.last_update_login;
SELECT bill_to_customer_id,
bill_to_site_use_id,
trx_number,
batch_source_id
FROM RA_CUSTOMER_TRX_ALL
WHERE customer_trx_id = pr_new.customer_trx_id;
SELECT customer_site
FROM JAI_AR_SUP_HDRS_ALL
WHERE supplementary_num = TO_NUMBER(p_trx_number);
SELECT exchange_rate
FROM ra_customer_trx_all
WHERE customer_trx_id = pr_new.customer_trx_id;
SELECT created_from, trx_number, batch_source_id, set_of_books_id, primary_salesrep_id,
invoice_currency_code, exchange_rate_type, exchange_date, exchange_rate
FROM ra_customer_trx_all
WHERE customer_trx_id = v_header_id;
SELECT A.organization_id, A.location_id
FROM JAI_OM_WSH_LINES_ALL A,WSH_NEW_DELIVERIES B
WHERE A.delivery_id = B.DELIVERY_ID AND
B.NAME = v_interface_line_attribute3 AND
A.order_line_id = TO_NUMBER(v_interface_line_attribute6);
SELECT
1
FROM
oe_order_lines_all
WHERE
item_type_code IN ('CONFIG', 'MODEL', 'OPTION', 'CLASS')
AND line_id = (SELECT ato_line_id
FROM oe_order_lines_all
WHERE line_id = v_interface_line_attribute6 );
SELECT
organization_id, location_id
FROM
JAI_OM_WSH_LINES_ALL
WHERE
order_line_id IN (SELECT line_id
FROM oe_order_lines_all oel2
WHERE oel2.item_type_code = 'CONFIG'
AND oel2.header_id = (
SELECT header_id
FROM oe_order_lines_all oel
WHERE oel.line_id = v_interface_line_attribute6)
AND oel2.ato_line_id = (SELECT ato_line_id
FROM oe_order_lines_all oel1
WHERE oel1.line_id = v_interface_line_attribute6))
AND organization_id is not null
AND location_id is not null
AND rownum = 1 ;
SELECT organization_id,
Location_id
FROM JAI_AR_TRXS
WHERE customer_trx_id = TO_NUMBER(pr_new.interface_line_attribute4); -- Replaced pr_new.interface_line_attribute1 , Bug 4392001
SELECT A.organization_id,
A.Location_id
FROM JAI_AR_TRXS A,
JAI_AR_SUP_HDRS_ALL B,
RA_CUSTOMER_TRX_ALL C
WHERE pr_new.customer_trx_id = c.customer_trx_id
AND c.trx_number = TO_CHAR(B.SUPPLEMENTARY_NUM)
AND B.customer_trx_id = A.customer_trx_id;
SELECT tax_category_id,Quantity , -- Quantity Added By Sriram Bug # .. Base Bug 2335923
(tax_amount/quantity) tax_amount, --Added by Jagdish 30-Aug-01
assessable_value, (basic_excise_duty_amount/quantity) basic_excise_duty_amount,
(add_excise_duty_amount/quantity) add_excise_duty_amount,
(oth_excise_duty_amount/quantity) oth_excise_duty_amount,
register, excise_invoice_no,
preprinted_excise_inv_no, excise_invoice_date,
excise_exempt_type, excise_exempt_refno, excise_exempt_date
, ar3_form_no, ar3_form_date, -- Vijay Shankar for Bug # 3181892
vat_exemption_flag, vat_exemption_type, vat_exemption_date, vat_exemption_refno, vat_assessable_value, vat_invoice_no, vat_invoice_date -- added, Harshita for bug#4245062
FROM JAI_OM_WSH_LINES_ALL a,wsh_new_deliveries b
WHERE A.delivery_id = b.delivery_id AND
B.NAME = v_interface_line_attribute3 AND
A.order_line_id = TO_NUMBER(v_interface_line_attribute6);
SELECT DISTINCT A.tax_category_id,
(NVL(A.excise_diff_amt,0)+NVL(A.other_diff_amt,0)) TAX_AMT,
NVL(A.excise_diff_amt,0) excise_diff,
A.new_assessable_value
FROM JAI_AR_SUP_LINES A,
ra_customer_trx_all b,
ra_cust_trx_types_all c
WHERE B.CUST_TRX_TYPE_ID = C.CUST_TRX_TYPE_ID
AND A.SUP_INV_TYPE = DECODE(C.TYPE,'DM','DB','CM','CR','INV','SI')
AND A.customer_trx_line_id = TO_NUMBER(pr_new.interface_line_attribute3)
AND pr_new.customer_trx_id = b.customer_trx_id;
SELECT SUM(NVL(A.excise_diff_amt,0))+SUM(NVL(A.other_diff_amt,0)) TAX_AMT,
NVL(A.excise_diff_amt,0) excise_diff,
A.new_assessable_value
FROM JAI_AR_SUP_LINES A,
JAI_AR_SUP_HDRS_ALL b,
ra_customer_trx_all c,
ra_cust_trx_types_all e
WHERE pr_new.customer_trx_id = c.customer_trx_id
AND c.trx_number = TO_CHAR( b.SUPPLEMENTARY_NUM)
AND B.customer_trx_id = A.customer_trx_id
AND C.cust_trx_type_id = e.cust_trx_type_id
AND b.supp_inv_type = DECODE(e.TYPE,'DM','DB','CM','CR','INV','SI')
AND A.description = pr_new.description
AND A.sup_inv_type = b.supp_inv_type
GROUP BY A.inventory_item_id ,
A.new_assessable_value,
b.supp_inv_type;
SELECT A.tax_category_id
FROM JAI_AR_SUP_LINES A,
JAI_AR_SUP_HDRS_ALL b,
ra_customer_trx_all c,
ra_cust_trx_types_all D
WHERE pr_new.customer_trx_id = c.customer_trx_id
AND c.trx_number =TO_CHAR( b.SUPPLEMENTARY_NUM)
AND B.customer_trx_id = A.customer_trx_id
AND A.sup_inv_type = b.supp_inv_type
AND C.cust_trx_type_id = D.cust_trx_type_id
AND b.supp_inv_type = DECODE(D.TYPE,'DM','DB','CM','CR','INV','SI');
SELECT b.quantity , --Added by Jagdish 30-Aug-01
A.tax_line_no,
A.uom,
A.tax_id,
A.tax_rate,
A.qty_rate,
A.base_tax_amount,
A.tax_amount,
c.tax_type,
A.func_tax_amount,
A.precedence_1, A.precedence_2, A.precedence_3, A.precedence_4, A.precedence_5,
A.precedence_6, A.precedence_7, A.precedence_8, A.precedence_9, A.precedence_10,
b.vat_invoice_no,b.vat_invoice_date
FROM JAI_OM_WSH_LINE_TAXES A,JAI_OM_WSH_LINES_ALL b,
JAI_CMN_TAXES_ALL c, wsh_new_deliveries D
WHERE A.delivery_detail_id = b.delivery_detail_id
AND A.tax_id=c.tax_id
and c.tax_type <> 'Modvat Recovery'/*Bug 4881426 bduvarag*/
AND b.delivery_id = D.delivery_id
AND D.name = v_interface_line_attribute3
AND b.order_line_id = TO_NUMBER(v_interface_line_attribute6); --Added on 17-Apr-2002
SELECT SUM(A.base_tax_amount) base_tax_amount,
SUM(A.tax_amount) tax_amount,
SUM(A.func_tax_amount) func_tax_amount
FROM JAI_OM_WSH_LINE_TAXES A,JAI_OM_WSH_LINES_ALL b,WSH_NEW_DELIVERIES C
WHERE A.delivery_detail_id = b.delivery_detail_id
AND b.delivery_id = c.delivery_id
AND c.NAME = v_interface_line_attribute3
AND b.order_line_id = TO_NUMBER(v_interface_line_attribute6)
AND A.tax_id = p_tax_id
GROUP BY A.tax_id; --17-Apr-2002
SELECT DISTINCT A.tax_line_no,
A.new_uom,
A.new_tax_id,
A.new_rate,
A.new_qty_rate,
(NVL(A.new_base_tax_amt,0) - NVL(A.old_base_tax_amt,0)) BASE_TAX_AMT,
A.diff_amt,
A.diff_amt FUNC_TAX_AMT,
t.tax_type,
t.stform_type
FROM JAI_AR_SUP_TAXES A,
JAI_AR_SUP_LINES b,
ra_customer_trx_all C,
ra_cust_trx_types_all D,
JAI_CMN_TAXES_ALL t
WHERE A.link_to_cust_trx_line_id = b.customer_trx_line_id
AND A.sup_inv_type = b.sup_inv_type
AND b.customer_Trx_line_id = TO_NUMBER(pr_new.interface_line_attribute3)
AND pr_new.customer_trx_id = c.customer_trx_id
AND c.cust_trx_type_id = D.cust_trx_type_id
AND b.sup_inv_type = DECODE(D.TYPE,'DM','DB','CM','CR','INV','SI')
AND A.new_tax_id = t.tax_id
ORDER BY A.tax_line_no;
SELECT A.new_uom,
A.new_tax_id,
A.new_rate,
A.new_qty_rate,
SUM(NVL(A.new_base_Tax_amt,0) - NVL(A.old_base_tax_amt,0)) BASE_TAX_AMT,
SUM(A.diff_amt) DIFF_AMT,
SUM(A.diff_amt) FUNC_TAX_AMT,
t.tax_type,
t.stform_type
FROM JAI_AR_SUP_TAXES A,
JAI_AR_SUP_LINES b,
JAI_AR_SUP_HDRS_ALL c,
ra_customer_trx_all D,
ra_cust_trx_types_all e,
JAI_CMN_TAXES_ALL t
WHERE pr_new.customer_trx_id = D.customer_trx_id
AND D.trx_number = TO_CHAR(c.SUPPLEMENTARY_NUM)
AND c.customer_trx_id = b.customer_trx_id
AND b.customer_trx_line_id = A.link_to_cust_trx_line_id
AND b.sup_inv_type = A.sup_inv_type
AND b.description = pr_new.description
AND c.supp_inv_type = b.sup_inv_type
AND e.cust_trx_type_id = D.cust_trx_type_id
AND c.supp_inv_type = DECODE(e.TYPE,'DM','DB','CM','CR','INV','SI')
and t.tax_type <> lc_modvat_tax/*Bug 4881426 bduvarag*/
AND A.new_tax_id = t.tax_id
GROUP BY b.inventory_item_id,
A.new_tax_id,
A.new_uom,
A.new_qty_rate,
A.new_rate,
t.tax_type,
t.stform_type ;
SELECT 1
FROM JAI_AR_TRXS
WHERE customer_trx_id = v_header_id;
SELECT once_completed_flag
FROM JAI_AR_TRXS
WHERE customer_trx_id = v_header_id;
SELECT 1
FROM JAI_OM_WSH_LINES_ALL A,WSH_NEW_DELIVERIES B
WHERE A.delivery_id = B.DELIVERY_ID AND
B.NAME = v_interface_line_attribute3
AND A.order_line_id = TO_NUMBER(v_interface_line_attribute6); --17-Apr-2002
SELECT 1
FROM JAI_AR_SUP_LINES
WHERE customer_trx_line_id = TO_NUMBER(pr_new.interface_line_attribute3);
SELECT 1
FROM JAI_AR_SUP_LINES A,
JAI_AR_SUP_HDRS_ALL B,
RA_CUSTOMER_TRX_ALL C,
RA_CUST_TRX_TYPES_ALL D
WHERE pr_new.CUSTOMER_TRX_ID = C.customer_trx_id
AND c.cust_trx_type_id = D.cust_trx_type_id
AND c.trx_number = TO_CHAR(b.SUPPLEMENTARY_NUM)
AND b.supp_inv_type = DECODE(D.TYPE,'INV','SI','CM','CR','DM','DB')
AND b.supp_inv_type = A.sup_inv_type
AND A.customer_trx_id = b.customer_trx_id;
SELECT DISTINCT 1
FROM JAI_OM_WSH_LINE_TAXES A, JAI_OM_WSH_LINES_ALL b, WSH_NEW_DELIVERIES C
WHERE A.delivery_detail_id = b.delivery_detail_id
AND b.delivery_id = C.DELIVERY_ID AND
c.NAME = v_interface_line_attribute3
AND b.order_line_id = TO_NUMBER(v_interface_line_attribute6); --17-Apr-2002
SELECT 1
FROM JAI_AR_SUP_TAXES A, JAI_AR_SUP_LINES b
WHERE A.link_to_cust_trx_line_id = b.customer_trx_line_id
AND b.customer_trx_line_id = v_customer_Trx_line_id;
SELECT 1
FROM JAI_AR_SUP_LINES A,
JAI_AR_SUP_HDRS_ALL B,
RA_CUSTOMER_TRX_ALL C,
RA_CUST_TRX_TYPES_ALL D,
JAI_AR_SUP_TAXES E
WHERE pr_new.CUSTOMER_TRX_ID = C.customer_trx_id
AND c.cust_trx_type_id = D.cust_trx_type_id
AND c.trx_number = TO_CHAR(b.SUPPLEMENTARY_NUM)
AND b.supp_inv_type = DECODE(D.TYPE,'INV','SI','CM','CR','DM','DB')
AND b.supp_inv_type = A.sup_inv_type
AND A.customer_trx_id = b.customer_trx_id
AND E.link_to_cust_trx_line_id = A.customer_trx_line_id
AND A.sup_inv_type = e.sup_inv_type;
SELECT complete_flag
FROM ra_customer_trx_all
WHERE customer_trx_id = v_header_id;
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_header_id
AND NVL(A.org_id,0) = NVL(pr_new.org_id,0);
SELECT item_type_code, serviced_quantity, return_reference_id, original_system_line_reference, customer_product_id,
warehouse_id, header_id
FROM So_Lines_All
WHERE line_id = TO_NUMBER(v_interface_line_attribute6);
SELECT 1
FROM JAI_OM_OE_SO_LINES
WHERE line_id = TO_NUMBER(v_interface_line_attribute6);
SELECT DISTINCT 1
FROM JAI_OM_OE_SO_TAXES A, JAI_OM_OE_SO_LINES b
WHERE A.line_id = TO_NUMBER(v_interface_line_attribute6)
AND A.line_id = b.line_id;
SELECT A.tax_line_no, A.uom, A.tax_id, A.tax_rate, A.qty_rate, A.base_tax_amount,A.tax_amount,
A.func_tax_amount,
A.precedence_1, A.precedence_2, A.precedence_3, A.precedence_4, A.precedence_5,
A.precedence_6, A.precedence_7, A.precedence_8, A.precedence_9, A.precedence_10,
c.tax_type
FROM JAI_OM_OE_SO_TAXES A, JAI_OM_OE_SO_LINES b, JAI_CMN_TAXES_ALL c
WHERE A.line_id = b.line_id
AND b.line_id = TO_NUMBER(v_interface_line_attribute6)
AND A.tax_id = c.tax_id
and c.tax_type <> lc_modvat_tax/*Bug 4881426 bduvarag*/
ORDER BY A.tax_line_no;
SELECT tax_category_id, tax_amount, assessable_value, excise_exempt_type,excise_exempt_refno, excise_exempt_date,
vat_exemption_flag, vat_exemption_type, vat_exemption_date, vat_exemption_refno, vat_assessable_value -- added, Harshita for bug#4245062
FROM JAI_OM_OE_SO_LINES
WHERE line_id = to_number(v_interface_line_attribute6);
SELECT assessable_value, service_type_code -- service_type_code added by csahoo for bug#5879769
from JAI_OM_OE_SO_LINES
WHERE line_id = TO_NUMBER(v_interface_line_attribute6);
SELECT 1
FROM JAI_AR_TRX_LINES
WHERE customer_trx_LINE_id = v_customer_trx_line_id;
SELECT Customer_Trx_Id
FROM JAI_AR_TRX_LINES
WHERE customer_trx_LINE_id = v_customer_trx_line_id;
SELECT 1
FROM JAI_AR_TRX_TAX_LINES
WHERE link_to_cust_trx_line_id = v_customer_trx_line_id
AND tax_id = v_tax_id;
SELECT A.organization_id,
A.location_id,
A.order_type_id
FROM JAI_OM_WSH_LINES_ALL A,WSH_NEW_DELIVERIES B
WHERE A.delivery_id = B.DELIVERY_ID AND
B.NAME = v_interface_line_attribute3
AND A.order_line_id = TO_NUMBER(v_interface_line_attribute6); --17-Apr-2002
SELECT register_code
FROM JAI_OM_OE_BOND_REG_HDRS
WHERE organization_id = p_organization_id
AND location_id = p_location_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 = 'Y');
SELECT pref_rg23a, pref_rg23c, pref_pla
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_organization_id
AND location_id = p_location_id ;
SELECT MAX(A.fin_year)
FROM JAI_CMN_FIN_YEARS A
WHERE organization_id = p_org_id AND fin_active_flag = 'Y';
SELECT NVL(rg23a_balance,0) rg23a_balance ,NVL(rg23c_balance,0) rg23c_balance,NVL(pla_balance,0) pla_balance
FROM JAI_CMN_RG_BALANCES
WHERE organization_id = p_organization_id
AND location_id = p_location_id ;
SELECT ssi_unit_flag
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_organization_id AND
location_id = p_location_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 meaning
FROM So_lookups
WHERE lookup_code = p_register_code
AND lookup_type = cp_lookup_type; /* 'REGISTER_TYPE'; Ramananda for removal of SQL LITERALs */
SELECT name
FROM Ra_Batch_Sources_All
WHERE batch_source_id = p_batch_source_id
AND NVL(org_id,0) = NVL(pr_new.org_id,0);
SELECT start_number, end_number, jump_by, prefix
FROM JAI_CMN_RG_EXC_INV_NOS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = p_fin_year
AND transaction_type = 'I'
AND order_invoice_type = p_batch_name
AND register_code = p_register_code ;
SELECT A.Organization_Id, A.Location_Id
FROM JAI_CMN_INVENTORY_ORGS A
WHERE A.Ec_Code IN (SELECT B.Ec_Code
FROM JAI_CMN_INVENTORY_ORGS B
WHERE B.Organization_Id = p_organization_id
AND B.Location_Id = p_location_id);
SELECT SHIP_TO_CUSTOMER_ID,SHIP_TO_SITE_USE_ID
FROM ra_customer_trx_all
WHERE customer_trx_id = pr_new.customer_trx_id
AND org_id = pr_new.org_id;
gets inserted even if where clause fails to retrieve a record.
*/
-- bug # 3000550 sriram
cursor c_cust_trx_tax_line_amt is
select nvl(sum(tax_amount),0)
from JAI_AR_TRX_TAX_LINES
where link_to_cust_trx_line_id = v_customer_trx_line_id;
select FND_PROFILE.VALUE('ONT_SOURCE_CODE')
from dual;
select 1
from oe_wf_order_assign_v o_wf_asg
where order_type_name = pr_new.interface_line_attribute2
and exists
(
select 1
from oe_wf_line_assign_v l_wf_asg
where assignment_id = o_wf_asg.assignment_id
and process_name = cp_process_name /*'R_BILL_ONLY' Ramananda for removal of SQL LITERALs */
and order_type_id = l_wf_asg.order_type_id
)
;
SELECT legal_entity_id
FROM RA_CUSTOMER_TRX_ALL
WHERE customer_trx_id = pr_new.customer_trx_id ;
select item_type_code
from oe_order_lines_all
where line_id = v_interface_line_attribute6;
select *
from JAI_OM_OE_SO_TAXES
where line_id = v_so_config_line_id;
Select line_id
from oe_order_lines_all
where ato_line_id = v_ato_line_id
and item_type_code = cp_item_code; /*'CONFIG'; Ramananda for removal of SQL LITERALs */
SELECT *
FROM JAI_OM_OE_SO_LINES
WHERE line_id = v_ato_line_id;
SELECT excise_invoice_no , register , preprinted_excise_inv_no , ar3_form_no ,ar3_form_date
FROM JAI_OM_WSH_LINES_ALL
WHERE order_line_id = v_ato_line_id;
SELECT a.organization_id, a.location_id
FROM JAI_OM_WSH_LINES_ALL a,wsh_new_deliveries b
WHERE a.delivery_id = b.delivery_id and
B.NAME = v_interface_line_attribute3 AND
A.order_line_id = TO_NUMBER(v_ato_line_id);
SELECT tax_type
FROM JAI_CMN_TAXES_ALL
WHERE tax_id = p_tax_id;
INSERT INTO JAI_AR_TRX_TAX_LINES
(
tax_line_no ,
customer_trx_line_id ,
link_to_cust_trx_line_id ,
precedence_1 ,
precedence_2 ,
precedence_3 ,
precedence_4 ,
precedence_5 ,
precedence_6 ,
precedence_7 ,
precedence_8 ,
precedence_9 ,
precedence_10 ,
tax_id ,
tax_rate ,
qty_rate ,
uom ,
tax_amount ,
func_tax_amount ,
base_tax_amount ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
)
VALUES
(so_taxes_rec.tax_line_no,
ra_customer_trx_lines_s.nextval,
v_customer_trx_line_id,
so_taxes_rec.precedence_1,
so_taxes_rec.precedence_2,
so_taxes_rec.precedence_3,
so_taxes_rec.precedence_4,
so_taxes_rec.precedence_5,
so_taxes_rec.precedence_6,
so_taxes_rec.precedence_7,
so_taxes_rec.precedence_8,
so_taxes_rec.precedence_9,
so_taxes_rec.precedence_10,
so_taxes_rec.tax_id,
so_taxes_rec.tax_rate,
so_taxes_rec.qty_rate,
so_taxes_rec.uom,
so_taxes_rec.tax_amount,
so_taxes_rec.base_tax_amount,
so_taxes_rec.func_tax_amount,
sysdate,
so_taxes_rec.created_by,
sysdate,
so_taxes_rec.last_updated_by,
so_taxes_rec.last_update_login
);
FND_FILE.PUT_LINE(FND_FILE.LOG, ' After insert into JAI_AR_TRX_TAX_LINES');
INSERT INTO JAI_AR_TRX_TAX_LINES
(
TAX_LINE_NO ,
CUSTOMER_TRX_LINE_ID ,
LINK_TO_CUST_TRX_LINE_ID ,
PRECEDENCE_1 ,
PRECEDENCE_2 ,
PRECEDENCE_3 ,
PRECEDENCE_4 ,
PRECEDENCE_5 ,
PRECEDENCE_6 ,
PRECEDENCE_7 ,
PRECEDENCE_8 ,
PRECEDENCE_9 ,
PRECEDENCE_10 ,
TAX_ID ,
TAX_RATE ,
QTY_RATE ,
UOM ,
TAX_AMOUNT ,
FUNC_TAX_AMOUNT ,
BASE_TAX_AMOUNT ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
values
(so_taxes_rec.tax_line_no,
ra_customer_trx_lines_s.nextval,
v_customer_trx_line_id,
so_taxes_rec.precedence_1,
so_taxes_rec.precedence_2,
so_taxes_rec.precedence_3,
so_taxes_rec.precedence_4,
so_taxes_rec.precedence_5,
so_taxes_rec.precedence_6,
so_taxes_rec.precedence_7,
so_taxes_rec.precedence_8,
so_taxes_rec.precedence_9,
so_taxes_rec.precedence_10,
so_taxes_rec.tax_id,
so_taxes_rec.tax_rate,
so_taxes_rec.qty_rate,
so_taxes_rec.uom,
so_taxes_rec.tax_amount,
so_taxes_rec.base_tax_amount,
so_taxes_rec.func_tax_amount,
sysdate,
so_taxes_rec.created_by,
sysdate,
so_taxes_rec.last_updated_by,
so_taxes_rec.last_update_login
);
' Else - After insert into JAI_AR_TRX_TAX_LINES');
Insert into JAI_AR_TRX_LINES
(
customer_trx_line_id ,
customer_trx_id ,
line_number ,
inventory_item_id ,
description ,
unit_code ,
quantity ,
unit_selling_price ,
tax_category_id ,
line_amount ,
tax_amount ,
total_amount ,
auto_invoice_flag ,
assessable_value ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login ,
excise_exempt_type ,
excise_exempt_refno ,
excise_exempt_date ,
excise_invoice_no ,
payment_register ,
preprinted_excise_inv_no ,
ar3_form_no ,
ar3_form_date ,
vat_exemption_flag , -- added, harshita for bug#4245062
vat_exemption_type ,
vat_exemption_date ,
vat_exemption_refno ,
vat_assessable_value ,
service_type_code --Added by csahoo for Bug#5879769
)
Values
(
v_customer_trx_line_id,
v_header_id,
pr_new.line_number,
pr_new.inventory_item_id,
pr_new.description,
so_line_rec.unit_code,
so_line_rec.quantity,
so_line_rec.selling_price,
so_line_rec.tax_category_id,
so_line_rec.line_amount,
v_tax_amount,
so_line_rec.line_amount + v_tax_amount,
'Y',
so_line_rec.assessable_value,
sysdate,
so_line_rec.created_by,
sysdate,
so_line_rec.last_updated_by,
so_line_rec.last_update_login,
so_line_rec.excise_exempt_type,
so_line_rec.excise_exempt_refno,
so_line_rec.excise_exempt_date,
v_ex_inv_no ,
v_pmt_reg ,
v_pre_prnt_ex_no,
v_ar3_form_no ,
v_ar3_form_date,
so_line_rec.vat_exemption_flag, -- added, Harshita for bug#4245062
so_line_rec.vat_exemption_type,
so_line_rec.vat_exemption_date,
so_line_rec.vat_exemption_refno,
so_line_rec.vat_assessable_value,
v_service_type --Added by csahoo for Bug#5879769
);
' Inserted jai_ar_trx_lines for TRX LINE ID: '||v_customer_trx_line_id);
Insert into JAI_AR_TRXS
(
CUSTOMER_TRX_ID ,
ORGANIZATION_ID ,
LOCATION_ID ,
UPDATE_RG_FLAG ,
ONCE_COMPLETED_FLAG ,
TOTAL_AMOUNT ,
LINE_AMOUNT ,
TAX_AMOUNT ,
TRX_NUMBER ,
BATCH_SOURCE_ID ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
SET_OF_BOOKS_ID ,
PRIMARY_SALESREP_ID ,
INVOICE_CURRENCY_CODE ,
EXCHANGE_RATE_TYPE ,
EXCHANGE_DATE ,
EXCHANGE_RATE ,
CREATED_FROM ,
UPDATE_RG23D_FLAG ,
LEGAL_ENTITY_ID /* rallamse bug#4448789 */
)
Values
(
v_header_id,
v_organization_id,
v_location_id,
'Y',
'N',/*Bug 4694650 bduvarag*/
v_ato_total_amount,
v_ato_line_amount,
v_ato_tax_amount,
v_trx_number,
v_batch_source_id,
sysdate,
uid,
sysdate,
uid,
uid,
v_books_id,
v_salesrep_id,
c_from_currency_code,
c_conversion_type,
c_conversion_date,
c_conversion_rate,
v_created_from,
'Y',
ln_legal_entity_id /* rallamse bug#4448789 */
);
FND_FILE.PUT_LINE(FND_FILE.LOG,'After insert into JAI_AR_TRXS ');
select 1
from JAI_AR_TRXS
where customer_trx_id = pr_new.customer_trx_id;
select trx_number ,
batch_source_id ,
set_of_books_id ,
primary_salesrep_id ,
invoice_currency_Code ,
exchange_rate_type ,
exchange_date ,
exchange_rate ,
created_from ,
nvl(bill_to_customer_id,ship_to_customer_id) customer_id , -- Date 26-feb-2006 added by sacsethi for bug 5631784
trx_date
from ra_customer_trx_all
where customer_trx_id = pr_new.customer_trx_id;
SELECT tax_category_id ,
tax_amount ,
assessable_value ,
line_amount ,
excise_exempt_type ,
excise_exempt_refno ,
excise_exempt_date ,
vat_exemption_flag , -- added, Harshita for bug#4245062
vat_exemption_type ,
vat_exemption_date ,
vat_exemption_refno ,
vat_assessable_value,
unit_code , -- Date 26-feb-2006 added by sacsethi for bug 5631784
inventory_item_id , -- Date 26-feb-2006 added by sacsethi for bug 5631784
quantity , -- Date 26-feb-2006 added by sacsethi for bug 5631784
service_type_code -- Added by csahoo, Bug 5879769
FROM JAI_OM_OE_SO_LINES
WHERE line_id = TO_NUMBER(v_interface_line_attribute6);
select sum(tax_amount) tax_amt , sum(line_amount) line_amt
from JAI_AR_TRX_LINES
where customer_trx_id = pr_new.customer_trx_id;
select DEFAULT_LOCATION_BILL_ONLY
from JAI_CMN_INVENTORY_ORGS
where organization_id = cp_organization_id
and location_id = 0;
select master_organization_id
from oe_system_parameters ;
select count(1)
from jai_regime_tax_types_v jrttv
, JAI_AR_TRX_TAX_LINES jrctt
, JAI_CMN_TAXES_ALL jtc
where jtc.tax_id = jrctt.tax_id
and jtc.tax_type = jrttv.tax_type
and regime_code = cp_regime_code
and jrctt.link_to_cust_trx_line_id = cp_cust_trx_line_id;
select regime_id
from JAI_RGM_DEFINITIONS
where regime_code = cp_regime_code;
select sum(tax_amount)
from JAI_AR_TRX_TAX_LINES
Where link_to_cust_trx_line_id = v_customer_trx_line_id;
c) only one record should be inserted in the JAI_AR_TRXS table.
*/
ln_hdr_exists := 0;
hence insert a record into the table.
*/
open c_trx_cur;
We could still update the organization and location id in the JAI_AR_TRXS table later on (as a datafix)
*/
lv_appl_src := 'JA_IN_OE_AR_LINES_INSERT_TRG' ;
insert into JAI_CMN_ERRORS_T
(
APPLICATION_SOURCE ,
ERROR_MESSAGE ,
ADDITIONAL_ERROR_MESG ,
CREATION_DATE ,
CREATED_BY ,
-- added, Harshita for Bug 4866533
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
values
(
lv_appl_src, /*'JA_IN_OE_AR_LINES_INSERT_TRG', Ramananda for removal of SQL LITERALs */
lv_err_msg, /* 'Default Location is not setup for Inventory Organization ' || pr_new.warehouse_id , */
lv_addl_msg, /* 'Please setup the Default Location in Organization Additional Information Screen for Trx id : ' || pr_new.customer_trx_id , */
sysdate,
fnd_global.user_id ,
-- added, Harshita for Bug 4866533
fnd_global.user_id,
sysdate
);
insert into JAI_AR_TRXS -- bill only invoice
(
CUSTOMER_TRX_ID ,
ORGANIZATION_ID ,
LOCATION_ID ,
UPDATE_RG_FLAG ,
ONCE_COMPLETED_FLAG ,
TOTAL_AMOUNT ,
LINE_AMOUNT ,
TAX_AMOUNT ,
TRX_NUMBER ,
BATCH_SOURCE_ID ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
SET_OF_BOOKS_ID ,
PRIMARY_SALESREP_ID ,
INVOICE_CURRENCY_CODE ,
EXCHANGE_RATE_TYPE ,
EXCHANGE_DATE ,
EXCHANGE_RATE ,
CREATED_FROM ,
UPDATE_RG23D_FLAG ,
TAX_INVOICE_NO ,
LEGAL_ENTITY_ID /* rallamse bug#4448789 */
)
values
(
pr_new.customer_trx_id ,
ln_inv_orgn_id ,
ln_default_locn_id ,
'N' ,
'N' ,/*Bug 4694650 bduvarag*/
0 ,
0 ,
0 ,
lr_trx_rec.trx_number ,
lr_trx_rec.batch_source_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
fnd_global.login_id ,
lr_trx_rec.set_of_books_id ,
lr_trx_rec.primary_salesrep_id ,
lr_trx_rec.invoice_currency_code ,
lr_trx_rec.exchange_rate_type ,
lr_trx_rec.exchange_date ,
lr_trx_rec.exchange_rate ,
lr_trx_rec.created_from ,
'N' ,
NULL ,
ln_legal_entity_id /* rallamse bug#4448789 */
);
' After insert into JAI_AR_TRXS - Bill only invoice');
insert into the JAI_AR_TRX_TAX_LINES table and then insert into the JAI_AR_TRX_LINES table.
pr_new.interface_line_attribute6 = order_line_id
pr_new.interface_line_context = 'ORDER ENTRY'
*/
open c_ont_source_code;
INSERT INTO JAI_AR_TRX_TAX_LINES
(
tax_line_no ,
customer_trx_line_id ,
link_to_cust_trx_line_id ,
precedence_1 ,
precedence_2 ,
precedence_3 ,
precedence_4 ,
precedence_5 ,
precedence_6 ,
precedence_7 ,
precedence_8 ,
precedence_9 ,
precedence_10 ,
tax_id ,
tax_rate ,
qty_rate ,
uom ,
tax_amount ,
func_tax_amount ,
base_tax_amount ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
)
VALUES
(
tax_rec.tax_line_no ,
ra_customer_trx_lines_s.nextval ,
v_customer_trx_line_id ,
tax_rec.precedence_1 ,
tax_rec.precedence_2 ,
tax_rec.precedence_3 ,
tax_rec.precedence_4 ,
tax_rec.precedence_5 ,
tax_rec.precedence_6 ,
tax_rec.precedence_7 ,
tax_rec.precedence_8 ,
tax_rec.precedence_9 ,
tax_rec.precedence_10 ,
tax_rec.tax_id ,
tax_rec.tax_rate ,
tax_rec.qty_rate ,
tax_rec.uom ,
round(tax_rec.tax_amount,2) ,
round(tax_rec.func_tax_amount,2) ,
round(tax_rec.base_tax_amount,2) ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
fnd_global.login_id
);
INSERT INTO JAI_AR_TRX_TAX_LINES
(
tax_line_no ,
customer_trx_line_id ,
link_to_cust_trx_line_id ,
precedence_1 ,
precedence_2 ,
precedence_3 ,
precedence_4 ,
precedence_5 ,
precedence_6 ,
precedence_7 ,
precedence_8 ,
precedence_9 ,
precedence_10 ,
tax_id ,
tax_rate ,
qty_rate ,
uom ,
tax_amount ,
func_tax_amount ,
base_tax_amount ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
)
VALUES
(
tax_rec.tax_line_no ,
ra_customer_trx_lines_s.nextval ,
v_customer_trx_line_id ,
tax_rec.precedence_1 ,
tax_rec.precedence_2 ,
tax_rec.precedence_3 ,
tax_rec.precedence_4 ,
tax_rec.precedence_5 ,
tax_rec.precedence_6 ,
tax_rec.precedence_7 ,
tax_rec.precedence_8 ,
tax_rec.precedence_9 ,
tax_rec.precedence_10 ,
tax_rec.tax_id ,
tax_rec.tax_rate ,
tax_rec.qty_rate ,
tax_rec.uom ,
round(tax_rec.tax_amount,2) ,
round(tax_rec.func_tax_amount,2) ,
round(tax_rec.base_tax_amount,2) ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
fnd_global.login_id
);
select max(tax_line_no)
into ln_last_line_no
from JAI_AR_TRX_TAX_LINES
where link_to_cust_trx_line_id = v_customer_trx_line_id;
select max(tax_line_no)
into ln_base_line_no
from JAI_AR_TRX_TAX_LINES jrctt
, JAI_CMN_TAXES_ALL jtc
where jrctt.link_to_cust_trx_line_id = v_customer_trx_line_id
and jrctt.tax_id = jtc.tax_id
and jtc.tax_type = jai_constants.tax_type_tcs;
|| The api jai_rgm_thhold_proc_pkg.default_thhold_taxes inserts lines as per the same specified in the TCS tax category
|| into the ja_in_so_picking_tax_lines table
*/
jai_rgm_thhold_proc_pkg.default_thhold_taxes
(
p_source_trx_id => ''
, p_source_trx_line_id => v_customer_trx_line_id
, p_source_event => jai_constants.bill_only_invoice
, p_action => jai_constants.default_taxes
, p_threshold_tax_cat_id => ln_threshold_tax_cat_id
, p_tax_base_line_number => ln_base_line_no
, p_last_line_number => ln_last_line_no
, p_currency_code => lr_trx_rec.invoice_currency_code
, p_currency_conv_rate => lr_trx_rec.exchange_rate
, p_quantity => nvl(rec_so_lines.quantity,0)
, p_base_tax_amt => nvl(rec_so_lines.line_amount,0)
, p_assessable_value => rec_so_lines.assessable_value * rec_so_lines.quantity --ADDED rec_so_lines.quantity FOR BUG#6498072
, p_inventory_item_id => rec_so_lines.inventory_item_id
, p_uom_code => rec_so_lines.unit_code
, p_vat_assessable_value => rec_so_lines.vat_assessable_value
, p_process_flag => lv_process_flag
, p_process_message => lv_process_message
);
INSERT INTO JAI_AR_TRX_LINES (
customer_trx_line_id ,
line_number ,
customer_trx_id ,
description ,
payment_register ,
excise_invoice_no ,
preprinted_excise_inv_no ,
excise_invoice_date ,
inventory_item_id ,
unit_code ,
quantity ,
tax_category_id ,
auto_invoice_flag ,
unit_selling_price ,
line_amount ,
tax_amount ,
total_amount ,
assessable_value ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login ,
excise_exempt_type ,
excise_exempt_refno ,
excise_exempt_date ,
ar3_form_no ,
ar3_form_date ,
vat_exemption_flag , -- added, Harshita for bug#4245062
vat_exemption_type ,
vat_exemption_date ,
vat_exemption_refno ,
vat_assessable_value ,
service_type_code --Added by csahoo for Bug#5879769
)
VALUES (
pr_new.customer_trx_line_id ,
pr_new.line_number ,
pr_new.customer_trx_id ,
pr_new.description ,
NULL ,
NULL ,
NULL ,
NULL ,
pr_new.inventory_item_id ,
pr_new.uom_code ,
pr_new.quantity_invoiced ,
rec_so_lines.tax_category_id ,
'Y' ,
pr_new.unit_selling_price ,
round(nvl(rec_so_lines.line_amount,0),2) ,
round(nvl(ln_ar_tax_amount,0),2) ,
round(nvl(rec_so_lines.line_amount,0) +
nvl(ln_ar_tax_amount,0),2) ,
rec_so_lines.assessable_value ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
fnd_global.login_id ,
rec_so_lines.excise_exempt_type ,
rec_so_lines.excise_exempt_refno ,
rec_so_lines.excise_exempt_date ,
NULL ,
NULL ,
rec_so_lines.vat_exemption_flag , -- added, Harshita for bug#4245062
rec_so_lines.vat_exemption_type ,
rec_so_lines.vat_exemption_date ,
rec_so_lines.vat_exemption_refno ,
rec_so_lines.vat_assessable_value ,
rec_so_lines.service_type_code --Added by csahoo for Bug#5879769
);
update JAI_AR_TRXS
set tax_amount = ln_tax_amount ,
line_amount = ln_line_amount,
total_amount = ln_line_amount + ln_tax_amount
where customer_trx_id = pr_new.customer_trx_id;
FILENAME: JA_IN_OE_AR_LINES_INSERT_TRG.sql CHANGE HISTORY:
S.No Date Author and Details
---------------------------------------------------------------------------------------------
1. 12/02/01 MANOHAR MISHRA
Removed the Duplicate_hdr_cur from the commented zone.
2. 2001/04/11 JAGDISH BHOSLE
Added Bond register checking to avoid Excise duty to hit
Accounting entries.
3. 2001/04/20 Anuradha Parthasarathy
Enhancement for RG entries for Supplementary Transactions.
4. 2001/05/03 Gadde,Jagdish
Check added to avoid firing of trigger for Non_Indian OU.
5. 2001/06/05 Anuradha Parthasarathy
Cursor added to take care of correct tax insertions
into JAI_AR_TRX_TAX_LINES
6. 2001/09/13 Vijay
Added condition of interface_line_context to return
for Project Accounting
7. 2001/11/01 Anuradha Parthasarathy
Condition added to ensure that Modvat Recovery types of Taxes
should not be charged to the customer.
8. 2002/04/17 RPK
for BUG# 2327261.
Code modified to prevent the erroring out of the autoinvoice import program
when the manual delivery is made and when the delivery name is alphanumeric.
When the order created is assigned to this delivery and the ship confimation is
done ,then the autoinvoice import program is running into error because the
the validation is failing for delivery_id and interface_line_attribute3
9. 2002/04/22 SRIRAM For Bug # 2316589 . The JAI_AR_TRXS table was updated with
wrong values . Instead a new update has been written using a different value to
update tax amounts correctly.
10 2002/06/26 SRIRAM For Bug # 2398198 . Tax Lines were inserted multiple times in the
JAI_AR_TRX_TAX_LINES , JAI_AR_TRX_LINES tables when discounts
exist and tax amounts were updated doubly in JAI_AR_TRXS table, which have been
solved in this bug.
11. 2002/08/17 SRIRAM .Fopr Bug # 2518534 . When there is a change in UOM in the
Sales order screen , the taxes in the localization screen are not
flowing correctly.
12. 2002/11/19 SRIRAM - Bug # 2668342. Performance issue reported when using the
org_organization_definitions table using nvl(operating_unit) in the where
clause. This is causing couple of full table scans which was a performance
bottle neck.A new cursor definition has been written which uses the
HR_OPERATING_UNITS table and this is performance optimized.
Organization_id column is the operating_unit in the table.
13. 2003/01/09 Sriram - Bug # 2742849 - Version is 615.3
This problem is about tax lines being inserted for discount line also.
The earlier fix assumed that the discount line follows the item line by line number,
that is they are consecutive lines. But this assumption is not correct and it depends on setup.
Found out from base apps team that interface_line_attribute11 can be used to identify a discount line from the item line.
For a Invoice line imported from OM , the interface_line_attribute11 will have a value 0 or Null ,
whereas for a discount line , the interface_line_attribute11 will have a value which maps to the price_adjustment_id.
13. 2003/02/07 Vijay Shankar - Bug # 2779990 - Version is 615.4
When bond transaction invoice is created, then tax_amounts populated in JAI_AR_TRXS and JAI_AR_TRX_LINES is wrong.
this is rectified by writing a new cursor name c_tax_amount
14. 2003/03/17 Sriram LMW ATO Issue - Bug #2806274 Version 615.5
Created an internal procedure Process_Taxes_for_ATO_Order to pull taxes from OM to Ar for ATO Orders.
The idea was to import the taxes for the 'Model' Item based on the config item.
15. 2003/06/26 Sriram - Bug # 3000550 version 616.1
Tax amounts were not calculated correctly , in the JAI_AR_TRXS and JAI_AR_TRX_LINES
table.This was observed when there was a split done during shipment.
Data was correct in the JAI_AR_TRX_TAX_LINES table , but incorrect in the JAI_AR_TRX_LINES
and JAI_AR_TRXS table.
This has been fixed in this bug
16. 2003/08/22 Sriram - Bug # 3021588 version 616.2
For Multiple Bond Register Enhancement,
Instead of using the cursors for fetching the register associated with the invoice type , a call has been made to the procedures
of the jai_cmn_bond_register_pkg package. There enhancement has created dependency because of the
introduction of 3 new columns in the JAI_OM_OE_BOND_REG_HDRS table and also call to the new package jai_cmn_bond_register_pkg.
This fix has introduced huge dependency . All future changes in this object should have this bug as a prereq
17 2003/09/16 SSUMAITH - Bug # 3134224 Version 616.3
For a Supplementary invoice , if if the ST form Type is NULL , entries are going into the ST forms Tracking tables.
This is causing the unique constraint violation error. This has been resolved by making a change to ensure that
entries in the ST forms table needs to go only if the St form Type is not null and tax type is 'Sales Tax' or 'CST'.
18 2003/10/10 Vijay shankar - Bug # 3181892, Version 616.4
AR3 form number and date values are not populated into JAI_AR_TRX_LINES table which is resolved with this fix
19 2003/12/30 Aiyer - Bug #3328871, Version 618.1
Issue: -
Create an order such that it does not have any associated taxes.
Ship this order and run the auto invoice program.
It is found that no records are inserted in the table JAI_AR_TRX_LINES.
Solution: -
This was happening because the code was assuming that a line cannot exist without any taxes.
So in such a case the control was made to return back without inserting a line in JAI_AR_TRX_LINES
table.
Fix Details:-
Added a if clause in the code. Placed the return statement between the if clause.
So the modified functionality is that is a line does not have any taxes in shpiing tables and also does not have
any taxes in JAI_OM_OE_SO_TAXES table then check whether a line exists in JAI_OM_OE_SO_LINES table only.
IF yes then do not retunr, if not found then return.
Also added a nvl clause in the cursor c_cust_trx_tax_line_amt. so that a value of zero would be returned
even if the where clause failed to fetch a record.
This would take care that tax amount would be inserted as 0 and total amount would also be computed properly
in table JAI_AR_TRX_LINES.
This issue was reported thorugh the bug 3344492.
Also indented the whole code and added additional comments wereever necessary.
Removed the commented code wherever applicable.
Dependency Introduced Due To This Bug:-
None
20 2004/02/16 RBASKER - Bug 3357587, Version 618.2
When SO UOM is different from that of the primary UOM,
JAI_AR_TRX_LINES is updated with wrong assessable value
Added a cursor C_JA_SO_LINES_ASSESSABLE_VAL to fetch the correct assessable_value
from JAI_OM_OE_SO_LINES instead of JAI_OM_WSH_LINES_ALL table.
21. 2004/05/05 ssumaith - bug# 3607101 - Version 619.1
When autoinvoice import program imports an invoice from customer software and if interface_line_attribute11
field in the ra_customer_trx_lines_all table is not null then , localization taxes are not retreived
into the AR invoice.
This issue has been resolved by adding a context based check that interface_line_attribute11 field cannot be
null for Order Entry as the source of the invoice for taxes to be imported , and other wise if the context is not
Order Entry then the localisation taxes will be populated into the Invoice from shipment.
22. 2004/04/16 ssumaith - bug# 3532716 Version - 115.1
The variable v_tax_count was not re-initialised to zero , as a result code flow is not entering into an if condition
because of the static value of 1 it has initially and hence all taxes are not flowing into the AR invoice.
23. 2004/11/03 Vijay Shankar for Bug# 3985561 (Porting of Bug#3651923), Version: 115.2
commented a delete statement on JAI_AR_TRX_INS_LINES_T table which is redundant and causing deadlock problems
This DELETE statement is executed in Concurrent process where in taxes are defaulted from OM to AR
* HIGH DEPENDENCY for future bugs *
24. 29-nov-2004 ssumaith - bug# 4037690 - File version 115.4
Check whether india localization is being used was done using a INR check in every trigger.
This check has now been moved into a new package and calls made to this package from this trigger
If the function jai_cmn_utils_pkg.check_jai_exists returns true it means INR is the set of books currency ,
Hence if this function returns FALSE , control should return.
25. 06-JAn-2005 ssumaith - bug#4136981 File version 115.5
In case of Bond Register Setup , in addition to Excise, the Education Cess taxes should also not flow to
Receivables Localization tables.
This check has been done in the code at two places.
Also added code for ensuring that taxes flow into ar in case of bill only workflow scenario
This fix does not introduce dependency on this object , but this patch cannot be sent alone to the CT
because it relies on the alter done and the new tables created as part of the education cess enhancement
bug# 4146708 creates the objects
26. 17-Mar-2005 hjujjuru - bug #4245062 File version 115.6
The columns vat_exemption_flag, vat_exemption_type, vat_exemption_date, vat_exemption_refno,
and vat_assessable_value have been added in JAI_OM_OE_SO_LINES, JAI_OM_WSH_LINES_ALL
and JAI_AR_TRX_LINES.
Additional fields vat_invoice_no and vat_invoice_date have been added into JAI_OM_WSH_LINES_ALL
and JAI_AR_TRXS.
The trigger has been updated to ensure that any data flowing into JAI_AR_TRX_LINES and
JAI_AR_TRXS also includes the Vat information that comes down from either JAI_OM_OE_SO_LINES
or JAI_OM_WSH_LINES_ALL .
Base bug - #4245089
Trigger file name is renamed to JAI_AR_RCTLA_T7.SQL
---------------------------------------------------
27. 25-MAY-2005 BRATHOD, Bug# 4392001, File Version 116.1
Issue:-
RA_INTERFACE_LINES DFF segments needs to be limited use only one segment
Fix:-
- Following four segments will be obsoleted
1. SUPPLEMENT CM
2. SUPPLEMENT DM
3. SUPPLEMENT INVOICE
4. TDS CREDIT
- A new segment (INDIA INVOICES) will be created with following attributes
1. INTERFACE_LINE_ATTRIBUTE1 - Invoice Type
2. INTERFACE_LINE_ATTRIBUTE2 - Unique Identifier
- As new dff uses the ATTRIBUTE1 field the existing values of ATTRIBUTE1 will be
migrated to ATTRIBUTE4
- Attribute context will be changed to INDIA INVOICES.
- INTERFACE_LINE_ATTRIBUTE1 will identify the type of invoice the possible values
for this field will be same as different segments used previously
i.e SUPPLEMENT CM, SUPPLEMENT DM, SUPPLEMENT INVOICE, TDS CREDIT
28 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.2
29 10-Jun-2005 File Version: 116.3
Removal of SQL LITERALs is done
30 10-Jun-2005 rallamse bug#4448789 116.3
Added legal_entity_id for table JAI_AR_TRXS in insert statement
06-Jul-2005 Ramananda for bug#4477004. File Version: 116.4
GL Sources and GL Categories got changed. Refer bug for the details
31 14-Jul-2005 rchandan for bug#4487676. File Version: 117.2
JAI_CMN_RG_23AC_I_TXNS_S is replaced by JAI_CMN_RG_23AC_I_TRXS_S
32 26-Jul-2005 rallamse for bug#4510143 File Version 120.2
Changed the legal_entity_id to get from function get_legal_entity_id.
Replaced legal_entity_id with ln_legal_entity_id( holds value of get_legal_entity_id )
for table JAI_AR_TRXS in insert statement.
33. 24-Aug-2005 Ramananda for bug #4567935 (115 bug 4404898). File version 120.2
Issue:-
1. Trigger currently processes invoices with interface line context as LEGACY.This needs to be stopped
Fix:-
1. Added the check to RETURN from the trigger if interface line context in ( 'LEGACY','PROJECTS INVOICES','OKS CONTRACTS')
also did forward porting for the bugs 4395450,4426613.
Dependency due to this bug :-
Functional dependency with jai_ar_rctla_ari_t8 trigger of jai_fin_t.sql (120.2)
34. 28-Jun-2007 CSahoo for bug#6155839, File Version 120.16
replaced RG Register Data Entry by jai_constants.je_category_rg_entry
35. 16-oCT-2007 CSahoo for bug#6498072, File Version 120.21
Multipled p_quantity to the assesible value in the call to the procedure jai_rgm_thhold_proc_pkg.default_thhold_taxes
36. 18-OCT-2007 CSahoo for bug#6498072, File Version 120.22
Added the cursor get_ar_tax_amount to get the total tax amount from JAI_AR_TRX_TAX_LINES table.
Moved the code for Inserting into JAI_AR_TRX_LINES and updating the table JAI_AR_TRXS to the end in the procedure
process_bill_only_invoice
37 11-Nov-2008 JMEENA for bug#6498345( FP 6492966 )
Issue: AR Autoinvoice completes in Error: ORA-20130: ORGANIZATION CANNOT BE NULL
Reason: OPTION items in the sales order have taxes attached (Excise + VAT) with zero tax amounts.
For OPTION item, derivation of organization and location is not present in the code
as the taxes are not expected to be in the option item.
As the it has vat taxes, it checks for organization id and AI errors out.
Fix: Added logic to extract organization and location for OPTION items
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_oe_ar_lines_insert_trg.sql
----------------------------------------------------------------------------------------------------------------------------------------------------
616.2 3021588 IN60104D1 + ssumaith 22/08/2003 Bond Register Enhancement
2801751 +
2769440
115.4 4037690 IN60105D2 ja_in_util_pkg_s.sql 115.0 ssumaith 29-Nov-2004 Call to this function.
ja_in_util_pkg_s.sql 115.0 ssumaith
115.6 4245062 IN60106 + 4245089 hjujjuru 17/03/2005 VAT Implelentation
----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------*/
/* --Ramananda for File.Sql.35, start */
v_org_id :=pr_new.org_id;
v_last_update_date := pr_new.last_update_date;
v_last_updated_by := pr_new.last_updated_by;
v_last_update_login := pr_new.last_update_login;
INSERT INTO JAI_AR_TRXS ( -- supplement
customer_trx_id ,
organization_id ,
location_id ,
trx_number ,
update_rg_flag ,
update_rg23d_flag ,
once_completed_flag ,
batch_source_id ,
set_of_books_id ,
primary_salesrep_id ,
invoice_currency_code ,
exchange_rate_type ,
exchange_date ,
exchange_rate ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login ,
legal_entity_id /* rallamse bug#4448789 */
)
VALUES (
v_header_id ,
v_organization_id ,
v_location_id ,
v_trx_number ,
'Y' ,
'Y' ,
'N' ,
v_batch_source_id ,
v_books_id ,
v_salesrep_id ,
c_from_currency_code ,
c_conversion_type ,
c_conversion_date ,
c_conversion_rate ,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login ,
ln_legal_entity_id /* rallamse bug#4448789 */
);
FND_FILE.PUT_LINE(FND_FILE.LOG, ' Inside x <> 1: After insert into JAI_AR_TRXS');
INSERT INTO JAI_AR_TRX_TAX_LINES(
tax_line_no ,
customer_trx_line_id ,
link_to_cust_trx_line_id ,
precedence_1 ,
precedence_2 ,
precedence_3 ,
precedence_4 ,
precedence_5 ,
precedence_6 ,
precedence_7 ,
precedence_8 ,
precedence_9 ,
precedence_10 ,
tax_id ,
tax_rate ,
qty_rate ,
uom ,
tax_amount ,
func_tax_amount ,
base_tax_amount ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
)
VALUES(
v_tax_line_no ,
ra_customer_trx_lines_s.NEXTVAL ,
v_customer_trx_line_id ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
tax_rec.new_tax_id ,
tax_rec.new_rate ,
tax_rec.new_qty_rate ,
tax_rec.new_uom ,
tax_rec.diff_amt ,
tax_rec.func_tax_amt*nvl(v_exchange_rate,1) ,
tax_rec.base_tax_amt ,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login
);
INSERT INTO JAI_AR_TRX_TAX_LINES(
tax_line_no ,
customer_trx_line_id ,
link_to_cust_trx_line_id ,
precedence_1 ,
precedence_2 ,
precedence_3 ,
precedence_4 ,
precedence_5 ,
precedence_6 ,
precedence_7 ,
precedence_8 ,
precedence_9 ,
precedence_10 ,
tax_id ,
tax_rate ,
qty_rate ,
uom ,
tax_amount ,
func_tax_amount ,
base_tax_amount ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
)
VALUES (
v_tax_line_no ,
ra_customer_trx_lines_s.nextval ,
v_customer_trx_line_id ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
tax_rec.new_tax_id ,
tax_rec.new_rate ,
tax_rec.new_qty_rate ,
tax_rec.new_uom ,
tax_rec.diff_amt ,
tax_rec.func_tax_amt*nvl(v_exchange_rate,1) ,
tax_rec.base_tax_amt ,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login
);
INSERT INTO JAI_AR_TRX_LINES (
customer_trx_line_id ,
line_number ,
customer_trx_id ,
description ,
inventory_item_id ,
unit_code ,
quantity ,
tax_category_id ,
auto_invoice_flag ,
unit_selling_price ,
line_amount ,
tax_amount ,
total_amount ,
assessable_value ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
)
VALUES(
v_customer_trx_line_id ,
pr_new.line_number ,
v_header_id ,
pr_new.description ,
pr_new.inventory_item_id ,
pr_new.uom_code ,
NVL(pr_new.quantity_invoiced,0) ,
v_tax_category_id ,
'Y' ,
NVL(pr_new.unit_selling_price,0) ,
v_line_amount ,
v_tax_amount ,
(v_line_amount + v_tax_amount) ,
v_assessable_value ,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login
);
UPDATE JAI_AR_TRXS
SET
line_amount = NVL(line_amount, 0 ) + NVL(v_line_amount,0),
once_completed_flag = NVL(v_once_completed_flag,'N')
WHERE
customer_trx_id = v_header_id;
UPDATE
JAI_CMN_RG_EXC_INV_NOS
SET
start_number = v_start_number,
last_update_date = v_last_update_date,
last_updated_by = v_last_updated_by,
last_update_login = v_last_update_login
WHERE
organization_id = master_org_rec.organization_id AND
location_id = master_org_rec.location_id AND
fin_year = v_fin_year AND
order_invoice_type = v_order_invoice_type AND
register_code = v_meaning;
UPDATE JAI_AR_TRX_LINES
SET payment_register = v_reg_type,
excise_invoice_no = v_exc_invoice_no,
excise_invoice_date = trunc(sysdate)
WHERE
customer_trx_line_id = v_customer_trx_line_id AND
inventory_item_id = pr_new.inventory_item_id AND
customer_trx_id = v_header_id;
v_last_update_date ,
v_last_updated_by ,
v_last_update_login
);
SELECT JAI_CMN_RG_23AC_I_TRXS_S.currval INTO v_part_i_register_id FROM dual; /* txns changed to trxs by rchandan for bug#4487676 */
v_last_update_date ,
v_last_updated_by ,
v_last_update_login ,
pr_new.customer_trx_line_id ,
null ,
null
);
SELECT JAI_CMN_RG_23AC_I_TRXS_S.currval INTO v_rg23_part_i_no FROM dual; /* txns changed to trxs by rchandan for bug# bug#4487676 */
SELECT JAI_CMN_RG_23AC_II_TRXS_S.currval INTO v_rg23_part_ii_no FROM dual;
UPDATE JAI_CMN_RG_23AC_I_TRXS
SET REGISTER_ID_PART_II = v_rg23_part_ii_no,
charge_account_id = (
SELECT
charge_account_id
FROM
JAI_CMN_RG_23AC_II_TRXS
WHERE
register_id = v_rg23_part_ii_no
)
WHERE register_id = v_rg23_part_i_no;
v_last_update_date ,
v_last_updated_by ,
v_last_update_login
);
SELECT JAI_CMN_RG_PLA_TRXS_S1.currval INTO v_pla_register_no FROM dual;
UPDATE JAI_CMN_RG_23AC_I_TRXS
SET REGISTER_ID_PART_II = v_pla_register_no,
charge_account_id = (SELECT charge_account_id FROM JAI_CMN_RG_PLA_TRXS
WHERE register_id = v_pla_register_no)
WHERE register_id = v_rg23_part_i_no;
INSERT INTO JAI_AR_TRXS
(Customer_Trx_ID, Organization_ID, Location_ID, Trx_Number,
Update_RG_Flag,UPDATE_RG23D_FLAG, Once_Completed_Flag, Batch_Source_ID, Set_Of_Books_ID,
Primary_Salesrep_ID, Invoice_Currency_Code, Exchange_Rate_Type,
Exchange_Date, Exchange_Rate,
creation_date, created_by, last_update_date, last_updated_by, last_update_login,
legal_entity_id /* rallamse bug#4448789 */
)
VALUES (
v_header_id, v_organization_id, v_location_id, v_trx_number,
'Y', 'Y','N', v_batch_source_id, v_books_id,
v_salesrep_id, c_from_currency_code, c_conversion_type,
c_conversion_date, c_conversion_rate,
v_creation_date, v_created_by, v_last_update_date, v_last_updated_by, v_last_update_login,
ln_legal_entity_id /* rallamse bug#4448789 */
);
IF yes then insert all the taxes into ja_in_ra_cust_trx_lines_all table
IF no then check the same in ja_in_so_tax_lines_table.
(
*/
IF NVL(v_exist_flag,0) = 1 THEN
v_bond_tax_amt := 0; -- cbabu for Bug# 2779990
UPDATE JAI_AR_TRXS
SET vat_invoice_no = tax_rec.vat_invoice_no,
vat_invoice_date = tax_rec.vat_invoice_date
WHERE customer_trx_id = pr_new.customer_trx_id;
INSERT INTO JAI_AR_TRX_TAX_LINES (
tax_line_no ,
customer_trx_line_id ,
link_to_cust_trx_line_id ,
precedence_1 ,
precedence_2 ,
precedence_3 ,
precedence_4 ,
precedence_5 ,
precedence_6 ,
precedence_7 ,
precedence_8 ,
precedence_9 ,
precedence_10 ,
tax_id ,
tax_rate ,
qty_rate ,
uom ,
tax_amount ,
func_tax_amount ,
base_tax_amount ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
)
VALUES
(
tax_rec.tax_line_no ,
ra_customer_trx_lines_s.nextval ,
v_customer_trx_line_id ,
tax_rec.precedence_1 ,
tax_rec.precedence_2 ,
tax_rec.precedence_3 ,
tax_rec.precedence_4 ,
tax_rec.precedence_5 ,
tax_rec.precedence_6 ,
tax_rec.precedence_7 ,
tax_rec.precedence_8 ,
tax_rec.precedence_9 ,
tax_rec.precedence_10 ,
tax_rec.tax_id ,
tax_rec.tax_rate ,
tax_rec.qty_rate ,
tax_rec.uom ,
round(v_tax_amt,2) ,
round(v_func_tax_amount,2) ,
round(v_base_tax_amount,2) ,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login
);
INSERT INTO JAI_AR_TRX_TAX_LINES(
tax_line_no ,
customer_trx_line_id ,
link_to_cust_trx_line_id ,
precedence_1 ,
precedence_2 ,
precedence_3 ,
precedence_4 ,
precedence_5 ,
precedence_6 ,
precedence_7 ,
precedence_8 ,
precedence_9 ,
precedence_10 ,
tax_id ,
tax_rate ,
qty_rate ,
uom ,
tax_amount ,
func_tax_amount ,
base_tax_amount ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
)
VALUES(
tax_rec.tax_line_no ,
ra_customer_trx_lines_s.nextval ,
v_customer_trx_line_id ,
tax_rec.precedence_1 ,
tax_rec.precedence_2 ,
tax_rec.precedence_3 ,
tax_rec.precedence_4 ,
tax_rec.precedence_5 ,
tax_rec.precedence_6 ,
tax_rec.precedence_7 ,
tax_rec.precedence_8 ,
tax_rec.precedence_9 ,
tax_rec.precedence_10 ,
tax_rec.tax_id ,
tax_rec.tax_rate ,
tax_rec.qty_rate ,
tax_rec.uom ,
round(v_tax_amt,2) ,
round(v_func_tax_amount,2) ,
round(v_base_tax_amount,2) ,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login
);
IF yes then insert all the taxes into JAI_AR_TRX_TAX_LINES table.
*/
IF NVL(v_exist_flag,0) = 1 THEN
FOR tax_rec IN ja_so_tax_lines_info LOOP
-- Date 23/02/2006 by sacsethi for bug 5228046
-- precedence 6 to 10
INSERT INTO JAI_AR_TRX_TAX_LINES(
tax_line_no ,
customer_trx_line_id ,
link_to_cust_trx_line_id ,
precedence_1 ,
precedence_2 ,
precedence_3 ,
precedence_4 ,
precedence_5 ,
precedence_6 ,
precedence_7 ,
precedence_8 ,
precedence_9 ,
precedence_10 ,
tax_id ,
tax_rate ,
qty_rate ,
uom ,
tax_amount ,
func_tax_amount ,
base_tax_amount ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
)
VALUES(
tax_rec.tax_line_no ,
ra_customer_trx_lines_s.nextval ,
v_customer_trx_line_id ,
tax_rec.precedence_1 ,
tax_rec.precedence_2 ,
tax_rec.precedence_3 ,
tax_rec.precedence_4 ,
tax_rec.precedence_5 ,
tax_rec.precedence_6 ,
tax_rec.precedence_7 ,
tax_rec.precedence_8 ,
tax_rec.precedence_9 ,
tax_rec.precedence_10 ,
tax_rec.tax_id ,
tax_rec.tax_rate ,
tax_rec.qty_rate ,
tax_rec.uom ,
round(tax_rec.tax_amount,2) ,
round(tax_rec.func_tax_amount,2) ,
round(tax_rec.base_tax_amount,2) ,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login
);
Update done by aiyer for the bug #3328871
If taxes do not exist in JAI_OM_WSH_LINE_TAXES and JAI_OM_OE_SO_TAXES then control
would come here.
Code should return only in case of an RMA i.e return only when a line does not exist in JAI_OM_OE_SO_LINES table
for the given line_id.
*/
DECLARE
CURSOR c_so_lines_exists
IS
SELECT 1
FROM JAI_OM_OE_SO_LINES
WHERE line_id = pr_new.interface_line_attribute6;
INSERT INTO JAI_AR_TRX_LINES (
customer_trx_line_id ,
line_number ,
customer_trx_id ,
description ,
payment_register ,
excise_invoice_no ,
preprinted_excise_inv_no ,
excise_invoice_date ,
inventory_item_id ,
unit_code ,
quantity ,
tax_category_id ,
auto_invoice_flag ,
unit_selling_price ,
line_amount ,
tax_amount ,
total_amount ,
assessable_value ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login ,
excise_exempt_type ,
excise_exempt_refno ,
excise_exempt_date ,
ar3_form_no ,
ar3_form_date ,
vat_exemption_flag , -- added, Harshita for bug#4245062
vat_exemption_type ,
vat_exemption_date ,
vat_exemption_refno ,
vat_assessable_value
)
VALUES (
v_customer_trx_line_id ,
pr_new.line_number ,
v_header_id ,
pr_new.description ,
v_payment_register ,
v_excise_invoice_no ,
v_preprinted_excise_inv_no ,
v_excise_invoice_date ,
pr_new.inventory_item_id ,
pr_new.uom_code ,
pr_new.quantity_invoiced ,
v_tax_category_id ,
'Y' ,
pr_new.unit_selling_price ,
round(v_line_amount,2) ,
round(v_calc_tax_amount,2) ,
round((v_line_amount + v_calc_tax_amount),2) ,
v_assessable_value ,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login ,
v_excise_exempt_type ,
v_excise_exempt_refno ,
v_excise_exempt_date ,
v_ar3_form_no ,
v_ar3_form_date ,
lv_vat_exemption_flag , -- added, Harshita for bug#4245062
lv_vat_exemption_type ,
lv_vat_exemption_date ,
lv_vat_exemption_refno ,
ln_vat_assessable_value
);
UPDATE JAI_AR_TRX_LINES
SET customer_trx_id = v_header_id
WHERE customer_trx_line_id = v_customer_trx_line_id;
DELETE JAI_AR_TRXS
WHERE customer_trx_id = v_old_customer_trx_id;
UPDATE JAI_AR_TRXS
SET line_amount = round(NVL(line_amount,0) + NVL(v_line_amount,0),2),
tax_amount = round(NVL(tax_amount,0) + NVL(v_so_tax_amount,0),2),
total_amount = round(NVL(total_amount,0) + NVL(v_line_amount,0) + NVL(v_so_tax_amount,0),2),
once_completed_flag = NVL(v_once_completed_flag,'N'),
vat_invoice_no = ln_vat_invoice_no, vat_invoice_date = ln_vat_invoice_date -- added, Harshita for bug#4245062
WHERE customer_trx_id = v_header_id;
UPDATE JAI_AR_TRXS
SET line_amount = round(NVL(line_amount, 0 ) + NVL(v_line_amount,0),2),
total_amount = round(NVL(total_amount,0) + NVL(v_line_amount,0),2),
once_completed_flag = NVL(v_once_completed_flag,'N'),
vat_invoice_no = ln_vat_invoice_no, vat_invoice_date = ln_vat_invoice_date -- added, Harshita for bug#4245062
WHERE customer_trx_id = v_header_id;
lv_appl_src := 'JA_IN_OE_AR_LINES_INSERT_TRG' ;
INSERT INTO JAI_CMN_ERRORS_T
(
APPLICATION_SOURCE ,
error_message ,
additional_error_mesg ,
creation_date ,
created_by ,
-- added, Harshita for Bug 4866533
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES
(
lv_appl_src, /*'JA_IN_OE_AR_LINES_INSERT_TRG' ,*/
lv_err_msg , /* 'EXCEPTION Occured ' ,*/
substr(vsqlerrm,1,200) ,
sysdate ,
user ,
-- added, Harshita for Bug 4866533
fnd_global.user_id,
sysdate
);
v_last_update_date DATE := pr_new.last_update_date;
v_last_updated_by NUMBER := pr_new.last_updated_by;
v_last_update_login NUMBER := pr_new.last_update_login;
SELECT 1
FROM JAI_AR_TRX_LINES
WHERE customer_trx_LINE_id = v_customer_trx_line_id;
SELECT Customer_Trx_Id
FROM JAI_AR_TRX_LINES
WHERE customer_trx_LINE_id = v_customer_trx_line_id;
SELECT created_from, trx_number, batch_source_id, set_of_books_id, primary_salesrep_id,
invoice_currency_code, exchange_rate_type, exchange_date, exchange_rate,
legal_entity_id /* rallamse bug#4448789 */
FROM ra_customer_trx_all
WHERE customer_trx_id = v_header_id;
SELECT organization_id, location_id
FROM JAI_OM_WSH_LINES_ALL
WHERE order_line_id IN
(SELECT reference_line_id
FROM oe_order_lines_all
WHERE to_char(line_id) = v_interface_line_attribute7
);
SELECT organization_id , location_id FROM JAI_INV_SUBINV_DTLS
WHERE organization_id = pr_new.interface_line_attribute10
AND UPPER(sub_inventory_name) IN
(SELECT UPPER(subinventory)
FROM rcv_transactions
WHERE
organization_id = pr_new.interface_line_attribute10
AND (oe_order_line_id) = v_interface_line_attribute7
AND subinventory IS NOT NULL
);
SELECT tax_category_id, assessable_value, service_type_code -- service_type_code added by csahoo for bug#5879769
FROM JAI_OM_OE_RMA_LINES
WHERE rma_line_id = v_interface_line_attribute7;
SELECT A.tax_line_no, A.uom, A.tax_id, A.tax_rate, A.qty_rate, A.base_tax_amount, A.tax_amount, b.tax_type,
A.func_tax_amount,
A.precedence_1, A.precedence_2, A.precedence_3, A.precedence_4, A.precedence_5 ,
A.precedence_6, A.precedence_7, A.precedence_8, A.precedence_9, A.precedence_10
FROM JAI_OM_OE_RMA_TAXES A, JAI_CMN_TAXES_ALL b
WHERE A.rma_line_id = v_interface_line_attribute7
AND A.tax_id = b.tax_id
and b.tax_type <> lc_modvat_tax/*Bug 4881426 bduvarag*/
ORDER BY tax_line_no;
SELECT 1
FROM JAI_AR_TRXS
WHERE customer_trx_id = v_header_id;
SELECT 1
FROM JAI_OM_OE_RMA_LINES
WHERE rma_line_id = v_interface_line_attribute7;
SELECT 1
FROM JAI_OM_OE_RMA_TAXES
WHERE rma_line_id = v_interface_line_attribute7;
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_header_id
AND NVL(A.org_id,0) = NVL(pr_new.org_id,0);
SELECT NVL(quantity,0)
FROM JAI_OM_OE_RMA_LINES
WHERE rma_line_id = v_interface_line_attribute7;
select FND_PROFILE.VALUE('ONT_SOURCE_CODE')
from dual;
SELECT
ott.transaction_type_id
FROM
ra_customer_trx_all rct,
oe_transaction_types_tl ott
WHERE
rct.customer_trx_id = pr_new.previous_customer_trx_id
/* bug 4926865. Added by Lakshmi Gopalsami
Removed the upper as the data getting inserted into
interface_header_attribute2 will be same as name in
oe_transaction_types_tl
*/
AND rct.interface_header_attribute2 = ott.name ;
SELECT
nvl(sum(tax_amount),0)
FROM
JAI_AR_TRX_TAX_LINES
WHERE
link_to_cust_trx_line_id = pr_new.customer_trx_line_id ;
FILENAME: JA_IN_RMA_AR_LINES_INSERT_TRG.sql
CHANGE HISTORY:
S.No Date Author and Details
================================================================================================
1. 2001/06/14 Subbu Code added to avoid unique constraint violation.
2. 2002/06/07 Ashish Shukla 2398850 INR check included in the code.(changes as per GSR)
3. 2002/09/11 Sriram Bug # 2461542. Added an if condition to check the duplicate taxes.
This was happening was because when a RMA was done against a sales order
that had discounts,the taxes were flowing as many times extra as the number
of discounts. If a line has 2 discounts , then the taxes were getting tripled.
4 2002/11/22 Bug # 2668342 - replaced cursor using org_organization_definitions
with nvl(operating_unit,0) causing performance problem
and used the hr_operating_units table instead.
5 . 2003/01/09 Sriram - Bug # 2742849 - Version is 615.2
This problem is about tax lines being inserted for discount line also. The earlier fix assumed
that the discount line follows the item line by line number, that is they are consecutive
lines. But this assumption is not correct and it depends on setup.
Found out from base apps team that interface_line_attribute11 can be used to identify a
discount line from the item line. For a Invoice line imported from OM , the
interface_line_attribute11 will have a value 0 or Null , whereas for a discount line , the
interface_line_attribute11 will have a value which maps to the price_adjustment_id.
6. 2003/01/18 Sriram - Bug # 2755890 - File Version 615.3
When a credit memo created in AR because of a RMA Transaction is queried
in the localization AR screen , it was causing no reords to be retreived.
It is because , organization id and location id are not getting populated
in the JAI_AR_TRXS table. This was because a cursor fetch was done incorrectly. The
cursor definition has been corrected .
For RMA without reference , organization and location has to be fetched based on the subinventory
chosen in the receiving transaction in Purchasing responsibility. Hence a new cursor is written
to fetch the organization id , location id based on the subinventory name from the JAI_INV_SUBINV_DTLS
table based on the subinventory name in the rcv transactions table.
7. 2003/04/18 Sriram - Bug # 2905912 - File Version 615.4
For a RMA Transaction , the tax amount and total amount columns in the JAI_AR_TRXS
table were incorrect. This has been corrected in this fix.
8. 2004/06/09 ssumaith - bug# 3680721 File Version 115.1
When autoinvoice import program imports a crdit memo from custom software and if interface_line_attribute11
field in the ra_customer_trx_lines_all table is not null then , localization taxes are not retreived
into the AR invoice.
This issue has been resolved by adding a context based check that interface_line_attribute11 field cannot be
null for Order Entry as the source of the invoice for taxes to be imported , and other wise if the context is not
Order Entry then the localisation taxes will be populated into the credit memo based on rma order.
9. 29/Nov/2004 Aiyer for bug#4035566. Version#115.1
Issue:-
The trigger should not get fired when the non-INR based set of books is attached to the current operating unit
where transaction is being done.
Fix:-
Function jai_cmn_utils_pkg.check_jai_exists is being called which returns the TRUE if the currency is INR and FALSE if the currency is
NON-INR
Also removed the cursors Fetch_Book_Id_Cur and Sob_cur and the variables v_gl_set_of_bks_id and v_currency_code and v_operating_id
Dependency Due to this Bug:-
The current trigger becomes dependent on the function jai_cmn_utils_pkg.check_jai_exists version 115.0. introduced through the bug 4033992
10. 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
11. 10-Jun-2005 File Version: 116.2
Removal of SQL LITERALs is done
12. 10-Jun-2005 rallamse bug#4448789 116.3
Added legal_entity_id for table JAI_AR_TRXS in insert statement
13. 26-07-2005 rallamse bug#4510143 120.2
The legal_entity_id is derived from CREATED_FROM_CUR cursor based on ra_customer_trx_all.
14. 24-Aug-2005 Ramananda for bug #4567935 (115 bug 4404898, 4395450). File version 120.2
Issue:-
1. The following type of taxes should not be inserted into JAI_AR_TRX_LINES when Order type is BOND register:-
'EXCISE', 'OTHER EXCISE', 'CVD_EDUCATION_CESS', 'EXCISE_EDUCATION_CESS'
2. Trigger currently processing Credit memo invoices with interface line context as LEGACY.This needs to be stopped
Fix:-
1. Before a insert in the table JAI_AR_TRX_LINES, added a IF statement to bypass the insert into JAI_AR_TRX_LINES if
the register code is 'BOND_REG' and any of the above stated taxes are present.
2. Added the check to RETURN from the trigger if interface line context = 'LEGACY'
Dependency due to this bug :-
Functional dependency with jai_ar_rctla_ari_t7 trigger of jai_fin_t.sql (120.2)
15. 20-Sep-2007 CSahoo for bug#6407648, file version 120.20
Added the Cursor cur_get_cmline_tax_amt to get the line tax amount.
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_rma_ar_lines_insert_trg
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
115.1 4035566 IN60105D2 + ja_in_util_pkg_s.sql 115.0 Aiyer 29-Nov-2004 Call to this function.
4033992 ja_in_util_pkg_b.sql 115.0
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
/*
|| Code added by aiyer for the bug 4035566
|| Call the function jai_cmn_utils_pkg.check_jai_exists to check the current set of books in INR/NON-INR based.
*/
--IF jai_cmn_utils_pkg.check_jai_exists ( p_calling_object => 'JA_IN_RMA_AR_LINES_INSERT_TRG' ,
-- p_set_of_books_id => pr_new.set_of_books_id
-- ) = FALSE
-- THEN
/*
|| return as the current set of books is NON-INR based
*/
-- RETURN;
INSERT INTO JAI_AR_TRXS
(
Customer_Trx_ID,
Organization_ID,
Location_ID,
Trx_Number,
Update_RG_Flag,
Once_Completed_Flag,
Batch_Source_ID,
Set_Of_Books_ID,
Primary_Salesrep_ID,
Invoice_Currency_Code,
Exchange_Rate_Type,
Exchange_Date,
Exchange_Rate,
Created_From,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
legal_entity_id /* rallamse bug#4448789 */
)
VALUES
(
v_header_id,
v_organization_id,
v_location_id,
v_trx_number,
'Y',
'Y',
v_batch_source_id,
v_books_id,
v_salesrep_id,
c_from_currency_code,
c_conversion_type,
c_conversion_date,
c_conversion_rate,
v_created_from,
v_creation_date,
v_created_by,
v_last_update_date,
v_last_updated_by,
v_last_update_login,
ln_legal_entity_id /* rallamse bug#4448789 */
);
INSERT INTO JAI_AR_TRX_TAX_LINES
(
tax_line_no,
customer_trx_line_id,
link_to_cust_trx_line_id,
precedence_1,
precedence_2,
precedence_3,
precedence_4,
precedence_5,
precedence_6,
precedence_7,
precedence_8,
precedence_9,
precedence_10,
tax_id,
tax_rate,
qty_rate,
uom,
tax_amount,
func_tax_amount,
base_tax_amount,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
VALUES(
Tax_Rec.tax_line_no,
ra_customer_trx_lines_s.NEXTVAL,
v_customer_trx_line_id,
Tax_Rec.precedence_1,
Tax_Rec.precedence_2,
Tax_Rec.precedence_3,
Tax_Rec.precedence_4,
Tax_Rec.precedence_5,
Tax_Rec.precedence_6,
Tax_Rec.precedence_7,
Tax_Rec.precedence_8,
Tax_Rec.precedence_9,
Tax_Rec.precedence_10,
Tax_Rec.tax_id,
Tax_Rec.tax_rate,
Tax_Rec.qty_rate,
Tax_Rec.uom,
NVL((Tax_Rec.tax_amount * v_quantity_credited) / v_quantity,0),
v_func_tax_amount,
NVL((Tax_Rec.base_tax_amount * v_quantity_credited) / v_quantity,0),
v_creation_date,
v_created_by,
v_last_update_date,
v_last_updated_by,
v_last_update_login
);
INSERT INTO JAI_AR_TRX_LINES
(customer_trx_line_id,
line_number,
customer_trx_id,
description,
inventory_item_id,
unit_code,
quantity,
tax_category_id,
auto_invoice_flag,
unit_selling_price,
line_amount,
tax_amount,
total_amount,
assessable_value,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
service_type_code -- added by csahoo for bug#5879769
)
VALUES(
v_customer_trx_line_id,
pr_new.line_number,
v_header_id,
pr_new.description,
pr_new.inventory_item_id,
pr_new.uom_code,
pr_new.quantity_credited,
v_tax_category_id,
'Y',
pr_new.unit_selling_price,
v_line_amount,
v_tax_amount,
(v_line_amount + v_tax_amount),
v_assessable_value,
v_creation_date,
v_created_by,
v_last_update_date,
v_last_updated_by,
v_last_update_login,
v_service_type -- added by csahoo for bug#5879769
);
DELETE JAI_AR_TRX_INS_LINES_T
WHERE Customer_Trx_Line_Id = v_customer_trx_line_id;
UPDATE JAI_AR_TRX_LINES
SET Customer_Trx_Id = v_header_id
WHERE Customer_Trx_Line_Id = v_customer_trx_line_id;
DELETE JAI_AR_TRXS
WHERE customer_trx_id = v_old_customer_trx_id;
UPDATE JAI_AR_TRXS
SET line_amount = NVL(line_amount, 0 ) + NVL(v_line_amount,0),
tax_amount = NVL(tax_amount,0) + NVL(v_excise_amount,0),
total_amount = NVL(total_amount,0) + NVL(v_line_amount,0) + NVL(v_excise_amount,0)
WHERE customer_trx_id = v_header_id;
select gl_date
from ra_cust_trx_line_gl_dist_all
where customer_trx_id = cp_customer_trx_id
and account_class = jai_constants.account_class_rec
and latest_rec_flag = jai_constants.yes;
SELECT REGIME_ID
FROM JAI_RGM_DEFINITIONS
WHERE REGIME_CODE = CP_REGIME_CODE;
SELECT COUNT(1)
FROM JAI_CMN_TAX_CTG_LINES CATL
,JAI_CMN_TAXES_ALL CODES
,JAI_REGIME_TAX_TYPES_V JRTTV
WHERE CATL.TAX_CATEGORY_ID = CP_TAX_CATEGORY_ID
AND CATL.TAX_ID = CODES.TAX_ID
AND CODES.TAX_TYPE = JRTTV.TAX_TYPE
AND JRTTV.REGIME_CODE = CP_REGIME_CODE;
v_last_update_date Date; -- := pr_new.last_update_date;
v_last_updated_by Number; -- := pr_new.last_updated_by;
v_last_update_login Number; -- := pr_new.last_update_login;
SELECT A.org_id,
A.ship_to_customer_id,
NVL(A.ship_to_site_use_id,0)
FROM RA_CUSTOMER_TRX_ALL A
WHERE customer_trx_id = v_header_id;
SELECT set_of_books_id,
invoice_currency_code,
exchange_rate_type,
exchange_date,
exchange_rate, trx_date
FROM RA_CUSTOMER_TRX_ALL
WHERE customer_trx_id = v_header_id;
SELECT cust_acct_site_id address_id
FROM HZ_CUST_SITE_USES_ALL A /* Removed ra_site_uses_all for Bug# 4434287 */
WHERE A.site_use_id = NVL(p_ship_to_site_use_id,0);
SELECT A.tax_id, A.tax_rate, A.tax_amount tax_amt,b.tax_type t_type
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
ORDER BY 1;
SELECT A.list_price, a.unit_code
FROM so_price_list_lines A, JAI_CMN_CUS_ADDRESSES B
WHERE A.price_list_id = B.price_list_id
AND B.customer_id = p_customer_id
AND B.address_id = p_address_id
AND A.inventory_item_id = p_inventory_item_id
AND a.unit_code = v_uom_code
AND NVL(a.end_date_active,SYSDATE) >= p_trx_date;
SELECT organization_id
FROM JAI_AR_TRX_APPS_RELS_T ;/*altered by rchandan for bug#4479131*/
SELECT A.organization_id
FROM JAI_AR_TRXS A, RA_CUSTOMER_TRX_ALL B
WHERE A.trx_number = B.recurred_from_trx_number AND B.customer_trx_id = v_header_id;
SELECT created_from , trx_date
FROM ra_customer_trx_all
WHERE customer_trx_id = v_header_id;
SELECT once_completed_flag
FROM JAI_AR_TRXS
WHERE customer_trx_id = v_header_id;
SELECT rowid
FROM RA_CUSTOMER_TRX_ALL
WHERE customer_trx_id = v_header_id;
SELECT line_amount
FROM JAI_AR_TRX_LINES
WHERE CUSTOMER_TRX_ID = pr_old.CUSTOMER_TRX_ID
AND CUSTOMER_TRX_LINE_ID = pr_old.CUSTOMER_TRX_LINE_ID;
SELECT DISTINCT gl_date
FROM RA_CUST_TRX_LINE_GL_DIST_ALL
WHERE CUSTOMER_TRX_LINE_ID = v_customer_trx_line_id;
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_header_id
And NVL(a.org_id,0) = NVL(pr_new.org_id,0);
/* This Trigger fires , when you insert a record in RA_CUSTOMER_TRX_LINES_ALL */
/*------------------------------------------------------------------------------------------
FILENAME: JA_IN_AR_LINES_INSERT_TRG.sql
CHANGE HISTORY:
S.No Date Author and Details
--------------------------------------------------------------------------------------------
1. 2001/06/22 Anuradha Parthasarathy
Code commented and added to improve performance.
2. 2003/03/12 Sriram - Bug # 2846277 - File Version 615.1
In case where seup business group setup is done , inventory organization is a value 0.
This was causing the trigger to return because of code comparison . Hence changed the
comparison to a large value such as 999999
3. 2004/10/17 ssumaith - bug# 3957682 - file version 115.1
Tax defaultation was not happening for a manual credit memo created without reference to
an invoice.
Added code in the trigger to ensure that code does not return when the transaction type is
either 'INV' or 'CM'. Earlier the check was for 'INV' only.
4. 29-nov-2004 ssumaith - bug# 4037690 - File version 115.2
Check whether india localization is being used was done using a INR check in every trigger.
This check has now been moved into a new package and calls made to this package from this trigger
If the function jai_cmn_utils_pkg.check_jai_exists returns true it means INR is the set of books currency ,
Hence if this function returns FALSE , control should return
5. 16/Mar/05 ssumaith - bug# 4245053 file version 115.3
uptake of the vat assessable value has been done in this trigger.
A call to the jai_general_pkg.JA_IN_VAT_ASSESSABLE_VALUE has been made passing the parameters
to get the vat assessable value to the tax calculation routines and update the vat assessable
value in the JAI_OM_OE_SO_LINES table.
This vat assessable value is sent as an additional parameter to the various procedures
such as jai_om_tax_pkg.recalculate_oe_taxes , jai_cmn_tax_defaultation_pkg.ja_in_calc_prec_taxes
Hence, its mandatory that with this object all the other objects in this patch as well as the base bug
need to be sent.
VAt assessable value , vat exemption related columns (type , refno and date) have also been copied
from the source line in the case of a copy order / split scenario.
Dependency due to this bug - Huge
This patch should always be accompanied by the VAT consolidated patch - 4245089
6. 08-Jun-2005 File Version 116.1. This Object is Modified to refer to New DB Entity names in place of Old
DB Entity as required for CASE COMPLAINCE.
7. 10-Jun-2005 File Version: 116.2
Removal of SQL LITERALs is done
8. 8-Jul-2005 rchandan for bug#4479131. File Version: 116.3
The object is modified to eliminate the paddr usage.
9. 23-Aug-2005 Ramananda for bug#4567935 (115 bug3671351). File version 120.2
Problem
-------
Excise taxes not getting calculated on assessable price in AR INVOICE.
Fix
---
Commented the code to calculate the assessable price and added a call
to the jai_om_utils_pkg.get_oe_assessable_value function to calculate the assessable price
correctly through various levels of defaultation.
10. 12-Nov-2008 CSahoo for bug#6012465, File Version 120.7.12000000.8
Issue: ASSESSABLE PRICE FOR ITEMS DOES NOT APPEAR IN AR
Reason: While creation of Manual Invoices, attachment of Item / Tax category list is checked before the
calculation of excise / vat assessable value. If any category list is not attached,
assessable value is shown as zero.
Fix: Tax Category check is moved after the calculation of excise and vat assessable value.
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_ar_lines_insert_trg.sql
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
115.2 4037690 IN60105D2 ja_in_util_pkg_s.sql 115.0 ssumaith 29-Nov-2004 Call to this function.
ja_in_util_pkg_s.sql 115.0 ssumaith
115.3 4245053 IN60106 + ssumaith Service Tax and VAT Infrastructure are created
4146708 + based on the bugs - 4146708 and 4545089 respectively.
4245089
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
/* --Ramananda for File.Sql.35, start */
v_header_id := pr_new.customer_trx_id;
v_last_update_date := pr_new.last_update_date;
v_last_updated_by := pr_new.last_updated_by;
v_last_update_login := pr_new.last_update_login;
UPDATE JAI_AR_TRXS
SET line_amount = nvl(line_amount,0) - nvl(v_old_line_amount,0),
tax_amount = nvl(tax_amount,0) - nvl(v_old_tax_tot,0),
total_amount = nvl(total_amount,0) - (nvl(v_old_line_amount,0) + nvl(v_old_tax_tot,0))
WHERE customer_trx_id = pr_old.CUSTOMER_TRX_ID;
DELETE JAI_AR_TRX_TAX_LINES
WHERE LINK_TO_CUST_TRX_LINE_ID = pr_old.CUSTOMER_TRX_LINE_ID;
DELETE JAI_AR_TRX_LINES
WHERE CUSTOMER_TRX_ID = pr_old.CUSTOMER_TRX_ID
AND CUSTOMER_TRX_LINE_ID = pr_old.CUSTOMER_TRX_LINE_ID;
v_last_update_date ,
v_last_updated_by ,
v_last_update_login,
NULL,
ln_vat_assessable_value, /* added by ssumaith - bug# 4245053*/
p_thhold_cat_base_tax_typ => jai_constants.tax_type_tcs , -- Date 26-feb-2006 added by sacsethi for bug 5631784
p_threshold_tax_cat_id => ln_threshold_tax_cat_id,-- Date 26-feb-2006 added by sacsethi for bug 5631784
p_source_trx_type => null,-- Date 26-feb-2006 added by sacsethi for bug 5631784
p_source_table_name => null,-- Date 26-feb-2006 added by sacsethi for bug 5631784
p_action => jai_constants.default_taxes-- Date 26-feb-2006 added by sacsethi for bug 5631784
);
UPDATE JAI_AR_TRXS
SET line_amount = NVL(line_amount,0) + NVL(v_line_amount,0),
total_amount = NVL(total_amount,0)+ NVL(v_tot_amt,0) ,
tax_amount = NVL(tax_amount,0) + NVL(v_total_tax_amount,0)
WHERE JAI_AR_TRXS.customer_trx_id = v_header_id;
INSERT INTO JAI_AR_TRX_LINES(
customer_trx_line_id,
line_number,
customer_trx_id,
description,
inventory_item_id,
unit_code,
quantity,
tax_category_id,
auto_invoice_flag ,
unit_selling_price,
line_amount,
gl_date,
tax_amount,
total_amount,
assessable_value,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
vat_assessable_value, /* added by ssumaith - bug# 4245053*/
service_type_code -- Added by csahoo for Bug#5879769
)
VALUES(
v_customer_trx_line_id,
pr_new.line_number,
v_header_id,
pr_new.description,
pr_new.inventory_item_id,
pr_new.uom_code,
--NVL(pr_new.quantity_invoiced,0), -- commented - bug# 3957682
nvl(v_quantity,0), -- added - bug# 3957682
v_tax_category_id,
'N',
pr_new.unit_selling_price,
v_line_amount,
v_gl_date,
v_line_tax_amount,
(v_line_amount + v_line_tax_amount),
v_price_list,
v_creation_date,
v_created_by,
v_last_update_date,
v_last_updated_by,
v_last_update_login,
ln_vat_assessable_value, /* added by ssumaith - bug# 4245053*/
v_service_type -- Added by csahoo for Bug#5879769
);
lv_appl_src := 'JA_IN_AR_LINES_INSERT_TRG';
INSERT INTO JAI_CMN_ERRORS_T
( APPLICATION_SOURCE,
error_message,
additional_error_mesg,
creation_date,
created_by ,
-- added, Harshita for Bug 4866533
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
values
(
lv_appl_src, /*'JA_IN_AR_LINES_INSERT_TRG', Ramananda for removal of SQL LITERALs */
VAR_SQLERRM,
lv_add_err, /* 'CUSTOMER_TRX_ID = ' || TO_CHAR(pr_new.CUSTOMER_TRX_ID) || ' CUSTOMER_TRX_LINE_ID = ' || TO_CHAR(pr_new.CUSTOMER_TRX_LINE_ID) , */
sysdate,
user ,
-- added, Harshita for Bug 4866533
fnd_global.user_id,
sysdate
);
v_last_update_date Date ; -- := pr_new.last_update_date;
v_last_updated_by Number; -- := pr_new.last_updated_by;
v_last_update_login Number; -- := pr_new.last_update_login;
SELECT A.org_id,A.ship_to_customer_id,NVL(A.ship_to_site_use_id,0)
FROM RA_CUSTOMER_TRX_ALL A
WHERE customer_trx_id = v_header_id;
SELECT set_of_books_id , invoice_currency_code,
exchange_rate_type, exchange_date,
exchange_rate , trx_date
FROM RA_CUSTOMER_TRX_ALL
WHERE customer_trx_id = v_header_id;
select A.tax_id, A.tax_rate, A.tax_amount tax_amt,b.tax_type t_type
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
order by 1;
SELECT organization_id
FROM JAI_AR_TRX_APPS_RELS_T;/*altered by rchandan for bug#4479131*/
SELECT organization_id
FROM JAI_AR_TRXS
WHERE trx_number = (
SELECT recurred_from_trx_number
FROM RA_CUSTOMER_TRX_ALL
WHERE customer_trx_id = v_header_id
);
SELECT created_from
FROM ra_customer_trx_all
WHERE customer_trx_id = v_header_id;
SELECT rowid FROM RA_CUSTOMER_TRX_ALL
WHERE customer_trx_id = v_header_id;*//*commented by rchandan for bug#4479131*/
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_header_id
And (a.org_id = pr_new.org_id
OR
(a.org_id is null and pr_new.org_id is null )) ; /* Modified by Ramananda for removal of SQL LITERALs */
FILENAME: JA_IN_AR_CM_LINES_UPDATE_TRG.sql
CHANGE HISTORY:
S.No Date Author and Details
1. 2001/06/22 Anuradha Parthasarathy
Code commented and added to improve performance.
2. 2003/03/12 Sriram - Bug # 2846277 File Version - 615.1
In case where setup business group setup is done , inventory organization is a value 0.
This was causing the trigger to return because of code comparison . Hence changed the
comparison to a large value such as 999999
3. 2004/10/21 ssumaith - bug# 3957682 File Version - 115.1
Added code to return the control when the condition = 'ARXTWMAI' and transaction_Type = 'CM'
because tax defaultation is already taken care of as part of the trigger ja_in_ar_lines_update_trg
4. 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
5. 13-Jun-2005 File Version: 116.2
Ramananda for bug#4428980. Removal of SQL LITERALs is done
6. 8-Jul-2005 File Version: 116.3
rchandan for bug#4479131
The object is modified to eliminate the paddr usage.
--------------------------------------------------------------------------------------------*/
/* --Ramananda for File.Sql.35 */
v_header_id := pr_old.customer_trx_id;
v_last_update_date := pr_new.last_update_date;
v_last_updated_by := pr_new.last_updated_by;
v_last_update_login := pr_new.last_update_login;
'AR_LINES_UPDATE' ,
null ,
v_header_id ,
v_customer_trx_line_id ,
v_price_list_val ,
v_line_tax_amount ,
v_converted_rate ,
v_inventory_item_id ,
NVL(pr_new.quantity_credited,0) ,
pr_new.uom_code ,
NULL ,
NULL ,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login
);
UPDATE JAI_AR_TRXS
SET line_amount = NVL(line_amount,0) + NVL(v_line_amount,0) - NVL(v_old_amount,0),
total_amount = NVL(total_amount,0)+ NVL(v_tot_amt,0) - nvl(v_old_amount,0) - NVL(v_old_tax_tot,0),
tax_amount = NVL(tax_amount,0) + NVL(v_tax_tot,0) - NVL(v_old_tax_tot,0),
creation_date = v_creation_date,
created_by = v_created_by,
last_update_date = v_last_update_date,
last_updated_by = v_last_updated_by,
last_update_login = v_last_update_login
WHERE customer_trx_id = v_header_id;
UPDATE JAI_AR_TRX_LINES
SET description = pr_new.description,
inventory_item_id = pr_new.inventory_item_id,
unit_code = pr_new.uom_code,
quantity = pr_new.quantity_credited,
auto_invoice_flag = 'N',
tax_category_id = v_tax_category_id,
unit_selling_price = pr_new.unit_selling_price,
line_amount = v_line_amount,
tax_amount = v_line_tax_amount,
total_amount = v_line_amount + v_line_tax_amount,
creation_date = v_creation_date,
created_by = v_created_by,
last_update_date = v_last_update_date,
last_updated_by = v_last_updated_by,
last_update_login = v_last_update_login
WHERE customer_trx_line_id = pr_old.customer_trx_line_id
AND customer_trx_id = v_header_id;
v_last_update_date Date; -- := pr_new.last_update_date;
v_last_updated_by Number; -- := pr_new.last_updated_by;
v_last_update_login Number; -- := pr_new.last_update_login;
SELECT A.org_id,A.ship_to_customer_id,NVL(A.ship_to_site_use_id,0)
FROM RA_CUSTOMER_TRX_ALL A
WHERE customer_trx_id = v_header_id;
SELECT set_of_books_id, invoice_currency_code, exchange_rate_type, exchange_date,
exchange_rate, trx_date
FROM RA_CUSTOMER_TRX_ALL
WHERE customer_trx_id = v_header_id;
SELECT line_no
FROM JAI_CMN_TAX_CTG_LINES A
WHERE A.tax_category_id = p_tax_category_id
ORDER BY line_no;
SELECT cust_acct_site_id address_id
FROM hz_cust_site_uses_all A
WHERE A.site_use_id = p_ship_to_site_use_id; /* Modified by Ramananda for removal of SQL LITERALs */
select A.tax_id, A.tax_rate, A.tax_amount tax_amt,b.tax_type t_type
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
order by 1;
SELECT organization_id
FROM JAI_AR_TRX_APPS_RELS_T;/*altered by rchandan for paddr limination*/
SELECT organization_id
FROM JAI_AR_TRXS
WHERE trx_number = (
SELECT recurred_from_trx_number
FROM RA_CUSTOMER_TRX_ALL
WHERE customer_trx_id = v_header_id
);
SELECT created_from , trx_date
FROM ra_customer_trx_all
WHERE customer_trx_id = v_header_id;
SELECT rowid FROM RA_CUSTOMER_TRX_ALL
WHERE customer_trx_id = v_header_id;*//*commented by rchandan for bug#4479131*/
SELECT once_completed_flag
FROM JAI_AR_TRXS
WHERE customer_trx_id = v_header_id;
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_header_id
And ( a.org_id = pr_new.org_id
OR
(a.org_id is NULL AND pr_new.org_id is NULL )); /* Modified by Ramananda for removal of SQL LITERALs */
FILENAME: JA_IN_AR_LINES_UPDATE_TRG.sql
CHANGE HISTORY:
S.No Date Author and Details
------------------------------------------------------------------------------------------
1. 2001/06/22 Anuradha Parthasarathy
Code commented and added to improve performance.
2. 2003/03/12 Sriram - Bug # 2846277 - File Version 615.1
If inventory organization is 0 , which is possible when setup business group
is done , it was causing the trigger to return . Hence comparing the nvl against
a large value such as 999999.
3. 2004/21/10 ssumaith - bug# 3957682 - file version 115.1
For a manual credit memo when quantity is changed, taxes are not getting recalculated.
This was because this trigger was written to fire only for invoice quantity change.
Changes done:
1) Added a new when clause to ensure that trigger fires for credit memo quantity change
2) Added code to use the quantity_invoiced for an invoice and quantity_credited for a credit memo.
4. 29-nov-2004 ssumaith - bug# 4037690 - File version 115.2
Check whether india localization is being used was done using a INR check in every trigger.
This check has now been moved into a new package and calls made to this package from this trigger
If the function jai_cmn_utils_pkg.check_jai_exists returns true it means INR is the set of books currency ,
Hence if this function returns FALSE , control should return.
5. 17/03/2005 ssumaith - bug# 4245053 - File Version 115.3
uptake of the vat assessable value has been done in this trigger.
A call to the jai_general_pkg.JA_IN_VAT_ASSESSABLE_VALUE has been made passing the parameters
to get the vat assessable value to the tax calculation routines and update the vat assessable
value in the JAI_OM_OE_SO_LINES table.
This vat assessable value is sent as an additional parameter to the various procedures
such as jai_om_tax_pkg.recalculate_oe_taxes , jai_cmn_tax_defaultation_pkg.ja_in_calc_prec_taxes
Hence, its mandatory that with this object all the other objects in this patch as well as the base bug
need to be sent.
VAt assessable value , vat exemption related columns (type , refno and date) have also been copied
from the source line in the case of a copy order / split scenario.
Dependency due to this bug - Huge
This patch should always be accompanied by the VAT consolidated patch - 4245089
6. 08-Jun-2005 File Version 116.1. This Object is Modified to refer to New DB Entity names in place of Old
DB Entity as required for CASE COMPLAINCE.
7. 13-Jun-2005 Ramananda for bug#4428980. File Version: 116.2
Removal of SQL LITERALs is done
8. 8-Jul-2005 rchandan for bug#4479131. File Version: 116.3
The object is modified to eliminate the paddr usage.
9. 23-Aug-2005 Ramananda for bug# 4567935 (115 bug3671351). File Version 120.2
Problem:
-------
Excise taxes not getting calculated on assessable price in AR INVOICE.
Fix:
----
Commented the code to calculate the assessable price and added a call
to the jai_om_utils_pkg.get_oe_assessable_value function to calculate the assessable price
correctly through various levels of defaultation
10. 2-FEB-2006 SACSETHI for bug 5631784 , forward porting bug
for TCS Enchancement
11. 18-Nov-2008 JMEENA for bug#6414523
Issue: When selling price is changed, Excise Assessable Value should be updated correctly
Reason: Excise Assessable value is derived but not updated in the table ja_in_ra_customer_trx_lines
Fix: Modifed procedure ARU_T2. Added column assessable_value in the update statement of table JAI_AR_TRX_LINES.
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_ar_lines_update_trg.sql
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
115.2 4037690 IN60105D2 ja_in_util_pkg_s.sql 115.0 ssumaith 29-Nov-2004 Call to this function.
ja_in_util_pkg_s.sql 115.0 ssumaith
115.3 4245053 IN60106 + ssumaith Service Tax and VAT Infrastructure are created
4146708 + based on the bugs - 4146708 and 4545089 respectively.
4245089
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
/* --Ramananda for File.Sql.35 */
v_header_id := pr_old.customer_trx_id;
v_last_update_date := pr_new.last_update_date;
v_last_updated_by := pr_new.last_updated_by;
v_last_update_login := pr_new.last_update_login;
jai_ar_utils_pkg.recalculate_tax('AR_LINES_UPDATE' ,
v_tax_category_id ,
v_header_id,
v_customer_trx_line_id,
v_price_list_val ,
v_line_tax_amount ,
v_converted_rate,
v_inventory_item_id ,
NVL(v_new_quantity,0),
pr_new.uom_code ,
NULL ,
NULL ,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login,
ln_vat_assessable_value /* added by ssumaith - bug# 4245053*/
);
/* Update The Localizaed Header Table with the Line amount, Tax amount, Total Amount */
FOR excise_cal_rec in excise_cal_cur
LOOP
IF excise_cal_rec.t_type IN ('Excise') THEN
v_excise := nvl(v_excise,0) + excise_cal_rec.tax_amt;
UPDATE JAI_AR_TRXS
SET line_amount = NVL(line_amount,0) + NVL(v_line_amount,0) - NVL(v_old_amount,0),
total_amount = NVL(total_amount,0)+ NVL(v_tot_amt,0) - nvl(v_old_amount,0) - NVL(v_old_tax_tot,0),
tax_amount = NVL(tax_amount,0) + NVL(v_tax_tot,0) - NVL(v_old_tax_tot,0),
creation_date = v_creation_date,
created_by = v_created_by,
last_update_date = v_last_update_date,
last_updated_by = v_last_updated_by,
last_update_login = v_last_update_login
WHERE customer_trx_id = v_header_id;
UPDATE JAI_AR_TRX_LINES
SET description = pr_new.description,
inventory_item_id = pr_new.inventory_item_id,
unit_code = pr_new.uom_code,
quantity = v_new_quantity,
auto_invoice_flag = 'N',
tax_category_id = v_tax_category_id,
unit_selling_price = pr_new.unit_selling_price,
line_amount = v_line_amount,
tax_amount = v_line_tax_amount,
total_amount = v_line_amount + v_line_tax_amount,
creation_date = v_creation_date,
created_by = v_created_by,
last_update_date = v_last_update_date,
last_updated_by = v_last_updated_by,
last_update_login = v_last_update_login,
assessable_value = v_price_list -- Added by JMEENA for Bug#6414523( FP 6318850)
WHERE customer_trx_line_id = pr_old.customer_trx_line_id
AND customer_trx_id = v_header_id;
select *
from jai_cmn_document_taxes
where source_doc_type = jai_pa_billing_pkg.gv_source_projects
and source_doc_line_id = cpn_draft_invoice_line_id
order by tax_line_no;
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 = r_new.customer_trx_id
AND NVL(A.org_id,0) = NVL(r_new.org_id,0);
select trx_number, trx_date, batch_source_id, set_of_books_id
, primary_salesrep_id ,invoice_currency_code, exchange_rate_type
, exchange_date, exchange_rate
, legal_entity_id -- 6012570
from ra_customer_trx_all
where customer_trx_id = r_new.customer_trx_id;
select customer_trx_id
from jai_ar_trxs
where customer_trx_id = r_new.customer_trx_id;
select customer_trx_line_id
from jai_ar_trx_lines
where customer_trx_line_id = r_new.customer_trx_line_id;
select project_id
from pa_projects_all
where segment1 = cp_project_code;
select draft_invoice_id, organization_id, location_id
, excise_register_type
, excise_invoice_no
, excise_invoice_date
, vat_invoice_no
, vat_invoice_date
from jai_pa_draft_invoices
where project_id = cpn_project_id
and draft_invoice_num = cpn_draft_invoice_num;
select
draft_invoice_id
, draft_invoice_line_id
, tax_category_id
, service_type_code
from jai_pa_draft_invoice_lines
where draft_invoice_id = cpn_draft_invoice_id
and line_num = cpn_line_num;
/* insert into jai_ar_trxs */
insert into jai_ar_trxs(
customer_trx_id
, organization_id
, location_id
, trx_number
, update_rg_flag
, update_rg23d_flag
, once_completed_flag
, batch_source_id
, set_of_books_id
, primary_salesrep_id
, invoice_currency_code
, exchange_rate_type
, exchange_date
, exchange_rate
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, vat_invoice_no
, vat_invoice_date
, legal_entity_id -- 6012570
) values (
r_new.customer_trx_id
, r_jai_pa_draft_invoice.organization_id
, r_jai_pa_draft_invoice.location_id
, r_ra_customer_trx.trx_number
, 'Y'
, 'Y'
, 'N'
, r_ra_customer_trx.batch_source_id
, r_ra_customer_trx.set_of_books_id
, r_ra_customer_trx.primary_salesrep_id
, r_ra_customer_trx.invoice_currency_code
, r_ra_customer_trx.exchange_rate_type
, r_ra_customer_trx.exchange_date
, r_ra_customer_trx.exchange_rate
, sysdate
, r_new.created_by
, sysdate
, r_new.last_updated_by
, r_new.last_update_login
, r_jai_pa_draft_invoice.vat_invoice_no
, r_jai_pa_draft_invoice.vat_invoice_date
, r_ra_customer_trx.legal_entity_id -- 6012570
);
/* insert into ja_in_ra_cust_trx_tax_lines */
for tax_rec in c_draft_inv_line_taxes(r_jai_pa_draft_inv_line.draft_invoice_line_id) loop
ln_tax_amount := round(tax_rec.tax_amt,2);
insert into jai_ar_trx_tax_lines(
tax_line_no ,
customer_trx_line_id ,
link_to_cust_trx_line_id ,
precedence_1 ,
precedence_2 ,
precedence_3 ,
precedence_4 ,
precedence_5 ,
precedence_6 ,
precedence_7 ,
precedence_8 ,
precedence_9 ,
precedence_10 ,
tax_id ,
tax_rate ,
qty_rate ,
uom ,
tax_amount ,
func_tax_amount ,
base_tax_amount ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
) values(
tax_rec.tax_line_no ,
ra_customer_trx_lines_s.nextval ,
ln_line_customer_trx_line_id ,
tax_rec.precedence_1 ,
tax_rec.precedence_2 ,
tax_rec.precedence_3 ,
tax_rec.precedence_4 ,
tax_rec.precedence_5 ,
tax_rec.precedence_6 ,
tax_rec.precedence_7 ,
tax_rec.precedence_8 ,
tax_rec.precedence_9 ,
tax_rec.precedence_10 ,
tax_rec.tax_id ,
tax_rec.tax_rate ,
tax_rec.qty_rate ,
tax_rec.uom ,
ln_tax_amount ,
ln_func_tax_amount ,
ln_base_tax_amount, /* Complete this round(v_base_tax_amount,2) , */
sysdate ,
r_new.created_by,
sysdate ,
r_new.last_updated_by ,
r_new.last_update_login
);
/* insert into jai_ar_trx_lines */
open c_jai_ra_customer_trx_line;
INSERT INTO jai_ar_trx_lines (
customer_trx_line_id ,
line_number ,
customer_trx_id ,
description ,
payment_register ,
excise_invoice_no ,
preprinted_excise_inv_no ,
excise_invoice_date ,
inventory_item_id ,
unit_code ,
quantity ,
tax_category_id ,
auto_invoice_flag ,
unit_selling_price ,
line_amount ,
tax_amount ,
total_amount ,
assessable_value ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login ,
excise_exempt_type ,
excise_exempt_refno ,
excise_exempt_date ,
ar3_form_no ,
ar3_form_date ,
vat_exemption_flag ,
vat_exemption_type ,
vat_exemption_date ,
vat_exemption_refno ,
vat_assessable_value ,
service_type_code
) VALUES (
ln_line_customer_trx_line_id ,
r_new.line_number ,
r_new.customer_trx_id ,
r_new.description ,
r_jai_pa_draft_invoice.excise_register_type ,
r_jai_pa_draft_invoice.excise_invoice_no ,
null , -- v_preprinted_excise_inv_no ,
r_jai_pa_draft_invoice.excise_invoice_date ,
r_new.inventory_item_id ,
r_new.uom_code ,
ln_quantity , -- 6369471, using ln_quanity instead of r_new.quantity_inoviced
r_jai_pa_draft_inv_line.tax_category_id ,
'Y' ,
r_new.unit_selling_price ,
ln_line_amount ,
ln_line_tax_amt ,
(ln_line_amount + ln_line_tax_amt) ,
null , -- v_assessable_value ,
sysdate ,
r_new.created_by ,
sysdate ,
r_new.last_updated_by ,
r_new.last_update_login ,
null , -- v_excise_exempt_type ,
null , -- v_excise_exempt_refno ,
null , -- v_excise_exempt_date ,
null , -- v_ar3_form_no ,
null , -- v_ar3_form_date ,
null , -- lv_vat_exemption_flag ,
null , -- lv_vat_exemption_type ,
null , -- lv_vat_exemption_date ,
null , -- lv_vat_exemption_refno ,
null , -- ln_vat_assessable_value
r_jai_pa_draft_inv_line.service_type_code
);
update jai_ar_trxs
set tax_amount = nvl(tax_amount,0) + round( nvl(ln_line_tax_amt,0), 2)
, line_amount = nvl(line_amount,0) + round( nvl(ln_line_amount,0), 2)
, total_amount = nvl(total_amount,0) + round(nvl(ln_line_amount,0) + nvl(ln_line_tax_amt,0), 2)
, last_updated_by = r_new.last_updated_by
, last_update_date = sysdate
, last_update_login = r_new.last_update_login
where customer_trx_id = r_new.customer_trx_id;
SELECT hzcas.cust_acct_site_id
FROM hz_cust_site_uses_all hzcsu ,
hz_cust_acct_sites_all hzcas
WHERE hzcas.cust_acct_site_id = hzcsu.cust_acct_site_id
AND hzcsu.site_use_id = pn_party_site_id
AND hzcas.cust_account_id = pn_party_id ;
SELECT service_type_code
FROM JAI_CMN_CUS_ADDRESSES
WHERE customer_id = pn_cust_id
AND address_id = pn_address_id;
SELECT service_type_code
FROM JAI_CMN_VENDOR_SITES
WHERE vendor_id = cp_vendor_id
AND vendor_site_id = cp_vendor_site_id;