create or replace view vw_production_unit_type as select int_type, txt_name from tbl_type where int_category_id = 1001;
CREATE or replace VIEW vw_production_units 
AS 
SELECT tbl_production_units.int_production_unit_id, 
tbl_production_units.txt_production_unit, 
tbl_production_units.int_production_unit_type, 
vw_production_unit_type.txt_name as txt_production_unit_type_name,
tbl_production_units.int_user_id
FROM tbl_production_units
Left JOIN vw_production_unit_type ON(vw_production_unit_type.int_type=tbl_production_units.int_production_unit_type)
LEFT JOIN tbl_users ON (tbl_production_units.int_user_id=tbl_users.int_user_id);

CREATE or replace VIEW vw_production 
AS 
SELECT tbl_production.int_production_id, 
tbl_production.int_production_unit_id, 
tbl_production_units.txt_production_unit, 
tbl_production.txt_batch_no, 
tbl_production.dt_start, 
tbl_production.dt_end, 
tbl_production.int_organisation_id, 
tbl_production.txt_remarks, 
tbl_production.int_user_id
FROM tbl_production
LEFT JOIN tbl_production_units ON (tbl_production.int_production_unit_id=tbl_production_units.int_production_unit_id)
LEFT JOIN tbl_organisation ON (tbl_production.int_organisation_id=tbl_organisation.int_organisation_id)
LEFT JOIN tbl_users ON (tbl_production.int_user_id=tbl_users.int_user_id);

CREATE or replace VIEW vw_sales_order 
AS 
SELECT tbl_sales_order.int_sales_order_id AS  int_sales_order_id, 
tbl_sales_order.dt_transaction, 
tbl_sales_order.int_number AS  int_number, 
tbl_sales_order.txt_formatted_number AS  txt_formatted_number, 
tbl_sales_order.int_party_id,  
tbl_parties.txt_name AS txt_party_name, 
tbl_sales_order.int_product_id, 
tbl_products.txt_product_name, 
tbl_sales_order.int_agent_party_id AS  int_agent_party_id, 
tbl_sales_order.dbl_rate, 
tbl_sales_order.dbl_total_weight AS  dbl_total_weight, 
tbl_sales_order.dbl_total_product_value AS  dbl_total_product_value, 
tbl_sales_order.dbl_total_freight_value, 
tbl_sales_order.dbl_total_tax_value AS  dbl_total_tax_value, 
tbl_sales_order.txt_remarks AS  txt_remarks, 
tbl_sales_order.int_organisation_id, 
tbl_sales_order.int_user_id
FROM tbl_sales_order
LEFT JOIN tbl_parties ON (tbl_sales_order.int_party_id=tbl_parties.int_party_id)
LEFT JOIN tbl_products ON (tbl_sales_order.int_product_id=tbl_products.int_product_id)
LEFT JOIN tbl_organisation ON (tbl_sales_order.int_organisation_id=tbl_organisation.int_organisation_id)
LEFT JOIN tbl_users ON (tbl_sales_order.int_user_id=tbl_users.int_user_id);

CREATE or replace VIEW vw_purchase_order 
AS 
SELECT tbl_purchase_order.int_purchase_order_id AS  int_purchase_order_id, 
tbl_purchase_order.dt_transaction, 
tbl_purchase_order.int_number AS  int_number, 
tbl_purchase_order.txt_formatted_number AS  txt_formatted_number, 
tbl_purchase_order.int_party_id, 
tbl_parties.txt_name AS txt_party_name, 
tbl_purchase_order.int_product_id, 
tbl_products.txt_product_name, 
tbl_purchase_order.int_agent_party_id AS  int_agent_party_id, 
tbl_purchase_order.txt_party_document_no, 
tbl_purchase_order.dbl_rate, 
tbl_purchase_order.dbl_total_gross_weight, 
tbl_purchase_order.dbl_total_tare_weight, 
tbl_purchase_order.dbl_total_net_weight, 
tbl_purchase_order.dbl_total_product_value AS  dbl_total_product_value, 
tbl_purchase_order.dbl_total_freight_value, 
tbl_purchase_order.dbl_total_tax_value AS  dbl_total_tax_value, 
tbl_purchase_order.txt_remarks AS  txt_remarks, 
tbl_purchase_order.int_organisation_id, 
tbl_purchase_order.int_user_id
FROM tbl_purchase_order
LEFT JOIN tbl_parties ON (tbl_purchase_order.int_party_id=tbl_parties.int_party_id)
LEFT JOIN tbl_products ON (tbl_purchase_order.int_product_id=tbl_products.int_product_id)
LEFT JOIN tbl_organisation ON (tbl_purchase_order.int_organisation_id=tbl_organisation.int_organisation_id)
LEFT JOIN tbl_users ON (tbl_purchase_order.int_user_id=tbl_users.int_user_id);

