3 queries found for "Bill Segment Calc Header"

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 Billable Charge Id

select  billable_chg_id
from ci_bill_chg bc
where billable_chg_id not in (select billable_chg_id from ci_bseg_calc bc1 where trim(bc.billable_chg_id)=trim(bc1.billable_chg_id))
and billable_chg_id not in (select char_val_fk1 from ci_td_entry_cha where trim(char_val_fk1) = billable_chg_id)
AND DESCR_ON_BILL<>'xxxxx'
AND BC.BILLABLE_CHG_STAT = '10'

uploaded by Lipika Roy

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