<!DOCTYPE openRPTDef>
<report>
 <title>Time Phased Sales History</title>
 <name>TimePhasedSalesHistory</name>
 <description></description>
 <grid>
  <snap/>
  <show/>
  <x>0.05</x>
  <y>0.05</y>
 </grid>
 <size>Letter</size>
 <portrait/>
 <topmargin>50</topmargin>
 <bottommargin>50</bottommargin>
 <rightmargin>50</rightmargin>
 <leftmargin>50</leftmargin>
 <querysource>
  <name>head</name>
  <sql>SELECT &lt;? if exists("byCust") ?>
         text('Customer')
       &lt;? elseif exists("byProdcat") ?>
         text('Product Category')
       &lt;? else ?>
         text('Item')
       &lt;? endif ?>
          AS groupby,
      &lt;? if exists("byCust") ?>
         text('Name')
       &lt;? else ?>
         text('Description')
       &lt;? endif ?>
          AS groupbyDescrip,
       &lt;? if exists("salesDollars") ?>
         text('')
       &lt;? else ?>
         text('UOM')
       &lt;? endif ?>
       AS lbl_uom,
       &lt;? if exists("inventoryUnits") ?>
         text('Qty.')
       &lt;? elseif exists("capacityUnits") ?>
         text('Capacity')
       &lt;? elseif exists("altCapacityUnits") ?>
         text('Alt. Capacity')
       &lt;? elseif exists("salesDollars") ?>
         text('Sales')
       &lt;? else ?>
         text('ERROR')
       &lt;? endif ?>
       AS lbl_unittype;     </sql>
 </querysource>
 <querysource>
  <name>detail</name>
  <sql>SELECT *, 
  coalesce(unit,0) AS unit, 
&lt;? if exists("salesDollars") ?>
  formatExtPrice(coalesce(unit,0)) AS f_unit
&lt;? else ?>
  formatQty(coalesce(unit,0)) AS f_unit
&lt;? endif ?>
FROM
-- Outer Table Query
(SELECT DISTINCT
       calitem_id,
       findPeriodStart(calitem_id) AS pstart,
       findPeriodEnd(calitem_id) AS pend,
       (formatDate(findPeriodStart(calitem_id)) || '-' || formatDate(findPeriodEnd(calitem_id))) AS period,
&lt;? if exists("byCust") ?>
         cust_id, cust_number AS f_number, cust_name AS f_description
&lt;? elseif exists("byProdcat") ?>
         prodcat_id, prodcat_code AS f_number, prodcat_descrip AS f_description
&lt;? elseif exists("byItem") ?>
         item_id, item_number AS f_number, item_descrip1 AS f_description
&lt;? endif ?>
       , warehous_id, warehous_code
&lt;? if exists("salesDollars") ?>
       , &lt;? value("baseCurrAbbr") ?>::text AS f_uom
&lt;? else ?>
  &lt;? if exists("inventoryUnits") ?>
       , uom_name AS f_uom
  &lt;? elseif exists("capacityUnits") ?>
       , itemcapuom(item_id) AS f_uom
  &lt;? elseif exists("altCapacityUnits") ?>
       , itemaltcapuom(item_id) AS f_uom
  &lt;? endif ?>
&lt;? endif ?>
FROM cohist JOIN itemsite ON (itemsite_id=cohist_itemsite_id)
            JOIN item ON (item_id=itemsite_item_id)
&lt;? if exists("inventoryUnits") ?>
 JOIN uom ON (item_inv_uom_id=uom_id)
&lt;? endif ?>
 JOIN site() ON (itemsite_warehous_id=warehous_id)
&lt;? if reExists("[cC]ust") ?>
 JOIN custinfo ON (cohist_cust_id=cust_id)
&lt;? endif ?> 
&lt;? if reExists("[pP]rodcat") ?>
 JOIN prodcat ON (item_prodcat_id=prodcat_id)
&lt;? endif ?>
&lt;? if reExists("custgrp") ?>
 JOIN custgrpitem ON (custgrpitem_cust_id=cohist_cust_id)
 JOIN custgrp ON (custgrpitem_custgrp_id=custgrp_id)
&lt;? endif ?>
    ,   ( SELECT rcalitem_id AS calitem_id,
                findPeriodStart(rcalitem_id) AS calitem_start,
                findPeriodEnd(rcalitem_id) AS calitem_end
           FROM rcalitem
          WHERE (rcalitem_id IN (
&lt;? foreach("period_id_list") ?>
  &lt;? if not isfirst("period_id_list") ?>
    ,
  &lt;? endif ?>
  &lt;? value("period_id_list") ?>
&lt;? endforeach ?>
                                )
                )
          UNION
         SELECT acalitem_id AS calitem_id,
                findPeriodStart(acalitem_id) AS calitem_start,
                findPeriodEnd(acalitem_id) AS calitem_end
           FROM acalitem
          WHERE (acalitem_id IN (
&lt;? foreach("period_id_list") ?>
  &lt;? if not isfirst("period_id_list") ?>
    ,
  &lt;? endif ?>
  &lt;? value("period_id_list") ?>
&lt;? endforeach ?>
                                )
                )
       ) AS calitem
WHERE ((cohist_invcdate BETWEEN
&lt;? foreach("period_id_list") ?>
  &lt;? if isfirst("period_id_list") ?>
    findPeriodStart(&lt;? value("period_id_list") ?>)
  &lt;? endif ?>
  &lt;? if isLast("period_id_list") ?>
    AND findPeriodEnd(&lt;? value("period_id_list") ?>)
  &lt;? endif ?>
&lt;? endforeach ?>
)
&lt;? if exists("item_id") ?> 
    AND (item_id=&lt;? value("item_id") ?>)
&lt;? endif ?>
&lt;? if exists("cust_id") ?> 
    AND (cust_id=&lt;? value("cust_id") ?>)
&lt;? endif ?>
&lt;? if exists("custtype_id") ?> 
    AND (cust_custtype_id=&lt;? value("custtype_id") ?>)
&lt;? endif ?>
&lt;? if exists("custtype_pattern") ?>
    AND (cust_custtype_id IN (SELECT custtype_id 
                              FROM custtype 
                              WHERE (custtype_code ~ &lt;? value("custtype_pattern") ?>)))
&lt;? endif ?>
&lt;? if exists("custgrp_id") ?> 
    AND (custgrpitem_custgrp_id=&lt;? value("custgrp_id") ?>)
&lt;? endif ?>
&lt;? if exists("custgrp_pattern") ?>
    AND (cust_custgrp_name ~ &lt;? value("custgrp_pattern") ?>)))
