/sh/BillingCHK.sh_940315bk

#! /bin/ksh
. /osms_d1/ratedata/.profile

cd /osms_d1/ratedata/BILLING/CHK
echo "cd /osms_d1/ratedata/BILLING/CHK"

chktime=$(gawk 'BEGIN {printf("%s\n",strftime("%Y%m%d",systime()))}');
exec 1> /osms_d1/ratedata/BILLING/CHK/BillingCHK$chktime.log
exec 2> /osms_d1/ratedata/BILLING/CHK/BillingCHK$chktime.err


if (( $#!=5 )); then
   echo "*********************************************************************************"
   echo "*  Author: ??                                                             *****"
   echo "*  Object: For Check Billing Data                                               *"
   echo "*  BillingCHK.sh billing_period billing_cycle BillingBeginDate BillingEndDate DB*"
   echo "*  BillingCHK.sh 200207 02 20020625 20020724                                    *"
   echo "*********************************************************************************"
   exit
elif (( $#==5 )); then
   billing_period=$1
   billing_cycle=$2
   BeginDate=$3
   EndDate=$4
   DB=$5
   BegingDatetime="$BeginDate 00:00:00"
   EndDatetime="$EndDate 23:59:59"
fi

if [[ $DB = PROD ]]; then
   connectStr="fibills/fibills@osms" 
elif [[ $DB = DEMO ]]; then
   connectStr="fibills/billsdemo@demo" 
elif [[ $DB = ITTEST ]]; then
   connectStr="fibills/itosms02@itosms02"
elif [[ $DB = DEMO2 ]]; then
   connectStr="fibills/demo2@demo2"

fi

echo $connectStr

##sqlplus "fibills/billsdemo@demo"  <<END
sqlplus "$connectStr"  <<END
set verify off
set pagesize 50000
set time on
--set linesize 200
spool $BILLING/CHK/BillingCHK$chktime.txt

---?????? Begin ---

---??ID???????? ---'
select b.subscr_nbr, b.phone_nbr, b.cust_id, b.user_catg
from cw_tb_subscr_mbl b
where b.user_catg in ('BUS','PVPN')
and ((length(b.cust_id) != 8) or ( nvl(sex_indic,'x') <> 'C'));


--- ??ID????????  ---'
select a.subscr_nbr, a.phone_nbr, a.cust_id, a.user_catg, a.sex_indic
from cw_tb_subscr_mbl a
where a.user_catg = 'CITIZ' 
and ( length(a.cust_id) != 10
or decode(substr(a.cust_id, 2, 1), '1', 'M', '2', 'F') != a.SEX_INDIC );


---?????????????????. ---'
select a.subscr_nbr, a.phone_nbr, a.acct_id, a.acct_nme,
       a.PAYMENT_METHOD, a.PAYMENT_METHOD_ORG
from cw_tb_accts a
where ((a.PAYMENT_METHOD is null) or (a.PAYMENT_METHOD_ORG is null));


--- ???? billing_cycle ??? 01 cycle ? ---
select a.subscr_nbr, a.phone_nbr, a.cust_id, a.user_catg ,a.billing_cycle
from cw_tb_subscr_mbl a
where a.user_catg = 'BUS' 
and a.svc_status = 'ACTV'
and length(a.cust_id) = 8
and a.billing_cycle <> '01'
and a.subscr_nbr not in ('00980676') ;



---sample18 ??ID???????? ---'
select b.subscr_nbr, b.phone_nbr, b.cust_id, b.user_catg
from cw_tb_subscr_mbl b
where b.user_catg = 'BUS' 
and length(b.cust_id) != 8;

---sample19 ??ID????????  ---'
select a.subscr_nbr, a.phone_nbr, a.cust_id, a.user_catg, a.sex_indic
from cw_tb_subscr_mbl a
where a.user_catg = 'CITIZ' 
and ( length(a.cust_id) != 10
or decode(substr(a.cust_id, 2, 1), '1', 'M', '2', 'F') != a.SEX_INDIC );

---sample20 ??????????---'
select b.subscr_nbr, b.phone_nbr, b.sex_indic , b.user_catg
from  cw_tb_subscr_mbl b
where ( (sex_indic = 'C' and user_catg not in ('BUS','PVPN')) 
   or (sex_indic = 'M' and user_catg  in ( 'BUS','PVPN')) 
   or (sex_indic = 'F' and user_catg  in ( 'BUS','PVPN')) );


---????,???????---
---(??02 cycle ? 00980676???90??????)---
select  BILLING_CYCLE, USER_CATG, SEX_INDIC , COUNT(*) from cw_tb_subscr_mbl
 WHERE billing_cycle = '$billing_cycle'
   AND ((OUT_SVC_DATE IS NULL) OR (OUT_SVC_DATE > SYSDATE -90))
   AND SUBSCR_NBR != '00980676'
GROUP BY BILLING_CYCLE, USER_CATG, SEX_INDIC
ORDER BY BILLING_CYCLE, USER_CATG, SEX_INDIC ;



---sample21 ???????????,??????? ---'
select b.subscr_nbr, b.phone_nbr, b.payment_method_org, b.bank_acct_nbr_org
from cw_tb_accts b
where b.payment_method_org != 'CASH'
AND b.BANK_ACCT_NBR_org IS NULL;


---sample21_2 ???????????,??????? ---'
select b.subscr_nbr, b.phone_nbr, b.payment_method, b.bank_acct_nbr
from cw_tb_accts b
where b.payment_method != 'CASH'
AND b.BANK_ACCT_NBR IS NULL;


---sample22 ?????????,??????? ---'
select b.subscr_nbr,b.phone_nbr, b.payment_method_org, b.bank_acct_nbr_org
from  cw_tb_accts b
where  b.payment_method_org = 'CASH'
AND b.BANK_ACCT_NBR_org IS not NULL;

---sample22_2 ?????????,??????? ---'
select b.subscr_nbr,b.phone_nbr, b.payment_method, b.bank_acct_nbr
from  cw_tb_accts b
where  b.payment_method = 'CASH'
AND b.BANK_ACCT_NBR IS not NULL;


---sample23 ?????????????????????? ---'
select b.subscr_nbr, a.phone_nbr, a.cust_nme, b.acct_nme
from cw_tb_subscr_mbl a, cw_tb_accts b
where a.subscr_nbr = b.subscr_nbr
and a.cust_nme != b.acct_nme;

---??accts?mbl??
select subscr_nbr from cw_tb_accts     
minus
select subscr_nbr from cw_tb_subscr_mbl ;

---??mbl?accts??
select subscr_nbr from cw_tb_subscr_mbl     
minus
select subscr_nbr from cw_tb_accts ;


---??mbl?mon_item??
select distinct subscr_nbr from cw_tb_subscr_mbl
minus
select distinct subscr_nbr from cw_tb_subscr_mon_item;


---??mon_item?mbl??
select distinct subscr_nbr from cw_tb_subscr_mon_item
minus
select distinct subscr_nbr from cw_tb_subscr_mbl;


---cw_tb_subscr_plan,cw_tb_subscr_package???????? ---
---2001/07???????---
--select a.subscr_nbr, a.package_cde , to_char(a.plan_eff_date,'yyyymmdd hh24:mi:ss') ,
--       to_char(b.sp_eff_date,'yyyymmdd hh24:mi:ss')
--from cw_tb_subscr_plan a , cw_tb_subscr_package b 
--where a.subscr_nbr=b.subscr_nbr
--  and a.package_cde=b.package_cde
--  and to_char(a.plan_eff_date,'yyyymmdd') != to_char(b.sp_eff_date, 'yyyymmdd');




--?? 567???? ????---
select * from cw_tb_call_dtl@dblink_rating
where billing_period = '$billing_period'
and billing_cycle = '$billing_cycle'
and rate_area_cde in ('I23','I24')
and call_duration = 0;


---????????????---
SELECT cp_id, cp_sub_id, rate_seq, count(*) 
  FROM CW_TB_MTYPE_VALUE_ADD_REC
 WHERE trunc(eff_date) <= TO_DATE('$EndDate','YYYYMMDD')
   and ((end_date is null) or (trunc(end_date) >= TO_DATE('$BeginDate','YYYYMMDD')))
   and  cp_id||cp_sub_id||rate_seq not in (select cp_id||cp_sub_id||rate_seq 
                                             from cw_tb_content 
                                            where pay_type='MON')
 GROUP By cp_id, cp_sub_id, rate_seq;


---?????,?????---
select subscr_nbr ,cp_id ,cp_sub_id ,rate_seq , cp_sub_dir_id , count(*)  
  from CW_TB_MTYPE_VALUE_ADD_REC
 where end_date is  null
 group by subscr_nbr ,cp_id ,cp_sub_id ,rate_seq , cp_sub_dir_id
having count(*) >1;



---?????,????? 2 ---

select count(*)
from CW_TB_MTYPE_VALUE_ADD_REC a
     , CW_TB_MTYPE_VALUE_ADD_REC b
     , cw_tb_subscr_mbl c
where a.subscr_nbr = b.subscr_nbr 
  and a.cp_id = b.cp_id 
  and a.cp_sub_id = b.cp_sub_id
  and nvl(a.cp_sub_dir_id,'0')=nvl(b.cp_sub_dir_id,'0') 
  and a.rate_seq = b.rate_seq
  and a.end_date is not null 
  and b.end_date is null
  and b.eff_date < a.end_date
  and b.subscr_nbr= c.subscr_nbr
  and a.eff_date >= to_date('$BeginDate','YYYYMMDD') ;




---?????,????? 3 ---

SELECT CP_ID, CP_SUB_ID, NVL(CP_SUB_DIR_ID,'0'), SUBSCR_NBR, COUNT(*)
FROM CW_TB_MTYPE_VALUE_ADD_REC
WHERE END_DATE IS NULL
GROUP BY CP_ID, CP_SUB_ID, NVL(CP_SUB_DIR_ID,'0'), SUBSCR_NBR
HAVING COUNT(*) > 1 ;






---????????????---
select distinct cp_id, cp_sub_id
  from cw_tb_subscr_usage_ctl
 where billing_period='$billing_period'
   and billing_cycle = '$billing_cycle'
   and cp_id||cp_sub_id||rate_seq  not in
       (select cp_id||cp_sub_id||rate_seq 
          from cw_tb_content WHERE PAY_TYPE !='MON'
           and trunc(service_eff_time) <= TO_DATE('$EndDate','YYYYMMDD')
           and (service_end_time is null 
            or TO_DATE('$BeginDate','YYYYMMDD') <= trunc(service_end_time)));

--????????????,???????--
select distinct cp_id, cp_sub_id
  from cw_tb_subscr_usage_ctl
 where billing_period='$billing_period'
   and billing_cycle = '$billing_cycle'
   and cp_id||cp_sub_id||rate_seq  not in
       (select cp_id||cp_sub_id||rate_seq
          from cw_tb_content WHERE PAY_TYPE !='MON'); 

--???content???cw_tb_usage_ctl
SELECT cp_id, cp_sub_id, count(*) ,sum(amount_count), sum(usage_count)
FROM CW_TB_SUBSCR_USAGE_CTL A
 WHERE BILLING_PERIOD='$billing_period'
   and BILLING_CYCLE = '$billing_cycle'
   AND EXISTS (SELECT * FROM CW_TB_CONTENT
                WHERE CP_ID=A.CP_ID 
                  AND CP_SUB_ID=A.CP_SUB_ID
                  AND RATE_SEQ=A.RATE_SEQ
                  AND SERVICE_END_time <TO_DATE('$BeginDate','YYYYMMDD')
                  and pay_type = 'USAGE') 
 GROUP By cp_id, cp_sub_id;

-- ???
--New 

select area_cde,phone_nbr,si_svc_item,chgit_cde,
       acct_nbr,mon_rate_org,si_eff_date,si_end_date,
       nvl(mon_fee_day_indic,'N') mon_fee_day_indic,
       nvl(mon_fee_days,0) mon_fee_days,rowid
 from cw_tb_subscr_svcit a
  where subscr_nbr in (select subscr_nbr from cw_tb_subscr_mbl
                        where billing_cycle='$billing_cycle')
    and a.si_eff_date <= to_date('$EndDatetime','yyyymmddhh24:mi:ss')
    and ((a.si_end_date >= to_date('$BegingDatetime','yyyymmddhh24:mi:ss') or (a.si_end_date is null)))
    and a.mon_rate_org is not null
    and not exists (select * from cw_tb_fix_rate
                    where chgit_cde=a.chgit_cde
                    and RATE_CDE=a.mon_rate_org) ;                            
 
--subscr_mon_item
 select a.* from cw_tb_subscr_mon_item a
  where subscr_nbr in (select subscr_nbr from cw_tb_subscr_mbl
                        where billing_cycle='$billing_cycle')
    and a.EFF_DATE <= to_date('$EndDatetime','yyyymmddhh24:mi:ss')
    and ((a.END_DATE >= to_date('$BegingDatetime','yyyymmddhh24:mi:ss') or (a.END_DATE is null)))
    and a.MON_RATE is not null
    and not exists (select * from cw_tb_fix_rate
                    where chgit_cde=a.PACKAGE_TYPE
                    and RATE_CDE=a.MON_RATE) ;
  

-- MON_RATE IS NULL
select a.* from cw_tb_subscr_mon_item a
where a.MON_RATE is null;




--???????,???????????
select a.subscr_nbr, count(*) from cw_tb_subscr_mon_item a
 where subscr_nbr in (select subscr_nbr from cw_tb_subscr_mbl
                       where billing_cycle='$billing_cycle')
   and a.EFF_DATE <= to_date('$EndDatetime','yyyymmddhh24:mi:ss')
   and ((a.END_DATE >= to_date('$BegingDatetime','yyyymmddhh24:mi:ss') or (a.END_DATE is null)))
   and a.MON_RATE is not null
group by a.subscr_nbr    
having count(*) > '1' ;





--??????,? billing_period ??????
select count(*) from cw_tb_subscr_mbl
  where billing_cycle = '$billing_cycle'
    and subscr_nbr in ( select subscr_nbr from CW_TB_SUBSCR_BILL_ITEM
                         where billing_period = '$billing_period' );

--???,?????,? billing_period ??????
select subscr_nbr, count(*) from cw_tb_subscr_mbl
  where billing_cycle = '$billing_cycle'
    and subscr_nbr in ( select subscr_nbr from CW_TB_EMP_PAY
                         where billing_period = '$billing_period' )
group by subscr_nbr
having count(*) > 1 ;



--?? EMAIL ??1000 ?
select count(*) from cw_tb_call_dtl@dblink_rating
where billing_period = '$billing_period'
and billing_cycle = '$billing_cycle' 
and svc_item = 'EMAIL'
and call_duration > '1000';


--?????????
--A. MBL ??????????
select subscr_nbr, BILLING_CYCLE, B_CITY, B_DISTRICT, B_ADDRESS1  
  from cw_tb_subscr_mbl
 where ((B_CITY is null) or (B_DISTRICT is null) or (B_ADDRESS1 is null)) ;



--A_1. MBL ??????????
select subscr_nbr, BILLING_CYCLE, B_CITY, B_DISTRICT, B_ADDRESS1, NVL(B_ZIP_CDE,'NULL') B_ZIP_CDE
  from cw_tb_subscr_mbl
 where ((B_CITY is null) or (B_DISTRICT is null) or (B_ADDRESS1 is null) OR (B_ZIP_CDE IS NULL));
 



--A_2. MBL org??????????
-------???,??????????.
select count(*)
  from cw_tb_subscr_mbl m 
 where ((m.B_CITY_ORG is null) or (m.B_DISTRICT_ORG is null) or (m.B_ADDRESS1_ORG is null) or (m.B_ZIP_CDE_ORG is null))
   and m.subscr_nbr in (
                      select subscr_nbr
                        from cw_tb_subscr_mbl
                       where billing_cycle = '$billing_cycle'
                         and in_svc_date <= to_date('$EndDatetime','yyyymmdd hh24:mi:ss')
                         and ((out_svc_date is null) or (out_svc_date >= to_date('$BegingDatetime','yyyymmdd hh24:mi:ss')))
                      union
                      select sm.subscr_nbr
                        from cw_tb_subscr_mbl sm, cw_tb_subscr_billing_term sbt
                       where sm.subscr_nbr = sbt.subscr_nbr
                         and billing_period = '$billing_period'
                         and sm.billing_cycle = '$billing_cycle'
                      union
                      select subscr_nbr
                        from cw_tb_subscr_mbl b
                       where billing_cycle = '$billing_cycle'
                         and out_svc_date is not null
                         and out_svc_date < to_date('$BegingDatetime','yyyymmdd hh24:mi:ss')
                         and exists (select 'x' from cw_tb_subscr_adj
                       where subscr_nbr=b.subscr_nbr
                         and billing_period='$billing_period'
                         and adj_type is not null)
                     union
                      select subscr_nbr
                        from cw_tb_subscr_mbl c
                       where billing_cycle = 'billing_cycle'
                         and out_svc_date is not null
                         and out_svc_date < to_date('$BegingDatetime','yyyymmdd hh24:mi:ss')
                         and exists (select 'x' from cw_tb_acct_payment
                       where acct_nbr=c.acct_nbr
                         and billing_period='$billing_period')
                       ) ;
                       
                       
  



--B. ACCTS ??????????
select subscr_nbr, BILLING_CYCLE, CITY, DISTRICT, ADDRESS1  
  from cw_tb_accts
 where CITY is null ;

--C. ACCTS ??????????
select subscr_nbr, BILLING_CYCLE, R_CITY, R_DISTRICT, R_ADDRESS1  
  from cw_tb_accts
 where R_CITY is null ;


--D. MBL & LOOKUP_CDE ??????
select distinct B_CITY from CW_TB_SUBSCR_MBL
MINUS
select distinct LOOKUP_CDE from CW_TB_LOOKUP_CDE
where lookup_type='CITY'
and DSCR is not null ;


--D_2. MBL B_CITY_ORG.ORG & LOOKUP_CDE ??????
select distinct B_CITY_ORG from CW_TB_SUBSCR_MBL
MINUS
select distinct LOOKUP_CDE from CW_TB_LOOKUP_CDE
where lookup_type='CITY'
and DSCR is not null ;



--D_3. MBL B_CITY_ORG.ORG & LOOKUP_CDE ??????
-------???,???????? B_CITY_ORG ??
select count(*)
  from (
        select distinct B_CITY_ORG from CW_TB_SUBSCR_MBL
         where billing_cycle = '$billing_cycle'
           and in_svc_date <= to_date('$EndDatetime','yyyymmdd hh24:mi:ss')
        MINUS
        select distinct LOOKUP_CDE from CW_TB_LOOKUP_CDE
         where lookup_type='CITY'
           and DSCR is not null 
        );
        
--D_4 ???????? 5 ? : 
SELECT * FROM CW_TB_SUBSCR_MBL
 WHERE ((length(B_ZIP_CDE) > 5) or (length(B_ZIP_CDE_org) > 5)) ;





--cw_tb_invoice_autovoid ???????????--
select billing_period, billing_cycle, cust_id, proc_status
  from cw_tb_invoice_autovoid
 where billing_cycle !='01'
   and proc_status IS NULL;

--CW_TB_SUBSCR_CHGIT TAX_INDIC TAX_RATE IS NULL--
--?????;??Update 
--
select count(*)
  from cw_tb_subscr_chgit
 where billing_period = '$billing_period'
   and billing_cycle = '$billing_cycle'
   and (tax_indic is null or tax_rate is null);


--Check TAX_INDIC and TAX_RATE value
--?????;??Update 
--
select distinct CHRG_CDE, TAX_INDIC, TAX_RATE from cw_tb_subscr_chgit
 where billing_period = '$billing_period'
   and billing_cycle = '$billing_cycle' ;


--?????,?????????????
--??insert,?????
select  *  from cw_tb_subscr_billing_term
where  billing_period='$billing_period'
and  billing_cycle = '$billing_cycle' 
order by subscr_nbr ;


--?????,?????????????_2
--??insert,?????
select subscr_nbr, count(*)
  from cw_tb_subscr_billing_term
 where billing_period='$billing_period' 
   and billing_cycle = '$billing_cycle' 
group by subscr_nbr
having count(*) > 1 ;





-- call_dtl's "BC_CHGIT"  and chrg_item's "CHGIT_CDE" not match chk --
--1.
select distinct BC_CHGIT from cw_tb_call_dtl@dblink_rating
 where  billing_period = '$billing_period'
   and billing_cycle = '$billing_cycle'
minus
select CHGIT_CDE from cw_tb_chrg_item ;


--2.
select A.BC_CHGIT
from (
      select distinct BC_CHGIT from cw_tb_call_dtl@dblink_rating
       where  billing_period = '$billing_period'
         and billing_cycle = '$billing_cycle'
      union 
      select distinct BC_CHGIT from cw_tb_smpay_call_dtl@dblink_rating
       where  billing_period = '$billing_period'
         and billing_cycle = '$billing_cycle'
      union
      select distinct BC_CHGIT from cw_tb_merch_dtl@dblink_rating
       where  billing_period = '$billing_period'
         and billing_cycle = '$billing_cycle'
     ) A
minus
select CHGIT_CDE from cw_tb_chrg_item ;



-- ????,????????? --
select count(*) from cw_tb_merch_dtl@dblink_rating
where billing_period = '$billing_period'
  and billing_cycle = '$billing_cycle' ;


----??90?????????? ----
SELECT SUBSCR_NBR , COUNT(*) FROM CW_TB_STOP_MON
where smon_st_date<=to_date('$EndDatetime','yyyymmdd hh24:mi:ss')
and (smon_ed_date>=to_date('$BegingDatetime','yyyymmdd hh24:mi:ss') or smon_ed_date is null)
GROUP BY SUBSCR_NBR
HAVING COUNT(*) > '1' ;



----  ??????,?ADJ_TYPE???????.  ----
select SUBSCR_NBR,BILLING_PERIOD,ADJ_TYPE,count(*) from cw_tb_subscr_adj
where billing_period = '$billing_period'
group by SUBSCR_NBR,BILLING_PERIOD,ADJ_TYPE
having count(*) > 1 ;


----  ??????,subscr_adj ? subscr_agj_dtl ?????  ----
select a.subscr_nbr, a.billing_period, c.billing_cycle, a.adj_type, a.balance, b.adj_amt 
  from (
        select subscr_nbr, billing_period, adj_type, sum(balance) balance from cw_tb_subscr_adj
         where billing_period = '$billing_period'
        group by subscr_nbr, billing_period, adj_type
        order by subscr_nbr 
       ) a ,
       (
        select subscr_nbr, billing_period, adj_type, sum(adj_amt) adj_amt from cw_tb_subscr_adj_dtl
         where billing_period = '$billing_period'
        group by subscr_nbr, billing_period, adj_type
        order by subscr_nbr 
       ) b,
       cw_tb_subscr_mbl c
 where a.subscr_nbr = b.subscr_nbr
   and a.billing_period = b.billing_period
   and a.adj_type = b.adj_type
   and a.balance <> b.adj_amt
   and a.subscr_nbr = c.subscr_nbr  ;
   
   



----  ??????? ----
select a.subscr_nbr,c.phone_nbr,b.billing_cycle,c.billing_cycle
from cw_tb_cinf_wo a,
(select subscr_nbr,billing_cycle from cw_tb_nsvc_wo) b,
(select subscr_nbr,phone_nbr,billing_cycle from cw_tb_subscr_mbl) c
where a.subscr_nbr=b.subscr_nbr
and a.subscr_nbr=c.subscr_nbr
and a.fr_cust_id is not null
and a.to_cust_id is not null
and trunc(a.wo_date) > sysdate-30
and a.wo_issuer like 'CR%'
and b.billing_cycle<>c.billing_cycle ;

---- ??????????????45? ----
SELECT subscr_nbr,phone_nbr,cust_nme,cust_id,
       in_svc_date,package_cde,package_grp,
       emp_dept,emp_nbr,package_catg,svc_status
  FROM cw_tb_subscr_mbl
 WHERE cust_id = '16080042'
   AND package_cde !='007'
   AND svc_status NOT IN ('TERM','COWN','LOST','DETE','TEDE')
   AND SUBSCR_NBR NOT IN 
       (
'01708887','01708888','01708889','01708890','01708891',
'01708892','01708893','01708894','01708895','01708896',
'01708897','01708898','01708899','01708900','01708901',
'01708902','01708903','01708904','01708905','01708906',
'01708907','01708908','01708909','01708910','01708911',
'01708912','01708913','01708914','01708915','01708919',
'01708920','01708921','01708922','01708923','01708924',
'01708925','01708926','01708927','01708928','01708929',
'01708930','01708931','01708932','01717951','01766183'
);




----????????????????? ----
--SELECT SUBSCR_NBR, PACKAGE_CDE, COUNT(*) FROM CW_TB_SUBSCR_PACKAGE
--where SP_EFF_DATE <= to_date('$EndDatetime','yyyymmdd hh24:mi:ss')
--and ((SP_END_DATE >= to_date('$BegingDatetime','yyyymmdd hh24:mi:ss')) or (SP_END_DATE is null))
--GROUP BY SUBSCR_NBR, PACKAGE_CDE
--HAVING COUNT(*) > '1' ;




----????????????,????----
----???(DEMO)??????,???????----
----cw_tb_subscr_bill.unapid_amt update ? '0',???????. ----
select a.subscr_nbr, b.billing_period, a.ovpay_amt, b.unpaid_amt  
from cw_tb_subscr_mbl a, cw_tb_subscr_bill b
where a.billing_cycle='$billing_cycle'
and a.subscr_nbr=b.subscr_nbr
and a.billing_cycle=b.billing_cycle
and a.ovpay_amt > '0'
and b.unpaid_amt > '0'
order by a.subscr_nbr ;



----????????????,????----
----???(DEMO)??????,???????----
----cw_tb_subscr_mbl.balance update ? '0',???????. ----
select billing_cycle, subscr_nbr, balance, OVPAY_AMT 
  from cw_tb_subscr_mbl
 where billing_cycle='$billing_cycle'
   and nvl(OVPAY_AMT,0) <> 0
   and nvl(balance,0) <> 0
order by billing_cycle, subscr_nbr ;



--RBT ???
SELECT DISTINCT MONTH_FEE
  FROM CW_TB_SMPAY_MTYPE ;



--RBT ??????????.
select a.SUBSCR_NBR, a.BC_CHGIT, a.EFF_DATE, a.END_DATE
  from cw_tb_smpay_mtype a
       , (
           select subscr_nbr, to_date(to_char(end_date,'yyyy/mm/dd hh24'),'yyyy/mm/dd hh24') end_date , count(*) from cw_tb_smpay_mtype
            where service_item='RBT'
              and end_date is not null
              and end_date > to_date('$BegingDatetime','yyyymmdd hh24:mi:ss') 
           group by subscr_nbr, end_date
           having count(*) > 1
           UNION
           select subscr_nbr, end_date, count(*) from cw_tb_smpay_mtype
            where service_item='RBT'
              and end_date is null
           group by subscr_nbr, end_date
           having count(*) > 1
          ) b
 where a.subscr_nbr=b.subscr_nbr
order by a.subscr_nbr ; 



--- CHRG_CDE  W10, NBRFE (??)???????. --
select BILLING_CYCLE, subscr_nbr, CHRG_CDE, to_char(CHRG_DATE,'yyyy/mm/dd hh24:mi') CHRG_DATE, count(*) from cw_tb_subscr_chgit
 where billing_period = '$billing_period'
   and billing_cycle='$billing_cycle'
   and batch_nbr is null
   and chrg_cde <> 'CDRPR'
GROUP BY BILLING_CYCLE, subscr_nbr, CHRG_CDE, to_char(CHRG_DATE,'yyyy/mm/dd hh24:mi')
HAVING COUNT(*) > 1
ORDER BY BILLING_CYCLE, subscr_nbr, CHRG_CDE;



--?????? CHRG_CDE = 'PEN01'
--2005/02/14 add by james
select subscr_nbr
  from cw_tb_subscr_pen
 where ( (CHRG_CDE is null) or (CHRG_CDE <> 'PEN01') ) ;


--????????????????
--2005/02/14 add by james
select subscr_nbr, count(*)
  from cw_tb_subscr_pen
group by subscr_nbr
having count(*) > 1 ;   

--???????????
--select a.subscr_nbr, a.pen_mon, a.pen_amt
--from cw_tb_subscr_pen a, cw_tb_subscr_mbl b
--where a.subscr_nbr = b.subscr_nbr
--  and a.billing_period = '$billing_period'
--  and b.billing_cycle='$billing_cycle'
--  and a.PEN_PAY_TYPE = 'OSMS';


--??????
select '??????? : '|| a.billing_period || ' - ' || a.billing_cycle  "????" ,
       '???? : '||b.due_date DUE_DATE
from cw_tb_aut_billing_cycle_prd a , cw_tb_billing_cycle b
where a.due_date is null 
  and a.billing_cycle=b.billing_cycle;

--???????
select '????? : '||GLOBAL_NAME "DB_name"
from global_name ;


---?????? END ---

spool off;
exit;
END
 

mailx -s "${billing_period}bc${billing_cycle}${DB} BillingCHK Report" kuo@fitel.net.tw < $BILLING/CHK/BillingCHK$chktime.txt
mailx -s "${billing_period}bc${billing_cycle}${DB} BillingCHK Report" jamesy2@fitel.net.tw < $BILLING/CHK/BillingCHK$chktime.txt
mailx -s "${billing_period}bc${billing_cycle}${DB} BillingCHK Report" lisa@fitel.net.tw < $BILLING/CHK/BillingCHK$chktime.txt
mailx -s "${billing_period}bc${billing_cycle}${DB} BillingCHK Report" sean@fitel.net.tw < $BILLING/CHK/BillingCHK$chktime.txt
mailx -s "${billing_period}bc${billing_cycle}${DB} BillingCHK Report" hank@fitel.net.tw < $BILLING/CHK/BillingCHK$chktime.txt
mailx -s "${billing_period}bc${billing_cycle}${DB} BillingCHK Report" eric1002@fitel.net.tw < $BILLING/CHK/BillingCHK$chktime.txt
mailx -s "${billing_period}bc${billing_cycle}${DB} BillingCHK Report" grape_c@hotmail.com
< $BILLING/CHK/BillingCHK$chktime.txt