APPS.OKC_WF_K_APPROVE SQL Statements

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

Line: 68

procedure select_next(itemtype	in varchar2 default NULL,
				itemkey  	in varchar2 default NULL,
				p_role_type 	in varchar2,
				p_current  		in varchar2 default NULL,
				x_role	 out nocopy varchar2,
				x_name	 out nocopy varchar2
) is

--
-- 	Next Approver Cursor - here comes from user profile option value
--

/*

Due to performance reasons we decided to refuse from

(select 1 						num,
	FND_PROFILE.VALUE('OKC_K_APPROVER')	role,
	DISPLAY_NAME 				name
from wf_roles
where name=FND_PROFILE.VALUE('OKC_K_APPROVER'))

and restrict select to fnd_user data only

might be customisation problem

*/

cursor Next_Approver_csr is
select role, name
from  -- here should be your view of structure (num,role,name)
------------------------------------------------------
(
select 1 num,
  FND_PROFILE.VALUE('OKC_K_APPROVER') role,
  FND_PROFILE.VALUE('OKC_K_APPROVER') name
from dual
where not exists
( select '!'
  from FND_USER USR, PER_PEOPLE_F PER
  where USR.USER_NAME=FND_PROFILE.VALUE('OKC_K_APPROVER')
  and USR.EMPLOYEE_ID = PER.PERSON_ID
)
union all
select 1        num,
 FND_PROFILE.VALUE('OKC_K_APPROVER')   role,
 NVL(PER.FULL_NAME,FND_PROFILE.VALUE('OKC_K_APPROVER')) name
from FND_USER USR, PER_PEOPLE_F PER
where USR.USER_NAME=FND_PROFILE.VALUE('OKC_K_APPROVER')
and USR.EMPLOYEE_ID = PER.PERSON_ID
)
------------------------------------------------------
where p_current is NULL
order by num;

Line: 156

select role, name from -- here should be your view
------------------------------------------------------
(select 1 num, l_approver role, '' name from dual
   where l_approver is not NULL
 union all
 select 2 num, l_initiator role, '' name from dual
 where not exists
   (select 1 from wf_user_roles
    where user_name=l_initiator
    and USER_ORIG_SYSTEM IN ('PER','FND_USR')
    and ROLE_NAME=l_approver
   )
)
------------------------------------------------------
where (p_current is NULL
	or num > (select num from -- same view
------------------------------------------------------
(select 1 num, l_approver role, '' name from dual
   where l_approver is not NULL
 union all
 select 2 num, l_initiator role, '' name from dual
 where not exists
   (select 1 from wf_user_roles
    where user_name=l_initiator
    and USER_ORIG_SYSTEM IN ('PER','FND_USR')
    and ROLE_NAME=l_approver
   )
)
------------------------------------------------------
		    where role = p_current)
) order by num;

Line: 194

select role, name from -- here should be your view
------------------------------------------------------
(select rownum num, user_name role, '' name
 from
  (select distinct user_name from wf_user_roles
   where role_name in (l_initiator,l_approver)))
------------------------------------------------------
where (p_current is NULL
	or num > (select num from -- same view
------------------------------------------------------
(select rownum num, user_name role, '' name
 from
  (select distinct user_name from wf_user_roles
   where role_name in (l_initiator,l_approver)))
------------------------------------------------------
		    where role = p_current)
) order by num;

Line: 253

end select_next;

Line: 277

select 'T'
from FND_USER
where
	FND_USER.USER_NAME=p_account
	and FND_USER.EMPLOYEE_ID is NULL
	and trunc(sysdate) between trunc(start_date) and nvl(end_date,sysdate)
union all
select 'T'
from FND_USER USR, PER_PEOPLE_F PER
where USR.USER_NAME=p_account
and trunc(sysdate) between trunc(USR.start_date) and nvl(USR.end_date,sysdate)
and USR.EMPLOYEE_ID = PER.PERSON_ID
and trunc(sysdate) between trunc(per.effective_start_date) and nvl(per.effective_end_date,sysdate)
;

