/sh/BillingCHK.sh_940315bk
Korn Shell | 747 lines | 723 code | 22 blank | 2 comment | 7 complexity | a52a68022d526bc35f7ea38222d366b1 MD5 | raw file
- #! /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