19 queries found for "Bill"

Get all from Bill

select * 
from CI_BILL

uploaded by Nuno Rodrigues

Get specific Bill

select * 
from CI_BILL
where 
BILL_ID = 'XXXX'

uploaded by Nuno Rodrigues

Retrieve Pending Bill Exceptions

SELECT EXCP.*
FROM CI_BILL BILL, CI_BILL_EXCP EXCP
WHERE BILL.BILL_ID = 'INSERT_BILL_ID_HERE'
AND BILL.BILL_STAT_FLG = 'P'
AND BILL.BILL_ID = EXCP.BILL_ID
ORDER BY EXCP.CRE_DTTM DESC

uploaded by Nuno Rodrigues

Retrieve Off Cycle Bill Generator's Bill

SELECT
  BILL_ID
FROM
  CI_BILL
WHERE
  OFFCYC_BGEN_ID = 'INSERT_OFFCYC_GEN_ID'

uploaded by Nuno Rodrigues

Financial Transactions on Bill created by Offcyle Generator

SELECT FT.FT_ID,
       FT.SA_ID,
       FT.ACCOUNTING_DT,
       FT.CUR_AMT,
       FT.TOT_AMT,
       FT.FT_TYPE_FLG,
       LK2.DESCR SHOW_ON_BILL_DESCR,
       FT.CURRENCY_CD,
       LK1.DESCR FT_TYPE_DESCR
FROM CI_FT FT,
     CI_LOOKUP LK1,
     CI_LOOKUP LK2,
     CI_BILL BI
WHERE BI.OFFCYC_BGEN_ID = 'INSERT_OFFCYC_BGEN_ID'
AND FT.BILL_ID   = BI.BILL_ID
AND LK1.FIELD_NAME = 'FT_TYPE_FLG'
AND LK1.FIELD_VALUE = FT.FT_TYPE_FLG
AND LK1.LANGUAGE_CD = 'INSERT_LANGUAGE_CD_MOSTLY_ENG'
AND LK2.FIELD_NAME = 'F1_YESNO_FLG'
AND LK2.FIELD_VALUE = FT.SHOW_ON_BILL_SW
AND LK2.LANGUAGE_CD = 'INSERT_LANGUAGE_CD_MOSTLY_ENG'
ORDER BY FT.ACCOUNTING_DT, FT.SA_ID, FT.FT_ID

uploaded by Nuno Rodrigues

Financial Transactions on Bill

SELECT FT.FT_ID,
       FT.SA_ID,
       FT.ACCOUNTING_DT,
       FT.CUR_AMT,
       FT.TOT_AMT,
       FT.FT_TYPE_FLG,
       LK2.DESCR SHOW_ON_BILL_DESCR,
       FT.CURRENCY_CD,
       LK1.DESCR FT_TYPE_DESCR
FROM CI_FT FT,
     CI_LOOKUP LK1,
     CI_LOOKUP LK2,
     CI_BILL BI
WHERE BI.BILL_ID = 'INSERT_BILL_ID'
AND FT.BILL_ID   = BI.BILL_ID
AND LK1.FIELD_NAME = 'FT_TYPE_FLG'
AND LK1.FIELD_VALUE = FT.FT_TYPE_FLG
AND LK1.LANGUAGE_CD = 'INSERT_LANGUAGE_CD_MOSTLY_ENG'
AND LK2.FIELD_NAME = 'F1_YESNO_FLG'
AND LK2.FIELD_VALUE = FT.SHOW_ON_BILL_SW
AND LK2.LANGUAGE_CD = 'INSERT_LANGUAGE_CD_MOSTLY_ENG'
ORDER BY FT.ACCOUNTING_DT, FT.SA_ID, FT.FT_ID

uploaded by Nuno Rodrigues

Check if Account has Pending Bill

SELECT
  BILL_ID as PENDING_BILL
FROM
   CI_BILL BILL
WHERE 
   BILL.ACCT_ID = 'INSERT_ACCOUNT_ID'
   AND BILL.BILL_STAT_FLG = 'P'

uploaded by Nuno Rodrigues

Retrieve Latest Bill of Account ( Last Bill )

SELECT
  BILL1.BILL_DT,
  BILL1.BILL_ID,
  BILL1.DUE_DT
FROM
  CI_BILL BILL1
WHERE
  BILL1.ACCT_ID = 'INSERT_ACCOUNT_ID'
  AND BILL1.BILL_STAT_FLG = 'C'
  AND BILL1.BILL_DT = (SELECT MAX(BILL2.BILL_DT)
                                     FROM CI_BILL BILL2
                                  WHERE BILL2.ACCT_ID = BILL1.ACCT_ID
                                       AND BILL2.BILL_STAT_FLG = 'C'
                                         AND BILL2.BILL_DT <= SYSDATE)

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 Bills for Customer

