---根据前台输入号码和帐期察看号码家庭情况 fan_drop_retable(upper('jtxy_qthm_srcx_a'),'SHZC'); SQL_STRING:='create table SHZC.jtxy_qthm_srcx_a as select * from tbcs.GROUP_SUBS_MEMBER@bcv where MEMSERVNUMBER='''||v_sjhm||''' and prodid in (select prodid from tbcs.product@bcv pp where prodname like ''家庭有线宽带%人版产品包'' ) and STARTDATE<add_months(to_date('''||v_cxzq||''',''yyyymm''),1) and nvl(ENDDATE,sysdate+9999)>=to_date('''||v_cxzq||''',''yyyymm'') ' ; execute immediate (SQL_STRING); ---根据家庭编码查询全部成员 fan_drop_retable(upper('jtxy_qthm_srcx_b'),'SHZC'); SQL_STRING:='create table SHZC.jtxy_qthm_srcx_b as select * from tbcs.GROUP_SUBS_MEMBER@bcv where GROUPOID in (select b.groupoid from SHZC.jtxy_qthm_srcx_a b ) and prodid in (select prodid from tbcs.product@bcv pp where prodname like ''家庭有线宽带%人版产品包'' ) and STARTDATE<add_months(to_date('''||v_cxzq||''',''yyyymm''),1) and nvl(ENDDATE,sysdate+9999)>=to_date('''||v_cxzq||''',''yyyymm'')' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jtxy_qthm_srcx_c'),'SHZC'); SQL_STRING:='create table SHZC.jtxy_qthm_srcx_c as select distinct a.grpsubsid,a.prodid,a.privid,a.startdate,a.enddate from zhyw.shc_subs_priv a, SHZC.jtxy_qthm_srcx_a b where a.grpsubsid = b.groupoid' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jtxy_qthm_srcx_d'),'SHZC'); SQL_STRING:='create table SHZC.jtxy_qthm_srcx_d as select a.*,b.RATEPLAN from SHZC.jtxy_qthm_srcx_c a, (select * from tbcs.privilege_rateplan@bcv b where status =1 ) b where a.privid=b.privid ' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jtxy_qthm_srcx_e'),'SHZC'); SQL_STRING:='create table SHZC.jtxy_qthm_srcx_e as select a.*,b.ITEM_CODE from SHZC.jtxy_qthm_srcx_d a, acct.DiscRefItem_def@bcv b where a.rateplan=b.TARIFFPLAN_ID(+) ' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jtxy_qthm_srcx_f'),'SHZC'); SQL_STRING:='create table SHZC.jtxy_qthm_srcx_f as select distinct b.itemcode,b.itemname,a.INCLUDEFLAG --0不算1算 from SHZC.jtxy_qthm_srcx_e e , acct.feegroup_accitem_def@bcv a, account.acctitem_def@zwbcv b where a.itemcode=b.itemcode(+) and e.item_code=a.feegroupid(+) ' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jtxy_qthm_srcx_g'),'SHZC'); SQL_STRING:='create table SHZC.jtxy_qthm_srcx_g as select a.acctid,h.memservnumber,a.subsid,a.billcycle,a.itemcode,b.itemname,sum(nvl(a.fee/100,0)) fee, sum(nvl(case when nvl(ITEMTYPE,''4'')<''4'' then a.fee end,0))/100 需交fee, sum(nvl(case when nvl(ITEMTYPE,''4'')=''6'' then a.fee end,0))/100 滞纳金 from account.billitem533@zwbcv a,account.acctitem_def@zwbcv b, SHZC.jtxy_qthm_srcx_b h where substr(a.billcycle,1,6)='''||v_cxzq||''' ---标示:账期 and h.subsid=a.subsid(+) and a.itemcode=b.itemcode(+) and a.itemcode not in (''6001'',''500001'') group by a.acctid, h.memservnumber,a.subsid,a.billcycle,a.itemcode,b.itemname ' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jtxy_qthm_srcx_h'),'SHZC'); SQL_STRING:='create table SHZC.jtxy_qthm_srcx_h as select distinct a.*,b.includeflag includeflag1,d.includeflag includeflag2,d1.includeflag includeflag3 from SHZC.jtxy_qthm_srcx_g a, SHZC.jtxy_qthm_srcx_f b, (select distinct substr(itemcode,1,4) itemcode,d.includeflag from SHZC.jtxy_qthm_srcx_f d ) d, (select distinct substr(itemcode,1,2) itemcode,d.includeflag from SHZC.jtxy_qthm_srcx_f d ) d1 where a.itemcode=b.itemcode(+) and substr(a.itemcode,1,4)=substr(d.itemcode(+),1,4) and substr(a.itemcode,1,2)=substr(d1.itemcode(+),1,2) ' ; execute immediate (SQL_STRING); fan_drop_retable(upper('jtxy_qthm_srcx_i'),'SHZC'); SQL_STRING:='create table SHZC.jtxy_qthm_srcx_i as select distinct a.acctid,a.memservnumber,a.subsid,a.billcycle,a.itemcode,a.itemname,a.fee,a.需交fee, nvl(a.includeflag1,nvl(a.includeflag2,a.includeflag3)) includeflag1 from SHZC.jtxy_qthm_srcx_h a ' ; execute immediate (SQL_STRING); SQL_STRING:='insert into shzc.jtxy_qthm_srcx_bd select '''||p_userid||''' p_userid,sysdate in_time, a.acctid,a.memservnumber,a.subsid,a.billcycle,a.itemcode,a.itemname, a.fee 优惠前账单,a.需交fee 优惠后账单,nvl(case when a.includeflag1=''0'' then ''否'' end,''是'') 是否计算协议消费 from shzc.jtxy_qthm_srcx_i a order by a.includeflag1 ' ; execute immediate (SQL_STRING); commit;