Variables Red Flag
 Share
The version of the browser you are using is no longer supported. Please upgrade to a supported browser.Dismiss

 
$
%
123
 
 
 
 
 
 
 
 
 
ABCDEFGHIJKLOVWXYZAAABACAD
1
FuenteIndicador (EN)Indicador (ES)DescripciónEtapaIdoneidadRazónSummarySQL ScriptETLComentariosNombre Corto
2
Dozorro/TI/Chile Transparente
Unreasonable tender guarantee requirement (bid bond)
Requisito de boleta de garantía irrazonable
Required tender guarantee exceeds a threshold percentage of contract amount. High bid bond requirements can act as barriers to entry to smaller firms with limited access to capital.
1. Elaboración1Bonding requirement--Selects all bid bond requirements and removes duplicates, calculates required percentage within Spoon, assigns 0 values to any tenders with 0 denominator values to avoid dividing by zero
SELECT a.rbhCode, MAX(b.rgaAmount) AS MontoGarantia, MAX(a.rbhEstimatedAmount) AS MontoEstimado
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcRFBGuarantee b
ON a.rbhCode = b.rgaRFBCode
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
GROUP BY a.rbhCode
SiokRequisito Garantia
4
TITerm of the contract (long)Plazo del contrato (largo o indefinido)
This red flag indicator identifies a risk if the contracting authority announces an intention to conclude a contract for a definite term longer than four years.
4. Adjudicación1Contract lengthSELECT rbhCode, rbhID, rbhExternalCode, rbhContractDuration, rbhContractTime
FROM DCCPProcurement.dbo.prcRFBHeader
WHERE YEAR(rbhCreationDate) >= 2014
AND rbhProcessType = 1

SiokPlazo Contrato Largo
5
TIContract can be renewed (several times, or for a longer time, or without any information)Posibilidad de renovación de contratoThis red flag indicator is based on information on the renewability of contracts and signals situations as risky
when the number of possible renewals may be considered high (3 or more), or result in a contract of a longer
term (longer than 4 years).
1. Elaboración1Contract renewalSELECT rbhCode, rbhID, rbhOptionContratoRenovable
FROM DCCPProcurement.dbo.prcRFBHeader
WHERE YEAR(rbhCreationDate) >= 2014
AND rbhProcessType = 1
SiokContrato Renovable
6
OECD/OECDIncomplete evaluation criteriaCriterios de evaluación incompletosSelection and award criteria are not clearly defined or not disclosed in advance1. Elaboración1Evaluation criteria not defined--Counts evaluation criteria by tender; indicator triggered if no evaluation criteria associated with tender
SELECT a.rbhcode,
(CASE WHEN
COUNT(b.crclid) = 0 THEN 1
ELSE 0 END) AS CriteriosEvaluacionIncompletos
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcRegisterCriteriaClauseRFB b
ON a.rbhCode = b.rbcRFBCode
WHERE YEAR(rbhCreationDate) >= 2014
GROUP BY rbhCode
SiNo entendí revisemos estaCriterias Evaluacion Incompletos
7
TI/Chile TransparenteLos requisitos de experiencia sobrepasan un umbral razonableLos requisitos de experiencia sobrepasan un umbral razonableEl criterio experiencia no es garantía de la calidad de un bien o servicio y puede servir como una barrera de entrada para las empresas que buscan expandir sus oportunidades participando del abastecimiento del Estado 1. Elaboración1Experience requirement--Ya que no hay un campo numérico que describe la exigencia del requisito de experiencia, la variable indica solamente si la licitacion lleva un requisito de experiencia o no

SELECT a.rbhCode, (COUNT(DISTINCT CASE
WHEN b.rbrDescription LIKE '%experiencia%' THEN 1
ELSE 0 END)-1) AS RequisitoExperiencia
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcRFBRequisite b
ON a.rbhCode = b.rbrRFBCode
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
GROUP BY a.rbhCode
SiRequisito Experiencia
8
OECDUnjustified constraints hindering foreign competitionLimitaciones injustificadas para competencia extranjera
Lowering barriers to entry has the double advantage of both improving the efficiency of the bidding process and reducing the likelihood of successful collusion. Barriers to foreign participation in procurement should be eliminated
1. Elaboración1Foreign restrictions--Flags any tenders with nationality requirements, which may unduly limit competition
SELECT a.rbhCode, MAX(
CASE
WHEN LOWER(b.rbcTitle) LIKE '%nacionalidad%' THEN 1
ELSE 0 END) AS RequisitoNacionalidad
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcRFBClause b
ON a.rbhCode = b.rbcRFBCode
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
GROUP BY a.rbhCode
SiRequisito Nacionalidad
9
TITechnical capacity - setting geographical requirementsRequisitos geográficos
This red flag indicator is related (but not restricted) to technical capacity, and checks whether the contracting authority unjustifiably limits compliance with ability/capacity requirements by setting so called geographical requirements, e.g. the availability of capacities, equipment or site within a specific physical distance.
1. Elaboración1Geographical requirement--Flags any tenders with geographical requirements, which may unduly limit competition
SELECT a.rbhCode, MAX(
CASE
WHEN ((LOWER(b.rbcTitle) LIKE '%ubicacion%'
OR LOWER(b.rbcTitle) LIKE '%ubicación%'
OR LOWER(b.rbcTitle) LIKE '%localizacion%'
OR LOWER(b.rbcTitle) LIKE '%localización%'
OR LOWER(b.rbcTitle) LIKE '%geografico%'
OR LOWER(b.rbcTitle) LIKE '%geográfico%'
OR LOWER(b.rbcTitle) LIKE '%geografica%'
OR LOWER(b.rbcTitle) LIKE '%geografica%')
AND LOWER (b.rbcTitle) NOT LIKE '%croquis%') THEN 1
ELSE 0 END) AS RequisitoGeografico
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcRFBClause b
ON a.rbhCode = b.rbcRFBCode
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
GROUP BY a.rbhCode


