Category: PIPER-RX Managing Workflows Paper 2008
Name: MWP003 - Step 2A - Aged Workflows (Months)
ReportDefFileName: .\MWP003 - Step 2A - Aged Workflows (Months).fr3
===Begin Comments===
Copyright (C) 2009 G Piper

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU Affero General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU Affero General Public License for more details.

a copy of the GNU Affero General Public Licenses provided is part of the 
down load Terms and Conditions available on our site www.piper-rx.com or 
you can refere to www.gnu.org/licenses/gpl.html
===End Comments===
===Begin SQL===
-- ****************************************************
-- ** PIPER-RX - Managing Workflows Paper
-- **
-- ** Copyright (C) 2008 G.Piper
-- **
-- ** Step 2A - Aged Workflows (Months)
-- **
-- ** Version 1.0  Nov-08  G.Piper - PIPER-Rx
-- ****************************************************

SELECT to_char(sysdate, 'DD-Mon-YY HH24:MI') report_date,
       wi.item_type workflow,
       substr(witt.display_name,1,50) display_name,
       count(*) sample_size,
       round(max(sysdate - wi.begin_date),0) oldest_workflow_days,
       sum(decode( trunc(months_between (sysdate, wi.begin_date)),
               0, 1,  0 )) "Current", 
       sum(decode( trunc(months_between (sysdate, wi.begin_date)),
               1, 1,  0 )) "30 Day", 
       sum(decode( trunc(months_between (sysdate, wi.begin_date)),
               2, 1,  0 )) "60 Day", 
       sum(decode( trunc(months_between (sysdate, wi.begin_date)),
               3, 1,  0 )) "90 Day", 
       sum(decode( trunc(months_between (sysdate, wi.begin_date)),
               null, 0, 0, 0, 1, 0, 2, 0, 3, 0, 1 )) "120 + Days"
  FROM applsys.wf_items wi,
       applsys.wf_item_types_tl witt
 WHERE wi.end_date is null
   and wi.item_type = witt.name 
   and witt.language = userenv('LANG')
 Group by to_char(sysdate, 'DD-Mon-YY HH24:MI'),
          wi.item_type,
          witt.display_name
 ORDER by wi.item_type