Line: 342

procedure Selector  ( 	item_type	in varchar2,
			item_key  	in varchar2,
			activity_id	in number,
			command		in varchar2,
			resultout out nocopy varchar2	) is
-- local declarations
begin
	resultout := ''; -- return value for other possible modes

Line: 383

		'SELECTOR',
		item_type,
		item_key,
		to_char(activity_id),
		command);

Line: 389

end Selector;

Line: 410

select  --+ORDERED
	u.name, u.display_name
from wf_user_roles r, wf_users u
where r.role_name=l_name
and r.USER_ORIG_SYSTEM=u.ORIG_SYSTEM
and r.USER_ORIG_SYSTEM_ID=u.ORIG_SYSTEM_ID
and
(
  (l_context not like 'email:%' and u.NAME=l_context)
 or
  (l_context like 'email:%' and u.EMAIL_ADDRESS=substr(l_context,7))
);

Line: 426

select  --+ORDERED
	u.name, u.display_name
from
   wf_users u
where
(
  (l_context not like 'email:%' and u.NAME=l_context)
 or
  (l_context like 'email:%' and u.EMAIL_ADDRESS=substr(l_context,7))
);

Line: 494

select  --+ORDERED
	u.name, u.display_name
from
   wf_users u
where
(
  (l_context not like 'email:%' and u.NAME=l_context)
 or
  (l_context like 'email:%' and u.EMAIL_ADDRESS=substr(l_context,7))
);

Line: 549

  select 'T'
  from OKC_K_REL_OBJS_V R, ASO_Quote_Headers_ALL Q
  where R.chr_id = wf_engine.GetItemAttrNumber(itemtype,itemkey,'CONTRACT_ID')
--  and R.RTY_CODE = 'CONTRACTNEGOTIATESQUOTE'
  and R.RTY_CODE like 'CONTRACT%IS%TERM%FOR%QUOTE'
  and R.CLE_ID IS NULL
    and Q.QUOTE_HEADER_ID = R.OBJECT1_ID1
    and Q.QUOTE_SOURCE_CODE like 'IStore%' ;

Line: 561

select 'T'
  from okc_k_rel_objs
 where chr_id = wf_engine.GetItemAttrNumber(itemtype,itemkey,'CONTRACT_ID')
   and JTOT_OBJECT1_CODE = G_OBJECT_CODE
   ---and RTY_CODE          = G_TERMSFORQUOTE ;

Line: 683

   select 'T'
   from   okc_k_headers_v
   where  id = wf_engine.GetItemAttrNumber(itemtype,itemkey,'CONTRACT_ID')
   and    template_yn = 'Y';

Line: 752

  select name,display_name
  from wf_roles
  where orig_system = 'FND_USR'
  and orig_system_id=P_USER_ID
union all
select
       USR.USER_NAME name,
       PER.FULL_NAME display_name
from
       PER_PEOPLE_F PER,
       FND_USER USR
where  trunc(SYSDATE) between PER.EFFECTIVE_START_DATE
                          and PER.EFFECTIVE_END_DATE
and    PER.PERSON_ID       = USR.EMPLOYEE_ID
and USR.USER_ID = P_USER_ID
and not exists (select '1'
  from wf_roles
  where orig_system = 'FND_USR'
  and orig_system_id=P_USER_ID)
-- replaced to boost perf
*/
  select user_name name,user_name display_name
  from fnd_user
  where user_id=P_USER_ID
  and employee_id is null
union all
  select
       USR.USER_NAME name, PER.FULL_NAME display_name
  from
       PER_PEOPLE_F PER,
       FND_USER USR
  where  trunc(SYSDATE)
      between PER.EFFECTIVE_START_DATE and PER.EFFECTIVE_END_DATE
    and    PER.PERSON_ID       = USR.EMPLOYEE_ID
    and USR.USER_ID = P_USER_ID
;

