SQL语句过程：根据前台输入号码和帐期察看号码家庭情况

 

        ---根据前台输入号码和帐期察看号码家庭情况
        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;