4 queries found for "Bill Characteristic"

Get all from Bill Characteristic

select * 
from CI_BILL_CHAR

uploaded by Nuno Rodrigues

Get specific Bill Characteristic

select * 
from CI_BILL_CHAR
where 
BILL_ID = 'XXXX' and CHAR_TYPE_CD = 'XXXX'

uploaded by Nuno Rodrigues

Get Bill with specific Characteristic value

SELECT
  BI.ACCT_ID,
  BI.BILL_STAT_FLG,
  BI.BILL_DT,
  BI.BILL_ID,
  BI.CRE_DTTM,
  BI.DUE_DT,  
  NM.ENTITY_NAME,
  LK.DESCR AS BILL_STAT_DESCR
FROM
  CI_BILL BI,
  CI_ACCT_PER AP,
  CI_PER_NAME NM,
  CI_BILL_CHAR BIC,
  CI_CHAR_TYPE CHTY,
   CI_LOOKUP_VAL_L LK
WHERE
  BI.ACCT_ID = AP.ACCT_ID
  AND AP.MAIN_CUST_SW = 'Y'
  AND NM.NAME_TYPE_FLG =   'PRIM'
  AND AP.PER_ID = NM.PER_ID
  AND BI.BILL_ID = BIC.BILL_ID
  AND TRIM(BIC.CHAR_TYPE_CD) =  'INSERT_CHAR_TYPE_CD'
  AND TRIM(BIC.SRCH_CHAR_VAL) LIKE UPPER('INSERT_SEARCH_VALUE')
  AND CHTY.CHAR_TYPE_CD = BIC.CHAR_TYPE_CD
   AND LK.FIELD_NAME = 'BILL_STAT_FLG'
  AND LK.LANGUAGE_CD = :LANGUAGE
  AND LK.FIELD_VALUE = BI.BILL_STAT_FLG   
ORDER BY BI.ACCT_ID ASC
  , BI.BILL_DT DESC
  , BI.CRE_DTTM DESC  
  

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