余额存取

来源:互联网 发布:pdu协议数据单元 编辑:程序博客网 时间:2024/04/27 14:43
余额存取select * from bill.MON_HIGH_BILL where month>201701select * from cust.serv_history@to_ncash where acc_nbr='18907365258' and state='00A';select * from cust.serv_history where acc_nbr='1890736525';select * from cust.serv_historyselect * from bill.MON_HIGH_BILL where acc_nbr='18907365258';select * from bill.MON_HIGH_BILL@to_nbill where acc_nbr='18907478730'insert into  bill.MON_HIGH_BILL(HIGH_BILL_ID, MONTH, BILLING_CYCLE_ID,ACC_NBR,ACCT_ID,AMOUNT,AMOUNT_3AVG,LATN_ID,DIAGNOSIS_TYPE,HIGH_SBDS_MESSAGE,WORK_ORDER_ID)select HIGH_BILL_ID, MONTH, BILLING_CYCLE_ID,ACC_NBR,ACCT_ID,AMOUNT,AMOUNT_3AVG,LATN_ID,DIAGNOSIS_TYPE,HIGH_SBDS_MESSAGE,WORK_ORDER_ID from bill.MON_HIGH_BILL@to_nbill where acc_nbr='18907365258';delete bill.MON_HIGH_BILL where  select b.* from bill.MON_HIGH_BILL@to_nbill b where  b.acc_nbr='18907478730'-- 13348610819 颖佳 acct.CO_BUSI_OPP_LIB_log 搜索 查找 代码-------------------------------------------------------------------------------------------- --第一步 用户资料select FROM cust.SERV_HISTORY where acc_nbr = '13348614487'  and product_family_id = 10 and state = '00A'  and serv_state <> '2HB' order by serv_id,serv_seq desc  --第二步帐务定制关系 cust.SERV_ACCTSELECT acct_id, bill_require_id  FROM cust.SERV_ACCT where serv_id = 600003687477 AND acct_item_group_id = -1 AND state = '00A' --第三步 销售品包含对象实例 PRODUCT_OFFER_OBJECT_INSTANCE 80A 产品实例OCS_USER_FLAG OCS相关用户标志 OFFER_OBJECT_INSTANCE_ID,包含对象实例标识, PRODUCT_OFFER_INSTANCE_ID,商品实例标识, 80C 销售品实例80H 事件80I 客户80J 帐户80P 指定类别的产品实例总集80R 指定类别的帐户总集80U 定价包含的对象SELECT     OFFER_OBJECT_INSTANCE_ID, PRODUCT_OFFER_INSTANCE_ID, OFFER_OBJECT_ID,    OBJECT_TYPE,    OBJECT_ID,    SEQ_NBR,    STATE,    EFF_DATE,    EXP_DATE,    LATN_ID,    OCS_USER_FLAG,    AGREEMENT_ID    FROM     cust.PRODUCT_OFFER_OBJECT_INSTANCE WHERE OBJECT_TYPE = '80A' AND OBJECT_ID = 600003687477 AND EFF_DATE <= to_date('2017-03-31 23:59:59','yyyy-mm-dd hh24:mi:ss') AND EXP_DATE >= to_date('2017-03-01 00:00:00','yyyy-mm-dd hh24:mi:ss');select a.*,a.rowid from PRODUCT_OFFER_OBJECT_INSTANCE a; --第四步select * from RATABLE_RESOURCE_ACCUMULATOR 无 计费累积值 SELECT RATABLE_RESOURCE_ACCUM_ID,OWNER_TYPE,OWNER_ID,RATABLE_RESOURCE_ID,round(BALANCE*10000,0),round(INIT_VALUE*10000,0),BALANCE,INIT_VALUE,CREATED_DATE,STATE,STATE_DATE,EFF_DATE,EXP_DATE,PRODUCT_OFFER_INSTANCE_ID, REPOSITORY_ID, round(PEAK_VALUE *10000,0), CARRYOVER_AMOUNT, round(CARRYOVER_AMOUNT*10000,0), CUST_CATEGORY,GENERATE_SOURCE,BILLING_CYCLE_ID,PEAK_VALUE,LATN_ID,IF_SUITE_RESOURCE,RESOURCE_TYPE,RESOURCE_FLAG,RATABLE_CYCLE_ID,round(CARRYOVER_AMOUNT *10000,0)  FROM RATABLE_RESOURCE_ACCUMULATOR WHERE PRODUCT_OFFER_INSTANCE_ID IN ( '604842743618','604842739383','604842743615','600003687753','600003687476','604907734652','603096027010','604842743610','604907710215','600425757564','604907689799','603096026914') AND EFF_DATE<=to_date('2017-03-31 23:59:59','yyyy-mm-dd hh24:mi:ss') AND EXP_DATE>=to_date('2017-03-01 00:00:00','yyyy-mm-dd hh24:mi:ss')   AND ratable_resource_id not in ( '50007','50180','50360','50000','50001','5001' ) ;  --第五步 销售品实例 select PRIORITY_VALUE FROM cust.PRODUCT_OFFER_INSTANCE优先级 SELECT PRODUCT_OFFER_INSTANCE_ID,CUST_AGREEMENT_ID ,CUST_ID,PRODUCT_OFFER_ID,PRIORITY_VALUE,EFF_DATE,EXP_DATE,STATE,STATE_DATE, CREATE_DATE, LATN_ID    FROM cust.PRODUCT_OFFER_INSTANCE  WHERE PRODUCT_OFFER_INSTANCE_ID IN('604842743618','604842739383','604842743615','600003687753','600003687476','604907734652','603096027010','604842743610','604907710215','600425757564','604907689799','603096026914')--第六步 表名: PRODUCT_OFFER 解释: 销售品 SELECT offer_id,pricing_plan_id,offer_name,offer_comments,can_be_buy_alone,offer_code,state,eff_date,exp_date, PRIORITY_VALUE,nvl(BRAND_ID,-1),OFFER_TYPE,PACKET_TYPE,if_bill_disp, bill_disp_prior,credit_ctrl_setting_rule,nvl(BILL_DISP_NAME,offer_name),nvl(INVOICE_DISP_NAME,offer_name),BILL_ITEM_CLASS,display_name FROM prod.PRODUCT_OFFER where offer_id IN (  60000045  ,  1126203  ,  1135190  ,  1165025  ,  1167411  ,  1176314  ,  1170438  ,  1173854  ,  1176667  ,  1164196  ,  1164194  ,  1164197  ) select* from prod.PRODUCT_OFFER;--第七步FROM  prod.PRODUCT_OFFER_RESOURCE 表名:PRODUCT_OFFER_RESOURCE 解释:销售品累积量SELECT POR.OFFER_RESOURCE_ID,POR.OFFER_ID,POR.OBJ_INFO_ID,POR.RATABLE_RESOURCE_ID,POR.QUERY_NAME,POR.QUERY_TARIFF_UNIT_ID,POR.CONVERSE_RATE * 10000,POR.OFFER_RESOURCE_DESC,POR.CHANNEL_DISPLAY,POR.STATE,POR.STATE_DATE,POR.DISCT_TYPE_ID,POR.RESOURCE_TYPE,POR.DISCT_NAME_ID FROM  prod.PRODUCT_OFFER_RESOURCE POR  WHERE POR.OFFER_ID = 1176314 AND POR.RATABLE_RESOURCE_ID = 743533 AND POR.STATE = '00A'--无 SELECT     MAP_ID,    OFFER_ID_SRC,    OFFER_ID_STD,    SOURCE,    EFF_DATE,    EXP_DATE,    SEQ_NBR,    STATE,    STATE_DATE,    CREATE_DATE    FROM     cust.MAP_OFFER_INSTANCE    WHERE OFFER_ID_SRC = 604842739383 AND STATE = '00A' AND SOURCE = 'LTE' ORDER BY STATE_DATE DESC --无 SELECT product_offer_id,  src_ratable_resource_id, dest_ratable_resource_id,  state, state_date FROM prod.PRODUCT_OFFER_RESOURCE_CFG WHERE product_offer_id=1176314 AND src_ratable_resource_id=743533 AND state='00A'--第八步调用接口INSERT INTO    acct.HN_INTERFACE_LOG(log_id,interface_name,server_info,client_info,requ_xml,resp_xml,requ_date,resp_date,execute_time,content,state_date)     VALUES (102511752825,'WebServicesCenter:UserResourceQuerySRV-UserResourceQuery_ABM','134.176.12.141:8101','134.176.3.38','<?xml version='1.0' encoding='UTF-8'?>              <SAMRequest><Service-Information><Bill-Information>                        <Acc-Nbr>13348614487</Acc-Nbr>                        <Destination-Attr>2</Destination-Attr>                        <Billing-Cycle>201703</Billing-Cycle>                        <Product-Offer-Id></Product-Offer-Id>                        </Bill-Information>"                        </Service-Information></SAMRequest>','<?xml version="1.0" encoding="UTF-8"?><SAMResponse><Service-Information><Service-Result-Code>0</Service-Result-Code><Product_OFF_info><Product-Offer-Id>740000000001176314</Product-Offer-Id><ProdOfferInstanceId>740000604842739383</ProdOfferInstanceId><Product_OFF_Name>201605-乐享家201605 399元套餐</Product_OFF_Name><Offer-Type>19</Offer-Type><Respond-Ratable-Query><Owner-Type>80C</Owner-Type><Owner-ID>740000604842739383</Owner-ID><Ratable-Resource-ID>331100</Ratable-Resource-ID><Ratable-Resource-name>国内上网流量</Ratable-Resource-name><BeginTime>20170301000000</BeginTime><EndTime>20170331235959</EndTime><Ratable_Amount>9437184</Ratable_Amount><Balance-Amount>9429868</Balance-Amount><Usage-Amount>7316</Usage-Amount><UnitType_Id>3</UnitType_Id></Respond-Ratable-Query></Product_OFF_info></Service-Information></SAMResponse>',to_date('2017-03-14 17:05:05','yyyy-mm-dd hh24:mi:ss'),to_date('2017-03-14 17:05:05','yyyy-mm-dd hh24:mi:ss'),694,'0000',to_date('2017-03-14 17:05:05','yyyy-mm-dd hh24:mi:ss'))----余额存取--------------------------------------------------------------------------------------------------第一步:开始输入号码 18973246642select a.*,a.rowid from  acct.CO_BUSI_OPP_LIB_log a  where acc_nbr='18973246642'--大表--select * from acct.CO_BUSI_OPP_LIB_log a where acc_nbr='18973246642'SELECT/* log_id ,lan_id,sale_opp_id,serv_id,acc_nbr,busi_name,prop_words,if_willing_handle,party_role_id,create_date */ SELECT * FROM acct.CO_BUSI_OPP_LIB_logWHERE acc_nbr = '18973246642'; select acc_nbr FROM acct.CO_BUSI_OPP_LIB--第二步:073192332094 select a.*,a.rowid from acct.CO_BUSI_OPP_LIB a where acc_nbr='073192332094'--主要行busI_name,prop_words,busi_type_name select rownum,  LAN_ID , SALE_OPP_ID , SERV_ID,  BUSI_NAME,  PROP_WORDS,  PROCESS_DATE,  GROUP_ID,  BUSI_TYPE_CODE,  CONTROL_TYPE,  TIME_PERIOD,  MAX_TRIGGER_TIMES,  TRIGGER_PRIORITY,  ACC_NBR, BUSI_TYPE_NAME,  BUSI_ID  from ( select   a.LAN_ID , a.SALE_OPP_ID , a.SERV_ID,  a.BUSI_NAME,  a.PROP_WORDS,  a.PROCESS_DATE,  a.GROUP_ID,  a.BUSI_TYPE_CODE,  a.CONTROL_TYPE,  a.TIME_PERIOD,  a.MAX_TRIGGER_TIMES,  a.TRIGGER_PRIORITY,  a.ACC_NBR, a.BUSI_TYPE_NAME,  a.BUSI_ID      FROMacct.CO_BUSI_OPP_LIB a  where a.group_id = (select b.group_id from acct.CO_BUSI_OPP_LIB  b where b.acc_nbr='13348610819'  and rownum=1)  order by a.trigger_priority,  a.BUSI_TYPE_CODE asc) where rownum<=5 --第三步:(不显示如此)导入推荐表界面展示INSERT INTO  acct.CO_BUSI_OPP_LIB_log ( log_id ,lan_id,sale_opp_id, serv_id , acc_nbr,busi_name,prop_words,if_willing_handle,party_role_id,create_date ,GROUP_ID,BUSI_TYPE_CODE,CONTROL_TYPE,MAX_TRIGGER_TIMES,TRIGGER_PRIORITY,TIME_PERIOD,BUSI_TYPE_NAME,BUSI_ID  )  VALUES  (100009800702,732,476793686,601224522387,'18973246642','4G手机3G卡场景','【推荐换4G卡,开通4G功能】中国电信网络升级,老卡将会被淘汰,为避免老卡失效影响您的正常使用,请您尽快更换为新的4G手机卡;您已是4G手机,配上4G卡后信号更强、更稳定!现在换卡不仅免费,还送2G流量;若升级到4G套餐,更可送40G流量。',0,830065573,sysdate,'601224522387','1','周期内次数',1,'1',1,'移动主卡','10') --第四步: select * FROM cust.SERV_HISTORY where acc_nbr='073192332094'SELECT SERV_ID, SERV_SEQ, ACCT_ID, CUST_ID, AREA_CODE, ACC_NBR, REGION_ID, SERV_STATE, OWE_BUSINESS_TYPE_ID, STATE, AREA_ID, LATN_ID FROM cust.SERV_HISTORY@to_ncash WHERE 1 = 1 AND (STATE = '00A' or OWE_BUSINESS_TYPE_ID = 1)AND (ACC_NBR = '0731-13348610819' OR (ACC_NBR = '13348610819'  )) select * from cust.KEY_ACCT@to_ncash  where acct_id='50000187500'SELECT SERV_ID, SERV_SEQ, ACCT_ID, CUST_ID, AREA_CODE, ACC_NBR, REGION_ID, SERV_STATE, OWE_BUSINESS_TYPE_ID, STATE, AREA_ID, LATN_ID FROM cust.SERV_HISTORY WHERE 1 = 1 AND (STATE = '00A' or OWE_BUSINESS_TYPE_ID = 1)AND (ACC_NBR = '0731-18973246642' OR (ACC_NBR = '18973246642'  )) SELECT SYSDATE FROM DUAL --抽取系统时间--表名:SERV_ATTR 解释:产品实例附加属性 select attr_val FROM  cust.SERV_ATTR   where serv_id='600643681456'SELECT serv_id,agreement_id,seq_nbr,attr_id,product_id,attr_val,eff_date,exp_date FROM  cust.SERV_ATTR WHERE serv_id = 605030440178 AND attr_id = 666 AND exp_date > SYSDATE--cust.KEY_ACCT关键账户SELECT acct_id,acct_code,serv_count,state,create_date,enable_query FROM  cust.KEY_ACCT WHERE acct_id = '50052530678' AND state = '00A'--shouzhirizhi SELECT  ITEM,  HOST_ID,  MODIFY_COUNT FROM  cust.STATIC_DATA_NOTIFY WHERE ITEM <> 'TRIGGER' ORDER BY ITEM, MODIFY_COUNT DESC--PRODUCT_OFFER_INSTANCE销售品实例SELECT serv_id,agreement_id,seq_nbr,attr_id,product_id,attr_val,eff_date,exp_date FROM  cust.SERV_ATTR WHERE serv_id = 605030440178 AND attr_id = 667 AND exp_date > SYSDATESELECT SYSDATE FROM DUAL --找到user_name用户名字 FROM cust.SERV_HISTORYSELECT SERV_ID, SERV_SEQ, ACCT_ID, CUST_ID, PRODUCT_FAMILY_ID, PRODUCT_ID, AGREEMENT_ID, AREA_CODE,ACC_NBR, REGION_ID, BILLING_CYCLE_TYPE_ID, BILLING_MODE_ID,COMPLETED_DATE, BILL_DATE, CREATED_DATE, EFF_DATE, EXP_DATE,SERV_STATE, STATE_DATE, OWE_BUSINESS_TYPE_ID, STATE, EXCHANGE_ID,ADDRESS_ID, USER_NAME, CUST_CATEGORY, ACTION, BONUS_PLAN_ID, CREDIT_LIMIT_PLAN_ID, AREA_ID, BILLING_FLAG_ID, COUNTY_TYPE, RESID_FLAG,REMOVE_DATE, PHYSICAL_NBR, SERV_GRADE, UPDATE_FLAG,HOT_BILLING_FLAG,SITE_ID, LATN_ID, CHANNEL_ID, PROVINCE_CODE, ACTIVATE_DEADLINE, OCS_USER_FLAG,payment_flag,segment_id FROM cust.SERV_HISTORY WHERE SERV_ID IN (605030440178) ORDER BY SERV_ID, SERV_SEQ -- select * FROM  cust.SERV_PRODUCT 找bill_dateSELECT serv_product_id,serv_pro_seq_nbr,agreement_id,product_id,serv_id,completed_date,bill_date,created_date,removed_date,state,product_qunantity,state_date,eff_date,exp_date  FROM  cust.SERV_PRODUCT WHERE serv_id = 605030440178 AND product_id = 80076909 AND eff_date < SYSDATE AND exp_date > SYSDATE AND state =  '00A'--标示图 select * from ACCT_BALANCE_QUOTASELECT acct_balance_id,billing_cycle_id,cycle_upper * 10000 as cycle_upper,cycle_lower * 10000 as cycle_lower,state,state_date FROM ACCT_BALANCE_QUOTA WHERE STATE = '00A'  AND ACCT_BALANCE_ID  = 733589975252 /*SELECT BATCH_ID,TOTAL_AMOUNT,TOTAL_COUNT,BEGIN_OPER_ID,END_OPER_ID,CREATE_DATE,FILE_NAME FROM  cust.SP_BATCH_CASH*/--收支日志FROM acct.BILLING_CYCLE T_BCY, acct.BILLING_CYCLE_TYPE T_BCTSELECT T_BCY.billing_cycle_id,T_BCY.billing_cycle_type_id,T_BCY.last_billing_cycle_id,T_BCY.cycle_begin_date,T_BCY.cycle_end_date,T_BCY.due_date,T_BCY.block_date,T_BCY.state,T_BCY.state_date,T_BCY.cycle_cc_flag,T_BCT.region_id, T_BCT.billing_cycle_type_name  FROM acct.BILLING_CYCLE T_BCY, acct.BILLING_CYCLE_TYPE T_BCT WHERE T_BCY.billing_cycle_type_id = T_BCT.billing_cycle_type_id  order by T_BCY.cycle_begin_date --收支日志select * from prod.SHMCONFIG_CHANGE_NOTIFYINSERT INTO prod.SHMCONFIG_CHANGE_NOTIFY(change_id, table_name, state, create_date, state_date,reason, party_role_id, change_source) VALUES(102170759, 'Product_Offer', 0, to_date('2017-03-13 16:41:45','yyyy-mm-dd hh24:mi:ss'), to_date('2017-03-13 16:41:45','yyyy-mm-dd hh24:mi:ss'), '', -1, 'PAY')--select * from acct.STATIC_DATA_LOAD_LOG/*INSERT INTO acct.STATIC_DATA_LOAD_LOG(LOG_ID,CREATE_DATE,LOAD_ITEMS,OPER_STAFF_ID)*/INSERT INTO acct.STATIC_DATA_LOAD_LOG(LOG_ID,CREATE_DATE,LOAD_ITEMS,OPER_STAFF_ID) VALUES(102084710,to_date('2017-03-13 16:42:17','yyyy-mm-dd hh24:mi:ss'),'ProductOffer',1000110) --表名:ACCT_PAYOUT_LOG 解释:帐户零头支出日志表select * FROM  acct.ACCT_PAYOUT_LOG@to_ncash where party_id=110000015021 and state_date>=to_date('2017-03-19 00:00:00','yyyy-mm-dd hh24:mi:ss') 操作员 得到acct_idselect TAPL.log_id,  TAPL.oper_amount * 10000 as oper_amount,TAPL.oper_balance * 10000 as oper_balance,  TAPL.staff_id,TAPL.oper_date,TAPL.STATE,TAPL.operated_log_id,TAPL.payment_id FROM  acct.ACCT_PAYOUT_LOG TAPL  WHERE 1=1  AND OPER_TYPE = '3'  AND  TAPL.acct_id =50076863559 AND TAPL.oper_date >= to_date('2017-03-10 00:00:00','yyyy-mm-dd hh24:mi:ss')  AND TAPL.oper_date <= to_date('2017-03-10 23:59:59','yyyy-mm-dd hh24:mi:ss')  ORDER BY TAPL.oper_date--select *  FROM  prod.PRODUCT_OFFER SELECT offer_id,pricing_plan_id,offer_name,offer_comments,can_be_buy_alone,offer_code,state,eff_date,exp_date, PRIORITY_VALUE,BRAND_ID,OFFER_TYPE,PACKET_TYPE,if_bill_disp, bill_disp_prior,brand_id,bill_item_class,nvl(BILL_DISP_NAME,offer_name),nvl(offer_disp_class,-1),MAIN_FLAG FROM  prod.PRODUCT_OFFER WHERE STATE = '00A' order by offer_name;--销售品 prod.PRODUCT_OFFER_RESOURCE_CFSELECT product_offer_id,  src_ratable_resource_id, dest_ratable_resource_id,  state, state_date FROM prod.PRODUCT_OFFER_RESOURCE_CFGSELECT     MAP_ID,    OFFER_ID_SRC,    OFFER_ID_STD,    SOURCE,    EFF_DATE,    EXP_DATE,    SEQ_NBR,    STATE,    STATE_DATE,    CREATE_DATE    FROM     cust.MAP_OFFER_INSTANCE--select *   FROM cust.SERV_PRODUCT_ATTRSELECT serv_product_id,seq_nbr,attr_id,product_id,attr_val,eff_date,exp_date FROM cust.SERV_PRODUCT_ATTR WHERE serv_product_id = 600494434772 AND attr_id = 800125483 AND eff_date < SYSDATE AND exp_date > SYSDATE--select * from  acct.BALANCE_PAYOUT_736@to_ncash where aCCT_BALANCE_ID  = 733589975252 --INSERT INTO    acct.HN_INTERFACE_LOG(log_id,interface_name,server_info,client_info,requ_xml,resp_xml,requ_date,resp_date,execute_time,content,state_date)     VALUES (102511752405,'WebServicesCenter:QueryBalanceSRV-QueryBalance_balance','134.176.12.141:8101','134.176.3.36','<?xml version='1.0' encoding='UTF-8'?>               <SAMRequest><Service-Information><Balance-Information>         <Destination-Id>18075130412</Destination-Id>           <Destination-Id-Type>2</Destination-Id-Type>               <Destination-Attr>2</Destination-Attr>           <AreaCode>0731</AreaCode>             <Query-Flag>1</Query-Flag>            <Query-Type>1</Query-Type>                </Balance-Information>           </Service-Information></SAMRequest>','<?xml version="1.0" encoding="UTF-8"?><SAMResponse><Service-Information><Service-Result-Code>0</Service-Result-Code><PaymentFlag>0</PaymentFlag><Total-Balance-Available>38000</Total-Balance-Available><Balance-Information><Balance-Available>39900</Balance-Available><BalanceTypeFlag>1</BalanceTypeFlag></Balance-Information><Balance-Information><Balance-Available>0</Balance-Available><BalanceTypeFlag>0</BalanceTypeFlag></Balance-Information></Service-Information></SAMResponse>',to_date('2017-03-13 16:00:41','yyyy-mm-dd hh24:mi:ss'),to_date('2017-03-13 16:00:41','yyyy-mm-dd hh24:mi:ss'),400,'0000',to_date('2017-03-13 16:00:41','yyyy-mm-dd hh24:mi:ss'))--select * FROM ACCT_BALANCE_QUOTASELECT acct_balance_id,billing_cycle_id,cycle_upper * 10000 as cycle_upper,cycle_lower * 10000 as cycle_lower,state,state_date FROM aCCT_BALANCE_QUOTA WHERE STATE = '00A'  AND ACCT_BALANCE_ID  = 733589976252 SELECT ACCT_ID,DEAL_TYPE,STATE,STATE_DATE,LATN_ID  FROM cust.ACCT_SPEC_TYPE WHERE ACCT_ID = 50066481838select * from cust.serv_history WHERE ACCT_ID = 50066481838

0 0
原创粉丝点击