Investeam

select cp.C_Project_ID::integer,
cp.PlannedAmt as valorContrato,
COALESCE)::integer,0)::INTEGER as dif_dias,

(select sum(cp1.PlannedAmt)
from C_Project cp1
inner join tb_estadoContrato est1 on est1.tb_estadoContrato_ID=cp1.tb_estadoContrato_ID
where cp1.tipo_negociacion=cp.tipo_negociacion

and cp1.salesRep_ID=cp.salesRep_ID 
 and cp1.tb_proyecto_ID=cp.tb_proyecto_ID 
 and est1.isValid='Y' and est1.IsSOTrx='Y'  and est1.isActive='Y' and est1.esVenta='Y' 
 and cp1.fechaVenta::date>='2025-01-18'::date and cp1.fechaVenta::date<='2025-02-20'::date  and cp1.tipoFinanciamiento in ('HIP', 'CON', 'DIR')  and cp.esVentaEstructurada='N'  ) as ventas,

(select count(cp1.value)
from C_Project cp1
  inner join tb_estadoContrato est1 on est1.tb_estadoContrato_ID=cp1.tb_estadoContrato_ID
 where cp1.tipo_negociacion=cp.tipo_negociacion

and cp1.salesRep_ID=cp.salesRep_ID
 and cp1.tb_proyecto_ID=cp.tb_proyecto_ID
 and est1.isValid='Y'  and est1.IsSOTrx='Y' and est1.isActive='Y' and est1.esVenta='Y' 
 and cp1.fechaVenta::date>='2025-01-18'::date and cp1.fechaVenta::date<='2025-02-20'::date  and cp1.tipoFinanciamiento in ('HIP', 'CON', 'DIR')  and cp1.esVentaEstructurada='N'  )::integer as unidades_ventas

, COALESCE((select rel.PlannedAmt 
 FROM C_Project rel
 where rel.C_Project_ID=cp.C_ProjectRelation_ID ),0) as valor_contratoRelacionado,  tram.DateDoc::date  as fecha_tramite, tbo.vmc as valorArrendamiento, 
 cp.DateContract::date as fecha_contrato, 
 cp.fechaVenta::date as fecha_venta, 
 COALESCE(cp.C_ProjectRelation_ID::Integer,0::Integer)::Integer 
 from C_Project cp
 INNER JOIN (
     SELECT cpl.c_project_ID as c_project_ID , mpx.m_product_ID as m_product_ID
     from c_projectLine cpl 
     inner join m_product mpx on mpx.m_product_ID=cpl.m_product_ID
     where mpx.isActive='Y' and mpx.bienInmueble='Y'
 ) cpl on cpl.c_project_ID=cp.C_Project_ID  inner join tb_estadoContrato est on est.tb_estadoContrato_ID=cp.tb_estadoContrato_ID
 left join tb_order_parameters tbo on tbo.C_Order_ID=cp.C_Order_ID  left join tb_tramiteNotariaCont tram on tram.C_Project_ID=cp.C_Project_ID and tram.tipoTramite IN ('PCVC', 'DPEO' ,'CONRES' ,'PMC') 
 where  cp.AD_Org_ID=1000000 and est.isValid='Y' and est.IsSOTrx='Y'  and est.isActive='Y'
 and ( CASE WHEN 'HIP'='HIP' THEN cp.tipoFinanciamiento in ('HIP', 'CON', 'DIR') ELSE cp.tipoFinanciamiento='HIP' END )  AND cp.salesRep_ID=1012697 and cp.tipo_negociacion='V'  and  CASE WHEN 0=0 THEN 1=1 ELSE cp.C_BPartner_ID=0 end  and est.esVenta='Y'  and est.issharedcommission='N' and cp.esVentaEstructurada='Y'  and cp.tb_proyecto_ID=1000000   and cp.fechaVenta::date >='2025-01-18'::date and cp.fechaVenta::date<='2025-02-20'::date [236]
17:15:06.723===========> CommissionCalc_ITSC.crearCalculoComision:  [236]
17:15:06.728===========> CommissionCalc_ITSC.crearCalculoComision: entro a bloque contrato referido con valor y venta estructurada Y [236]
17:15:06.734===========> CommissionCalc_ITSC.crearCalculoComision: queryObtenerReferidos  SELECT COALESCE(ROUND(cd.ActualAmt,2),0),  COALESCE(ROUND(cd.AmtMultiplier,2),0) 
 FROM C_CommissionDetail cd
 WHERE cd.C_Project_ID=1003498 and cd.tipopagocomision='PE'  [236]
17:15:06.741===========> CommissionCalc_ITSC.crearCalculoComision: nuevo valor de porcentaje_com con else 1.20 [236]
17:15:06.746===========> CommissionCalc_ITSC.crearCalculoComision: Seccion valorComisionEsquema Contrato y Relacionado [236]
17:15:06.752===========> CommissionCalc_ITSC.crearCalculoComision: valor de valorComisionEsquemaContrato 49237.18 [236]
17:15:06.758===========> CommissionCalc_ITSC.crearCalculoComision: valor de valorComisionEsquemaContratoRel 49237.18 [236]
17:15:06.764===========> CommissionCalc_ITSC.crearCalculoComision: valor de valorComisionEsquema 49237.18 [236]
17:15:06.778===========> CommissionCalc_ITSC.crearCalculoComision: porcentajePagado_Esq 0 [236]
17:15:06.785===========> CommissionCalc_ITSC.crearCalculoComision: valor de detail comission rate 0 [236]
17:15:06.792===========> CommissionCalc_ITSC.crearCalculoComision: >>>>>>esVentaEstructuradaLin Y [236]
17:15:06.797===========> CommissionCalc_ITSC.crearCalculoComision:  [236]
17:15:06.803===========> CommissionCalc_ITSC.crearCalculoComision:  Bloque venta estructurada Y [236]