IP BomQty

Функции

---

iP_BomQty - это функция, которая раскладывает по уровням BOM и считает сколько нужно сырья на каждом уровне для получения конечного продукта в заданном количестве по нормам расходов. Расчет производится в точности до последних знаков после запятой как в Libero Manufacturing Order.

Изначально она была предназначена для сверки BOM с 1С при переходе.

На входе функции:
- готовый продукт или полуфабрикат,
- дата BOM(используется при хранении истории бомов, если истории не ведется, то можно любую дату),
- количество готового продукта или полуфабриката,
- последний параметр показывает нужно ли высчитывать сырье для полуфабрикатов с типом Компонент. 0 - не учитывать 'CO', 1 - учитывать 'CO'. (например, если есть полуфабрикаты фантомы, то для них нужно считать сырье, а если полуфабрикат изготавливается заранее и лежит уже готовый на складе, то считать на него сырье не нужно, достаточно показать сколько нужно этого полуфабриката для конечного продукта)

На выходе функции:
- конечный продукт,
- уровень BOM,
- полуфабрикат, изготавливаемый на уровне,
- необходимое количество этого полуфабриката на уровне для изготовления конечного продукта в заданном на входе количестве,
- ID строки BOM,
- сырье для изготовления полуфабриката на уровне,
- тип компонента,
- норма расхода,
- количество сырья, необходимое для изготовления полуфабриката на уровне в заданном количестве,

 
  CREATE OR REPLACE FUNCTION ip_bomqty(IN numeric, IN timestamp without time zone, IN numeric, IN numeric)
    RETURNS TABLE(sel_product_id numeric, 
                  levelno numeric, 
                  m_product_id numeric, 
                  bomqty numeric, 
                  pp_product_bomline_id numeric, 
                  m_productbom_id numeric, 
                  componenttype character, 
                  qtybom numeric, 
                  qtyrequired numeric) AS
  $BODY$
  
  /** @author @kinerix Anna Smirnova  */
  
  DECLARE L0 iP_BomQtyTypeCOPH[];
  DECLARE L1 iP_BomQtyTypeCOPH[];
  DECLARE L2 iP_BomQtyTypeCOPH[];
  DECLARE LNo numeric := 0;
  DECLARE Sel_Prod numeric := $1;
  DECLARE QtyReq numeric := $3;
  DECLARE P_date date := $2::date;
  DECLARE P_COPH numeric := $4;  /**when 0 then exclude raw from product with componenttype 'CO', because it is manufactured in other order*/
  
  BEGIN
  
  /**Before need to create custom type in this database*/
  /** create type iP_BomQtyTypeCOPH as (Sel_Product_ID numeric, 
  					LevelNo numeric, 
  					M_Product_ID numeric, 
  					BomQty numeric,
  					PP_Product_BomLine_ID numeric, 
  					M_ProductBom_ID numeric, 
  					ComponentType character(2),
  					QtyBom numeric, 
  					QtyRequired numeric);*/
  					
  L0 = (select  ARRAY[(select array_agg(array_to_string(ARRAY[(Sel_Prod, 
  							LNo, 
  							cast(0 as numeric), 
  							QtyReq,
  							cast(0 as numeric), 
  							Sel_Prod,
  							'',
  							cast(1 as numeric), 
  							QtyReq )]::iP_BomQtyTypeCOPH[], ', ')))]::iP_BomQtyTypeCOPH[]);
  L1 = L0;
  L2 = L0;
  
  LOOP
  
  L1 = (select 	ARRAY[(select array_agg(array_to_string(ARRAY[(
  				Sel_Prod,
  				LNo+1, 
  				t0.M_Product_ID, 
  				t2.QtyRequired,
  				t1.pp_product_bomline_ID,
  				t1.M_Product_ID,
  				t1.ComponentType,
  				t1.QtyBom,
  				cast(t1.QtyBom*t2.QtyRequired as numeric(19,5))
  				)]::iP_BomQtyTypeCOPH[], ', '))
  
  				
  				from pp_product_bomline t1
  				inner join pp_product_bom t0 on t1.pp_product_bom_ID = t0.pp_product_bom_ID 
  				inner join ( select * from unnest(L1) v0
  						where case when P_COPH=0 
  							then v0.ComponentType<>'CO' 
  							else v0.ComponentType=v0.ComponentType end ) t2 
  						on t2.M_ProductBom_ID = t0.M_Product_ID and t2.LevelNo = LNo
  				where	cast(t1.validfrom as date)<=P_date 
  					and (case when t1.validto is null then '2999-12-31' else cast(t1.validto as date) end)>=P_date
  				and t1.ComponentType <> 'VA'
  				and t0.m_product_ID in (select h.M_ProductBom_ID from unnest(L1) h where h.LevelNo = LNo)
  				 )]::iP_BomQtyTypeCOPH[]);
  
  LNo = LNo+1;
  
  L2 = 	(select		 	ARRAY[(select array_agg(array_to_string(ARRAY[(	L2_t.Sel_Product_ID, 
  										L2_t.LevelNo, 
  										L2_t.M_Product_ID, 
  										L2_t.BomQty, 
  										L2_t.PP_Product_BomLine_ID, 
  										L2_t.M_ProductBom_ID, 
  										L2_t.ComponentType,
  										L2_t.QtyBom, 
  										L2_t.QtyRequired)]::iP_BomQtyTypeCOPH[], ', '))
  	from
  	(select t0.Sel_Product_ID, t0.LevelNo, t0.M_Product_ID, t0.BomQty, t0.PP_Product_BomLine_ID,
                t0.M_ProductBom_ID, t0.ComponentType, t0.QtyBom, t0.QtyRequired from unnest(L2) t0
  	union all 
  	select t1.Sel_Product_ID, t1.LevelNo, t1.M_Product_ID, t1.BomQty, t1.PP_Product_BomLine_ID,
                t1.M_ProductBom_ID, t1.ComponentType, t1.QtyBom, t1.QtyRequired from unnest(L1) t1) L2_t  )]::iP_BomQtyTypeCOPH[]);
  
  EXIT WHEN LNo = 10;
  --- EXIT WHEN coalesce(L1,array[0]) = array[cast(0 as numeric)]; 
  --- EXIT WHEN coalesce((select count(*)::numeric from unnest(L1)),0) = cast(0 as numeric); 
  --- sometimes this exit from loop work's, sometimes - not. You can use HARD for levels of BOM for example LNo = 10 to exit from loop 100%
  
  END LOOP;
  	
  RETURN QUERY						
  
  select d.Sel_Product_ID, 
  	d.LevelNo, 
  	d.M_Product_ID, 
  	d.BomQty,
  	d.PP_Product_BomLine_ID, 
  	d.M_ProductBom_ID,
  	d.ComponentType, 
  	d.QtyBom, 
  	d.QtyRequired from unnest(L2) d;
  
  END;
  
  $BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100
    ROWS 1000;
  ALTER FUNCTION ip_bomqty(numeric, timestamp without time zone, numeric, numeric)
    OWNER TO adempiere;