/*=======================================================================+
 |  Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA|
 |                            All rights reserved.                       |
 +=======================================================================+
 | FILENAME
 |   wfdemob.sql
 |
 | DESCRIPTION
 |   PL/SQL body for packages:  WF_DEMO, WF_REQDEMO
 |
 | NOTES
 | MODIFIED     10/30/97 	Added create_req, submit_req, confirm_req,
 |                              req_timeout, and print_error
 |				for web based demo of Requisition Approval
 |                              Workflow.
 | MODIFIED     10/30/97        Modified selector, CanOwnerApprove, 
 |                              SelectApprover, and VerifyAuthority
 |                              procedures to use workflow coding standards.
 | MODIFIED     11/03/97        Modified call to monitor to use admin mode.
 | MODIFIED     11/10/97        Added Item Type field to Create_Req input
 |                              form and supporting procedures to accept
 |                              and pass Item Type parameter.
 |                              Added procedures Forward_Req, Approve_Req,
 |                              and Reject_Req to replace WF_STANDARD.NOOP
 |                              function calls in activities RECORDFORWARD,
 |                              APPROVEREQUISITION, and REJECTREQUISITION,
 |                              respectively.
 |                              Added display of message to view notifications
 |                              for requisition approver and requestor.
 | MODIFIED     11/10/97        Removed procedure CanOwnerApprove.
 | MODIFIED     11/12/97        Replaced call to wf_engine.ProcessTimeout
 |                              with call to wf_engine.Background for
 |                              the current itemtype and timeouts only.
 | MODIFIED     01/15/98        Replaced all results 'COMPLETE' with
 |                              result 'COMPLETE:' so that the engine won't
 |                              interpret COMPLETE as the result instead of
 |                              a result of NULL.
 | MODIFIED     11/18/98	Removed calls to SetItemAttribute for the
 |                              "display" item attributes:
 |				FORWARD_FROM_DISPLAY_NAME
 |				FORWARD_TO_DISPLAY_NAME
 |				REQUESTOR_DISPLAY_NAME
 |                              Added a call to set the Item Attribute
 |				REQ_DOCUMENT to 
 |				'PLSQL:WF_REQDEMO.CREATE_REQ_DOCUMENT/
 |                              <ItemKey>'
 |                              Added procedure Create_Req_Document to
 |				generate a text/html and a text/plain
 |				version of the Requisition.
 | MODIFIED     11/18/98	Added a call to set the Item Attribute
 |				REM_DOCUMENT to 
 |				'PLSQL:WF_REQDEMO.REMINDER_REQ_DOCUMENT/
 |                              <ItemKey>'
 |                              Added procedure Reminder_Req_Document to
 |				generate a text/html and a text/plain
 |				version of the Requisition Reminder.
 | MODIFIED     11/18/98        Replaced user names with Vision database
 |				compliant names according to:
 |                                 JANDERSON => BLEWIS
 |                                 MBEECH    => CDOUGLAS
 |                                 DMCKEE    => KWALKER
 |                                 JRUSH     => SPIERSON
 |                                 Removed user name JSMITH from demo.
 | MODIFIED     03/09/99	Replaced section titles with page title
 |                              in call to wfa_sec.header.
 | MODIFIED     03/12/99        Added package WF_DEMO.
 |                              Added procedures Home, HomeHeader, and 
 |				HomeMenu to generate a home menu
 |                              of demonstrations to choose from.
 |                              Rearranged Create_Req page.
 | MODIFIED     01/2002         JWSMITH BUG 2001012
 |                              Increased username, l_forward_to_username
 |                              l_forward_from_username, l_requestor,
 |                              l_process_owner, l_forward_from to    
 |                              varchar2(320)
 | MODIFIED     03/2002         VSHANMUG BUG 2209427
 |                              Replaced hardcoded strings in procedures
 |                              Create_Req_Demo and Reminder_Req_Demo with
 |                              wf_core.translate('WFDEMO_REQUISITION')
 |                              wf_core.translate('WFDEMO_APPROVAL_REQ')
 |                              wf_core.translate('WFDEMO_FORWARD_FROM')
 |                              wf_core.translate('WFDEMO_FORWARDED_FROM')
 |                              wf_core.translate('WFDEMO_NOTE')
 | MODIFIED     02/21/03        hgandiko Bug 2785163
 |				Replaced the wf_monitor.geturl with 
 |				wf_monitor.getdiagram and specified a value
 |				for the agent when setting the 
 |				item Attribute text for MONITOR_URL.
 |
 | Modified     09/23/03        fgraham
 |              Added new user 'TEST.USER.OVER.30.CHARS.ORACLE.COM' for
 |              purpose of creating new WinRunner RTs for testing Bugs
 |              3053601 & 3063982
 *=======================================================================*/
--
whenever sqlerror exit failure rollback;
set arraysize 1
set escape '*'
set escape '\'
set scan off
--
CREATE OR REPLACE PACKAGE BODY wf_demo as
/* $Header: wfdemob.sql 26.10 2004/05/12 09:53:50 parsharm ship $ */
 base_url	VARCHAR2(2000) := wfa_html.base_url;

procedure Home is

  username   varchar2(320);   -- Username to query

begin
  -- Check session and current user
  wfa_sec.GetSession(username);
  username := upper(username);

  htp.htmlOpen;
  htp.headOpen;
  htp.title(wf_core.translate('WFDEMO_HOMETITLE'));
  htp.headClose;

  -- Open frameset
  htp.p('<FRAMESET ROWS="10%,90%" frameborder=no border=0>');

  htp.p('<FRAME NAME="header_bar" MARGINHEIGHT=10 MARGINWIDTH=10 ' ||
	'scrolling=no frameborder=no framespacing=noresize '||
	'src="'||wfa_html.base_url||
		'/wf_demo.homeheader">');

  htp.p('<FRAMESET COLS="20%,80%" frameborder=no border=0>');

  htp.p('<FRAME NAME="toc" scrolling=no frameborder=no framespacing=noresize '||
	'src="'||wfa_html.base_url||
		'/wf_demo.homemenu?origin=FRAME">');
  htp.p('<FRAME NAME="main" frameborder=no framespacing=0 '||
	'src="'||wfa_html.base_url||
		'/wfa_html.WorkList?user='||username||
                '&resetcookie=1&clearbanner=TRUE">');

  htp.p('</FRAMESET>');
  htp.p('</FRAMESET>');
  htp.htmlClose;

exception
	when others then
	--
	wf_core.context('WF_DEMO','Home');
	wf_reqdemo.Print_Error;
end Home;

procedure HomeHeader is

begin
  -- Set page title
  htp.htmlOpen;
  htp.headOpen;
  htp.title(wf_core.translate('WFDEMO_HOMETITLE'));
  htp.headClose;
  -- Page Header
  wfa_sec.Header(page_title=>wf_core.translate('WFDEMO_HOMETITLE'));
  wfa_html.create_help_function('wf/links/dem.htm?DEMOWF');
  wfa_sec.Footer;
  htp.htmlClose;

end HomeHeader;

-- HomeMenu
-- Prints the menu for the home page.
-- May also be called directly to print a regular page.
procedure HomeMenu(
  origin in varchar2 default 'NORMAL') 
is
  anchor_tag varchar2(60);
  username   varchar2(320);   -- Username to query

