APPS.OKC_ARTWF_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 6

		select
         art.org_id,
		   art.article_id,
   		artv.article_version_id,
   		artv.article_status,
   		artv.adoption_type,
   		artv.global_yn,
   		'AP-'||
         to_char(art.org_id)||'.'||
   		to_char(art.article_id)||'.'||
   		to_char(artv.article_version_id)||'.'||
   		to_char(artv.object_version_number)	ikey,
         substr(nvl(art.article_number, art.article_title),0,240) ukey,
   		artv.object_version_number+1,
         art.article_title,
         artv.article_version_number
		from
			okc_articles_all art,
   	   okc_article_versions artv
		where art.article_id = artv.article_id
		and   art.standard_yn = 'Y'
		and   artv.article_status in ('DRAFT','REJECTED')
		and   art.org_id = cp_org_id
		and   artv.article_version_id = cp_version_id
		and   greatest(nvl(trunc(end_date), trunc(sysdate))+1, trunc(sysdate)) <> trunc(sysdate); -- bug#3517002

Line: 32

      for update of
      artv.article_status,
      artv.object_version_number,
      artv.last_update_date,
      artv.last_updated_by
      nowait;

Line: 42

		select
		   arta.local_org_id,
		   art.article_id,
   		arta.global_article_version_id,
   		arta.adoption_status,
   		arta.adoption_type,
   		artv.global_yn,
   		'AD-'||
         to_char(arta.local_org_id)||'.'||
   		to_char(art.article_id)||'.'||
   		to_char(artv.article_version_id)||'.'||
   		to_char(arta.object_version_number) ikey,
   		substr(nvl(art.article_number,art.article_title),0,240) ukey,
   		arta.object_version_number+1,
         art.article_title,
         artv.article_version_number
		from
			okc_articles_all art,
   		okc_article_versions artv,
   		okc_article_adoptions arta
		where 	art.article_id = artv.article_id
		and	art.standard_yn = 'Y'
		and	artv.global_yn = 'Y'
		and	arta.adoption_type = 'AVAILABLE'
      and   (arta.adoption_status = 'REJECTED' or arta.adoption_status is null)
		and	arta.local_org_id = cp_org_id
		and	artv.article_version_id = cp_version_id
		and	arta.global_article_version_id = artv.article_version_id
		and	artv.article_status = 'APPROVED'
		and	sysdate <= nvl(artv.end_date,sysdate+1)
      and   not exists
      (
         select 1
         from okc_article_adoptions
         where global_article_version_id in
         (select article_version_id
         from okc_article_versions
         where article_id = art.article_id)
         and local_org_id = arta.local_org_id
         and adoption_type = 'LOCALIZED'
         union
         select 1
         from okc_article_adoptions
         where global_article_version_id in
         (select article_version_id
         from okc_article_versions
         where article_id = art.article_id)
         and local_org_id = arta.local_org_id
         and adoption_type = 'ADOPTED'
         and article_version_number > artv.article_version_number
      );

Line: 94

      for update of
      arta.adoption_type,
      arta.adoption_status,
      arta.object_version_number,
      arta.last_update_date,
      arta.last_updated_by
      nowait;

Line: 225

   select nvl(display_name, name)
   from wf_users
   where name = nvl(id, 'WFADMIN');

Line: 245

		select
         	art.org_id,
		art.article_id,
   		artv.article_version_id,
   		artv.article_status,
   		artv.adoption_type,
   		artv.global_yn,
   		'AI-'||
		   to_char(art.org_id)||'.'||
   		to_char(art.article_id)||'.'||
   		to_char(artv.article_version_id)||'.'||
   		to_char(artv.object_version_number) ikey,
   		substr(nvl(art.article_number,art.article_title),0,240) ukey,
   		artv.object_version_number
		from
			okc_articles_all art,
   			okc_article_versions artv,
   			okc_art_interface_all int
		where art.article_id = artv.article_id
		and   art.standard_yn = 'Y'
		and   artv.article_status in ('PENDING_APPROVAL','APPROVED')
		and   art.article_title = int.article_title
      		and   int.process_status in ('W', 'S') 	-- this process status tells
                                             		-- that the article has been imported
      		and   int.request_id = c_req_id         -- in order to find articles imported
                                             		-- in this process only
                and   int.batch_number = c_batch_num      -- added for performance
                and   int.org_id = c_org_id               -- added for performance
                and   int.article_status in ('PENDING_APPROVAL', 'APPROVED')
      		and   artv.article_version_number = int.article_version_number;