SiRequisito Geografico
17
DozorroProcurement with amounts close to thresholdMonto estimado cerca de los umbrales legalesA "pre-threshold procedure" has been announced or a "report on concluded agreement" has been published with amounts close to threshold amounts: UAH 190,000 to 200,000 and UAH 1.49 million to 1.5 million for regular procuring entities, and UAH 990,000 to 1 million and 4.9 million to 5 million for "certain areas of activity."1. Elaboración1Price relative to legal thresholds--Selects estimated amount and date, joins with data grid in Spoon to get UTM conversion, then uses CASE statement to mark tenders with estimated amounts close to ChileCompra's threshold quantities (95-100, 995-1000, 1995-2000, 4995-5000)
SELECT rbhCode, rbhExternalCode, rbhCreationDate, rbhEstimatedAmount, rbhProcessSubType,
(CAST (YEAR(rbhCreationDate) AS VARCHAR(4)) + '-' + CAST (MONTH(rbhCreationDate) AS VARCHAR(2))) AS MonthYear
FROM DCCPProcurement.dbo.prcRFBHeader
WHERE YEAR(rbhCreationDate) >= 2014
AND rbhProcessType = 1
SiMonto Cerca Umbral
18
ChileCompraFragmentación ponderadaFragmentación ponderadaPorcentaje que representa una licitación en cuanto a cantidad de ítems sobre el total de las licitaciones por el mismo producto o servicio que ha realizado el organismo público.1. Elaboración1Price relative to past procurement--The first below select the total amount spent on each tender. The second query calculates the total amount spent per buying entity. Each tender spend amount is then divided by the buying entity's total to see the ratio (calculation done within Spoon).

SELECT a.rbhCode, a.IDUnidaddeCompra, SUM(b.MontoTotalOC) AS TenderSpend
FROM DM_Transaccional.dbo.THOportunidadesNegocio a
LEFT JOIN DM_Transaccional.dbo.THOrdenesCompra b
ON a.rbhCode = b.rbhCode
WHERE YEAR(a.FechaPublicacion) >= 2014
AND a.IDTipoON IN (1, 2, 3, 23, 24, 25)
GROUP BY a.rbhCode, a.IDUnidaddeCompra

SELECT a.IDUnidaddeCompra, SUM(a.MontoTotalOC) AS TotalSpend
FROM DM_Transaccional.dbo.THOrdenesCompra a
GROUP BY a.IDUnidaddeCompra
SiFragmentacion Ponderada
24
PwCBids after the deadline acceptedOfertas aceptadas después de la fecha límiteBids submitted after the admission deadline still accepted2. Licitación1Bids after deadline accepted--Selects tenders with bid(s) accepted after closing date
SELECT a.rbhCode
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcBIDQuote b
ON a.rbhCode = b.bidRFBCode
LEFT JOIN DCCPProcurement.dbo.prcRFBDate c
ON a.rbhCode = c.rbdRFBCode
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
AND b.bidTechnicalIssueDate > c.rbdTechnicalBidReception
GROUP BY a.rbhCode

--Selects all other tenders
SELECT a.rbhCode
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcBIDQuote b
ON a.rbhCode = b.bidRFBCode
LEFT JOIN DCCPProcurement.dbo.prcRFBDate c
ON a.rbhCode = c.rbdRFBCode
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
AND b.bidTechnicalIssueDate <= c.rbdTechnicalBidReception
GROUP BY a.rbhCode

