/*=======================================================================+
 |  Copyright (c) 2000 Oracle Corporation Redwood Shores, California, USA|
 |                            All rights reserved.                       |
 +=======================================================================+
 | FILENAME
 |   wfevdemb.sql
 |
 | DESCRIPTION
 |   PL/SQL body for package:  WF_EVENTDEMO
 |
 | NOTES
 | MODIFIED     10/10/00 Created 
 *=======================================================================*/

/* $Header: wfevdemb.sql 26.11 2004/09/24 06:00:56 vshanmug ship $ */

whenever sqlerror exit failure rollback;

create or replace package body WF_EVENTDEMO as

base_url       VARCHAR2(2000) := wfa_html.base_url;

--
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 Order	
--
-- Description		Creates a Purchase Order.
procedure Create_Order is

-- Item Number
  cursor items is
	select item_number
	from	wf_eventdemo_items;

-- Requestor and Process Owner
  cursor roles is
        select display_name, name
        from   wf_roles
        where  name in ('BLEWIS'
                        ,'CDOUGLAS'
                        ,'KWALKER'
                        ,'SPIERSON'
                        ,'SYSADMIN')
        order  by display_name;

  status_flag varchar2(15);
  username    varchar2(30);

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('WFEVENTDEMO_TITLE'));
  wfa_html.create_help_function('wf/links/evd.htm?EVDEMOWEB');
  htp.headClose;
  status_flag := 'FNDIACTV.gif';

  wfa_sec.header(background_only=>FALSE,
                page_title=>wf_core.translate('WFEVENTDEMO_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('WFEVENTDEMO_DEMO_NOTE'))
        );

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


 -- 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('WFEVENTDEMO_BUYER_NOTE')));
  htp.tableRowClose;


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

  -- Item Number field (pulldown list of wf_roles)
  htp.tableRowOpen;
  htp.tableData(cvalue=>wf_core.translate('WFEVENTDEMO_ITEM'),
                calign=>'right',
                cattributes=>'valign=middle');
  htp.p('<TD>');
  htp.formSelectOpen(cname=>'Item_Number');
  for item in items loop
        htp.formSelectOption(cvalue=>item.item_number
                        ,cattributes=>'value='||item.item_number);
  end loop;
  htp.formSelectClose;
  htp.p('</TD>');
  htp.tableRowClose;

  -- Description field
  htp.tableRowOpen;
  htp.tableData(cvalue=>wf_core.translate('WFEVENTDEMO_ITEMDESCRIPTION'),
                calign=>'right',
                cattributes=>'valign=middle');
  htp.tableData(cvalue=>htf.formText(cname=>'Item_Description',
                csize=>'60',
                cmaxlength=>'80'),
                calign=>'left');
  htp.tableRowClose;
 
  -- Delivery Date
  htp.tableRowOpen;
  htp.tableData(cvalue=>wf_core.translate('WFEVENTDEMO_DATE'),
                calign=>'right',
                cattributes=>'valign=middle');
  htp.tableData(cvalue=>htf.formText(cname=>'Delivery_Date',
                csize=>'15',
                cmaxlength=>'44'),
                calign=>'left');
  htp.tableRowClose;

  -- Amount field
  htp.tableRowOpen;
  htp.tableData(cvalue=>wf_core.translate('WFEVENTDEMO_AMOUNT'),
                calign=>'right',
                cattributes=>'valign=middle');
  htp.tableData(cvalue=>htf.formText(cname=>'Total_Cost',
                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=>'Requestor');
  for wfr in roles loop
        htp.formSelectOption(cvalue=>wfr.display_name
                        ,cattributes=>'value='||wfr.name);
  end loop;
  htp.formSelectClose;
  htp.p('</TD>');
  htp.tableRowClose;
  htp.tableClose;

  htp.formClose;

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

  wfa_html.create_reg_button ('javascript:document.WF_EVENTDEMO.submit()',
                                wf_core.translate('WFEVENTDEMO_SUBMIT'),
                                wfa_html.image_loc,
                                'FNDJLFOK.gif',
                                wf_core.translate('WFEVENTDEMO_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;
  wfa_sec.footer;

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

procedure SubmitPO (PO_Number		in varchar2,
			Requestor	in varchar2,
			Item_Number	in varchar2,
			Item_Description in varchar2,
			Total_Cost	in number,
			Delivery_Date	in varchar2)
is 

l_url		varchar2(4000);

begin
	insert into wf_eventdemo_po
		(PO_Number,
                 Requestor,
                 Item_Number,
                 Item_Description,
                 Total_Cost,
                 Delivery_Date)        
	values
		(PO_Number,
                 Requestor,
                 Item_Number,
                 nvl(Item_Description,'No Item Description Entered'),
                 Total_Cost,
                 to_date(nvl(Delivery_Date, to_char(sysdate)),'DD-MON-YYYY'));

	commit;

	wf_event.raise('demo.oracle.wf.b2b.po.create', po_number);

	l_url :=  (wfa_html.base_url||
                '/WF_EVENTDEMO.Confirm_Order?p_po_number=' ||
                wfa_html.conv_special_url_chars(('PO'||po_number)));
	
	 owa_util.redirect_url(curl=> l_url);
exception
when others then
   wf_core.context('WF_EVENTDEMO','SubmitPO');
   wf_eventdemo.Print_Error;
end;
--
-- Procedure
--	Confirm Order
--
-- Description		Confirm Order, track progress
--
procedure Confirm_Order (
	P_Po_Number	in	varchar2)
is

l_order		varchar2(100);
username	varchar2(30);

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

  -- Print confirmation page
  htp.htmlOpen;
  htp.title(ctitle=>'Event Demo Order Confirmation');
  wfa_html.create_help_function('wf/links/evd.htm?EVDEMOWEB');
  htp.headClose;
  -- Open body and draw standard header
  wfa_sec.header(page_title=>wf_core.translate('WFEVENTDEMO_TRACK_ORDER')||' - '||P_Po_Number
                );
  htp.br;

  -- htp.br;
  -- htp.p(wf_core.translate('WFEVENTDEMO_QUEUE')||' '||
  --      htf.anchor(curl=> wfa_html.base_url ||
  --               '/wf_event_html.eventqueuedisplay',
  --       ctext=>wf_core.translate('WFEVENTDEMO_QUEUEDISPLAY')));
  -- htp.br;

  htp.br;
  htp.p(wf_core.translate('WFEVENTDEMO_MON_NOTE')||' '||
      htf.anchor(
          curl=>wf_monitor.GetAdvancedEnvelopeUrl(
                x_agent         => base_url,
                x_item_type     => 'WFEVDEME',
                x_item_key      => P_Po_Number,
                x_admin_mode    => 'YES',
                x_options       => 'YES')
          ,ctext=>wf_core.translate('WFDEMO_MONITOR')));
  htp.br;

  htp.br;
  htp.p(wf_core.translate('WFEVENTDEMO_PROCESSORDER_NOTE')||' '||
        htf.anchor(curl=> wfa_html.base_url ||
                '/WF_EVENTDEMO.process_order?p_item_key='|| 
                wfa_html.conv_special_url_chars(P_Po_Number),
        ctext=>wf_core.translate('WFEVENTDEMO_PROCESSORDER')));
  htp.br;

  htp.br;
  htp.bodyClose;
  htp.htmlClose;
exception
  when others then
    wf_core.context('WF_EVENTDEMO', 'Confirm_Order');
    wf_eventdemo.Print_Error;
end Confirm_Order;
--
-- Procedure
--	Process Order
--
-- Description: Run Background Engine/Listener 
--
procedure Process_Order(
 P_ITEM_KEY	in	varchar2) is

  l_url		varchar2(200);
l_foo number;
l_job integer;
agent		varchar2(40);

cursor iq is
	select name
	from wf_agents
	where direction = 'IN'
	and status = 'ENABLED'
	and system_guid = wf_core.translate('WF_SYSTEM_GUID');

begin

  --for i in iq LOOP
  --dbms_job.submit(l_job,'wf_event.listen(''WF_IN'');',SYSDATE,NULL);
  --end loop;

  --commit;

  wf_engine.Background(itemtype=>'WFEVDEME',
                       process_deferred=>TRUE,
                       process_timeout=>TRUE);


  l_url :=  (wfa_html.base_url||
                '/WF_EVENTDEMO.Confirm_Order?p_po_number=' ||
                wfa_html.conv_special_url_chars((p_item_key)));
       
  owa_util.redirect_url(curl=> l_url);

exception
  when others then
    wf_core.context('WF_EVENTDEMO','Process_Order');
    wf_eventdemo.Print_Error;
end;
--
-- Procedure
--	Track Order
--
-- Description:		Find Order to track
procedure track_order is

status_flag varchar2(15);
username    varchar2(30);
l_job       integer;

cursor order_numbers is
	select item_key
	from wf_items
	where item_type = 'WFEVDEME'
	order by item_key desc;

begin

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

  -- Run Listener in Case this is the first time
  dbms_job.submit(l_job,'wf_event.listen(''WF_IN'');',SYSDATE,NULL);
  commit;

  -- Page title
  htp.htmlOpen;
  htp.headOpen;
  htp.title(ctitle=>wf_core.translate('WFEVENTDEMO_TITLE'));
  wfa_html.create_help_function('wf/links/evd.htm?EVDEMOWEB');
  htp.headClose;
  status_flag := 'FNDIACTV.gif';

  wfa_sec.header(background_only=>FALSE,
                page_title=>wf_core.translate('WFEVENTDEMO_TRACK_ORDER'));

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

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

  htp.tableOpen(calign=>'CENTER');

  -- a blank row
  htp.tableRowOpen;
  htp.tableData(htf.br);
  htp.tableData(htf.br);
  htp.tableRowClose;

  -- Order Number field (pulldown list of Order Numbers)
  htp.tableRowOpen;
  htp.tableData(htf.br);
  htp.tableData(htf.br);
  htp.tableRowClose;

  htp.tableRowOpen;
  htp.tableData(cvalue=>wf_core.translate('WFEVENTDEMO_PONUMBER'),
                calign=>'right',
                cattributes=>'valign=middle');
  htp.p('<TD>');
  htp.formSelectOpen(cname=>'P_Po_Number');
  for order_number in order_numbers loop
        htp.formSelectOption(cvalue=>order_number.item_key
                        ,cattributes=>'value='||order_number.item_key);
  end loop;
  htp.formSelectClose;
  htp.p('</TD>');
  htp.tableRowClose;
  htp.tableClose;

  htp.formClose;

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

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

  htp.tableRowClose;
  htp.tableClose;
  htp.centerClose;
  wfa_sec.footer;

  htp.htmlClose;
exception
  when others then
    wf_core.context('WF_EVENTDEMO','Track_Order');
    wf_eventdemo.Print_Error;
end;
--
-- Function 
-- 	Generate XML	
--
-- Description		Generates XML PO
function GenerateXML (
  P_EVENT_NAME     in    varchar2,
  P_EVENT_KEY      in    varchar2
) return clob is

cursor po is
	select * 
	from	wf_eventdemo_po
	where   po_number = p_event_key;

po_rec	wf_eventdemo_po%ROWTYPE;

msg		clob;
l_message_data	varchar2(4000) :=
			'<?xml version = ''1.0'' encoding = ''UTF8''?>
        <ORDER>
	<PO_NUMBER>AAA</PO_NUMBER>
	<REQUESTOR>BBB</REQUESTOR>
	<ITEM_NUMBER>CCC</ITEM_NUMBER>
	<ITEM_DESCRIPTION>DDD</ITEM_DESCRIPTION>
	<TOTAL_COST>EEE</TOTAL_COST>
	<DELIVERY_DATE>FFF</DELIVERY_DATE>
	<INVOICE_NUMBER>###</INVOICE_NUMBER>
	</ORDER>
';

begin

  open po;
  fetch po into po_rec;
  if po%FOUND then
	l_message_data := replace(l_message_data,'AAA',po_rec.po_number);
	l_message_data := replace(l_message_data,'BBB',po_rec.requestor);
	l_message_data := replace(l_message_data,'CCC',po_rec.item_number);
	l_message_data := replace(l_message_data,'DDD',po_rec.item_description);
	l_message_data := replace(l_message_data,'EEE',po_rec.total_cost);
	l_message_data := replace(l_message_data,'FFF',to_char(po_rec.delivery_date,'DD-MON-YYYY'));
	l_message_data := replace(l_message_data,'###','INV'||po_rec.po_number);

	dbms_lob.createtemporary(msg, FALSE, DBMS_LOB.CALL);

	dbms_lob.write(msg, length(l_message_data), 1, l_message_data);

	return(msg);
	
  end if;
  close po;
exception
  when others then
    wf_core.context('WF_EVENTDEMO','GenerateXML');
    wf_eventdemo.Print_Error;
end GENERATEXML;
--
-- Procedure
--      FindSupplier
--
-- Description - Does nothing currently
--
procedure FindSupplier ( 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_EVENTDEMO','FindSupplier',itemtype,
                                itemkey,to_char(actid),funcmode);
                raise;
end FindSupplier;
--
-- Procedure
--      StockCheck 
--
-- Description - Does nothing currently
--
procedure StockCheck ( 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_EVENTDEMO','StockCheck',itemtype,
                                itemkey,to_char(actid),funcmode);
                raise;
end StockCheck;
--
-- Procedure
--      CheckShipping 
--
-- Description - Does nothing currently
--
procedure CheckShipping ( 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_EVENTDEMO','CheckShipping',itemtype,
                                itemkey,to_char(actid),funcmode);
                raise;
end CheckShipping;
--
-- Procedure
--     CreateInvoice 
--
-- Description - Does nothing currently
--
procedure CreateInvoice ( 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_EVENTDEMO','CreateInvoice',itemtype,
                                itemkey,to_char(actid),funcmode);
                raise;
end CreateInvoice;
--
-- Function 
--      DeriveCorrelationID 
--
-- Description
--   This rule function derives the correlation id from the event
--   It will be associated with a subscription that fires before
--   the main subscription which will be launch or continue a
--   a workflow process.
--
function DeriveCorrelationID (p_subscription_guid in     raw,
                              p_event             in out wf_event_t) 
return varchar2 is
l_event varchar2(80);
l_eventkey varchar(80);
l_source_type varchar(8);
begin

  select source_type into l_source_type
  from wf_event_subscriptions
  where guid = p_subscription_guid;

  l_eventkey := p_event.GetEventKey();
  l_event := p_event.GetEventName();

  if l_event = 'demo.oracle.wf.b2b.po.create' then
    if l_source_type = 'LOCAL' then
      p_event.SetCorrelationId('PO'||l_eventkey);
    elsif l_source_type = 'EXTERNAL' then
      p_event.SetCorrelationId('SO'||l_eventkey);
    end if;
  elsif l_event in ('demo.oracle.wf.b2b.po.invoice',
			'demo.oracle.wf.b2b.po.ack',
			'demo.oracle.wf.b2b.po.asn') then
    if l_source_type = 'LOCAL' then
      return 'ERROR';
    elsif l_source_type = 'EXTERNAL' then
      p_event.SetCorrelationId('PO'||l_eventkey);
    end if;
  end if;

  return 'SUCCESS';

exception
  when others then
    wf_core.context('Wf_EventDemo', 'DeriveCorrelationId', 
						p_event.getEventName(),
                                                p_subscription_guid);
    wf_event.setErrorInfo(p_event, 'ERROR');
    return 'ERROR';
end DeriveCorrelationID;
end WF_EVENTDEMO;
/
-- show error package body WF_EVENTDEMO 
commit;
exit;