11 queries found for "Service Point"

Get all from Service Point

select * 
from CI_SP

uploaded by Nuno Rodrigues

Get specific Service Point

select * 
from CI_SP
where 
SP_ID = 'XXXX'

uploaded by Nuno Rodrigues

Get Premise SPs

SELECT *
FROM CI_SP
WHERE PREM_ID = 'INSERT_PREMISE_ID'

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

Retrieve SP/premises linked to account's active SAs

SELECT C.PREM_ID, C.SP_ID, D.SP_TYPE_CD, D.DESCR, E.ADDRESS1, E.CITY, E.STATE, E.POSTAL 
  FROM CI_SA A, CI_SA_SP B, CI_SP C, CI_SP_TYPE_L D, CI_PREM E
 WHERE A.ACCT_ID = 'INSERT_ACCOUNT_ID'  
   AND A.SA_STATUS_FLG < '60'
   AND A.SA_ID = B.SA_ID
   AND B.START_DTTM <= 'INSERT_EXPECTED_START_DATE'
   AND (B.STOP_DTTM >= 'INSERT_EXPECTED_STOP_DATE' OR B.STOP_DTTM IS NULL) 
   AND B.SP_ID = C.SP_ID
   AND C.SP_TYPE_CD = D.SP_TYPE_CD
   AND D.LANGUAGE_CD = :LANGUAGE
   and C.PREM_ID = E.PREM_ID
GROUP BY C.PREM_ID, C.SP_ID, D.SP_TYPE_CD, D.DESCR, E.ADDRESS1, E.CITY, E.STATE, E.POSTAL

uploaded by Nuno Rodrigues

Retrieve Service Point for SP Geo Type/Val

SELECT SP.* 
FROM 
    CI_SP_GEO SPG,
    CI_SP SP
WHERE
SPG.SP_ID = SP.SP_ID
AND SPG.GEO_TYPE_CD = 'INSERT_GEO_TYPE_CD'
AND SPG.GEO_VAL  = 'INSERT_GEO_VAL'

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

Retrieve Field Activities with Blank Dispatch Group

SELECT * FROM CI_FA fa, ci_sp sp, ci_sp_op_area op
where fa.sp_id=sp.sp_id
and  fa_type_cd='INSERT YOUR FA TYPE CD'
and sp.sp_id=op.sp_id
and sp.sp_type_cd='INSERT YOUR SP TYPE CD'
and fa.DISP_GRP_CD = ' '

uploaded by Muhammad Junaid

Retrieve audit through query

SELECT * FROM CI_AUDIT
WHERE AUDIT_TBL_NAME='CI_SP-- insert required table name'
AND PK_VALUE1='INSERT SP ID'

uploaded by Muhammad Junaid

Retrieve unmatched Bill Cyc and SP MR Cyc code

SELECT ac.mailing_prem_id, ac.bill_cyc_cd, ac.acct_id, sp.prem_id,
       sp.mr_rte_cd, sp.mr_cyc_cd, sp.sp_id
  FROM ci_acct ac, ci_sp sp
 WHERE sp.prem_id = ac.mailing_prem_id 
 AND ac.bill_cyc_cd <> sp.mr_cyc_cd

uploaded by Muhammad Junaid

Query to find accounts linked to multiple premise

SELECT ACCT_ID, COUNT(*) FROM (
SELECT SA.ACCT_ID, PREM.PREM_ID
fROM CI_SA_SP SASP, CI_SA SA, CI_SP SP, CI_PREM PREM
WHERE SA.SA_ID = SASP.SA_ID
AND SP.SP_ID = SASP.SP_ID
AND SP.PREM_ID = PREM.PREM_ID
AND SASP.USAGE_FLG = '+'
AND SA.SA_STATUS_FLG = '20'
-- AND PREM.CITY = 'CALAIS' -- to limit to a particular city
-- AND ACCESS_GRP_CD = 'PB55_PART   ' - to limit to a particular access group
-- AND ROWNUM <= 10000 -- to limit the number of records to search as this can take a long time
GROUP BY SA.ACCT_ID, PREM.PREM_ID)
GROUP BY ACCT_ID HAVING COUNT(*) > 1 ;

uploaded by Hariharan Ramanathan