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