R12常用sql

1、 查询EBS 系统在线人数

SELECT https://www.doczj.com/doc/a215174608.html,ER_NAME,
APP.APPLICATION_SHORT_NAME,
FAT.APPLICATION_NAME,
FR.RESPONSIBILITY_KEY,
FRT.RESPONSIBILITY_NAME,
FFF.FUNCTION_NAME,
https://www.doczj.com/doc/a215174608.html,ER_FUNCTION_NAME,
ICX.FUNCTION_TYPE,
ICX.FIRST_CONNECT,
https://www.doczj.com/doc/a215174608.html,ST_CONNECT
FROM ICX_SESSIONS ICX,
FND_USER U,
FND_APPLICATION APP,
FND_APPLICATION_TL FAT,
FND_RESPONSIBILITY FR,
FND_RESPONSIBILITY_TL FRT,
FND_FORM_FUNCTIONS FFF,
FND_FORM_FUNCTIONS_TL FFT
WHERE 1 = 1
AND https://www.doczj.com/doc/a215174608.html,ER_ID = https://www.doczj.com/doc/a215174608.html,ER_ID
AND ICX.RESPONSIBILITY_APPLICATION_ID = APP.APPLICATION_ID
AND FAT.APPLICATION_ID = ICX.RESPONSIBILITY_APPLICATION_ID
AND https://www.doczj.com/doc/a215174608.html,NGUAGE = 'ZHS'
AND FR.APPLICATION_ID = ICX.RESPONSIBILITY_APPLICATION_ID
AND FR.RESPONSIBILITY_ID = ICX.RESPONSIBILITY_ID
AND https://www.doczj.com/doc/a215174608.html,NGUAGE = 'ZHS'
AND FRT.APPLICATION_ID = ICX.RESPONSIBILITY_APPLICATION_ID
AND FRT.RESPONSIBILITY_ID = ICX.RESPONSIBILITY_ID
AND FFF.FUNCTION_ID = ICX.FUNCTION_ID
AND FFT.FUNCTION_ID = ICX.FUNCTION_ID
AND ICX.DISABLED_FLAG != 'Y'
AND ICX.PSEUDO_FLAG = 'N'
AND (https://www.doczj.com/doc/a215174608.html,ST_CONNECT +
DECODE(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'),
NULL,
ICX.LIMIT_TIME,
0,
ICX.LIMIT_TIME,
FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT') / 60) / 24) >
SYSDATE
AND ICX.COUNTER < ICX.LIMIT_CONNECTS;

2、 查询数据库表对象

SELECT *
FROM dba_objects db
WHERE db.object_type = 'TABLE' --可以变为 其他对象如 VI
AND db.object_name LIKE '%INTERFACE%'; --查询接口表

3、EBS 系统当前完成请求时间监测

SELECT
REQUEST_ID,
PROGRAM,
round((to_number(LAST_UPDATE_DATE-REQUESTED_START_DATE)*24*60)/60,2) 待定时间,
round((to_number(ACTUAL_COMPLETION_DATE-LAST_UPDATE_DATE)*24*60)/60,2) 运行时间,
'小时' 单位,
REQUESTED_START_DATE 提交日期,
LAST_UPDATE_DATE 起始日期,
ACTUAL_COMPLETION_DATE 完成日期,
PROGRAM_SHORT_NAME,
ARGUMENT_TEXT,
COMPLETION_TEXT,
RESPONSIBILITY_APPLICATION_ID,
STATUS_CODE,
PRIORITY_REQUEST_ID,
REQUESTOR
FROM FND_CONC_REQ_SUMMARY_V
WHERE PHASE_CODE = 'C'
and (nvl(request_type, 'X') != 'S')
and (trunc(request_date) >= trunc(sysdate - 7))
and round((to_number(ACTUAL_COMPLETION_DATE-LAST_UPDATE_DATE)*24*60)/60,2)>0.1 --6分钟以上程序
order by 运行时间 DESC,PROGRAM_SHORT_NAME,REQUEST_ID DESC

4、查询死锁的Session SQL 语句

SELECT dob.OBJECT_NAME Table_Name,
lo.LOCKED_MODE,
lo.SESSION_ID,
vss.SERIAL#,
vps.spid,
vss.action Action,
vss.osuser OSUSER,
vss.process AP_PID,
VPS.SPID DB_PID,
'alter system kill session ' || '''' || lo.SESSION_ID || ',' ||
vss.SERIAL# || ''';' kill_command
from v$locked_object lo, dba_objects

dob, v$session vss, V$PROCESS VPS
where lo.OBJECT_ID = dob.OBJECT_ID
and lo.SESSION_ID = vss.SID
AND VSS.paddr = VPS.addr
/* AND dob.OBJECT_NAME like 'AP_%' */
/* AND vss.client_info like 'OU_ID%' --OU_ID:就是指所在企业的OU的ID*/
order by 2, 3, DOB.object_name;

5、 查询并发程序是否启动跟踪功能-trc文件对数据库性能有影响

SELECT ICON_NAME,
ROW_ID,
USER_CONCURRENT_PROGRAM_NAME,
ENABLED_FLAG,
CONCURRENT_PROGRAM_NAME,
DESCRIPTION,
EXECUTION_OPTIONS,
REQUEST_PRIORITY,
INCREMENT_PROC,
RUN_ALONE_FLAG,
RESTART,
ENABLE_TRACE,
NLS_COMPLIANT,
OUTPUT_FILE_TYPE,
SAVE_OUTPUT_FLAG,
PRINT_FLAG,
MINIMUM_WIDTH,
MINIMUM_LENGTH,
OUTPUT_PRINT_STYLE,
REQUIRED_STYLE,
PRINTER_NAME,
APPLICATION_ID,
LAST_UPDATE_DATE,
EXECUTION_METHOD_CODE,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
EXECUTABLE_ID,
LAST_UPDATED_BY,
EXECUTABLE_APPLICATION_ID,
CONCURRENT_PROGRAM_ID,
CONCURRENT_CLASS_ID,
CLASS_APPLICATION_ID,
ARGUMENT_METHOD_CODE,
REQUEST_SET_FLAG,
QUEUE_METHOD_CODE,
QUEUE_CONTROL_FLAG,
SRS_FLAG,
CD_PARAMETER,
MLS_EXECUTABLE_ID,
MLS_EXECUTABLE_APP_ID,
RESOURCE_CONSUMER_GROUP,
ROLLBACK_SEGMENT,
OPTIMIZER_MODE,
SECURITY_GROUP_ID,
ENABLE_TIME_STATISTICS,
REFRESH_PORTLET,
PROGRAM_TYPE,
ACTIVITY_SUMMARIZER,
ALLOW_MULTIPLE_PENDING_REQUEST,
DELETE_LOG_FILE,
TEMPLATE_APPL_SHORT_NAME,
TEMPLATE_CODE,
MULTI_ORG_CATEGORY
FROM FND_CONCURRENT_PROGRAMS_VL
WHERE queue_control_flag = 'N'
--and (APPLICATION_ID = 555)
-- and (CONCURRENT_PROGRAM_ID = 46914)
and ENABLE_TRACE = 'Y'
order by application_id, user_concurrent_program_name

6、 查询 EBS 系统物料净重、毛重

select distinct MSI.SEGMENT1 || ',' 物料编码,
MSI.DESCRIPTION 物料名称,
MSI.UNIT_WEIGHT 毛重,
MSI.WEIGHT_UOM_CODE 毛重单位,
MSI.UNIT_VOLUME 净重,
MSI.VOLUME_UOM_CODE 净重单位
from MTL_SYSTEM_ITEMS_FVL MSI
where MSI.INVENTORY_ITEM_STATUS_CODE = 'Active'
and MSI.SEGMENT1 like '82%' --物料编码
--and (MSI.UNIT_WEIGHT is null or MSI.UNIT_VOLUME is null)
order by 1

7、月结各模块关闭情况查询SQL
-----库存模块
SELECT oap.STATUS 关闭状态,
oap.PERIOD_NAME 所属期间,
https://www.doczj.com/doc/a215174608.html,ANIZATION_ID 组织ID,
(select name from hr_organization_units x where https://www.doczj.com/doc/a215174608.html,ANIZATION_ID = https://www.doczj.com/doc/a215174608.html,ANIZATION_ID) 组织名称,
https://www.doczj.com/doc/a215174608.html,ST_UPDATE_DATE 执行关闭日期,
(select hre.full_name from hr_employees_all_v hre,fnd_user fu where hre.employee_id = fu.employee_id

and https://www.doczj.com/doc/a215174608.html,er_id = https://www.doczj.com/doc/a215174608.html,ST_UPDATED_BY) 执行关闭人,
oap.CREATED_BY
FROM ORG_ACCT_PERIODS_V oap
WHERE
(oap.PERIOD_NUMBER = 6) --月份
and (oap.PERIOD_YEAR = 2012) -- 年份
and https://www.doczj.com/doc/a215174608.html,ANIZATION_ID <> 0
order by https://www.doczj.com/doc/a215174608.html,ANIZATION_ID,oap.PERIOD_NAME desc,oap.Status desc
-----------------------------------------------------------------其他模块------------------------------------------------------------
SELECT gps.PERIOD_NAME 所属期间,
(select faa.Application_name
from fnd_application_all_view faa
where faa.APPLICATION_ID = gps.APPLICATION_ID) 模块名称,
gps.LEDGER_ID 分类账套,
gps.SHOW_STATUS 期间状态,
(select hre.full_name from hr_employees_all_v hre,fnd_user fu where hre.employee_id = fu.employee_id and https://www.doczj.com/doc/a215174608.html,er_id = https://www.doczj.com/doc/a215174608.html,ST_UPDATED_BY) 执行关闭人,
https://www.doczj.com/doc/a215174608.html,ST_UPDATE_DATE 最后次操作时间
FROM GL_PERIOD_STATUSES_V gps
WHERE --gps.application_id = 101
--AND

gps.ledger_id = 2021
AND gps.closing_status != 'N'
and (gps.LEDGER_ID = 2021)
order by gps.APPLICATION_ID,gps.PERIOD_NAME desc

8、总账库存科目明细追溯SQL

SELECT xel.subinventory_code 子库,
decode(xel.lot_number,'','来源,非库存') 批次,
cux_public_pkg.get_item_segment1(81, xeh.inventory_item_id) 物料编码,
cux_public_pkg.get_item_description(81, xeh.inventory_item_id) 物料品名,
xeh.transaction_uom 单位,
sum(xdl.unrounded_entered_dr)借方金额,
sum(xdl.unrounded_entered_cr) 贷方金额
FROM XLA_AE_HEADERS AH,
XLA_AE_LINES AL,
xla_distribution_links xdl,
gmf_xla_extract_headers xeh,
gmf_xla_extract_lines xel
WHERE AH.AE_HEADER_ID = AL.AE_HEADER_ID
--AND AH.APPLICATION_ID = 555
AND AH.PERIOD_NAME = '2012-03'
and al.CODE_COMBINATION_ID =
(select k.code_combination_id
from gl_code_combinations_kfv k
where k.concatenated_segments = '10.0.141103.0.0.0.0')
and al.ae_header_id = xdl.ae_header_id
and al.ae_line_num = xdl.ae_line_num
and al.application_id = xdl.application_id
and xdl.source_distribution_id_num_1 = xel.line_id(+)
and xel.header_id = xeh.header_id(+)
group by xel.subinventory_code,
xel.lot_number,
xeh.inventory_item_id,
xeh.transaction_uom

9、应收事物处理删除 SQL 语句

--组织表
select * from hr_organization_units_v
--AR 事物处理安全性限制
begin
mo_global.set_policy_context('S','107');
end;
-- 备份事物处理
create table bak.RA_CUSTOMER_TRX_ALL20120619 as
select * from RA_CUSTOMER_TRX_ALL where TRX_NUMBER = '10102672'
-- 检测备份情况
select * from bak.RA_CUSTOMER_TRX_ALL20120619
-- 修改事物处理
select t.*,t.rowid from RA_CUSTOMER_TRX_ALL t where TRX_NUMBER = '10102672'

-- 备份事物处理行
create table bak.RA_CUSTOMER_TRX_LINES_all0619

as
SELECT *
FROM RA_CUSTOMER_TRX_LINES_all where CUSTOMER_TRX_ID = 978317 --CUSTOMER_TRX_LINE_ID
-- 检测备份数据
select * from bak.RA_CUSTOMER_TRX_LINES_all0619
-- 修改事物处理行
SELECT t.*,t.rowid
FROM RA_CUSTOMER_TRX_LINES_all t where CUSTOMER_TRX_ID = 978317 --CUSTOMER_TRX_LINE_ID

-- 备份应收事物处理分配行
create table bak.RA_CUST_TRX_LINE_GL_DIST0619 as
select *
from RA_CUST_TRX_LINE_GL_DIST where CUSTOMER_TRX_ID = 978317
-- 检测备份数据
select * from bak.RA_CUST_TRX_LINE_GL_DIST0619
-- 修改应收事物处理分配行
select t.*,t.rowid
from RA_CUST_TRX_LINE_GL_DIST t where CUSTOMER_TRX_ID = 978317

-- 备份应收事物处理-税行
create table bak.ZX_LINES20120619 as
select * from ZX_LINES where TRX_NUMBER = '10102672'
-- 检测备份数据
select * from bak.ZX_LINES20120619
-- 修改应收事物处理-税行
select t.*,t.rowid from ZX_LINES t where TRX_NUMBER = '10102672'

10、BC_SQL_用户与职责与请求关系语句
---------------------------------------------------------------------------------------------------
--本SQL获取的是用户对应职责职责对应请求组、请组下对应“程序”【除程序以外还有 集、应用等】
--本SQL也可以简单理解为 获取用户可以提交什么请求，（请求可以简单理解为报表，但请求不是报表，包含关系）
--SQL addTime 2012-05-14 13：11， create by sunyukun
---------------------------------------------------------------------------------------------------
select https://www.doczj.com/doc/a215174608.html,er_ID,
https://www.doczj.com/doc/a215174608.html,er_name,
fu.start_date,
fu.END_DATE,
fu.description,
https://www.doczj.com/doc/a215174608.html,st_name,
fr.RESPONSIBILITY_NAME,
fr.description, --职责描述
fr.start_date,
fr.END_DATE,
frg.request_group_name, ---- 请求组名称
frg.description requestdsc, ---- 请求组描述
fr.menu_id, ---- 菜单 ID
REQUEST_UNIT_TYPE, ---- 请求类型
https://www.doczj.com/doc/a215174608.html,er_concurrent_program_name, ---请求并发程序名
decode(fcp.EXECUTION_METHOD_CODE,
'H',
'主机',
'S',
'立即',
'J',
'Java 存储过程',
'K',
'Java 并发程序',
'M',
'多语言功能',
'P',
'Oracle Reports',
'I',
'PL/SQL 存储过程',
'B',
'请求集阶段函数',
'A',
'派生',
'L',
'SQL*Loader 程序',
'Q',
'SQL*Plus',
'E',
'Perl 并发程序')
from fnd_user fu,
hr_employees fe,
FND_USER_RESP_GROUPS_DIRECT ugd,
FND_RESPONSIBILITY_VL fr,
fnd_request_groups frg,
FND_REQUEST_GROUP_UNITS frgu,
FN

D_CONCURRENT_PROGRAMS_VL fcp
where to_char(fu.creation_date, 'yyyy') >= '2008'
and fu.employee_id = fe.employee_id(+) --用户与职员关系
and https://www.doczj.com/doc/a215174608.html,er_id = https://www.doczj.com/doc/a215174608.html,er_id
and ugd.RESPONSIBILITY_ID = fr.responsibility_id
and ugd.RESPONSIBILITY_APPLICATION_ID = fr.APPLICATION_ID --- 以上用户与职责关系
and fr.request_group_id = frg.request_group_id(+)
and fr.group_application_id = frg.application_ID(+) --- 以上是请求组和职责关系
and frgu.application_id(+) = frg.application_ID
and frg.request_group_id = frgu.request_group_id(+) --- 以上是请求组中间表与职责
and fcp.CONCURRENT_PROGRAM_ID = frgu.REQUEST_UNIT_ID
and frgu.UNIT_application_id = fcp.application_id
and user_name = 'SUNYUKUN' --- 'SUNYUKUN' 登录用户名,可变量
order by User_id,
Responsibility_name


11、应收发票相关 脚本

--组织表
select * from hr_organization_units_v
--AR 事物处理安全性限制
begin
mo_global.set_policy_context('S','107');
end;
-- 备份事物处理
create table bak.RA_CUSTOMER_TRX_ALL20120619 as
select * from RA_CUSTOMER_TRX_ALL where TRX_NUMBER = '10102672'
-- 检测备份情况
select * from bak.RA_CUSTOMER_TRX_ALL20120619
-- 修改事物处理
select t.*,t.rowid from RA_CUSTOMER_TRX_ALL t where TRX_NUMBER = '10102672'

-- 备份事物处理行
create table bak.RA_CUSTOMER_TRX_LINES_all0619 as
SELECT *
FROM RA_CUSTOMER_TRX_LINES_all where CUSTOMER_TRX_ID = 978317 --CUSTOMER_TRX_LINE_ID
-- 检测备份数据
select * from bak.RA_CUSTOMER_TRX_LINES_all0619
-- 修改事物处理行
SELECT t.*,t.rowid
FROM RA_CUSTOMER_TRX_LINES_all t where CUSTOMER_TRX_ID = 978317 --CUSTOMER_TRX_LINE_ID

-- 备份应收事物处理分配行
create table bak.RA_CUST_TRX_LINE_GL_DIST0619 as
select *
from RA_CUST_TRX_LINE_GL_DIST where CUSTOMER_TRX_ID = 978317
-- 检测备份数据
select * from bak.RA_CUST_TRX_LINE_GL_DIST0619
-- 修改应收事物处理分配行
select t.*,t.rowid
from RA_CUST_TRX_LINE_GL_DIST t where CUSTOMER_TRX_ID = 978317

-- 备份应收事物处理-税行
create table bak.ZX_LINES20120619 as
select * from ZX_LINES where TRX_NUMBER = '10102672'
-- 检测备份数据
select * from bak.ZX_LINES20120619
-- 修改应收事物处理-税行
select t.*,t.rowid from ZX_LINES t where TRX_NUMBER = '10102672'

12、 安全性 SQL
begin
mo_global.set_policy_context('S','组织ID');
end;

select * from hr_organization_units_v --组织表

13、删除 AP 发票相关脚本 SQL
--发票
create table bak.AP_INVOICES_ALL_110707 as
select * from AP_INVOICES_ALL aia
where aia.invoice_id in (90490,90333)
--发票行
create table bak.AP_INVOICE_LINES_110707 as
select * from AP_INVOICE_LINES_ALL ala
where ala.invoice_id in (90490,90333);
--分配
create table bak.Ap_Invoice_Dist_110707 as
select *
from Ap_Invoice_Distributions_All aid
where aid.invoice_id = 90490;
--计划付款
create ta

ble bak.AP_PAYMENT_SCHEDULES_110707 as
select * from AP_PAYMENT_SCHEDULES_ALL p
where p.invoice_id in (90490,90333);
--暂挂
create table bak.AP_HOLDS_110707 as
select * from AP_HOLDS_ALL h
where h.invoice_id = 90490;
--付款行
create table bak.AP_INVOICE_PAYMENTS_110707 as
select * from AP_INVOICE_PAYMENTS_all aip
where aip.invoice_id = 90333;
--付款头
create table bak.AP_CHECKS_110707 as
select * from AP_CHECKS_ALL ac where ac.check_id = 64863;
--分录事件
create table bak.xla_trans_entities_110707 as
select *
from xla.xla_transaction_entities xte
where xte.source_id_int_1 in (90490, 90333)
and xte.security_id_int_1 = 81
and application_id = 200;
insert into bak.xla_trans_entities_110707
select * from xla.xla_transaction_entities xte
where xte.source_id_int_1 in (64863)
and xte.security_id_int_1 = 81
and application_id = 200;

--分录头
create table bak.xla_ae_headers_110707 as
select *
from xla.xla_ae_headers xah
where xah.entity_id in (9556541, 9554363);
insert into bak.xla_ae_headers_110707
select * from xla.xla_ae_headers xah where xah.entity_id in (9554366);

--会计事件
create table bak.xla_events_110707 as
select *
from xla_events xe
where xe.event_id in
(select event_id
from xla.xla_ae_headers xah
where xah.entity_id in (9556541, 9554363));
insert into bak.xla_events_110707
select * from xla_events xe
where xe.event_id in
(select event_id
from bak.xla_ae_headers_110707 xah
where xah.entity_id in (9554366));
--分录行
create table bak.xla_ae_lines_110707 as
select *
from xla.xla_ae_lines xal
where xal.ae_header_id in (14101317, 14103708, 14299824);

insert into bak.xla_ae_lines_110707
select *
from xla.xla_ae_lines xal
where xal.ae_header_id=14101322;

--日记账导入参考
create table bak.gl_import_references_110707 as
select *
from gl.gl_import_references gr
where gr.gl_sl_link_id in
(select gl_sl_link_id
from xla.xla_ae_lines xal
where xal.ae_header_id in (14101317, 14103708, 14299824));
insert into bak.gl_import_references_110707
select *
from gl.gl_import_references gr
where gr.gl_sl_link_id in (25174221,25174222);
--日记账头
create table bak.gl_je_headers_110707 as
select *
from gl_je_headers gjh
where gjh.je_header_id in
(select je_header_id
from gl.gl_import_references gr
where gr.gl_sl_link_id in
(select gl_sl_link_id
from xla.xla_ae_lines xal
where xal.ae_header_id in (14101317, 14103708, 14299824)));
insert into bak.gl_je_headers_110707
select * from gl_je_headers gjh
where gjh.je_header_id =5553330;

--日记账行
create table bak.gl_je_lines_110707 as
select *
from gl_je_lines gjl
where gjl.je_header_id in
(select je_header_id
from gl.gl_import_references gr
where gr.gl_sl_link_id in

(select gl_sl_link_id
from xla.xla_ae_lines xal
where xal.ae_header_id in (14101317, 14103708, 14299824)));
insert into bak.gl_je_lines_110707
select *
from gl_je_lines gjl
where gjl.je_header_id=5553330;
--日记帐批
create table bak.gl_je_batches_110707 as
select *
from gl_je_batches gjb
where gjb.je_batch_id in
(select je_batch_id
from gl.gl_import_references gr
where gr.gl_sl_link_id in
(select gl_sl_link_id
from xla.xla_ae_lines xal
where xal.ae_header_id in (14101317, 14103708, 14299824)));


--发票
delete from AP_INVOICES_ALL aia
where aia.invoice_id in (90490,90333)
--发票行
delete from AP_INVOICE_LINES_ALL ala
where ala.invoice_id in (90490,90333);
--分配
delete from Ap_Invoice_Distributions_All aid
where aid.invoice_id = 90490;
--计划付款
delete from AP_PAYMENT_SCHEDULES_ALL p
where p.invoice_id in (90490,90333);
--暂挂
delete from AP_HOLDS_ALL h
where h.invoice_id = 90490;
--付款行
delete from AP_INVOICE_PAYMENTS_all aip
where aip.invoice_id = 90333;
--付款头
delete from AP_CHECKS_ALL ac where ac.check_id = 64863;
--分录事件
delete from xla.xla_transaction_entities xte
where xte.source_id_int_1 in (90490, 90333)
and xte.security_id_int_1 = 81
and application_id = 200;
delete from xla.xla_transaction_entities xte
where xte.source_id_int_1 in (64863)
and xte.security_id_int_1 = 81
and application_id = 200;

--分录头
delete from xla.xla_ae_headers xah
where xah.entity_id in (9556541, 9554363);

delete from xla.xla_ae_headers xah where xah.entity_id in (9554366);
--会计事件
delete from xla_events xe
where xe.event_id in
(select event_id
from bak.xla_ae_headers_110707 xah
where xah.entity_id in (9556541, 9554363));
delete from xla_events xe
where xe.event_id in
(select event_id
from bak.xla_ae_headers_110707 xah
where xah.entity_id in (9554366));
--分录行
delete from xla.xla_ae_lines xal
where xal.ae_header_id in (14101317, 14103708, 14299824);

delete from xla.xla_ae_lines xal
where xal.ae_header_id=14101322;
--日记账导入参考
delete from gl.gl_import_references gr
where gr.gl_sl_link_id in
(select gl_sl_link_id
from bak.xla_ae_lines_110707 xal
where xal.ae_header_id in (14101317, 14103708, 14299824));
delete from gl.gl_import_references gr
where gr.gl_sl_link_id in (25174221,25174222);
--日记账头
delete from gl_je_headers gjh
where gjh.je_header_id in
(select je_header_id
from bak.gl_import_references_110707 gr
where gr.gl_sl_link_id in
(select gl_sl_link_id
from bak.xla_ae_lines_110707 xal
where xal.ae_header_id in (14101317, 14103708, 14299824)));
delete from gl_je_headers gjh
where gjh.je

_header_id =5553330;
--日记账行
delete from gl_je_lines gjl
where gjl.je_header_id in
(select je_header_id
from bak.gl_import_references_110707 gr
where gr.gl_sl_link_id in
(select gl_sl_link_id
from bak.xla_ae_lines_110707 xal
where xal.ae_header_id in (14101317, 14103708, 14299824)));
delete from gl_je_lines gjl
where gjl.je_header_id=5553330;

--日记帐批
delete from gl_je_batches gjb where gjb.je_batch_id=5007897
select rowid,gjb.*
from gl_je_batches gjb
where gjb.je_batch_id in
(select je_batch_id
from bak.gl_import_references_110707 gr
where gr.gl_sl_link_id in
(select gl_sl_link_id
from bak.xla_ae_lines_110707 xal
where xal.ae_header_id in (14101317, 14103708, 14299824)));