16 queries found for "Account Person Relationship"

Get Person Accounts

select ACCT_ID from CI_ACCT_PER 
where MAIN_CUST_SW= 'Y'
AND PER_ID = 'INSERT_PERSON_ID'

uploaded by Nuno Rodrigues

Get Main Person (with active SAs) Linked to Service Point

select PN.*
from CI_SA_SP SASP, CI_SA SA, CI_ACCT_PER AP, CI_PER_NAME PN
where 	
        SASP.SP_ID = 'INSERT_SERVICE_POINT_ID'
        AND SASP.START_DTTM <= SYSDATE
        AND (SASP.STOP_DTTM >= SYSDATE OR SASP.STOP_DTTM IS NULL)
        AND SA.SA_ID = SASP.SA_ID
        AND SA.SA_STATUS_FLG IN ('20','30')
        AND AP.ACCT_ID = SA.ACCT_ID
        AND AP.MAIN_CUST_SW = 'Y'
        AND AP.PER_ID = PN.PER_ID

uploaded by Nuno Rodrigues

Retrieve Account Persons

SELECT
  A.PER_ID, 
  A.ACCT_REL_TYPE_CD,
  B.ENTITY_NAME
FROM 
  CI_ACCT_PER A,
  CI_PER_NAME B 
WHERE 
  A.ACCT_ID = 'INSERT_ACCOUNT_ID'
AND B.PER_ID = A.PER_ID
AND B.PRIM_NAME_SW = 'Y'

uploaded by Nuno Rodrigues

Retrieve Person/Account Bill Routing Info

SELECT 
  PER_ID,
  BILL_RTE_TYPE_CD,
  RECEIVE_COPY_SW
FROM
  CI_ACCT_PER ACP
WHERE
      ACP.ACCT_ID = 'INSERT_ACCOUNT_ID'

uploaded by Nuno Rodrigues

Get Bill Segments for Service Agreement

SELECT
  BS.START_DT,
  BS.END_DT,
  BS.BSEG_STAT_FLG,
  BS.BSEG_ID,
  BS.BILL_ID,
  BS.SA_ID,
  NM.ENTITY_NAME
, SA.ACCT_ID,
  LK.DESCR
FROM
  CI_PER_NAME NM,
   CI_ACCT_PER AC,
   CI_SA SA,
   CI_BSEG BS,
   CI_LOOKUP_VAL_L  LK
WHERE
  BS.SA_ID = 'INSERT_SA_ID'
   AND SA.SA_ID = BS.SA_ID
   AND AC.ACCT_ID = SA.ACCT_ID
   AND AC.MAIN_CUST_SW = 'Y'
   AND NM.PER_ID = AC.PER_ID
   AND NM.PRIM_NAME_SW = 'Y'
   AND LK.LANGUAGE_CD = 'INSERT_LANGUAGE_CODE_MOSTLY_ENG'
   AND LK.FIELD_NAME = 'BSEG_STAT_FLG'          
   AND LK.FIELD_VALUE = BS.BSEG_STAT_FLG
ORDER BY BS.START_DT DESC

uploaded by Nuno Rodrigues

Get Bill Segments for Person

SELECT
    BS.START_DT,
    BS.END_DT,
    BS.BSEG_STAT_FLG,
    BS.BSEG_ID,
    BS.BILL_ID,
    BS.SA_ID,
    NM.ENTITY_NAME,  
 SA.ACCT_ID,
LK.DESCR
 FROM
    CI_PER_NAME NM,
     CI_ACCT_PER AC,
     CI_SA SA,
     CI_BSEG BS,
    CI_LOOKUP_VAL_L LK
 WHERE
    NM.ENTITY_NAME_UPR LIKE UPPER('INSERT_PERSON_NAME')
     AND AC.PER_ID = NM.PER_ID
     AND SA.ACCT_ID = AC.ACCT_ID
     AND BS.SA_ID = SA.SA_ID
     AND BS.BILL_ID <> ' '
   AND LK.LANGUAGE_CD = 'INSERT_LANGUAGE_CD_MOSTLY_ENG'
   AND LK.FIELD_NAME = 'BSEG_STAT_FLG'          
   AND LK.FIELD_VALUE = BS.BSEG_STAT_FLG