===End SQL===
===Begin Report Definition===
<?xml version="1.0" encoding="utf-8"?>
<TfrxReport Name="" Version="3.21" DotMatrixReport="False" IniFile="\Software\Fast Reports" PreviewOptions.Buttons="2047" PreviewOptions.Zoom="1" PrintOptions.Printer="Default" ReportOptions.CreateDate="38653.3379644213" ReportOptions.Description.Text="" ReportOptions.LastChange="39954.3982401273" ScriptLanguage="PascalScript" ScriptText.Text="&#13;&#10;begin&#13;&#10;&#13;&#10;end." PropData="044C656674020803546F70020E08446174617365747301010C2C00000020446174615365743D225265706F727473466F726D2E2220446174615365744E616D653D224D6173746572220000095661726961626C65730100055374796C650100">
  <TfrxReportPage Name="Page1" Orientation="poLandscape" PaperWidth="297" PaperHeight="210" PaperSize="9" LeftMargin="10" RightMargin="10" TopMargin="10" BottomMargin="10" ColumnWidth="0" ColumnPositions.Text="" HGuides.Text="" VGuides.Text="">
    <TfrxReportTitle Name="ReportTitle1" Height="94.48825" Left="0" Top="18.89765" Width="1046.92981">
      <TfrxMemoView Name="Memo2" Left="0" Top="0" Width="1046.92981" Height="83.14966" Color="9502720" Font.Charset="1" Font.Color="16777215" Font.Height="-16" Font.Name="Arial" Font.Style="1" HAlign="haCenter" ParentFont="False" VAlign="vaCenter" Text="WORKFLOW - AGED WORKFLOWS&#13;&#10;(Active Workflows Only)&#13;&#10;As at [Master.&#34;REPORT_DATE&#34;]"/>
      <TfrxLineView Name="Line2" Left="0" Top="86.92919" Width="1046.92981" Height="0" Frame.Color="8388608" Frame.Typ="4" Frame.Width="2"/>
      <TfrxMemoView Name="Memo3" Left="0" Top="0" Width="117.16543" Height="18.89765" Font.Charset="1" Font.Color="16777215" Font.Height="-13" Font.Name="Arial" Font.Style="1" ParentFont="False" Text="MWP003"/>
      <TfrxMemoView Name="Memo4" Left="937.32344" Top="3.77953" Width="75.5906" Height="26.45671" GroupIndex="2" Font.Charset="0" Font.Color="16777215" Font.Height="-16" Font.Name="Arial" Font.Style="1" ParentFont="False" Text="PIPER -"/>
      <TfrxMemoView Name="Memo5" Left="1001.57545" Top="3.77953" Width="37.7953" Height="26.45671" GroupIndex="2" Font.Charset="0" Font.Color="255" Font.Height="-16" Font.Name="Arial" Font.Style="1" ParentFont="False" Text="Rx"/>
    </TfrxReportTitle>
    <TfrxMasterData Name="MasterData1" Height="22.67718" Left="0" Top="222.99227" Width="1046.92981" ColumnWidth="0" ColumnGap="0" DataSet="ReportsForm." DataSetName="Master" RowCount="0">
      <TfrxMemoView Name="Memo7" Left="0" Top="0" Width="79.37013" Height="18.89765" DataField="WORKFLOW" DataSet="ReportsForm." DataSetName="Master" Font.Charset="1" Font.Color="-16777208" Font.Height="-13" Font.Name="Arial" Font.Style="0" ParentFont="False" Text="[Master.&#34;WORKFLOW&#34;]"/>
      <TfrxMemoView Name="Memo12" Left="419.52783" Top="0" Width="79.37013" Height="18.89765" DataField="SAMPLE_SIZE" DataSet="ReportsForm." DataSetName="Master" DisplayFormat.DecimalSeparator="." DisplayFormat.FormatStr="%2.0n" DisplayFormat.Kind="fkNumeric" Font.Charset="1" Font.Color="-16777208" Font.Height="-13" Font.Name="Arial" Font.Style="0" HAlign="haRight" ParentFont="False" Text="[Master.&#34;SAMPLE_SIZE&#34;]"/>
      <TfrxMemoView Name="Memo15" Left="525.35467" Top="0" Width="79.37013" Height="18.89765" DataField="OLDEST_WORKFLOW_DAYS" DataSet="ReportsForm." DataSetName="Master" DisplayFormat.DecimalSeparator="." DisplayFormat.FormatStr="%2.0n" DisplayFormat.Kind="fkNumeric" Font.Charset="1" Font.Color="-16777208" Font.Height="-13" Font.Name="Arial" Font.Style="0" HAlign="haRight" ParentFont="False" Text="[Master.&#34;OLDEST_WORKFLOW_DAYS&#34;]"/>
      <TfrxMemoView Name="Memo16" Left="631.18151" Top="0" Width="79.37013" Height="18.89765" DataField="Current" DataSet="ReportsForm." DataSetName="Master" DisplayFormat.DecimalSeparator="." DisplayFormat.FormatStr="%2.0n" DisplayFormat.Kind="fkNumeric" Font.Charset="1" Font.Color="-16777208" Font.Height="-13" Font.Name="Arial" Font.Style="0" HAlign="haRight" ParentFont="False" Text="[Master.&#34;Current&#34;]"/>
      <TfrxMemoView Name="Memo17" Left="714.33117" Top="0" Width="83.14966" Height="18.89765" DataField="30 Day" DataSet="ReportsForm." DataSetName="Master" DisplayFormat.DecimalSeparator="." DisplayFormat.FormatStr="%2.0n" DisplayFormat.Kind="fkNumeric" Font.Charset="1" Font.Color="-16777208" Font.Height="-13" Font.Name="Arial" Font.Style="0" HAlign="haRight" ParentFont="False" Text="[Master.&#34;30 Day&#34;]"/>
      <TfrxMemoView Name="Memo18" Left="801.26036" Top="0" Width="79.37013" Height="18.89765" DataField="60 Day" DataSet="ReportsForm." DataSetName="Master" DisplayFormat.DecimalSeparator="." DisplayFormat.FormatStr="%2.0n" DisplayFormat.Kind="fkNumeric" Font.Charset="1" Font.Color="-16777208" Font.Height="-13" Font.Name="Arial" Font.Style="0" HAlign="haRight" ParentFont="False" Text="[Master.&#34;60 Day&#34;]"/>
      <TfrxMemoView Name="Memo19" Left="884.41002" Top="0" Width="79.37013" Height="18.89765" DataField="90 Day" DataSet="ReportsForm." DataSetName="Master" DisplayFormat.DecimalSeparator="." DisplayFormat.FormatStr="%2.0n" DisplayFormat.Kind="fkNumeric" Font.Charset="1" Font.Color="-16777208" Font.Height="-13" Font.Name="Arial" Font.Style="0" HAlign="haRight" ParentFont="False" Text="[Master.&#34;90 Day&#34;]"/>
      <TfrxMemoView Name="Memo20" Left="967.55968" Top="0" Width="79.37013" Height="18.89765" DataField="120 + Days" DataSet="ReportsForm." DataSetName="Master" DisplayFormat.DecimalSeparator="." DisplayFormat.FormatStr="%2.0n" DisplayFormat.Kind="fkNumeric" Font.Charset="1" Font.Color="-16777208" Font.Height="-13" Font.Name="Arial" Font.Style="0" HAlign="haRight" ParentFont="False" Text="[Master.&#34;120 + Days&#34;]"/>
      <TfrxMemoView Name="Memo11" Left="86.92919" Top="0" Width="328.81911" Height="18.89765" DataField="DISPLAY_NAME" DataSet="ReportsForm." DataSetName="Master" Font.Charset="1" Font.Color="-16777208" Font.Height="-13" Font.Name="Arial" Font.Style="0" ParentFont="False" Text="[Master.&#34;DISPLAY_NAME&#34;]"/>
    </TfrxMasterData>
    <TfrxPageFooter Name="PageFooter1" Height="49.13389" Left="0" Top="385.51206" Width="1046.92981">
      <TfrxMemoView Name="Memo1" Left="854.17378" Top="26.45671" Width="188.9765" Height="18.89765" HAlign="haRight" Text="Page [Page#] of [TotalPages#]"/>
      <TfrxLineView Name="Line1" Left="0" Top="0" Width="1046.92981" Height="0" Frame.Color="8388608" Frame.Typ="4" Frame.Width="2"/>
      <TfrxMemoView Name="Memo8" Left="0" Top="26.45671" Width="147.40167" Height="18.89765" Font.Charset="1" Font.Color="-16777208" Font.Height="-13" Font.Name="Arial" Font.Style="0" ParentFont="False" Text="[Date] [Time]"/>
      <TfrxMemoView Name="Memo9" Left="442.20501" Top="26.45671" Width="351.49629" Height="18.89765" GroupIndex="5" Font.Charset="1" Font.Color="0" Font.Height="-13" Font.Name="Arial" Font.Style="1" HAlign="haCenter" ParentFont="False" Text="Home of The Oracle E-Busness Suite Resource Center"/>
      <TfrxMemoView Name="Memo10" Left="317.48052" Top="26.45671" Width="124.72449" Height="18.89765" GroupIndex="5" Font.Charset="1" Font.Color="16711680" Font.Height="-13" Font.Name="Arial" Font.Style="1" HAlign="haCenter" ParentFont="False" Text="www.piper-rx.com"/>
      <TfrxMemoView Name="Memo29" Left="457.32313" Top="3.77952999999997" Width="185.19697" Height="18.89765" GroupIndex="5" Font.Charset="1" Font.Color="-16777208" Font.Height="-13" Font.Name="Arial" Font.Style="1" HAlign="haCenter" ParentFont="False" Text="Copyright © 2008 G.Piper"/>
    </TfrxPageFooter>
    <TfrxPageHeader Name="PageHeader1" Height="26.45671" Left="0" Top="136.06308" Width="1046.92981">
      <TfrxMemoView Name="Memo6" Left="0" Top="0" Width="68.03154" Height="18.89765" Font.Charset="1" Font.Color="16711680" Font.Height="-13" Font.Name="Arial" Font.Style="1" ParentFont="False" Text="Workflow"/>
      <TfrxLineView Name="Line3" Left="0" Top="22.67718" Width="1046.92981" Height="0" Frame.Color="8388608" Frame.Typ="4" Frame.Width="2"/>
      <TfrxMemoView Name="Memo13" Left="86.92919" Top="0" Width="166.29932" Height="18.89765" Font.Charset="1" Font.Color="16711680" Font.Height="-13" Font.Name="Arial" Font.Style="1" ParentFont="False" Text="Display Name"/>
      <TfrxMemoView Name="Memo14" Left="415.7483" Top="0" Width="83.14966" Height="18.89765" Font.Charset="1" Font.Color="16711680" Font.Height="-13" Font.Name="Arial" Font.Style="1" HAlign="haRight" ParentFont="False" Text="Sample size"/>
      <TfrxMemoView Name="Memo21" Left="653.85869" Top="0" Width="64.25201" Height="18.89765" Font.Charset="1" Font.Color="16711680" Font.Height="-13" Font.Name="Arial" Font.Style="1" HAlign="haRight" ParentFont="False" Text="Current"/>
      <TfrxMemoView Name="Memo22" Left="737.00835" Top="0" Width="64.25201" Height="18.89765" Font.Charset="1" Font.Color="16711680" Font.Height="-13" Font.Name="Arial" Font.Style="1" HAlign="haRight" ParentFont="False" Text="30 Day"/>
      <TfrxMemoView Name="Memo25" Left="510.23655" Top="0" Width="94.48825" Height="18.89765" Font.Charset="1" Font.Color="16711680" Font.Height="-13" Font.Name="Arial" Font.Style="1" HAlign="haRight" ParentFont="False" Text="Oldest (Days)"/>
      <TfrxMemoView Name="Memo24" Left="816.37848" Top="0" Width="64.25201" Height="18.89765" Font.Charset="1" Font.Color="16711680" Font.Height="-13" Font.Name="Arial" Font.Style="1" HAlign="haRight" ParentFont="False" Text="60 Day"/>
      <TfrxMemoView Name="Memo23" Left="903.30767" Top="0" Width="64.25201" Height="18.89765" Font.Charset="1" Font.Color="16711680" Font.Height="-13" Font.Name="Arial" Font.Style="1" HAlign="haRight" ParentFont="False" Text="90 Day"/>
      <TfrxMemoView Name="Memo26" Left="982.6778" Top="0" Width="64.25201" Height="18.89765" Font.Charset="1" Font.Color="16711680" Font.Height="-13" Font.Name="Arial" Font.Style="1" HAlign="haRight" ParentFont="False" Text="120 +"/>
    </TfrxPageHeader>
    <TfrxReportSummary Name="ReportSummary1" Height="56.69295" Left="0" Top="306.14193" Width="1046.92981">
      <TfrxLineView Name="Line4" Left="0" Top="7.55905999999999" Width="1046.92981" Height="0" Frame.Color="8388608" Frame.Style="fsDouble" Frame.Typ="4" Frame.Width="2"/>
      <TfrxMemoView Name="Memo27" Left="377.953" Top="15.11812" Width="120.94496" Height="18.89765" DisplayFormat.DecimalSeparator="." DisplayFormat.FormatStr="%2.0n" DisplayFormat.Kind="fkNumeric" Font.Charset="1" Font.Color="-16777208" Font.Height="-13" Font.Name="Arial" Font.Style="1" HAlign="haRight" ParentFont="False" Text="[SUM(&#60;Master.&#34;SAMPLE_SIZE&#34;&#62;,MasterData1)]"/>
      <TfrxMemoView Name="Memo28" Left="204.09462" Top="15.11812" Width="162.51979" Height="18.89765" Font.Charset="1" Font.Color="-16777208" Font.Height="-13" Font.Name="Arial" Font.Style="1" ParentFont="False" Text="Total Active Workflows:"/>
    </TfrxReportSummary>
  </TfrxReportPage>
</TfrxReport>