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

   1 <!DOCTYPE openRPTDef>
   2 <report>
   3  <title>Substitue Availability By Root Item</title>
   4  <name>SubstituteAvailabilityByRootItem</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 item_number,
  15        item_descrip1,
  16        item_descrip2,
  17        &lt;? if exists(&quot;warehous_id&quot;) ?>
  18          ( SELECT warehous_code
  19              FROM whsinfo
  20             WHERE (warehous_id=&lt;? value(&quot;warehous_id&quot;) ?>) )
  21        &lt;? else ?>
  22          text('All Sites')
  23        &lt;? endif ?>
  24        AS warehouse
  25   FROM item
  26  WHERE (item_id=&lt;? value(&quot;item_id&quot;) ?>);</sql>
  27  </querysource>
  28  <querysource>
  29   <name>detail</name>
  30   <sql>SELECT warehous_code,
  31        item_number,
  32        item_descrip1,
  33        item_descrip2,
  34        formatQty(qtyAvailable(sub.itemsite_id)) AS f_qtyonhand,
  35        formatQty(CASE WHEN(sub.itemsite_useparams) THEN sub.itemsite_reorderlevel ELSE 0.0 END) AS f_reorderlevel,
  36        sub.itemsite_leadtime as leadtime,
  37 &lt;? if exists(&quot;byDays&quot;) ?>
  38        formatQty(qtyAllocated(sub.itemsite_id, &lt;? value(&quot;byDays&quot;) ?>)) AS f_allocated,
  39        formatQty(qtyOrdered(sub.itemsite_id, &lt;? value(&quot;byDays&quot;) ?>)) AS f_ordered,
  40        formatQty(qtyAvailable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id, &lt;? value(&quot;byDays&quot;) ?>) - qtyAllocated(sub.itemsite_id, &lt;? value(&quot;byDays&quot;) ?>)) as f_avail
  41 &lt;? elseif exists(&quot;byDate&quot;) ?>
  42        formatQty(qtyAllocated(sub.itemsite_id, (&lt;? value(&quot;byDate&quot;) ?> - CURRENT_DATE))) AS f_allocated,
  43        formatQty(qtyOrdered(sub.itemsite_id, (&lt;? value(&quot;byDate&quot;) ?> - CURRENT_DATE))) AS f_ordered,
  44        formatQty(qtyAvailable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id, (&lt;? value(&quot;byDate&quot;) ?> - CURRENT_DATE)) - qtyAllocated(sub.itemsite_id, (&lt;? value(&quot;byDate&quot;) ?> - CURRENT_DATE))) as f_avail
  45 &lt;? else ?>
  46        formatQty(qtyAllocated(sub.itemsite_id, sub.itemsite_leadtime)) AS f_allocated,
  47        formatQty(qtyOrdered(sub.itemsite_id, sub.itemsite_leadtime)) AS f_ordered,
  48        formatQty(qtyAvailable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id, sub.itemsite_leadtime) - qtyAllocated(sub.itemsite_id, sub.itemsite_leadtime)) as f_avail
  49 &lt;? endif ?>
  50   FROM item, itemsite AS sub, itemsite AS root, whsinfo, itemsub
  51  WHERE ((sub.itemsite_item_id=item_id)
  52    AND (root.itemsite_item_id=itemsub_parent_item_id)
  53    AND (sub.itemsite_item_id=itemsub_sub_item_id)
  54    AND (root.itemsite_warehous_id=sub.itemsite_warehous_id)
  55    AND (sub.itemsite_warehous_id=warehous_id)
  56    AND (root.itemsite_item_id=&lt;? value(&quot;item_id&quot;) ?>)
  57 &lt;? if exists(&quot;warehous_id&quot;) ?>
  58    AND (root.itemsite_warehous_id=&lt;? value(&quot;warehous_id&quot;) ?>)
  59 &lt;? endif ?>
  60 )
  61 ORDER BY itemsub_rank;</sql>
  62  </querysource>
  63  <rpthead>
  64   <height>221</height>
  65   <field>
  66    <rect>
  67     <x>140</x>
  68     <y>140</y>
  69     <width>200</width>
  70     <height>20</height>
  71    </rect>
  72    <font>
  73     <face>Arial</face>
  74     <size>10</size>
  75     <weight>bold</weight>
  76    </font>
  77    <left/>
  78    <vcenter/>
  79    <data>
  80     <query>head</query>
  81     <column>warehouse</column>
  82    </data>
  83   </field>
  84   <label>
  85    <rect>
  86     <x>65</x>
  87     <y>185</y>
  88     <width>106</width>
  89     <height>15</height>
  90    </rect>
  91    <font>
  92     <face>Arial</face>
  93     <size>8</size>
  94     <weight>normal</weight>
  95    </font>
  96    <left/>
  97    <vcenter/>
  98    <string>Item Number</string>
  99   </label>
 100   <label>
 101    <rect>
 102     <x>10</x>
 103     <y>200</y>
 104     <width>35</width>
 105     <height>15</height>
 106    </rect>
 107    <font>
 108     <face>Arial</face>
 109     <size>8</size>
 110     <weight>normal</weight>
 111    </font>
 112    <hcenter/>
 113    <vcenter/>
 114    <string>LT</string>
 115   </label>
 116   <label>
 117    <rect>
 118     <x>275</x>
 119     <y>10</y>
 120     <width>450</width>
 121     <height>38</height>
 122    </rect>
 123    <font>
 124     <face>Arial</face>
 125     <size>18</size>
 126     <weight>bold</weight>
 127    </font>
 128    <right/>
 129    <vcenter/>
 130    <string>Substitute Availability By Root Item</string>
 131   </label>
 132   <field>
 133    <rect>
 134     <x>140</x>
 135     <y>100</y>
 136     <width>385</width>
 137     <height>20</height>
 138    </rect>
 139    <font>
 140     <face>Arial</face>
 141     <size>10</size>
 142     <weight>bold</weight>
 143    </font>
 144    <left/>
 145    <vcenter/>
 146    <data>
 147     <query>head</query>
 148     <column>item_descrip1</column>
 149    </data>
 150   </field>
 151   <label>
 152    <rect>
 153     <x>540</x>
 154     <y>200</y>
 155     <width>100</width>
 156     <height>15</height>
 157    </rect>
 158    <font>
 159     <face>Arial</face>
 160     <size>8</size>
 161     <weight>normal</weight>
 162    </font>
 163    <hcenter/>
 164    <vcenter/>
 165    <string>Reorder Level</string>
 166   </label>
 167   <field>
 168    <rect>
 169     <x>140</x>
 170     <y>120</y>
 171     <width>385</width>
 172     <height>20</height>
 173    </rect>
 174    <font>
 175     <face>Arial</face>
 176     <size>10</size>
 177     <weight>bold</weight>
 178    </font>
 179    <left/>
 180    <vcenter/>
 181    <data>
 182     <query>head</query>
 183     <column>item_descrip2</column>
 184    </data>
 185   </field>
 186   <line>
 187    <xstart>5</xstart>
 188    <ystart>215</ystart>
 189    <xend>745</xend>
 190    <yend>215</yend>
 191    <weight>2</weight>
 192   </line>
 193   <label>
 194    <rect>
 195     <x>434</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>Allocated</string>
 208   </label>
 209   <field>
 210    <rect>
 211     <x>140</x>
 212     <y>80</y>
 213     <width>385</width>
 214     <height>20</height>
 215    </rect>
 216    <font>
 217     <face>Arial</face>
 218     <size>10</size>
 219     <weight>bold</weight>
 220    </font>
 221    <left/>
 222    <vcenter/>
 223    <data>
 224     <query>head</query>
 225     <column>item_number</column>
 226    </data>
 227   </field>
 228   <label>
 229    <rect>
 230     <x>645</x>
 231     <y>185</y>
 232     <width>100</width>
 233     <height>15</height>
 234    </rect>
 235    <font>
 236     <face>Arial</face>
 237     <size>8</size>
 238     <weight>normal</weight>
 239    </font>
 240    <hcenter/>
 241    <vcenter/>
 242    <string>Available</string>
 243   </label>
 244   <label>
 245    <rect>
 246     <x>10</x>
 247     <y>185</y>
 248     <width>35</width>
 249     <height>15</height>
 250    </rect>
 251    <font>
 252     <face>Arial</face>
 253     <size>8</size>
 254     <weight>normal</weight>
 255    </font>
 256    <hcenter/>
 257    <vcenter/>
 258    <string>Site</string>
 259   </label>
 260   <label>
 261    <rect>
 262     <x>65</x>
 263     <y>200</y>
 264     <width>106</width>
 265     <height>15</height>
 266    </rect>
 267    <font>
 268     <face>Arial</face>
 269     <size>8</size>
 270     <weight>normal</weight>
 271    </font>
 272    <left/>
 273    <vcenter/>
 274    <string>Description</string>
 275   </label>
 276   <label>
 277    <rect>
 278     <x>330</x>
 279     <y>185</y>
 280     <width>100</width>
 281     <height>15</height>
 282    </rect>
 283    <font>
 284     <face>Arial</face>
 285     <size>8</size>
 286     <weight>normal</weight>
 287    </font>
 288    <hcenter/>
 289    <vcenter/>
 290    <string>QOH</string>
 291   </label>
 292   <label>
 293    <rect>
 294     <x>540</x>
 295     <y>185</y>
 296     <width>100</width>
 297     <height>15</height>
 298    </rect>
 299    <font>
 300     <face>Arial</face>
 301     <size>8</size>
 302     <weight>normal</weight>
 303    </font>
 304    <hcenter/>
 305    <vcenter/>
 306    <string>On Order</string>
 307   </label>
 308   <label>
 309    <rect>
 310     <x>30</x>
 311     <y>140</y>
 312     <width>105</width>
 313     <height>20</height>
 314    </rect>
 315    <font>
 316     <face>Arial</face>
 317     <size>10</size>
 318     <weight>normal</weight>
 319    </font>
 320    <right/>
 321    <vcenter/>
 322    <string>Site:</string>
 323   </label>
 324   <label>
 325    <rect>
 326     <x>30</x>
 327     <y>80</y>
 328     <width>105</width>
 329     <height>20</height>
 330    </rect>
 331    <font>
 332     <face>Arial</face>
 333     <size>10</size>
 334     <weight>normal</weight>
 335    </font>
 336    <right/>
 337    <vcenter/>
 338    <string>Item Number:</string>
 339   </label>
 340  </rpthead>
 341  <pghead>
 342   <firstpage/>
 343   <height>6</height>
 344  </pghead>
 345  <pghead>
 346   <height>41</height>
 347   <label>
 348    <rect>
 349     <x>65</x>
 350     <y>20</y>
 351     <width>106</width>
 352     <height>15</height>
 353    </rect>
 354    <font>
 355     <face>Arial</face>
 356     <size>8</size>
 357     <weight>normal</weight>
 358    </font>
 359    <left/>
 360    <vcenter/>
 361    <string>Description</string>
 362   </label>
 363   <line>
 364    <xstart>5</xstart>
 365    <ystart>35</ystart>
 366    <xend>745</xend>
 367    <yend>35</yend>
 368    <weight>2</weight>
 369   </line>
 370   <label>
 371    <rect>
 372     <x>434</x>
 373     <y>5</y>
 374     <width>100</width>
 375     <height>15</height>
 376    </rect>
 377    <font>
 378     <face>Arial</face>
 379     <size>8</size>
 380     <weight>normal</weight>
 381    </font>
 382    <hcenter/>
 383    <vcenter/>
 384    <string>Allocated</string>
 385   </label>
 386   <label>
 387    <rect>
 388     <x>10</x>
 389     <y>5</y>
 390     <width>35</width>
 391     <height>15</height>
 392    </rect>
 393    <font>
 394     <face>Arial</face>
 395     <size>8</size>
 396     <weight>normal</weight>
 397    </font>
 398    <hcenter/>
 399    <vcenter/>
 400    <string>Site</string>
 401   </label>
 402   <label>
 403    <rect>
 404     <x>65</x>
 405     <y>5</y>
 406     <width>106</width>
 407     <height>15</height>
 408    </rect>
 409    <font>
 410     <face>Arial</face>
 411     <size>8</size>
 412     <weight>normal</weight>
 413    </font>
 414    <left/>
 415    <vcenter/>
 416    <string>Item Number</string>
 417   </label>
 418   <label>
 419    <rect>
 420     <x>540</x>
 421     <y>20</y>
 422     <width>100</width>
 423     <height>15</height>
 424    </rect>
 425    <font>
 426     <face>Arial</face>
 427     <size>8</size>
 428     <weight>normal</weight>
 429    </font>
 430    <hcenter/>
 431    <vcenter/>
 432    <string>Reorder Level</string>
 433   </label>
 434   <label>
 435    <rect>
 436     <x>330</x>
 437     <y>5</y>
 438     <width>100</width>
 439     <height>15</height>
 440    </rect>
 441    <font>
 442     <face>Arial</face>
 443     <size>8</size>
 444     <weight>normal</weight>
 445    </font>
 446    <hcenter/>
 447    <vcenter/>
 448    <string>QOH</string>
 449   </label>
 450   <label>
 451    <rect>
 452     <x>10</x>
 453     <y>20</y>
 454     <width>35</width>
 455     <height>15</height>
 456    </rect>
 457    <font>
 458     <face>Arial</face>
 459     <size>8</size>
 460     <weight>normal</weight>
 461    </font>
 462    <hcenter/>
 463    <vcenter/>
 464    <string>LT</string>
 465   </label>
 466   <label>
 467    <rect>
 468     <x>645</x>
 469     <y>5</y>
 470     <width>100</width>
 471     <height>15</height>
 472    </rect>
 473    <font>
 474     <face>Arial</face>
 475     <size>8</size>
 476     <weight>normal</weight>
 477    </font>
 478    <hcenter/>
 479    <vcenter/>
 480    <string>Available</string>
 481   </label>
 482   <label>
 483    <rect>
 484     <x>540</x>
 485     <y>5</y>
 486     <width>100</width>
 487     <height>15</height>
 488    </rect>
 489    <font>
 490     <face>Arial</face>
 491     <size>8</size>
 492     <weight>normal</weight>
 493    </font>
 494    <hcenter/>
 495    <vcenter/>
 496    <string>On Order</string>
 497   </label>
 498  </pghead>
 499  <section>
 500   <name>detail</name>
 501   <detail>
 502    <key>
 503     <query>detail</query>
 504    </key>
 505    <height>56</height>
 506    <field>
 507     <rect>
 508      <x>65</x>
 509      <y>30</y>
 510      <width>250</width>
 511      <height>15</height>
 512     </rect>
 513     <font>
 514      <face>Arial</face>
 515      <size>8</size>
 516      <weight>bold</weight>
 517     </font>
 518     <left/>
 519     <vcenter/>
 520     <data>
 521      <query>detail</query>
 522      <column>item_descrip2</column>
 523     </data>
 524    </field>
 525    <field>
 526     <rect>
 527      <x>65</x>
 528      <y>15</y>
 529      <width>250</width>
 530      <height>15</height>
 531     </rect>
 532     <font>
 533      <face>Arial</face>
 534      <size>8</size>
 535      <weight>bold</weight>
 536     </font>
 537     <left/>
 538     <vcenter/>
 539     <data>
 540      <query>detail</query>
 541      <column>item_descrip1</column>
 542     </data>
 543    </field>
 544    <field>
 545     <rect>
 546      <x>10</x>
 547      <y>0</y>
 548      <width>35</width>
 549      <height>15</height>
 550     </rect>
 551     <font>
 552      <face>Arial</face>
 553      <size>8</size>
 554      <weight>bold</weight>
 555     </font>
 556     <hcenter/>
 557     <vcenter/>
 558     <data>
 559      <query>detail</query>
 560      <column>warehous_code</column>
 561     </data>
 562    </field>
 563    <field>
 564     <rect>
 565      <x>330</x>
 566      <y>0</y>
 567      <width>100</width>
 568      <height>15</height>
 569     </rect>
 570     <font>
 571      <face>Arial</face>
 572      <size>8</size>
 573      <weight>bold</weight>
 574     </font>
 575     <right/>
 576     <vcenter/>
 577     <data>
 578      <query>detail</query>
 579      <column>f_qtyonhand</column>
 580     </data>
 581    </field>
 582    <field>
 583     <rect>
 584      <x>10</x>
 585      <y>15</y>
 586      <width>35</width>
 587      <height>15</height>
 588     </rect>
 589     <font>
 590      <face>Arial</face>
 591      <size>8</size>
 592      <weight>bold</weight>
 593     </font>
 594     <hcenter/>
 595     <vcenter/>
 596     <data>
 597      <query>detail</query>
 598      <column>leadtime</column>
 599     </data>
 600    </field>
 601    <field>
 602     <rect>
 603      <x>645</x>
 604      <y>0</y>
 605      <width>100</width>
 606      <height>15</height>
 607     </rect>
 608     <font>
 609      <face>Arial</face>
 610      <size>8</size>
 611      <weight>bold</weight>
 612     </font>
 613     <right/>
 614     <vcenter/>
 615     <data>
 616      <query>detail</query>
 617      <column>f_avail</column>
 618     </data>
 619    </field>
 620    <field>
 621     <rect>
 622      <x>540</x>
 623      <y>0</y>
 624      <width>100</width>
 625      <height>15</height>
 626     </rect>
 627     <font>
 628      <face>Arial</face>
 629      <size>8</size>
 630      <weight>bold</weight>
 631     </font>
 632     <right/>
 633     <vcenter/>
 634     <data>
 635      <query>detail</query>
 636      <column>f_ordered</column>
 637     </data>
 638    </field>
 639    <field>
 640     <rect>
 641      <x>540</x>
 642      <y>15</y>
 643      <width>100</width>
 644      <height>15</height>
 645     </rect>
 646     <font>
 647      <face>Arial</face>
 648      <size>8</size>
 649      <weight>bold</weight>
 650     </font>
 651     <right/>
 652     <vcenter/>
 653     <data>
 654      <query>detail</query>
 655      <column>f_reorderlevel</column>
 656     </data>
 657    </field>
 658    <field>
 659     <rect>
 660      <x>434</x>
 661      <y>0</y>
 662      <width>100</width>
 663      <height>15</height>
 664     </rect>
 665     <font>
 666      <face>Arial</face>
 667      <size>8</size>
 668      <weight>bold</weight>
 669     </font>
 670     <right/>
 671     <vcenter/>
 672     <data>
 673      <query>detail</query>
 674      <column>f_allocated</column>
 675     </data>
 676    </field>
 677    <field>
 678     <rect>
 679      <x>65</x>
 680      <y>0</y>
 681      <width>250</width>
 682      <height>15</height>
 683     </rect>
 684     <font>
 685      <face>Arial</face>
 686      <size>8</size>
 687      <weight>bold</weight>
 688     </font>
 689     <left/>
 690     <vcenter/>
 691     <data>
 692      <query>detail</query>
 693      <column>item_number</column>
 694     </data>
 695    </field>
 696    <line>
 697     <xstart>5</xstart>
 698     <ystart>50</ystart>
 699     <xend>745</xend>
 700     <yend>50</yend>
 701     <weight>0</weight>
 702    </line>
 703   </detail>
 704  </section>
 705  <pgfoot>
 706   <height>16</height>
 707   <field>
 708    <rect>
 709     <x>90</x>
 710     <y>0</y>
 711     <width>100</width>
 712     <height>15</height>
 713    </rect>
 714    <font>
 715     <face>Arial</face>
 716     <size>8</size>
 717     <weight>bold</weight>
 718    </font>
 719    <left/>
 720    <vcenter/>
 721    <data>
 722     <query>Context Query</query>
 723     <column>report_date</column>
 724    </data>
 725   </field>
 726   <label>
 727    <rect>
 728     <x>615</x>
 729     <y>0</y>
 730     <width>85</width>
 731     <height>15</height>
 732    </rect>
 733    <font>
 734     <face>Arial</face>
 735     <size>8</size>
 736     <weight>normal</weight>
 737    </font>
 738    <right/>
 739    <vcenter/>
 740    <string>Page:</string>
 741   </label>
 742   <label>
 743    <rect>
 744     <x>0</x>
 745     <y>0</y>
 746     <width>85</width>
 747     <height>15</height>
 748    </rect>
 749    <font>
 750     <face>Arial</face>
 751     <size>8</size>
 752     <weight>normal</weight>
 753    </font>
 754    <right/>
 755    <vcenter/>
 756    <string>Report Date:</string>
 757   </label>
 758   <field>
 759    <rect>
 760     <x>705</x>
 761     <y>0</y>
 762     <width>40</width>
 763     <height>15</height>
 764    </rect>
 765    <font>
 766     <face>Arial</face>
 767     <size>8</size>
 768     <weight>bold</weight>
 769    </font>
 770    <left/>
 771    <vcenter/>
 772    <data>
 773     <query>Context Query</query>
 774     <column>page_number</column>
 775    </data>
 776   </field>
 777  </pgfoot>
 778 </report>