ORDER BY 
   NM.ENTITY_NAME_UPR
    , BS.START_DT DESC
    , BS.SA_ID

uploaded by Nuno Rodrigues

Get Bill Segments for Account

SELECT
    BS.START_DT,
    BS.END_DT,
    BS.BSEG_STAT_FLG,
    BS.BSEG_ID,
    BS.BILL_ID,
    BS.SA_ID,
    NM.ENTITY_NAME,
    LK.DESCR
 FROM
    CI_PER_NAME NM,
     CI_ACCT_PER AC,
     CI_SA SA,
     CI_BSEG BS,
    CI_LOOKUP_VAL_L  LK
 WHERE
    SA.ACCT_ID = 'INSERT_ACCOUNT_ID'
     AND AC.ACCT_ID = SA.ACCT_ID
     AND AC.MAIN_CUST_SW = 'Y'
     AND NM.PER_ID = AC.PER_ID
     AND NM.PRIM_NAME_SW = 'Y'
     AND BS.SA_ID = SA.SA_ID
     AND BS.BILL_ID <> ' '
   AND LK.LANGUAGE_CD = 'INSERT_LANGUAGE_CD_MOSTLY_ENG'
   AND LK.FIELD_NAME = 'BSEG_STAT_FLG'          
   AND LK.FIELD_VALUE = BS.BSEG_STAT_FLG
ORDER BY 
    BS.START_DT DESC
    , BS.SA_ID

uploaded by Nuno Rodrigues

Get Bills for Customer

SELECT
  BI.ACCT_ID,
  BI.BILL_STAT_FLG,
  BI.BILL_DT,
  BI.BILL_ID,
  BI.CRE_DTTM,
  NM.ENTITY_NAME,
  BI.DUE_DT,
  LK.DESCR AS BILL_STAT_DESCR
FROM
  CI_BILL BI,
   CI_ACCT_PER AP,
   CI_PER_NAME NM,
   CI_LOOKUP_VAL_L LK
WHERE
  NM.ENTITY_NAME_UPR LIKE UPPER('INSERT_CUSTOMER_NAME')
   AND BI.ACCT_ID = AP.ACCT_ID
   AND AP.PER_ID = NM.PER_ID
   AND LK.FIELD_NAME = 'BILL_STAT_FLG'
   AND LK.LANGUAGE_CD = :LANGUAGE
   AND LK.FIELD_VALUE = BI.BILL_STAT_FLG
   ORDER BY NM.ENTITY_NAME_UPR ASC
  , BI.ACCT_ID ASC
  ,  BI.BILL_DT DESC
  , BI.CRE_DTTM DESC

uploaded by Nuno Rodrigues

Get Bills for Account

SELECT
  BI.ACCT_ID,
  BI.BILL_STAT_FLG,
  BI.BILL_DT,
  BI.BILL_ID,
  BI.CRE_DTTM,
  NM.ENTITY_NAME,
  BI.DUE_DT,
  LK.DESCR AS BILL_STAT_DESCR
FROM
  CI_BILL BI,
   CI_ACCT_PER AP,
   CI_PER_NAME NM,
   CI_LOOKUP_VAL_L LK
WHERE
  BI.ACCT_ID = 'INSERT_ACCOUNT_ID'
   AND BI.ACCT_ID = AP.ACCT_ID
   AND AP.MAIN_CUST_SW = 'Y'
   AND NM.NAME_TYPE_FLG = 'PRIM'
   AND AP.PER_ID = NM.PER_ID
  AND LK.FIELD_NAME = 'BILL_STAT_FLG'
  AND LK.LANGUAGE_CD = :LANGUAGE
  AND LK.FIELD_VALUE = BI.BILL_STAT_FLG   
ORDER BY BI.ACCT_ID ASC
  , BI.BILL_DT DESC
  , BI.CRE_DTTM DESC
  

uploaded by Nuno Rodrigues

Get Bill with specific Characteristic value