begin
--

  -- Check session and current user
  wfa_sec.GetSession(username);
  username := upper(username);

  if origin = 'FRAME' then
    anchor_tag := '<A TARGET="_top" HREF=';
    wfa_sec.Header(background_only=>TRUE);
  else
    anchor_tag := '<A HREF=';
    wfa_sec.Header(background_only=>FALSE,
		page_title=>wf_core.translate('WFDEMO_HOMETITLE'));
  end if;

  if origin = 'FRAME' then
    htp.fontOpen(cface=>'ARIAL,HELVETICA',csize=>'-1');
  end if;

  htp.tableopen;

	htp.tablerowopen;
	htp.tabledata('<img src="'||wfa_html.image_loc||'FNDWATHS.gif" height=18 '||
		'width=18 alt="'||wf_core.translate('WFDEMO_SECTION_TITLE')||
		'">'||anchor_tag||wfa_html.base_url ||
		'/wf_reqdemo.create_req'||'>'||
		wf_core.translate('WFDEMO_SECTION_TITLE')||'</A>');
	htp.tablerowclose;

	--htp.tablerowopen;
	--htp.tabledata('<img src="'||wfa_html.image_loc||'FNDWATHS.gif" height=18 '||
	--	'width=18 alt="'||wf_core.translate('WFSRV_SECTION_TITLE')||
	--	'">'||anchor_tag||wfa_html.base_url||
	--	'/wf_surveydemo.create_survey'||'>'||
	--	wf_core.translate('WFSRV_SECTION_TITLE')||'</A>');
	--htp.tablerowclose;

	--htp.tablerowopen;
	--htp.tabledata('<img src="'||wfa_html.image_loc||'FNDWATHS.gif" height=18 '||
	--	'width=18 alt="'||wf_core.translate('WFDEMO_DM')||
	--	'">'||anchor_tag||wfa_html.base_url ||
	--	'/wf_initiate.Process?ItemType=WFDM'||'>'||
	--	wf_core.translate('WFDEMO_DM')||'</A>');
	--htp.tablerowclose;

     --	htp.tablerowopen;
--	htp.tabledata('<img src="'||wfa_html.image_loc||'FNDWATHS.gif" height=18 '||
		--'width=18 alt="'||wf_core.translate('WFDEMO_ALERT')||
		--'">'||anchor_tag||wfa_html.base_url ||
		--'/wf_initiate.Process?ItemType=WFALERT'||'>'||
		--wf_core.translate('WFDEMO_ALERT')||'</A>');
	--htp.tablerowclose;

	htp.tablerowopen;
        htp.tabledata('<img src="'||wfa_html.image_loc||'FNDWATHS.gif" height=18 '||
               'width=18 alt="'||wf_core.translate('WFEVENTDEMO_BUYER')||
               '">'||anchor_tag||wfa_html.base_url||
                '/wf_eventdemo.create_order'||'>'||
                wf_core.translate('WFEVENTDEMO_BUYER')||'</A>');
        htp.tablerowclose;

	--htp.tablerowopen;
        --htp.tabledata('<img src="'||wfa_html.image_loc||'FNDWATHS.gif" height=18
 --'||
  --              'width=18 alt="'||wf_core.translate('WFEVENTDEMO_TRACK_ORDER')||
   --             '">'||anchor_tag||wfa_html.base_url||
    --            '/wf_eventdemo.track_order'||'>'||
     --           wf_core.translate('WFEVENTDEMO_TRACK_ORDER')||'</A>');
      --  htp.tablerowclose;

	
  htp.tableclose;
  wfa_sec.Footer;
  htp.htmlClose;

exception
  when others then
  wf_core.context('wf_demo','HomeMenu');
  wf_reqdemo.Print_Error;
end HomeMenu;

end wf_demo;
/
show errors package body wf_demo 


CREATE OR REPLACE PACKAGE BODY wf_reqdemo as
/* $Header: wfdemob.sql 26.10 2004/05/12 09:53:50 parsharm ship $ */
 base_url	VARCHAR2(2000) := wfa_html.base_url;

--
-- Procedure 
--	StartProcess
--
-- Description
--      Initiate workflow for a requisition
-- IN
--   RequisitionNumber	- Requisition Number from calling application
--   RequisitionDesc	- Requisition Descriptor (user displayed key) from
--			  calling application
--   RequisitionAmount  - Requisition Amount from calling application
--   RequestorUsername  - Requisition Requestor Username from callling application
--   ProcessOwner	- Requisition Process Owner Username from calling application
--   Workflowprocess    - Workflow process to run.
--	
procedure StartProcess(	RequisitionNumber	in varchar2,
			RequisitionDesc		in varchar2,
			RequisitionAmount	in number,
			RequestorUsername	in varchar2,
			ProcessOwner		in varchar2,
			Workflowprocess		in varchar2 default null,
			item_type		in varchar2 default null ) is
--
--
ItemType	varchar2(30) := nvl(item_type,'WFDEMO');
ItemKey		varchar2(30) := RequisitionNumber;
ItemUserKey	varchar2(80) := RequisitionDesc;
--
begin
	--
	-- Start Process :
	--  If workflowprocess is passed, it will be run.
	--  If workflowprocess is NOT passed, the selector function 
	--  defined in the item type will determine which process to run.
	--
	wf_engine.CreateProcess( ItemType => ItemType,
				 ItemKey  => ItemKey,
				 process  => WorkflowProcess );

	wf_engine.SetItemUserKey ( 	ItemType	=> ItemType,
					ItemKey		=> ItemKey,
					UserKey		=> ItemUserKey);
	--
	--
	-- Initialize workflow item attributes
	--
	wf_engine.SetItemAttrText ( 	itemtype => itemtype,
	      				itemkey  => itemkey,
  	      				aname 	 => 'REQUISITION_NUMBER',
					avalue	 =>  RequisitionNumber);
	--
	wf_engine.SetItemAttrText ( 	itemtype => itemtype,
	      				itemkey  => itemkey,
  	      				aname 	 => 'REQUISITION_DESCRIPTION',
					avalue	 =>  ItemUserKey);
	--
	wf_engine.SetItemAttrNumber ( 	itemtype => itemtype,
	      				itemkey  => itemkey,
  	      				aname 	 => 'REQUISITION_AMOUNT',
					avalue	 =>  RequisitionAmount );
	--
	wf_engine.SetItemAttrText ( 	itemtype => itemtype,
	      				itemkey  => itemkey,
  	      				aname 	 => 'REQUESTOR_USERNAME',
					avalue	 =>  RequestorUsername);
	--
        -- Set 'forward to' == 'requestor'  in case  requestors can approve their own requisitions
        --
	wf_engine.SetItemAttrText ( 	itemtype => itemtype,
	      				itemkey  => itemkey,
  	      				aname 	 => 'FORWARD_TO_USERNAME',
					avalue	 =>  RequestorUsername);
	--
	wf_engine.SetItemAttrText ( 	itemtype => itemtype,
	      				itemkey  => itemkey,
  	      				aname 	 => 'REQUISITION_PROCESS_OWNER',
					avalue	 =>  ProcessOwner);
	--
	-- Bug 2785163
	wf_engine.SetItemAttrText( 	itemtype => itemtype,
	      				itemkey  => itemkey,
  	      				aname 	 => 'MONITOR_URL',
					avalue	 =>  
			wf_monitor.GetDiagramUrl(WF_CORE.Translate('WF_WEB_AGENT'),
						 itemtype,itemkey,'NO'));

      /**
       Bug 2336455
       The PL/SQL document for the requisition creation is
       now created using the message functions 
       WF_NOTIFICATION(ATTR,<attr1>...) and 
       WF_NOTIFICATION(HISTORY)
	--
	wf_engine.SetItemAttrText(	itemtype => itemtype,
					itemkey  => itemkey,
					aname    => 'REQ_DOCUMENT',
					avalue   =>
			'PLSQL:WF_REQDEMO.CREATE_REQ_DOCUMENT/'||
			ItemType||':'||
			ItemKey);
       **/

	--
	wf_engine.SetItemAttrText(	itemtype => itemtype,
					itemkey  => itemkey,
					aname    => 'REM_DOCUMENT',
					avalue   =>
			'PLSQL:WF_REQDEMO.REMINDER_REQ_DOCUMENT/'||
			ItemType||':'||
			ItemKey);
	--
	wf_engine.SetItemOwner (	itemtype => itemtype,
					itemkey	 => itemkey,
					owner	 => ProcessOwner );

	--
	wf_engine.StartProcess( 	itemtype => itemtype,
	      				itemkey	 => itemkey );
	--