&lt;? endif ?>
&lt;? if exists("prodcat_id") ?> 
    AND (item_prodcat_id=&lt;? value("prodcat_id") ?>) 
&lt;? endif ?>
&lt;? if exists("prodcat_pattern") ?>
    AND (item_prodcat_id IN (SELECT prodcat_id  
                             FROM prodcat 
                             WHERE (prodcat_code ~ &lt;? value("prodcat_pattern") ?>)))
&lt;? endif ?>
&lt;? if exists("warehous_id") ?> 
    AND(itemsite_warehous_id=&lt;? value("warehous_id") ?>) 
&lt;? endif ?>
      )
ORDER BY pstart, f_number, f_description, f_uom, warehous_code) list
LEFT OUTER JOIN
-- Sales calcuations
-- Loop through each period bucket to find bookings for period
(&lt;? foreach("period_id_list") ?>
  SELECT 
       &lt;? value("period_id_list") ?> AS calitem_id,
       warehous_id,
&lt;? if exists("byCust") ?>
         cust_id
&lt;? elseif exists("byProdcat") ?>
         prodcat_id 
&lt;? elseif exists("byItem") ?>
         item_id
&lt;? endif ?>
&lt;? if exists("salesDollars") ?>
       , sum(round(cohist_qtyshipped * currtobase(cohist_curr_id, cohist_unitprice, cohist_invcdate), 2)) AS unit
       , &lt;? value("baseCurrAbbr") ?>::text AS f_uom
&lt;? else ?>
       , sum(cohist_qtyshipped)
  &lt;? if exists("capacityUnits") ?>
       * itemcapinvrat(item_id)
  &lt;? elseif exists("altCapacityUnits") ?>
       * itemaltcapinvrat(item_id)
  &lt;? endif ?>
       AS unit
  &lt;? if exists("inventoryUnits") ?>
       , uom_name AS f_uom
  &lt;? elseif exists("capacityUnits") ?>
       , itemcapuom(item_id) AS f_uom
  &lt;? elseif exists("altCapacityUnits") ?>
       , itemaltcapuom(item_id) AS f_uom
  &lt;? endif ?>
&lt;? endif ?>
FROM cohist JOIN itemsite ON (itemsite_id=cohist_itemsite_id)
            JOIN item ON (item_id=itemsite_item_id)
&lt;? if exists("inventoryUnits") ?>
   JOIN uom ON (item_inv_uom_id=uom_id)
&lt;? endif ?>
   JOIN site() ON (itemsite_warehous_id=warehous_id)
&lt;? if reExists("[cC]ust") ?>
   JOIN custinfo ON (cohist_cust_id=cust_id)
&lt;? endif ?> 
&lt;? if reExists("[pP]rodcat") ?>
   JOIN prodcat ON (item_prodcat_id=prodcat_id)
&lt;? endif ?>
&lt;? if reExists("custgrp") ?>
 JOIN custgrpitem ON (custgrpitem_cust_id=cohist_cust_id)
 JOIN custgrp ON (custgrpitem_custgrp_id=custgrp_id)
&lt;? endif ?>
  WHERE ((cohist_invcdate BETWEEN findPeriodStart(&lt;? value("period_id_list") ?>) AND findPeriodEnd(&lt;? value("period_id_list") ?>))
&lt;? if exists("item_id") ?> 
    AND (item_id=&lt;? value("item_id") ?>)
&lt;? endif ?>
&lt;? if exists("cust_id") ?> 
    AND (cust_id=&lt;? value("cust_id") ?>)
&lt;? endif ?>
&lt;? if exists("custtype_id") ?> 
    AND (cust_custtype_id=&lt;? value("custtype_id") ?>)
&lt;? endif ?>
&lt;? if exists("custtype_pattern") ?>
    AND (cust_custtype_id IN (SELECT custtype_id 
                              FROM custtype 
                              WHERE (custtype_code ~ &lt;? value("custtype_pattern") ?>)))
&lt;? endif ?>
&lt;? if exists("custgrp_id") ?> 
    AND (custgrpitem_custgrp_id=&lt;? value("custgrp_id") ?>)
&lt;? endif ?>
&lt;? if exists("custgrp_pattern") ?>
    AND (cust_custgrp_name ~ &lt;? value("custgrp_pattern") ?>)))