Line: 352

   select article_intent into art_intent
   from okc_articles_all
   where article_id = art_id;

Line: 373

   select article_intent into art_intent
   from okc_articles_all
   where article_id = art_id;

Line: 394

   select article_intent into art_intent
   from okc_articles_all a, okc_article_versions v
   where a.article_id = v.article_id
   and v.article_version_id = art_ver_id;

Line: 416

   select decode(p_art_intent, 'S', org_information2, 'B', org_information6, 'SYSADMIN')
   from hr_organization_information
   where organization_id = p_org_id
   and org_information_context = 'OKC_TERMS_LIBRARY_DETAILS';

Line: 447

	select 'Y' into result FROM wf_items_v
	where item_type = 'OKCARTAP'
	and item_key = p_ikey;

Line: 463

      update okc_article_versions
      set
      article_status = 'PENDING_APPROVAL',
      object_version_number = c_tab(write_ptr).ovn,
      last_update_date = sysdate,
      last_updated_by = fnd_global.user_id
      where current of cv;

Line: 545

      update okc_article_adoptions
      set
      adoption_type = 'ADOPTED',
      adoption_status = 'PENDING_APPROVAL',
      object_version_number = c_tab(write_ptr).ovn,
      last_update_date = sysdate,
      last_updated_by = fnd_global.user_id
      where current of ca;

Line: 710

select
   adoption_type,
   hr.organization_id,
   decode(p_art_intent, 'S', org_information3, 'B', org_information7,
   'SYSADMIN') notified
from
   okc_article_adoptions arta,
   hr_organization_information hri,
   hr_organization_units hr
where
   global_article_version_id = p_artv_id
and
   hri.organization_id = local_org_id
and
   hr.organization_id = local_org_id
and
   org_information_context = 'OKC_TERMS_LIBRARY_DETAILS';

Line: 811

SELECT user_name
FROM fnd_user
WHERE user_name = notified_usr
 AND nvl(end_date,   sysdate + 1) > sysdate;

Line: 885

         select nvl(display_name, name) into approver_name
         from wf_users where name = approver_role;

Line: 917

   select
      art.article_title,
      art.article_number,
      artv.article_version_number,
      artv.article_description,
      tm.meaning type_meaning,
      im.meaning intent_meaning,
      gm.meaning global_meaning,
      pm.meaning provision_meaning,
      artv.start_date,
      artv.end_date
   from
      okc_articles_all art,
      okc_article_versions artv,
      okc_lookups_v tm,
      okc_lookups_v im,
      okc_lookups_v gm,
      okc_lookups_v pm
   where art.standard_yn = 'Y'
   and art.org_id = cp_org_id
   and article_version_id = cp_art_ver_id
   and art.article_id = artv.article_id
   and tm.lookup_type ='OKC_SUBJECT'
   and im.lookup_type ='OKC_ARTICLE_INTENT'
   and gm.lookup_type ='OKC_YN'
   and pm.lookup_type ='OKC_YN'
   and tm.lookup_code = art.article_type
   and im.lookup_code = art.article_intent
   and gm.lookup_code = artv.global_yn
   and pm.lookup_code = artv.provision_yn;

