星期一
横表变成纵表
Source表需要再处理一次变成系统需要的数据
为什么要清算 为什么要有结算清单
select *from md_process_control t
where t.start_time>to_date('20120801','yyyymmdd');
CRM抽数过程
Impl
本地数据处理过程
Info
数据准备过程
SP_SETT_SRC
结算任务
select t.*, t.rowidfrom agent_data.settle_task_result t
跑清单
SP_SETT_LIST
select * from md_process_control t
where t.start_time>to_date('20120801','yyyymmdd')
AND t.process_name='SP_SETT_LIST'
如果出现清单任务报错的话
Stat_date 就是任务ID 可以用一下语句查询出来哪个任务的出错了
select t.*, t.rowid from agent_data.settle_task_result t
WHERE t.execute_time>to_date('20120801','yyyymmdd')
Stat_date对应的是task_result_id
select t.*, t.rowidfrom meso_sett_src_prod t传说中很重要的中间表
SELECT *FROM crm2_sett_result_list_prod传说是清单
select t.*, t.rowid from agent_data.settle_task_result t
WHERE t.execute_time>to_date('20120801','yyyymmdd')
SELECT *FROM inst_offer_prod tWHERE t.redu_access_number='14814XXXX'
14814XXXX是电话号码 可以换成1898084XXXX
SELECT *
FROM INST.OFFER_PRODXXXX A
WHERE A.REDU_ACCESS_NUMBER = '14814XXXX';
SELECT C.COMP_OFFER, B.NAME, A.*
FROM INST_OFFER_MEMBER A, SPEC_OFFER_SPEC B, SPEC_OFFER_SPEC_INFO C
WHERE A.LATN_ID = B.LATN_ID
AND A.OFFER_SPEC_ID = B.OFFER_SPEC_ID
AND A.OFFER_SPEC_ID = C.OFFER_SPEC_ID
AND A.LATN_ID = 11XXXX
AND A.MEMBER_ID = 35302236XXXX;
SELECT A.ACTIVITY_PRICE_PLAN,
A.PROD_ID,
A.OFFER_ID,
A.PRE_AP_CHARGE,
A.TYPE_PRICE_PLAN,
A.TYPE_COMP_PRICE_PLAN,
A.TYPE_COMP_PAY_PRICE_PLAN,
A.*
FROM MESO_SETT_SRC_PROD A
WHERE A.TASK_RESULT_ID = 4XXXX80
AND A.ACTIONTYPE = 30001
AND A.PROD_ID = 353XXXX2360466;
SELECT * FROM Settle_Index_Value_Merge
a WHERE a.latn_id=11500
AND a.src_id='10900005XXXX';
SELECT * FROM agent_data.Settle_Index_Value_Merge_his
a WHERE a.latn_id=11500
AND a.src_id='10900005XXXX4';
SELECT * FROM settle_rule a
WHERE a.latn_id=XXXXAND a.expression LIKE '%9XXXX174%'
AND a.expression LIKE '%30001%';
SELECT *
FROM AGENT_DATA.CONTRACT_RULE_RELATION A,
AGENT_DATA.RELT_CONTRACT_AGENT B
WHERE A.CONTRACT_ID = B.CONTRACTID
AND B.REL_OBJECT_ID = 11XXXX
AND A.RULE_ID IN (342011,342XXXX);
查询过程对应的session 跑到哪一步了
SELECT DISTINCT A.USERNAME,
A.SID,
SERIAL#,
A.MACHINE,
A.LOGON_TIME,
A.MACHINE,a.EVENT
FROM V$SESSION A, V$LOCKED_OBJECT B
WHERE A.SID = B.SESSION_ID
ORDER BY A.LOGON_TIME;
SELECT C.SQL_TEXT
FROM V$SESSION A, V$SQLTEXT C
WHERE A.SID = 144
AND C.HASH_VALUE = A.SQL_HASH_VALUE
AND C.ADDRESS = A.SQL_ADDRESS
ORDER BY C.PIECE;
星期二
第一步:给出一个号码 1898084XXXX
Select * from inst_offer_prod t where t.redu_access_number =’1898080XXXX’
查询出prod_id
第二步:根据prod id 查询出 offer_spec_id 就是settle_index_value _merge表中的src_id
SELECT C.COMP_OFFER, B.NAME, A.*
FROM INST_OFFER_MEMBER A, SPEC_OFFER_SPEC B, SPEC_OFFER_SPEC_INFO C
WHERE A.LATN_ID = B.LATN_ID
AND A.OFFER_SPEC_ID = B.OFFER_SPEC_ID
AND A.OFFER_SPEC_ID = C.OFFER_SPEC_ID
AND A.LATN_ID =11500
AND A.MEMBER_ID =353022360466;
最新未结算 脚本如下:
SELECT *FROM AGT_AREA T;
SELECT T.*, T.ROWID
FROM MESO_SETT_SRC_PROD T
WHERE T.TASK_RESULT_ID =41280
AND T.PROD_ID =35302236XXXX;---查出 actiontype
SELECT *
FROM INST.OFFER_PROD@DBLINK_CRM2_DB1 A
WHERE A.REDU_ACCESS_NUMBER ='1339837XXXX';-- 查出prod_id
SELECT C.COMP_OFFER, B.NAME, A.*
FROM INST_OFFER_MEMBER A, SPEC_OFFER_SPEC B, SPEC_OFFER_SPEC_INFO C
WHERE A.LATN_ID = B.LATN_ID
AND A.OFFER_SPEC_ID = B.OFFER_SPEC_ID
AND A.OFFER_SPEC_ID = C.OFFER_SPEC_ID
AND A.LATN_ID =1XXXX
AND A.MEMBER_ID =16010277XXXX;-------查出的offer_spec_id 就是 settle_index_value_merge 中的src_id
SELECT A.ACTIVITY_PRICE_PLAN,
A.PROD_ID,
A.OFFER_ID,
A.PRE_AP_CHARGE,
A.TYPE_PRICE_PLAN,
A.TYPE_COMP_PRICE_PLAN,
A.TYPE_COMP_PAY_PRICE_PLAN,
A.*
FROM MESO_SETT_SRC_PROD A
WHERE A.TASK_RESULT_ID =41XXXX
AND A.ACTIONTYPE =XXXX
AND A.PROD_ID =16010277XXXX;
SELECT *
FROM SETTLE_INDEX_VALUE_MERGE A
WHERE A.LATN_ID =10XXXX
AND A.SRC_ID ='100000000XXXX';
SELECT *
FROM AGENT_DATA.SETTLE_INDEX_VALUE_MERGE_HIS A
WHERE A.LATN_ID =XXXX AND A.SRC_ID ='10900XXXX';----可以查询出规则更改的最近一次时间
SELECT *
FROM SETTLE_RULE A
WHERE A.LATN_ID =10XXXX0
AND A.EXPRESSIONLIKE'%93XXXX%'
AND A.EXPRESSIONLIKE'%XXXX%';--- 可以看到expression 表达式!
SELECT *
FROM SETTLE_RULE A
WHERE A.LATN_ID =10XXXX0
AND A.RULE_ID =34XXXX;
SELECT *
FROM AGENT_DATA.CONTRACT_RULE_RELATION A,
AGENT_DATA.RELT_CONTRACT_AGENT B
WHERE A.CONTRACT_ID = B.CONTRACTID
AND B.REL_OBJECT_ID =12XXXX08------119XXXX是co_branch_id
AND A.RULE_IDIN (353XXXX05,35XXXX25);-----查合同
星期三
商店员表的整理
agt_agent
agt_branch
emp_employee
按照字段提取!
广安重新出账
星期四
Powerdesign中的表的箭头的含义是 指向的那张表 向源表提供数据 也就是说指向那张表有外键
熟悉电信渠道系统