exception
	when others then
		--
		wf_core.context('WF_REQDEMO','StartProcess',RequisitionNumber,RequisitionAmount,RequestorUsername,ProcessOwner,Workflowprocess);
		raise;
		--
end StartProcess;
--
-- Procedure 
--	selector
--
-- Description
--      Determine which process to run
-- IN
--   itemtype  - A valid item type from (WF_ITEM_TYPES table).
--   itemkey   - A string generated from the application object's primary key.
--   actid     - The function activity(instance id).
--   funcmode  - Run/Cancel/Timeout
-- OUT
--   resultout - Name of workflow process to run
--	
procedure selector  ( 	itemtype	in varchar2,
			itemkey  	in varchar2, 
			actid		in number,
			funcmode	in varchar2,
			resultout	out varchar2	) is
--
begin
	--
	-- RUN mode - normal process execution
	--
	if (funcmode = 'RUN') then
		--
		-- Return process to run
		--
		resultout := 'REQUISITION_APPROVAL';
		return;
	end if;
	
	--
exception
	when others then
		wf_core.context('WF_REQDEMO','Selector',itemtype,itemkey,actid,funcmode);
		raise;
end selector;
--
-- Procedure 
--	Forward_Req
--
-- Description - Does nothing currently
--
-- IN
--   itemtype  - A valid item type from (WF_ITEM_TYPES table).
--   itemkey   - A string generated from the application object's primary key.
--   itemuserkey - A string generated from the application object user-friendly
--               primary key.
--   actid     - The function activity(instance id).
--   processowner - The username owner for this item instance.
--   funcmode  - Run/Cancel
-- OUT
--   resultout    - Name of workflow process to run
--	
-- USED BY ACTIVITIES
--  <ITEM_TYPE> <ACTIVITY>
--   WFDEMO     RECORDFORWARD
--
procedure Forward_Req (	itemtype	in varchar2,
			itemkey  	in varchar2,
			actid		in number,
			funcmode	in varchar2,
			resultout	out varchar2	) is
--
begin
	--
	-- RUN mode - normal process execution
	--
	if (funcmode = 'RUN') then
		--
		-- Return process to run
		--
		resultout := 'COMPLETE:';
		return;
	end if;
	
	--
	-- CANCEL mode - activity 'compensation'
	--
	if (funcmode = 'CANCEL') then
		--
		-- Return process to run
		--
		resultout := 'COMPLETE:';
		return;
	end if;		
	--
	-- TIMEOUT mode
	--
	if (funcmode = 'TIMEOUT') then
		resultout := 'COMPLETE:';
		return;
	end if;
	--
exception
	when others then
		wf_core.context('WF_REQDEMO','Forward_Req',itemtype,
				itemkey,to_char(actid),funcmode);
		raise;
end Forward_Req;

--
-- Procedure 
--	Approve_Req
--
-- Description - Does nothing currently
--
-- IN
--   itemtype  - A valid item type from (WF_ITEM_TYPES table).
--   itemkey   - A string generated from the application object's primary key.
--   itemuserkey - A string generated from the application object user-friendly
--               primary key.
--   actid     - The function activity(instance id).
--   processowner - The username owner for this item instance.
--   funcmode  - Run/Cancel/Timeout
-- OUT
--   resultout    - Name of workflow process to run
--	
-- USED BY ACTIVITIES
--  <ITEM_TYPE> <ACTIVITY>
--   WFDEMO     APPROVEREQUISITION
--
procedure Approve_Req (	itemtype	in varchar2,
			itemkey  	in varchar2,
			actid		in number,
			funcmode	in varchar2,
			resultout	out varchar2	) is
--
begin
	--
	-- RUN mode - normal process execution
	--
	if (funcmode = 'RUN') then
		--
		-- Return process to run
		--
		resultout := 'COMPLETE:';
		return;
	end if;
	
	--
	-- CANCEL mode - activity 'compensation'
	--
	if (funcmode = 'CANCEL') then
		--
		-- Return process to run
		--
		resultout := 'COMPLETE:';
		return;
	end if;		
	--
	-- TIMEOUT mode
	--
	if (funcmode = 'TIMEOUT') then
		resultout := 'COMPLETE:';
		return;
	end if;
	--
exception
	when others then
		wf_core.context('WF_REQDEMO','Approve_Req',itemtype,
				itemkey,to_char(actid),funcmode);
		raise;
end Approve_Req;

--
-- Procedure 
--	Reject_Req
--
-- Description - Does nothing currently
--
-- IN
--   itemtype  - A valid item type from (WF_ITEM_TYPES table).
--   itemkey   - A string generated from the application object's primary key.
--   itemuserkey - A string generated from the application object user-friendly
--               primary key.
--   actid     - The function activity(instance id).
--   processowner - The username owner for this item instance.
--   funcmode  - Run/Cancel
-- OUT
--   resultout    - Name of workflow process to run
--	
-- USED BY ACTIVITIES
--  <ITEM_TYPE> <ACTIVITY>
--   WFDEMO     REJECTREQUISITION
--
procedure Reject_Req (	itemtype	in varchar2,
			itemkey  	in varchar2,
			actid		in number,
			funcmode	in varchar2,
			resultout	out varchar2	) is
--
begin
	--
	-- RUN mode - normal process execution
	--
	if (funcmode = 'RUN') then
		--
		-- Return process to run
		--
		resultout := 'COMPLETE:';
		return;
	end if;
	
	--
	-- CANCEL mode - activity 'compensation'
	--
	if (funcmode = 'CANCEL') then
		--
		-- Return process to run
		--
		resultout := 'COMPLETE:';
		return;
	end if;		
	--
	-- TIMEOUT mode
	--
	if (funcmode = 'TIMEOUT') then
		resultout := 'COMPLETE:';
		return;
	end if;
	--
exception
	when others then
		wf_core.context('WF_REQDEMO','Reject_Req',itemtype,
				itemkey,to_char(actid),funcmode);
		raise;
end Reject_Req;

--
-- Procedure 
--	GetManager
--
-- Description
--	Select an Employees manager
-- IN
--	user_name - User Name
-- Out
--      manager of user_name
--
function GetManager ( user_name in varchar2 ) return varchar2 is
--
l_forward_to_username varchar2(320);
--
begin
	--
	select	manager_username
	into	l_forward_to_username
	from 	wf_reqdemo_emp_hierarchy
	where	person_username = user_name;
	--
	return(l_forward_to_username);
	--
exception
	when others then
		wf_core.context('WF_REQDEMO','GetManager',user_name);
		raise;
end GetManager;
--
-- Procedure 
--	CheckSpendingLimit
--
-- Description
--	Check the spending limit of an employee against the requisition amount
-- IN
--	user_name  
--	requisition_amount
--
-- Returns
--    TRUE  -  If RequisitionAmount <= SpendingLimit
--    FALSE -  If RequisitionAmount >  SpendingLimit
--
function CheckSpendingLimit ( user_name in varchar2 ,requisition_amount number ) return boolean is
--
l_spending_limit number;
--
begin
	select	spending_limit
	into	l_spending_limit
	from	wf_reqdemo_emp_authority
	where	username = user_name;
	--
	if ( Requisition_Amount <= l_spending_Limit ) then
		--
		return(TRUE);
	else
		return(FALSE);
	end if;
exception
	when others then
		wf_core.context('WF_REQDEMO','CheckSpendingLimit',user_name,requisition_amount);
		raise;