Line: 949

   select
      art.article_title,
      art.article_number,
      artv.article_version_number,
      artv.article_description,
      tm.meaning type_meaning,
      im.meaning intent_meaning,
      gm.meaning global_meaning,
      pm.meaning provision_meaning,
      artv.start_date,
      artv.end_date
   from
      okc_articles_all art,
      okc_article_versions artv,
      okc_article_adoptions arta,
      okc_lookups_v tm,
      okc_lookups_v im,
      okc_lookups_v gm,
      okc_lookups_v pm
   where art.standard_yn = 'Y'
   and artv.global_yn = 'Y'
   and arta.local_org_id = cp_org_id
   and article_version_id = cp_art_ver_id
   and art.article_id = artv.article_id
   and artv.article_version_id = arta.global_article_version_id
   and tm.lookup_type ='OKC_SUBJECT'
   and im.lookup_type ='OKC_ARTICLE_INTENT'
   and gm.lookup_type ='OKC_YN'
   and pm.lookup_type ='OKC_YN'
   and tm.lookup_code = art.article_type
   and im.lookup_code = art.article_intent
   and gm.lookup_code = artv.global_yn
   and pm.lookup_code = artv.provision_yn;

Line: 1067

   select meaning from okc_lookups_v
   where lookup_type = 'OKC_ARTICLE_STATUS'
   and lookup_code = 'PENDING_APPROVAL';

Line: 1085

   select meaning from okc_lookups_v
   where lookup_type = 'OKC_ARTICLE_ADOPTION_TYPE'
   and lookup_code = 'ADOPTED';

Line: 1100

procedure selector(  itemtype in varchar2,
                     itemkey in varchar2,
                     actid in number,
                     command in varchar2,
                     resultout in out nocopy varchar2)
is
   l_current_org_id number;

Line: 1151

    WF_CORE.CONTEXT ('OKC_ARTWF_PVT', 'selector', itemtype,
                     itemkey, to_char(actid), command);

Line: 1156

procedure select_process(   itemtype in varchar2,
                           itemkey in varchar2,
                           actid in number,
                           command in varchar2,
                           resultout in out nocopy varchar2)
is
   adoption_type okc_article_adoptions.adoption_type%type;

Line: 1187

   WF_CORE.CONTEXT ('OKC_ARTWF_PVT', 'select_process', itemtype,
                     itemkey, to_char(actid), command);

Line: 1286

   select user_name
   from fnd_user_view
   where user_id = fnd_global.user_id;

Line: 1376

      select 'Y' into result
      from okc_articles_local_v
      where org_id = p_org_id
      and article_version_id = p_article_version_id;

Line: 1381

select 'Y' into result
from (
   select 'Y'
   from
      okc_articles_all art,
      okc_article_versions artv
   where art.standard_yn = 'Y'
      and art.article_id = artv.article_id
      and artv.article_version_id = p_article_version_id
      and art.org_id = p_org_id
   union all
   select 'Y'
   from
      okc_articles_all art,
      okc_article_versions artv,
      okc_article_adoptions arta
   where art.standard_yn = 'Y'
      and artv.global_yn = 'Y'
      and art.article_id = artv.article_id
      and artv.article_version_id = arta.global_article_version_id
      and artv.article_version_id = p_article_version_id
      and arta.local_org_id = p_org_id
      and arta.local_article_version_id is null
)
where rownum <= 1;

Line: 1409

         select 'Y' into result
         from okc_articles_local_v
         where org_id = p_org_id
         and article_version_id = p_article_version_id
         and article_status is null;

Line: 1415

