15 queries found for "Bill Segment"

Get all from Bill Segment

select * 
from CI_BSEG

uploaded by Nuno Rodrigues

Get specific Bill Segment

select * 
from CI_BSEG
where 
BSEG_ID = 'XXXX'

uploaded by Nuno Rodrigues

Get Frozen Bill Segments For Usage

SELECT USG.BSEG_ID  
  FROM C1_USAGE USG,CI_BSEG BSEG
  WHERE USG.USAGE_ID = 'INSERT_USAGE_ID'
AND USG.BSEG_ID=BSEG.BSEG_ID 
AND BSEG.BSEG_STAT_FLG='50'

uploaded by Nuno Rodrigues

Retrieve Frozen Bill Segments for SA during a certain period

SELECT A.BSEG_ID, A.START_DT, A.END_DT, A.MDM_START_DTTM, A.MDM_END_DTTM, SUM(B.CALC_AMT) AS CALC_AMT
   FROM CI_BSEG A, CI_BSEG_CALC B
  WHERE A.SA_ID = 'INSERT_SA_ID'
  AND A.BSEG_STAT_FLG = '50'
  AND A.BSEG_ID = B.BSEG_ID
  AND A.END_DT BETWEEN 'END_DT_START' AND 'END_DT_END'
  GROUP BY A.BSEG_ID, A.START_DT, A.END_DT, A.MDM_START_DTTM, A.MDM_END_DTTM

uploaded by Nuno Rodrigues

Retrieve Historical Bills for Accounts

SELECT 
  BILL.ACCT_ID,
  BILL.BILL_ID,
  BILL.BILL_DT,
  'C' AS TYPE,
  (SELECT
    MIN(START_DT)
  FROM
    CI_BSEG BSEG
  WHERE
    BSEG.BILL_ID = BILL.BILL_ID) AS MIN_BSEG_DT
FROM
  CI_BILL BILL
WHERE
  BILL.ACCT_ID = 'INSERT_ACCOUNT ID'
  AND BILL.BILL_STAT_FLG = 'C'  
  AND BILL.BILL_DT > 'INSERT_INITIAL_SEARCH_DATE'
  AND BILL.BILL_DT <= 'INSERT_FINAL_SEARCH_DATE'

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

Billing History (UOM/TOU/SQI) for Service Agreement

SELECT DISTINCT B.UOM_CD, B.TOU_CD, B.SQI_CD
       , C.DESCR AS UOM_DESCR, D.DESCR AS TOU_DESCR, E.DESCR AS SQI_DESCR
  FROM CI_BSEG A
      ,CI_BSEG_SQ B
      ,CI_UOM_L C
      ,CI_TOU_L D
      ,CI_SQI_L E
 WHERE A.SA_ID = 'INSERT_SERVICE_AGREEMENT_ID'
   AND A.BSEG_ID = B.BSEG_ID
   AND (B.UOM_CD = C.UOM_CD (+) AND C.LANGUAGE_CD (+) = 'INSERT_LANGUAGE_CD_MOSTLY_ENG')   
   AND (B.TOU_CD = D.TOU_CD (+) AND D.LANGUAGE_CD (+) = 'INSERT_LANGUAGE_CD_MOSTLY_ENG')
   AND (B.SQI_CD = E.SQI_CD (+) AND E.LANGUAGE_CD (+) = 'INSERT_LANGUAGE_CD_MOSTLY_ENG')

uploaded by Nuno Rodrigues

Service Agreement Annual Bill Amount

SELECT SUM(A.CUR_AMT) AS CUR_AMT
          , SUM(A.TOT_AMT) AS TOT_AMT           
          ,to_char(B.START_DT, 'YYYY') as Year
          , COUNT(A.FT_ID) AS NUM_FINANCIAL_TRANSACTIONS  
   FROM CI_FT A          
          , CI_BSEG B 
 WHERE A.SA_ID = 'INSERT_SA_ID'
     AND A.FT_TYPE_FLG = 'BS'   
     AND A.SIBLING_ID = B.BSEG_ID   
     AND NOT EXISTS (SELECT 'X' FROM CI_FT C                    
                                WHERE C.SIBLING_ID = A.SIBLING_ID                     
                                    AND C.FT_TYPE_FLG = 'BX')
GROUP BY to_char(B.START_DT, 'YYYY')

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

Retrieve Match Events for Bill

SELECT DISTINCT ME.MATCH_EVT_ID,
                ME.MEVT_STATUS_FLG,
                ME.ARCHIVE_SW
FROM   CI_BSEG BS,
       CI_FT FT,
       CI_MATCH_EVT ME
WHERE  FT.SIBLING_ID = BS.BSEG_ID
       AND BS.BILL_ID = 'INSERT_BILL_ID'
       AND ( FT.FT_TYPE_FLG = 'BS'
              OR FT.FT_TYPE_FLG = 'BX' )
       AND ME.MATCH_EVT_ID = FT.MATCH_EVT_ID

uploaded by Nuno Rodrigues

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