[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select USER_PROFILE_OPTION_NAME
from fnd_profile_options_vl
where application_id=510
and PROFILE_OPTION_NAME = p_profile_option;
select SQL_ID from (
select SQL_ID, XST_ID
from OKC_REPORT_XST
connect by ID = prior XST_ID
start with ID = fnd_profile.value('OKC_WEB_REPORT')
) where SQL_ID is not NULL;
select XSL_ID
from OKC_REPORT_XST
where ID = fnd_profile.value('OKC_WEB_REPORT');
select name, help_text
from okc_report_sql_v
where id = p_sql;
select name, help_text
from okc_report_xsl_v
where id = p_xsl;
select V.MAJOR_VERSION, V.MINOR_VERSION
from
okc_k_vers_numbers_v V
where V.CHR_ID = pp_chr_id
and (pp_major_version is NULL or pp_major_version = V.MAJOR_VERSION)
union all
select pp_major_version, max(H.MINOR_VERSION) MINOR_VERSION
from okc_k_vers_numbers_v V1,
OKC_K_VERS_NUMBERS_H H
where V1.CHR_ID = pp_chr_id
and H.CHR_ID = pp_chr_id
and H.MAJOR_VERSION = pp_major_version
and H.MAJOR_VERSION <> V1.MAJOR_VERSION
;
--'I' - inserted empty, or old and obsolete; locked
select S.CACHE_YN, S.LAST_UPDATE_DATE, S.id SQL_ID
from OKC_REPORT_SQL_V S, OKC_REPORT_XST T
where T.id = p_xid and T.sql_id = S.id
and sysdate between S.start_date and nvl(S.end_date,sysdate);
select '!'
from
okc_report_xml_v M
where M.CHR_ID = pp_chr_id
and M.MAJOR_VERSION = pp_major_version
and M.MINOR_VERSION = pp_minor_version
and M.SQL_ID = pp_sql_id
and M.LAST_UPDATE_DATE > pp_sql_date
and M.XML_TEXT is not NULL
;
select M.XML_TEXT
from
okc_report_xml_v M
where M.CHR_ID = pp_chr_id
and M.MAJOR_VERSION = pp_major_version
and M.MINOR_VERSION = pp_minor_version
and M.SQL_ID = pp_sql_id
and M.LAST_UPDATE_DATE > pp_sql_date
and M.XML_TEXT is not NULL
;
delete from okc_report_xml M
where M.CHR_ID = G_kid
and M.MAJOR_VERSION = G_vid
and M.LANGUAGE = userenv('LANG')
and M.SQL_ID = l_sql_id
;
insert into okc_report_xml
( CHR_ID
,MAJOR_VERSION
,LANGUAGE
,SQL_ID
,MINOR_VERSION
,XML_TEXT
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
) values
( G_kid,
G_vid,
userenv('LANG'),
l_sql_id,
G_mid,
empty_clob(),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
);
select XML_TEXT into x_xml_text
from okc_report_xml
where CHR_ID = G_kid
and MAJOR_VERSION = G_vid
and LANGUAGE = userenv('LANG')
and SQL_ID = l_sql_id
for update of XML_TEXT nowait;
cursor last_update_csr(p_xst number) is
select greatest(
max(S.LAST_UPDATE_DATE),
max(Q.LAST_UPDATE_DATE),
max(T.LAST_UPDATE_DATE)) d
from
(select SQL_ID, XSL_ID, LAST_UPDATE_DATE
from OKC_REPORT_XST
connect by ID = prior XST_ID
start with ID = p_xst) T,
OKC_REPORT_XSL_V S,
OKC_REPORT_SQL_V Q
where T.XSL_ID = S.ID
and T.SQL_ID = Q.ID (+);
open last_update_csr(p_xst_id);
fetch last_update_csr into l_date;
close last_update_csr;
--'I' - inserted empty, or old and obsolete; locked
select T.CACHE_YN
from OKC_REPORT_XST_V T, OKC_REPORT_XSL_V L
where T.id = p_xst
and T.XSL_ID = L.ID
and sysdate between L.start_date and nvl(L.end_date,sysdate);
select '!'
from
okc_report_htm_v H
where H.CHR_ID = pp_chr_id
and H.MAJOR_VERSION = pp_major_version
and H.MINOR_VERSION = pp_minor_version
and H.XST_ID = pp_xst_id
and H.SCN_ID = pp_scn_id
and H.LAST_UPDATE_DATE > pp_xst_date
and H.HTM_TEXT is not NULL
;
select H.HTM_TEXT
from
okc_report_htm_v H
where H.CHR_ID = pp_chr_id
and H.MAJOR_VERSION = pp_major_version
and H.MINOR_VERSION = pp_minor_version
and H.XST_ID = pp_xst_id
and H.SCN_ID = pp_scn_id
and H.LAST_UPDATE_DATE > pp_xst_date
and H.HTM_TEXT is not NULL
;
delete from okc_report_htm H
where H.CHR_ID = G_kid
and H.MAJOR_VERSION = G_vid
and H.LANGUAGE = userenv('LANG')
and H.XST_ID = G_xid
and H.SCN_ID = 0
;
insert into okc_report_htm
( CHR_ID
,MAJOR_VERSION
,LANGUAGE
,XST_ID
,SCN_ID
,MINOR_VERSION
,HTM_TEXT
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
) values
( G_kid,
G_vid,
userenv('LANG'),
G_xid,
0,
G_mid,
empty_clob(),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
);
select HTM_TEXT into x_htm_text
from okc_report_htm
where CHR_ID = G_kid
and MAJOR_VERSION = G_vid
and LANGUAGE = userenv('LANG')
and XST_ID = G_xid
and SCN_ID = 0
for update of HTM_TEXT nowait;
select '!' from okc_k_headers_b
where id=p_kid;
select '!'
from
okc_report_xst X,
okc_report_prm_v P
where X.ID = p_xid
and P.sql_id = X.sql_id
and P.code not in ('xid', 'kid', 'vid', 'content_type');
select to_char(id) from okc_k_headers_b
where contract_number = p_kn;
select value
from NLS_SESSION_PARAMETERS
where PARAMETER=param;
select lower(host_name)||'_'||lower(instance_name)||'.dbc' into l_dbc
from v$instance;
select wf_mail.URLENCODE(value) into l_value
from NLS_SESSION_PARAMETERS
where PARAMETER='NLS_LANGUAGE';
select wf_mail.URLENCODE(value) into l_value
from NLS_SESSION_PARAMETERS
where PARAMETER='NLS_TERRITORY';
select '!'
from OKC_K_HEADERS_TL
where ID = p_chr_id
and LANGUAGE = userenv('LANG')
for update of last_update_date, last_updated_by
nowait;
update OKC_K_HEADERS_TL
set last_updated_by = fnd_global.user_id,
last_update_date = sysdate+1/144
where ID = p_chr_id
and LANGUAGE = userenv('LANG');
select '!'
from OKC_K_HEADERS_TL
where ID = p_chr_id
and LANGUAGE = userenv('LANG')
and last_updated_by = fnd_global.user_id
and last_update_date > sysdate
for update of last_update_date
nowait;
select name from okc_report_sql_v
where id=p_sql;
select name from okc_report_xsl_v
where id=p_xsl;
select
'XSL' TEMPLATE_TYPE,
S.NAME TEMPLATE_NAME
from
( select XSL_ID,SQL_ID
from OKC_REPORT_XST
connect by ID = prior XST_ID
start with ID = fnd_profile.value('OKC_WEB_REPORT')) T,
OKC_REPORT_XSL_V S
where T.XSL_ID = S.ID
and not(sysdate between S.START_DATE and NVL(S.END_DATE,sysdate))
union all
select
'SQL' TEMPLATE_TYPE,
Q.NAME TEMPLATE_NAME
from
( select XSL_ID,SQL_ID
from OKC_REPORT_XST
connect by ID = prior XST_ID
start with ID = fnd_profile.value('OKC_WEB_REPORT')) T,
OKC_REPORT_SQL_V Q
where T.SQL_ID = Q.ID
and not(sysdate between Q.START_DATE and NVL(Q.END_DATE,sysdate))
;
select '"'||USER_PROFILE_OPTION_NAME||'"'
from fnd_profile_options_vl
where application_id=510
and PROFILE_OPTION_NAME = 'OKC_WEB_REPORT'
;
select '!'
from OKC_REPORT_XST T, OKC_REPORT_XSL_V L
where T.ID = fnd_profile.value('OKC_WEB_REPORT')
and T.XSL_ID = L.ID;
select id
from okc_k_articles_v
where dnz_chr_id = p_chr
and chr_id = p_chr
minus
SELECT SC.cat_id id
from okc_section_contents_v SC
WHERE SC.scn_id in
(SELECT id
from okc_sections_b
connect by prior id = scn_id
start with chr_id = p_chr
and scn_id is NULL);
select '!'
from okc_sections_v
where CHR_ID = p_chr
;
select '!'
from okc_k_lines_v
where CHR_ID = p_chr
;
cursor sql_csr is select name from okc_report_sql_v
where id=p_sql_id;
select NVL(max(id),10000)+1 into l_n
from okc_report_sql_b
where id between 10001 and 20000-1;
select NVL(max(id),10000)+1 into l_n
from okc_report_xsl_b
where id between 10001 and 20000-1;
select NVL(max(id),10000)+1 into l_n
from okc_report_xst
where id between 10001 and 20000-1;
select name
from okc_report_sql_v
where nvl(end_date,sysdate)>=sysdate
group by name
having count(1)>1;
select name
from okc_report_xsl_v
where nvl(end_date,sysdate)>=sysdate
group by name
having count(1)>1;
select T.SQL_ID
from OKC_REPORT_XST T, OKC_REPORT_XSL_V L
where T.ID = fnd_profile.value('OKC_WEB_REPORT')
and T.XSL_ID = L.ID;
select code,
prompt,
type,
required_yn,
lov_query,
OKC_PARAMETERS_PUB.get(code) value
from OKC_REPORT_PRM_V
where sql_id = p
order by sequence_num;
open c2 for 'select 1 a from ('||r.lov_query||') where id = :1' using r.value;
'select 1 a from ('||r.lov_query||') where id = :1 and name=:2'
using r.value, nv;
'select id from ('||r.lov_query||') where name=:2'
using nv;
'select id from ('||r.lov_query||') where name like :2'
using nv;
select replace(prompt,'<',fnd_global.local_chr(38)||'lt;'), lov_query
select replace(MEANING,'<',fnd_global.local_chr(38)||'lt;') from fnd_lookups
select replace(MEANING,'<',fnd_global.local_chr(38)||'lt;') from fnd_lookups
select replace(description,'<',fnd_global.local_chr(38)||'lt;') from fnd_lookups
select HTM_TEXT
from okc_report_htm_v
where CHR_ID = pp_kid
and MAJOR_VERSION = pp_vid
and XST_ID = pp_xid;
select
Contract_number||
decode(Contract_number_modifier,NULL,NULL,' - ')||
Contract_number_modifier
from okc_k_headers_b
where id = k;
select
S.NAME
from okc_report_xst T,
okc_report_sql_v Q,
okc_report_xsl_v S
where T.id = x
and T.sql_id = Q.id
and T.xsl_id = S.id;
select
MAJOR_VERSION
from OKC_K_VERS_NUMBERS_V
where CHR_ID = k;
select
Contract_number
,Contract_number_modifier
from okc_k_headers_b
where id = k;
select
Q.NAME NAME,
S.NAME DESCRIPTION
from okc_report_xst T,
okc_report_sql_tl Q,
okc_report_xsl_tl S
where T.id = x
and T.sql_id = Q.id
and T.xsl_id = S.id
and Q.LANGUAGE = userenv('LANG')
and S.LANGUAGE = userenv('LANG')
;