Edit Report:Back Order Fill Report
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Back Order Fill Report
-- Back Order Fill Report
--
-- VARIABLE: {
-- name: "supplierno",
-- display: "Vendor",
-- type: "select",
-- database_options: { table: "suppliers", display: "suppname", column: "supplierid", order:{ column: "suppname" , order: "ASC"} },
-- multiple:true,
-- }
-- VARIABLE: {
-- name: "zeroqoh",
-- display: "Exclude Zero On Hand",
-- type: "select",
-- multiple: false,
-- options: [{display: 'Include', value:'includeqoh'},{display: 'Exclude', value:'exludeqoh'},{display: 'Fillable', value:'fillonly'}],
-- }
-- ROLLUP: {
-- columns: {
-- "OUTSTANDING": "Total: {{sum}}",
-- "ORDERED": "Total: {{sum}}",
-- "INVOICED": "Total: {{sum}}",
-- "NETTOTAL": "Total: {{sum}}",
-- }
-- }
-- FILTER: {
-- filter: "class",
-- column: "OUTSTANDING",
-- params: {
-- class: "center"
-- }
-- }
-- FILTER: {
-- filter: "class",
-- column: "ORDERED",
-- params: {
-- class: "center"
-- }
-- }
-- FILTER: {
-- filter: "class",
-- column: "INVOICED",
-- params: {
-- class: "center"
-- }
-- }
-- FILTER: {
-- filter: "twig",
-- column: "ORDERNO",
-- params: {
-- template: "<a href='/SelectOrderItems.php?ModifyOrderNumber={{ value }}' target='_blank'>{{ value }}</a>"
-- }
-- }
-- FILTER: {
-- filter: "html",
-- column: "ORDERNO"
--}
-- FILTER: {
-- filter: "twig",
-- column: "DISCOUNT",
-- params: {
-- template: "{{ value }}%"
-- }
-- }
-- FILTER: {
-- filter: "twig",
-- column: "PRICE",
-- params: {
-- template: "${{ value | number_format(2, '.', ',')}}"
-- }
-- }
-- FILTER: {
-- filter: "twig",
-- column: "NETTOTAL",
-- params: {
-- template: "{{ value | number_format(2, '.', '')}}"
-- }
-- }
-- FILTER: {
-- filter: "class",
-- column: "DISCOUNT",
-- params: {
-- class: "center"
-- }
-- }
-- FILTER: {
-- filter: "class",
-- column: "QOH",
-- params: {
-- class: "center"
-- }
-- }
-- FILTER: {
-- filter: "class",
-- column: "PRICE",
-- params: {
-- class: "right"
-- }
-- }
-- FILTER: {
-- filter: "class",
-- column: "NETTOTAL",
-- params: {
-- class: "right"
-- }
-- }
select
p.supplierno AS "SUPPLIER",
so.orddate as 'DATE',
so.orderno as 'ORDERNO',
debtorno as 'CUSTOMER',
so.deliverto AS 'NAME',
so.contactphone AS 'PHONE',
sa.salesmanname as 'SALESPERSON',
stkcode as ITEMCODE,
s.description as DESCRIPTION,
(quantity - qtyinvoiced) as OUTSTANDING ,
unitprice as 'PRICE',
(discountpercent*100) as 'DISCOUNT',
CAST(((unitprice - unitprice * discountpercent ) * (quantity - qtyinvoiced)) AS DECIMAL(10,2)) as NETTOTAL,
(select sum(quantity) from locstock where loccode = so.fromstkloc and stockid =stkcode) as QOH
from
salesorderdetails
LEFT JOIN salesorders so ON so.orderno = salesorderdetails.orderno
LEFT JOIN stockmaster s ON s.stockid = salesorderdetails.stkcode
INNER JOIN purchdata p ON s.stockid = p.stockid AND p.preferred = 1
JOIN salesman sa ON sa.salesmancode = so.salesperson
where
completed = 0
and quantity <> qtyinvoiced
and stkcode not like 'SUAA%'
and debtorno <> ''
and fromstkloc = '00'
{% if zeroqoh == 'exludeqoh' %}
having QOH > 0
{% endif %}
{% if zeroqoh == 'fillonly' %}
having QOH > OUTSTANDING
{% endif %}
{% if supplierno|length > 0 %}
and p.supplierno in (
{% for supp in supplierno %}
'{{ supp }}'
{{ not loop.last ? ',' }}
{% endfor %})
{% endif %}
order by debtorno asc, stkcode asc, orddate desc