[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select t.name into result
from
oe_blanket_headers_all h,
oe_transaction_types_all a,
oe_transaction_types_tl t
where t.transaction_type_id = h.order_type_id
and a.transaction_type_id = t.transaction_type_id
and t.language = USERENV('LANG')
and a.sales_document_type_code = 'B'
and h.order_number = p_order_number;
select t.name into result
from
oe_order_headers_all h,
oe_transaction_types_all a,
oe_transaction_types_tl t
where t.transaction_type_id = h.order_type_id
and a.transaction_type_id = t.transaction_type_id
and t.language = USERENV('LANG')
and a.sales_document_type_code = 'O'
and h.order_number = p_order_number;
select article_title into result
from okc_articles_all a, okc_article_versions v
where a.article_id = v.article_id
and article_version_id = p_article_version_id;
select nvl(display_name, article_title) into result
from okc_articles_all a, okc_article_versions v
where a.article_id = v.article_id
and article_version_id = p_article_version_id;
select article_version_number into result
from okc_articles_all a, okc_article_versions v
where a.article_id = v.article_id
and article_version_id = p_article_version_id;
select article_id into result
from okc_article_versions
where article_version_id = p_article_version_id;
select org_id into result
from okc_articles_all a, okc_article_versions v
where a.article_id = v.article_id
and article_version_id = p_article_version_id;
select a.article_id, org_id into l_article_id, l_org_id
from okc_articles_all a, okc_article_versions v
where a.article_id = v.article_id
and article_version_id = p_article_version_id;
select article_version_id into result
from okc_article_versions
where article_id = l_article_id
and article_status in ('APPROVED','ON_HOLD')
and (start_date, article_version_number) = (
select
max(start_date),
max(article_version_number)
from
okc_article_versions
where article_id = l_article_id
and article_status in ('APPROVED','ON_HOLD')
);
select article_version_id into result
from okc_article_versions
where article_id = l_article_id
and global_yn = 'Y'
and article_status in ('APPROVED','ON_HOLD')
and (start_date, article_version_number) = (
select
max(start_date),
max(article_version_number)
from
okc_article_versions
where article_id = l_article_id
and article_status in ('APPROVED','ON_HOLD')
and exists (
select 1 from okc_article_adoptions
where global_article_version_id = article_version_id
and local_org_id = p_org_id
and adoption_type = 'ADOPTED'
and adoption_status = 'APPROVED'
)
);
select name into result
from okc_bus_doc_types_tl
where document_type = p_code
and language = userenv('LANG');
select party_name into result
from hz_parties p,
pon_bid_headers b
where trading_partner_id = party_id
and bid_status IN ('ACTIVE','DISQUALIFIED')
and auction_header_id = p_auction_header_id;
delete okc_ca_documents_gt
where srch_id = p_search_id;
delete okc_ca_art_versions_gt
where article_srch_id = p_search_id
and article_flag = 'S';
select
article_srch_id,
art.article_id,
article_version_id,
art.org_id,
article_title,
article_type,
article_version_number,
decode(global_yn, 'Y', 'GLOBAL', nvl(v.adoption_type, 'LOCAL')) adoption_type,
org_name
from
okc_articles_all art,
okc_article_versions v,
( select
orgu.organization_id org_id,
orgu.name org_name
from
hr_organization_information orgi,
hr_operating_units orgu
where orgi.org_information_context = 'OKC_TERMS_LIBRARY_DETAILS'
and orgi.organization_id = orgu.organization_id
) org,
( select
target_article_id,
org_id,
article_srch_id
from
okc_article_relatns_all,
okc_ca_art_versions_gt
where source_article_id = article_id
and org_id = article_org_id
and article_srch_id = p_search_id
and article_flag = 'U'
) r
where art.article_id = v.article_id
and art.article_id = r.target_article_id
and art.org_id = r.org_id
and article_status in ('APPROVED','ON_HOLD')
and (start_date, article_version_number) = (
select
max(start_date),
max(article_version_number)
from
okc_article_versions
where article_id = art.article_id
and article_status in ('APPROVED','ON_HOLD')
)
and org.org_id = art.org_id
union all
select
article_srch_id,
v.article_id,
v.article_version_id,
a.local_org_id,
article_title,
article_type,
article_version_number,
'ADOPTED' adoption_type,
org_name
from
okc_articles_all art,
okc_article_adoptions a,
okc_article_versions v,
( select
orgu.organization_id org_id,
orgu.name org_name
from
hr_organization_information orgi,
hr_operating_units orgu
where orgi.org_information_context = 'OKC_TERMS_LIBRARY_DETAILS'
and orgi.organization_id = orgu.organization_id
) org,
( select
target_article_id,
org_id,
article_srch_id
from
okc_article_relatns_all,
okc_ca_art_versions_gt
where source_article_id = article_id
and org_id = article_org_id
and article_srch_id = p_search_id
and article_flag = 'U'
) r
where art.article_id = v.article_id
and a.global_article_version_id = v.article_version_id
and v.article_id = r.target_article_id
and a.local_org_id = r.org_id
and article_status in ('APPROVED','ON_HOLD')
and a.adoption_type = 'ADOPTED'
and adoption_status = 'APPROVED'
and global_yn = 'Y'
and (start_date, article_version_number) = (
select
max(start_date),
max(article_version_number)
from
okc_article_versions v1,
okc_article_adoptions a1
where global_yn = 'Y'
and v1.article_version_id = a1.global_article_version_id
and v1.article_status in ('APPROVED','ON_HOLD')
and a1.adoption_type = 'ADOPTED'
and a1.adoption_status = 'APPROVED'
and a1.local_org_id = r.org_id
and v1.article_id = v.article_id)
and org.org_id = r.org_id
) alt
ON (
gt.article_srch_id = alt.article_srch_id
and gt.article_id = alt.article_id
and gt.article_version_id = alt.article_version_id
and gt.article_org_id = alt.org_id
)
when matched then
update set gt.object_version_number = gt.object_version_number+1
when not matched then
insert (
gt.article_srch_id,
gt.article_id,
gt.article_version_id,
gt.article_org_id,
gt.article_flag,
gt.article_title,
gt.article_type,
gt.article_standard_yn,
gt.article_version_number,
gt.article_adoption_type,
gt.article_org_name,
gt.std_article_title,
gt.std_article_version_number,
gt.object_version_number)
values (
alt.article_srch_id,
alt.article_id,
alt.article_version_id,
alt.org_id,
'S',
alt.article_title,
alt.article_type,
'Y',
alt.article_version_number,
alt.adoption_type,
alt.org_name,
null,null,1);
select
article_srch_id,
article_id,
article_version_id,
a.local_org_id,
article_title,
article_type,
article_version_number,
'ADOPTED' adoption_type,
org_name
from
okc_article_adoptions a,
okc_ca_art_versions_gt t,
( select
orgu.organization_id org_id,
orgu.name org_name
from
hr_organization_information orgi,
hr_operating_units orgu
where orgi.org_information_context = 'OKC_TERMS_LIBRARY_DETAILS'
and orgi.organization_id = orgu.organization_id
) org
where article_srch_id = p_search_id
-- and article_flag = 'U'
and global_article_version_id = article_version_id
and article_adoption_type = 'GLOBAL'
and a.adoption_type = 'ADOPTED'
and a.adoption_status = 'APPROVED'
and article_standard_yn = 'Y'
and org.org_id = a.local_org_id
) ado
on (
gt.article_srch_id = ado.article_srch_id
and gt.article_id = ado.article_id
and gt.article_version_id = ado.article_version_id
and gt.article_org_id = ado.local_org_id
)
when matched then
update set gt.object_version_number = gt.object_version_number+1
when not matched then
insert (
gt.article_srch_id,
gt.article_id,
gt.article_version_id,
gt.article_org_id,
gt.article_flag,
gt.article_title,
gt.article_type,
gt.article_standard_yn,
gt.article_version_number,
gt.article_adoption_type,
gt.article_org_name,
gt.std_article_title,
gt.std_article_version_number,
gt.object_version_number)
values (
ado.article_srch_id,
ado.article_id,
ado.article_version_id,
ado.local_org_id,
'S',
ado.article_title,
ado.article_type,
'Y',
ado.article_version_number,
ado.adoption_type,
ado.org_name,
null,null,1);
select
article_srch_id,
t.article_id,
a.article_version_id,
t.article_org_id,
t.article_title,
t.article_type,
a.article_version_number,
article_adoption_type,
org_name
from
okc_article_versions a,
okc_ca_art_versions_gt t,
( select
orgu.organization_id org_id,
orgu.name org_name
from
hr_organization_information orgi,
hr_operating_units orgu
where orgi.org_information_context = 'OKC_TERMS_LIBRARY_DETAILS'
and orgi.organization_id = orgu.organization_id
) org
where t.article_srch_id = p_search_id
and a.article_id = t.article_id
and article_adoption_type in ('LOCAL','GLOBAL','LOCALIZED')
and article_status in ('APPROVED','ON_HOLD')
and org.org_id = article_org_id
union all
select
article_srch_id,
t.article_id,
a.article_version_id,
t.article_org_id,
t.article_title,
t.article_type,
a.article_version_number,
'ADOPTED' adoption_type,
org_name
from
okc_article_versions a,
okc_ca_art_versions_gt t,
( select
orgu.organization_id org_id,
orgu.name org_name
from
hr_organization_information orgi,
hr_operating_units orgu
where orgi.org_information_context = 'OKC_TERMS_LIBRARY_DETAILS'
and orgi.organization_id = orgu.organization_id
) org
where t.article_srch_id = p_search_id
and a.article_id = t.article_id
and t.article_adoption_type = 'ADOPTED'
and exists (
select 1
from okc_article_adoptions
where local_org_id = t.article_org_id
and global_article_version_id = a.article_version_id
and adoption_type = 'ADOPTED'
and adoption_status = 'APPROVED'
)
and org.org_id = article_org_id
) ver
on (
gt.article_srch_id = ver.article_srch_id
and gt.article_id = ver.article_id
and gt.article_version_id = ver.article_version_id
and gt.article_org_id = ver.article_org_id
)
when matched then
update set gt.object_version_number = gt.object_version_number+1
when not matched then
insert (
gt.article_srch_id,
gt.article_id,
gt.article_version_id,
gt.article_org_id,
gt.article_flag,
gt.article_title,
gt.article_type,
gt.article_standard_yn,
gt.article_version_number,
gt.article_adoption_type,
gt.article_org_name,
gt.std_article_title,
gt.std_article_version_number,
gt.object_version_number)
values (
ver.article_srch_id,
ver.article_id,
ver.article_version_id,
ver.article_org_id,
'S',
ver.article_title,
ver.article_type,
'Y',
ver.article_version_number,
ver.article_adoption_type,
ver.org_name,
null,null,1);
select
article_srch_id,
a.article_id,
v.article_version_id,
a.org_id,
a.article_title,
a.article_type,
v.article_version_number,
'NON-STANDARD' adoption_type,
org_name,
t.article_title std_article_title,
t.article_version_number std_article_version_number
from
okc_articles_all a,
okc_article_versions v,
okc_ca_art_versions_gt t,
( select
orgu.organization_id org_id,
orgu.name org_name
from
hr_organization_information orgi,
hr_operating_units orgu
where orgi.org_information_context = 'OKC_TERMS_LIBRARY_DETAILS'
and orgi.organization_id = orgu.organization_id
) org
where a.article_id = v.article_id
and standard_yn = 'N'
and article_srch_id = p_search_id
and t.article_version_id = std_article_version_id
and t.article_org_id = org.org_id
and t.article_org_id = a.org_id
) non
on (
gt.article_srch_id = non.article_srch_id
and gt.article_id = non.article_id
and gt.article_version_id = non.article_version_id
and gt.article_org_id = non.org_id
and gt.article_standard_yn = 'N'
)
when matched then
update set gt.object_version_number = gt.object_version_number+1
when not matched then
insert (
gt.article_srch_id,
gt.article_id,
gt.article_version_id,
gt.article_org_id,
gt.article_flag,
gt.article_title,
gt.article_type,
gt.article_standard_yn,
gt.article_version_number,
gt.article_adoption_type,
gt.article_org_name,
gt.std_article_title,
gt.std_article_version_number,
gt.object_version_number)
values (
non.article_srch_id,
non.article_id,
non.article_version_id,
non.org_id,
'S',
non.article_title,
non.article_type,
'N',
non.article_version_number,
null,
-- non.adoption_type,
non.org_name,
non.std_article_title,
non.std_article_version_number,
1);
select nvl(art.article_number, decode(std_article_version_id, null, art.article_number,
(select
a1.article_number
from
okc_articles_all a,
okc_article_versions v,
okc_article_versions v1,
okc_articles_all a1
where
a.article_id = v.article_id
and v.std_article_version_id = v1.article_version_id
and v1.article_id = a1.article_id
and a.article_id = art.article_id)
)) article_number into result
from okc_articles_all art,
okc_article_versions ver
where art.article_id = ver.article_id
and art.article_id = p_article_id
and rownum <= 1;