5 queries found for "SP/Meter History"

Get Meter Installed at SP

SELECT MC.MTR_ID MTR
FROM CI_SP_MTR_HIST SPMH, CI_MTR_CONFIG MC
WHERE
  SPMH.SP_ID = 'INSERT_SERVICE_POINT_ID'
  AND SPMH.REMOVAL_MR_ID = ' '
  AND SPMH.MTR_CONFIG_ID = MC.MTR_CONFIG_ID
  AND MC.EFF_DTTM <= 'INSERT_METER_CONFIG_EFFECTIVE_DATE'

uploaded by Nuno Rodrigues

Get SP from Meter or Mtr Cfg or SP/Mtr Hist installed on SP

SELECT 
HIST.*
FROM 
CI_MTR_CONFIG CFG,
CI_SP_MTR_HIST HIST
-- choose one and remove the [ ]
[ WHERE CFG.MTR_ID = 'INSERT_METER_ID']
[ WHERE CFG.MTR_CONFIG_ID = 'INSERT_METER_CONFIG_ID']
[ WHERE HIST.SP_MTR_HIST_ID = 'INSERT_METER_HISTORY_ID']
AND CFG.MTR_CONFIG_ID = HIST.MTR_CONFIG_ID
AND HIST.REMOVAL_MR_ID = ' '
AND HIST.REMOVAL_DTTM IS NULL

uploaded by Nuno Rodrigues

Determine if SA is Linked to Any Metered SP

SELECT
  COUNT(*)  AS MSP_COUNT
FROM 
  CI_SA_SP SASP,
  CI_SP SP,
  CI_SP_TYPE SPTY,
  CI_SP_MTR_HIST SPM,
  CI_MTR_CONFIG CFG
WHERE
    SASP.SA_ID = 'INSERT_SA_ID'
AND SP.SP_ID = SASP.SP_ID
AND SPTY.SP_TYPE_CD = SP.SP_TYPE_CD
AND SPTY.SP_SUBTYPE_FLG = 'M'
AND SPM.SP_ID = SP.SP_ID
AND (SPM.REMOVAL_DTTM IS NULL OR SPM.REMOVAL_DTTM > 'INSERT_MINIMUM_REMOVAL_DATE')
AND CFG.MTR_CONFIG_ID = SPM.MTR_CONFIG_ID
AND NOT EXISTS (SELECT 'X'
                  FROM CI_REG REG
                 WHERE REG.MTR_ID = CFG.MTR_ID
                   AND REG.EFF_DTTM = CFG.EFF_DTTM
                   AND REG.INTV_REG_TYPE_CD != ' ')
                   

uploaded by Nuno Rodrigues

Get Meter Information at SP

SELECT
  CFG.MTR_ID MTR, 
  MTR.BADGE_NBR BADGE, 
  CFG.MTR_CONFIG_TY_CD  CONFIGTYPE
FROM
  CI_SP_MTR_HIST HIST, 
  CI_MTR_CONFIG CFG, 
  CI_MTR MTR
WHERE
  HIST.SP_ID = 'INSERT_SERVICE_POINT_ID'
AND(HIST.REMOVAL_DTTM IS NULL
  OR HIST.REMOVAL_DTTM >= 'INSERT_MINIMUM_REMOVAL_DATE')
  AND CFG.MTR_CONFIG_ID = HIST.MTR_CONFIG_ID
  AND MTR.MTR_ID = CFG.MTR_ID
  

uploaded by Nuno Rodrigues

Retrieve Accounts, with meters installed, where Active Service Agreements exist.

-- 'In our scenario, some Service Agreements need to be closed after meter installation, but due to some issues those SAs could not be closed, so we need to get those cases for users to resolve the problem.'

 
select 
    ((select pc.char_val from ci_prem_char pc where pc.char_type_cd= 'UNIT' and pc.prem_id= prem.prem_id AND  trim (pc.char_val) = 'UNIT' and rownum=1)) UNIT,
        ((select cvl.DESCR from ci_prem_char pc, ci_char_val_l cvl where pc.char_type_cd= 'REGION' and pc.prem_id= prem.prem_id and pc.char_type_cd = cvl.char_type_cd and pc.char_val=cvl.char_val and rownum=1)) REGION,
       ((select cvl.DESCR from ci_prem_char pc, ci_char_val_l cvl where pc.char_type_cd= 'ZONE' and pc.prem_id= prem.prem_id and pc.char_type_cd = cvl.char_type_cd and pc.char_val=cvl.char_val and rownum=1)) ZONE,
  ((select cvl.DESCR from ci_prem_char pc, ci_char_val_l cvl where pc.char_type_cd= 'SUB-ZONE' and pc.prem_id= prem.prem_id and pc.char_type_cd = cvl.char_type_cd and pc.char_val=cvl.char_val and rownum=1)) SUB_ZONE,
  ((select cvl.DESCR from ci_prem_char pc, ci_char_val_l cvl where pc.char_type_cd= 'AREA' and pc.prem_id= prem.prem_id and pc.char_type_cd = cvl.char_type_cd and pc.char_val=cvl.char_val and rownum=1)) AREA,
       pn.entity_name_upr,
       ap.acct_id,
       sa.sa_id,
       sa.sa_type_cd,
       sal.descr,
       prem.Address1,
       prem.Address2,
       prem.Address3,
       prem.Address4,
       prem.prem_id,
       sa.start_dt,
       sa.tot_to_bill_amt,
--       pc.char_type_cd,
       pc.char_val UNIT,
       ds.dst_id,
       ds.GL_ACCT
 from ci_per_name pn,ci_sa sa,ci_acct_per ap, ci_prem prem,ci_prem_char pc, ci_sa_type_l sal, ci_sa_type st, ci_dst_code_eff ds
where   pn.per_id = ap.per_id
and     sal.SA_TYPE_CD= st.SA_TYPE_CD
and     st.DST_ID= ds.dst_id
and     sa.acct_id = ap.acct_id
and     sa.sa_type_cd = sal.sa_type_cd
and     sa.char_prem_id = pc.prem_id
and     sa.char_prem_id=prem.prem_id
and     pc.char_type_cd='UNIT'
and     pn.name_type_flg = 'PRIM'
and     sa.sa_status_flg = '20'
and     sa.sa_type_cd in ('C-ADV-IM','C-ADV-II','C-ADV-NC','C-ADV-ND','C-ADV-NF','C-ADV-NS','C-ADV-PC','C-ADV-PI','C-ADV-RF','C-AE-CNG','C-AE-HPC','C-AE-IND',
'C-AE-PG',
'C-AN-CNG',
'C-AN-HPC',
'C-AN-IND',
'C-AN-PG')
AND char_prem_id IN
(SELECT prem_id FROM ci_sp sp,ci_sp_mtr_hist smh
WHERE sp.sp_id=smh.sp_id
AND smh.removal_mr_id=' ')

uploaded by Muhammad Junaid