SELECT
  BI.ACCT_ID,
  BI.BILL_STAT_FLG,
  BI.BILL_DT,
  BI.BILL_ID,
  BI.CRE_DTTM,
  NM.ENTITY_NAME,
  BI.DUE_DT,
  LK.DESCR AS BILL_STAT_DESCR
FROM
  CI_BILL BI,
   CI_ACCT_PER AP,
   CI_PER_NAME NM,
   CI_LOOKUP_VAL_L LK
WHERE
  NM.ENTITY_NAME_UPR LIKE UPPER('INSERT_CUSTOMER_NAME')
   AND BI.ACCT_ID = AP.ACCT_ID
   AND AP.PER_ID = NM.PER_ID
   AND LK.FIELD_NAME = 'BILL_STAT_FLG'
   AND LK.LANGUAGE_CD = :LANGUAGE
   AND LK.FIELD_VALUE = BI.BILL_STAT_FLG
   ORDER BY NM.ENTITY_NAME_UPR ASC
  , BI.ACCT_ID ASC
  ,  BI.BILL_DT DESC
  , BI.CRE_DTTM DESC

uploaded by Nuno Rodrigues

Get Bills for Account

SELECT
  BI.ACCT_ID,
  BI.BILL_STAT_FLG,
  BI.BILL_DT,
  BI.BILL_ID,
  BI.CRE_DTTM,
  NM.ENTITY_NAME,
  BI.DUE_DT,
  LK.DESCR AS BILL_STAT_DESCR
FROM
  CI_BILL BI,
   CI_ACCT_PER AP,
   CI_PER_NAME NM,
   CI_LOOKUP_VAL_L LK
WHERE
  BI.ACCT_ID = 'INSERT_ACCOUNT_ID'
   AND 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 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

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

Bills completed on a particular date

SELECT COUNT(*)  ,TRUNC (BILL_DT)
FROM CISADM.CI_BILL
WHERE BILL_STAT_FLG = 'C'
AND TRUNC(COMPLETE_DTTM) =TO_DATE ('25-MAY-2015','DD-MMM-YYYY')
GROUP BY TRUNC(BILL_DT)
ORDER BY TRUNC(BILL_DT);

uploaded by Vinayak Gadgil

Retrieve the number of bills generated by the BILLING batch for the given input date

SELECT COUNT(*) FROM
CI_BILL 
WHERE BILL_DT = 'Insert input date'
AND BILL_CYC_CD <> ' ';

uploaded by Roopesh Rekhawar

Dollars billed for particular bill cycle and business date.

select sum(cur_amt) from cisadm.ci_ft a where a.ft_type_flg = 'BS'
and parent_id in (select bill_id from cisadm.ci_bill where bill_cyc_cd = 'MDXX'   and bill_dt = 'DD-MMM-YYYY');	

uploaded by Vinayak Gadgil

Pending Bills for particular bill cycles

--This query is used by CSR to sort out pending bills ordering by bill_cycle.

SELECT COUNT(*), BILL_CYC_CD
  ,TRUNC(CRE_DTTM)
FROM CISADM.CI_BILL
WHERE BILL_STAT_FLG = 'P'
AND TRUNC(CRE_DTTM) = TO_DATE ('00-XXX-0000','DD-MON-YYYY')
GROUP BY TRUNC(CRE_DTTM),BILL_CYC_CD
ORDER BY TRUNC(CRE_DTTM);

uploaded by Vinayak Gadgil

Count of bills by month in a given year

SELECT JAN,
  FEB,
  MAR,
  APR,
  MAY,
  JUN,
  JUL,
  AUG,
  SEP,
  OCT,
  NOV,
  DEC
FROM (SELECT BILL_ID, TO_CHAR (BILL_DT, 'MON') MON
FROM CI_BILL
WHERE TO_CHAR(BILL_DT,'YYYY')=:year) PIVOT (COUNT(BILL_ID) FOR MON IN ('JAN' AS JAN, 'FEB' AS FEB, 'MAR' AS MAR, 'APR' AS APR, 'MAY' AS MAY, 'JUN' AS JUN, 'JUL' AS JUL, 'AUG' AS AUG, 'SEP' AS SEP, 'OCT' AS OCT, 'NOV' AS NOV, 'DEC' AS DEC));

uploaded by Venkata Sai Bhargav Kakunuri

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

Retrieve Account's Last Bill Details

SELECT bil1.acct_id,
  bil1.due_dt last_bill_due_dt,
  bil1.bill_id last_bill_id,
  bil1.BILL_DT LAst_bill_dt,
  ACCT.CUST_CL_CD
FROM ci_bill bil1,
  ci_acct acct
WHERE bil1.acct_id = 'INSERT_ACCOUNT_ID'
AND bil1.bill_dt =
  (SELECT MAX(BILL_DT)
  FROM ci_bill bil2
  WHERE bil2.acct_id = bil1.acct_id
  )
AND bil1.acct_id = acct.acct_id;

uploaded by Ninad Deshpande