9 queries found for "Service Agreement / Service Point"

Get Active or Pending Stop SA Linked to SP

SELECT B.*
FROM CI_SA_SP A, CI_SA B
WHERE
     A.SP_ID = 'INSERT_SERVICE_POINT_ID'
     AND A.SA_ID = B.SA_ID
     AND B.SA_STATUS_FLG IN ('20','30')
ORDER BY A.SA_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

Get Service Points linked to Service Agreement

SELECT DISTINCT *
FROM CI_SA_SP
WHERE SA_ID = 'INSERT_SA_ID'

uploaded by Nuno Rodrigues

Get Usage Requests in Awaiting Data Sync Status by SP

SELECT 
USAGE.USAGE_ID
FROM 
CI_SA SA
, CI_SA_SP SASP
, C1_USAGE USAGE
, F1_BUS_OBJ_STATUS BOSTATUS
, F1_BUS_OBJ_STATUS_OPT OPT
, F1_BUS_OBJ BO
 WHERE SASP.SA_ID = SA.SA_ID AND SASP.SP_ID = 'INSERT_SP_ID' and SASP.START_DTTM <= SYSDATE AND (SASP.STOP_DTTM IS NULL OR SASP.STOP_DTTM > SYSDATE)
AND USAGE.SA_ID = SASP.SA_ID
AND USAGE.BUS_OBJ_CD = BO.BUS_OBJ_CD
AND BO.LIFE_CYCLE_BO_CD = BOSTATUS.BUS_OBJ_CD
AND USAGE.BO_STATUS_CD = BOSTATUS.BO_STATUS_CD
AND BOSTATUS.BUS_OBJ_CD = OPT.BUS_OBJ_CD
AND OPT.BO_OPT_FLG = 'F1SC' 
AND OPT.BO_OPT_VAL = 'C1AS'

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

Check Start / Stop Meter Read

SELECT * FROM CI_SA_SP
WHERE
START_MR_ID = 'INSERT_START_DATE' OR
STOP_MR_ID = 'INSERT_STOP_DATE'

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

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