Pharmatech Chile SpA

Cenabast Government API Integration Integración API Gubernamental Cenabast

Full Technical Implementation Details Detalles Técnicos Completos de la Implementación

Project Overview Resumen del Proyecto

Spearheaded the technical planning, cross-functional strategic meetings, and the development of bidirectional API communications bridging the Chilean Ministry of Health (Cenabast) systems with the corporate Softland ERP. Lideré la planificación técnica, reuniones estratégicas y el desarrollo de comunicaciones API bidireccionales uniendo los sistemas del Ministerio de Salud (Cenabast) con el ERP corporativo Softland.

To enforce strict government compliance and regulatory SLAs, I engineered and optimized comprehensive SQL queries. This automation efficiently extracted, validated, and synchronized thousands of electronic invoices and dispatch guides, eliminating heavy manual data entry errors. Diseñé y optimicé consultas SQL integrales para hacer cumplir la normativa gubernamental. Esta automatización extrajo, validó y sincronizó miles de facturas electrónicas y guías de despacho, eliminando graves errores manuales.

Core Extraction Query Consulta Principal de Extracción

Below is the core SQL routine programmed to automatically extract and validate both electronic invoices ('F') and dispatch guides ('S') from Softland ERP, ensuring they meet the strict government compliance requirements before syncing. A continuación se muestra la rutina SQL principal programada para extraer y validar automáticamente las facturas electrónicas ('F') y las guías de despacho ('S') del ERP Softland, garantizando el cumplimiento normativo estricto del gobierno antes de la sincronización.

cenabast_invoice_sync.sql


SELECT
    nventa.RetiradoPor AS Doc_Cenabast,
    CASE WHEN gsaen.Tipo = 'F' THEN '1' ELSE '0' END as FacturaGuia,
    CONVERT(int, gsaen.folio) AS Folio,
    CONVERT(datetime, gsaen.Fecha) AS FechaCreacion,
    CONVERT(varchar(30), gmovi.DetProd) AS Producto,
    CONVERT(varchar(30),gmovi.Partida) AS Lote,
    CONVERT(int, gmovi.CantFacturada) AS Cantidad
FROM softland.iw_gsaen gsaen
INNER JOIN softland.iw_gmovi gmovi
    ON gsaen.NroInt = gmovi.NroInt AND gsaen.Tipo = gmovi.Tipo
INNER JOIN softland.nw_nventa nventa
    ON nventa.NVNumero = gsaen.nvnumero
WHERE gsaen.Fecha BETWEEN CONVERT(datetime, @fecha_inicio, 103) AND CONVERT(datetime, @fecha_termino, 103)
  AND gsaen.CodBode = '03'
  AND gsaen.tipo = 'F'
  AND gsaen.SubTipoDocto = 'T'
  AND LEN(LTRIM(RTRIM(nventa.RetiradoPor))) > 0

UNION ALL

SELECT
    nventa.RetiradoPor AS Doc_Cenabast,
    CASE WHEN gsaen.Tipo = 'F' THEN '1' ELSE '0' END as FacturaGuia,
    CONVERT(int, gsaen.folio) AS Folio,
    CONVERT(datetime, gsaen.Fecha) AS FechaCreacion,
    CONVERT(varchar(30), gmovi.DetProd) AS Producto,
    CONVERT(varchar(30),gmovi.Partida) AS Lote,
    CONVERT(int, gmovi.CantDespachada) AS Cantidad
FROM softland.iw_gsaen gsaen
INNER JOIN softland.iw_gmovi gmovi
    ON gsaen.NroInt = gmovi.NroInt AND gsaen.Tipo = gmovi.Tipo
INNER JOIN softland.nw_nventa nventa 
    ON nventa.NVNumero = gsaen.nvnumero
WHERE gsaen.Fecha BETWEEN CONVERT(datetime, @fecha_inicio, 103) AND CONVERT(datetime, @fecha_termino, 103)
  AND gsaen.CodBode = '03'
  AND gsaen.tipo = 'S'
  AND gsaen.SubTipoDocto = 'T'
  AND LEN(LTRIM(RTRIM(nventa.RetiradoPor))) > 0