select 'Y' into result
from (
   select
   decode(artv.article_status, 'APPROVED', decode(greatest(nvl(trunc(end_date),
   trunc(sysdate))+1, trunc(sysdate)), trunc(sysdate), 'EXPIRED', artv.article_status),
   artv.article_status) article_status
   from
      okc_articles_all art,
      okc_article_versions artv
   where art.standard_yn = 'Y'
      and art.article_id = artv.article_id
      and artv.article_version_id = p_article_version_id
      and art.org_id = p_org_id
   union all
   select
   decode(arta.adoption_type, 'AVAILABLE', decode( decode(greatest(nvl(trunc(end_date),
   trunc(sysdate))+1, trunc(sysdate)), trunc(sysdate), 'EXPIRED', artv.article_status),
   'APPROVED', arta.adoption_status, 'EXPIRED', 'EXPIRED', 'ON_HOLD', 'ON_HOLD', 'REJECTED',
   'REJECTED' ), 'ADOPTED', decode( decode(arta.adoption_status, 'PENDING_APPROVAL', 'PENDING_APPROVAL',
   decode(greatest(nvl(trunc(end_date), trunc(sysdate))+1, trunc(sysdate)), trunc(sysdate), 'EXPIRED',
   artv.article_status) ), 'APPROVED', arta.adoption_status, 'EXPIRED', 'EXPIRED', 'ON_HOLD', 'ON_HOLD',
   'PENDING_APPROVAL', 'PENDING_APPROVAL' ) ) article_status
   from
      okc_articles_all art,
      okc_article_versions artv,
      okc_article_adoptions arta
   where art.standard_yn = 'Y'
      and artv.global_yn = 'Y'
      and art.article_id = artv.article_id
      and artv.article_version_id = arta.global_article_version_id
      and artv.article_version_id = p_article_version_id
      and arta.local_org_id = p_org_id
      and arta.local_article_version_id is null
) a
where rownum <= 1
and article_status is null;

Line: 1453

         select 'Y' into result
         from okc_articles_local_v
         where org_id = p_org_id
         and article_version_id = p_article_version_id
         and article_status = upper(p_article_status);

Line: 1459

select 'Y' into result
from (
   select
   decode(artv.article_status, 'APPROVED', decode(greatest(nvl(trunc(end_date),
   trunc(sysdate))+1, trunc(sysdate)), trunc(sysdate), 'EXPIRED', artv.article_status),
   artv.article_status) article_status
   from
      okc_articles_all art,
      okc_article_versions artv
   where art.standard_yn = 'Y'
      and art.article_id = artv.article_id
      and artv.article_version_id = p_article_version_id
      and art.org_id = p_org_id
   union all
   select
   decode(arta.adoption_type, 'AVAILABLE', decode( decode(greatest(nvl(trunc(end_date),
   trunc(sysdate))+1, trunc(sysdate)), trunc(sysdate), 'EXPIRED', artv.article_status),
   'APPROVED', arta.adoption_status, 'EXPIRED', 'EXPIRED', 'ON_HOLD', 'ON_HOLD', 'REJECTED',
   'REJECTED' ), 'ADOPTED', decode( decode(arta.adoption_status, 'PENDING_APPROVAL',
   'PENDING_APPROVAL', decode(greatest(nvl(trunc(end_date), trunc(sysdate))+1, trunc(sysdate)),
   trunc(sysdate), 'EXPIRED', artv.article_status) ), 'APPROVED', arta.adoption_status, 'EXPIRED',
   'EXPIRED', 'ON_HOLD', 'ON_HOLD', 'PENDING_APPROVAL', 'PENDING_APPROVAL' ) ) article_status
   from
      okc_articles_all art,
      okc_article_versions artv,
      okc_article_adoptions arta
   where art.standard_yn = 'Y'
      and artv.global_yn = 'Y'
      and art.article_id = artv.article_id
      and artv.article_version_id = arta.global_article_version_id
      and artv.article_version_id = p_article_version_id
      and arta.local_org_id = p_org_id
      and arta.local_article_version_id is null
) a
where rownum <= 1
and article_status = upper(p_article_status);

Line: 1500

      select 'Y' into result
      from okc_articles_global_v
      where org_id = p_org_id
      and article_version_id = p_article_version_id;

Line: 1505

