余额存取

余额存取

select * from bill.MON_HIGH_BILL where month>201701
select * 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_history
select * 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_ACCT
SELECT 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'))
----余额存取------------------------------------------------------------------------------------------------
--第一步：开始输入号码 18973246642
select 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_log
WHERE 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   
   FROM
acct.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 > SYSDATE

SELECT SYSDATE FROM DUAL 

--找到user_name用户名字 FROM cust.SERV_HISTORY
SELECT 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_date
SELECT 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_QUOTA
SELECT 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_BCT
SELECT 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_NOTIFY
INSERT 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_id
select 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_CF
SELECT product_offer_id,  src_ratable_resource_id, dest_ratable_resource_id,  state, state_date FROM prod.PRODUCT_OFFER_RESOURCE_CFG
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

--select *   FROM cust.SERV_PRODUCT_ATTR
SELECT 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_QUOTA
SELECT 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 = 50066481838
select * from cust.serv_history WHERE ACCT_ID = 50066481838