end;
--
--
-- SelectApprover
--   Workflow cover: Select an employees manager and set Workflow item attributes
-- IN
--   itemtype  - A valid item type from (WF_ITEM_TYPES table).
--   itemkey   - A string generated from the application object's primary key.
--   actid     - The function activity(instance id).
--   funcmode  - Run/Cancel/Timeout
-- OUT
--   Resultout    - 'COMPLETE:T' if employee has a manager
--		  - 'COMPLETE:F' if employee does not have a manager
--
-- USED BY ACTIVITIES
--  <ITEM_TYPE> <ACTIVITY>
--   WFDEMO     SELECTAPPROVER
--
procedure SelectApprover ( 	itemtype	in varchar2,
				itemkey  	in varchar2,
				actid		in number,
				funcmode	in varchar2,
				resultout	out varchar2	) is
--
l_forward_from_username	varchar2(320);
l_forward_to_username	varchar2(320);
--
begin
	--
	-- RUN mode - normal process execution
	--
	if (funcmode = 'RUN') then
		l_forward_to_username := wf_engine.GetItemAttrText(
					itemtype => itemtype,
    					itemkey => itemkey,
    					aname  	=> 'FORWARD_TO_USERNAME' );
		--
		-- Retrieve the last person the requisition was forwarded 
		-- to for approval.
		--
		if ( l_forward_to_username is null ) then
		--
  		  l_forward_to_username := wf_engine.GetItemAttrText(
					itemtype => itemtype,
		    			itemkey  => itemkey,
		    			aname => 'REQUESTOR_USERNAME' );
		
		--
		end if;
		--
		l_forward_from_username := l_forward_to_username;
		--
		wf_engine.SetItemAttrText ( itemtype	=> itemtype,
	      				itemkey  	=> itemkey,
  	      				aname	=> 'FORWARD_FROM_USERNAME',
					avalue	=>  l_forward_from_username);
		--
		-- Call Application API to retrieve an employees manager
		--
		l_forward_to_username := wf_reqdemo.GetManager
					( l_forward_from_username);
	--
		wf_engine.SetItemAttrText (itemtype	=> itemtype,
	      			itemkey  	=> itemkey,
  	      			aname 		=> 'FORWARD_TO_USERNAME',
				avalue		=>  l_forward_to_username);
	--
		if ( l_forward_to_username is null ) then
			--
			resultout := 'COMPLETE:F';
			--
		else
			--
			resultout := 'COMPLETE:T';
			--
		end if;
	-- 
	end if;
	--
  	-- CANCEL mode - activity 'compensation'
	--
  	if (funcmode = 'CANCEL') then
		--
    		resultout := 'COMPLETE:';
    		return;
		--
  	end if;
	--
	-- TIMEOUT mode
	--
	if (funcmode = 'TIMEOUT') then
		resultout := 'COMPLETE:';
		return;
	end if;

exception
	when others then
		wf_core.context('WF_REQDEMO','SelectorApprover',itemtype,itemkey,actid,funcmode);
		raise;
end SelectApprover;

--
-- VerifyAuthority
--   Workflow Cover: Check the spending limit of an employee against the requisition amount
-- IN
--   itemtype  - A valid item type from (WF_ITEM_TYPES table).
--   itemkey   - A string generated from the application object's primary key.
--   actid     - The function activity(instance id).
--   funcmode  - Run/Cancel/Timeout
-- OUT
--   Resultout    
--	'COMPLETE:Y' - If RequisitionAmount <= SpendingLimit
--	'COMPLETE:N' - If RequisitionAmount >  SpendingLimit
--
-- USED BY ACTIVITIES
--  <ITEM_TYPE> <ACTIVITY>
--   WFDEMO     VERIFYAUTHORITY
--
procedure VerifyAuthority ( 	itemtype	in varchar2,
				itemkey  	in varchar2,
				actid		in number,
				funcmode	in varchar2,
				resultout	out varchar2	) 
is
l_forward_to_username	varchar2(320);
l_requisition_amount	number;
l_spending_limit	number;
--
begin
	-- 
	-- RUN mode - normal process execution
	--
	if (funcmode = 'RUN') then

		l_requisition_amount  := wf_engine.GetItemAttrNumber(
					itemtype => itemtype,
			    		itemkey	=> itemkey,
			    		aname => 'REQUISITION_AMOUNT' );
	--
		l_forward_to_username := wf_engine.GetItemAttrText(
				 	itemtype => itemtype,
			    		itemkey	=> itemkey,
			    		aname	=> 'FORWARD_TO_USERNAME' );
		--
		-- Call Application Api to retrieve spending limit
		--
		if ( wf_reqdemo.CheckSpendingLimit(l_forward_to_username
						,l_requisition_amount)) then
		--
			resultout := 'COMPLETE:Y';
		else
			resultout := 'COMPLETE:N';
		end if;
	--
	end if;
	--
  	-- CANCEL mode - activity 'compensation'
	--
  	if (funcmode = 'CANCEL') then
		--
    		resultout := 'COMPLETE:';
    		return;
		--
  	end if;
	--
	-- TIMEOUT mode
	--
	if (funcmode = 'TIMEOUT') then
		resultout := 'COMPLETE:';
		return;
	end if;
	--
exception
	when others then
		wf_core.context('WF_REQDEMO','VerifyAuthority',itemtype,itemkey,actid,funcmode);
		raise;
end VerifyAuthority;
--
procedure Print_Error
is
  error_name	varchar2(30);
  error_message	varchar2(2000);
  error_stack	varchar2(32000);
begin
  htp.htmlOpen;
  htp.headOpen;
  htp.title(wf_core.translate('ERROR'));
  htp.headClose;
  htp.bodyOpen;
  htp.header(3, wf_core.translate('ERROR'));
  htp.br;

  wf_core.get_error(error_name, error_message, error_stack);
  if (error_name is not null) then
	htp.p(error_message);
  elsif (sqlcode != 0) then
	htp.p(sqlerrm);
  end if;

  if (error_stack is not null) then
	htp.p(wf_core.translate('WFDEMO_ERROR_STACK'));
	htp.p(replace(error_stack,chr(10),'<br>') || '<br>');
  end if;

  htp.bodyClose;
  htp.htmlClose;
end Print_Error;

procedure Create_Req
is
-- Employee Approval Hierarchy and Spending Authority
  cursor eh_cursor is
	select person_username
	,      spending_limit
	,      manager_username
	from   wf_reqdemo_emp_hierarchy eh
	,      wf_reqdemo_emp_authority ea
	where  eh.person_username = ea.username;

  emprec eh_cursor%ROWTYPE;

-- Requestor and Process Owner 
  cursor roles is
	select display_name, name
	from   wf_roles
	where  name in ('BLEWIS'
			,'CDOUGLAS'
			,'KWALKER'
			,'SPIERSON'
			,'SYSADMIN'
            ,'TEST.USER.OVER.30.CHARS.ORACLE.COM'
            ,'WFTESTER')
	order  by display_name;

-- 'WFDEMO%' Item Types
  cursor demo_itemtypes is
	select wit.display_name, wit.name
	from   wf_item_types_vl wit
	where  wit.name like 'WFDEMO%'
	order  by wit.display_name;

  status_flag varchar2(15);
  username    varchar2(320);
