7 queries found for "SA Type"

Retrieve Active Rateable Service Agreements of Account

SELECT 
  B.BILL_PRT_PRIO_FLG, 
  A.SA_ID,
  A.CIS_DIVISION,
  A.SA_TYPE_CD
FROM 
  CI_SA A, 
  CI_SA_TYPE B
WHERE 
  A.ACCT_ID='INSERT_ACCOUNT_ID'
  AND A.CIS_DIVISION = B.CIS_DIVISION
  AND A.SA_TYPE_CD = B.SA_TYPE_CD
  AND A.SA_STATUS_FLG IN ('20','30','40','50')
  AND B.RT_REQ_SW = 'Y'

uploaded by Nuno Rodrigues

Retrieve Account's Active SAs linked to SP with Same Service Type

SELECT A.SA_ID, B.SA_TYPE_CD, B.CIS_DIVISION, C. SVC_TYPE_CD, C.DESCR
FROM CI_SA A, CI_SA_TYPE B, CI_SVC_TYPE_L C
WHERE A.ACCT_ID = 'INSERT_ACCOUNT_ID'
AND A.SA_STATUS_FLG = '20'
AND A.CIS_DIVISION = B.CIS_DIVISION 
AND A.SA_TYPE_CD = B.SA_TYPE_CD 
AND B.ALLOW_SP_SW = 'Y'
AND B.SVC_TYPE_CD = C.SVC_TYPE_CD 
AND C.LANGUAGE_CD = 'INSERT_LANGUAGE_CD_MOSTLY_ENG'

uploaded by Nuno Rodrigues

Retrieve SA Types

SELECT SAT.SA_TYPE_CD, SATL.DESCR, SAT.CIS_DIVISION
from CI_SA_TYPE SAT, CI_SA_TYPE_L SATL
where SAT.CIS_DIVISION = SATL.CIS_DIVISION 
and SAT.SA_TYPE_CD = SATL.SA_TYPE_CD
and SATL.LANGUAGE_CD = 'INSERT_LANGUAGE_CD_MOSTLY_ENG'

uploaded by Nuno Rodrigues

Retrieve Deposit SA For Account

SELECT SA.SA_ID, SA.SA_STATUS_FLG, SA.TOT_TO_BILL_AMT, SA.START_DT 
   FROM CI_SA SA, CI_SA_TYPE SAT 
 WHERE SA.ACCT_ID = 'INSERT_ACCOUNT_ID'
     AND SA.SA_STATUS_FLG  IN ('10','20')  
     AND SAT.CIS_DIVISION = SA.CIS_DIVISION 
     AND SAT.SA_TYPE_CD = SA.SA_TYPE_CD 
     AND SAT.SPECIAL_ROLE_FLG = 'CD'
     AND SA.START_DT = (SELECT MAX(A.START_DT) 
            FROM CI_SA A , CI_SA_TYPE B
            WHERE A.ACCT_ID = SA.ACCT_ID
            AND A.SA_STATUS_FLG  IN ('10','20')  
            AND A.SA_TYPE_CD = B.SA_TYPE_CD
            AND B.CIS_DIVISION =A.CIS_DIVISION               
            AND B.SPECIAL_ROLE_FLG = 'CD'
            AND A.START_DT <= 'INSERT_EXPECTED_MAX_DATE')
            

uploaded by Nuno Rodrigues

Get SA Type Distribution Code for Bill Segment

SELECT SAT.DST_ID 
  FROM CI_BSEG BSEG , CI_SA SA, CI_SA_TYPE SAT
 WHERE BSEG.BSEG_ID = 'INSERT_BILL_SEGMENT_ID'
    AND BSEG.SA_ID = SA.SA_ID 
    AND SA.SA_TYPE_CD = SAT.SA_TYPE_CD
    AND SA.CIS_DIVISION = SAT.CIS_DIVISION

uploaded by Nuno Rodrigues

Get Bill's Bill Segment Amount

SELECT
  SVCTYPEL.SVC_TYPE_CD, 
  SVCTYPEL.DESCR, 
  SUM(FT.CUR_AMT) AS CUR_AMT_SUM
FROM
  CI_SVC_TYPE SVCTYPE, 
  CI_SVC_TYPE_L SVCTYPEL, 
  CI_SA_TYPE SATY, 
  CI_SA SA, 
  CI_FT FT
WHERE 
  FT.BILL_ID = 'INSERT_BILL_ID'
  AND FT.PARENT_ID = FT.BILL_ID
  AND FT.FT_TYPE_FLG IN ('BS','BX')
  AND FT.FREEZE_SW = 'Y'
  AND FT.SHOW_ON_BILL_SW = 'Y'
  AND FT.SA_ID = SA.SA_ID
  AND SA.CIS_DIVISION = SATY.CIS_DIVISION
  AND SA.SA_TYPE_CD = SATY.SA_TYPE_CD
  AND SATY.SVC_TYPE_CD = SVCTYPE.SVC_TYPE_CD
  AND SVCTYPE.SVC_TYPE_CD = SVCTYPEL.SVC_TYPE_CD
  AND SVCTYPEL.LANGUAGE_CD = 'INSERT_LANGUAGE_CD_MOSTLY_ENG'
GROUP BY
  SVCTYPEL.SVC_TYPE_CD, 
  SVCTYPEL.DESCR
  

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