2 queries found for "Distribution Code GL Account"

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 Distribution Codes

select a.dst_id,z.descr,a.effdt,df.gl_acct,
(select char_val from ci_dst_cd_char c where c.chaR_type_cd='GLFUNCT' and c.dst_id=a.dst_id
        and effdt=(select max(effdt) from ci_dst_cd_char b where b.dst_id=c.dst_id and b.char_type_cd=c.char_type_cd)) gl_funct,
(select char_val from ci_dst_cd_char c where c.chaR_type_cd='GLACCTYP' and c.dst_id=a.dst_id
        and effdt=(select max(effdt) from ci_dst_cd_char b where b.dst_id=c.dst_id and b.char_type_cd=c.char_type_cd)) gl_acct_typ         
from ci_dst_cd_char a,ci_dst_code_l z ,CI_DST_CODE_EFF df
where a.effdt=(select max(effdt) from ci_dst_cd_char b where b.dst_id=a.dst_id 
and b.char_type_cd=a.char_type_cd)
and a.dst_id=z.dst_id
and df.dst_id=a.dst_id
group by a.dst_id,a.effdt,z.descr,df.gl_acct
order by dst_id

uploaded by Muhammad Junaid