[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
cursor av_csr is select table_name,Column_Name ,data_type,data_length,NVL(data_precision,OKC_API.G_MISS_NUM)
data_precision,NVL(data_scale,0) data_scale
FROM user_tab_columns
WHERE table_name = UPPER( p_view_name) and (data_type='VARCHAR2' OR data_type='NUMBER');
cursor c1 is select value from v$nls_parameters where parameter='NLS_NUMERIC_CHARACTERS';
l_query:='select id from ' || p_view_name || ' where ' || p_col_name || ' =:l_value' ;
l_query:='select id from ' || p_view_name || ' where ' || p_col_name || ' =:l_value' ;
l_query:='select id from ' || p_view_name || ' where trunc(' || p_col_name || ' ) =trunc(:l_value)';
l_query:='select id from ' || p_view_name || ' where ';
l_pk_selected_str VARCHAR2(600):=G_SPECIAL_STR;
l_query:='select ' || l_pk_cols||' from ' || p_table_name || ' where ' || p_col_name || ' =:l_value';
FETCH unq_csr into l_pk_selected_str;
IF (l_pk_selected_str<>G_SPECIAL_STR AND l_pk_selected_str<>l_pk_in_str) THEN
x_return_status:=OKC_API.G_RET_STS_ERROR;
l_pk_selected_str VARCHAR2(600):=G_SPECIAL_STR;
l_query:='select ' || l_pk_cols||' from ' || p_table_name || ' where trunc(' || p_col_name || ') =trunc(:l_value)';
FETCH unq_csr into l_pk_selected_str;
IF (l_pk_selected_str<>G_SPECIAL_STR AND l_pk_selected_str<>l_pk_in_str) THEN
x_return_status:=OKC_API.G_RET_STS_ERROR;
l_pk_selected_str VARCHAR2(600):=G_SPECIAL_STR;
l_query:='select ' || l_pk_cols||' from ' || p_table_name || ' where ';
FETCH unq_csr into l_pk_selected_str;
IF (l_pk_selected_str<>G_SPECIAL_STR AND l_pk_selected_str<>l_pk_in_str) THEN
x_return_status:=OKC_API.G_RET_STS_ERROR;
l_query:='select ''1'' from ' || p_view_name || ' where ';
SELECT uhcv.pdf_using_id
FROM okc_process_defs_b pdfv,
okc_user_hook_calls_b uhcv
WHERE pdfv.id = uhcv.pdf_id
AND pdfv.package_name = upper(p_package_name)
AND pdfv.procedure_name = upper(p_procedure_name)
ORDER BY uhcv.run_sequence;
SELECT decode(pdf.package_name, null, rtrim(pdf.procedure_name),
rtrim(pdf.package_name) || '.' || rtrim(pdf.procedure_name)) proc_name
FROM okc_process_defs_b pdf;
SELECT 'S'
FROM fnd_lookups fndlup
where fndlup.lookup_type = p_type
and fndlup.lookup_code = p_code
and trunc(sysdate) between
trunc(nvl(fndlup.start_date_active,sysdate))
and
nvl(fndlup.end_date_active,sysdate);
SELECT 'S'
FROM okc_rule_defs_v fndlup
where fndlup.rule_code = p_code;
SELECT FROM_TABLE, WHERE_CLAUSE
FROM JTF_OBJECTS_B
WHERE OBJECT_CODE = p_object_code;
l_sql_stmt := 'SELECT name FROM ' || l_from_table ||
' WHERE ID1 = :id_1 AND ID2 = :id2';
SELECT FROM_TABLE, WHERE_CLAUSE
FROM JTF_OBJECTS_B
WHERE OBJECT_CODE = p_object_code;
l_sql_stmt := 'SELECT description FROM ' || l_from_table ||
' WHERE ID1 = :id_1 AND ID2 = :id2';
SELECT FROM_TABLE, WHERE_CLAUSE
FROM JTF_OBJECTS_B
WHERE OBJECT_CODE = p_object_code;
l_sql_stmt := 'SELECT name,description FROM ' || l_from_table ||
' WHERE ID1 = :id_1 AND ID2 = :id2';
SELECT from_table, where_clause, order_by_clause
FROM jtf_objects_b
WHERE object_code = p_object_code;
FUNCTION GET_SELECTNAME_FROM_JTFV(
p_object_code IN VARCHAR2,
p_id IN NUMBER)
RETURN VARCHAR2 IS
l_selname VARCHAR2(2000);
l_select_id VARCHAR2(2000);
l_select_name VARCHAR2(2500);
SELECT SELECT_ID,SELECT_NAME,FROM_TABLE,WHERE_CLAUSE
FROM JTF_OBJECTS_B
WHERE OBJECT_CODE = p_object_code;
FETCH jtfv_csr INTO l_select_id,l_select_name,l_from_table,l_where_clause;
l_sql_stmt := 'SELECT contract_number||'||''''||' '||''''||'||contract_number_modifier||'||''''||' '||''''||'||line_number'||' '||
'FROM okc_k_headers_b khr,okc_k_lines_b khl,okc_condition_headers_b cnh WHERE khr.id = khl.dnz_chr_id and cnh.object_id = khl.id and khl.Id = :id';
l_sql_stmt := 'SELECT ' || l_select_name || ' FROM ' || l_from_table || ' WHERE ' || l_select_id || ' = :id';
/*l_sql_stmt := 'SELECT ' || l_select_name || ' FROM ' || l_from_table;
p_update_attachment IN VARCHAR2 ,
p_orig_source_code IN VARCHAR2)
RETURN Varchar2 IS
l_scs_code okc_k_headers_b.scs_code%TYPE;
SELECT scs_code,nvl(orig_system_source_code,'NOSOURCECODE')
FROM okc_k_headers_b
WHERE id = p_chr_id;
SELECT ras.access_level
FROM okc_subclass_resps ras
WHERE ras.scs_code = l_scs_code
AND ras.resp_id = fnd_global.resp_id
AND l_date BETWEEN ras.start_date AND nvl(ras.end_date, l_date);
SELECT res.resource_id
FROM jtf_rs_resource_extns res,
jtf_rs_role_relations rrr,
jtf_rs_roles_b rr
WHERE res.user_id = fnd_global.user_id
AND l_date between res.start_date_active
and nvl(res.end_date_active, l_date)
AND res.resource_id = rrr.role_resource_id
AND rrr.role_resource_type = 'RS_INDIVIDUAL'
AND nvl(rrr.delete_flag,'N') = 'N'
AND l_date between rrr.start_date_active
and nvl(rrr.end_date_active, l_date)
AND rrr.role_id = rr.role_id
AND rr.role_type_code = 'CONTRACTS';
SELECT cas.access_level
FROM okc_k_accesses cas
WHERE cas.chr_id = p_chr_id
AND cas.resource_id = g_user_resource_id;
SELECT cas.group_id,
cas.access_level
FROM okc_k_accesses cas
WHERE cas.chr_id = p_chr_id
AND cas.group_id is not null
ORDER BY 2 DESC;
SELECT rgm.group_id
FROM jtf_rs_group_members rgm,
jtf_rs_role_relations rrr,
jtf_rs_roles_b rr,
jtf_rs_groups_b rgb
WHERE rgm.resource_id = g_user_resource_id
AND rgm.group_id = rgb.group_id
AND l_date between nvl(rgb.start_date_active, l_date)
and nvl(rgb.end_date_active, l_date)
AND rgm.group_id = rrr.role_resource_id
AND nvl(rgm.delete_flag,'N') = 'N'
AND rrr.role_resource_type = 'RS_GROUP'
AND nvl(rrr.delete_flag,'N') = 'N'
AND l_date between rrr.start_date_active
and nvl(rrr.end_date_active, l_date)
AND rrr.role_id = rr.role_id
AND rr.role_type_code = 'CONTRACTS'
UNION
SELECT rgd.parent_group_id
FROM jtf_rs_group_members rgm,
jtf_rs_groups_denorm rgd,
jtf_rs_role_relations rrr,
jtf_rs_roles_b rr,
jtf_rs_groups_b rgb
WHERE rgm.resource_id = g_user_resource_id
AND nvl(rgm.delete_flag,'N') = 'N'
AND rgd.group_id = rgm.group_id
AND rgd.parent_group_id = rgb.group_id
AND l_date between nvl(rgb.start_date_active, l_date)
and nvl(rgb.end_date_active, l_date)
AND rgd.parent_group_id = rrr.role_resource_id
AND rrr.role_resource_type = 'RS_GROUP'
AND nvl(rrr.delete_flag,'N') = 'N'
AND l_date between rrr.start_date_active
and nvl(rrr.end_date_active, l_date)
AND rrr.role_id = rr.role_id
AND rr.role_type_code = 'CONTRACTS';
SELECT 'X'
FROM okc_k_headers_b
WHERE ID = p_contract_id;
SELECT authoring_org_id
FROM okc_k_headers_b
WHERE id = p_chr_id;
IF p_update_attachment = 'true' THEN
/* The contract created from forms,but can the attachment be updated in contracts online */
-- IF OKC_ASSENT_PUB.header_operation_allowed(p_chr_id,'UPDATE') = OKC_API.G_TRUE THEN
l_ret_status := l_modify_access;
END IF; -- if update attachment = true
select application_id
from okc_k_headers_b
where id = p_chr_id;
Select access_level
from okc_subclass_resps_v
where scs_code=p_scs_code
and resp_id=fnd_global.resp_id
and sysdate between start_date and nvl(end_date,sysdate);
Select meaning
from okc_subclasses_v
where code=p_scs_code;
update okc_std_art_versions_tl set text=(select text from okc_std_art_versions_tl
where sae_id=id and sav_release=release and language=lang)
where sae_id=id and sav_release=release and source_lang=lang and language<>lang;
UPDATE okc_article_versions
SET article_text = p_text,
object_version_number = object_version_number+1,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID,
last_update_date = Sysdate
WHERE article_version_id=(SELECT article_version_id
FROM okc_k_articles_b WHERE id = p_id);
select dbms_lob.getlength(text) into length from okc_k_articles_tl
where id = p_id and language = lang;
update okc_k_articles_tl
set text=(select text from okc_k_articles_tl
where id=p_id and language=lang)
where id = p_id and source_lang = lang and language <> lang;
update okc_k_articles_tl
set text=NULL
where id = p_id and language = lang;
update okc_k_articles_tl
set text= p_text
where id = p_id and language = lang;
UPDATE okc_article_versions
SET article_text
= (SELECT article_text FROM okc_article_versions
WHERE article_id=p_sae_id
AND sysdate BETWEEN start_date AND Nvl(end_date,Sysdate+1) ),
object_version_number = object_version_number+1,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID,
last_update_date = Sysdate
WHERE article_version_id
=(SELECT article_version_id
FROM okc_k_articles_b WHERE id = p_article_id);
SELECT sav_release
FROM okc_std_art_versions_b
WHERE sae_id = p_sae_id
AND date_active = (SELECT max(date_active)
FROM okc_std_art_versions_b
WHERE sae_id = p_sae_id
AND date_active <= sysdate);
update okc_k_articles_tl
set text = (select text
from okc_std_art_versions_tl
where sae_id = p_sae_id
and sav_release = l_release
and language = lang)
where id = p_article_id;
select g.short_name||decode(g.mrc_sob_type_code, 'N', NULL,
decode(l_multi_cur, 'N', NULL,
': ' || g.currency_code))
from okx_set_of_books_v g
where g.set_of_books_id=NVL(fnd_profile.value('GL_SET_OF_BKS_ID'),0);
select g.short_name||decode(g.mrc_sob_type_code, 'N', NULL,
decode(l_multi_cur, 'N', NULL,
': ' || g.currency_code))
from okx_set_of_books_v g, okx_organization_defs_v o
where o.id1=NVL(fnd_profile.value('ORG_ID'),0)
and g.set_of_books_id = to_number(o.set_of_books_id)
and o.organization_type= 'OPERATING_UNIT'
and o.information_type= 'Operating Unit Information' ;
SELECT nvl(multi_org_flag, 'N')
, nvl(multi_currency_flag, 'N')
INTO l_multi_org
, l_multi_cur
FROM fnd_product_groups;
SELECT pr.spid
,se.sid
,se.program
,se.module
INTO l_process_id
,l_session_id
,l_program
,l_module
FROM v$session se
,v$process pr
WHERE se.audsid = USERENV('SESSIONID')
AND se.paddr = pr.addr;
SELECT pa.value
INTO l_parameter_value
FROM v$parameter pa
WHERE pa.name = 'utl_file_dir';
--Select and Open the log file
l_trace_file.id:=FND_FILE.log;
--Select and Open the output file
l_output_file.id:=FND_FILE.output;
SELECT
SPID, S.AUDSID
INTO
osp_id, s_id
FROM
V$PROCESS P,
V$SESSION S
WHERE
S.AUDSID = USERENV('SESSIONID')
AND P.Addr = S.Paddr
AND rownum <= 1;
SELECT se.program
,se.module -- header and footer
INTO l_program
,l_module
FROM v$session se
,v$process pr
WHERE se.audsid = USERENV('SESSIONID')
AND se.paddr = pr.addr;
select max(ENDPOINT_NUMBER) into l_percent
from all_histograms
where owner=p_owner
and TABLE_NAME=p_table
and COLUMN_NAME=p_column;
select ENDPOINT_NUMBER into l_endpoint
from all_histograms
where owner=p_owner
and TABLE_NAME=p_table
and COLUMN_NAME=p_column
and ENDPOINT_VALUE=p_value;
select max(ENDPOINT_NUMBER) into l_startpoint
from all_histograms
where owner=p_owner
and TABLE_NAME=p_table
and COLUMN_NAME=p_column
and ENDPOINT_NUMBER
select sum(OKC_UTIL.get_prcnt('OKC','OKC_K_GRPINGS','CGP_PARENT_ID',g.id)) into d
from okc_k_groups_tl g
where g.name like p_grp_like and g.language=userenv('LANG');
select meaning
from FND_LOOKUP_VALUES
where language = userenv('LANG')
and NVL(enabled_flag,'N') = 'Y'
and lookup_type = p_lookup_type
and lookup_code = p_lookup_code;
select meaning
from okc_rule_defs_v
where rule_code = p_lookup_code;
SELECT application_name
FROM fnd_application_vl
WHERE application_id = p_application_id;
select description
from FND_LOOKUP_VALUES
where language = userenv('LANG')
and lookup_type = p_lookup_type
and lookup_code = p_lookup_code;
SELECT start_date, authoring_org_id
FROM OKC_K_HEADERS_B WHERE id=p_chr_id;
SELECT TEMPLATE_ID FROM okc_template_usages_v
WHERE document_type = x_doc_type AND document_id = x_doc_id ;
SELECT TEMPLATE_ID FROM okc_terms_templates_all
WHERE template_name = l_tmpl_name and org_id=l_org_id;
SELECT OKC_TERMS_TEMPLATES_ALL_S.NEXTVAL
INTO l_tmpl_id FROM DUAL;
INSERT INTO OKC_TERMS_TEMPLATES_ALL(
TEMPLATE_NAME,
TEMPLATE_ID,
WORKING_COPY_FLAG,
INTENT,
STATUS_CODE,
START_DATE,
GLOBAL_FLAG,
CONTRACT_EXPERT_ENABLED,
DESCRIPTION,
ORG_ID,
ORIG_SYSTEM_REFERENCE_CODE,
HIDE_YN,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE)
VALUES (
l_tmpl_name,
l_tmpl_id,
'N',
Decode( x_doc_type,'OKC_BUY','B','OKE_BUY','B', 'S'),
'APPROVED',
to_date('01-01-1951','DD-MM-YYYY'),
'N',
'N',
'Dummy Template for 11.5.10 Upgrade',
l_org_id,
decode (x_doc_type,'OKE_SELL', 'OKC11510UPG:OKE', 'OKE_BUY', 'OKC11510UPG:OKE', 'OKC11510UPG'),
decode(x_doc_type,'OKS','N','Y'),
1,
Fnd_Global.User_Id,
trunc(sysdate),
Fnd_Global.User_Id,
Fnd_Global.Login_Id,
trunc(sysdate)
);
INSERT INTO OKC_ALLOWED_TMPL_USAGES(
ALLOWED_TMPL_USAGES_ID,
TEMPLATE_ID,
DOCUMENT_TYPE,
DEFAULT_YN,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE)
VALUES (
OKC_ALLOWED_TMPL_USAGES_S.NEXTVAL,
l_tmpl_id,
x_doc_type,
'N',
1,
Fnd_Global.User_Id,
trunc(sysdate),
Fnd_Global.User_Id,
Fnd_Global.Login_Id,
trunc(sysdate)
);
SELECT language_code
BULK COLLECT INTO g_language_code
FROM fnd_languages
WHERE installed_flag IN ( 'I', 'B' );