--综合查询总表：cust.ACCT cust.SERV_HISTORY cust.SERV_ACCT
--FROM cust.ACCT获取 CUST_ID =804128701606;
--from cust.SERV_HISTORY A获取一堆 ,cust.SERV_ACCT B ,ocs标志,SERV_ID
-------------------------------------------------------------------------------------------------
SELECT A.SERV_ID, A.SERV_SEQ, B.ACCT_ID, A.CUST_ID, A.PRODUCT_FAMILY_ID, A.PRODUCT_ID, A.AGREEMENT_ID,A.AREA_CODE, A.ACC_NBR, A.REGION_ID, A.BILLING_CYCLE_TYPE_ID, A.BILLING_MODE_ID,A.COMPLETED_DATE, A.BILL_DATE, A.CREATED_DATE, B.EFF_DATE, A.EXP_DATE,A.SERV_STATE, B.STATE,A.STATE_DATE, A.OWE_BUSINESS_TYPE_ID, A.STATE, A.EXCHANGE_ID,A.ADDRESS_ID, A.USER_NAME, A.CUST_CATEGORY, A.ACTION, A.BONUS_PLAN_ID, A.CREDIT_LIMIT_PLAN_ID, A.AREA_ID, A.BILLING_FLAG_ID, A.COUNTY_TYPE, A.RESID_FLAG,A.REMOVE_DATE, A.PHYSICAL_NBR,A.SERV_GRADE, A.UPDATE_FLAG,A.HOT_BILLING_FLAG,A.area_code,B.EXP_DATE
FROM cust.SERV_HISTORY A ,cust.SERV_ACCT B WHERE A.SERV_ID = B.SERV_ID
AND B.ACCT_ID
IN (
SELECT ACCT_ID FROM cust.ACCT
WHERE CUST_ID = 804128701606) ;
select* from cust.SERV_HISTORY@to_ncash where acc_nbr='18188923291';
-- from cust.ACCT 获取ACCT_ID,CUST_ID,SERV_ID----------
SELECT ACCT_ID,CUST_ID,SERV_ID,ACCT_NAME,ACCT_CODE,ADDRESS_ID,STATE,STATE_DATE ,CREDIT_LIMIT_PLAN_ID , BILLING_FLAG_ID , REGION_ID, latn_id, party_id_locked, ACCT_TYPE FROM cust.ACCT@to_ncash WHERE serv_ID =603057842857
--from cust.SERV_ACCT ACCT_ID 获取SERV_ID 表名:SERV_ACCT 解释:帐务定制关系
SELECT DISTINCT ACCT_ID FROM cust.SERV_ACCT WHERE SERV_ID IN (605270331539) AND STATE in ('00A');
SELECT SERV_ID,STANDARD_CODE,OWE_BUSINESS_TYPE_ID,STATE,EFF_DATE,EXP_DATE,SERIAL_NUMBER,SERV_STATE_ATTR_ID,RECOVER_SERIAL_NUMBER,RECOVER_REASON,RECOVER_PARTY_ROLE_ID, REASON,PARTY_ROLE_ID FROM cust.SERV_STATE_ATTR WHERE SERV_ID = 605270331539 AND STATE = '00A' AND Standard_Code IN ('A04', 'A05') and exp_date>sysdate ；
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 = 605270331539 AND STATE = '00A' ORDER BY SERV_SEQ DESC；
;
---------------------------------------------------------
--用户资料查询 prod.STAFF_PRIVILEGE
--prod.STAFF_PRIVILEGE
SELECT
STAFF_PRIVILEGE_ID,
EFF_DATE,
EXP_DATE,
PARTY_ROLE_ID,
PRIVILEGE_ID,
REGION_ID,
STATE
FROM prod.STAFF_PRIVILEGE WHERE PRIVILEGE_ID = 400040734 AND PARTY_ROLE_ID = 1000102
AND state = '00A'
AND EXP_DATE > SYSDATE;
-----prod.STAFF_ROLE T_STR, prod.ROLE_PRIVILEGE T_ROL
SELECT T_STR.EFF_DATE, T_STR.EXP_DATE, T_STR.PARTY_ROLE_ID, T_STR.REGION_ID, T_STR.ROLE_ID, T_STR.STATE FROM prod.STAFF_ROLE T_STR, prod.ROLE_PRIVILEGE T_ROL
WHERE T_STR.ROLE_ID = T_ROL.ROLE_ID
AND T_ROL.PRIVILEGE_ID = 400040734
AND T_STR.PARTY_ROLE_ID = 1000102
AND T_STR.EXP_DATE > SYSDATE
AND T_STR.STATE = '00A'
AND T_ROL.STATE = '00A';
---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 = 605270331539 AND SERV_SEQ = 1;
--cust.SERV_CREDIT_INFO信用
SELECT SERV_ID, SEQ_NBR, HOTBILLING_FLAG, EFF_DATE, EXP_DATE, STATE, DATA_SOURCE FROM cust.SERV_CREDIT_INFO WHERE SERV_ID = 605270331539
AND EFF_DATE <= to_date('2017-03-09 20:02:22','yyyy-mm-dd hh24:mi:ss') AND EXP_DATE >= to_date('2017-03-09 20:02:22','yyyy-mm-dd hh24:mi:ss')
ORDER BY EXP_DATE DESC;
--日志 select * from acct.QUERY_LOG where staff_code='731100220' order by query_date DESC
INSERT INTO acct.QUERY_LOG(LOG_ID,ISFORMATBILL,ISHISQUERY,ISVALUECUST,OBJECT_ID,OBJECT_TYPE,OPERATE_TYPE_ID,QUERY_CYCLE,QUERY_DATE,REASON,RESULT,RES_DURATION,STAFF_CODE,LIST_TYPE_ID,CERTIFICATE_TYPE,CERTIFICATE_NO,ISPRINT,ip_addr,file_id,file_type,ir_date) VALUES (100363178706,'','2','0',605270331539,'80A',16,0,to_date('2017-03-09 20:02:22','yyyy-mm-dd hh24:mi:ss'),'','1',9,'ztezm',0,'','','F','134.160.170.53','','','')；
select * from acct.QUERY_LOG;
--
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 = 605270331539
ORDER BY SERV_SEQ desc;
--cust.AGREEMENT
SELECT agreement_id,cust_id,parent_agreement_id,agreement_type,sign_date,completed_date,accept_staff_id,source,state,comments
FROM cust.AGREEMENT WHERE agreement_id = 803715398702
select* from cust.AGREEMENT;
--参与人角色select party_role_id FROM cust.EXEMPT_INSTANCE
SELECT EXEMPT_INSTANCE_ID,OBJECT_ID,OBJECT_TYPE,LATN_ID,EXEMPT_TYPE_ID,EFF_DATE,EXP_DATE,CREATE_DATE,PARTY_ROLE_ID,OCS_USER_FLAG FROM cust.EXEMPT_INSTANCE WHERE OBJECT_TYPE = '5BC' AND OBJECT_ID = 605270331539 AND EFF_DATE <= SYSDATE AND EXP_DATE >= SYSDATE
select* FROM cust.EXEMPT_INSTANCE;
--prod.ATTRIBUTE_VALUE
SELECT serv_id,owe_business_type_id,state,eff_date,exp_date FROM cust.SERV_STATE_ATTR WHERE SERV_ID = 605270331539 AND EXP_DATE >= to_date('2030-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') ORDER BY EXP_DATE ;
SELECT serv_id,attr_id,max(seq_nbr) FROM cust.SERV_ATTR WHERE serv_id = 605270331539 AND eff_date < to_date('2030-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') AND exp_date > to_date('2017-02-01 20:03:14','yyyy-mm-dd hh24:mi:ss') GROUP BY serv_id,attr_id ;
SELECT ATTR_VALUE_ID,ATTR_ID,ATTR_VALUE,ATTR_VALUE_DESC FROM prod.ATTRIBUTE_VALUE WHERE ATTR_ID = 20074 AND ATTR_VALUE = '1';
SELECT ATTR_VALUE_ID,ATTR_ID,ATTR_VALUE,ATTR_VALUE_DESC FROM prod.ATTRIBUTE_VALUE WHERE ATTR_ID = 20003 AND ATTR_VALUE = '1';.....
--FROM cust.ADDRESS地址
SELECT
ADDRESS_ID,
CITY_NAME,
DETAIL,
POSTCODE,
PROVINCE_NAME,
STREET_NAME,
STREET_NBR ,
LATN_ID
FROM
cust.ADDRESS WHERE ADDRESS_ID = 803113186010;
select * from cust.ADDRESS;
--销售品实例FROM prod.PRODUCT_OFFER_PARAM
SELECT OFFER_PARAM_ID, OFFER_ID, PARAM_ATTR_ID, PARAM_NAME, DEFAULT_VALUE, PARAM_VALUE_LIST, PARAM_VALUE_MIN, PARAM_VALUE_MAX, PARAM_VALUE_KIND, DISPLAY_FLAG, PRICING_PLAN_ID FROM prod.PRODUCT_OFFER_PARAM@to_nbill WHERE OFFER_PARAM_ID =454900 ;
select * from prod.PRODUCT_OFFER_PARAM@to_nbill ;
select * FROM prod.PRODUCT_OFFER_PARAM@to_nbill where ;
--用户属性信息cust.AGREEMENT 重复
SELECT agreement_id,cust_id,parent_agreement_id,agreement_type,sign_date,completed_date,accept_staff_id,source,state,comments FROM cust.AGREEMENT WHERE agreement_id = 803715398702;
select * from cust.AGREEMENT;
--ocs日志
SELECT COUNT(*) FROM acct.CHARGE_ADJUST_FAIL_LOG WHERE OPERATE_LOG_ID=100000788195
SELECT * FROM acct.CHARGE_ADJUST_FAIL_LOG WHERE OPERATE_LOG_ID=100000788195
--第一步 用户资料
select *FROM cust.SERV_HISTORY@to_ncash where acc_nbr = '18188923291' 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 定价包含的对象
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 * from ratable
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 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 1 = 1 AND (ACC_NBR = '13319513888' OR (ACC_NBR = '13319513888' AND AREA_CODE is null )) AND STATE = '00A'
AND SERV_STATE != '2HB'
AND PRODUCT_FAMILY_ID = 10 AND AREA_CODE is null
******* <134.160.170.53> | <731100220> | <1489547488162> | <com.ztesoft.bsn.cashmgr.bll.CheckControlMgr> | <noCheckedStaffListByParty> | <1169 ms> *******
SELECT
DISTINCT TCLI.CREDIT_VALUE_ID, TCLI.CREDIT_VALUE_TYPE_ID,TCLI.CREDIT_VALUE * 10000,TCLI.EFF_DATE,TCLI.EXP_DATE
FROM cust.CREDIT_VALUE TCLI
WHERE TCLI.USED_OBJECT_ID = 33683513 AND TCLI.USED_OBJECT_TYPE = '5BC' AND TCLI.CREDIT_VALUE_TYPE_ID <> 0 AND TCLI.STATE = '00A' AND TCLI.EFF_DATE < SYSDATE AND TCLI.EXP_DATE > SYSDATE
*******表名:CREDIT_VALUE 解释:信用额度
备注: 信用额度是用来描述客户、帐户或者用户等实体的费用透支额度信息。信用额度分为初始信用额度、临时信用额度、调整信用额度等
SELECT
DISTINCT TCLI.CREDIT_VALUE_ID,TCLI.CREDIT_VALUE_TYPE_ID,TCLI.CREDIT_VALUE*10000,TCLI.EFF_DATE,TCLI.EXP_DATE
FROM cust.CREDIT_VALUE TCLI
WHERE TCLI.USED_OBJECT_ID = 33683513 AND TCLI.USED_OBJECT_TYPE = 'S' AND TCLI.CREDIT_VALUE_TYPE_ID <> 0 AND TCLI.STATE = '00A' AND TCLI.EFF_DATE <SYSDATE AND TCLI.EXP_DATE >SYSDATE
*******插入表格显示在测试平台上面 acct.HN_INTERFACE_LOG
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 (102511753132,'WebServicesCenter:QueryCreditLimit-queryCreditLimit','134.176.12.141:8101','134.176.3.45','<?xml version='1.0' encoding='UTF-8'?> <QueryCreditLimitRequ><FamilyId>10</FamilyId><AccNbr>13319513888</AccNbr></QueryCreditLimitRequ>','<?xml version="1.0" encoding="UTF-8"?><QueryCreditLimitResp><CreditLimitList><SerialNo>103624521</SerialNo><CreditTypeName>初始/普通信用度</CreditTypeName><CreditValue>30.0</CreditValue><EffDate>2009-04-01 21:48:02</EffDate><ExpDate>2030-01-01 00:00:00</ExpDate><OweBusinessTypeName>全部</OweBusinessTypeName></CreditLimitList><ResultCode>0</ResultCode><ErrorInfo/></QueryCreditLimitResp>',to_date('2017-03-15 11:11:29','yyyy-mm-dd hh24:mi:ss'),to_date('2017-03-15 11:11:29','yyyy-mm-dd hh24:mi:ss'),16,'0000',to_date('2017-03-15 11:11:29','yyyy-mm-dd hh24:mi:ss'))
select* from acct.HN_INTERFACE_LOG
------调账---------------
select * FROM acct.ADJUST_THRESHOLD_DEFINE WH
SELECT LATN_ID,THRESHOLD_TYPE,THRESHOLD_VALUE,CREATE_DATE,STATE_DATE,STATE,PARTY_ROLE_ID,ADJUST_GRADE,adjust_threshold_define_id FROM acct.ADJUST_THRESHOLD_DEFINE WHERE 1=1 AND LATN_ID=731 AND ADJUST_GRADE = 1 AND THRESHOLD_TYPE = '3' AND STATE = '00A' AND THRESHOLD_VALUE >= 3000
******* <134.160.170.73> | <ztezm> | <1489548823982> | <com.ztesoft.bsn.adjustmgr.bll.AdjustHelper> | <queryApproveThreshold> | <6 ms> *******
[TIME]:2017-03-15 11:33:43
SELECT LATN_ID,THRESHOLD_TYPE,THRESHOLD_VALUE,CREATE_DATE,STATE_DATE,STATE FROM acct.ADJUST_THRESHOLD_DEFINE WHERE LATN_ID=731
******* <134.160.170.73> | <ztezm> | <1489548824004> | <com.ztesoft.bsn.adjustmgr.bll.AdjustHelper> | <saveAdjustApply> | <14 ms> *******
[TIME]:2017-03-15 11:33:44
SELECT SERV_ID, SERV_SEQ, ACCT_ID, CUST_ID, PRODUCT_FAMILY_ID, PRODUCT_ID, AGREEMENT_ID, 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,latn_id,area_code
FROM cust.SERV_HISTORY
WHERE SERV_ID = 603443062232 AND STATE = '00A' AND SERV_STATE != '2HB'
[SQL_EXECUTE_TIME(ms)]:3
******* <134.160.170.73> | <ztezm> | <1489548824004> | <com.ztesoft.bsn.adjustmgr.bll.AdjustHelper> | <saveAdjustApply> | <20 ms> *******
[TIME]:2017-03-15 11:33:44
SELECT acct_id FROM cust.SERV_ACCT WHERE serv_id = 603443062232 AND EFF_DATE < to_date('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') AND EXP_DATE > to_date('2017-01-31 23:59:59','yyyy-mm-dd hh24:mi:ss') AND acct_item_group_id = -1
[SQL_EXECUTE_TIME(ms)]:2
******* <134.160.170.73> | <ztezm> | <1489548824004> | <com.ztesoft.bsn.adjustmgr.bll.AdjustHelper> | <saveAdjustApply> | <24 ms> *******
[TIME]:2017-03-15 11:33:44
INSERT INTO ACCT.STAFF_ADJUST_APPLY (apply_no,acc_nbr,acct_id,adjust_type,adjust_month,adjust_amount,party_role_id,audit_file_name,party_role_id_yj,party_role_id_ej,batch,create_date,state,approve_date_yj,approve_opinion_yj,approve_date_ej,approve_opinion_ej,apply_exp_date,reason,batch_file_name,latn_id,serv_id) VALUES('LSQF2017031510000143','18075130412',50066481838,1,201701,'30.0',1000102,'',1000110,'','F',to_date('2017-03-15 11:34:21','yyyy-mm-dd hh24:mi:ss'),'00A','','','','',to_date('2017-03-25 11:34:21','yyyy-mm-dd hh24:mi:ss'),'','',731,603443062232)
[SQL_EXECUTE_TIME(ms)]:3
******* <134.160.170.73> | <ztezm> | <1489548824004> | <com.ztesoft.bsn.adjustmgr.bll.AdjustHelper> | <saveAdjustApply> | <30 ms> *******
[TIME]:2017-03-15 11:33:44
SELECT * FROM prod.PARTY_ROLE WHERE PARTY_ROLE_ID = 1000110
******* <134.160.170.73> | <ztezm> | <1489548824004> | <com.ztesoft.bsn.adjustmgr.bll.AdjustHelper> | <saveAdjustApply> | <34 ms> *******
[TIME]:2017-03-15 11:33:44
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 1 = 1 AND (ACC_NBR = '18975101689' OR (ACC_NBR = '18975101689' AND AREA_CODE is null )) AND STATE = '00A'
AND SERV_STATE != '2HB'
AND PRODUCT_FAMILY_ID = 10 AND AREA_CODE is null
******* <134.160.170.73> | <ztezm> | <1489548824004> | <com.ztesoft.bsn.adjustmgr.bll.AdjustHelper> | <saveAdjustApply> | <40 ms> *******
--表名:ADVICE_INFO 解释:通知提醒信息
INSERT INTO acct.ADVICE_INFO(ADVICE_INFO_ID, ADVICE_EVENT_ID, ADVICE_MSG_ID,SMS_TYPE,PRIORITY,SERV_ID,ACC_NBR, MSG, CREATE_DATE, EFF_DATE, STATE, STATE_DATE, COMMENTS, LATN_ID, CREATE_MONTH) VALUES(101918016308, -1, -1, 'TB3', 1,600533448695,'18975101689','中兴-朱敏员工申请历史欠费调帐，申请单号：LSQF2017031510000143，申请调账金额30.00元，请一级审批人中兴-姚敬飞审批', SYSDATE, to_date('2017-03-15 11:34:21','yyyy-mm-dd hh24:mi:ss'), 'A',SYSDATE, '', 731, 201703)
select * from acct.ADVICE_INFO
[SQL_EXECUTE_TIME(ms)]:2
Commit.
---费用明细查询
select b.name,a.charge,a.* from bill.acct_item_sd_341702@to_nbill a, acct.acct_item_type@to_NCASH b
where a.acct_item_type_id=b.acct_item_type_id and a.serv_id in (
select serv_id from cust.serv_history@to_ncash where serv_id in
(select serv_id from cust.serv_acct@to_ncash where acct_id=50073739608
) and billing_mode_id=1
) and a.charge<>0 ;
select * FROM acct.ACCT_ITEM_745
---FROM acct.ACCT_ITEM_732
SELECT DISTINCT acct_id FROM cust.SERV_ACCT WHERE SERV_ID = 601224522387 AND EFF_DATE < to_date('2017-02-28 00:00:00','yyyy-mm-dd hh24:mi:ss') AND EXP_DATE > to_date('2017-02-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
SELECT 1 FROM acct.SYNC_DATE_CHANGE_732 WHERE rel_serial_id = 50052530678 and CHANGE_TYPE='1' AND STATE != '2'
SELECT ACCT_ITEM_ID,ACCT_ID,ITEM_SOURCE_ID,BILL_ID,BILLING_CYCLE_ID,ACCT_ITEM_TYPE_ID,SERV_ID,AMOUNT * 10000 as AMOUNT,OLD_AMOUNT * 10000 as OLD_AMOUNT,CREATED_DATE,FEE_CYCLE_ID,STATE,STATE_DATE, BALANCE_PAID * 10000 as BALANCE_PAID, NO_INVOICE_AMOUNT * 10000 as NO_INVOICE_AMOUNT, HAD_INVOICE_AMOUNT * 10000 as HAD_INVOICE_AMOUNT, FRG_ID,DISCT_EXPRESS_ID, PLAN_OWNER_INST_ID, PRICING_PLAN_ID, EVENT_PRICING_STRATEGY_ID, DEAL_SOURCE, BRAND_ID, PAY_CYCLE_ID, SOURCE_SERV_ID, ACC_NBR, RATE_DURATION, METER_READING, BYTE_ALL, PSEUDO_FLAG, repository_id ,partition_item_type ,partition_charge * 10000 as partition_charge FROM acct.ACCT_ITEM_732 WHERE SERV_ID IN (601224522387) AND BILLING_CYCLE_ID IN (321702) AND NOT (AMOUNT = 0 AND OLD_AMOUNT = 0 AND BALANCE_PAID = 0 AND NO_INVOICE_AMOUNT = 0) AND deal_source<>11
INSERT INTO acct.QUERY_LOG(LOG_ID,ISFORMATBILL,ISHISQUERY,ISVALUECUST,OBJECT_ID,OBJECT_TYPE,OPERATE_TYPE_ID,QUERY_CYCLE,QUERY_DATE,REASON,RESULT,RES_DURATION,STAFF_CODE,LIST_TYPE_ID,CERTIFICATE_TYPE,CERTIFICATE_NO,ISPRINT,ip_addr,file_id,file_type,ir_date) VALUES (100363214682,'','2','0',601224522387,'80A',34,0,to_date('2017-03-17 18:07:55','yyyy-mm-dd hh24:mi:ss'),'ZXQMM4363|该用户（帐户、客户）在选择的时间内没有相应的费用信息','2',123,'ztezm',0,'','','F','134.160.170.53','','','')
--呆坏账
select * FROM acct.BADACCT_TASK T_TASK,acct.BADACCT_TASK_PROC T_PROC
--
select* from cust.SERV_HISTORY@to_ncash where acc_nbr='18188923291';
select * from acct.acct_item_731@to_ncash where acct_id='50063482779' and state<>'5JB'
select * from cust.ACCT @to_ncash where serv_id=603057842857