CREATE or replace VIEW vw_stock_movement 
AS 
SELECT tbl_stock_movement.int_stock_movement_id, 
tbl_stock_movement.dt_transaction, 
tbl_stock_movement.int_product_id, 
tbl_products.txt_product_name, 
tbl_stock_movement.dbl_quantity, 
tbl_stock_movement.int_source_purchase_id, 
concat(tbl_purchase_order.txt_formatted_number,'/',tbl_purchase_order.int_number) as txt_source_purchase_order_number, 
tbl_stock_movement.int_source_storage_unit_id, 
tbl_storage_units.txt_storage AS txt_source_storage_name, 
tbl_storage_units.int_storage_capacity AS int_source_storage_capacity, 
tbl_stock_movement.int_source_production_id, 
tbl_production.txt_batch_no AS txt_source_production_batch_no, 
tbl_stock_movement.txt_source_drum_tag, 
tbl_stock_movement.int_opening_id, 
tbl_stock_movement.int_target_sales_order_id, 
concat(tbl_sales_order.txt_formatted_number,'/',tbl_sales_order.int_number) as txt_target_sales_order_number, 
tbl_stock_movement.int_target_storage_unit_id, 
tbl_target_storage_units.txt_storage AS txt_target_storage_name, 
tbl_target_storage_units.int_storage_capacity AS int_target_storage_capacity, 
tbl_stock_movement.int_target_production_id, 
tbl_target_production.txt_batch_no AS txt_target_production_batch_no, 
tbl_stock_movement.txt_target_drum_tag, 
tbl_stock_movement.txt_remarks, 
tbl_stock_movement.int_organisation_id, 
tbl_stock_movement.int_user_id
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)
LEFT JOIN tbl_storage_units ON (tbl_stock_movement.int_source_storage_unit_id=tbl_storage_units.int_storage_unit_id)
LEFT JOIN tbl_production ON (tbl_stock_movement.int_source_production_id=tbl_production.int_production_id)
LEFT JOIN tbl_sales_order ON (tbl_stock_movement.int_target_sales_order_id=tbl_sales_order.int_sales_order_id)
LEFT JOIN tbl_storage_units tbl_target_storage_units ON (tbl_stock_movement.int_target_storage_unit_id=tbl_target_storage_units.int_storage_unit_id)
LEFT JOIN tbl_production tbl_target_production ON (tbl_stock_movement.int_target_production_id=tbl_target_production.int_production_id)
LEFT JOIN tbl_organisation ON (tbl_stock_movement.int_organisation_id=tbl_organisation.int_organisation_id)
LEFT JOIN tbl_users ON (tbl_stock_movement.int_user_id=tbl_users.int_user_id);

CREATE or replace VIEW vw_drum_register 
AS 
SELECT 
tbl_stock_movement.dt_transaction, 
tbl_stock_movement.int_source_production_id, 
vw_production.int_production_unit_id,
vw_production.txt_production_unit,
vw_production.txt_batch_no AS txt_source_production_batch_no, 
tbl_stock_movement.int_product_id, 
tbl_products.txt_product_name, 
(CASE 
  WHEN tbl_stock_movement.txt_target_drum_tag !='null' THEN tbl_stock_movement.dbl_quantity 
  ELSE null
END) as dbl_stock_in,
0 as dbl_stock_out,
tbl_stock_movement.txt_source_drum_tag ,  
tbl_stock_movement.txt_target_drum_tag , 
tbl_stock_movement.txt_remarks
FROM tbl_stock_movement
LEFT JOIN tbl_products ON (tbl_stock_movement.int_product_id=tbl_products.int_product_id )
LEFT JOIN vw_production ON (tbl_stock_movement.int_source_production_id=vw_production.int_production_id )
where tbl_stock_movement.txt_target_drum_tag !='null'
union all
SELECT 
tbl_stock_movement.dt_transaction, 
tbl_stock_movement.int_source_production_id, 
vw_production.int_production_unit_id,
vw_production.txt_production_unit,
vw_production.txt_batch_no AS txt_source_production_batch_no, 
tbl_stock_movement.int_product_id, 
tbl_products.txt_product_name, 
0 as dbl_stock_in,
(CASE 
  WHEN tbl_stock_movement.txt_source_drum_tag !='null' THEN tbl_stock_movement.dbl_quantity 
  ELSE null
END) as dbl_stock_out,
tbl_stock_movement.txt_source_drum_tag ,  
tbl_stock_movement.txt_target_drum_tag , 
tbl_stock_movement.txt_remarks
FROM tbl_stock_movement
LEFT JOIN tbl_products ON (tbl_stock_movement.int_product_id=tbl_products.int_product_id )
LEFT JOIN vw_production ON (tbl_stock_movement.int_source_production_id=vw_production.int_production_id )
where tbl_stock_movement.txt_source_drum_tag !='null'
---where tbl_stock_movement.int_source_production_id is not null and tbl_stock_movement.txt_target_drum_tag !='null'