begin
  -- Check session and current user
  wfa_sec.GetSession(username);
  username := upper(username);

  -- Page title
  htp.htmlOpen;
  htp.headOpen;
  htp.title(ctitle=>wf_core.translate('WFDEMO_TITLE'));
  wfa_html.create_help_function('wf/links/dem.htm?DEMOWEB');
  htp.headClose;

  status_flag := 'FNDIACTV.gif';

    wfa_sec.header(background_only=>FALSE,
		page_title=>wf_core.translate('WFDEMO_SECTION_TITLE'));
    htp.p(htf.img(wfa_html.image_loc||status_flag,'left',
		null, null, 'height=40 width=35 align=middle'));

  htp.p(htf.br||
	htf.bold(wf_core.translate('WFDEMO_DEMO_NOTE'))
	);

  -- wf_reqdemo.submit_req is the url(procedure) to which the contents 
  -- of this form is sent
  htp.formOpen(curl=>'wf_reqdemo.submit_req'
		,cmethod=>'Post', cattributes=>'NAME="WF_REQDEMO"');


 -- Display the Employee Approval Hierarchy and Spending Authority table
  -- If there are no rows found, display a message and exit
  open eh_cursor;
  fetch eh_cursor into emprec;
  if eh_cursor%NOTFOUND then
    close eh_cursor;
    htp.p(wf_core.translate('WFDEMO_NO_HIERARCHY_NOTE'));
    wfa_sec.Footer;
    htp.htmlClose;
    return;
  end if;

  -- Open table to display form 
  htp.tableOpen(calign=>'CENTER'
		,cattributes=>'border=0 cellpadding=2 cellspacing=0');
 -- a blank row
  htp.tableRowOpen;
  htp.tableData(htf.br);
  htp.tableRowClose;
  htp.tableRowOpen;
  htp.tableData(htf.bold(wf_core.translate('WFDEMO_REQUISITION')));
  htp.tableRowClose;


  -- Number field
  htp.tableRowOpen;
  htp.tableData(cvalue=>wf_core.translate('WFDEMO_NUMBER'),
		calign=>'right',
		cattributes=>'valign=middle');
  htp.tableData(cvalue=>htf.formText(cname=>'RequisitionNumber',
		csize=>'30',
		cmaxlength=>'30'),
		calign=>'left');
  htp.tableRowClose;

  -- Description field 
  htp.tableRowOpen;
  htp.tableData(cvalue=>wf_core.translate('WFDEMO_DESCRIPTION'),
		calign=>'right',
		cattributes=>'valign=middle');
  htp.tableData(cvalue=>htf.formText(cname=>'RequisitionDescription',
		csize=>'60',
		cmaxlength=>'80'),
		calign=>'left');
  htp.tableRowClose;


  -- Amount field
  htp.tableRowOpen;
  htp.tableData(cvalue=>wf_core.translate('WFDEMO_AMOUNT'),
		calign=>'right',
		cattributes=>'valign=middle');
  htp.tableData(cvalue=>htf.formText(cname=>'RequisitionAmount',
		csize=>'15',
		cmaxlength=>'44'),
		calign=>'left');
  htp.tableRowClose;


  -- Requestor field (pulldown list of wf_roles)
  htp.tableRowOpen;
  htp.tableData(cvalue=>wf_core.translate('WFDEMO_REQUESTOR'),
		calign=>'right',
		cattributes=>'valign=middle');
  htp.p('<TD>');
  htp.formSelectOpen(cname=>'RequestorUsername');
  for wfr in roles loop
	htp.formSelectOption(cvalue=>wfr.display_name
			,cattributes=>'value='||wfr.name);
  end loop;
  htp.formSelectClose;
  htp.p('</TD>');
  htp.tableRowClose;


  -- Owner field (pulldown list of wf_roles)
  htp.tableRowOpen;
  htp.tableData(cvalue=>wf_core.translate('WFDEMO_PROCESS_OWNER'),
		calign=>'right',
		cattributes=>'valign=middle');
  htp.p('<TD>');
  htp.formSelectOpen(cname=>'ProcessOwner');
  for wfr in roles loop
	htp.formSelectOption(cvalue=>wfr.display_name
			,cattributes=>'value='||wfr.name);
  end loop;
  htp.formSelectClose;
  htp.p('</TD>');
  htp.tableRowClose;

  -- Demo Item Types field (pulldown list of wf_item_types)
  htp.tableRowOpen;
  htp.tableData(cvalue=>wf_core.translate('WFDEMO_ITEM_TYPE'),
		calign=>'right',
		cattributes=>'valign=middle');
  htp.p('<TD>');
  htp.formSelectOpen(cname=>'Item_Type');
  for dit in demo_itemtypes loop
	htp.formSelectOption(cvalue=>dit.display_name
			,cattributes=>'value='||dit.name);
  end loop;
  htp.formSelectClose;
  htp.p('</TD>');
  htp.tableRowClose;

  htp.tableClose;
  htp.formClose;

  -- Show Submit and Clear buttons
--  htp.formSubmit(cvalue=>wf_core.translate('WFDEMO_SUBMIT'));
--  htp.formReset(cvalue=>wf_core.translate('WFDEMO_CLEAR'));

  -- Submit Button
  
  htp.tableopen (calign=>'CENTER');
  htp.tableRowOpen;
  htp.p('<TD>');

  wfa_html.create_reg_button ('javascript:document.WF_REQDEMO.submit()',
				wf_core.translate('WFDEMO_SUBMIT'),
				wfa_html.image_loc,
				'FNDJLFOK.gif',
				wf_core.translate('WFDEMO_SUBMIT'));
  htp.p('</TD>');

  -- Cancel button
  htp.p('<TD>');
  wfa_html.create_reg_button ('javascript:window.history.back()',
				wf_core.translate('CANCEL'),
				wfa_html.image_loc,
				'FNDJLFCN.gif',
				wf_core.translate('CANCEL'));
  htp.p('</TD>');
  htp.tableRowClose;
  htp.tableClose;
  htp.centerClose;


  -- At least one row exists in hierarchy.  Construct the table.
  -- Put Approval Hierarchy and Spending Limit table on left
  -- "blurb" on how the hierarchy and spending limit is used on right.

  htp.br;
  -- Create the outer table without a border and centered
  htp.tableOpen(calign=>'CENTER'
		,cattributes=>'border=0 cellpadding=3 bgcolor=cccccc');

  -- Outer table table contents
  htp.tableRowOpen(null, 'TOP');
  htp.tableData(htf.tableOpen(calign=>'LEFT'
	,cattributes=>'border=1 cellpadding=3 bgcolor=white'||
	' <caption>'||
	htf.bold(wf_core.translate('WFDEMO_HIERARCHY_CAPTION'))||
	'</caption'));

  -- Column headers
  htp.tableRowOpen(cattributes=>'bgcolor=#006699');
  htp.tableData('<font color=#FFFFFF>'||
		wf_core.translate('WFDEMO_EMPLOYEE')||'</font>'
		,'Center');
  htp.tableData('<font color=#FFFFFF>'||
		wf_core.translate('WFDEMO_SPENDING_LIMIT')||'</font>'
		,'Center');
  htp.tableData('<font color=#FFFFFF>'||
		wf_core.translate('WFDEMO_MANAGER')||'</font>'
		,'Center');
  htp.tableRowClose;
  htp.tableRowOpen;
  htp.tableRowClose;
 
  -- Approval Hierarchy and Spending Limit
  loop

    htp.tableRowOpen(null,'TOP');
    htp.tableData(wf_directory.GetRoleDisplayName(emprec.person_username)
		 , 'left');
    htp.tableData(to_char(emprec.spending_limit), 'right');
    if emprec.manager_username is null then
	htp.tableData(htf.br);
    else 
	htp.tableData(wf_directory.GetRoleDisplayName(emprec.manager_username)
		 , 'left');
    end if;
    htp.tableRowClose;

    fetch eh_cursor into emprec;
    exit when eh_cursor%NOTFOUND;
  end loop;
  close eh_cursor;

  -- Close Approval Hierarchy and Spending Authority table  
  htp.tableClose;

  -- display requisition approval demo description in next column
  -- make it an "invisible" table though so the text lines up with the
  -- body of the table to the left
  htp.tableData(htf.tableOpen(calign=>'LEFT'
	,cattributes=>'border=0 cellpadding=0 width=500 bgcolor=cccccc'||
	' <caption>'||
	htf.bold(wf_core.translate('WFDEMO_DESCRIPTION'))||
	'</caption'), 'left');
  htp.tableRowOpen;
  htp.tableData(wf_core.translate('WFDEMO_REQUISITION_DESCRIPTION'));
  htp.tableRowClose;
  htp.tableClose;

  -- Close outer table row
  htp.tableRowClose;
  htp.tableClose;

  wfa_sec.footer;

  htp.htmlClose;
exception
  when others then
    wf_core.context('wf_reqdemo', 'Create_Req');
    Print_Error;
