git.roojs.org Git - xtuple/blob - foundation-database/public/tables/report/ReorderExceptionsByPlannerCode.xml

   1 <!DOCTYPE openRPTDef>
   2 <report>
   3  <title>Reorder Exceptions by Planner Code</title>
   4  <name>ReorderExceptionsByPlannerCode</name>
   5  <description></description>
   6  <size>Letter</size>
   7  <portrait/>
   8  <topmargin>50</topmargin>
   9  <bottommargin>50</bottommargin>
  10  <rightmargin>50</rightmargin>
  11  <leftmargin>50</leftmargin>
  12  <querysource>
  13   <name>head</name>
  14   <sql>SELECT &lt;? if exists(&quot;plancode_id&quot;) ?>
  15          ( SELECT (plancode_code || '-' || plancode_name)
  16              FROM plancode
  17             WHERE plancode_id=&lt;? value(&quot;plancode_id&quot;) ?>)
  18        &lt;? elseif exists(&quot;plancode_pattern&quot;) ?>
  19          text(&lt;? value(&quot;plancode_pattern&quot;) ?>)
  20        &lt;? else ?>
  21          text('All Planner Codes')
  22        &lt;? endif ?>
  23        AS plannercode,
  24        &lt;? if exists(&quot;includePlannedOrders&quot;) ?>
  25          formatBoolYN(true)
  26        &lt;? else ?>
  27          formatBoolYN(false)
  28        &lt;? endif ?>
  29        AS includeplnord,
  30        &lt;? if exists(&quot;warehous_id&quot;) ?>
  31          ( SELECT warehous_code
  32              FROM whsinfo
  33             WHERE (warehous_id=&lt;? value(&quot;warehous_id&quot;) ?>) )
  34        &lt;? else ?>
  35          text('All Sites')
  36        &lt;? endif ?>
  37        AS warehouse;</sql>
  38  </querysource>
  39  <querysource>
  40   <name>detail</name>
  41   <sql>SELECT warehous_code, item_number, item_descrip1, item_descrip2,
  42        formatDate(reorderdate) AS f_reorderdate,
  43        formatQty(reorderlevel) AS f_reorderlevel,
  44        formatQty((itemsite_qtyonhand - qtyAllocated(itemsite_id, reorderdate) + qtyOrdered(itemsite_id, reorderdate))) AS f_projavail,
  45        reorderdate
  46   FROM ( SELECT itemsite_id,
  47                 CASE WHEN (item_type IN ('M', 'B', 'T')) THEN 1
  48                      WHEN (item_type IN ('P', 'O')) THEN 2
  49                      ELSE 3
  50                 END AS itemtype,
  51                 warehous_code, item_number, item_descrip1, item_descrip2,
  52                 reorderDate(itemsite_id, &lt;? value(&quot;lookAheadDays&quot;) ?>, &lt;? if exists(&quot;includePlannedOrder&quot;) ?>true&lt;? else ?>false&lt;? endif ?>) AS reorderdate, itemsite_qtyonhand,
  53                 reorderlevel
  54            FROM ( SELECT itemsite_id, itemsite_item_id, itemsite_warehous_id,
  55                          itemsite_qtyonhand,
  56                          CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel
  57                     FROM itemsite
  58                    WHERE ((true)
  59 &lt;? if exists(&quot;warehous_id&quot;) ?>
  60                      AND (itemsite_warehous_id=&lt;? value(&quot;warehous_id&quot;) ?>)
  61 &lt;? endif ?>
  62 &lt;? if exists(&quot;plancode_id&quot;) ?>
  63                      AND (itemsite_plancode_id=&lt;? value(&quot;plancode_id&quot;) ?>)
  64 &lt;? elseif exists(&quot;plancode_pattern&quot;) ?>
  65                      AND (itemsite_plancode_id IN (SELECT plancode_id
  66                                                      FROM plancode
  67                                                     WHERE (plancode_code ~ &lt;? value(&quot;plancode_pattern&quot;) ?>)))
  68 &lt;? endif ?>
  69                          )
  70                 ) AS itemsitedate, item, whsinfo
  71           WHERE ((itemsite_item_id=item_id)
  72             AND (itemsite_warehous_id=warehous_id))
  73        ) AS data
  74  WHERE (reorderdate IS NOT NULL)
  75 ORDER BY reorderdate, item_number;</sql>
  76  </querysource>
  77  <rpthead>
  78   <height>221</height>
  79   <label>
  80    <rect>
  81     <x>385</x>
  82     <y>185</y>
  83     <width>100</width>
  84     <height>15</height>
  85    </rect>
  86    <font>
  87     <face>Arial</face>
  88     <size>8</size>
  89     <weight>normal</weight>
  90    </font>
  91    <hcenter/>
  92    <vcenter/>
  93    <string>Exception Date</string>
  94   </label>
  95   <label>
  96    <rect>
  97     <x>50</x>
  98     <y>200</y>
  99     <width>106</width>
 100     <height>15</height>
 101    </rect>
 102    <font>
 103     <face>Arial</face>
 104     <size>8</size>
 105     <weight>normal</weight>
 106    </font>
 107    <left/>
 108    <vcenter/>
 109    <string>Description</string>
 110   </label>
 111   <label>
 112    <rect>
 113     <x>490</x>
 114     <y>185</y>
 115     <width>100</width>
 116     <height>15</height>
 117    </rect>
 118    <font>
 119     <face>Arial</face>
 120     <size>8</size>
 121     <weight>normal</weight>
 122    </font>
 123    <hcenter/>
 124    <vcenter/>
 125    <string>Reorder Level</string>
 126   </label>
 127   <label>
 128    <rect>
 129     <x>345</x>
 130     <y>80</y>
 131     <width>175</width>
 132     <height>20</height>
 133    </rect>
 134    <font>
 135     <face>Arial</face>
 136     <size>10</size>
 137     <weight>normal</weight>
 138    </font>
 139    <right/>
 140    <vcenter/>
 141    <string>Include Planned Orders:</string>
 142   </label>
 143   <label>
 144    <rect>
 145     <x>240</x>
 146     <y>10</y>
 147     <width>484</width>
 148     <height>38</height>
 149    </rect>
 150    <font>
 151     <face>Arial</face>
 152     <size>18</size>
 153     <weight>bold</weight>
 154    </font>
 155    <right/>
 156    <vcenter/>
 157    <string>Reorder Exceptions by Planner Code</string>
 158   </label>
 159   <label>
 160    <rect>
 161     <x>30</x>
 162     <y>100</y>
 163     <width>105</width>
 164     <height>20</height>
 165    </rect>
 166    <font>
 167     <face>Arial</face>
 168     <size>10</size>
 169     <weight>normal</weight>
 170    </font>
 171    <right/>
 172    <vcenter/>
 173    <string>Site:</string>
 174   </label>
 175   <field>
 176    <rect>
 177     <x>140</x>
 178     <y>120</y>
 179     <width>200</width>
 180     <height>20</height>
 181    </rect>
 182    <font>
 183     <face>Arial</face>
 184     <size>10</size>
 185     <weight>bold</weight>
 186    </font>
 187    <left/>
 188    <vcenter/>
 189    <data>
 190     <query>Parameter Query</query>
 191     <column>lookAheadDays</column>
 192    </data>
 193   </field>
 194   <label>
 195    <rect>
 196     <x>595</x>
 197     <y>185</y>
 198     <width>100</width>
 199     <height>15</height>
 200    </rect>
 201    <font>
 202     <face>Arial</face>
 203     <size>8</size>
 204     <weight>normal</weight>
 205    </font>
 206    <hcenter/>
 207    <vcenter/>
 208    <string>Proj. Avail.</string>
 209   </label>
 210   <line>
 211    <xstart>5</xstart>
 212    <ystart>215</ystart>
 213    <xend>745</xend>
 214    <yend>215</yend>
 215    <weight>2</weight>
 216   </line>
 217   <label>
 218    <rect>
 219     <x>50</x>
 220     <y>185</y>
 221     <width>106</width>
 222     <height>15</height>
 223    </rect>
 224    <font>
 225     <face>Arial</face>
 226     <size>8</size>
 227     <weight>normal</weight>
 228    </font>
 229    <left/>
 230    <vcenter/>
 231    <string>Item Number</string>
 232   </label>
 233   <label>
 234    <rect>
 235     <x>5</x>
 236     <y>185</y>
 237     <width>35</width>
 238     <height>15</height>
 239    </rect>
 240    <font>
 241     <face>Arial</face>
 242     <size>8</size>
 243     <weight>normal</weight>
 244    </font>
 245    <hcenter/>
 246    <vcenter/>
 247    <string>Site</string>
 248   </label>
 249   <field>
 250    <rect>
 251     <x>525</x>
 252     <y>80</y>
 253     <width>200</width>
 254     <height>20</height>
 255    </rect>
 256    <font>
 257     <face>Arial</face>
 258     <size>10</size>
 259     <weight>bold</weight>
 260    </font>
 261    <left/>
 262    <vcenter/>
 263    <data>
 264     <query>head</query>
 265     <column>includeplnord</column>
 266    </data>
 267   </field>
 268   <field>
 269    <rect>
 270     <x>140</x>
 271     <y>100</y>
 272     <width>200</width>
 273     <height>20</height>
 274    </rect>
 275    <font>
 276     <face>Arial</face>
 277     <size>10</size>
 278     <weight>bold</weight>
 279    </font>
 280    <left/>
 281    <vcenter/>
 282    <data>
 283     <query>head</query>
 284     <column>warehouse</column>
 285    </data>
 286   </field>
 287   <field>
 288    <rect>
 289     <x>140</x>
 290     <y>80</y>
 291     <width>385</width>
 292     <height>20</height>
 293    </rect>
 294    <font>
 295     <face>Arial</face>
 296     <size>10</size>
 297     <weight>bold</weight>
 298    </font>
 299    <left/>
 300    <vcenter/>
 301    <data>
 302     <query>head</query>
 303     <column>plannercode</column>
 304    </data>
 305   </field>
 306   <label>
 307    <rect>
 308     <x>5</x>
 309     <y>120</y>
 310     <width>130</width>
 311     <height>20</height>
 312    </rect>
 313    <font>
 314     <face>Arial</face>
 315     <size>10</size>
 316     <weight>normal</weight>
 317    </font>
 318    <right/>
 319    <vcenter/>
 320    <string>Look Ahead Days:</string>
 321   </label>
 322   <label>
 323    <rect>
 324     <x>30</x>
 325     <y>80</y>
 326     <width>105</width>
 327     <height>20</height>
 328    </rect>
 329    <font>
 330     <face>Arial</face>
 331     <size>10</size>
 332     <weight>normal</weight>
 333    </font>
 334    <right/>
 335    <vcenter/>
 336    <string>Planner Code(s):</string>
 337   </label>
 338  </rpthead>
 339  <pghead>
 340   <firstpage/>
 341   <height>6</height>
 342  </pghead>
 343  <pghead>
 344   <height>41</height>
 345   <label>
 346    <rect>
 347     <x>50</x>
 348     <y>5</y>
 349     <width>106</width>
 350     <height>15</height>
 351    </rect>
 352    <font>
 353     <face>Arial</face>
 354     <size>8</size>
 355     <weight>normal</weight>
 356    </font>
 357    <left/>
 358    <vcenter/>
 359    <string>Item Number</string>
 360   </label>
 361   <label>
 362    <rect>
 363     <x>595</x>
 364     <y>5</y>
 365     <width>100</width>
 366     <height>15</height>
 367    </rect>
 368    <font>
 369     <face>Arial</face>
 370     <size>8</size>
 371     <weight>normal</weight>
 372    </font>
 373    <hcenter/>
 374    <vcenter/>
 375    <string>Proj. Avail.</string>
 376   </label>
 377   <line>
 378    <xstart>5</xstart>
 379    <ystart>35</ystart>
 380    <xend>745</xend>
 381    <yend>35</yend>
 382    <weight>2</weight>
 383   </line>
 384   <label>
 385    <rect>
 386     <x>490</x>
 387     <y>5</y>
 388     <width>100</width>
 389     <height>15</height>
 390    </rect>
 391    <font>
 392     <face>Arial</face>
 393     <size>8</size>
 394     <weight>normal</weight>
 395    </font>
 396    <hcenter/>
 397    <vcenter/>
 398    <string>Reorder Level</string>
 399   </label>
 400   <label>
 401    <rect>
 402     <x>385</x>
 403     <y>5</y>
 404     <width>100</width>
 405     <height>15</height>
 406    </rect>
 407    <font>
 408     <face>Arial</face>
 409     <size>8</size>
 410     <weight>normal</weight>
 411    </font>
 412    <hcenter/>
 413    <vcenter/>
 414    <string>Exception Date</string>
 415   </label>
 416   <label>
 417    <rect>
 418     <x>50</x>
 419     <y>20</y>
 420     <width>106</width>
 421     <height>15</height>
 422    </rect>
 423    <font>
 424     <face>Arial</face>
 425     <size>8</size>
 426     <weight>normal</weight>
 427    </font>
 428    <left/>
 429    <vcenter/>
 430    <string>Description</string>
 431   </label>
 432   <label>
 433    <rect>
 434     <x>5</x>
 435     <y>5</y>
 436     <width>35</width>
 437     <height>15</height>
 438    </rect>
 439    <font>
 440     <face>Arial</face>
 441     <size>8</size>
 442     <weight>normal</weight>
 443    </font>
 444    <hcenter/>
 445    <vcenter/>
 446    <string>Site</string>
 447   </label>
 448  </pghead>
 449  <section>
 450   <name>detail</name>
 451   <detail>
 452    <key>
 453     <query>detail</query>
 454    </key>
 455    <height>51</height>
 456    <line>
 457     <xstart>5</xstart>
 458     <ystart>45</ystart>
 459     <xend>745</xend>
 460     <yend>45</yend>
 461     <weight>0</weight>
 462    </line>
 463    <field>
 464     <rect>
 465      <x>5</x>
 466      <y>0</y>
 467      <width>35</width>
 468      <height>15</height>
 469     </rect>
 470     <font>
 471      <face>Arial</face>
 472      <size>8</size>
 473      <weight>bold</weight>
 474     </font>
 475     <hcenter/>
 476     <vcenter/>
 477     <data>
 478      <query>detail</query>
 479      <column>warehous_code</column>
 480     </data>
 481    </field>
 482    <field>
 483     <rect>
 484      <x>50</x>
 485      <y>30</y>
 486      <width>300</width>
 487      <height>15</height>
 488     </rect>
 489     <font>
 490      <face>Arial</face>
 491      <size>8</size>
 492      <weight>bold</weight>
 493     </font>
 494     <left/>
 495     <vcenter/>
 496     <data>
 497      <query>detail</query>
 498      <column>item_descrip2</column>
 499     </data>
 500    </field>
 501    <field>
 502     <rect>
 503      <x>385</x>
 504      <y>0</y>
 505      <width>100</width>
 506      <height>15</height>
 507     </rect>
 508     <font>
 509      <face>Arial</face>
 510      <size>8</size>
 511      <weight>bold</weight>
 512     </font>
 513     <hcenter/>
 514     <vcenter/>
 515     <data>
 516      <query>detail</query>
 517      <column>f_reorderdate</column>
 518     </data>
 519    </field>
 520    <field>
 521     <rect>
 522      <x>490</x>
 523      <y>0</y>
 524      <width>100</width>
 525      <height>15</height>
 526     </rect>
 527     <font>
 528      <face>Arial</face>
 529      <size>8</size>
 530      <weight>bold</weight>
 531     </font>
 532     <right/>
 533     <vcenter/>
 534     <data>
 535      <query>detail</query>
 536      <column>f_reorderlevel</column>
 537     </data>
 538    </field>
 539    <field>
 540     <rect>
 541      <x>595</x>
 542      <y>0</y>
 543      <width>100</width>
 544      <height>15</height>
 545     </rect>
 546     <font>
 547      <face>Arial</face>
 548      <size>8</size>
 549      <weight>bold</weight>
 550     </font>
 551     <right/>
 552     <vcenter/>
 553     <data>
 554      <query>detail</query>
 555      <column>f_projavail</column>
 556     </data>
 557    </field>
 558    <field>
 559     <rect>
 560      <x>50</x>
 561      <y>0</y>
 562      <width>250</width>
 563      <height>15</height>
 564     </rect>
 565     <font>
 566      <face>Arial</face>
 567      <size>8</size>
 568      <weight>bold</weight>
 569     </font>
 570     <left/>
 571     <vcenter/>
 572     <data>
 573      <query>detail</query>
 574      <column>item_number</column>
 575     </data>
 576    </field>
 577    <field>
 578     <rect>
 579      <x>50</x>
 580      <y>15</y>
 581      <width>300</width>
 582      <height>15</height>
 583     </rect>
 584     <font>
 585      <face>Arial</face>
 586      <size>8</size>
 587      <weight>bold</weight>
 588     </font>
 589     <left/>
 590     <vcenter/>
 591     <data>
 592      <query>detail</query>
 593      <column>item_descrip1</column>
 594     </data>
 595    </field>
 596   </detail>
 597  </section>
 598  <pgfoot>
 599   <height>16</height>
 600   <label>
 601    <rect>
 602     <x>615</x>
 603     <y>0</y>
 604     <width>85</width>
 605     <height>15</height>
 606    </rect>
 607    <font>
 608     <face>Arial</face>
 609     <size>8</size>
 610     <weight>normal</weight>
 611    </font>
 612    <right/>
 613    <vcenter/>
 614    <string>Page:</string>
 615   </label>
 616   <label>
 617    <rect>
 618     <x>0</x>
 619     <y>0</y>
 620     <width>85</width>
 621     <height>15</height>
 622    </rect>
 623    <font>
 624     <face>Arial</face>
 625     <size>8</size>
 626     <weight>normal</weight>
 627    </font>
 628    <right/>
 629    <vcenter/>
 630    <string>Report Date:</string>
 631   </label>
 632   <field>
 633    <rect>
 634     <x>705</x>
 635     <y>0</y>
 636     <width>40</width>
 637     <height>15</height>
 638    </rect>
 639    <font>
 640     <face>Arial</face>
 641     <size>8</size>
 642     <weight>bold</weight>
 643    </font>
 644    <left/>
 645    <vcenter/>
 646    <data>
 647     <query>Context Query</query>
 648     <column>page_number</column>
 649    </data>
 650   </field>
 651   <field>
 652    <rect>
 653     <x>90</x>
 654     <y>0</y>
 655     <width>100</width>
 656     <height>15</height>
 657    </rect>
 658    <font>
 659     <face>Arial</face>
 660     <size>8</size>
 661     <weight>bold</weight>
 662    </font>
 663    <left/>
 664    <vcenter/>
 665    <data>
 666     <query>Context Query</query>
 667     <column>report_date</column>
 668    </data>
 669   </field>
 670  </pgfoot>
 671 </report>