/*
CREATE TABLE rptpriceoverride
(
  date character varying(8) NOT NULL,
  pos_nbr smallint NOT NULL,
  trans_nbr smallint NOT NULL,
  seq_nbr smallint NOT NULL,
  operator_nbr integer NOT NULL,
  supervisor_nbr integer NOT NULL,
  upc character varying(20) NOT NULL,
  dept_number smallint NOT NULL,
  unit_price integer NOT NULL,
  altered_unit_price integer NOT NULL,
  qty integer NOT NULL,
  reason_code smallint NOT NULL,
  action_code smallint NOT NULL,
  code2 smallint NOT NULL
)
WITH (
  OIDS=FALSE
);
ALTER TABLE rptpriceoverride
  OWNER TO postgres;
*/

-- Function: search_cambio_precio(text, text, text, text, text, text, text)

-- DROP FUNCTION search_cambio_precio(text, text, text, text, text, text, text);

CREATE OR REPLACE FUNCTION search_cambio_precio(
    IN param_fechadesde text,
    IN param_fechahasta text,
    IN param_departamento text,
    IN param_host text,
    IN param_user text,
    IN param_password text,
    IN param_db text)
  RETURNS TABLE(txid smallint, fecha_completa text, departamento text, departamento_numero text, teminal_numero numeric, operador_numero integer, upc_numero bigint, descripcion text, razon_codigo smallint, razon text, precio_tienda numeric, precio_autdo numeric, cantidad numeric, precio_venta numeric, monto_venta numeric, monto_devolucion numeric, dif_monto numeric, dif_porcentaje numeric) AS
$BODY$

BEGIN

	RETURN QUERY(
		select
		p.trans_nbr as TXID,
		cast(p.date as text) as FECHA_COMPLETA,
		dep.designation as DEPARTAMENTO,
		cast(p.dept_number as text) as DEPARTAMENTO_NUMERO,
		cast(p.pos_nbr as numeric) as TEMINAL_NUMERO,
		p.operator_nbr as OPERADOR_NUMERO,
		cast(p.upc as bigint) as UPC_NUMERO,
		ar.designation as DESCRIPCION,
		p.reason_code as RAZON_CODIGO,
		cast(pa.valuetext as text) as RAZON,
		abs(p.unit_price * 0.01)::numeric as PRECIO_TIENDA,
		abs(p.altered_unit_price * 0.01)::numeric as PRECIO_AUTDO,
		(p.qty::numeric/1000) as CANTIDAD,
		((p.altered_unit_price * (p.qty::numeric/1000)) * 0.01)::numeric as PRECIO_VENTA,
		(case when (p.altered_unit_price * (p.qty::numeric/1000) ) > 0 and action_code in (4,0) and code2 = 0 then (p.altered_unit_price * (p.qty::numeric/1000) ) else 0 end)::numeric as MONTO_VENTA,
		(case when (p.altered_unit_price * (p.qty::numeric/1000) ) < 0 and action_code = 4 and code2 = 4 then (p.altered_unit_price * (p.qty::numeric/1000) * -1) else 0 end)::numeric as MONTO_DEVOLUCION,
		(abs(p.altered_unit_price) - abs(p.unit_price) )* 0.01::numeric as DIF_MONTO,
		round(((abs(p.altered_unit_price) - (abs(p.unit_price)) / (p.qty::numeric/1000))/ abs(p.unit_price)) * 100) as DIF_PORCENTAJE
		from rptpriceoverride p
		left outer join (select * from dblink('host='||Param_host||' user='||Param_user||' password='||Param_password||' dbname='||Param_db, 'SELECT article_number, designation FROM mtxadmin.article ORDER BY article_number')as t1(article_number text, designation text)) as ar on cast(p.upc as bigint) = cast(ar.article_number as bigint)
		left outer join (select * from dblink('host='||Param_host||' user='||Param_user||' password='||Param_password||' dbname='||Param_db, 'SELECT department_number, designation FROM mtxadmin.department ORDER BY department_number')as t2(department_number text, designation text)) as dep on cast(p.dept_number as integer) = cast(dep.department_number as integer)
		left outer join (select * from dblink('host='||Param_host||' user='||Param_user||' password='||Param_password||' dbname='||Param_db, 'SELECT (SUBSTRING(reasonno,4,3))::smallint as reasonno, valuetext FROM par_reasoncode  where (SUBSTRING(reasonno,2,1))::smallint = 1 order by reasonno')as t3(reasonno text, valuetext text)) as pa on cast(p.reason_code  as integer) = cast(pa.reasonno as integer)
		where (Param_FechaDesde is null or p.date >= Param_FechaDesde)
		and (Param_FechaHasta is null or p.date <= Param_FechaHasta)
		and (Param_Departamento is null or Param_Departamento = '0000' or (p.dept_number = cast(Param_Departamento as integer)))
		order by DEPARTAMENTO_NUMERO, TEMINAL_NUMERO,OPERADOR_NUMERO, TXID
	);
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION search_cambio_precio(text, text, text, text, text, text, text)
  OWNER TO mtxadmin;