
-- Select dt_transaction,int_product_id, txt_product_name, txt_opening, txt_purchase, txt_drum, txt_tanks, sum(dbl_qty)
-- From
--(
CREATE or replace VIEW vw_current_stock 
AS
SELECT
tbl_stock_movement.dt_transaction, 
tbl_stock_movement.int_product_id, 
tbl_products.txt_product_name, 
tbl_products.txt_product_name as txt_opening,
null as txt_purchase,
null as txt_drum,
null as txt_tanks,
tbl_stock_movement.dbl_quantity as dbl_qty
FROM tbl_stock_movement
LEFT JOIN tbl_products ON (tbl_stock_movement.int_product_id=tbl_products.int_product_id)
where tbl_stock_movement.int_opening_id is not null and tbl_stock_movement.txt_remarks ='Product Opening'
UNION All
SELECT
tbl_stock_movement.dt_transaction, 
tbl_stock_movement.int_product_id, 
tbl_products.txt_product_name, 
null as txt_opening,
concat(tbl_purchase_order.txt_formatted_number,'/',tbl_purchase_order.int_number) as txt_purchase,
null as txt_drum,
null as txt_tanks,
tbl_stock_movement.dbl_quantity as dbl_qty
FROM tbl_stock_movement
LEFT JOIN tbl_products ON (tbl_stock_movement.int_product_id=tbl_products.int_product_id)
LEFT JOIN tbl_purchase_order ON (tbl_stock_movement.int_source_purchase_id=tbl_purchase_order.int_purchase_order_id)
where tbl_stock_movement.int_source_purchase_id is not null
UNION All
SELECT
tbl_stock_movement.dt_transaction, 
tbl_stock_movement.int_product_id, 
tbl_products.txt_product_name, 
null as txt_opening,
null as txt_purchase,
null as txt_drum,
tbl_storage_units.txt_storage as txt_tanks,
(-tbl_stock_movement.dbl_quantity) as dbl_qty
FROM tbl_stock_movement
LEFT JOIN tbl_products ON (tbl_stock_movement.int_product_id=tbl_products.int_product_id)
LEFT JOIN tbl_storage_units ON (tbl_stock_movement.int_source_storage_unit_id=tbl_storage_units.int_storage_unit_id)
where tbl_stock_movement.int_source_storage_unit_id is not null
UNION All
SELECT
tbl_stock_movement.dt_transaction, 
tbl_stock_movement.int_product_id, 
tbl_products.txt_product_name, 
null as txt_opening,
null as txt_purchase,
tbl_stock_movement.txt_source_drum_tag as txt_drum,
null as txt_tanks,
(-tbl_stock_movement.dbl_quantity) as dbl_qty
FROM tbl_stock_movement
LEFT JOIN tbl_products ON (tbl_stock_movement.int_product_id=tbl_products.int_product_id)
where tbl_stock_movement.txt_source_drum_tag != 'null'
UNION All
SELECT
tbl_stock_movement.dt_transaction, 
tbl_stock_movement.int_product_id, 
tbl_products.txt_product_name, 
null as txt_opening,
null as txt_purchase,
null as txt_drum,
tbl_storage_units.txt_storage as txt_tanks,
tbl_stock_movement.dbl_quantity as dbl_qty
FROM tbl_stock_movement
LEFT JOIN tbl_products ON (tbl_stock_movement.int_product_id=tbl_products.int_product_id)
LEFT JOIN tbl_storage_units ON (tbl_stock_movement.int_target_storage_unit_id=tbl_storage_units.int_storage_unit_id)
where tbl_stock_movement.int_target_storage_unit_id is not null
UNION All
SELECT
tbl_stock_movement.dt_transaction, 
tbl_stock_movement.int_product_id, 
tbl_products.txt_product_name, 
null as txt_opening,
null as txt_purchase,
tbl_stock_movement.txt_target_drum_tag as txt_drum,
null as txt_tanks,
(tbl_stock_movement.dbl_quantity) as dbl_qty
FROM tbl_stock_movement
LEFT JOIN tbl_products ON (tbl_stock_movement.int_product_id=tbl_products.int_product_id)
where tbl_stock_movement.txt_target_drum_tag != 'null'
UNION All
SELECT
tbl_stock_movement.dt_transaction, 
tbl_stock_movement.int_product_id, 
tbl_products.txt_product_name, 
null as txt_opening,
concat(tbl_sales_order.txt_formatted_number,'/',tbl_sales_order.int_number) as txt_purchase,
null as txt_drum,
null as txt_tanks,
(-tbl_stock_movement.dbl_quantity) as dbl_qty
FROM tbl_stock_movement
LEFT JOIN tbl_products ON (tbl_stock_movement.int_product_id=tbl_products.int_product_id)
LEFT JOIN tbl_sales_order ON (tbl_stock_movement.int_target_sales_order_id=tbl_sales_order.int_sales_order_id)
where tbl_stock_movement.int_target_sales_order_id is not null
-- )qry

-- group by int_product_id
-- order by dt_transaction desc