&lt;? endif ?>
&lt;? if exists("prodcat_id") ?> 
    AND (item_prodcat_id=&lt;? value("prodcat_id") ?>) 
&lt;? endif ?>
&lt;? if exists("prodcat_pattern") ?>
    AND (item_prodcat_id IN (SELECT prodcat_id  
                             FROM prodcat 
                             WHERE (prodcat_code ~ &lt;? value("prodcat_pattern") ?>)))
&lt;? endif ?>
&lt;? if exists("warehous_id") ?> 
    AND(itemsite_warehous_id=&lt;? value("warehous_id") ?>) 
&lt;? endif ?>
      )
GROUP BY
&lt;? if exists("byCust") ?>
  cust_id, 
  &lt;? if exists("capacityUnits") ?>
    item_id,
  &lt;? elseif exists("altCapacityUnits") ?>
    item_id,
  &lt;? endif ?>
&lt;? elseif exists("byProdcat") ?>
  prodcat_id,
  &lt;? if exists("capacityUnits") ?>
    item_id,
  &lt;? elseif exists("altCapacityUnits") ?>
    item_id,
  &lt;? endif ?>
&lt;? elseif exists("byItem") ?>
  item_id,
&lt;? endif ?>
  f_uom, warehous_id
  &lt;? if isLast("period_id_list") ?>
  &lt;? else ?>
UNION
  &lt;? endif ?>
&lt;? endforeach ?> 
) sales ON
&lt;? if exists("byCust") ?>
   (list.cust_id=sales.cust_id)
&lt;? elseif exists("byProdcat") ?>
    (list.prodcat_id=sales.prodcat_id)
&lt;? elseif exists("byItem") ?>
    (list.item_id=sales.item_id)
