2 queries found for "Bill Segment Service Quantity"

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

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