4 queries found for "Premise Characteristic"

Get all from Premise Characteristic

select * 
from CI_PREM_CHAR

uploaded by Nuno Rodrigues

Get specific Premise Characteristic

select * 
from CI_PREM_CHAR
where 
PREM_ID = 'XXXX' and CHAR_TYPE_CD = 'XXXX'

uploaded by Nuno Rodrigues

Retrieve Active 'Service Agreements' without Payments

select  
(SELECT DESCR FROM CI_CHAR_VAL_L CHARL,CI_PREM_CHAR PREMCHARA WHERE PREMCHARA.CHAR_TYPE_CD = CHARL.CHAR_TYPE_CD 
AND PREMCHARA.CHAR_VAL = CHARL.CHAR_VAL AND CHARL.CHAR_TYPE_CD = 'UNIT' AND PREMCHARA.PREM_ID  = a.char_PREM_ID AND ROWNUM = 1)  "UNIT",
(SELECT DESCR FROM CI_CHAR_VAL_L CHARL,CI_PREM_CHAR PREMCHARA WHERE PREMCHARA.CHAR_TYPE_CD = CHARL.CHAR_TYPE_CD 
AND PREMCHARA.CHAR_VAL = CHARL.CHAR_VAL AND CHARL.CHAR_TYPE_CD = 'REGION' AND PREMCHARA.PREM_ID  = a.char_PREM_ID AND ROWNUM = 1) "REGION",
(SELECT DESCR FROM CI_CHAR_VAL_L CHARL,CI_PREM_CHAR PREMCHARA WHERE PREMCHARA.CHAR_TYPE_CD = CHARL.CHAR_TYPE_CD 
AND PREMCHARA.CHAR_VAL = CHARL.CHAR_VAL AND CHARL.CHAR_TYPE_CD = 'AREA' AND PREMCHARA.PREM_ID  = a.char_PREM_ID AND ROWNUM = 1) "AREA",
(SELECT DESCR FROM CI_CHAR_VAL_L CHARL,CI_PREM_CHAR PREMCHARA WHERE PREMCHARA.CHAR_TYPE_CD = CHARL.CHAR_TYPE_CD 
AND PREMCHARA.CHAR_VAL = CHARL.CHAR_VAL AND CHARL.CHAR_TYPE_CD = 'ZONE' AND PREMCHARA.PREM_ID  = a.char_PREM_ID AND ROWNUM = 1) "ZONE",
(SELECT DESCR FROM CI_CHAR_VAL_L CHARL,CI_PREM_CHAR PREMCHARA WHERE PREMCHARA.CHAR_TYPE_CD = CHARL.CHAR_TYPE_CD 
AND PREMCHARA.CHAR_VAL = CHARL.CHAR_VAL AND CHARL.CHAR_TYPE_CD = 'SUB-ZONE' AND PREMCHARA.PREM_ID  = a.char_PREM_ID AND ROWNUM =1) "SUB-ZONE",
        a.acct_id,
        (select upper(pn.entity_name) from ci_acct_per ap,ci_per_name pn where ap.per_id = pn.per_id and ap.acct_id = a.acct_id and pn.PRIM_NAME_SW = 'Y' and rownum =1) Entity_name,
        (select (TRIM (PM.ADDRESS1)||' '||TRIM (PM.ADDRESS2)||' '||TRIM (PM.ADDRESS3)||' '||TRIM (PM.ADDRESS4)) from ci_prem pm where
        pm.prem_id = a.char_prem_id and rownum =1) address,
        a.SA_ID,
        a.SA_TYPE_CD,
        a.START_DT,
        a.SA_STATUS_FLG,
        a.CHAR_PREM_ID,
        a.TOT_TO_BILL_AMT,
        au.USER_ID SA_CREATED_BY
from    ci_sa a ,CM_AUDIT_SA au
WHERE A.sa_type_cd IN ( Insert SA Type Codes) 
and     sa_status_flg in ('insert SA status flag') 
and     not exists (select 'x' from ci_ft where sa_id=a.sa_id and ft_type_flg in ('PS','PX'))
and
        (select to_char(c.START_DTTM,'dd/mm/yyyy')
from    ci_sa b,ci_sa_sp c
where   b.sa_id = c.sa_id and B.ACCT_ID = A.ACCT_ID
and     STOP_MR_ID = ' ' AND STOP_DTTM IS NULL
and     sa_type_cd like 'U-%' and sa_status_flg in ('20') and rownum = 1
) IS NOT NULL
and au.PK_VALUE1 = a.sa_id
and au.AUDIT_ACTION_FLG = 'A'
and au.audit_fld_name = 'START_DT'

uploaded by Muhammad Junaid

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