end Create_Req;

procedure Submit_Req(	RequisitionNumber	in varchar2,
			RequisitionDescription	in varchar2,
			RequisitionAmount	in varchar2,
			RequestorUsername	in varchar2,
			ProcessOwner		in varchar2,
			Item_Type		in varchar2 default null)
is
ItemType	varchar2(30) := nvl(Item_Type,'WFDEMO');
ItemKey		varchar2(30) := RequisitionNumber;
l_forward_to_username	varchar2(320);
username	varchar2(320);
begin
  -- Check session and current user
  wfa_sec.GetSession(username);
  username := upper(username);

  -- Start Requisition Approval process

  wf_reqdemo.startprocess(RequisitionNumber=>Submit_Req.RequisitionNumber
			,RequisitionDesc=>
			substr(Submit_Req.RequisitionDescription,1,80)
			,RequisitionAmount=>
			to_number(Submit_Req.RequisitionAmount)
			,RequestorUsername=>Submit_Req.RequestorUsername
			,ProcessOwner=>Submit_Req.ProcessOwner
			,item_type=>Submit_Req.ItemType);

  -- use owa_util.redirect_url to redirect the URL to the confirmation page
  owa_util.redirect_url(curl=>wfa_html.base_url || 
		'/wf_reqdemo.Confirm_Req?RequisitionNumber='||
		wfa_html.conv_special_url_chars(Submit_Req.RequisitionNumber)||
		'&'||'Item_Type='||
		wfa_html.conv_special_url_chars(Submit_Req.ItemType));


exception
  when others then
    wf_core.context('wf_reqdemo', 'Submit_Req', RequisitionNumber,
	RequisitionDescription, RequisitionAmount,RequestorUsername,ProcessOwner);
    Print_Error;
end Submit_Req;

procedure Req_TimeOut(	RequisitionNumber	in varchar2
			,Item_Type		in varchar2 default null)

is
ItemType	varchar2(30) := nvl(Item_Type,'WFDEMO');
username	varchar2(320);
begin
  -- Check session and current user
  wfa_sec.GetSession(username);
  username := upper(username);


  wf_engine.Background(itemtype=>ItemType
			,process_deferred=>FALSE
			,process_timeout=>TRUE);

  -- use owa_util.redirect_url to redirect the URL to the confirmation page
  owa_util.redirect_url(curl=>	wfa_html.base_url|| 
		'/wf_reqdemo.Confirm_Req?RequisitionNumber=' ||
		wfa_html.conv_special_url_chars(Req_TimeOut.RequisitionNumber)||
		'&'||'Item_Type=' ||
		wfa_html.conv_special_url_chars(Req_TimeOut.ItemType));

exception
  when others then
    wf_core.context('wf_reqdemo', 'Req_TimeOut', RequisitionNumber, Item_Type);
    Print_Error;
end Req_TimeOut;

procedure Confirm_Req(	RequisitionNumber	in varchar2
			,Item_Type		in varchar2 default null)
is
ItemType	varchar2(30) := nvl(Item_Type,'WFDEMO');
ItemKey		varchar2(30) := RequisitionNumber;
l_requisition_description varchar2(80);
l_requisition_amount	varchar2(44);
l_requestor		varchar2(320);
l_process_owner		varchar2(320);
l_forward_to_username	varchar2(320);
l_begin_time             varchar2(40);
l_display_name           varchar2(80);
status_flag varchar2(15);
username		varchar2(320);
begin
  -- Check session and current user
  wfa_sec.GetSession(username);
  username := upper(username);

  l_requisition_description := wf_engine.GetItemAttrText(
				itemtype => ItemType,
				itemkey  => ItemKey,
				aname    => 'REQUISITION_DESCRIPTION');
  l_requisition_amount  := wf_engine.GetItemAttrText(
				itemtype => ItemType,
				itemkey  => ItemKey,
				aname    => 'REQUISITION_AMOUNT');
  l_requestor := wf_engine.GetItemAttrText(
				itemtype => ItemType,
				itemkey  => ItemKey,
				aname    => 'REQUESTOR_USERNAME');
  l_process_owner := wf_engine.GetItemAttrText(
				itemtype => ItemType,
				itemkey  => ItemKey,
				aname    => 'REQUISITION_PROCESS_OWNER');
  -- Get the Approver UserName
  l_forward_to_username := wf_engine.GetItemAttrText(
				itemtype => ItemType,
				itemkey  => ItemKey,
				aname    => 'FORWARD_TO_USERNAME');

  -- Get the begin time for the process
  begin
    select to_char(begin_date, 'DD-MON-YYYY HH24:MI:SS')
    into   l_begin_time
    from   wf_items
    where  item_type = ItemType
    and    item_key  = ItemKey;
  exception
    when no_data_found then
      l_begin_time := '';
  end;

  -- Get the display name for the item type passed
  begin
    select wit.display_name
    into   l_display_name
    from   wf_item_types_vl wit
    where  wit.name = ItemType;
  exception
    when no_data_found then
      l_display_name := '';
  end;


  -- Confirmation page
  htp.htmlOpen;
  htp.headOpen;

  htp.title(ctitle=>wf_core.translate('WFDEMO_TITLE'));
  wfa_html.create_help_function('wf/links/req.htm?REQCONF');
  htp.headClose;

  -- Open body and draw standard header
  wfa_sec.header(page_title=>wf_core.translate('WFDEMO_REQ_CREATED'));

  -- display Workflow flag for fun
  status_flag := 'FNDIACTV.gif';
  htp.p(htf.img(wfa_html.image_loc||status_flag,'center',
		null, null, 'height=40 width=35'));

  htp.tableOpen(cattributes=>'border=0 cellpadding=2 cellspacing=0');

  -- Requisition title
  htp.tableRowOpen;
  htp.tableData(cvalue=>htf.bold(wf_core.translate('WFDEMO_REQUISITION')),
		cattributes=>'align=RIGHT');
  htp.tableRowClose;

  -- Requisition Number
  htp.tableRowOpen;
  htp.tableData(cvalue=>wf_core.translate('WFDEMO_NUMBER'),
		cattributes=>'align=RIGHT');
  htp.tableData(cvalue=>htf.bold(Confirm_Req.RequisitionNumber));
  htp.tableRowClose;

  -- Requisition Description
  htp.tableRowOpen;
  htp.tableData(cvalue=>wf_core.translate('WFDEMO_DESCRIPTION'),
		cattributes=>'align=RIGHT');
  htp.tableData(cvalue=>htf.bold(l_requisition_description));
  htp.tableRowClose;

  -- Requisition Amount
  htp.tableRowOpen;
  htp.tableData(cvalue=>wf_core.translate('WFDEMO_AMOUNT'),
		cattributes=>'align=RIGHT');
  htp.tableData(cvalue=>htf.bold(l_requisition_amount));
  htp.tableRowClose;

  -- Requestor
  htp.tableRowOpen;
  htp.tableData(cvalue=>wf_core.translate('WFDEMO_REQUESTOR'),
		cattributes=>'align=RIGHT');
  htp.tableData(cvalue=>htf.bold(wf_directory.GetRoleDisplayName(l_requestor)));
  htp.tableRowClose;

  -- Process Owner
  htp.tableRowOpen;
  htp.tableData(cvalue=>wf_core.translate('WFDEMO_PROCESS_OWNER'),
		cattributes=>'align=RIGHT');
  htp.tableData(cvalue=>htf.bold(wf_directory.GetRoleDisplayName(l_process_owner)));
  htp.tableRowClose;

  -- Item Type
  htp.tableRowOpen;
  htp.tableData(cvalue=>wf_core.translate('WFDEMO_ITEM_TYPE'),
		cattributes=>'align=RIGHT');
  htp.tableData(cvalue=>htf.bold(l_display_name));
  htp.tableRowClose;
  htp.tableClose;

  -- Monitor Envelope page
  htp.br;
  htp.p(wf_core.translate('WFDEMO_MON_NOTE')|| '  ' ||
	htf.anchor(
	curl=>wf_monitor.GetEnvelopeURL(base_url,
		ItemType,
		ItemKey,
		'Y'),
	ctext=>wf_core.translate('WFDEMO_MONITOR')) 
	);
  htp.br;
  htp.br;

  -- View Notifications for the requisition approver 
  -- Should be displayed on the Oracle Workflow Demonstration Webpage only
  -- This won't work with the security an actual user would install