Line: 790

  select SHORT_DESCRIPTION
  from okc_k_headers_tl
  where id = P_CONTRACT_ID
	and language=userenv('LANG');

Line: 823

	  select_next(itemtype => itemtype,
			itemkey => itemkey,
			p_role_type 	=> 'ADMINISTRATOR',
			x_role		=> L_CONTRACT_ADMIN_USERNAME,
			x_name		=> V_DUMMY);

Line: 836

	  select_next(itemtype => itemtype,
			itemkey => itemkey,
			p_role_type 	=> 'SIGNATORY',
			x_role		=> L_SIGNATORY_USERNAME,
			x_name		=> L_SIGNATORY_DISPLAY_NAME);

Line: 866

 update_invalid_approver(itemtype => itemtype,
                         itemkey  => itemkey,
                         actid => actid,
                         funcmode => funcmode,
                         resultout => resultout);

Line: 914

procedure Select_Approver(	itemtype	in varchar2,
				itemkey  	in varchar2,
				actid		in number,
				funcmode	in varchar2,
				resultout out nocopy varchar2	) is
L_INITIATOR_DISPLAY_NAME varchar2(240);

Line: 939

	    select_next(itemtype => itemtype,
			itemkey => itemkey,
			p_role_type 	=> 'APPROVER',
			p_current  		=> NULL,
			x_role		=> L_NEXT_PERFORMER_USERNAME,
			x_name		=> L_N_PERFORMER_DISPLAY_NAME);

Line: 988

	    select_next(itemtype => itemtype,
			itemkey => itemkey,
			p_role_type 	=> 'APPROVER',
			p_current	=> L_NEXT_PERFORMER_USERNAME,
			--x_role	=> L_NEXT_PERFORMER_USERNAME,
			x_role		=> L_NEXT_PERFORMER_USERNAME_OUT,
			x_name		=> L_N_PERFORMER_DISPLAY_NAME);

Line: 1054

		'SELECT_APPROVER',
		itemtype,
		itemkey,
		to_char(actid),
		funcmode);

Line: 1060

end Select_Approver;

Line: 1071

procedure Select_Informed(	itemtype	in varchar2,
				itemkey  	in varchar2,
				actid		in number,
				funcmode	in varchar2,
				resultout out nocopy varchar2) is
L_NEXT_INFORMED_USERNAME varchar2(100);

Line: 1091

	  select_next(itemtype     => itemtype,
                      itemkey      => itemkey,
                      p_role_type  => 'INFORMED',
                      p_current    => L_NEXT_INFORMED_USERNAME,
                      x_role	   => L_NEXT_INFORMED_USERNAME_OUT,
		      x_name	   => V_DUMMY);

Line: 1133

		'SELECT_INFORMED',
		itemtype,
		itemkey,
		to_char(actid),
		funcmode);

Line: 1139

end Select_Informed;

Line: 1141

procedure Select_Informed_A(	itemtype	in varchar2,
				itemkey  	in varchar2,
				actid		in number,
				funcmode	in varchar2,
				resultout out nocopy varchar2) is
begin
  Select_Informed(	itemtype,
				itemkey ,
				actid	,
				funcmode,
				resultout);

Line: 1154

procedure Select_Informed_AR(	itemtype	in varchar2,
				itemkey  	in varchar2,
				actid		in number,
				funcmode	in varchar2,
				resultout out nocopy varchar2) is

 l_contract_id   NUMBER;

Line: 1168

  	select okch.scs_code, oksh.wf_item_key
  	from okc_k_headers_all_b okch, oks_k_headers_b oksh
  	where okch.ID = p_chr_id
     and   okch.id = oksh.chr_id;

Line: 1174

  Select_Informed(	itemtype,
				itemkey ,
				actid	,
				funcmode,
				resultout);

Line: 1215

		'SELECT_INFORMED_AR',
		itemtype,
		itemkey,
		to_char(actid),
		funcmode);

Line: 1224

