|
-- FUNCTION: adempiere.calculo_comision_cobranza(numeric, numeric, character varying, date, date, integer, integer)
|
|
|
|
-- DROP FUNCTION adempiere.calculo_comision_cobranza(numeric, numeric, character varying, date, date, integer, integer);
|
|
|
|
CREATE OR REPLACE FUNCTION adempiere.calculo_comision_cobranza(
|
|
organizacion numeric,
|
|
oficial numeric,
|
|
tipo character varying,
|
|
fechadesde date,
|
|
fechahasta date,
|
|
diadesde integer,
|
|
diahasta integer)
|
|
RETURNS double precision
|
|
LANGUAGE 'plpgsql'
|
|
|
|
COST 100
|
|
VOLATILE
|
|
|
|
AS $BODY$
|
|
declare
|
|
valor float;
|
|
begin
|
|
if tipo='XV' then
|
|
select (
|
|
select
|
|
sum(pys.dueamt) as valor
|
|
from Tb_ProjectPaySchedule pys
|
|
inner join C_Project cp on cp.C_Project_id=pys.C_Project_id
|
|
INNER join tb_estadoContrato tbec on tbec.tb_estadocontrato_id = cp.tb_estadocontrato_id
|
|
left join C_ProjectType type on type.C_ProjectType_ID=cp.C_ProjectType_ID and type.Name='CONTRATO_INMOBILIARIO'
|
|
left join ad_user ca on ca.ad_user_ID=cp.CarteraRep_ID
|
|
|
|
where cp.ad_org_id=organizacion
|
|
AND tbec.IsValid='Y' AND tbec.IsActive='Y' AND cp.IsActive='Y'
|
|
AND CASE WHEN 'Y'='Y' THEN tbec.esVenta='Y' ELSE (tbec.esVenta='N' OR tbec.esVenta='Y') END
|
|
AND pys.paymentType ='CE' AND pys.isActive='Y'
|
|
AND cp.IsInDispute='N'
|
|
AND cp.tipoFinanciamiento IN('DIR','CON','HIP')
|
|
AND pys.DueDate::date>=fechaDesde and pys.DueDate::date<=fechaHasta
|
|
AND cp.CarteraRep_ID=oficial
|
|
AND pys.isActive='Y'
|
|
) into valor;
|
|
end if;
|
|
|
|
if tipo='XVC' THEN
|
|
select (
|
|
|
|
SELECT sum(val.valorConCargo)+sum(val.valor) as valor
|
|
FROM(
|
|
select sum(k.payamt) as valor,
|
|
|
|
/*(coalesce((select sum(k.payamt)
|
|
from Tb_ProjectPaySchedule pp
|
|
left join Tb_ProjectPayScheduleLine k on k.Tb_ProjectPaySchedule_id=pp.Tb_ProjectPaySchedule_id
|
|
left join tb_allocationpayschedule al_pys on al_pys.tb_allocationpayschedule_id=k.tb_allocationpayschedule_id
|
|
where
|
|
k.Tb_ProjectPaySchedule_id=pp.Tb_ProjectPaySchedule_id
|
|
and al_pys.docstatus IN ('CO', 'CL')
|
|
and pp.isActive='Y'
|
|
and k.c_payment_ID is null
|
|
and k.paymentDate::date>=fechaDesde::date --$P{fechaVencimientoCuota}
|
|
and CASE WHEN pp.DueDate::date + interval '1 day'*5 <= fechaHasta::date THEN k.paymentDate::date<=fechaHasta::date
|
|
ELSE k.paymentDate::date<= (pp.DueDate::date + interval '1 day'*5) END
|
|
AND pp.paymentType ='CE'
|
|
),0)) as valorConCargo*/
|
|
|
|
(coalesce((select sum(k.payamt)
|
|
from Tb_ProjectPaySchedule ppx
|
|
left join Tb_ProjectPayScheduleLine k on k.Tb_ProjectPaySchedule_id=ppx.Tb_ProjectPaySchedule_id
|
|
left join tb_allocationpayschedule al_pys on al_pys.tb_allocationpayschedule_id=k.tb_allocationpayschedule_id
|
|
where
|
|
k.Tb_ProjectPaySchedule_id=ppx.Tb_ProjectPaySchedule_id
|
|
and al_pys.docstatus IN ('CO', 'CL')
|
|
and ppx.isActive='Y'
|
|
and k.c_payment_ID is null
|
|
and k.paymentDate::date>=fechaDesde::date --$P{fechaVencimientoCuota}
|
|
and CASE WHEN ppx.DueDate::date + interval '1 day'*5 <= fechaHasta::date THEN k.paymentDate::date<=fechaHasta::date
|
|
ELSE k.paymentDate::date<= (ppx.DueDate::date + interval '1 day'*5) END
|
|
AND ppx.paymentType ='CE'
|
|
AND ppx.Tb_ProjectPaySchedule_id=pp.Tb_ProjectPaySchedule_id
|
|
),0)) as valorConCargo
|
|
|
|
from Tb_ProjectPaySchedule pp
|
|
left join Tb_ProjectPayScheduleLine k on k.Tb_ProjectPaySchedule_id=pp.Tb_ProjectPaySchedule_id
|
|
left join tb_allocationpayschedule al_pys on al_pys.tb_allocationpayschedule_id=k.tb_allocationpayschedule_id
|
|
inner join c_payment cp on cp.c_payment_ID=k.c_payment_ID
|
|
inner join C_Project cpx on cpx.C_Project_id=pp.C_Project_id
|
|
inner join tb_estadoContrato tbec on tbec.tb_estadocontrato_id = cpx.tb_estadocontrato_id
|
|
left join C_ProjectType type on type.C_ProjectType_ID=cpx.C_ProjectType_ID and type.Name='CONTRATO_INMOBILIARIO'
|
|
where
|
|
cpx.ad_org_id=organizacion
|
|
and al_pys.docstatus IN ('CO', 'CL')
|
|
and pp.isActive='Y'
|
|
and cp.docStatus IN ('CO','CL')
|
|
and k.paymentDate::date>=fechaDesde
|
|
and
|
|
CASE WHEN pp.DueDate::date + interval '1 day'*5<= fechaHasta
|
|
THEN k.paymentDate::date<=fechaHasta
|
|
ELSE k.paymentDate::date<= (pp.DueDate::date + interval '1 day'*5) END
|
|
AND pp.paymentType ='CE'
|
|
AND tbec.IsValid='Y' AND tbec.IsActive='Y' AND cp.IsActive='Y'
|
|
AND CASE WHEN 'Y'='Y' THEN tbec.esVenta='Y' ELSE (tbec.esVenta='N' OR tbec.esVenta='Y') END
|
|
AND pp.DueDate::date>=fechaDesde::date and pp.DueDate::date<=fechaHasta
|
|
AND cpx.CarteraRep_ID=oficial
|
|
AND cpx.IsInDispute='N'
|
|
AND cpx.tipoFinanciamiento IN('DIR','CON','HIP')
|
|
GROUP BY pp.Tb_ProjectPaySchedule_id
|
|
)val
|
|
) into valor;
|
|
end if;
|
|
|
|
|
|
if tipo='V' THEN
|
|
select (
|
|
select
|
|
(cobranza.valorCuotasVencida - COALESCE(cobranza.PagosCuotasVencidas,0)) as SaldoVencido
|
|
FROM (
|
|
select
|
|
sum((select sum(s.dueamt)
|
|
FROM Tb_ProjectPaySchedule s
|
|
where s.c_project_ID=cp.c_project_ID AND s.isActive='Y'
|
|
AND s.paymentType IN ('CE','CI')
|
|
and s.DueDate::date <= fechaDesde - interval '1 day'*diadesde
|
|
and s.DueDate::date >= fechaDesde - interval '1 day'*diahasta
|
|
))
|
|
AS valorCuotasVencida,
|
|
|
|
sum(coalesce((select
|
|
sum(coalesce(k.payamt,0)) as saldo
|
|
FROM Tb_ProjectPaySchedule s
|
|
inner join Tb_ProjectPayScheduleLine k on k.Tb_ProjectPaySchedule_id=s.Tb_ProjectPaySchedule_id and k.IsActive='Y'
|
|
and exists (select 1 from tb_allocationpayschedule alp where alp.tb_allocationpayschedule_id=k.tb_allocationpayschedule_id and alp.docstatus in ('CO', 'CL'))
|
|
and (case when k.c_Payment_ID isnull then 1=1 else exists (select 1 from c_Payment ptbl where ptbl.c_Payment_ID = k.c_Payment_ID and ptbl.docstatus in ('CO', 'CL')) end)
|
|
inner join tb_allocationpayschedule al_pys on al_pys.tb_allocationpayschedule_id=k.tb_allocationpayschedule_id
|
|
left join c_payment cpx on cpx.c_payment_ID=k.c_payment_ID and cpx.docStatus IN ('CO','CL')
|
|
WHERE
|
|
s.C_Project_ID=cp.C_Project_ID
|
|
AND s.paymentType IN ('CE','CI')
|
|
AND s.isActive='Y'
|
|
and s.DueDate::date <= fechaDesde - interval '1 day'*diadesde
|
|
and s.DueDate::date >= fechaDesde - interval '1 day'*diahasta
|
|
and k.paymentDate::date<fechaDesde
|
|
and al_pys.docstatus IN ('CO', 'CL')
|
|
and s.isActive='Y'
|
|
),0)) AS PagosCuotasVencidas
|
|
|
|
FROM C_Project cp
|
|
inner join tb_estadoContrato tbec on tbec.tb_estadocontrato_id = cp.tb_estadocontrato_id
|
|
WHERE cp.ad_org_id=organizacion
|
|
AND tbec.IsValid='Y' AND cp.IsActive='Y' AND tbec.IsActive='Y'
|
|
AND tbec.esVenta='Y'
|
|
AND cp.tipoFinanciamiento IN('DIR','CON','HIP')
|
|
and cp.CarteraRep_ID=oficial
|
|
AND cp.IsInDispute='N'
|
|
) cobranza
|
|
) into valor;
|
|
end if;
|
|
|
|
|
|
if tipo='VC' THEN
|
|
select (
|
|
|
|
select
|
|
sum(coalesce((select
|
|
sum(coalesce(k.payamt,0)) as saldo
|
|
FROM Tb_ProjectPaySchedule s
|
|
inner join Tb_ProjectPayScheduleLine k on k.Tb_ProjectPaySchedule_id=s.Tb_ProjectPaySchedule_id and k.IsActive='Y'
|
|
and exists (select 1 from tb_allocationpayschedule alp where alp.tb_allocationpayschedule_id=k.tb_allocationpayschedule_id and alp.docstatus in ('CO', 'CL'))
|
|
and (case when k.c_Payment_ID isnull then 1=1 else exists (select 1 from c_Payment ptbl where ptbl.c_Payment_ID = k.c_Payment_ID and ptbl.docstatus in ('CO', 'CL')) end)
|
|
inner join tb_allocationpayschedule al_pys on al_pys.tb_allocationpayschedule_id=k.tb_allocationpayschedule_id
|
|
left join c_payment cpx on cpx.c_payment_ID=k.c_payment_ID and cpx.docStatus IN ('CO','CL')
|
|
WHERE
|
|
s.C_Project_ID=cp.C_Project_ID
|
|
AND s.paymentType IN ('CE','CI')
|
|
AND s.isActive='Y'
|
|
and s.DueDate::date <= fechaDesde - interval '1 day'*diadesde
|
|
and s.DueDate::date >= fechaDesde - interval '1 day'*diahasta
|
|
and k.paymentDate::date>=fechaDesde
|
|
and k.paymentDate::date<=fechaHasta
|
|
and al_pys.docstatus IN ('CO', 'CL')
|
|
and s.isActive='Y'
|
|
),0)) AS PagosCuotasVencidas
|
|
|
|
FROM C_Project cp
|
|
inner join tb_estadoContrato tbec on tbec.tb_estadocontrato_id = cp.tb_estadocontrato_id
|
|
WHERE cp.ad_org_id=organizacion
|
|
AND tbec.IsValid='Y' AND cp.IsActive='Y' AND tbec.IsActive='Y'
|
|
AND tbec.esVenta='Y'
|
|
AND cp.tipoFinanciamiento IN('DIR','CON','HIP')
|
|
and cp.CarteraRep_ID=oficial
|
|
AND cp.IsInDispute='N'
|
|
|
|
) into valor;
|
|
end if;
|
|
|
|
|
|
return valor;
|
|
end
|
|
$BODY$;
|
|
|
|
ALTER FUNCTION adempiere.calculo_comision_cobranza(numeric, numeric, character varying, date, date, integer, integer)
|
|
OWNER TO adempiere;
|
