EBS alert 添加期间关闭及打开提醒 sql

select t.short_name, t.application_name, t.period_name, t.closing_status
– into &string1, &string2, &string3, &string4
from (select gl.short_name,
decode(fa.application_short_name,
‘SQLAP’,
‘AP’,
fa.application_short_name) application_name,
u.period_name,
u.CLOSING_STATUS
from apps.GL_PERIOD_STATUSES_V u,
APPS.FND_APPLICATION_VL fa,
apps.gl_ledgers gl
where 1 = 1
and gl.ledger_id = u.ledger_id
and fa.application_id = u.application_id
and u.ledger_id in (‘204’, ‘4’, ‘1’, ‘409’, ‘407’, ‘405’, ‘325’,
‘367’, ‘365’, ‘345’)
and fa.application_short_name in (‘SQLAP’)
and u.period_name = to_char(sysdate + 1, ‘MON-YY’)
and u.closing_status <> ‘O’
union all
select gl.short_name,
fa.application_short_name,
u.period_name,
u.CLOSING_STATUS
from apps.GL_PERIOD_STATUSES_V u,
APPS.FND_APPLICATION_VL fa,
apps.gl_ledgers gl
where 1 = 1
and gl.ledger_id = u.ledger_id
and fa.application_id = u.application_id
and u.ledger_id in
(‘4’, ‘1’, ‘409’, ‘407’, ‘405’, ‘325’, ‘367’, ‘365’, ‘345’)
and fa.application_short_name in (‘PO’)
and u.period_name = to_char(sysdate + 1, ‘MON-YY’)
and u.closing_status <> ‘O’
union all
select gl.short_name,
decode(fa.application_short_name,
‘SQLAP’,
‘AP’,
fa.application_short_name) application_name,
u.period_name,
u.CLOSING_STATUS
from apps.GL_PERIOD_STATUSES_V u,
APPS.FND_APPLICATION_VL fa,
apps.gl_ledgers gl
where 1 = 1
and gl.ledger_id = u.ledger_id
and fa.application_id = u.application_id
and u.ledger_id in (‘204’, ‘4’, ‘1’, ‘409’, ‘407’, ‘405’, ‘325’,
‘367’, ‘365’, ‘345’)
and fa.application_short_name in (‘SQLAP’)
and u.period_name = to_char(add_months(sysdate, -1), ‘MON-YY’)
and u.closing_status = ‘O’
union all
select gl.short_name,
fa.application_short_name,
u.period_name,
u.CLOSING_STATUS
from apps.GL_PERIOD_STATUSES_V u,
APPS.FND_APPLICATION_VL fa,
apps.gl_ledgers gl
where 1 = 1
and gl.ledger_id = u.ledger_id
and fa.application_id = u.application_id
and u.ledger_id in
(‘4’, ‘1’, ‘409’, ‘407’, ‘405’, ‘325’, ‘367’, ‘365’, ‘345’)
and fa.application_short_name in (‘PO’)
and u.period_name = to_char(add_months(sysdate, -1), ‘MON-YY’)
and u.closing_status = ‘O’) t
;