# Reporte producción - Apoyo a reporte financiero 06/2020

###### tags: `DataBase`

## QUERY

### Ejemplos
:::info
Clave: **MO/39081** <--- Aqui tenemos lo consumido, no reportados y lo finalizado
:::

:::info
Clave: **MO/39554** <--- Aqui tenemos lo consumido y lo finalizado
:::
### Descripcion

El query genera el listado de todos los productos de dilusión con las ordenes aplicadas en el intervalo de `2020-01-01` a `2020-06-01`, mostrando todos los movimientos ==CONSUMIDOS==, ==FINALIZADOS== Y ==NO REPORTADOS==. Si es necesario el nivel de detalle del **quant** solo desmarcar los inner comentados y campos.

### Query

SELECT d.*,
	       (p.create_date AT TIME ZONE 'UTC') mrpprod_crea,
	       p.product_qty                      mrpprod_product_qty,
	       p.product_uom_id                   mrpprod_uom_id,
	       pu."name"                          mrpprod_uom_name,
	       p."name"                           mrpprod_mo,
	       ml.product_id                      id_prod,
	       pp.default_code,
	       ml.product_qty,
	       sm.id							  sm_id,
	       sm.create_date                     sm_date,
	       pu2."name"                         sm_uom,
	       sm.price_unit                      sm_price_unit,
	       sm.product_qty                     sm_product_qty,
	       sl2."name"                         sm_location_origin,
	       sm.state                           sm_state,
	       sm.ordered_qty                     sm_qty_order,
	       sl3."name"                         sm_location_destiny,
	       sm.unit_factor                     sm_unit_factor,
	       sm.amount_stock_move               sm_amount_stock_move,
	       sm.product_categ_id                sm_product_category_id,
	       sm.udv_total                       sm_udv_total,
	       sm.picking_id                      sm_picking_id,
	       sm.group_id                        sm_group_id,
	       sm.workorder_id                    sm_work_order_id,
	       case 
			      when sm.state = 'cancel'  
			      	then 'MATERIAL NO REPORTADO'
			      else
			      	'MATERIAL CONSUMIDO'
			end material_state
	FROM dilusion d
	         INNER JOIN mrp_production p ON product_product_id = p.product_id
	         INNER JOIN product_uom pu on p.product_uom_id = pu.id
	         INNER JOIN mrp_bom_line ml on d.mb_id = ml.bom_id
	         INNER JOIN product_product pp on ml.product_id = pp.id
	         inner JOIN stock_move sm on sm.origin = p."name" 
	         inner join product_uom pu2 on pu2.id = sm.product_uom 
	         inner join stock_location sl2 on sm.location_id = sl2.id
	         inner join stock_location sl3 on sm.location_dest_id = sl3.id
	         /*LEFT JOIN stock_quant_move_rel sr ON sm.id = sr.move_id
	         LEFT JOIN stock_quant sq ON sq.id = sr.quant_id*/
	WHERE ( p.create_date AT TIME ZONE 'UTC' ) BETWEEN '2020-01-01' AND '2020-06-01'
	  AND p.name = 'MO/39081'
	  AND sm.product_id = ml.product_id
	union
	SELECT d.*,
	       (p.create_date AT TIME ZONE 'UTC') mrpprod_crea,
	       p.product_qty                      mrpprod_product_qty,
	       p.product_uom_id                   mrpprod_uom_id,
	       pu."name"                          mrpprod_uom_name,
	       p."name"                           mrpprod_mo,
	       pp2.id                             id_prod,
	       pp2.default_code                   default_code,
	       0 							      product_qty,
	       sm.id							  sm_id,
	       sm.create_date                     sm_date,
	       pu2."name"                         sm_uom,
	       sm.price_unit                      sm_price_unit,
	       sm.product_qty                     sm_product_qty,
	       sl2."name"                         sm_location_origin,
	       sm.state                           sm_state,
	       sm.ordered_qty                     sm_qty_order,
	       sl3."name"                         sm_location_destiny,
	       sm.unit_factor                     sm_unit_factor,
	       sm.amount_stock_move               sm_amount_stock_move,
	       sm.product_categ_id                sm_product_category_id,
	       sm.udv_total                       sm_udv_total,
	       sm.picking_id                      sm_picking_id,
	       sm.group_id                        sm_group_id,
	       sm.workorder_id                    sm_work_order_id,
	       'MATERIAL FINALIZADO'              material_state
	FROM dilusion d
	         INNER JOIN mrp_production p ON d.product_product_id = p.product_id
	         INNER JOIN product_uom pu on p.product_uom_id = pu.id
	         inner JOIN stock_move sm on sm.origin = p."name" 
	         inner join product_uom pu2 on pu2.id = sm.product_uom 
	         inner join stock_location sl2 on sm.location_id = sl2.id
	         inner join stock_location sl3 on sm.location_dest_id = sl3.id
	         inner join product_product pp2 on p.product_id = pp2.id
	         /*LEFT JOIN stock_quant_move_rel sr ON sm.id = sr.move_id
	         LEFT JOIN stock_quant sq ON sq.id = sr.quant_id*/
	WHERE ( p.create_date AT TIME ZONE 'UTC' ) BETWEEN '2020-01-01' AND '2020-06-01'
	  AND p.name = 'MO/39081'
	  AND sm.product_id = p.product_id
	  and sm.production_id notnull;

## Reporte a nivel de detalle de movimiento

select * from dilusion_movimientos where mrpprod_mo = 'MO/39081';

## Reporte a nivel de detalle de quant

select 
	 	dm.*,
	 	sr.quant_id,
	 	sq.create_date quant_date,
	 	sq.qty  quant_qty,
	 	sq."cost" quant_cost,
	 	sl."name" quant_location,
	 	sq.udv_total quant_udv_total
	from 
    	dilusion_movimientos dm
	    LEFT JOIN stock_quant_move_rel sr ON dm.sm_id = sr.move_id
	    LEFT JOIN stock_quant sq ON sq.id = sr.quant_id
	    left join stock_location sl on sq.location_id = sl.id 
    where 
    	mrpprod_mo = 'MO/39081';

:::warning
Favor de poner el intervalo de fechas para la busqueda con el campo **mrpprod_crea**
:::