--rks  htp.p('View notifications for the requisition approver, '
--	|| l_forward_to_username 
--	|| ', by selecting '
--	||htf.anchor(curl=> wfa_html.base_url || 
--			'/wfa_html.Viewer?User_ID='
--		|| wfa_html.conv_special_url_chars(l_forward_to_username),
--rks		ctext=>'Login as '|| l_forward_to_username) );

  -- View Notifications for the requisition approver note
  htp.p(wf_core.translate('WFDEMO_APPROVER_NOTIFICATIONS')
	|| ' ' || htf.bold(l_forward_to_username) ||'.');
  htp.br;
  htp.br;

  -- View Notifications for the requestor
  -- Should be displayed on the Oracle Workflow Demonstration Webpage only
  -- This won't work with the security an actual user would install
--rks  htp.p('View notifications for the requestor, '
--	|| l_requestor
--	|| ', by selecting '
--	|| htf.anchor(curl=> wfa_html.base_url || 
--			'/wfa_html.Viewer?User_ID=' || 
--                       wfa_html.conv_special_url_chars(l_requestor),
--rks		ctext=>'Login as '|| l_requestor) );

  -- View Notifications for the requestor note
  htp.p(wf_core.translate('WFDEMO_REQUESTOR_NOTIFICATIONS')
	|| ' ' || htf.bold(l_requestor) ||'.');
  htp.br;
  htp.br;

  -- Process Timeout for item type WFDEMO
  htp.p(wf_core.translate('WFDEMO_TIMEOUT_NOTE') || '  ' ||
	htf.anchor(curl=> wfa_html.base_url || 
		'/wf_reqdemo.Req_TimeOut?RequisitionNumber=' || 
		wfa_html.conv_special_url_chars(Confirm_Req.RequisitionNumber) ||
		'&'||'Item_Type=' ||
		wfa_html.conv_special_url_chars(Confirm_Req.ItemType),
	ctext=>wf_core.translate('WFDEMO_TIMEOUT'))
	);
  htp.br;
  htp.br;
  htp.p(wf_core.translate('WFDEMO_REQ_TIMEOUT_NOTE'));
  htp.p(htf.bold(l_begin_time));
  htp.br;
  htp.p(wf_core.translate('WFDEMO_REM_TIMEOUT_NOTE'));
  htp.br;
  htp.br;

  -- Enter another Requisition
  htp.p(wf_core.translate('WFDEMO_CREATE_REQ_NOTE') || '  ' ||
		 htf.anchor(curl=> wfa_html.base_url ||
		'/wf_reqdemo.Create_Req',
	ctext=>wf_core.translate('WFDEMO_CREATE_REQ')));
  

  htp.bodyClose;
  htp.htmlClose;
exception
  when others then
    wf_core.context('wf_reqdemo', 'Confirm_Req', Confirm_Req.RequisitionNumber,
	l_requisition_description,l_requisition_amount,
	l_requestor,l_process_owner);
    Print_Error;
end Confirm_Req;

/** Bug 2336498 
    The requisition PL/SQL document is now replaced by the 
    message functions WF_NOTIFICATION(ATTRS,<attr1>,...)
    and WF_NOTIFICATION(HISTORY).
--
-- Create_Req_Document
--   Generate the Requisition Document for display in messages, either
--   text or html
-- IN
--   document_id	- Item Key
--   display_type	- either 'text/plain' or 'text/html'
--   document		- document buffer 
--   document_type	- type of document buffer created, either 'text/plain'
--			  or 'text/html'
-- OUT
-- USED BY
--
procedure Create_Req_Document(document_id	in	varchar2,
				display_type	in	varchar2,
				document	in out	varchar2,
				document_type	in out	varchar2)
is
ItemType			varchar2(30);
ItemKey				varchar2(30);
l_requisition_description	varchar2(80);
l_requisition_amount		varchar2(44);
l_requestor			varchar2(320);
l_forward_from			varchar2(320);
l_note				varchar2(2000);
ntf_high			varchar2(15);
begin

  -- parse document_id for the ':' dividing item type name from item key value
  -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
  -- release 2.5 version of this demo
  ItemType := nvl(substr(document_id, 1, instr(document_id,':')-1),'WFDEMO');
  ItemKey  := substr(document_id
		, instr(document_id,':')+1);

  l_requisition_description := wf_engine.GetItemAttrText(
				itemtype => ItemType,
				itemkey  => ItemKey,
				aname    => 'REQUISITION_DESCRIPTION');
  l_requisition_amount  := wf_engine.GetItemAttrText(
				itemtype => ItemType,
				itemkey  => ItemKey,
				aname    => 'REQUISITION_AMOUNT');
  l_requestor := wf_engine.GetItemAttrText(
				itemtype => ItemType,
				itemkey  => ItemKey,
				aname    => 'REQUESTOR_USERNAME');
  l_forward_from := wf_engine.GetItemAttrText(
				itemtype => ItemType,
				itemkey  => ItemKey,
				aname    => 'FORWARD_FROM_USERNAME');
  l_note := wf_engine.GetItemAttrText(
				itemtype => ItemType,
				itemkey  => ItemKey,
				aname    => 'NOTE');


  -- Create an html text buffer 
  if (display_type = 'text/html') then

  -- build page body with Requisition data

  document := document||    htf.bold(wf_core.translate('WFDEMO_APPROVAL_REQ'));

 -- open a table without border lines
  document := document||    htf.tableOpen(cattributes=>'border=0 cellpadding=2 cellspacing=0');

 -- a blank row
  document := document||    htf.tableRowOpen;
  document := document||    htf.tableData(htf.br);
  document := document||    htf.tableRowClose;

  -- Requestion
  document := document||    htf.tableRowOpen;
  document := document||    htf.tableData(cvalue=>htf.bold(wf_core.translate('WFDEMO_REQUISITION')),
                            cattributes=>'align=RIGHT');
  document := document||    htf.tableData(htf.br);
  document := document||    htf.tableRowClose;

  -- Requisition Number
  document := document||    htf.tableRowOpen;
  document := document||    htf.tableData(cvalue=>wf_core.translate('WFDEMO_NUMBER'),
		            cattributes=>'align=RIGHT');
  document := document||    htf.tableData(cvalue=>htf.bold(ItemKey));
  document := document||    htf.tableRowClose;

  -- Requisition Description
  document := document||    htf.tableRowOpen;
  document := document||    htf.tableData(cvalue=>wf_core.translate('WFDEMO_DESCRIPTION'),
		cattributes=>'align=RIGHT');
  document := document||    htf.tableData(cvalue=>htf.bold(l_requisition_description));
  document := document||    htf.tableRowClose;

  -- Requisition Amount
  document := document||    htf.tableRowOpen;
  document := document||    htf.tableData(cvalue=>wf_core.translate('WFDEMO_AMOUNT'),
		cattributes=>'align=RIGHT');
  document := document||    htf.tableData(cvalue=>htf.bold(l_requisition_amount));
  document := document||    htf.tableRowClose;

  -- Forward From 
  document := document||    htf.tableRowOpen;
  document := document||    htf.tableData(cvalue=>wf_core.translate('WFDEMO_FORWARD_FROM'),
		cattributes=>'align=RIGHT');
  document := document||    htf.tableData(cvalue=>htf.bold(
		wf_directory.GetRoleDisplayName(l_forward_from)));
  document := document||    htf.tableRowClose;

  -- Requestor
  document := document||    htf.tableRowOpen;
  document := document||    htf.tableData(cvalue=>wf_core.translate('WFDEMO_REQUESTOR'),
		cattributes=>'align=RIGHT');
  document := document||    htf.tableData(cvalue=>htf.bold(
		wf_directory.GetRoleDisplayName(l_requestor)));
  document := document||    htf.tableRowClose;

  -- Note
  document := document||    htf.tableRowOpen;
  document := document||    htf.tableData(cvalue=>wf_core.translate('WFDEMO_NOTE'),
		cattributes=>'align=RIGHT');
  document := document||    htf.tableData(cvalue=>htf.bold(l_note));
  document := document||    htf.tableRowClose;

  document := document||    htf.tableClose;

  document_type := 'text/html';

  return;
  end if;

  -- Create a plain text buffer 
  if (display_type = 'text/plain') then

  document := document|| 'Requisition approval requested for:';
  document := document|| chr(10)|| '        Number: '||
		ItemKey;
  document := document|| chr(10)|| '   Description: '||
		l_requisition_description;
  document := document|| chr(10)|| '        Amount: '||
		l_requisition_amount;
  document := document|| chr(10)|| 'Forwarded From: '||
		wf_directory.GetRoleDisplayName(l_forward_from);
  document := document|| chr(10)|| '     Requestor: '||
		wf_directory.GetRoleDisplayName(l_requestor);
  document := document|| chr(10)|| '          Note: '||
		l_note;

  document_type := 'text/plain';

  return;
  end if;

exception
when others then
	wf_core.context('WF_REQDEMO','Create_Req_Document',ItemType,ItemKey);
	raise;

end Create_Req_Document;

**/

