oracle 数据同步
CREATE OR REPLACE PROCEDURE C2U.pro_send_contact_ecp (
p_send_data IN VARCHAR2)
----p_send_data Y: send data to ecp N: Just update snapshot table in local site
IS
CURSOR c_customer
IS
SELECT cust_party_id,
customer_name_cn,
customer_name_en,
customer_name_alias,
NVL (cust_grade, 'C0') cust_grade,
status,
customer_type,
url,
null real_erp_id
FROM apps.web_customer_master_v@to_240_apps
UNION ALL
SELECT party_id cust_party_id,
customer_name customer_name_cn,
NULL customer_name_en,
NULL customer_name_alias,
NVL ( (SELECT MAX (cust_grade)
FROM c2u_ncd_contact_master nc
WHERE nc.cust_party_id = cus.party_id),
'C0')
cust_grade,
'A' status,
'客户' customer_type,
NULL url,
mapping_party_id real_erp_id
FROM c2u_ncd_customer_master cus
WHERE party_id IS NOT NULL
and customer_name is not null;
CURSOR c_contact
IS
SELECT *
FROM (SELECT contact_id,
cust_party_id,
chinese_name,
english_name,
dept,
dept_type,
title,
title_type,
phones,
emails,
sex,
address_cn,
address_en,
status
FROM web_contact_master_v
where dept_type in
('供应链',
'公司高管',
'研发',
'生产制造' ---- cory_liu 20160902 add
)
UNION ALL
SELECT contact_id,
cust_party_id,
chinese_name,
NULL english_name,
dept,
NULL dept_type,
title,
NULL title_type,
phones,
emails,
sex,
address_cn,
NULL address_en,
'A' status
FROM c2u_ncd_contact_master);
v_customer_str VARCHAR2 (30000);
v_customer_send_date DATE;
v_contact_str VARCHAR2 (30000);
v_contact_send_date DATE;
v_ecm_date_str VARCHAR2 (30);
BEGIN
----- customer data
--- step 1 , update snapshot table
FOR r_customer IN c_customer
LOOP
BEGIN
SELECT customer_name_cn
|| customer_name_en
|| customer_name_alias
|| NVL (cust_grade, 'C0')
|| status
|| customer_type
|| url
|| real_erp_id
INTO v_customer_str
FROM t_erp_company_snapshot
WHERE cust_party_id = r_customer.cust_party_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_customer_str := NULL;
END;
IF v_customer_str IS NULL
THEN
INSERT INTO t_erp_company_snapshot (cust_party_id,
CUSTOMER_NAME_CN,
CUSTOMER_NAME_EN,
CUSTOMER_NAME_ALIAS,
CUST_GRADE,
STATUS,
CUSTOMER_TYPE,
URL,
real_erp_id,
UPDATE_DATE)
VALUES (r_customer.cust_party_id,
r_customer.CUSTOMER_NAME_CN,
r_customer.CUSTOMER_NAME_EN,
r_customer.CUSTOMER_NAME_ALIAS,
r_customer.CUST_GRADE,
r_customer.STATUS,
r_customer.CUSTOMER_TYPE,
r_customer.URL,
r_customer.real_erp_id,
SYSDATE);
ELSIF v_customer_str <>
r_customer.customer_name_cn
|| r_customer.customer_name_en
|| r_customer.customer_name_alias
|| NVL (r_customer.cust_grade, 'C0')
|| r_customer.status
|| r_customer.customer_type
|| r_customer.url
|| r_customer.real_erp_id
THEN
UPDATE t_erp_company_snapshot
SET CUSTOMER_NAME_CN = r_customer.CUSTOMER_NAME_CN,
CUSTOMER_NAME_EN = r_customer.CUSTOMER_NAME_EN,
CUSTOMER_NAME_ALIAS = r_customer.CUSTOMER_NAME_ALIAS,
CUST_GRADE = r_customer.CUST_GRADE,
STATUS = r_customer.STATUS,
CUSTOMER_TYPE = r_customer.CUSTOMER_TYPE,
URL = r_customer.URL,
real_erp_id = r_customer.real_erp_id,
UPDATE_DATE = SYSDATE
WHERE cust_party_id = r_customer.cust_party_id;
END IF;
END LOOP;
COMMIT;
--- step 2 , get last send date
SELECT last_send_date
INTO v_customer_send_date
FROM c2u_send_contact_ecp_control
WHERE data_table = 'T_ERP_COMPANY';
--- step 3 , send data
IF p_send_data = 'Y'
THEN
BEGIN
FOR i IN (SELECT *
FROM t_erp_company_snapshot
WHERE update_date > v_customer_send_date)
LOOP
INSERT INTO t_erp_company (erp_id,
erp_name,
erp_name_second,
erp_other_name,
ep_level,
state,
ep_type,
web_site,
real_erp_id)
VALUES (i.cust_party_id,
i.customer_name_cn,
i.customer_name_en,
i.customer_name_alias,
i.cust_grade,
i.status,
i.customer_type,
i.url,
i.real_erp_id);
COMMIT;
END LOOP;
END;
---step 4 , update last send date
UPDATE c2u_send_contact_ecp_control
SET last_send_date = SYSDATE
WHERE data_table = 'T_ERP_COMPANY';
END IF;
COMMIT;
----- contact data
--- step 1 , update snapshot table
FOR r_contact IN c_contact
LOOP
BEGIN
SELECT CUST_PARTY_ID
|| CHINESE_NAME
|| ENGLISH_NAME
|| DEPT
|| DEPT_TYPE
|| TITLE
|| TITLE_TYPE
|| PHONES
|| EMAILS
|| SEX
|| ADDRESS_CN
|| ADDRESS_EN
|| STATUS
INTO v_contact_str
FROM t_erp_contacts_snapshot
WHERE contact_id = r_contact.contact_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_contact_str := NULL;
END;
IF v_contact_str IS NULL
THEN
INSERT INTO t_erp_contacts_snapshot (contact_id,
CUST_PARTY_ID,
CHINESE_NAME,
ENGLISH_NAME,
DEPT,
DEPT_TYPE,
TITLE,
TITLE_TYPE,
PHONES,
EMAILS,
SEX,
ADDRESS_CN,
ADDRESS_EN,
STATUS,
UPDATE_DATE)
VALUES (r_contact.contact_id,
r_contact.CUST_PARTY_ID,
r_contact.CHINESE_NAME,
r_contact.ENGLISH_NAME,
r_contact.DEPT,
r_contact.DEPT_TYPE,
r_contact.TITLE,
r_contact.TITLE_TYPE,
r_contact.PHONES,
r_contact.EMAILS,
r_contact.SEX,
r_contact.ADDRESS_CN,
r_contact.ADDRESS_EN,
r_contact.STATUS,
SYSDATE);
ELSIF v_contact_str <>
r_contact.CUST_PARTY_ID
|| r_contact.CHINESE_NAME
|| r_contact.ENGLISH_NAME
|| r_contact.DEPT
|| r_contact.DEPT_TYPE
|| r_contact.TITLE
|| r_contact.TITLE_TYPE
|| r_contact.PHONES
|| r_contact.EMAILS
|| r_contact.SEX
|| r_contact.ADDRESS_CN
|| r_contact.ADDRESS_EN
|| r_contact.STATUS
THEN
UPDATE t_erp_contacts_snapshot
SET CUST_PARTY_ID = r_contact.CUST_PARTY_ID,
CHINESE_NAME = r_contact.CHINESE_NAME,
ENGLISH_NAME = r_contact.ENGLISH_NAME,
DEPT = r_contact.DEPT,
DEPT_TYPE = r_contact.DEPT_TYPE,
TITLE = r_contact.TITLE,
TITLE_TYPE = r_contact.TITLE_TYPE,
PHONES = r_contact.PHONES,
EMAILS = r_contact.EMAILS,
SEX = r_contact.SEX,
ADDRESS_CN = r_contact.ADDRESS_CN,
ADDRESS_EN = r_contact.ADDRESS_EN,
STATUS = r_contact.STATUS,
UPDATE_DATE = SYSDATE
WHERE contact_id = r_contact.contact_id;
END IF;
END LOOP;
COMMIT;
--- step 2 , get last send date
SELECT last_send_date
INTO v_contact_send_date
FROM c2u_send_contact_ecp_control
WHERE data_table = 'T_ERP_CONTACTS';
SELECT TO_CHAR (v_contact_send_date, 'yyyymmddhh24miss')
INTO v_ecm_date_str
FROM DUAL;
IF p_send_data = 'Y'
THEN
--- step 3 , send data
BEGIN
FOR i IN (SELECT *
FROM t_erp_contacts_snapshot
WHERE update_date > v_contact_send_date)
LOOP
-- dbms_output.put (i.contact_id);
INSERT INTO t_erp_contacts (erp_id,
erp_company_id,
name,
name_second,
dept,
dept_type,
position,
position_type,
link_data,
email,
sex,
-- address_ch,
-- address_en,
state)
VALUES (i.contact_id,
i.cust_party_id,
i.chinese_name,
i.english_name,
i.dept,
i.dept_type,
i.title,
i.title_type,
i.phones,
i.emails,
i.sex,
-- i.address_cn,
-- i.address_en,
i.status);
UPDATE t_erp_contacts
SET address_ch = i.address_cn
WHERE erp_id = i.contact_id
AND CREATE_TIME >
TO_DATE (v_ecm_date_str, 'yyyymmddhh24miss');
UPDATE t_erp_contacts
SET address_en = i.address_en
WHERE erp_id = i.contact_id
AND CREATE_TIME >
TO_DATE (v_ecm_date_str, 'yyyymmddhh24miss');
dbms_output.put_line ('ok');
COMMIT;
END LOOP;
END;
---step 4 , update last send date
UPDATE c2u_send_contact_ecp_control
SET last_send_date = SYSDATE
WHERE data_table = 'T_ERP_CONTACTS';
END IF;
COMMIT;
END;
/