--Within Spoon, first table's rows assigned 1, second table's rows assigned 2, both are combined through union to create a master table
SiHay que reformular, para dejar como variable binariaRecepcion Oferta Post Cierre
27
IACRC/PwCAll bids unreasonably highTodas las ofertas son demasiado altasAll bids higher than estimated overall costs2. Licitación1All bids too high--All bids counted and all bids higher than estimated costs counted. Binary column created in Spoon if all bids higher than estimated costs (i.e. BidCount = HighBidCount).
--Note that there are 430 tenders where bid currency does not match tender currency. These are excluded from the generation of the variable (but included in the Table as 0 values).
SELECT a.rbhCode, COUNT(b.bidID) AS BidCount, SUM(CASE
WHEN c.bitUnitNetPrice > a.rbhEstimatedAmount THEN 1
ELSE 0 END) AS HighBidCount
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcBIDQuote b
ON a.rbhCode = b.bidRFBCode
LEFT JOIN DCCPProcurement.dbo.prcBIDItem c
ON b.bidID = c.bitBID
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
AND a.rbhCurrency = c.bitCurrency
GROUP BY a.rbhCode
SiOfertas Todas Altas
29
IACRC/PwCAll bids unreasonably lowTodas las ofertas son demasiado bajasAll bids lower than estimated overall costs2. Licitación1All bids too low--All bids counted and all bids lower than estimated costs counted. Binary column created in Spoon if all bids lower than estimated costs (i.e. BidCount = LowBidCount)
--Note that there are 430 tenders where bid currency does not match tender currency. These are excluded from the generation of the variable (but included in the Table as 0 values).
SELECT a.rbhCode, COUNT(b.bidID) AS BidCount, SUM(CASE
WHEN c.bitUnitNetPrice < a.rbhEstimatedAmount THEN 1
ELSE 0 END) AS LowBidCount
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcBIDQuote b
ON a.rbhCode = b.bidRFBCode
LEFT JOIN DCCPProcurement.dbo.prcBIDItem c
ON b.bidID = c.bitBID
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
AND a.rbhCurrency = c.bitCurrency
GROUP BY a.rbhCode
SiOfertas Todas Bajas
34
Chile TransparenteEl organismo comprador responde de una manera concisa y oportuna a los reclamos de los proveedoresEl organismo comprador responde de una manera concisa y oportuna a los reclamos de los proveedoresEl tiempo entre el reclamo y su respuesta sobrepasa un umbral razonable2. Licitación1El organismo comprador responde de una manera concisa y oportuna a los reclamos de los proveedores--If response time exceeds threshold value of 336 hours (14 days, whereas average response time is 8.66 days), tender is marked 1 with binary variable
SELECT a.rbhCode, ((COUNT(DISTINCT
(CASE
WHEN DATEDIFF(HOUR, b.FechaAsignacionReclamoOOPP, c.FechaEnvio) > 336 THEN 1
ELSE 0 END)))-1) AS PlazoLargoRespuesta
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPReclamos.dbo.Reclamo b
ON a.rbhExternalCode = b.NumLicOC
LEFT JOIN DCCPReclamos.dbo.GestionInternaOOPP c
ON b.idReclamo = c.idReclamo
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
GROUP BY a.rbhCode
SiPlazo Largo Respuesta
36
ChileCompraInicio cierre preguntas fin de semanaInicio cierre preguntas fin de semanaVariable que indica si el inicio o cierre del periodo de realización de preguntas se da un fin de semana.2. Licitación1Inicio cierre preguntas fin de semanaSELECT rbhCode, rbhID "IDLic"
, rbhCreationDate "FechaLic"
, rbhExternalCode "CodLic"
, b.rbdQuestionClose "FechaCierrePreg"
, DATENAME(dw,b.rbdQuestionClose)
, (CASE DATENAME(dw,b.rbdQuestionClose)
WHEN 'Saturday' THEN 1
WHEN 'Sunday' THEN 1
ELSE 0
END) "FinDeSemana"
,(CASE WHEN (CONVERT(varchar(2), DAY(b.rbdQuestionClose))+ '-' + CONVERT(varchar(2), MONTH(b.rbdQuestionClose))+ '-' +RIGHT(CONVERT(varchar(4), YEAR(b.rbdQuestionClose)),2))
IN ('1-1-14', '18-4-14', '19-4-14', '1-5-14', '21-5-14', '7-6-14', '29-6-14', '16-7-14', '15-8-14', '20-8-14', '8-9-14', '18-9-14', '19-9-14', '20-9-14', '2-10-14', '12-10-14', '31-10-14', '1-11-14', '8-12-14', '25-12-14', '1-1-15', '3-4-15', '4-4-15', '1-5-15', '21-5-15', '7-6-15', '29-6-15', '16-7-15', '10-8-15', '15-8-15', '20-8-15', '18-9-15', '19-9-15', '12-10-15', '31-10-15', '1-11-15', '8-12-15', '25-12-15', '1-1-16', '25-3-16', '26-3-16', '1-5-16', '21-5-16', '7-6-16', '19-6-16', '27-6-16', '16-7-16', '10-8-16', '15-8-16', '20-8-16', '8-9-16', '18-9-16', '19-9-16', '20-9-16', '10-10-16', '23-10-16', '31-10-16', '1-11-16', '8-12-16', '25-12-16', '1-1-17', '2-1-17', '14-4-17', '15-4-17', '19-4-17', '1-5-17', '21-5-17', '7-6-17', '26-6-17', '2-7-17', '16-7-17', '10-8-17', '15-8-17', '20-8-17', '18-9-17', '19-9-17', '20-9-17', '21-9-17', '2-10-17', '9-10-17', '27-10-17', '1-11-17', '19-11-17', '8-12-17', '17-12-17', '25-12-17', '1-1-18', '16-1-18', '17-1-18', '18-1-18', '30-3-18', '31-3-18', '1-5-18', '21-5-18', '7-6-18', '2-7-18', '16-7-18', '15-8-18', '20-8-18', '17-9-18', '18-9-18', '19-9-18', '15-10-18', '1-11-18', '2-11-18', '8-12-18', '25-12-18', '1-1-19', '19-4-19', '20-4-19', '1-5-19', '21-5-19', '7-6-19', '29-6-19', '16-7-19', '15-8-19', '20-8-19', '18-9-19', '19-9-19', '20-9-19', '12-10-19', '31-10-19', '1-11-19', '8-12-19', '25-12-19')
THEN 1
ELSE 0
END) "Feriado"
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcRFBDate b
ON a.rbhCode = b.rbdRFBCode
WHERE YEAR(rbhCreationDate)>=2014
AND rbhProcessType = 1
SiokCierre Preguntas Finde
37
PwC/Chile CompraFuera de plazo mínimo de publicaciónFuera de plazo mínimo de publicaciónLa licitación no cumple con un estándar mínimo de días de publicación dado el tipo de licitación.2. Licitación1Legal bidding period violationSELECT rbhCode, rbhID, rbhCreationDate, rbhProcessSubType,
DATEDIFF(DAY, b.rbdOpeningDate, b.rbdTechnicalBidReception) AS PlazoPublicacion,
(CASE WHEN a.rbhProcessSubType = 1 AND DATEDIFF(DAY, b.rbdOpeningDate, b.rbdTechnicalBidReception) < 5 THEN 1
WHEN a.rbhProcessSubType = 2 AND DATEDIFF(DAY, b.rbdOpeningDate, b.rbdTechnicalBidReception) < 10 THEN 1
WHEN a.rbhProcessSubType = 3 AND DATEDIFF(DAY, b.rbdOpeningDate, b.rbdTechnicalBidReception) < 20 THEN 1
WHEN a.rbhProcessSubType = 24 AND DATEDIFF(DAY, b.rbdOpeningDate, b.rbdTechnicalBidReception) < 20 THEN 1
WHEN a.rbhProcessSubType = 25 AND DATEDIFF(DAY, b.rbdOpeningDate, b.rbdTechnicalBidReception) < 30 THEN 1
ELSE 0 END) AS PlazoPublicacionIlegal
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcRFBDate b
ON a.rbhCode = b.rbdRFBCode
WHERE YEAR(a.rbhCreationDate) >= 2014
AND rbhProcessSubType IN (1, 2, 3, 24, 25)
SiokPlazo Publicacion Ilegal
44
PwC/EU/OCP/TI/OECDNumber of bidsCantidad de ofertasNumber of bidders significantly less than average for similar item or procuring entity2. Licitación1Number of bids--Selects bid counts for all tenders
SELECT a.rbhCode, a.rbhOrganization, COUNT(b.bidID) AS BidCount
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcBIDQuote b
ON a.rbhCode = b.bidRFBCode
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
GROUP BY a.rbhCode, a.rbhOrganization
ORDER BY a.rbhOrganization

