Edit Report:Orders Picked by Packed by Invoiced by
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Orders Picked by Packed by Invoiced by
-- PLACE HOLDER REPORT DESCRIPTION
-- PLACE HOLDER REPORT FILTERS AND DRILL DOWN
-- VARIABLE: {
-- name: "orange",
-- display: "Invoice Date",
-- type: "daterange",
-- default: { start: "first day of last month", end: "today" }
-- }
-- VARIABLE: {
-- name: "invoicedby",
-- display: "Invoiced By",
-- type: "text",
-- }
-- VARIABLE: {
-- name: "pickedby",
-- display: "Picked By",
-- type: "text",
-- }
-- VARIABLE: {
-- name: "packedby",
-- display: "Packed By",
-- type: "text",
-- }
-- VARIABLE: {
-- name: "pickedby",
-- display: "Picked By",
-- type: "text",
-- }
-- ROLLUP: {
-- columns: {
-- "NUMBER OF ITEMS ON INVOICE": "Total: {{sum}}",
-- }
-- }
-- ROLLUP: {
-- columns: {
-- "INVOICE NUMBER": "Count: {{count}}",
-- }
-- }
-- ROLLUP: {
-- columns: {
-- "INVOICE AMT": "Inv Amt: {{sum}}",
-- }
-- }
SELECT
d.transno AS 'INVOICE NUMBER',
d. order_ AS 'PICK SLIP NUMBER',
sa.deliverto AS 'Salon Name',
d.inputdate AS 'INVOICE DATE',
s.userid AS 'INVOICED BY',
UPPER( pickedby ) AS 'PICKED BY',
UPPER( packedby ) AS 'PACKED BY',
sum( s.qty * - 1 ) AS 'NUMBER OF ITEMS ON INVOICE',
d.ovamount AS 'INVOICE AMT'
FROM
debtortrans d
INNER JOIN stockmoves s ON s.type = d.type AND s.transno = d.transno
INNER JOIN salesorders sa ON sa.orderno = d.order_
join shippers sh on sa.shipvia= sh.shipper_id
WHERE
d.type in ('10')
and s.loccode = '00'
{% if salesman is not empty %}
and d.salesperson in (
{% for person in salesman %}
{% if person != '' %}
'{{ person }}'
{{ not loop.last ? ',' }}
{% else %}
select salesmancode from salesman
{% endif %}
{% endfor %})
{% endif %}
AND d.trandate BETWEEN '{{ orange.start }}' AND '{{ orange.end }}'
AND UPPER(s.userid) like '{{invoicedby | upper }}%'
AND UPPER( pickedby ) like '{{ pickedby | upper }}%'
AND UPPER( packedby) like '{{ packedby | upper }}%'
GROUP BY
d.transno