资料接口配置手册--20120926.docx

S 资料接口配置手册描述：CRM和计费2.0的资料接口，需要配置套餐促销的转换关系。资料接口的配置转换涉及3张表：ZK.DISCT_RELATE -映射计费商品ZK.DISCT_OBJECT_RELATE -映射计费商品成员ZK.DISCT_PARAM_RELATE -映射计费商品参数环境：YZDB用户：ZK一产品包配置1、虚用户INSERT INTO ZK.DISCT_RELATE SELECT DISTINCT PAR_PLAN_ID, 0, PAR_PLAN_ID, 1, 客户级计划转组合商品（河南） FROM DATA.SYX_CUST_PRICING_DETAILNTEST_ZT WHERE PAR_PLAN_ID IN (虚用户PLAN_ID);INSERT INTO ZK.DISCT_RELATE SELECT PLAN_ID, PROM_ID, PROM_ID, 2, 客户计划内必选（河南） FROM DATA.SYX_CUST_PRICING_COMBINENTEST_ZT WHERE PLAN_ID IN (虚用户PLAN_ID);INSERT INTO ZK.DISCT_OBJECT_RELATE SELECT DISTINCT PAR_PLAN_ID, PLAN_ID, 0, NEW_DETAIL_ID, 3, 客户级计划转组合产品套餐（河南） FROM DATA.SYX_CUST_PRICING_DETAILNTEST_ZT WHERE PAR_PLAN_ID IN (虚用户PLAN_ID) AND NEW_DETAIL_ID IS NOT NULL;2、子计划INSERT INTO ZK.DISCT_RELATE SELECT PLAN_ID, PLAN_ID, PLAN_ID, 12, 过渡期商品 FROM DATA.SYX_CUST_PRICING_DETAILNTEST_ZT WHERE PAR_PLAN_ID IN (虚用户PLAN_ID) AND PLAN_ID IN (SELECT PLAN_ID FROM CP.PM_PLANS WHERE (PLAN_CLASS NOT IN (6, 8) AND PAY_MODE 5);INSERT INTO ZK.DISCT_OBJECT_RELATE SELECT DISTINCT 0, PLAN_ID, 0, PLAN_ID, 1, 用户级计划转单产品套餐（河南） FROM CP.PM_PLANS WHERE PLAN_ID IN (select plan_id from DATA.SYX_CUST_PRICING_DETAILNTEST_ZT WHERE PAR_PLAN_ID IN (虚用户PLAN_ID) AND PLAN_ID IN (SELECT PLAN_ID FROM CP.PM_PLANS WHERE (PLAN_CLASS NOT IN (6, 8) AND PAY_MODE 5);3、子计划可选促销insert into ZK.DISCT_RELATE select a.plan_id, m_id, m_id, 6, 用户级计划内可选促销（河南） from cp.pm_plan_prom a where plan_id in (select plan_id from data.syx_cust_pricing_detailNTEST_ZT where par_plan_id in (虚用户PLAN_ID) and new_detail_id is not null);insert into ZK.DISCT_OBJECT_RELATE select 0, a.plan_id, m_id, m_id, 2, 用户级计划下促销转单产品套餐（河南） from cp.pm_plan_prom a where plan_id in (select plan_id from data.syx_cust_pricing_detailNTEST_ZT where par_plan_id in (虚用户PLAN_ID) and new_detail_id is not null);4、客户级促销 INSERT INTO ZK.DISCT_RELATE SELECT DISTINCT A.PLAN_ID, B.PROD_ID, B.PROD_ID, 3, 客户计划内可选（河南） FROM CP.PM_PLANS A, CP.PM_PRODUCTS B WHERE A.PAY_MODE = 5 AND PLAN_ID = 需用户PLAN_ID AND B.PROD_ID = 促销ID; INSERT INTO ZK.DISCT_OBJECT_RELATE SELECT DISTINCT A.PLAN_ID, 0, B.PROD_ID, B.PROD_ID, 4, 客户级计划下促销转单产品套餐（河南） FROM CP.PM_PLANS A WHERE CP.PM_PLANS A, CP.PM_PRODUCTS B WHERE A.PAY_MODE = 5 AND PLAN_ID = 需用户PLAN_ID AND B.PROD_ID = 促销ID;5、通配子计划配置INSERT INTO ZK.DISCT_OBJECT_RELATE SELECT 50032250, PLAN_ID2, 0, PLAN_ID2, 3, 客户级计划转组合产品套餐（河南） FROM CP.PM_PLAN_REL A WHERE PLAN_ID1 IN (50032251, 50032252) AND A.RELATION_TYPE = 300;6、活动客户级促销INSERT INTO ZK.DISCT_RELATE SELECT DISTINCT 0, PROD_ID, PROD_ID, 7, 计划外促销（河南） FROM CP.PM_PRODUCTS a, COMM.PRODUCT_OFFERACCTDB B WHERE PROD_ID IN (select prod_id from cp.pm_busi_prod where prom_type = 4) and prod_id in (50032420,50032421,50032422) and A.PROd_ID = B.OFFER_ID;INSERT INTO ZK.DISCT_OBJECT_RELATE SELECT DISTINCT 0, 0, PROD_ID, PROD_ID, 5, 计划外促销转单产品套餐（河南） FROM CP.PM_PRODUCTS a,COMM.PRODUCT_OFFER_DETAILACCTDB B WHERE PROD_ID IN (select prod_id from cp.pm_busi_prod where prom_type = 4) and prod_id in (50032420,50032421,50032422) and A.PROd_ID = B.OFFER_ID AND B.ELEMENT_SEQ = 1;二：单用户套餐1、子计划INSERT INTO ZK.DISCT_RELATE SELECT PLAN_ID, PLAN_ID, PLAN_ID, 4, 用户计划内主产品（河南） FROM CP.PM_PLANS A, COMM.PRODUCT_OFFERACCTDB B WHERE A.PLAN_ID IN (50032435,50032439) AND A.PLAN_ID=B.OFFER_ID;-子计划INSERT INTO ZK.DISCT_OBJECT_RELATE SELECT DISTINCT 0, PLAN_ID, 0, PLAN_ID, 1, 用户级计划转单产品套餐（河南） FROM CP.PM_PLANS A, COMM.PRODUCT_OFFER_DETAILACCTDB B WHERE PLAN_ID IN (50032435,50032439) AND A.PLAN_ID = B.OFFER_ID AND B.ELEMENT_SEQ=1;- 2、子计划可选促销INSERT INTO ZK.DISCT_RELATE SELECT A.PLAN_ID, A.PROM_ID, b.offer_id, 6, 用户级计划内可选促销（河南） FROM CP.PM_PLAN_PROM A, COMM.PRODUCT_OFFERACCTDB B WHERE PLAN_ID IN (50032496, 50030140) AND PROM_ID IN (50030115, 50030116, 50030117, 50030118, 50030119, 50030120, 50030964, 50030965, 50030966, 50030967, 50030968, 50030969, 50030970, 50030971, 50030972, 50030973, 50030974, 50030975) AND A.PROM_ID = B.OFFER_ID and not exists (select 1 from ZK.DISCT_RELATE xx where a.plan_id = xx.plan_id and m_id = d_id);-子计划可选促销INSERT INTO ZK.DISCT_OBJECT_RELATE SELECT 0, A.PLAN_ID, A.PROM_ID, b.offer_detail_id, 2, 用户级计划下促销转单产品套餐（河南） FROM CP.PM_PLAN_PROM A, COMM.PRODUCT_OFFER_DETAILACCTDB B WHERE PLAN_ID IN (50032496, 50030140) AND PROM_ID IN (50030115, 50030116, 50030117, 50030118, 50030119, 50030120, 50030964, 50030965, 50030966, 50030967, 50030968, 50030969, 50030970, 50030971, 50030972, 50030973, 50030974, 50030975) AND A.PROM_ID = B.OFFER_ID AND B.ELEMENT_SEQ = 1 and not exists (select * from ZK.DISCT_OBJECT_RELATE xx where a.plan_id = xx.serv_plan and m_id = d_id);三、计划外促销【注意：活动促销属于计划外促销】INSERT INTO ZK.DISCT_RELATE SELECT DISTINCT 0, A.PROD_ID, B.OFFER_ID, 7, 计划外促销（河南） FROM CP.PM_PRODUCTS A, COMM.PRODUCT_OFFERACCTDB B WHERE A.PROD_ID IN (50032397, 50032398, 50032399, 50032400, 50032401, 50032402, 50032403) AND A.PROD_ID = B.OFFER_ID; INSERT INTO ZK.DISCT_OBJECT_RELATE SELECT DISTINCT 0, 0, A.PROD_ID, B.OFFER_DETAIL_ID, 5, 计划外促销转单产品套餐（河南） FROM CP.PM_PRODUCTS A, COMM.PRODUCT_OFFER_DETAILACCTDB B WHERE A.PROD_ID IN (50032397, 50032398, 50032399, 50032400, 5003240