select 'Y' into result
from (
   select 'Y'
   from
      okc_articles_all art,
      okc_article_versions artv
   where
      art.standard_yn = 'Y'
      and art.article_id = artv.article_id
      and org_id = p_org_id
      and article_version_id = p_article_version_id
) a
where rownum <= 1;

Line: 1521

         select 'Y' into result
         from okc_articles_global_v
         where org_id = p_org_id
         and article_version_id = p_article_version_id
         and article_status is null;

Line: 1527

select 'Y' into result
from (
   select
   decode(artv.article_status, 'APPROVED', decode(greatest(nvl(trunc(end_date),
   trunc(sysdate))+1, trunc(sysdate)), trunc(sysdate), 'EXPIRED', artv.article_status),
   artv.article_status) article_status
   from
      okc_articles_all art,
      okc_article_versions artv
   where
      art.standard_yn = 'Y'
      and art.article_id = artv.article_id
      and org_id = p_org_id
      and article_version_id = p_article_version_id
) a
where rownum <= 1
and article_status is null;

Line: 1546

         select 'Y' into result
         from okc_articles_global_v
         where org_id = p_org_id
         and article_version_id = p_article_version_id
         and article_status = upper(p_article_status);

Line: 1552

select 'Y' into result
from (
   select
   decode(artv.article_status, 'APPROVED', decode(greatest(nvl(trunc(end_date),
   trunc(sysdate))+1, trunc(sysdate)), trunc(sysdate), 'EXPIRED', artv.article_status),
   artv.article_status) article_status
   from
      okc_articles_all art,
      okc_article_versions artv
   where
      art.standard_yn = 'Y'
      and art.article_id = artv.article_id
      and org_id = p_org_id
      and article_version_id = p_article_version_id
) a
where article_status = upper(p_article_status);

Line: 1621

   select name into org_name
   from hr_organization_units
   where organization_id = p_org_id;

Line: 1625

select
   otl.name into org_name
from
   hr_all_organization_units o,
   hr_all_organization_units_tl otl
where
   o.organization_id = otl.organization_id
   and otl.language = userenv('LANG')
   and o.organization_id = p_org_id;

Line: 1674

      select 'Y' into result
      from wf_users
      where name = approverS;

Line: 1686

      select 'Y' into result
      from wf_users
      where name = approverB;

Line: 1757

         select 'Y' into result
         from wf_users
         where name = approverS;

Line: 1774

         select 'Y' into result
         from wf_users
         where name = approverB;

Line: 1825

select display_name
from wf_users
where name = new_approver;

Line: 1860

select context into str
from wf_notifications
where notification_id = nid;

Line: 1875

select context into str
from wf_notifications
where notification_id = nid;

Line: 1977

select
   otl.name into org_name
from
   hr_all_organization_units o,
   hr_all_organization_units_tl otl
where
   o.organization_id = otl.organization_id
   and otl.language = userenv('LANG')
   and o.organization_id = (
      select org_id
      from okc_terms_templates_all
      where template_id = p_template_id
   );

Line: 1999

   select meaning into intent_meaning
   from okc_lookups_v
   where lookup_type = 'OKC_TERMS_INTENT'
   and lookup_code = (
      select intent
      from okc_terms_templates_all
      where template_id = p_template_id
   );

Line: 2019

   select name
   from wf_notification_attributes
   where notification_id = nid;

Line: 2051

   select article_text into text
   from okc_article_versions
   where article_version_id = (
      select global_article_version_id
      from okc_article_adoptions
      where local_org_id = p_org_id
      and local_article_version_id = p_article_version_id
   );

Line: 2072

   select nvl(translated_yn, 'N') into yn
   from okc_article_versions
   where article_version_id = p_article_version_id;

Line: 2086

   select 'Y' into yn
   from okc_article_adoptions
   where local_org_id = p_org_id
   and local_article_version_id = p_article_version_id;

Line: 2101

   select global_article_version_id into id
   from okc_article_adoptions
   where local_org_id = p_org_id
   and local_article_version_id = p_article_version_id;