SELECT
'In' as "Type",
"Stock In Flow Table"."Stock In Flow ID" as "PK",
"Stock In Flow Table"."Product ID" as "Product ID",
"Stock In Flow Table"."Transaction Date" as "Transaction Date",
"Stock In Flow Table"."Quantity Physically Tracked" as "Physical Quantity",
"Stock In Flow Table"."Warehouse ID" as "Warehouse ID",
"Stock In Flow Table"."Total (BCY)" as "Amount",
0 as "Commited Stock"
FROM "Stock In Flow Table"
WHERE "Stock In Flow Table"."EntityType" != 'transfer_order'
UNION ALL
SELECT
'Out' as "Type",
"Stock Out Flow Table"."Stock Out Flow ID",
"Stock Out Flow Table"."Product ID",
"Stock Out Flow Table"."Transaction Date",
-1 * "Stock Out Flow Table"."Quantity Physically Tracked",
"Stock Out Flow Table"."Warehouse ID" as "Warehouse ID",
-1 * sum("FIFO Mapping Table"."Total (BCY)"),
0
FROM "Stock Out Flow Table"
LEFT JOIN "FIFO Mapping Table" ON "Stock Out Flow Table"."Stock Out Flow ID" = "FIFO Mapping Table"."Stock Out Flow ID"
WHERE "Stock Out Flow Table"."EntityType" != 'transfer_order'
GROUP BY 1,
2,
3,
4,
5,
6
UNION ALL
SELECT
'Purchase' as "Type",
"Purchase Order Items"."Item ID",
"Purchase Order Items"."Product ID",
"Purchase Orders"."Purchase Order Date",
"Purchase Order Items"."Quantity Received" + "Purchase Order Items"."Quantity Manually Received",
"Purchase Order Items"."Warehouse ID",
sum("Purchase Order Items"."Total (BCY)"),
0
FROM "Purchase Order Items"
LEFT JOIN "Purchase Orders" ON "Purchase Orders"."Purchase Order ID" = "Purchase Order Items"."Purchase Order ID"
GROUP BY 1,
2,
3,
4,
5,
6
UNION ALL
SELECT
'Sales' as "Type",
"Sales Order Items"."Item ID",
"Sales Order Items"."Product ID",
"Sales Orders"."Order Date",
-1 * ("Sales Order Items"."Quantity Shipped" + "Manually Fulfilled Quantity"),
"Sales Order Items"."Warehouse ID",
sum("Sales Order Items"."Total (BCY)"),
ifnull(ifnull(SUM("Sales Order Items"."Quantity"), 0) -ifnull(SUM("Sales Order Items"."Manually Fulfilled Quantity"), 0) -ifnull(SUM("Sales Order Items"."Quantity Cancelled"), 0) -ifnull(SUM("Sales Order Items"."Invoiced Quantity Cancelled"), 0) -ifnull(SUM("Sales Order Items"."Quantity Shipped"), 0), 0) as "Commited Stock"
FROM "Sales Order Items"
LEFT JOIN "Sales Orders" ON "Sales Orders"."Sales order ID" = "Sales Order Items"."Sales order ID"
WHERE "Sales Orders"."Status" not in ( 'draft' , 'void' , 'pending_approval' , 'approved' )
GROUP BY 1,
2,
3,
4,
5,
6
UNION ALL
SELECT
'SR',
"Sales Return Receive Items"."Item ID",
"Sales Return Items"."Product ID",
"Sales Return Receive"."Date",
"Sales Return Receive Items"."Quantity Received",
"Sales Return Items"."Warehouse ID",
0,
0
FROM "Sales Return Items"
LEFT JOIN "Sales Return Receive Items" ON "Sales Return Receive Items"."Sales Return Item ID" = "Sales Return Items"."Item ID"
LEFT JOIN "Sales Return Receive" ON "Sales Return Receive"."Sales Return Receive ID" = "Sales Return Receive Items"."Sales Return Receive ID"
UNION ALL
SELECT
'Transfer' as "Type",
"Transfer Order Items"."Item ID",
"Transfer Order Items"."Product ID",
"Transfer Order"."Date",
if("Transfer Order Items"."Transferred Quantity" < 0, "Transfer Order Items"."Transferred Quantity", if("Transfer Order"."Status" in ( 'transferred' , 'Transferred' ), "Transfer Order Items"."Transferred Quantity", 0)),
"Transfer Order Items"."Warehouse ID",
"Transfer Order Items"."Cost Price",
0
FROM "Transfer Order"
LEFT JOIN "Transfer Order Items" ON "Transfer Order"."Transfer Order ID" = "Transfer Order Items"."Transfer Order ID"
WHERE "Transfer Order"."Status" not in ( 'draft' , 'void' ) /* UNION ALL
SELECT
'comm',
"Invoice Items"."Item ID",
"Invoice Items"."Product ID",
"Invoices"."Invoice Date",
0,
0,0,
-1 * "Invoice Items"."Quantity"
FROM "Invoice Items"
JOIN "Invoices" ON "Invoices"."Invoice ID" = "Invoice Items"."Invoice ID"
WHERE "Invoice Items"."SO ItemID" IS NOT NULL
AND ("Invoices"."Invoice Status" NOT IN ( 'Draft' , 'Void' ))*/
