27 queries found for "SA (Service Agreement)"

Get all from SA (Service Agreement)

select * 
from CI_SA

uploaded by Nuno Rodrigues

Get specific SA (Service Agreement)

select * 
from CI_SA
where 
SA_ID = 'XXXX'

uploaded by Nuno Rodrigues

Retrieve Service Agreements linked to Account

select * from CI_SA 
where ACCT_ID = :INSERT_ACCOUNT_HERE

uploaded by Nuno Rodrigues

Retrieve Active SA of Account for specific CIS Division and SA Type

select *
  from CI_SA 
where ACCT_ID = 'INSERT_ACCOUNT_ID'
  and CIS_DIVISION = 'INSERT_CIS_DIVISION'
  and SA_TYPE_CD = 'INSERT_SA_TYPE_CD'
  and (SA_STATUS_FLG <> '60' 
  or SA_STATUS_FLG <> '70')

uploaded by Nuno Rodrigues

Get Active SAs for Account

select *
from CI_SA
where ACCT_ID= 'INSERT_ACCOUNT_ID'
and SA_STATUS_FLG IN ('20','30','40')

uploaded by Nuno Rodrigues

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 SA Type and CIS Div description given SA Id

SELECT A.SA_TYPE_CD, B.DESCR SATYDESCR,A.CIS_DIVISION,  C.DESCR CISDIVDESCR FROM CI_SA A,  CI_SA_TYPE_L B,CI_CIS_DIVISION_L C
WHERE
A.SA_ID = 'INSERT_SA_ID'
AND A.SA_TYPE_CD = B.SA_TYPE_CD
AND B.LANGUAGE_CD = 'INSERT_LANGUAGE_CD_MOSTLY_ENG'
AND A.CIS_DIVISION = C.CIS_DIVISION
AND C.LANGUAGE_CD = 'INSERT_LANGUAGE_CD_MOSTLY_ENG'

uploaded by Nuno Rodrigues

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

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

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

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

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

Find out all the frozen adjustments along with account ID mentioned

SELECT AA.ADJ_ID, AA.SA_ID, AA.ADJ_TYPE_CD, AA.ADJ_STATUS_FLG, AA.CRE_DT, AA.ADJ_AMT, AA.COMMENTS, BB.ACCT_ID FROM CI_ADJ AA, CI_SA BB 
WHERE BB.SA_ID=AA.SA_ID and AA.ADJ_STATUS_FLG=50;

uploaded by Ankit Singhal

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

Retrieve Current balance of the account

select sum(f.CUR_AMT)
from cisadm.CI_ACCT a, cisadm.ci_sa s, cisadm.CI_FT f
where a.ACCT_ID=s.ACCT_ID
and f.SA_ID=s.SA_ID
and a.ACCT_ID = 'INSERT ACCT ID'
group by s.ACCT_ID;

uploaded by Tushar Mali

DELETE PENDING BILLS

prompt "Start of delete bills treatment"
SET SERVEROUTPUT ON;


DECLARE
VBILL_ID            VARCHAR2(12);
BILL_COUNT          NUMBER:=0;

CURSOR C1 is select distinct bs.bill_id from  ci_bseg bs, ci_sa sa, ci_bill bill
where bs.sa_id=sa.sa_id
 and sa.sa_type_cd in ('xxxx','yyyyy','zzzzzz')
and bill.bill_id=bs.bill_id and bill.bill_stat_flg='P'
 and bs.bill_cyc_cd = '   ' and bs.bseg_stat_flg in ('10','20','30','40');



BEGIN

OPEN C1;
  LOOP
  FETCH c1 INTO VBILL_ID;
  EXIT WHEN c1%NOTFOUND;
  
BEGIN 

BILL_COUNT := BILL_COUNT+1;
delete from  ci_bill_sa where bill_id in (select bill_id from ci_bill where bill_id = VBILL_ID);
delete from  ci_bill_excp where bill_id in (select bill_id from ci_bill where bill_id = VBILL_ID);
delete from  ci_bill_routing where bill_id in (select bill_id from ci_bill where bill_id = VBILL_ID);

delete from  ci_bseg_sq where bseg_id in (select bseg_id from ci_bseg where bill_id = VBILL_ID);
delete from  ci_bseg_read where bseg_id in (select bseg_id from ci_bseg where bill_id = VBILL_ID);
delete from  ci_bseg_msg where bseg_id in (select bseg_id from ci_bseg where bill_id = VBILL_ID);

delete from  ci_bseg_k where bseg_id in (select bseg_id from ci_bseg where bill_id = VBILL_ID);
delete from  ci_bseg_item where bseg_id in (select bseg_id from ci_bseg where bill_id = VBILL_ID);
delete from  ci_bseg_excp where bseg_id in (select bseg_id from ci_bseg where bill_id = VBILL_ID);
delete from  ci_bseg_cl_char where bseg_id in (select bseg_id from ci_bseg where bill_id = VBILL_ID);
delete from  ci_bseg_calc_ln where bseg_id in (select bseg_id from ci_bseg where bill_id = VBILL_ID);
delete from  ci_bseg_calc where bseg_id in (select bseg_id from ci_bseg where bill_id = VBILL_ID);
delete from  ci_bseg where bseg_id in (select bseg_id from ci_bseg where bill_id = VBILL_ID);


delete from  ci_ft_gl  where ft_id in ( select ft_id from ci_ft where parent_id = VBILL_ID);
delete from  ci_ft_k  where ft_id in ( select ft_id from ci_ft where parent_id = VBILL_ID);
delete from  ci_ft  where ft_id in ( select ft_id from ci_ft where parent_id = VBILL_ID);

delete from  ci_bill where bill_id = VBILL_ID;
delete from  ci_bill_char where bill_id = VBILL_ID;
delete from  ci_bill_k where bill_id = VBILL_ID;

Exception 
When others 
then dbms_output.put_line('Error, Bill Id: '||VBILL_ID||', '||sqlerrm);
    
END;

END LOOP;  

close c1;
DBMS_OUTPUT.PUT_LINE('Number of Bills Processed ' || BILL_COUNT); 
END;
/



--commit;

prompt "End of Treatment"

exit;

uploaded by Alamuri Prasad Rao

Ft to bseg calc lines

SELECT sa.ACCT_ID, ft.ACCOUNTING_DT, ft.SA_ID, ft.FT_ID,ft.PARENT_ID,ft.SIBLING_ID,CUR_AMT
,bline.CALC_AMT,  bline.DST_ID, bline.BILL_SQ
, bline.DESCR_ON_BILL, BSEG.SA_ID ,bline.APP_IN_SUMM_SW
	FROM ods.CI_FT ft
	INNER JOIN ods.CI_BSEG bseg ON ft.SA_ID=BSEG.SA_ID AND ft.PARENT_ID = BSeg.BILL_ID
	INNER JOIN ods.CI_BSEG_CALC_LN bLine ON bseg.BSEG_ID = bline.BSEG_ID AND bline.DST_ID!=' '
	INNER JOIN ods.CI_SA sa ON sa.SA_ID = ft.SA_ID
	WHERE  ft.PARENT_ID = 'your bill id'

uploaded by Kemal Onyurt