SELECT
  BI.ACCT_ID,
  BI.BILL_STAT_FLG,
  BI.BILL_DT,
  BI.BILL_ID,
  BI.CRE_DTTM,
  BI.DUE_DT,  
  NM.ENTITY_NAME,
  LK.DESCR AS BILL_STAT_DESCR
FROM
  CI_BILL BI,
  CI_ACCT_PER AP,
  CI_PER_NAME NM,
  CI_BILL_CHAR BIC,
  CI_CHAR_TYPE CHTY,
   CI_LOOKUP_VAL_L LK
WHERE
  BI.ACCT_ID = AP.ACCT_ID
  AND AP.MAIN_CUST_SW = 'Y'
  AND NM.NAME_TYPE_FLG =   'PRIM'
  AND AP.PER_ID = NM.PER_ID
  AND BI.BILL_ID = BIC.BILL_ID
  AND TRIM(BIC.CHAR_TYPE_CD) =  'INSERT_CHAR_TYPE_CD'
  AND TRIM(BIC.SRCH_CHAR_VAL) LIKE UPPER('INSERT_SEARCH_VALUE')
  AND CHTY.CHAR_TYPE_CD = BIC.CHAR_TYPE_CD
   AND LK.FIELD_NAME = 'BILL_STAT_FLG'
  AND LK.LANGUAGE_CD = :LANGUAGE
  AND LK.FIELD_VALUE = BI.BILL_STAT_FLG   
ORDER BY BI.ACCT_ID ASC
  , BI.BILL_DT DESC
  , BI.CRE_DTTM DESC  
  

uploaded by Nuno Rodrigues

Get Bill Segments for Bill

SELECT
    BS.START_DT,
    BS.END_DT,
    BS.BSEG_STAT_FLG,
    BS.BSEG_ID,
    BS.BILL_ID,
    BS.SA_ID,
    NM.ENTITY_NAME
   ,SA.ACCT_ID,
    LK.DESCR
 FROM
    CI_PER_NAME NM,
     CI_ACCT_PER AC,
     CI_SA SA,
     CI_BSEG BS,
    CI_LOOKUP_VAL_L  LK
 WHERE
    BS.BILL_ID = 'INSERT_BILL_ID'
     AND SA.SA_ID = BS.SA_ID
     AND AC.ACCT_ID = SA.ACCT_ID
     AND AC.MAIN_CUST_SW = 'Y'
     AND NM.PER_ID = AC.PER_ID
     AND NM.PRIM_NAME_SW = 'Y'
   AND LK.LANGUAGE_CD = 'INSERT_LANGUAGE_CD'
   AND LK.FIELD_NAME = 'BSEG_STAT_FLG'          
   AND LK.FIELD_VALUE = BS.BSEG_STAT_FLG
 ORDER BY 
    BS.START_DT DESC
    , BS.SA_ID

uploaded by Nuno Rodrigues

Retrieve Service Agreement Linked to Service Point / Field Activity

SELECT 
A.SA_ID,
A.SA_SP_FA_TYPE_FLG,
C.PER_ID,
B.ACCT_ID
FROM 
CI_SA_SP_FA A,
CI_SA B,
CI_ACCT_PER C
WHERE A.FA_ID = 'INSERT_FIELD_ACTIVITY_ID'
AND A.SA_ID = B.SA_ID
AND B.ACCT_ID = C.ACCT_ID
AND C.MAIN_CUST_SW = 'Y'

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

Get all Bill Routes

SELECT aper.PER_ID,
  aper.ACCT_ID,
  rte.BILL_RTE_TYPE_CD
FROM ci_acct_per aper,
  ci_bill_rt_type rte
WHERE rte.bill_rte_type_cd=aper.bill_rte_type_cd

uploaded by Venkata Sai Bhargav Kakunuri

Retrieve blank AMG on account

SELECT ac.cust_cl_cd, ac.acct_id, ac.acct_mgmt_grp_cd, pn.entity_name_upr
  FROM ci_acct@production ac,
       ci_acct_per@production ap,
       ci_per_name@production pn
 WHERE ac.acct_id = ap.acct_id
   AND ap.per_id = pn.per_id
   AND pn.prim_name_sw = 'Y'
   AND ac.acct_mgmt_grp_cd = ' '

uploaded by Muhammad Junaid