数据库同步(一)

方法一:

PROCEDURE Update_FIM_CDM_CONTRACTS IS     V_SYNCH_TYPE             VARCHAR2(30); -- 同步类型     v_old_contract_header_id NUMBER; --合同号ID     v_last_update_date       DATE; -- 最近更新时间   BEGIN     REPORT_LINE('[' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')                                 || ']开始同步CDM合同号信息...');       --设置同步类型     V_SYNCH_TYPE := 'CDM_CONTRACT';       --遍历所有合同号     FOR REC IN (SELECT *                                   FROM apps.cdm_contract_headers_v_to_fol@to_cms.world                 /*WHERE rownum = 1*/                 )          LOOP       BEGIN         SELECT contract_header_id, LAST_UPDATE_DATE           INTO v_old_contract_header_id, v_last_update_date           FROM ztefim.zte_fim_cdm_contracts          WHERE contract_header_id = rec.contract_header_id;       EXCEPTION         WHEN OTHERS THEN                   v_old_contract_header_id := 0;               END;           IF v_old_contract_header_id = 0 THEN         --插入合同号         INSERT INTO ztefim.zte_fim_cdm_contracts           (last_update_date,            last_updated_by,            creation_date,            created_by,            last_update_login,            ENABLED_FLAG,                       contract_header_id,            contract_number,            hc_contract_id,            hc_contract_number,            org_id                      ,            org_name,            customer_id,            customer_number,            customer_name,            sale_org_id                      ,            sale_org_name,            sale_region_id,            sale_region,            salesrep_id,            salesrep_name                      ,            currency_code,            contract_amount,            conclude_amount)         VALUES           (SYSDATE,            0,            SYSDATE,            0,            0,            rec.enabled_flag,                       rec.contract_header_id,            rec.contract_number,            rec.hc_contract_id,            rec.hc_contract_number,            rec.org_id                      ,            rec.NAME,            rec.customer_id,            rec.customer_number,            rec.customer_name,            rec.sale_org_id                      ,            rec.sale_org_name,            rec.sale_region_id,            rec.sale_region_name,            rec.salesrep_id,            rec.salesrep_name                      ,            rec.currency_code,            rec.contract_amount,            rec.conclude_amount);       ELSE         IF v_last_update_date != rec.LAST_UPDATE_DATE THEN           --更新数据           UPDATE ztefim.zte_fim_cdm_contracts              SET last_update_date   = rec.last_update_date,                  contract_number    = rec.contract_number,                  hc_contract_id     = rec.hc_contract_id,                  hc_contract_number = rec.hc_contract_number,                  org_id             = rec.org_id,                  ENABLED_FLAG       = rec.ENABLED_FLAG,                                   org_name        = rec.NAME,                  customer_id     = rec.customer_id,                  customer_number = rec.customer_number,                  customer_name   = rec.customer_name,                  sale_org_id     = rec.sale_org_id                                  ,                  sale_org_name  = rec.sale_org_name,                  sale_region_id = rec.sale_region_id,                  sale_region    = rec.sale_region_name,                  salesrep_id    = rec.salesrep_id,                  salesrep_name  = rec.salesrep_name                                  ,                  currency_code   = rec.currency_code,                  contract_amount = rec.contract_amount,                  conclude_amount = rec.conclude_amount            WHERE contract_header_id = rec.contract_header_id;         END IF;       END IF;     END LOOP;     --报告成功同步结果       REPORT_LINE('[' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')                                 || ']同步CDM合同号信息完毕!');       COMMIT;       -- 记录同步历史       SET_SYNCH_HISTORY(SYSDATE, V_SYNCH_TYPE, 'Y', NULL);     EXCEPTION       WHEN OTHERS THEN           --报告失败同步结果           REPORT_LINE('[' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')                                     || ']CDM合同号信息同步异常:' || SQLERRM);           ROLLBACK;           -- 记录同步历史           SET_SYNCH_HISTORY(SYSDATE, V_SYNCH_TYPE, 'N', SQLERRM);   END;

 

--输出信息

PROCEDURE REPORT_LINE(P_TEXT VARCHAR2) IS     BEGIN       IF V_REPORT_FLAG = 'Y' THEN           APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.OUTPUT, P_TEXT);         ELSE           DBMS_OUTPUT.ENABLE(4000000);           DBMS_OUTPUT.PUT_LINE(P_TEXT);         END IF;     EXCEPTION       WHEN OTHERS THEN           V_REPORT_FLAG := 'N';           DBMS_OUTPUT.ENABLE(4000000);           DBMS_OUTPUT.PUT_LINE(P_TEXT);       END;

--       -- 将同步信息插入同步历史表中       --       INSERT INTO ZTE_FBP_SYNCH_HISTORIES       (               SYNCH_DATE,               SYNCH_TYPE,               SUCCEED_FLAG,               MEMO)         VALUES       (P_SYNCH_DATE,               P_SYNCH_TYPE,               P_SUCCEED_FLAG,               P_MEMO);       COMMIT;     EXCEPTION       WHEN OTHERS THEN           NULL;       END SET_SYNCH_HISTORY;