procedure Select_Informed_S(	itemtype	in varchar2,
				itemkey  	in varchar2,
				actid		in number,
				funcmode	in varchar2,
				resultout out nocopy varchar2) is

 l_contract_id   NUMBER;

Line: 1237

  	select scs_code
  	from okc_k_headers_all_b
  	where ID = p_chr_id;

Line: 1243

  Select_Informed(	itemtype,
				itemkey ,
				actid	,
				funcmode,
				resultout);

Line: 1285

		'SELECT_INFORMED_S',
		itemtype,
		itemkey,
		to_char(actid),
		funcmode);

Line: 1293

procedure Select_Informed_SR(	itemtype	in varchar2,
				itemkey  	in varchar2,
				actid		in number,
				funcmode	in varchar2,
				resultout out nocopy varchar2) is

 l_contract_id   NUMBER;

Line: 1307

  	select okch.scs_code, oksh.wf_item_key
  	from okc_k_headers_all_b okch, oks_k_headers_b oksh
  	where okch.ID = p_chr_id
     and   okch.id = oksh.chr_id;

Line: 1313

  Select_Informed(	itemtype,
				itemkey ,
				actid	,
				funcmode,
				resultout);

Line: 1354

		'SELECT_INFORMED_SR',
		itemtype,
		itemkey,
		to_char(actid),
		funcmode);

Line: 1830

SELECT userenv('sessionid')
FROM dual;

Line: 1858

                  OKC_OC_INT_PUB.update_quote_from_k(
                   p_api_version    => l_api_version
                  ,p_commit         => OKC_API.G_TRUE
                  ,p_quote_id       => NULL
                  ,p_contract_id    => l_contract_id
                  ,p_trace_mode     => NULL
                  ,x_return_status  => x_return_status
                  ,x_msg_count      => x_msg_count
                  ,x_msg_data       => x_msg_data
                  );

Line: 1932

   SELECT id
   FROM okc_k_processes
   WHERE chr_id = p_contract_id
/*Bug 3255018 AND pdf_id = (SELECT id */
   AND pdf_id in (SELECT id
                 FROM OKC_PROCESS_DEFS_V
                 WHERE usage = 'APPROVE'
                 AND PDF_TYPE = 'WPS'
 		 AND WF_NAME = 'OKCAUKAP');

Line: 1971

    Okc_Contract_Pub.update_contract_process(
      p_api_version  => l_api_version,
      p_init_msg_list => l_init_msg_list,
      x_return_status => l_return_status,
      x_msg_count => l_msg_count,
      x_msg_data => l_msg_data,
      p_cpsv_rec => l_cpsv_rec,
      x_cpsv_rec => x_cpsv_rec);

Line: 2030

PROCEDURE update_invalid_approver(itemtype	IN VARCHAR2,
				itemkey  	IN VARCHAR2,
				actid		IN NUMBER,
				funcmode	IN VARCHAR2,
				resultout OUT NOCOPY VARCHAR2	)
IS
   CURSOR csr_process_id(p_contract_id IN NUMBER)
   IS
   SELECT id, in_process_yn
   FROM okc_k_processes
   WHERE chr_id = p_contract_id
/*Bug 3255018 AND pdf_id = (SELECT id */
   AND pdf_id in (SELECT id
                 FROM OKC_PROCESS_DEFS_V
                 WHERE usage = 'APPROVE'
                 AND PDF_TYPE = 'WPS'
 		 AND WF_NAME = 'OKCAUKAP');

Line: 2078

      Okc_Contract_Pub.update_contract_process(
        p_api_version  => l_api_version,
        p_init_msg_list => l_init_msg_list,
        x_return_status => l_return_status,
        x_msg_count => l_msg_count,
        x_msg_data => l_msg_data,
        p_cpsv_rec => l_cpsv_rec,
        x_cpsv_rec => x_cpsv_rec);

Line: 2119

                'update_invalid_approver',
                itemtype,
                itemkey,
                TO_CHAR(actid),
                funcmode);

Line: 2125

END update_invalid_approver;