[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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_site_use_id
AND hzcas.cust_account_id = pn_customer_id ;
SELECT vat_Reg_no
FROM JAI_CMN_CUS_ADDRESSES
WHERE customer_id = pn_customer_id
AND address_id = pn_address_id;
v_update_rg Varchar2(1); -- := 'Y'; --Ramananda for File.Sql.35
v_update_rg23d_flag Varchar2(1); -- := 'Y'; --Ramananda for File.Sql.35
SELECT organization_id, location_id
FROM JAI_AR_TRX_APPS_RELS_T ; /* Modified cursor by rallamse bug#4479131 PADDR Elimination */
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 = pr_new.batch_source_id and order_flag = 'N');
SELECT organization_id,location_id
FROM JAI_AR_TRXS
WHERE trx_number = v_parent_trx_number;
Select type
From RA_CUST_TRX_TYPES_ALL
Where cust_trx_type_id = pr_new.cust_trx_type_id
And NVL(org_id,0) = NVL(pr_new.org_id,0);
Select organization_id, location_id, update_rg_flag
From JAI_AR_TRXS
Where customer_trx_id= pr_new.previous_customer_trx_id;
SELECT currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = c_sob;
v_update_rg := 'Y'; --Ramananda for File.Sql.35
v_update_rg23d_flag := 'Y'; --Ramananda for File.Sql.35
Insert Into JAI_AR_TRX_COPY_HDR_T
(TRX_NUMBER, CUSTOMER_TRX_ID, RECURRED_FROM_TRX_NUMBER, BATCH_SOURCE_ID,
CREATED_FROM, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE,
LAST_UPDATED_BY, LAST_UPDATE_LOGIN )
VALUES
(pr_new.trx_number, pr_new.CUSTOMER_TRX_ID, v_parent_trx_number, pr_new.BATCH_SOURCE_ID,
pr_new.CREATED_FROM, pr_new.CREATION_DATE, pr_new.CREATED_BY, pr_new.LAST_UPDATE_DATE,
pr_new.LAST_UPDATED_BY, pr_new.LAST_UPDATE_LOGIN);
Fetch localization_header_info Into v_org_id, v_loc_id, v_update_rg;
v_update_rg := 'N';
v_update_rg23d_flag := 'N';
v_update_rg23d_flag := 'Y';
v_update_rg := 'N';
v_update_rg := 'Y';
v_update_rg23d_flag := 'N';
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,
CREATED_FROM,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN ,
LAST_UPDATED_BY,
LEGAL_ENTITY_ID /* added rallamse bug#4448789 */
)
VALUES
(
pr_new.CUSTOMER_TRX_ID,
V_ORG_ID,
V_LOC_ID,
pr_new.TRX_NUMBER,
V_UPDATE_RG,
v_update_rg23d_flag,
pr_new.COMPLETE_FLAG,
pr_new.BATCH_SOURCE_ID,
pr_new.SET_OF_BOOKS_ID,
pr_new.PRIMARY_SALESREP_ID,
pr_new.INVOICE_CURRENCY_CODE,
pr_new.EXCHANGE_RATE_TYPE,
pr_new.EXCHANGE_DATE,
pr_new.EXCHANGE_RATE,
pr_new.CREATED_FROM,
pr_new.CREATION_DATE,
pr_new.CREATED_BY,
pr_new.LAST_UPDATE_DATE,
pr_new.LAST_UPDATE_LOGIN,
pr_new.LAST_UPDATED_BY,
pr_new.LEGAL_ENTITY_ID /* added rallamse bug#4448789 */
);
SELECT organization_id,location_id
FROM JAI_AR_TRXS
where customer_trx_id = pr_new.customer_trx_id;
SELECT TAX_INVOICE_NO
FROM JAI_AR_TRXS
WHERE Customer_Trx_Id = pr_new.customer_trx_id;
select 1
from JAI_AR_TRX_TAX_LINES
where link_to_cust_trx_line_id
in
(select customer_trx_line_id
from JAI_AR_TRX_LINES
where customer_trx_id = pr_new.customer_trx_id
)
and tax_id in
(select tax_id
from JAI_CMN_TAXES_ALL
where vat_flag = 'Y'
and org_id = pr_new.org_id
)
;
SELECT TYPE
FROM RA_CUST_TRX_TYPES_ALL
WHERE cust_trx_type_id = pr_new.cust_trx_type_id
AND NVL(org_id,0) = NVL(pr_new.org_id,0);
select vat_reg_no
from JAI_CMN_INVENTORY_ORGS
where organization_id = p_organization_id
and location_id = p_loc_id;
select current_number , jump_by , prefix
from JAI_CMN_INVENTORY_ORGS
where organization_id = p_organization_id
and location_id = p_loc_id;
update JAI_CMN_INVENTORY_ORGS
set last_update_date = last_update_date
where vat_reg_no = v_vat_reg_no;
update JAI_AR_TRXS
set tax_invoice_no = v_vat_invoice_no
where customer_trx_id = pr_new.customer_trx_id;
update JAI_CMN_INVENTORY_ORGS
set current_number = NVL(v_vat_start_num,0) ,
prefix = v_vat_prefix,
jump_by = v_vat_jump_by
where vat_Reg_no = v_vat_reg_no;
Select type
From RA_CUST_TRX_TYPES_ALL
Where cust_trx_type_id = pr_new.cust_trx_type_id
And NVL(org_id,0) = NVL(pr_new.org_id,0);
FILENAME:JA_IN_TRX_HDR_UPDATE_TRG.sql
CHANGE HISTORY:
S.No Date Author and Details
------------------------------------------------------------------------------------------
1. 10-Aug-2005 Aiyer bug 4545146 version 120.1
Issue:-
Deadlock on tables due to multiple triggers on the same table (in different sql files)
firing in the same phase.
Fix:-
Multiple triggers on the same table have been merged into a single file to resolve
the problem
The following files have been stubbed:-
jai_ar_rcta_t1.sql
jai_ar_rcta_t2.sql
jai_ar_rcta_t3.sql
jai_ar_rcta_t4.sql
jai_ar_rcta_t6.sql
jai_ar_rcta_t7.sql
jai_ar_rcta_t8.sql
jai_ar_rcta_t9.sql
Instead the new file jai_ar_rcta_t.sql has been created which contains all the triggers
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_trx_hdr_update_trg.sql
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
v_trx_number := pr_new.Trx_Number; --Ramananda for File.Sql.35
Update JAI_AR_TRXS
Set Customer_Trx_ID = pr_new.Customer_Trx_ID
Where Customer_Trx_ID = pr_old.Customer_Trx_ID;
Update JAI_AR_TRX_LINES
Set Customer_Trx_Id = pr_new.Customer_Trx_ID
Where Customer_Trx_ID = pr_old.Customer_Trx_ID;
Update JAI_AR_TRXS
Set Trx_Number = pr_new.Trx_Number
Where Customer_Trx_ID = pr_new.Customer_Trx_ID;
Update JAI_AR_TRXS
Set Trx_Number = pr_new.Trx_Number
Where Customer_Trx_ID = pr_new.Customer_Trx_ID;
Update JAI_AR_TRXS
Set Trx_Number = pr_new.Trx_Number
, Once_Completed_Flag = NVL(pr_new.Complete_Flag,'N')
Where Customer_Trx_ID = pr_new.Customer_Trx_ID;
Update JAI_AR_TRXS
Set Trx_Number = pr_new.Trx_Number
Where Customer_Trx_ID = pr_new.Customer_Trx_ID;
SELECT
organization_id ,
location_id ,
vat_invoice_no ,
vat_invoice_date
FROM
JAI_AR_TRXS
WHERE
customer_trx_id = pr_new.customer_trx_id;
SELECT
type
FROM
ra_cust_trx_types_all
WHERE
cust_trx_type_id = pr_new.cust_trx_type_id AND
NVL(org_id,0) = NVL(pr_new.org_id,0);
SELECT
regime_id ,
regime_code
FROM
JAI_AR_TRX_TAX_LINES jcttl,
JAI_AR_TRX_LINES jctl,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v jrttv
WHERE
jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id AND
jctl.customer_trx_id = pr_new.customer_trx_id AND
jcttl.tax_id = jtc.tax_id AND
jtc.tax_type = jrttv.tax_type AND
regime_code = jai_constants.vat_regime AND
jtc.org_id = pr_new.org_id ;
SELECT
gl_date
FROM
ra_cust_trx_line_gl_dist_all
WHERE
customer_trx_id = pr_new.customer_trx_id AND
account_class = cp_acct_class AND /*--'REC' AND*/
latest_rec_flag = 'Y';
SELECT
vat_invoice_no
FROM
JAI_AR_TRXS
WHERE
customer_trx_id = pr_new.previous_customer_trx_id;
SELECT
1
FROM
JAI_AR_TRX_TAX_LINES jcttl,
JAI_AR_TRX_LINES jctl,
JAI_CMN_TAXES_ALL jtc
WHERE
jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id AND
jctl.customer_trx_id = pr_new.customer_trx_id AND
jcttl.tax_id = jtc.tax_id AND
jtc.org_id = pr_new.org_id AND
jtc.tax_type = cp_tax_type ;
SELECT
regime_id
FROM
jai_regime_tax_types_v
WHERE
regime_code = jai_constants.vat_regime
AND rownum = 1 ;
UPDATE
JAI_AR_TRXS
SET
vat_invoice_no = lv_vat_invoice_no ,
vat_invoice_date = nvl(ld_gl_date,pr_new.trx_date)
WHERE
customer_trx_id = pr_new.customer_trx_id ;
v_update_rg VARCHAR2(1);
v_update_rg_flag VARCHAR2(1); -- := 'N'; --Ramananda for File.Sql.35
v_last_update_date DATE; -- := pr_new.last_update_date; --Ramananda for File.Sql.35
v_last_updated_by NUMBER; -- := pr_new.last_updated_by; --Ramananda for File.Sql.35
v_last_update_login NUMBER; -- := pr_new.last_update_login; --Ramananda for File.Sql.35
v_update_rg23d_flag JAI_AR_TRXS.UPDATE_RG23D_FLAG%TYPE;/*Bug#4601570 bduvarag*/
SELECT
organization_id ,
location_id ,
once_completed_flag ,
decode(once_completed_flag,'A','RG23A','C','RG23C','P','PLA') register_type,
update_rg_flag, -- update_rg_flag added by sriram - bug# 3496577
nvl(update_rg23d_flag,'N') /*Bug 5040383*/
FROM
JAI_AR_TRXS
WHERE
customer_trx_id = v_customer_trx_id;
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_org_id AND location_id = p_loc_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 = pr_new.batch_source_id AND order_flag= 'N'); /* Modified by Ramananda for removal of SQL LITERALs */
SELECT MAX(A.fin_year)
FROM JAI_CMN_FIN_YEARS A
WHERE organization_id = p_org_id AND fin_active_flag = 'Y';
SELECT NVL(tax_amount,0) tax_amount
FROM JAI_AR_TRXS
WHERE customer_trx_id = v_customer_trx_id;
SELECT pref_rg23a , pref_rg23c , pref_pla
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_org_id AND
location_id = p_loc_id;
SELECT item_class, excise_flag
FROM JAI_INV_ITM_SETUPS
WHERE inventory_item_id = P_Item_Id AND
ORGANIZATION_ID = P_ORG_ID;
SELECT organization_id,location_id
FROM JAI_AR_TRXS
WHERE trx_number = v_parent_trx_number;
SELECT NVL(register_balance,0) register_balance
FROM JAI_OM_OE_BOND_TRXS
WHERE transaction_id = (SELECT MAX(A.transaction_id)
FROM JAI_OM_OE_BOND_TRXS A, JAI_OM_OE_BOND_REG_HDRS B
WHERE A.register_id = B.register_id
AND B.organization_id = p_org_id AND B.location_id = p_loc_id);
SELECT NVL(rg23d_register_balance,0) rg23d_register_balance
FROM JAI_OM_OE_BOND_TRXS
WHERE transaction_id = (SELECT MAX(A.transaction_id)
FROM JAI_OM_OE_BOND_TRXS A, JAI_OM_OE_BOND_REG_HDRS B
WHERE A.register_id = B.register_id
AND B.organization_id = p_org_id AND B.location_id = p_loc_id);
SELECT customer_trx_line_id, inventory_item_id, quantity,line_number,
excise_exempt_type, assessable_value
FROM JAI_AR_TRX_LINES
WHERE customer_trx_id = v_customer_trx_id
ORDER BY customer_trx_line_id;
SELECT SUM(quantity_applied)
FROM JAI_CMN_MATCH_RECEIPTS
WHERE ref_line_id = p_customer_trx_line_id;
SELECT
A.tax_id,
A.tax_rate t_rate,
A.tax_amount tax_amt,
A.func_tax_amount func_amt,
b.tax_type t_type,
b.stform_type,
A.tax_line_no
FROM JAI_AR_TRX_TAX_LINES A ,
JAI_CMN_TAXES_ALL B,
JAI_INV_ITM_SETUPS C
WHERE link_to_cust_trx_line_id = p_line_id
AND b.tax_type IN --('Excise','Addl. Excise','Other Excise') /* Modified by Ramananda for removal of SQL LITERALs */
(jai_constants.tax_type_excise,jai_constants.tax_type_exc_additional,jai_constants.tax_type_exc_other)
AND A.tax_id = b.tax_id
AND c.inventory_item_id = p_inventory_item_id
AND c.organization_id = p_org_id
--AND c.item_class IN ('RMIN','RMEX','CGEX','CGIN','CCEX','CCIN','FGIN','FGEX') /* Modified by Ramananda for removal of SQL LITERALs */
AND c.item_class IN ( jai_constants.item_class_rmin, jai_constants.item_class_rmex,
jai_constants.item_class_cgex, jai_constants.item_class_cgin,
jai_constants.item_class_ccex, jai_constants.item_class_ccin,
jai_constants.item_class_fgin, jai_constants.item_class_fgex
)
ORDER BY 1;
SELECT ssi_unit_flag, nvl(trading,'N')/*Bug#4601570 bduvarag*/
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_org_id AND
location_id = p_loc_id;
SELECT TYPE
FROM RA_CUST_TRX_TYPES_ALL
WHERE cust_trx_type_id = pr_new.cust_trx_type_id
AND (org_id = pr_new.org_id
OR
(org_id is null and pr_new.org_id is null)) ; /* Modified by Ramananda for removal of SQL LITERALs */
SELECT name
FROM Ra_Batch_Sources_All
WHERE batch_source_id = pr_new.batch_source_id
AND (org_id = pr_new.org_id
OR
( org_id is null AND pr_new.org_id is null)); /* Modified by Ramananda for removal of SQL LITERALs */
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 IN ( 'I','DOM','EXP') --ashish 20jun02
AND order_invoice_type = p_batch_name
AND register_code = p_register_code ; /* Modified by Ramananda for removal of SQL LITERALs */
SELECT NVL(MAX(GP1),0),NVL(MAX(GP2),0)
FROM JAI_CMN_RG_EXC_INV_NOS
WHERE organization_id = p_org_id
AND location_id = p_loc_id
AND fin_year = p_fin_year
AND transaction_type IS NULL
AND order_invoice_type IS NULL
AND register_code IS NULL;
SELECT meaning
FROM ja_lookups
WHERE lookup_code = p_register_code
AND lookup_type = cp_register_type; /*'JAI_REGISTER_TYPE'; Ramananda for removal of SQL LITERALs */
SELECT MODVAT_REVERSE_PERCENT
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = v_org_id
AND (location_id = v_location_id
OR
(location_id is NULL and v_location_id is NULL)); /* Modified by Ramananda for removal of SQL LITERALs */
SELECT A.tax_rate
FROM JAI_AR_TRX_TAX_LINES A, JAI_CMN_TAXES_ALL b
WHERE A.tax_id = b.tax_id
AND A.link_to_cust_trx_line_id = p_cust_trx_line_id
AND b.tax_type = jai_constants.tax_type_modvat_recovery ; --'Modvat Recovery';
which is selecting SOB from org_organization_definitions
as the SOB will never by null in base table.
*/
/* CODE ADDED TO INCORPORATE MASTER ORGANIZATION */
CURSOR ec_code_cur(p_organization_id IN NUMBER, p_location_id IN NUMBER) IS
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 nvl(sum(jrtl.func_tax_amount),0)
FROM JAI_AR_TRXS jtrx,
JAI_AR_TRX_LINES jtl,
JAI_AR_TRX_TAX_LINES jrtl,
JAI_CMN_TAXES_ALL jtc ,
JAI_INV_ITM_SETUPS jmtl
WHERE jrtl.tax_id = jtc.tax_id
AND jtrx.customer_trx_id = jtl.customer_Trx_id
AND jrtl.link_to_cust_trx_line_id = jtl.customer_trx_line_id
AND jtl.inventory_item_id = jmtl.inventory_item_id
AND jtrx.organization_id = jmtl.organization_id
--AND jmtl.item_class in ('RMIN','RMEX','CGEX','CGIN','FGIN','FGEX','CCIN','CCEX') /* Modified by Ramananda for removal of SQL LITERALs */
AND jmtl.item_class IN ( jai_constants.item_class_rmin, jai_constants.item_class_rmex,
jai_constants.item_class_cgex, jai_constants.item_class_cgin,
jai_constants.item_class_ccex, jai_constants.item_class_ccin,
jai_constants.item_class_fgin, jai_constants.item_class_fgex
)
AND jtc.tax_type like '%Excise%'
AND jtl.customer_trx_id = pr_new.customer_trx_id
AND jtrx.customer_trx_id = pr_new.customer_trx_id;
SELECT NVL(SUM(jrctl.func_tax_amount),0) tax_amount
FROM JAI_AR_TRX_TAX_LINES jrctl ,
JAI_CMN_TAXES_ALL jtc
WHERE jtc.tax_id = jrctl.tax_id
AND link_to_cust_trx_line_id IN
(SELECT customer_trx_line_id
FROM JAI_AR_TRX_LINES
WHERE customer_trx_id = pr_new.customer_trx_id
)
AND upper(jtc.tax_type) IN (upper(jai_constants.tax_type_cvd_edu_cess), upper(jai_constants.tax_type_exc_edu_cess));
SELECT
1
FROM
JAI_AR_TRX_INS_LINES_T
WHERE
customer_trx_id = cp_customer_trx_id ;
SELECT '1'
FROM Fnd_Concurrent_Requests FCR
WHERE FCR.argument1 = to_char(cp_customer_trx_id)
and fcr.status_code<> 'C'
and fcr.phase_code<>'C'
AND fcr.request_id IN (Select nvl(Max(Request_ID),-1)
From Fnd_Concurrent_Programs FCP,
Fnd_Application FA ,
Fnd_Concurrent_Requests FCR
Where FCR.Program_Application_ID = FA.Application_ID
AND FCR.Concurrent_Program_ID = FCP.Concurrent_Program_ID
AND FA.Application_ID = FCP.Application_ID
AND Concurrent_Program_Name = 'JAILINEGL'
AND FA.Application_Short_Name = 'JA'
AND FCR.argument1 = To_Char(cp_customer_trx_id) );
SELECT
vat_invoice_no
FROM JAI_AR_TRXS
WHERE customer_trx_id = pr_new.customer_trx_id;
SELECT
regime_id ,
regime_code
FROM
JAI_AR_TRX_TAX_LINES jcttl,
JAI_AR_TRX_LINES jctl,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v jrttv
WHERE
jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id AND
jctl.customer_trx_id = pr_new.customer_trx_id AND
jcttl.tax_id = jtc.tax_id AND
jtc.tax_type = jrttv.tax_type AND
regime_code = jai_constants.vat_regime AND
jtc.org_id = pr_new.org_id ;
SELECT
1
FROM
JAI_AR_TRX_TAX_LINES jcttl,
JAI_AR_TRX_LINES jctl,
JAI_CMN_TAXES_ALL jtc
WHERE
jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id AND
jctl.customer_trx_id = pr_new.customer_trx_id AND
jcttl.tax_id = jtc.tax_id AND
jtc.org_id = pr_new.org_id AND
jtc.tax_type = cp_tax_type ;
SELECT
regime_id
FROM
jai_regime_tax_types_v
WHERE
regime_code = jai_constants.vat_regime
AND rownum = 1 ;
SELECT
nvl(attribute_value ,'N') attribute_value
FROM
JAI_RGM_ORG_REGNS_V
WHERE
regime_code = jai_constants.vat_regime AND
attribute_type_code = jai_constants.regn_type_others AND /*'OTHERS' AND */
attribute_code = jai_constants.attr_code_same_inv_no AND /*'SAME_INVOICE_NO' AND */
organization_id = cp_organization_id AND
location_id = cp_location_id;
SELECT
excise_invoice_no
FROM
JAI_AR_TRX_LINES
WHERE
customer_trx_id = pr_new.customer_trx_id ;
SELECT
gl_date
FROM
ra_cust_trx_line_gl_dist_all
WHERE
customer_trx_id = pr_new.customer_trx_id AND
account_class = cp_account_class AND /* 'REC' AND -- Ramananda for removal of SQL LITERALs */
latest_rec_flag = 'Y';
SELECT gst_invoice_no
FROM JAI_AR_TRXS
WHERE customer_trx_id = pr_new.customer_trx_id ;
SELECT regime_id ,
regime_code
FROM
JAI_AR_TRX_TAX_LINES jcttl,
JAI_AR_TRX_LINES jctl,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v jrttv
WHERE jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id
AND jctl.customer_trx_id = pr_new.customer_trx_id
AND jcttl.tax_id = jtc.tax_id
AND jtc.tax_type = jrttv.tax_type
AND regime_code = cp_regime_code
AND jtc.org_id = pr_new.org_id ;
SELECT regime_id,
attribute_value
FROM JAI_RGM_ORG_REGNS_V orrg
WHERE orrg.organization_id = cp_organization_id
AND orrg.location_id = cp_location_id
AND attribute_type_code = jai_constants.rgm_attr_type_code_primary
AND attribute_code = jai_constants.attr_code_regn_no
AND regime_code = cp_regime_code;
SELECT COUNT(lines.customer_trx_id)
FROM jai_ar_trx_lines lines,
jai_interface_lines_all intfs
WHERE lines.customer_trx_line_id = intfs.internal_trx_line_id
AND lines.customer_trx_id = pr_new.customer_trx_id
AND lines.interface_flag = 'Y'
AND intfs.taxable_event = 'EXTERNAL';
SELECT COUNT(interface_flag)
FROM jai_ar_trx_lines
WHERE customer_trx_id = pr_new.customer_trx_id
AND interface_flag = 'Y';
SELECT DISTINCT vat_invoice_no
FROM jai_interface_lines_all
WHERE internal_trx_id = pr_new.customer_trx_id;
SELECT
st_inv_number
FROM JAI_AR_TRXS
WHERE customer_trx_id = pr_new.customer_trx_id;
SELECT
regime_id ,
regime_code
FROM
JAI_AR_TRX_TAX_LINES jcttl,
JAI_AR_TRX_LINES jctl,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v jrttv
WHERE
jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id AND
jctl.customer_trx_id = pr_new.customer_trx_id AND
jcttl.tax_id = jtc.tax_id AND
jtc.tax_type = jrttv.tax_type AND
regime_code = jai_constants.service_regime AND
jtc.org_id = pr_new.org_id ;
SELECT
'Y'
FROM dual
WHERE EXISTS
(SELECT 1
FROM JAI_AR_TRX_TAX_LINES jcttl,
JAI_AR_TRX_LINES jctl,
JAI_CMN_TAXES_ALL jtc
WHERE jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id
AND jctl.customer_trx_id = pr_new.customer_trx_id
AND jcttl.tax_id = jtc.tax_id
AND upper(jtc.tax_type) IN
('EXCISE', 'EXCISE_EDUCATION_CESS', 'EXCISE_SH_EDU_CESS')
AND jtc.org_id = pr_new.org_id
AND jctl.customer_trx_line_id = pn_customer_trx_line_id
);
FILENAME: JA_IN_LOC_AR_HDR_UPDATE_TRG.sql
CHANGE HISTORY:
S.No Date Author and Details
1. 10-Aug-2005 Aiyer bug 4545146 version 120.1
Issue:-
Deadlock on tables due to multiple triggers on the same table (in different sql files)
firing in the same phase.
Fix:-
Multiple triggers on the same table have been merged into a single file to resolve
the problem
The following files have been stubbed:-
jai_ar_rcta_t1.sql
jai_ar_rcta_t2.sql
jai_ar_rcta_t3.sql
jai_ar_rcta_t4.sql
jai_ar_rcta_t6.sql
jai_ar_rcta_t7.sql
jai_ar_rcta_t8.sql
jai_ar_rcta_t9.sql
Instead the new file jai_ar_rcta_t.sql has been created which contains all the triggers in the above files
2 09-Mar-2007 ssawant for the bug#5040383, File version 120.6
Forward porting the changes done in bug#4998378
bduvarag for the bug#5171573, File version 120.6
Forward porting the changes done in bug#5057544
3 17/05/2007 bduvarag for the bug#4601570, File version 120.14
Forward porting the changes done in bug#4474270
4 31-dec-2011 qinglei for bug#13537078
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_loc_ar_hdr_update_trg.sql
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------*/
-----Once Complete Button is Pressed Following code tell you what will happen at what stage
/* --Ramananda for File.Sql.35, start */
v_complete_flag := 'N';
v_update_rg_flag := 'N';
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;
FETCH Complete_Cur INTO v_org_id, v_loc_id,v_complete_flag,v_reg_type, v_update_rg_flag,v_update_rg23d_flag;
FETCH Complete_Cur INTO v_org_id, v_loc_id,v_complete_flag,v_reg_type, v_update_rg_flag;
v_rg_flag := v_update_rg_flag;
UPDATE JAI_AR_TRXS
SET ONCE_COMPLETED_FLAG = pr_new.COMPLETE_FLAG
WHERE CUSTOMER_TRX_ID = V_CUSTOMER_TRX_ID;
UPDATE JAI_AR_TRXS
SET ONCE_COMPLETED_FLAG = pr_new.COMPLETE_FLAG
WHERE CUSTOMER_TRX_ID = V_CUSTOMER_TRX_ID;
v_update_rg_flag := 'Y';-- bug#3496577 -- setting the value to 'Y' because the update_rg_flag has to only impact
(v_rg_flag = 'Y' OR v_update_rg_flag = 'Y') AND
v_reg_code IS NOT NULL
THEN
/*
||End of bug 4101549
*/
FOR Line_Rec IN Line_Cur LOOP
FOR excise_cal_rec IN excise_cal_cur(Line_Rec.customer_trx_line_id, Line_Rec.Inventory_Item_ID, v_org_id) LOOP
IF excise_cal_rec.t_type IN ('Excise') THEN
v_basic_ed := NVL(v_basic_ed,0) + NVL(excise_cal_rec.func_amt,0);
v_update_rg23d_flag = 'Y'
THEN
/*Bug#4601570 bduvarag end*/
if line_rec.inventory_item_id is not null then
OPEN matched_qty_cur(line_rec.customer_trx_line_id);
UPDATE JAI_AR_TRX_LINES
SET EXCISE_INVOICE_NO = v_invoice_no ,
EXCISE_INVOICE_DATE = SYSDATE
WHERE CUSTOMER_TRX_LINE_ID = LINE_REC.customer_trx_line_id AND
INVENTORY_ITEM_ID = LINE_REC.inventory_item_id AND
CUSTOMER_TRX_ID = v_customer_trx_id;
SELECT once_completed_flag
INTO v_reg_type
FROM JAI_AR_TRXS
WHERE CUSTOMER_TRX_ID = pr_new.Customer_trx_id;
UPDATE JAI_AR_TRX_LINES
SET PAYMENT_REGISTER = v_excise_paid_register
WHERE CUSTOMER_TRX_LINE_ID = LINE_REC.customer_trx_line_id AND
INVENTORY_ITEM_ID = LINE_REC.inventory_item_id AND
CUSTOMER_TRX_ID = v_customer_trx_id;
SELECT once_completed_flag
INTO v_reg_type1
FROM JAI_AR_TRXS
WHERE CUSTOMER_TRX_ID = pr_new.Customer_trx_id;
UPDATE JAI_AR_TRX_LINES
SET PAYMENT_REGISTER = v_excise_paid_register
WHERE CUSTOMER_TRX_LINE_ID = LINE_REC.customer_trx_line_id AND
INVENTORY_ITEM_ID = LINE_REC.inventory_item_id AND
CUSTOMER_TRX_ID = v_customer_trx_id;
INSERT INTO JAI_AR_TRX_INS_HDRS_T
(
ORGANIZATION_ID,
LOCATION_ID,
CUSTOMER_TRX_ID ,
SHIP_TO_CUSTOMER_ID,
SHIP_TO_SITE_USE_ID,
CUST_TRX_TYPE_ID,
TRX_DATE,
SOLD_TO_CUSTOMER_ID,
BATCH_SOURCE_ID,
BILL_TO_CUSTOMER_ID , -- BILL_TO_CUSTOMER_ID column in insert added by sriram - 13/may-02
BILL_TO_SITE_USE_ID ,
CREATED_BY ,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
) -- BILL_TO_SITE_USE_ID column in insert added by sriram - 13/may-02
VALUES
(
V_ORG_ID,
V_LOC_ID,
V_CUSTOMER_TRX_ID ,
pr_new.SHIP_TO_CUSTOMER_ID,
pr_new.SHIP_TO_SITE_USE_ID,
pr_new.CUST_TRX_TYPE_ID,
pr_new.TRX_DATE,
pr_new.SOLD_TO_CUSTOMER_ID,
pr_new.BATCH_SOURCE_ID,
pr_new.BILL_TO_CUSTOMER_ID ,-- ADDED BY SRIRAM - 13-MAY-2002
pr_new.BILL_TO_SITE_USE_ID,
FND_GLOBAL.USER_ID , -- added standard who columns by brahtod for bug# 4558072
SYSDATE ,
FND_GLOBAL.USER_ID ,
SYSDATE);
UPDATE JAI_AR_TRXS
SET
ONCE_COMPLETED_FLAG = pr_new.COMPLETE_FLAG
WHERE CUSTOMER_TRX_ID = V_CUSTOMER_TRX_ID;
UPDATE
JAI_AR_TRXS
SET
vat_invoice_no = lv_vat_invoice_number ,
vat_invoice_date = nvl(ld_gl_date,pr_new.trx_date)
WHERE
customer_trx_id = pr_new.customer_trx_id ;
UPDATE jai_ar_trxs
SET st_inv_number = lv_st_inv_number
WHERE customer_trx_id = pr_new.customer_trx_id ;
SELECT to_date(attribute_value, 'DD/MM/YYYY')
FROM jai_rgm_registrations
WHERE regime_id = p_regime_id
AND attribute_code = 'EFF_DATE_ST_PT'
AND attribute_type_code = 'OTHERS'
AND registration_type = 'OTHERS';
select to_date(attribute_value, 'DD/MM/YYYY')
from JAI_RGM_ORG_REGNS_V
where regime_id = p_regime_id
and organization_id = p_organization_id
and location_id = p_location_id
AND attribute_code = 'EFF_DATE_ST_PT'
AND attribute_type_code = 'OTHERS'
AND registration_type = 'OTHERS'
AND (NOT EXISTS
(select '1'
from JAI_RGM_ORG_REGNS_V
where regime_id = p_regime_id
and attribute_code IN 'INV_ORG_CLASSIFICATION'
and attribute_value <> 'ORGANIZATION'
and organization_id = p_organization_id
and location_id = p_location_id)
OR
NOT EXISTS
(select '1'
from JAI_RGM_ORG_REGNS_V
where regime_id = p_regime_id
and attribute_code IN 'SERVICE TYPE'
and attribute_value <> 'OTHER'
and organization_id = p_organization_id
and location_id = p_location_id)
);
SELECT gl_date
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = pr_new.customer_trx_id
AND customer_trx_line_id = pn_customer_trx_line_id
AND account_class = 'REV';
SELECT * FROM JAI_AR_TRX_LINES
WHERE CUSTOMER_TRX_ID = pr_new.CUSTOMER_TRX_ID;
SELECT COUNT(*)
FROM RA_CUSTOMER_TRX_LINES_ALL
WHERE CUSTOMER_TRX_ID = pr_new.CUSTOMER_TRX_ID
AND LINE_TYPE in (lv_line_type_tax, lv_line_type_freight); /* Modified by Ramananda for removal of SQL LITERALs */
SELECT COUNT(*)
FROM RA_CUST_TRX_LINE_GL_DIST_ALL
WHERE CUSTOMER_TRX_ID = pr_new.CUSTOMER_TRX_ID
AND ACCOUNT_CLASS IN (lv_acct_class_tax , lv_acct_class_freight ); /* Modified by Ramananda for removal of SQL LITERALs */
SELECT A.tax_id taxid, A.tax_rate, A.uom uom,A.tax_amount tax_amt,b.tax_type t_type,A.customer_trx_line_id line_id , a.tax_line_no
FROM JAI_AR_TRX_TAX_LINES A , JAI_CMN_TAXES_ALL B
WHERE link_to_cust_trx_line_id = p_customer_trx_line_id
AND A.tax_id = B.tax_id
ORDER BY 1;
SELECT tax_account_id
FROM JAI_CMN_TAXES_ALL B
WHERE B.tax_id = p_tax_id ;
SELECT organization_id, location_id, batch_source_id
FROM JAI_AR_TRXS
WHERE Customer_Trx_ID = v_customer_trx_id;
SELECT regime_id, regime_code
FROM jai_regime_tax_types_v jrttv
WHERE upper(jrttv.tax_type) = upper(cp_tax_type);
SELECT register_code
FROM JAI_OM_OE_BOND_REG_HDRS
WHERE organization_id = p_org_id AND location_id = p_loc_id AND
register_id IN (SELECT register_id
FROM JAI_OM_OE_BOND_REG_DTLS
WHERE order_type_id = p_batch_source_id AND order_flag = 'N');
SELECT A.register_code
FROM JAI_OM_OE_BOND_REG_HDRS A,
JAI_OM_OE_BOND_REG_DTLS b,
oe_transaction_types_tl ott
WHERE A.organization_id = p_organization_id
AND A.location_id = p_location_id
AND A.register_id = b.register_id
AND b.order_flag = 'Y'
AND b.order_type_id = ott.transaction_type_id
AND ott.NAME = p_order_type;
SELECT
1
FROM
JAI_AR_TRX_TAX_LINES jcttl,
JAI_AR_TRX_LINES jctl,
JAI_CMN_TAXES_ALL jtc
WHERE
jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id AND
jctl.customer_trx_id = pr_new.customer_trx_id AND
jcttl.tax_id = jtc.tax_id AND
jtc.org_id = pr_new.org_id AND
jtc.tax_type = cp_tax_type ;
SELECT
regime_id
FROM
jai_regime_tax_types_v
WHERE
regime_code = jai_constants.vat_regime
AND rownum = 1 ;
FILENAME: JA_IN_APPS_AR_LINES_INSERT_TRG.sql
CHANGE HISTORY:
1. 10-Aug-2005 Aiyer bug 4545146 version 120.1
Issue:-
Deadlock on tables due to multiple triggers on the same table (in different sql files)
firing in the same phase.
Fix:-
Multiple triggers on the same table have been merged into a single file to resolve
the problem
The following files have been stubbed:-
jai_ar_rcta_t1.sql
jai_ar_rcta_t2.sql
jai_ar_rcta_t3.sql
jai_ar_rcta_t4.sql
jai_ar_rcta_t6.sql
jai_ar_rcta_t7.sql
jai_ar_rcta_t8.sql
jai_ar_rcta_t9.sql
Instead the new file jai_ar_rcta_t.sql has been created which contains all the triggers in the above files
Dependency:
----------
Sl No. Bug Dependent on
Bug/Patch set Details
-------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------*/
v_customer_trx_id := pr_new.customer_trx_id; --Ramananda for File.Sql.35
INSERT INTO JAI_AR_TRX_INS_LINES_T ( paddr,
extended_amount,
customer_trx_line_id,
customer_trx_id,
set_of_books_id,
link_to_cust_trx_line_id,
line_type,
uom_code,
vat_tax_id,
acctd_amount,
amount,
CODE_COMBINATION_ID,
cust_trx_line_sales_rep_id,
insert_update_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
tax_rate,
error_flag ,
source ,
org_id , -- bug# 3479348
line_number) -- added by sriram bug# 3479348
VALUES ( NULL, /* Previously passing v_paddr. Replaced with NULL by rallamse bug#4448789 */
TAX_TYPE_REC.tax_amt,
TAX_TYPE_REC.LINE_ID,
v_customer_trx_id,
v_books_id,
v_customer_trx_line_id,
v_line_type,
TAX_TYPE_REC.uom,
ln_tax_rate_id, --v_vat_tax, /* Modified by Ramananda for bug#4468353 due to ebtax uptake by AR */
v_converted_rate * TAX_TYPE_REC.tax_amt,
TAX_TYPE_REC.tax_amt,
v_ccid,
v_salesrep_id,
'U',
Sysdate,
UID,
Sysdate,
UID,
UID,
TAX_TYPE_REC.tax_rate,
'P',
v_created_from,
pr_new.org_id, -- added by sriram bug# 3479348
TAX_TYPE_REC.tax_line_no); -- added by sriram bug# 3479348
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 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 = p_ship_to_site_use_id; /* Modified by Ramananda for removal of SQL LITERALs */
select list_price, unit_code
from so_price_list_lines
where price_list_id in (select price_list_id from JAI_CMN_CUS_ADDRESSES
where customer_id = p_customer_id and
address_id = p_address_id) and
inventory_item_id = p_inventory_item_id
and unit_code = v_uom_code
AND NVL(end_date_active,SYSDATE) >= p_trx_date;
SELECT organization_id, location_id--added by peng.zheng for bug 10043656
FROM JAI_AR_TRX_APPS_RELS_T ;/*altered by rchandan for bug#4479131*/
SELECT organization_id, location_id--added by peng.zheng for bug 10043656
FROM JAI_AR_TRXS
WHERE trx_number = pr_new.recurred_from_trx_number;
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
Where a.cust_trx_type_id = pr_new.cust_trx_type_id
And a.org_id = v_org_id; /* Modified by Ramananda for removal of SQL LITERALs */
Select Customer_Trx_Line_ID, Inventory_Item_ID, Unit_Code, Line_Amount, Quantity,unit_selling_price
From JAI_AR_TRX_LINES
Where Customer_Trx_ID = v_header_id;
which is selecting SOB from org_organization_definitions
as the SOB will never by null in base table.
*/
ln_vat_assessable_value JAI_AR_TRX_LINES.VAT_ASSESSABLE_VALUE%TYPE;
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;
SELECT REGIME_ID
FROM JAI_RGM_DEFINITIONS
WHERE REGIME_CODE = CP_REGIME_CODE;
FILENAME: JA_IN_AR_HDR_UPDATE_TRG.sql
CHANGE HISTORY:
S.No Date Author and Details
1. 10-Aug-2005 Aiyer bug 4545146 version 120.1
Issue:-
Deadlock on tables due to multiple triggers on the same table (in different sql files)
firing in the same phase.
Fix:-
Multiple triggers on the same table have been merged into a single file to resolve
the problem
The following files have been stubbed:-
jai_ar_rcta_t1.sql
jai_ar_rcta_t2.sql
jai_ar_rcta_t3.sql
jai_ar_rcta_t4.sql
jai_ar_rcta_t6.sql
jai_ar_rcta_t7.sql
jai_ar_rcta_t8.sql
jai_ar_rcta_t9.sql
Instead the new file jai_ar_rcta_t.sql has been created which contains all the triggers in the above files
2. 31-AUG-2006 SACSETHI FOR BUG 5631784 , 5228046 FILE VERSION 120.4
FORWARD PORTING BUG FROM 11I BUG 4742259
NEW ENH: TAX COLLECTION AT SOURCE IN RECEIVABLES
Changes -
Object Type Object Name Change Description
---------------------------------------------------------------------------------------------
VARIABLE LN_TCS_EXISTS Add Variable Added
VARIABLE LN_TCS_REGIME_ID Add Variable Added
VARIABLE LN_THRESHOLD_SLAB_ID Add Variable Added
VARIABLE LN_THRESHOLD_TAX_CAT_ID Add Variable Added
CURSOR GC_CHK_RGM_TAX_EXISTS ADD CURSOR FOR GETTING COUNT(1) FROM TAXES
CURSOR GC_GET_REGIME_ID ADD CURSOR FOR GETTING REGIME ID FOR TCS
VARIABLE LV_PROCESS_FLAG ADD VARIABLE LV_PROCESS_FLAG IS PROCESS FLAG
VARIABLE LV_PROCESS_MESSAGE ADD VARIABLE LV_PROCESS_MESSAGE IS PROCESS MESSAGE RETURN BY CALLING OBJECT IN RESPONSE
CURSOR TAX_INFO_CUR MODIFY PRECEDENCE IS ADDED FROM 6 TO 10
SQL STATEMENT JAI_AR_TRX_TAX_LINES MODIFY PRECEDENCE IS ADDED FROM 6 TO 10
3. 27-Feb-2007 CSahoo for Bug 5390583, File Version 120.5 Forward Porting of 11i BUG 5357400
When a change is done in the invoice currency code from the front end
the change is being reflected in the JAI_AR_TRXS table.
Added a IF clause for the same.
4. 14-05-2007 ssawant for bug 5879769, File Version 120.6
Objects was not compiling. so changes are done to make it compiling.
5. 12-10-2007 ssumaith - bug#5597146 - file version 120.16
when there is a change in currency at the invoice header , the excise av
and vat av were calculated wrongly.
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_hdr_update_trg.sql
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
6. 21-Mar-2008 Jia for Bug#6859632
Issue: TAX WILL BE ERROR IF SHIP-TO FILED OF AR TRANSACTION IS NOT ENTER AT FIRST.
v_price_list_val didn't multiply quantity;
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 invoice_currency_code = pr_new.invoice_currency_code ,
exchange_rate_type = pr_new.exchange_rate_type ,
exchange_date = pr_new.exchange_date ,
exchange_rate = pr_new.exchange_rate
WHERE customer_trx_id = pr_new.customer_trx_id;
DELETE JAI_AR_TRX_TAX_LINES
WHERE LINK_TO_CUST_TRX_LINE_ID = Rec.CUSTOMER_TRX_LINE_ID;
v_last_update_date , v_last_updated_by , v_last_update_login
, null --Add a default value by Jia for Bug#6859632
, ln_vat_assessable_value
-- Bug 6109941, Added by brathod for fwd porting bug 4742259
, p_thhold_cat_base_tax_typ => jai_constants.tax_type_tcs
, p_threshold_tax_cat_id => ln_threshold_tax_cat_id
, p_source_trx_type => null
, p_source_table_name => null
, p_action => jai_constants.default_taxes
-- , pn_gst_assessable_value => ln_gst_assessable_value -- Added by Jia for GST Bug#10043656 on 2010/09/10
-- End 6109941
);
UPDATE JAI_AR_TRX_LINES
SET tax_category_id = v_tax_category_id,
service_type_code = v_service_type, --added by csahoo for Bug#5879769
assessable_value = nvl(v_price_list,0),
vat_assessable_value = ln_vat_assessable_value,
-- gst_assessable_value = ln_gst_assessable_value, -- Added by Jia for GST Bug#10043656 on 2010/09/10
tax_amount = v_line_tax_amount,
total_amount = nvl(rec.line_amount,0) + v_line_tax_amount,
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 = rec.customer_trx_line_id;
SELECT ONCE_COMPLETED_FLAG
FROM JAI_AR_TRXS
WHERE CUSTOMER_TRX_ID = V_HEADER_ID;
SELECT COUNT(lines.customer_trx_id)
FROM jai_ar_trx_lines lines,
jai_interface_lines_all intfs
WHERE lines.customer_trx_line_id = intfs.internal_trx_line_id
AND lines.customer_trx_id = v_header_id
AND lines.interface_flag = 'Y'
AND intfs.taxable_event = 'EXTERNAL';
SELECT COUNT(interface_flag)
FROM jai_ar_trx_lines
WHERE customer_trx_id = v_header_id
AND interface_flag = 'Y';
REM Get the VAT invoice number from the source SO and insert the source
REM VAT invoice number to REPOSITORY table when pre_customer_trx_id is null.
REM In functoinality,the issue happens when the Source SO and RMA SO
REM are imported into AR together.
REM
REM 10-Jun-2010 Modified by Allen Yang for bug 9793678
REM Commented code which populates VAT invoice number on
REM JAI_AR_TRXS for non-shippable RMA.
REM
REM 09-Jul-2010 Modified by Bo Li for Bug 9765108
REM Change the procedure ARU_T8, modified the logic of inserting
REM repository table for nonshippable RMA.
REM +======================================================================+
*/
PROCEDURE ARU_T8 ( 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_organization_id NUMBER ;
SELECT interface_line_attribute6 order_line_id
,inventory_item_id
,customer_trx_line_id --Added By Bo Li for bug#9765108 on 09-JUL-2010
FROM ra_customer_trx_lines_all
WHERE customer_trx_id = pr_new.customer_trx_id;
SELECT count(1)
FROM OE_ORDER_HEADERS_ALL oh,
OE_ORDER_LINES_ALL ol,
OE_TRANSACTION_TYPES_TL ot,
oe_workflow_assignments owf
WHERE oh.header_id = ol.header_id
AND oh.order_type_id = ot.transaction_type_id
AND oh.order_type_id = owf.order_type_id
AND ol.line_type_id = owf.line_type_id
AND oh.order_number = pr_new.interface_header_attribute1
AND ot.language = userenv('LANG')
AND ol.line_id = pn_order_line_id
AND owf.process_name IN ('R_RMA_CREDIT_APP_HDR_INV',
'R_RMA_CREDIT_WO_SHIP_APPROVE',
'R_RMA_CREDIT_WO_SHIP_HDR_INV',
'R_RMA_FOR_CREDIT_WO_SHIPMENT',
'R_RMA_FOR_OTA_CREDIT');
SELECT COUNT(1)
FROM MTL_SYSTEM_ITEMS msi,
JAI_OM_OE_RMA_LINES l
WHERE msi.inventory_item_id = pn_inventory_item_id
AND msi.inventory_item_id = l.inventory_item_id
AND l.rma_line_id = pn_order_line_id
AND msi.shippable_item_flag = 'N' ;
SELECT organization_id ,
location_id ,
vat_invoice_no ,
vat_invoice_date
FROM JAI_AR_TRXS
WHERE customer_trx_id = pr_new.customer_trx_id;
SELECT type
FROM ra_cust_trx_types_all
WHERE cust_trx_type_id = pr_new.cust_trx_type_id AND
NVL(org_id,0) = NVL(pr_new.org_id,0);
SELECT regime_id ,
regime_code
FROM
JAI_AR_TRX_TAX_LINES jcttl,
JAI_AR_TRX_LINES jctl,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v jrttv
WHERE jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id
AND jctl.customer_trx_id = pr_new.customer_trx_id
AND jcttl.tax_id = jtc.tax_id
AND jtc.tax_type = jrttv.tax_type
AND regime_code = jai_constants.vat_regime
AND jtc.org_id = pr_new.org_id ;
SELECT gl_date
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = pr_new.customer_trx_id
AND account_class = cp_acct_class
AND latest_rec_flag = 'Y';
SELECT vat_invoice_no
FROM JAI_AR_TRXS
WHERE customer_trx_id = pr_new.previous_customer_trx_id;
SELECT 1
FROM JAI_AR_TRX_TAX_LINES jcttl,
JAI_AR_TRX_LINES jctl,
JAI_CMN_TAXES_ALL jtc
WHERE jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id
AND jctl.customer_trx_id = pr_new.customer_trx_id
AND jcttl.tax_id = jtc.tax_id
AND jtc.org_id = pr_new.org_id
AND jtc.tax_type = cp_tax_type ;
SELECT regime_id
FROM jai_regime_tax_types_v
WHERE regime_code = jai_constants.vat_regime
AND rownum = 1 ;
SELECT jwl.vat_invoice_no
FROM OE_ORDER_HEADERS_ALL ohc,
oe_transaction_types_tl ot,
JAI_OM_WSH_LINES_ALL jwl
WHERE ohc.order_type_id = ot.transaction_type_id
AND ot.LANGUAGE = userenv('LANG')
AND ohc.source_document_id = jwl.ORDER_HEADER_ID
AND ohc.ORDER_NUMBER = pr_new.INTERFACE_HEADER_ATTRIBUTE1
AND ot.NAME = pr_new.INTERFACE_HEADER_ATTRIBUTE2;
SELECT
jctl.customer_trx_id ,
jctl.vat_assessable_value ,
nvl(jctl.vat_exemption_flag,'N') vat_exemption_flag ,
jcttl.customer_trx_line_id ,
jcttl.tax_id ,
jcttl.link_to_cust_trx_line_id ,
jcttl.func_tax_amount ,
jcttl.creation_date ,
jtc.tax_type ,
jcttl.tax_rate
FROM
JAI_AR_TRX_LINES jctl ,
JAI_AR_TRX_TAX_LINES jcttl ,
JAI_CMN_TAXES_ALL jtc ,
(
SELECT jrttv1.tax_type tax_type
FROM jai_regime_tax_types_v jrttv1
WHERE jrttv1.regime_code = jai_constants.vat_regime
UNION
SELECT 'VAT REVERSAL' tax_type
FROM DUAL
) jrttv
WHERE
jctl.customer_trx_id = pr_new.customer_trx_id AND
jctl.customer_trx_line_id = jcttl.link_to_cust_trx_line_id AND
jctl.customer_trx_line_id = cp_customer_trx_line_id AND
jcttl.tax_id = jtc.tax_id AND
jtc.tax_type = jrttv.tax_type AND
NOT EXISTS ( SELECT
1
FROM
jai_rgm_trx_records jrtr
WHERE
jrtr.source = cp_source AND
jrtr.source_trx_type = cp_source_trx_type AND
jrtr.organization_id = cp_organization_id AND
jrtr.location_id = cp_loc_id AND
jrtr.source_table_name = cp_source_table_name AND
jrtr.trx_reference1 = pr_new.customer_trx_id AND
jrtr.source_document_id = jcttl.customer_trx_line_id AND
jrtr.reference_id = jcttl.tax_id
) ;
jai_cmn_rgm_recording_pkg.insert_vat_repository_entry (
pn_repository_id => ln_repository_id ,
pn_regime_id => ln_regime_id ,
pv_tax_type => rec_cur_get_man_ar_inv_taxes.tax_type ,
pv_organization_type => jai_constants.orgn_type_io ,
pn_organization_id => v_organization_id ,
pn_location_id => v_loc_id ,
pv_source => jai_constants.source_ar ,
pv_source_trx_type => jai_constants.source_ttype_man_ar_inv ,
pv_source_table_name => jai_constants.tname_cus_trx_lines ,
pn_source_id => rec_cur_get_man_ar_inv_taxes.customer_trx_line_id ,
pd_transaction_date => rec_cur_get_man_ar_inv_taxes.creation_date ,
pv_account_name => lc_account_name , --Date 14/06/2007 by sacsethi for bug 6072461
pn_charge_account_id => ln_charge_ac_id ,
pn_balancing_account_id => ln_balancing_ac_id ,
pn_credit_amount => ln_credit_amount ,
pn_debit_amount => ln_debit_amount ,
pn_assessable_value => rec_cur_get_man_ar_inv_taxes.vat_assessable_value ,
pn_tax_rate => rec_cur_get_man_ar_inv_taxes.tax_rate ,
pn_reference_id => rec_cur_get_man_ar_inv_taxes.tax_id ,
pn_batch_id => NULL ,
pn_inv_organization_id => v_organization_id ,
pv_invoice_no => lv_vat_invoice_no ,
pd_invoice_date => nvl(nvl(ld_gl_date,pr_new.trx_date),rec_cur_get_man_ar_inv_taxes.creation_date) ,
pv_called_from => jai_constants.vat_repo_call_from_om_ar ,
pv_process_flag => lv_process_flag ,
pv_process_message => lv_process_message ,
pv_trx_reference_context => jai_constants.contxt_manual_ar ,
pv_trx_reference1 => rec_cur_get_man_ar_inv_taxes.customer_trx_id ,
pv_trx_reference2 => rec_cur_get_man_ar_inv_taxes.link_to_cust_trx_line_id ,
pv_trx_reference3 => NULL ,
pv_trx_reference4 => NULL ,
pv_trx_reference5 => NULL
);
UPDATE JAI_AR_TRXS
SET vat_invoice_no = lv_vat_invoice_no ,
vat_invoice_date = nvl(ld_gl_date,pr_new.trx_date)
WHERE customer_trx_id = pr_new.customer_trx_id ;
Added legal_entity_id for table JAI_AR_TRXS in insert statement
4. 26-07-2005 rallamse bug#4510143 120.2
Modified legal_entity_id for table JAI_AR_TRXS to get from header_info_cur cursor
5. 10-Aug-2005 Aiyer bug 4545146 version 120.1
Issue:-
Deadlock on tables due to multiple triggers on the same table (in different sql files)
firing in the same phase.
Fix:-
Multiple triggers on the same table have been merged into a single file to resolve
the problem
The following files have been stubbed:-
jai_ar_rcta_t1.sql
jai_ar_rcta_t2.sql
jai_ar_rcta_t3.sql
jai_ar_rcta_t4.sql
jai_ar_rcta_t6.sql
jai_ar_rcta_t7.sql
jai_ar_rcta_t8.sql
jai_ar_rcta_t9.sql
Instead the new file jai_ar_rcta_t.sql has been created which contains all the triggers
---------------------------------------------------------------------------------------------------*/
v_created_from Varchar2(30);
v_last_update_date Date;
v_last_updated_by Number;
v_last_update_login Number;
SELECT trx_number, customer_trx_id, recurred_from_trx_number, batch_source_id, created_from,
creation_date, created_by, last_update_date, last_updated_by, last_update_login
FROM JAI_AR_TRX_COPY_HDR_T
ORDER BY customer_trx_id;
SELECT once_completed_flag, 1
FROM JAI_AR_TRXS
WHERE customer_trx_id = p_header_id
AND NVL(batch_source_id,0) = p_batch_source_id;
SELECT a.customer_trx_id
FROM JAI_AR_TRXS a
WHERE a.trx_number = p_recurred_from_trx_number
AND NVL(batch_source_id,0) = p_batch_source_id;
SELECT customer_trx_line_id, line_number, 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
FROM JAI_AR_TRX_LINES
WHERE customer_trx_id = p_parent_header_id
ORDER BY customer_trx_line_id;
SELECT a.tax_line_no,
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, -- Date 06/12/2006 Bug 5228046 added by SACSETHI
a.tax_id, a.tax_rate, a.qty_rate, a.uom, a.tax_amount, a.base_tax_amount, a.func_tax_amount,
b.end_date valid_date, b.tax_type
FROM JAI_AR_TRX_TAX_LINES a, JAI_CMN_TAXES_ALL b
WHERE a.link_to_cust_trx_line_id = p_parent_line_id
AND a.tax_id = b.tax_id
ORDER BY a.tax_line_no;
SELECT CUSTOMER_TRX_ID, ORGANIZATION_ID, LOCATION_ID, UPDATE_RG_FLAG, UPDATE_RG23D_FLAG,
TAX_AMOUNT, LINE_AMOUNT, TOTAL_AMOUNT, BATCH_SOURCE_ID,legal_entity_id /* added rallamse bug#4448789 */
FROM JAI_AR_TRXS
WHERE trx_number = p_recurred_from_trx_number
AND NVL(batch_source_id,0) = p_batch_source_id;
v_last_update_date, v_last_updated_by, v_last_update_login;
DELETE JAI_AR_TRX_COPY_HDR_T
WHERE customer_trx_id = v_header_id;
INSERT INTO JAI_AR_TRXS
(customer_trx_id, organization_id, location_id, update_rg23d_flag,
update_rg_flag, trx_number, once_completed_flag,
line_amount, batch_source_id, created_from,
creation_date, created_by,
last_update_date,last_updated_by, last_update_login,
legal_entity_id) /* added rallamse bug#4448789 */
VALUES(v_header_id, hdr.organization_id, hdr.location_id, hdr.update_rg23d_flag,
hdr.update_rg_flag, v_trx_number, 'N',
hdr.line_amount, hdr.batch_source_id, v_created_from ,
v_creation_date, v_created_by,
v_last_update_date, v_last_updated_by, v_last_update_login,
hdr.legal_entity_id); /* added rallamse bug#4448789 */
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,
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(ra_customer_trx_lines_s.nextval,
--v_customer_trx_line_id, /* Commented by Ramananda as a part of removal of SQL LITERALs */
rec.line_number,
v_header_id,rec.description,
rec.inventory_item_id, rec.unit_code,
rec.quantity, rec.tax_category_id,rec.auto_invoice_flag,
rec.unit_selling_price,rec.line_amount, rec.gl_date,
rec.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
returning customer_trx_line_id into v_customer_trx_line_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, -- Date 06/12/2006 Bug 5228046 added by SACSETHI
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( ra_customer_trx_lines_s.nextval, v_customer_trx_line_id, rec1.tax_line_no,
rec1.precedence_1, rec1.precedence_2, rec1.precedence_3, rec1.precedence_4, rec1.precedence_5,
rec1.precedence_6, rec1.precedence_7, rec1.precedence_8, rec1.precedence_9, rec1.precedence_10, -- Date 06/12/2006 Bug 5228046 added by SACSETHI
rec1.tax_id, rec1.tax_rate, rec1.qty_rate, rec1.uom,
rec1.tax_amount, rec1.base_tax_amount, rec1.func_tax_amount,
v_creation_date, v_created_by, v_last_update_date,
v_last_updated_by, v_last_update_login);
UPDATE JAI_AR_TRX_LINES
SET tax_amount = v_line_tax_amount,
total_amount = nvl(line_amount,0) + v_line_tax_amount
WHERE customer_trx_line_id = v_customer_trx_line_id;
UPDATE JAI_AR_TRXS
SET tax_amount = v_header_tax_amount,
total_amount = nvl(line_amount,0) + v_header_tax_amount
WHERE customer_trx_id = v_header_id;
DELETE JAI_AR_TRXS
WHERE customer_trx_id = pr_old.customer_trx_id ;