Platform
Reconciliation

-- PARQUE_BSCS.sql — Oracle SQL*Plus
-- Extracts all active subscribers from BSCS billing system.
-- CH_STATUS != 'd' → excludes deactivated contracts.
-- CS_DEACTIV_DATE IS NULL → only lines with no deactivation date (active).
-- Output: pipe-delimited flat file for SQL Server import.

WHENEVER SQLERROR EXIT SQL.SQLCODE
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDD';
SET colsep '|';
SPOOL D:\03_DUMP\BSCS\DUMP_BSCS.txt;

SELECT
    CA.CUSTOMER_ID -- unique customer
  , CA.CUSTCODE
  , CA.CSCOMPTAXNO   -- RUT / tax ID
  , CNT.CO_ID        -- contract ID
  , CNT.CO_ACTIVATED
  , CNT.TMCODE       -- rate plan code (joins to 658-plan catalog)
  , CNT.RATEPLAN_CHANGE_DATE
  , CNT.TMCODE_OLD
  , CNT.CO_CODE
  , CNT.CH_STATUS
  , CSC.DN_ID
  , TO_CHAR(CSC.CS_ACTIV_DATE, 'YYYYMMDD')
  , TO_CHAR(CSC.CS_DEACTIV_DATE, 'YYYYMMDD')
  , DN.DN_NUM        -- MSISDN / phone number
  , BCY.BILLCYCLE
  , CNT.CH_STATUS_VALIDFROM
FROM CUSTOMER_ALL CA
  LEFT JOIN CONTRACT_ALL      CNT ON CA.CUSTOMER_ID = CNT.CUSTOMER_ID
  LEFT JOIN CONTR_SERVICES_CAP CSC ON CNT.CO_ID = CSC.CO_ID
  LEFT JOIN DIRECTORY_NUMBER    DN  ON CSC.DN_ID = DN.DN_ID
  LEFT JOIN CUSTOMER_BCH        BCY ON CA.CUSTCODE = BCY.CUSTCODE
WHERE
  TO_CHAR(CSC.CS_DEACTIV_DATE, 'YYYYMMDD') IS NULL  -- no deactivation date = still active
  AND CNT.CH_STATUS != 'd';                         -- exclude deleted contracts

SPOOL OFF;

-- DUMP_DWH — Netezza SQL
-- Pulls yesterday's subscriber park from DWH with full plan classification.
-- Joins to LK_PLN_PLAN for plan type, market segment, commercial objective.
-- This is the source of truth for what BSCS and HSS should reflect.

SELECT
  A.NUMERO_ABONADO,
  A.ESTADO_CONTRATO,
  A.ID_PLAN,
  B.DESC_CORTA_PLAN,
  CASE
    WHEN B.ID_TIPO_PLAN = 3 THEN 'UNLIMITED'
    WHEN B.ID_TIPO_PLAN = 5 THEN 'Prepago'
    WHEN B.ID_TIPO_PLAN = 6 THEN 'Controlado'
    ELSE 'NA'
  END AS TIPO_PLAN,
  CASE
    WHEN B.ID_MERCADO_PLAN = 2 THEN 'Consumer'
    WHEN B.ID_MERCADO_PLAN = 5 THEN 'Business'
    WHEN B.ID_MERCADO_PLAN = 4 THEN 'Prepago'
    ELSE 'NA'
  END AS MERCADO,
  A.ICCID, A.IMSI, A.FECHA_ALTA
FROM
  nz_prod.DWH_WOM.BT_CLE_PARQUE A
  LEFT JOIN NZ_PROD.DWH_WOM.LK_PLN_PLAN B ON A.ID_PLAN = B.ID_PLAN
WHERE
  A.PERIODO_PARQUE = TO_CHAR(NOW()-1, 'YYYYMM')
  AND A.estado_contrato != 'd';

-- TRAFICO_NZ — Netezza SQL (NZ_PROD)
-- FULL JOIN voice + data so a subscriber with ONLY voice or ONLY data is not lost.
-- DISTINCT + CASE WHEN handles the NULL from either side of the join.

SELECT DISTINCT
  CASE WHEN A.NUMERO_ABONADO IS NULL
       THEN B.NUMERO_ABONADO
       ELSE A.NUMERO_ABONADO
  END AS NUM_ABONADO,
  SUM(A.CANTIDAD_VOZ)  MIN_VOZ,
  A.ULTIMO_TRAFICO_VOZ,
  SUM(B.CANTIDAD_MB)   MB_DATOS,
  B.ULTIMO_TRAFICO_DATOS
FROM
  (SELECT
     Z.NUMERO_ABONADO,
     SUM(CAST(Z.DURACION AS INT) / 60) CANTIDAD_VOZ,
     MAX(Z.ID_DWH_DIA) ULTIMO_TRAFICO_VOZ
   FROM NZ_PROD.STG_TRAF.AGG_TRF_VOZ_MED_ABONADO Z
   WHERE Z.ID_DWH_DIA >= TO_CHAR(NOW()-30, 'YYYYMMDD')
   GROUP BY Z.NUMERO_ABONADO) A

FULL JOIN

  (SELECT
     X.NUMERO_ABONADO,
     ROUND(SUM((X.KB_DOWN_IX + X.KB_UP_IX) / 1024), 2) CANTIDAD_MB,
     MAX(X.ID_DWH_DIA) ULTIMO_TRAFICO_DATOS
   FROM NZ_PROD.STG_TRAF.AGG_TRF_DATOS_ABONADO X
   WHERE X.ID_DWH_DIA >= TO_CHAR(NOW()-30, 'YYYYMMDD')
   GROUP BY X.NUMERO_ABONADO) B

ON A.NUMERO_ABONADO = B.NUMERO_ABONADO
GROUP BY NUM_ABONADO, A.ULTIMO_TRAFICO_VOZ, B.ULTIMO_TRAFICO_DATOS;