APPS.OKS_WF_K_APPROVE SQL Statements

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

Line: 195

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

l_api_name     CONSTANT VARCHAR2(30) := 'select_next';

Line: 220

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: 255

        okc_debug.log('OKSWCAPB: Select_Next() --  Start of Select_Next()', 2);

Line: 370

          okc_debug.log('OKSWCAPB: Select_Next() --  End of Select_Next()', 2);

Line: 388

end select_next;

Line: 412

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: 479

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: 520

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

Line: 526

end Selector;

Line: 550

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: 566

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: 579

select u.name, u.display_name
from   wf_users u
where UPPER(u.EMAIL_ADDRESS)=UPPER(substr(p_context,7));

Line: 584

select  u.name, u.display_name
from    wf_users u
where u.NAME=p_context;

Line: 665

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: 678

select u.name, u.display_name
from   wf_users u
where UPPER(u.EMAIL_ADDRESS)=UPPER(substr(p_context,7));

Line: 683

select  u.name, u.display_name
from    wf_users u
where u.NAME=p_context;

Line: 689

SELECT action
FROM wf_routing_rules
WHERE message_name ='SIGN_CONTRACT'
AND role =p_name
AND message_type='OKCAUKAP'
AND ((SYSDATE >= begin_date) AND (SYSDATE <= Nvl(end_date,SYSDATE)));

Line: 698

SELECT action
FROM wf_routing_rules
WHERE message_name ='APPROVE_CONTRACT'
AND role =p_name
AND message_type='OKCAUKAP'
AND ((SYSDATE >= begin_date) AND (SYSDATE <= Nvl(end_date,SYSDATE)));

Line: 789

  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: 801

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: 923

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

Line: 992

  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: 1030

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

Line: 1076

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

Line: 1089

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

Line: 1132

            okc_debug.log('OKSWCAPB: Initialize() -- Before call update_invalid_approver()', 2);

Line: 1135

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

Line: 1141

            okc_debug.log('OKSWCAPB: Initialize() -- After call update_invalid_approver()', 2);

Line: 1186

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

l_api_name           CONSTANT VARCHAR2(50) := 'Select_Approver';

Line: 1201

            okc_debug.log('OKSWCAPB: Select_Approver() --  Start of Select_Approver()', 2);

Line: 1237

                   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: 1310

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

Line: 1387

            okc_debug.log('OKSWCAPB: Select_Approver() --  End of Select_Approver()', 2);

Line: 1392

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

Line: 1398

end Select_Approver;

Line: 1412

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

begin


  ame_api2.updateApprovalStatus2(
         applicationIdIn   => G_APPLICATION_ID,
         transactionTypeIn => G_TRANSACTION_TYPE,
         transactionIdIn   => wf_engine.GetItemAttrText(itemtype,itemkey,'CONTRACT_ID'),
         approvalStatusIn  => ame_util.approvedStatus,
         approverNameIn    => wf_engine.GetItemAttrText(itemtype,itemkey,'ACTUAL_PERFORMER'),
         forwardeeIn       => ame_util.emptyApproverRecord2
         );

Line: 1441

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: 1461

	  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: 1503

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

Line: 1509

end Select_Informed;

Line: 1511

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: 1524

procedure Select_Informed_AR(	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: 1537

procedure Select_Informed_S(	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: 1550

procedure Select_Informed_SR(	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: 1820

  	select scs_code
  	from okc_k_headers_all_b
  	where ID = p_chr_id;

Line: 2126

SELECT userenv('sessionid')
FROM dual;

Line: 2154

                  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: 2228

   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: 2267

    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: 2326

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: 2374

      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: 2415

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

Line: 2421

END update_invalid_approver;