--
-- Reminder_Req_Document
--   Generate the Reminder Requisition Document for display in messages, either
--   text or html
-- IN
--   document_id	- Item Key
--   display_type	- either 'text/plain' or 'text/html'
--   document		- document buffer 
--   document_type	- type of document buffer created, either 'text/plain'
--			  or 'text/html'
-- OUT
-- USED BY
--
procedure Reminder_Req_Document(document_id	in	varchar2,
				display_type	in	varchar2,
				document	in out	varchar2,
				document_type	in out	varchar2)
is
ItemType			varchar2(30);
ItemKey				varchar2(30);
l_requisition_description	varchar2(80);
l_requisition_amount		varchar2(44);
l_requestor			varchar2(320);
l_forward_from			varchar2(320);
l_note				varchar2(2000);
ntf_rem				varchar2(15);
begin
  -- parse document_id for the ':' dividing item type name from item key value
  -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
  -- release 2.5
  ItemType := nvl(substr(document_id, 1, instr(document_id,':')-1),'WFDEMO');
  ItemKey  := substr(document_id
		, instr(document_id,':')+1);

  l_requisition_description := wf_engine.GetItemAttrText(
				itemtype => ItemType,
				itemkey  => ItemKey,
				aname    => 'REQUISITION_DESCRIPTION');
  l_requisition_amount  := wf_engine.GetItemAttrText(
				itemtype => ItemType,
				itemkey  => ItemKey,
				aname    => 'REQUISITION_AMOUNT');
  l_requestor := wf_engine.GetItemAttrText(
				itemtype => ItemType,
				itemkey  => ItemKey,
				aname    => 'REQUESTOR_USERNAME');
  l_forward_from := wf_engine.GetItemAttrText(
				itemtype => ItemType,
				itemkey  => ItemKey,
				aname    => 'FORWARD_FROM_USERNAME');

  --The previous responders note is saved to WF_NOTE2  
  l_note := wf_engine.GetItemAttrText(
				itemtype => ItemType,
				itemkey  => ItemKey,
				aname    => 'WF_NOTE2');

  -- Create an html text buffer 
  if (display_type = 'text/html') then

  -- build page body with Requisition data

  document := document||    
    htf.bold('The following requisition requires your approval.  ');
  document := document||
    htf.bold('You will be sent this reminder every 7 days until you respond.');

 -- open a table without border lines
  document := document||    htf.tableOpen(cattributes=>'border=0 cellpadding=2 cellspacing=0');

 -- a blank row
  document := document||    htf.tableRowOpen;
  document := document||    htf.tableData(htf.br);
  document := document||    htf.tableRowClose;

  -- Requisition 
  document := document||    htf.tableRowOpen;
  document := document||    htf.tableData(cvalue=>htf.bold(wf_core.translate('WFDEMO_REQUISITION'))
					,cattributes=>'align=RIGHT');
  document := document||    htf.tableData(htf.br);
  document := document||    htf.tableRowClose;

  -- Requisition Number
  document := document||    htf.tableRowOpen;
  document := document||    htf.tableData(cvalue=>wf_core.translate('WFDEMO_NUMBER'),
		cattributes=>'align=RIGHT');
  document := document||    htf.tableData(cvalue=>htf.bold(ItemKey));
  document := document||    htf.tableRowClose;

  -- Requisition Description
  document := document||    htf.tableRowOpen;
  document := document||    htf.tableData(cvalue=>wf_core.translate('WFDEMO_DESCRIPTION'),
		cattributes=>'align=RIGHT');
  document := document||    htf.tableData(cvalue=>htf.bold(l_requisition_description));
  document := document||    htf.tableRowClose;

  -- Requisition Amount
  document := document||    htf.tableRowOpen;
  document := document||    htf.tableData(cvalue=>wf_core.translate('WFDEMO_AMOUNT'),
		cattributes=>'align=RIGHT');
  document := document||    htf.tableData(cvalue=>htf.bold(l_requisition_amount));
  document := document||    htf.tableRowClose;

  -- Forward From 
  document := document||    htf.tableRowOpen;
  document := document||    htf.tableData(cvalue=>wf_core.translate('WFDEMO_FORWARDED_FROM'),
		cattributes=>'align=RIGHT');
  document := document||    htf.tableData(cvalue=>htf.bold(
		wf_directory.GetRoleDisplayName(l_forward_from)));
  document := document||    htf.tableRowClose;

  -- Requestor
  document := document||    htf.tableRowOpen;
  document := document||    htf.tableData(cvalue=>wf_core.translate('WFDEMO_REQUESTOR'),
		cattributes=>'align=RIGHT');
  document := document||    htf.tableData(cvalue=>htf.bold(
		wf_directory.GetRoleDisplayName(l_requestor)));
  document := document||    htf.tableRowClose;

  -- Note
  document := document||    htf.tableRowOpen;
  document := document||    htf.tableData(cvalue=>wf_core.translate('WFDEMO_NOTE'),
		cattributes=>'align=RIGHT');
  document := document||    htf.tableData(cvalue=>htf.bold(l_note));
  document := document||    htf.tableRowClose;

  document := document||    htf.tableClose;

  document_type := 'text/html';

  return;
  end if;

  -- Create a plain text buffer 
  if (display_type = 'text/plain') then
  document := document|| 'The following requisition requires your approval.';
  document := document|| chr(10)||
	'You will be sent this reminder every 7 days until you respond.';
  document := document|| chr(10)|| '        Number: '||
	ItemKey;
  document := document|| chr(10)|| '   Description: '||
	l_requisition_description;
  document := document|| chr(10)|| '        Amount: '||
	l_requisition_amount;
  document := document|| chr(10)|| 'Forwarded From: '||
	wf_directory.GetRoleDisplayName(l_forward_from);
  document := document|| chr(10)|| '     Requestor: '||
	wf_directory.GetRoleDisplayName(l_requestor);

  document := document|| chr(10)|| '          Note: '||
	l_note;

  document_type := 'text/plain';

  return;
  end if;

exception
when others then
	wf_core.context('WF_REQDEMO','Reminder_Req_Document',ItemType,document_id);
	raise;

end Reminder_Req_Document;

end wf_reqdemo;
/
--show errors package body wf_reqdemo 

exit;