CREATEORREPLACEPROCEDUREPROCEDURE1AS
BEGIN
--单位PAK_GCCOMP
mergeintoPAK_GCCOMPbusing(select*frompak_gccomp@scjgwhererow_addtime>(selectrow_addtimefrom(select*from(select*from
pak_gccomporderbyrow_addtimedesc)whererownum=1)))con(b.PCOMP_ID=c.PCOMP_ID)
whenmatchedthen
updatesetb.COMP_ID=c.COMP_ID,b.COMP_NAME=c.COMP_NAME,b.COMP_ZZXL=c.COMP_ZZXL,b.COMP_ZZXL_NAME=c.COMP_ZZXL_NAME,b.COMP_LXR=c.COMP_LXR,b.COMP_LXR_PHONE=c.COMP_LXR_PHONE,b.COMP_FZR=c.COMP_FZR,b.COMP_FZR_PHONE=c.COMP_FZR_PHONE,b.COMP_LWFZR=c.COMP_LWFZR,
b.COMP_LWFZR_PHONE=c.COMP_LWFZR_PHONE,b.GC_ID=c.GC_ID,b.GC_CONTENT=c.GC_CONTENT,b.ROW_ADDUSERID=c.ROW_ADDUSERID,b.ROW_ADDUSER=c.ROW_ADDUSER,b.ROW_ADDTIME=c.ROW_ADDTIME,b.IS_USERD=c.IS_USERD,b.BACC_ID=c.BACC_ID,b.PCOMP_ID_B=c.PCOMP_ID_B
whennotmatchedthen
insertvalues(c.pcomp_id,c.COMP_ID,c.COMP_NAME,c.COMP_ZZXL,c.COMP_ZZXL_NAME,c.COMP_LXR,c.COMP_LXR_PHONE,c.COMP_FZR,c.COMP_FZR_PHONE,c.COMP_LWFZR,
c.COMP_LWFZR_PHONE,c.GC_ID,c.GC_CONTENT,c.ROW_ADDUSERID,c.ROW_ADDUSER,c.ROW_ADDTIME,c.IS_USERD,c.BACC_ID,c.PCOMP_ID_B);
commit;
--班组PAK_GROUP
mergeintoPAK_GROUPbusing(select*fromPAK_GROUP@scjgwhererow_addtime>(selectrow_addtimefrom(select*from(select*from
PAK_GROUPorderbyrow_addtimedesc)whererownum=1)))con(b.GROUP_ID=c.GROUP_ID)
whenmatchedthen
updatesetb.GROUP_NAME=c.GROUP_NAME,b.ROW_ADDUSER=c.ROW_ADDUSER,b.ROW_ADDUSERID=c.ROW_ADDUSERID,b.ROW_ADDTIME=c.ROW_ADDTIME,b.GC_ID=c.GC_ID,b.PERSION_ID=c.PERSION_ID,b.PCOMP_ID=c.PCOMP_ID,b.PERSION_WORKTYPE=c.PERSION_WORKTYPE
whennotmatchedthen
insertvalues(c.GROUP_ID,c.GROUP_NAME,c.ROW_ADDUSER,c.ROW_ADDUSERID,c.ROW_ADDTIME,c.GC_ID,c.PERSION_ID,c.PCOMP_ID,c.PERSION_WORKTYPE);
commit;
--进出场PAK_LWGX
mergeintoPAK_LWGXbusing(select*fromPAK_LWGX@scjgwhererow_addtime>(selectrow_addtimefrom(select*from(select*from
PAK_LWGXorderbyrow_addtimedesc)whererownum=1)))con(b.LWGX_ID=c.LWGX_ID)
whenmatchedthen
updatesetb.LWGX_NO=c.LWGX_NO,b.LWGX_STIME=c.LWGX_STIME,b.LWGX_ETIME=c.LWGX_ETIME,b.LWGX_PAYMETHOD=c.LWGX_PAYMETHOD,b.ROW_ADDUSER=c.ROW_ADDUSER,b.ROW_ADDUSERID=c.ROW_ADDUSERID,b.ROW_ADDTIME=c.ROW_ADDTIME,b.PERSION_ID=c.PERSION_ID,b.COMP_ID=c.COMP_ID,b.GROUP_ID=c.GROUP_ID,b.GC_ID=c.GC_ID
whennotmatchedthen
insertvalues(c.LWGX_ID,c.LWGX_NO,c.LWGX_STIME,c.LWGX_ETIME,c.LWGX_PAYMETHOD,c.ROW_ADDUSER,c.ROW_ADDUSERID,c.ROW_ADDTIME,c.PERSION_ID,c.COMP_ID,c.GROUP_ID,c.GC_ID);
commit;
--人员PAK_PERSION
mergeintoPAK_PERSIONbusing(select*fromPAK_PERSION@scjgwhererow_addtime>(selectrow_addtimefrom(select*from(select*from
PAK_PERSIONorderbyrow_addtimedesc)whererownum=1)))con(b.PERSION_ID=c.PERSION_ID)
whenmatchedthen--b.PERSION_ID=c.PERSION_ID,
updatesetb.PERSION_NAME=c.PERSION_NAME,b.PERSION_TEL=c.PERSION_TEL,b.PERSION_CODE=c.PERSION_CODE,b.PERSION_BIRTHDAY=c.PERSION_BIRTHDAY,b.PERSION_SEX=c.PERSION_SEX,b.PERSION_NATION=c.PERSION_NATION,b.PERSION_ADDR=c.PERSION_ADDR,b.ROW_ADDUSER=c.ROW_ADDUSER,b.ROW_ADDUSERID=c.ROW_ADDUSERID,b.ROW_ADDTIME=c.ROW_ADDTIME,b.PERSION_IMGURL=c.PERSION_IMGURL,b.PERSION_PHONE=c.PERSION_PHONE
whennotmatchedthen
insertvalues(c.PERSION_ID,c.PERSION_NAME,c.PERSION_TEL,c.PERSION_CODE,c.PERSION_BIRTHDAY,c.PERSION_SEX,c.PERSION_NATION,c.PERSION_ADDR,c.ROW_ADDUSER,c.ROW_ADDUSERID,c.ROW_ADDTIME,c.PERSION_IMGURL,c.PERSION_PHONE);
commit;
--考勤表PAK_ATTENDANCE
mergeintoPAK_ATTENDANCEbusingPAK_ATTENDANCE@scjgcon(b.id=c.id)
whenmatchedthen
updatesetb.idcardnum=c.idcardnum,b.createtime=c.createtime,b.name=c.name,b.serialnumber=c.serialnumber,b.importtype=c.importtype,b.bacc_id=c.bacc_id,b.workplace=c.workplace
whennotmatchedthen
insertvalues(c.id,c.idcardnum,c.createtime,c.name,c.serialnumber,c.importtype,c.bacc_id,c.workplace);
commit;
--企业端用户信息SF_AUTH_APPUSER
mergeintoSF_AUTH_APPUSER@scjgbusing(select*fromSF_AUTH_APPUSERwhererow_addtime>(selectrow_addtimefrom(select*from(select*from
SF_AUTH_APPUSER@scjgorderbyrow_addtimedesc)whererownum=1)))con(b.APPUSER_ID=c.APPUSER_ID)
whenmatchedthen
updatesetb.APPUSER_LOGID=c.APPUSER_LOGID,b.APPUSER_NAME=c.APPUSER_NAME,b.APPUSER_ALIASNAME=c.APPUSER_ALIASNAME,b.APPUSER_DOMAIN=c.APPUSER_DOMAIN,b.APPUSER_DDEPT=c.APPUSER_DDEPT,b.APPUSER_MPHONE=c.APPUSER_MPHONE,b.APPUSER_EMAIL=c.APPUSER_EMAIL,b.APPUSER_LASTLOGIN=c.APPUSER_LASTLOGIN,b.APPUSER_ROOT=c.APPUSER_ROOT,b.APPUSER_PWD=c.APPUSER_PWD,
b.APPUSER_CHANGEPWD=c.APPUSER_CHANGEPWD,b.APPUSER_CHANGEPWDDATE=c.APPUSER_CHANGEPWDDATE,b.APPUSER_LOGERROR=c.APPUSER_LOGERROR,b.APPUSER_TRYLOG=c.APPUSER_TRYLOG,b.APPUSER_BEGINDATE=c.APPUSER_BEGINDATE,b.APPUSER_OUTSERVICEDATE=c.APPUSER_OUTSERVICEDATE,b.APPUSER_GBID=c.APPUSER_GBID,b.APPUSER_DEFAULTPAGE=c.APPUSER_DEFAULTPAGE,
b.APPUSER_INDEXPORTAL=c.APPUSER_INDEXPORTAL,b.APPUSER_RELATION=c.APPUSER_RELATION,b.VALID_FLAG=c.VALID_FLAG,b.ROW_ADDUSER=c.ROW_ADDUSER,b.ROW_ADDTIME=c.ROW_ADDTIME,b.ROW_ADDUSERID=c.ROW_ADDUSERID,b.DDEPT_ORDER=c.DDEPT_ORDER,b.ROW_EDITUSER=c.ROW_EDITUSER,b.ROW_EDITTIME=c.ROW_EDITTIME,b.ROW_EDITUSERID=c.ROW_EDITUSERID,
b.ROW_STATUS=c.ROW_STATUS,b.APPUSER_ORDER=c.APPUSER_ORDER,b.GCUSER_STATUS=c.GCUSER_STATUS,b.IS_BJ=c.IS_BJ
whennotmatchedthen
insertvalues(c.APPUSER_ID,c.APPUSER_LOGID,c.APPUSER_NAME,c.APPUSER_ALIASNAME,c.APPUSER_DOMAIN,c.APPUSER_DDEPT,c.APPUSER_MPHONE,c.APPUSER_EMAIL,c.APPUSER_LASTLOGIN,c.APPUSER_ROOT,c.APPUSER_PWD,
c.APPUSER_CHANGEPWD,c.APPUSER_CHANGEPWDDATE,c.APPUSER_LOGERROR,c.APPUSER_TRYLOG,c.APPUSER_BEGINDATE,c.APPUSER_OUTSERVICEDATE,c.APPUSER_GBID,c.APPUSER_DEFAULTPAGE,
c.APPUSER_INDEXPORTAL,c.APPUSER_RELATION,c.VALID_FLAG,c.ROW_ADDUSER,c.ROW_ADDTIME,c.ROW_ADDUSERID,c.DDEPT_ORDER,c.ROW_EDITUSER,c.ROW_EDITTIME,c.ROW_EDITUSERID,
c.ROW_STATUS,c.APPUSER_ORDER,c.GCUSER_STATUS,c.IS_BJ);
commit;
--PAK_BANK_ACCOUNT
mergeintoPAK_BANK_ACCOUNT@scjgbusing(select*fromPAK_BANK_ACCOUNTwhereADD_TIME>(selectADD_TIMEfrom(select*from(select*from
PAK_BANK_ACCOUNT@scjgorderbyADD_TIMEdesc)whererownum=1)))con(b.bacc_id=c.bacc_id)
whenmatchedthen
updatesetb.BANK_BRANCHNAME=c.BANK_BRANCHNAME,b.BANK_ACCOUNT=c.BANK_ACCOUNT,b.BANK_MONEY=c.BANK_MONEY,b.BANK_ID=c.BANK_ID,b.APPUSER_LOGID=c.APPUSER_LOGID,b.IS_IMP=c.IS_IMP,b.LZZGY1_PHONE=c.LZZGY1_PHONE,b.LZZGY1_NAME=c.LZZGY1_NAME,b.STAFF_COMPFZR=c.STAFF_COMPFZR,b.STAFF_NAME=c.STAFF_NAME,b.STAFF_PHONE=c.STAFF_PHONE,b.YEZHU=c.YEZHU,b.PHONE_YEZHU=c.PHONE_YEZHU,b.STAFF_COMPFZRPHONE=c.STAFF_COMPFZRPHONE,
b.JS_COMP_NAME=c.JS_COMP_NAME,b.SG_COMP_NAME=c.SG_COMP_NAME,b.ADD_USER=c.ADD_USER,b.ADD_TIME=c.ADD_TIME,b.BACC_NAME=c.BACC_NAME,b.IS_BSJ=c.IS_BSJ,b.BANK_AREA=c.BANK_AREA,b.PROJECT_ID=c.PROJECT_ID,b.BANK_STATE=c.BANK_STATE,b.QXFLAG=c.QXFLAG
whennotmatchedthen
insertvalues(c.bacc_id,c.BANK_BRANCHNAME,c.BANK_ACCOUNT,c.BANK_MONEY,c.BANK_ID,c.APPUSER_LOGID,c.IS_IMP,c.LZZGY1_PHONE,c.LZZGY1_NAME,c.STAFF_COMPFZR,c.STAFF_NAME,c.STAFF_PHONE,c.YEZHU,c.PHONE_YEZHU,c.STAFF_COMPFZRPHONE,c.JS_COMP_NAME,c.SG_COMP_NAME,c.ADD_USER,c.ADD_TIME,c.BACC_NAME,c.IS_BSJ,c.BANK_AREA,c.PROJECT_ID,c.BANK_STATE,c.QXFLAG);
commit;
--PAK_BANK_GC
mergeintoPAK_BANK_GC@scjgbusingPAK_BANK_GCcon(b.BGC_ID=c.BGC_ID)
whenmatchedthen
updatesetb.GC_ID=c.GC_ID,b.BACC_ID=c.BACC_ID
whennotmatchedthen
insertvalues(c.BGC_ID,c.GC_ID,c.BACC_ID);
commit;
--PAK_ACCOUNT;
mergeintoPAK_ACCOUNTbusing(select*fromPAK_ACCOUNT@scjgwhererow_addtime>(selectrow_addtimefrom(select*from(select*from
PAK_ACCOUNTorderbyrow_addtimedesc)whererownum=1)))con(b.ACCOUNT_ID=c.ACCOUNT_ID)
whenmatchedthen
updatesetb.GROUP_ID=c.GROUP_ID,b.PERSION_ID=c.PERSION_ID,b.ACCOUNT_NO=c.ACCOUNT_NO,b.ACCOUNT_MONTH=c.ACCOUNT_MONTH,b.ACCOUNT_DAYS=c.ACCOUNT_DAYS,b.SALARY_SUM=c.SALARY_SUM,b.SALARY_NUM=c.SALARY_NUM,b.ROW_ADDUSER=c.ROW_ADDUSER,b.ROW_ADDUSERID=c.ROW_ADDUSERID,
b.ROW_ADDTIME=c.ROW_ADDTIME,b.FILE_ID=c.FILE_ID,b.GC_ID=c.GC_ID,b.TASK_CODE=c.TASK_CODE
whennotmatchedthen
insertvalues(c.ACCOUNT_ID,c.GROUP_ID,c.PERSION_ID,c.ACCOUNT_NO,c.ACCOUNT_MONTH,c.ACCOUNT_DAYS,c.SALARY_SUM,c.SALARY_NUM,c.ROW_ADDUSER,c.ROW_ADDUSERID,
c.ROW_ADDTIME,c.FILE_ID,c.GC_ID,c.TASK_CODE);
commit;
--PAK_PERSION_BANK
mergeintoPAK_PERSION_BANKbusing(select*fromPAK_PERSION_BANK@scjgwhererow_addtime>(selectrow_addtimefrom(select*from(select*from
PAK_PERSION_BANKorderbyrow_addtimedesc)whererownum=1)))con(b.BANK_ID=c.BANK_ID)
whenmatchedthen
updatesetb.BANK_TYPE=c.BANK_TYPE,b.BANK_CODE=c.BANK_CODE,b.PERSION_ID=c.PERSION_ID,b.GC_ID=c.GC_ID,b.ROW_ADDTIME=c.ROW_ADDTIME
whennotmatchedthen
insertvalues(c.BANK_ID,c.BANK_TYPE,c.BANK_CODE,c.PERSION_ID,c.GC_ID,c.ROW_ADDTIME);
commit;
ENDPROCEDURE1;