--Selects bid counts for all tenders, then grouped by organization and averaged within Spoon, then combined with above table and tender bid count is divided by average bid count per buying entity
SELECT a.rbhCode, a.rbhOrganization, COUNT(b.bidID) AS BidCount
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcBIDQuote b
ON a.rbhCode = b.bidRFBCode
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
GROUP BY a.rbhCode, a.rbhOrganization
ORDER BY a.rbhOrganization

SiDesviacion Cantidad Ofertas Promedio Comprador
45
ChileCompraPorcentaje reclamos por irregularidad sin respuestaPorcentaje reclamos por irregularidad sin respuestaPorcentaje de reclamos por presunta irregularidad del proceso que quedan sin respuesta por parte del comprador.2. Licitación1Porcentaje reclamos por irregularidad sin respuesta--Three tables are merged in Spoon and NULL values converted to zeroes
SELECT a.rbhCode, a.rbhExternalCode, (CONVERT(FLOAT,(COUNT(b.idReclamo)))) AS TodosReclamos
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPReclamos.dbo.Reclamo b
ON a.rbhExternalCode = b.NumLicOC
WHERE YEAR(a.rbhCreationDate) >= 2014
AND b.idMotivoReclamo <> 1
AND b.idMotivoReclamo <> 19
AND a.rbhProcessType = 1
GROUP BY a.rbhCode, a.rbhExternalCode

SELECT a.rbhExternalCode, (CONVERT(FLOAT,(COUNT(b.idReclamo)))) AS ReclamosSinRespuestas
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPReclamos.dbo.Reclamo b
ON a.rbhExternalCode = b.NumLicOC
WHERE YEAR(a.rbhCreationDate) >= 2014
AND b.idMotivoReclamo <> 1
AND b.idMotivoReclamo <> 19
AND b.RespuestaOOPP IS NULL
AND a.rbhProcessType = 1
GROUP BY rbhExternalCode

SELECT rbhCode
FROM DCCPProcurement.dbo.prcRFBHeader
WHERE YEAR(rbhCreationDate) >= 2014
AND rbhProcessType = 1
SiRevisarPorcentaje Reclamos Irreg Sin Respuestas
47
ChileCompraRatio días de publicación preguntasRatio días de publicación preguntasRatio que representa la relación entre la cantidad de días para la realización de preguntas, respecto al total de días de publicación de la licitación.2. Licitación1Ratio días de publicación preguntasSELECT rbhID, rbhCode, rbhCreationDate, rbhExternalCode, b.rbdRFBCode, b.rbdQuestionOpening, b.rbdQuestionClose, b.rbdDateInicio, b.rbdDateFin,
(DATEDIFF(day, b.rbdQuestionOpening, b.rbdQuestionClose)) AS QuestionPeriod,
(DATEDIFF(day, a.rbhCreationDate, b.rbdTechnicalBidReception)) AS TenderPeriod,
(Convert(Float,((DATEDIFF(day, b.rbdQuestionOpening, b.rbdQuestionClose)))))/(CONVERT(FLOAT,((DATEDIFF(day, a.rbhCreationDate, b.rbdTechnicalBidReception))))) AS QuestionPeriodRatio
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcRFBDate b
ON a.rbhCode = b.rbdRFBCode
WHERE YEAR(rbhCreationDate)>=2014
AND (CONVERT(FLOAT,((DATEDIFF(day, a.rbhCreationDate, b.rbdTechnicalBidReception))))) <> 0
AND a.rbhProcessType = 1
SiokRatio Plazo Preguntas
48
ChileCompraRatio reclamos irregularidad de ofertasRatio reclamos irregularidad de ofertasRatio que representa la relación entre los reclamos ingresados por presunta irregularidad respecto al total de ofertas recepcionadas.2. Licitación1Ratio reclamos irregularidad de ofertasSELECT a.rbhcode, (CONVERT(FLOAT,(COUNT(b.bidID)))) AS 'TotalOfertas'
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcBIDQuote b
ON a.rbhCode = b.bidRFBCode
LEFT JOIN DCCPReclamos.dbo.Reclamo c
ON a.rbhExternalCode = c.numLicOC
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
GROUP BY a.rbhcode

SELECT a.rbhcode, (CONVERT(FLOAT,(COUNT(DISTINCT c.idReclamo)))) AS 'ReclamosIrregularidad'
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcBIDQuote b
ON a.rbhCode = b.bidRFBCode
LEFT JOIN DCCPReclamos.dbo.Reclamo c
ON a.rbhExternalCode = c.numLicOC
WHERE YEAR(a.rbhCreationDate) >= 2014
AND c.idMotivoReclamo <> 1
AND c.idMotivoReclamo <> 19
AND a.rbhProcessType = 1
GROUP BY a.rbhcode


SiRevisarRatio Reclamos Irreg Ofertas
49
ChileCompraRatio reclamos pago ofertasRatio reclamos pago ofertasRatio que representa la relación entre los reclamos ingresados por problemas de pago, respecto al total de ofertas recepcionadas.2. Licitación1Ratio reclamos pago ofertasSELECT a.rbhCode, (CONVERT(FLOAT,(COUNT(DISTINCT c.idReclamo)))) AS 'ReclamosPago'
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcBIDQuote b
ON a.rbhCode = b.bidRFBCode
LEFT JOIN DCCPReclamos.dbo.Reclamo c
ON a.rbhExternalCode = c.numLicOC
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
AND (c.idMotivoReclamo = 1
OR c.idMotivoReclamo = 19)
GROUP BY a.rbhCode

