余额存取

xiaoxiao2021-03-25 97

余额存取 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

转载请注明原文地址: https://ju.6miu.com/read-149657.html

技术

最新回复(0)