&lt;? endif ?>
   AND (list.calitem_id=sales.calitem_id)
   AND (list.warehous_id=sales.warehous_id)
   AND (list.f_uom=sales.f_uom)</sql>
 </querysource>
 <rpthead>
  <height>221</height>
  <label>
   <rect>
    <x>10</x>
    <y>200</y>
    <width>150</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Period</string>
  </label>
  <line>
   <xstart>5</xstart>
   <ystart>215</ystart>
   <xend>745</xend>
   <yend>215</yend>
   <weight>2</weight>
  </line>
  <label>
   <rect>
    <x>340</x>
    <y>3</y>
    <width>385</width>
    <height>33</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>18</size>
    <weight>bold</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Time Phased Sales History</string>
  </label>
  <field>
   <rect>
    <x>660</x>
    <y>200</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>lbl_unittype</column>
   </data>
  </field>
  <field>
   <rect>
    <x>530</x>
    <y>200</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>lbl_uom</column>
   </data>
  </field>
  <label>
   <rect>
    <x>615</x>
    <y>200</y>
    <width>35</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Site</string>
  </label>
  <field>
   <rect>
    <x>220</x>
    <y>200</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>groupby</column>
   </data>
  </field>
  <field>
   <rect>
    <x>330</x>
    <y>200</y>
    <width>200</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>groupbyDescrip</column>
   </data>
  </field>
  <text>
   <rect>
    <x>10</x>
    <y>33</y>
    <width>725</width>
    <height>15</height>
   </rect>
   <bottompad>12</bottompad>
   <font>
    <face>Arial</face>
    <size>10</size>
    <weight>bold</weight>
   </font>
   <left/>
   <top/>
   <data>
    <query>Parameter Query</query>
    <column>filter</column>
   </data>
  </text>
 </rpthead>
 <pghead>
  <firstpage/>
  <height>6</height>
 </pghead>
 <pghead>
  <height>21</height>
  <line>
   <xstart>5</xstart>
   <ystart>15</ystart>
   <xend>745</xend>
   <yend>15</yend>
   <weight>2</weight>
  </line>
  <label>
   <rect>
    <x>615</x>
    <y>-1</y>
    <width>35</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Site</string>
  </label>
  <label>
   <rect>
    <x>10</x>
    <y>-1</y>
    <width>150</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <string>Period</string>
  </label>
  <field>
   <rect>
    <x>530</x>
    <y>-1</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>lbl_uom</column>
   </data>
  </field>
  <field>
   <rect>
    <x>660</x>
    <y>-1</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <hcenter/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>lbl_unittype</column>
   </data>
  </field>
  <field>
   <rect>
    <x>215</x>
    <y>-1</y>
    <width>80</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>groupby</column>
   </data>
  </field>
  <field>
   <rect>
    <x>330</x>
    <y>-1</y>
    <width>200</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>head</query>
    <column>groupbyDescrip</column>
   </data>
  </field>
 </pghead>
 <section>
  <name>detail</name>
  <group>
   <name>detail</name>
   <column>pstart</column>
   <head>
    <height>6</height>
    <field>
     <rect>
      <x>10</x>
      <y>5</y>
      <width>150</width>
      <height>15</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>8</size>
      <weight>bold</weight>
     </font>
     <hcenter/>
     <vcenter/>
     <data>
      <query>detail</query>
      <column>period</column>
     </data>
    </field>
   </head>
   <foot>
    <height>31</height>
    <field>
     <rect>
      <x>660</x>
      <y>5</y>
      <width>80</width>
      <height>15</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>8</size>
      <weight>bold</weight>
     </font>
     <right/>
     <vcenter/>
     <data>
      <query>detail</query>
      <column>unit</column>
     </data>
     <format builtin="true">extprice</format>
     <tracktotal subtotal="true"/>
    </field>
    <line>
     <xstart>640</xstart>
     <ystart>0</ystart>
     <xend>745</xend>
     <yend>0</yend>
     <weight>0</weight>
    </line>
    <label>
     <rect>
      <x>530</x>
      <y>5</y>
      <width>110</width>
      <height>15</height>
     </rect>
     <font>
      <face>Arial</face>
      <size>8</size>
      <weight>normal</weight>
     </font>
     <right/>
     <vcenter/>
     <string>Total:</string>
    </label>
   </foot>
  </group>
  <detail>
   <key>
    <query>detail</query>
   </key>
   <height>16</height>
   <field>
    <rect>
     <x>660</x>
     <y>0</y>
     <width>80</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <right/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>f_unit</column>
    </data>
   </field>
   <field>
    <rect>
     <x>615</x>
     <y>0</y>
     <width>35</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <hcenter/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>warehous_code</column>
    </data>
   </field>
   <field>
    <rect>
     <x>530</x>
     <y>0</y>
     <width>80</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <hcenter/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>f_uom</column>
    </data>
   </field>
   <field>
    <rect>
     <x>220</x>
     <y>0</y>
     <width>100</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <left/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>f_number</column>
    </data>
   </field>
   <field>
    <rect>
     <x>330</x>
     <y>0</y>
     <width>200</width>
     <height>15</height>
    </rect>
    <font>
     <face>Arial</face>
     <size>8</size>
     <weight>bold</weight>
    </font>
    <left/>
    <vcenter/>
    <data>
     <query>detail</query>
     <column>f_description</column>
    </data>
   </field>
  </detail>
 </section>
 <pgfoot>
  <height>16</height>
  <label>
   <rect>
    <x>5</x>
    <y>-1</y>
    <width>85</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Report Date:</string>
  </label>
  <field>
   <rect>
    <x>95</x>
    <y>-1</y>
    <width>100</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>Context Query</query>
    <column>report_date</column>
   </data>
  </field>
  <field>
   <rect>
    <x>690</x>
    <y>-1</y>
    <width>51</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>bold</weight>
   </font>
   <left/>
   <vcenter/>
   <data>
    <query>Context Query</query>
    <column>page_number</column>
   </data>
  </field>
  <label>
   <rect>
    <x>600</x>
    <y>-1</y>
    <width>85</width>
    <height>15</height>
   </rect>
   <font>
    <face>Arial</face>
    <size>8</size>
    <weight>normal</weight>
   </font>
   <right/>
   <vcenter/>
   <string>Page:</string>
  </label>
 </pgfoot>
</report>