SELECT a.rbhCode, (CONVERT(FLOAT,(COUNT(b.bidID)))) AS 'TotalOfertas'
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcBIDQuote b
ON a.rbhCode = b.bidRfBCode
LEFT JOIN DCCPReclamos.dbo.Reclamo c
ON a.rbhExternalCode = c.numLicOC
WHERE YEAR (a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
GROUP BY a.rbhCode
SiRevisarRatio Reclamos Pago Ofertas
53
OCPSingle bid receivedSolo una oferta recibidaTender featured a single bidder only2. Licitación1Single bid per tenderSELECT rbhCode, (COUNT(b.bidID)) AS Ofertas,
(CASE WHEN (COUNT(b.bidID)) = 1 THEN 1 ELSE 0 END) AS UnicaOferta
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcBIDQuote b
ON a.rbhCode = b.bidRFBCode
WHERE YEAR (a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
GROUP BY rbhCode

SiokUnica Oferta
54
EUSingle BiddingPorcentaje de licitaciones con una oferta% contract awards with one bid submitted over all contract awards (competitive markets only)2. Licitación1Single bids per entity--Creates temporary table in Postgre with counts of bids by tender
SELECT a.rbhCode, a.rbhOrganization, (CASE WHEN
((COUNT(b.bidID)) = 1) THEN 1
ELSE 0 END) AS SingleBidTenders
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcBIDQuote b
ON a.rbhCode = b.bidRFBCode
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
GROUP BY a.rbhCode, a.rbhOrganization
ORDER BY a.rbhOrganization

--Counts single-bid tenders per buying entity
SELECT rbhorganization, COUNT(singlebidtenders) AS SingleBidTenderCount
FROM public.temp_porcentajeunicaoferta1
WHERE singlebidtenders = 1
GROUP BY rbhorganization

--Counts all tenders per buying entity (percentage calculated in Spoon)
SELECT rbhorganization, COUNT(rbhcode) AS AllTenderCount
FROM public.temp_porcentajeunicaoferta1
GROUP BY rbhorganization

--Selects all tenders, which are then joined to the previous tables based on buying entity. Each tender is assigned the percentage calculated for its buying entity.
SELECT rbhCode, rbhOrganization
FROM DCCPProcurement.dbo.prcRFBHeader
WHERE YEAR(rbhCreationDate) >= 2014
AND rbhProcessType = 1



SiPorcentaje Unica Oferta
55
ChileCompraTasa de respuestaTasa de respuestaTasa de respuesta a preguntas realizadas por los proveedores a través del foro.2. Licitación1Tasa de respuesta--Both tables merged in Spoon. Only 16 licitaciones have data available in the 'Foro de Preguntas'. For each of these, a response rate is calculated. For all other licitaciones, the TasaRespuesta campo is left null.
SELECT a.rbhCode, (CONVERT(FLOAT,(COUNT(c.prgBidID)))) AS 'TotalPreguntas'
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcBIDQuote b
ON a.rbhCode = b.bidRFBCode
LEFT JOIN DCCPProcurement.dbo.prcForoPreguntaCS c
ON b.bidID = c.prgBidID
LEFT JOIN DCCPProcurement.dbo.prcForoRespuestaCS d
ON d.rpsIDPregunta = c.prgID
WHERE YEAR (a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
GROUP BY a.rbhCode

SELECT a.rbhCode, (CONVERT(FLOAT,(COUNT(c.prgBidID)))) AS 'PreguntasConRespuestas'
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcBIDQuote b
ON a.rbhCode = b.bidRFBCode
LEFT JOIN DCCPProcurement.dbo.prcForoPreguntaCS c
ON b.bidID = c.prgBidID
LEFT JOIN DCCPProcurement.dbo.prcForoRespuestaCS d
ON d.rpsIDPregunta = c.prgID
WHERE YEAR (a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
AND d.rpsRespuesta IS NOT NULL
GROUP BY a.rbhCode
SiRevisarTasa Respuesta
67
TI/OECDLong decision periodPlazo largo de decisiónUnreasonable delays in evaluating bids and selecting winner (longer than 90 days)3. Evaluación1Long decision period--Red flag variable triggered if decision period longer than 90 days (22 days being mean decision period)
SELECT a.rbhCode,
(CASE WHEN
(DATEDIFF(DAY, b.rbdTechnicalBidOpening, b.rbdAwardDate)) > 90 THEN 1
ELSE 0 END) AS PlazoDecisionLargo
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcRFBDate b
ON a.rbhcode = b.rbdRFBCode
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
SiokPlazo Decision Largo
71
ChileCompraPorcentaje precioPorcentaje precioPorcentaje que representa el precio como criterio de evaluación de la licitación.3. Evaluación1Porcentaje precio--Sums instances in which price criteria is greater than 60% of deciding factor for tender (not a binary variable)
SELECT a.rbhCode,
(SUM(CASE
WHEN b.rbaPercentage <= 60 then 0
WHEN b.rbaPercentage > 60 then 1
ELSE 0
END))
AS PorcentajePrecioAlto
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcRFBAwardCriteria b
ON a.rbhCode = b.rbaRFBCode
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
GROUP BY a.rbhCode
SiokPorcentaje Precio Alto
73
OCPDecision period for submitted bids excessively shortPlazo corto de decisiónDecision Period for Submitted Bids Excessively Short3. Evaluación1Short decision period--Red flag variable triggered if decision period shorter than 8 hours (in first percentile)
SELECT a.rbhCode,
(CASE WHEN
(DATEDIFF(HOUR, b.rbdTechnicalBidOpening, b.rbdAwardDate)) < 8 THEN 1
ELSE 0 END) AS PlazoDecisionCorto
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcRFBDate b
ON a.rbhcode = b.rbdRFBCode
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
SiokPlazo Decision Corto
76
OCPSupplier has abnormal address or phone numberDirección anormal del proveedorSupplier address is PO box with no address or phone number3. Evaluación1Supplier has abnormal address or phone number--Creates a binary variable for all suppliers with abormal addresses. Rows are then grouped by rbhCode in Spoon.
SELECT a.rbhCode, (COUNT(DISTINCT
CASE
WHEN c.eadAddress IS NULL THEN 1
WHEN LEN(c.eadAddress) < 10 THEN 1
WHEN c.eadPhone IS NULL THEN 1
WHEN c.eadPhone NOT LIKE '%[0-9][0-9][0-9]%' THEN 1
WHEN LEN(c.eadPhone) < 7 THEN 1
ELSE 0 END)-1) AS DireccionAnormal
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcPOHeader b
ON a.rbhCode = b.porSourceDocumentNumber
LEFT JOIN DCCPPlatform.dbo.gblEnterpriseAddress c
ON b.porSellerEnterprise = c.eadEnterprise
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
GROUP BY a.rbhCode
Direccion Anormal
77
ChileCompraTiempo entre cierre y adjudicación estimado acotadoTiempo entre cierre y adjudicación estimado acotadoLa cantidad de días entre el cierre y adjudicación es acotado en función del tipo de licitación.3. Evaluación1Tiempo entre cierre y adjudicación estimado acotado--Data Source
SELECT AVG(DATEDIFF(DAY, b.rbdTechnicalBidReception, b.rbdAwardDate)) AS PlazoPromedio
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcRFBDate b
ON a.rbhCode = b.rbdRFBCode
WHERE rbhProcessSubType IN (1, 2, 3, 24, 25)
GROUP BY rbhProcessSubType

--Static Averages (valores denominadores vienen de la primera consulta)
SELECT TOP 500 rbhCode, rbhID, rbhCreationDate, rbhProcessSubType,
DATEDIFF(DAY, b.rbdTechnicalBidReception, b.rbdAwardDate) AS PlazoDecision,
(CASE WHEN a.rbhProcessSubType = 1 THEN (CONVERT(FLOAT,((DATEDIFF(DAY, b.rbdTechnicalBidReception, b.rbdAwardDate))))/ 17)
WHEN a.rbhProcessSubType = 2 THEN (CONVERT(FLOAT,((DATEDIFF(DAY, b.rbdTechnicalBidReception, b.rbdAwardDate))))/ 25)
WHEN a.rbhProcessSubType = 3 THEN (CONVERT(FLOAT,((DATEDIFF(DAY, b.rbdTechnicalBidReception, b.rbdAwardDate))))/ 43)
WHEN a.rbhProcessSubType = 24 THEN (CONVERT(FLOAT,((DATEDIFF(DAY, b.rbdTechnicalBidReception, b.rbdAwardDate))))/ 46)
WHEN a.rbhProcessSubType = 25 THEN (CONVERT(FLOAT,((DATEDIFF(DAY, b.rbdTechnicalBidReception, b.rbdAwardDate))))/ 63)
ELSE 0 END) AS DesviacionPlazoDecision
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcRFBDate b
ON a.rbhCode = b.rbdRFBCode
WHERE YEAR(a.rbhCreationDate) >= 2014
AND rbhProcessType = 1
SiPlazo Decision Segun Tipo
87
OCPBidder that has never bid previously wins tenderOferente novato ganaSupplier has never submitted a bid for another contract4. Adjudicación1Bidder that has never bid previously wins tender--Queries all purchase orders and tags by tender code and seller organization
SELECT a.rbhCode, b.porID, b.porSellerOrganization
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcPOHeader b
ON a.rbhCode = b.porSourceDocumentNumber
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
GROUP BY a.rbhCode, b.porID, b.porSellerOrganization

--Counts awarded contracts to each selling organization
SELECT a.bidOrganization, COUNT(a.BidIsAwarded) AS Awards
FROM DCCPProcurement.dbo.prcBIDQuote a
GROUP BY a.bidOrganization

--Both tables are joined and then a column is created to highlight first time winners
SELECT rbhcode,
(COUNT(DISTINCT(CASE
WHEN AWARDS <= 1 THEN 1
ELSE 0 END))-1) AS OferenteNovatoGana
FROM public.temp_oferentenovatogana1
GROUP BY rbhCode
SiOferente Novato Gana
88
IACRC/PwC/OCPComplaints from non-winning biddersPresencia de reclamosAny complaints (formal or informal) from non-winning bidders4. Adjudicación1Complaints from non-winning bidders--First code operates only on non-winning bids, second returns all bids
SELECT a.rbhCode,
(CASE WHEN
COUNT(DISTINCT c.idReclamo) > 0 THEN 1
ELSE 0 END) AS ReclamoPerdedor
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcBIDQuote b
ON a.rbhCode = b.bidRFBCode
LEFT JOIN DCCPReclamos.dbo.Reclamo c
ON a.rbhExternalCode = c.NumLicOC
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
AND b.bidIsAwarded = 0
GROUP BY a.rbhCode

SELECT a.rbhCode
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcBIDQuote b
ON a.rbhCode = b.bidRFBCode
LEFT JOIN DCCPReclamos.dbo.Reclamo c
ON a.rbhExternalCode = c.NumLicOC
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
GROUP BY a.rbhCode
Sihay que dejarlo en forma binaria, se estan excluyendo algunas licitaciones, que no tienen bidIsAwarded 0Reclamo Perdedor
89
OCP/DozorroDelay between contract award and signingRetraso entre la adjudicación y la firma del contractoTime between contract award and actual contract signing date (should be less than 3 months according to World Bank) exceeds a reasonable threshold. Note: Actual signing date not available in database, so calculation uses estimated signing date as proxy.4. Adjudicación1Delay between award and signingSELECT a.rbhCode, a.rbhExternalCode,
(CASE WHEN
(DATEDIFF(DAY, b.rbdAwardDate, b.rbdEstimatedContractSign)) >= 90 THEN 1
ELSE 0 END )
AS PlazoLargoAdjudicacionFirma
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcRFBDate b
ON a.rbhCode = b.rbdRFBCode
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
SiokPlazo Largo Adjudicacion Firma
90
EU/OCP/TIDeviation of final contract value from estimate from estimated valueDivergencia entre monto final y monto estimadoRatio of the total final value and the estimated value is not within a reasonable range Difference between the final value and the estimated value exceeds a reasonable threshold (30% according to OCP)4. Adjudicación1Deviation of contract value from estimateSELECT rbhCode, SUM(rbhEstimatedAmount) AS MontoEstimado, SUM(b.porTotalAmount) AS MontoAdjudicado,
(SUM(b.porTotalAmount)/NULLIF((SUM(rbhEstimatedAmount)), 0)) AS Desviacion
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcPOHeader b
ON a.rbhCode = b.porSourceDocumentNumber
WHERE YEAR(rbhCreationDate) >= 2014
AND rbhProcessType = 1
GROUP BY rbhCode
SiokDesviacion Monto Adjudicado
94
EUForeign SuppliersProveedores extranjeros% contract awards to firms headquartered in a foreign country. The share of local and foreign bidders aims to signal that when at least some bidders come from outside the immediate locality the buyer is located in, competition is expected to be more intense due to more diverse companies competing.4. Adjudicación1Foreign awards--First two queries represent count of purchase orders with foreign and all suppliers grouped by purchasing entity (enterprise level).
--These tables are then merged in Spoon, a percentage of foreign suppliers is calculated for each entity, and this table is in turn merged with the tenders table.
SELECT a.porBuyerEnterprise, COUNT (c.eadCountry)
FROM DCCPProcurement.dbo.prcPOHeader a
LEFT JOIN DCCPPlatform.dbo.gblEnterprise b
ON a.porSellerEnterprise = b.entCode
LEFT JOIN DCCPPlatform.dbo.gblEnterpriseAddress c
ON b.entCode = c.eadEnterprise
LEFT JOIN DCCPProcurement.dbo.prcRFBHeader d
ON a.porSourceDocumentNumber = d.rbhCode
WHERE c.eadCountry <> 'CL'
GROUP BY porBuyerEnterprise

SELECT a.porBuyerEnterprise, COUNT (c.eadCountry)
FROM DCCPProcurement.dbo.prcPOHeader a
LEFT JOIN DCCPPlatform.dbo.gblEnterprise b
ON a.porSellerEnterprise = b.entCode
LEFT JOIN DCCPPlatform.dbo.gblEnterpriseAddress c
ON b.entCode = c.eadEnterprise
LEFT JOIN DCCPProcurement.dbo.prcRFBHeader d
ON a.porSourceDocumentNumber = d.rbhCode
GROUP BY porBuyerEnterprise

SELECT rbhCode, rbhEnterprise, rbhOrganization
FROM DCCPProcurement.dbo.prcRFBHeader
WHERE YEAR(rbhCreationDate) >= 2014
SiProveedores Extranjeros
95
OCPHigh number of contract awards to one bidderCantidad alta de adjudicaciones a un oferenteSupplier receives more than a threshold value of awards from one Procuring Entity during a given time period4. Adjudicación1High number of contract awards to one bidder--Counts each supplier's contract awards per procuring entity (average is 10)
SELECT porSellerOrganization, porBuyerOrganization, COUNT(porID) AS ContractAwards
FROM DCCPProcurement.dbo.prcPOHeader
GROUP BY porSellerOrganization, porBuyerOrganization

--Generates list of all tenders to be joined with above table in Spoon, then ContractAwards > 20 flagged with variable (count, not binary)
SELECT a.rbhCode, b.porSellerOrganization
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcPOHeader b
ON a.rbhCode = b.porSourceDocumentNumber
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
GROUP BY a.rbhCode, b.porSellerOrganization
SiProveedor Contratos Numerosos
104
OECDPenalty clauses missing from contractFalta de cláusulas de penalizaciónPenalty clauses missing from contract4. Adjudicación1Penalty clauses missing from contract--Busca la ausencia de clausulas de penalizcion utilizando palabras claves. Si una licitación falta estas clausulas, lleva 1; si tiene estas clausulas, lleva 0 (variable binaria creada en Pentaho)
SELECT a.rbhCode,
SUM(CASE WHEN rbcTitle LIKE '%penal%' THEN 1
WHEN rbcTitle LIKE '%sancion%' THEN 1
WHEN rbcTitle LIKE '%multa%' THEN 1
WHEN rbcTitle LIKE '%castigo%' THEN 1
WHEN rbcTitle LIKE '%prohibicion%' THEN 1
WHEN rbcTitle LIKE '%punicion%' THEN 1
WHEN rbcTitle LIKE '%correctivo%' THEN 1
WHEN rbcTitle LIKE '%recompensa%' THEN 1
WHEN rbcTitle LIKE '%escarmiento%' THEN 1
WHEN rbcTitle LIKE '%imposicion%' THEN 1
WHEN rbcTitle LIKE '%recargo%' THEN 1
WHEN rbcTitle LIKE '%gravamen%' THEN 1
WHEN rbcTitle LIKE '%amonestacion%' THEN 1
ELSE 0 END) AS ClausulaPenalizacion
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcRFBClause b
ON a.rbhCode = b.rbcRFBCode
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
GROUP BY a.rbhCode
SiClausula Penalizacion
111
TISuccessful procedure without contractingProcedimiento exitoso sin adjudicación
This red flag indicator signals a risk if the public procurement procedure was successful (there is a winner), but the parties still do not conclude the contract based on the procedure. (The contracting authority has to inform also about this fact and its reasons in the notice.) The occurrence of this situation may be lawful, but – in line with indicator no. 5 above (one of the cases of unsuccessful procedure) – it carries a significant risk, especially because the prerequisites of a relief of the contracting obligation are strict, and in such a case the entity who won the procedure is already known.
4. Adjudicación1Successful procedure without contracting--rbhDocumentStatus señala si ha estado adjudicado la licitación, mientras que el COUNT(b.porID) verifica si hay contratos vinculados a la licitación
SELECT a.rbhCode, a.rbhDocumentStatus, COUNT(b.porID) AS Contratos,
CASE WHEN
a.rbhDocumentStatus IN (8,9,10) AND COUNT(b.porID) = 0 THEN 1
ELSE 0 END AS AdjudicadoSinContratar
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcPOHeader b
ON a.rbhCode = b.porSourceDocumentNumber
WHERE a.rbhProcessType = 1
AND YEAR(a.rbhCreationDate) >= 2014
GROUP BY a.rbhCode, a.rbhDocumentStatus
SiAdjudicado Sin Contratar
114
DozorroTender cancelled when there is a complaint about the decision on the winnerLicitación cancelada por causa de reclamosTender cancelled when there is a complaint about the decision on the winner selection or pre-qualification results with a non-terminal status, that is "Awaits consideration" and "Under consideration". Indicator triggered if cancelled tender bears three or more submitted complaints.4. Adjudicación1Tender cancelled when there is a complaint about the decision on the winnerSELECT a.rbhExternalCode, (
CASE
WHEN
(COUNT(DISTINCT b.idReclamo)) > 3
AND
a.rbhDocumentStatus IN (1, 7, 15, 16) THEN 1
ELSE 0 END)
AS 'AnulacionReclamo'
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPReclamos.dbo.Reclamo b
ON a.rbhExternalCode = b.numLicOC
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
GROUP BY a.rbhExternalCode, a.rbhDocumentStatus
SiRevisar, porque no de vista como otrosAnulacion Reclamo
118
IACRC/OCP/TIFinal value of the contract is too high relative to purchasing entity's historicl averageValor final del contrato demasiado altoPayment of unjustified high prices relative to historical average of purchasing entity4. Adjudicación1Unjustified high price--Selects average tender amount for each of 6661 purchasing organizations.
--Within Spoon, grouped by buying organization only (i.e., AVG(SUM(MontoTotalOC)) taken to figure out final value).
SELECT a.IDUnidaddeCompra, a.rbhCode, SUM(MontoTotalOC) AS MontoLicitacion
FROM DM_Transaccional.dbo.THOrdenesCompra a
GROUP BY a.IDUnidaddeCompra, a.rbhCode

--Selects all tenders and inflation/currency-adjusted amounts. Later merged within Spoon with ChileCompra tenders with proper date and LP filters applied.
--Calculator takes ratio of tender total to entity average and, if over 2, flags the tender with a binary variable value of 1.
SELECT a.rbhCode, SUM(MontoTotalOC) AS MontoLicitacion
FROM DM_Transaccional.dbo.THOrdenesCompra a
GROUP BY a.rbhCode
SiDesviacion Monto Promedio Comprador
119
TIUnsuccessful procedure without statement of reasonProcedimiento desierto sin justificación ha sido desierta sin justificación
The red flag indicator signals, if the information notice does not specify the reason for the procedure’s lack of success, even though the procedure is unsuccessful (no winner declared).
4. Adjudicación1Unsuccessful procedure without statement of reasonSELECT rbhCode, rbhExternalCode, rbhDocumentStatus, rbhRevokeJustify,
(CASE WHEN
(rbhDocumentStatus = 7 OR rbhdocumentstatus = 15)
AND rbhRevokeJustify IS NULL THEN 1
ELSE 0 END) AS DesiertaSinJustificacion
FROM DCCPProcurement.dbo.prcRFBHeader
WHERE YEAR(rbhCreationDate) >= 2014
SiokDesierta Sin Justificacion
120
IACRCWinning Bid Very Close to Cost EstimatesA winning bid that is too close to confidential project cost estimates or budgets can indicate the leaking of bid information or an unbalanced bidding scheme. Both schemes are usually the result of corruption, as project officials provide such information to a favored bidder to enable it to win. This indicator is triggered if the winning bid is A winning bid that is too close to confidential project cost estimates or budgets can indicate the leaking of bid information or an unbalanced bidding scheme. Both schemes are usually the result of corruption, as project officials provide such information to a favored bidder to enable it to win. This indicator is triggered if the winning bid is within ± 15% of the buying entity's cost estimate. 4. Adjudicación1Winning bid very close to cost estimateSELECT a.rbhCode,
COUNT((CASE WHEN
(c.bitQuantity * c.bitUnitNetPrice)
BETWEEN (0.85 * a.rbhEstimatedAmount) AND (1.15 * a.rbhEstimatedAmount)
AND (c.bitIsSelected = 1 OR c.bitIsSelected = 2)
THEN 1
ELSE 0 END)) AS SimilitudOfertaEstimacion
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcBIDQuote b
ON a.rbhCode = b.bidRFBCode
LEFT JOIN DCCPProcurement.dbo.prcBIDItem c
ON b.bidID = c.bitBID
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
GROUP BY a.rbhCode
SiRevisar, lógicaDesviacion Oferta Estimacion
122
EUProcedures divided into lotsLicitación adjudicada a varias empresas
Lots are beneficial in particular for smaller firms (SMEs) because they can allow small, specialised companies to make an offer. Low percentages indicate that procurements mostly allow large companies to bid and public buyers are missing out on the opportunities smaller firms can offer
4. Adjudicación1--Distinct sellers counted and grouped by tender. If more than 1, triggers binary variable for 1. If 1, triggers binary variable for 0.
--Note that in this case a 1 variable reflects positively on the tendering process.
SELECT a.rbhCode, COUNT(DISTINCT b.porSellerOrganization) AS ContractedSellers
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcPOHeader b
ON a.rbhCode = b.porSourceDocumentNumber
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
GROUP BY a.rbhCode
SiVarios Proveedores
123
EUMissing seller registration numbersCódigo proveedor ausente
That public buyers are providing insufficient information about the seller of goods, works or services they selected following a procurement procedure.
4. Adjudicación1--Counts contracts with missing seller registration numbers, grouped by tender. Not a binary variable, but a sum of all missing seller registrations.
SELECT a.rbhCode, SUM(CASE
WHEN b.porSellerOrganization IS NULL THEN 1
ELSE 0 END
) AS CodigoProveedorAusente
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcPOHeader b
ON a.rbhCode = b.porSourceDocumentNumber
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
GROUP BY a.rbhCode
SiCodigo Proveedor Ausente
124
EUMissing buyer registration numberesCódigo comprador ausenteThat public buyers are providing insufficient information about their procurement.4. Adjudiación1--Counts contracts with missing buyer registration numbers, grouped by tender. Note, not a binary variable.
SELECT a.rbhCode, SUM(CASE
WHEN b.porBuyerOrganization IS NULL THEN 1
ELSE 0 END
) AS CodigoCompradorAusente
FROM DCCPProcurement.dbo.prcRFBHeader a
LEFT JOIN DCCPProcurement.dbo.prcPOHeader b
ON a.rbhCode = b.porSourceDocumentNumber
WHERE YEAR(a.rbhCreationDate) >= 2014
AND a.rbhProcessType = 1
GROUP BY a.rbhCode
SiCodigo Comprador Ausente
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
Loading...