--删除临时表IFEXISTS(SELECT1FROM(SELECTNAMEASTABLE_NAME,XTYPEASTABLE_XTYPEFROMsysobjectsWHEREXTYPE='U'ORXTYPE='V')ASKSQL_USERTABLESWHERETABLE_NAME='TEMP_SUBFENTRYID')BEGINDROPTABLETEMP_SUBFENTRYIDEND;--创建临时表SELECT*INTOTEMP_SUBFENTRYIDFROM(SELECTT1.FBILLNOASWWFBILLNO,T2.FSEQASWWFSEQ,T2.FENTRYIDASWWFENTRYID,T4.FENTRYIDASPOFENTRYID,T5.FENTRYIDASRECFENTRYID,T6.FENTRYIDASSTKFENTRYID,T7.FENTRYIDASMRAFENTRYID,T8.FENTRYIDASMRBFENTRYID,T9.FENTRYIDASINITSTKFENTRYID,T10.FENTRYIDASINITMRSFENTRYIDFROMT_SUB_REQORDERT1INNERJOINT_SUB_REQORDERENTRYT2ONT1.FID=T2.FID--委外订单LEFTJOINT_PUR_POORDERENTRY_LKT3ONT2.FENTRYID=T3.FSIDANDT3.FSTABLENAME='T_SUB_REQORDERENTRY'LEFTJOINT_PUR_POORDERENTRYT4ONT3.FENTRYID=T4.FENTRYID--采购订单LEFTJOINT_PUR_RECEIVEENTRYT5ONT4.FENTRYID=T5.FPOORDERENTRYID--收料通知单LEFTJOINT_STK_INSTOCKENTRYT6ONT4.FENTRYID=T6.FPOORDERENTRYID--采购入库单LEFTJOINT_PUR_MRAPPENTRYT7ONT4.FENTRYID=T7.FPOORDERENTRYID--退料申请单LEFTJOINT_PUR_MRBENTRYT8ONT4.FENTRYID=T8.FPOORDERENTRYID--采购退料单LEFTJOINT_STK_INITINSTOCKENTRYT9ONT4.FENTRYID=T9.FPOORDERENTRYID--期初采购入库单LEFTJOInT_PUR_INITMRSENTRYT10ONT4.FENTRYID=T10.FPOORDERENTRYID--期初采购退料单--系统数据量较大时可根据需求过滤掉久远的数据或根据时间分批执行--WHERET1.FCREATEDATE>'2022-10-01')TT;--备份数据SELECT*INTOT_PUR_POORDERENTRY_R_BAK_20231001FROMT_PUR_POORDERENTRY_R;--采购订单SELECT*INTOT_PUR_RECEIVEENTRY_BAK_20231001FROMT_PUR_RECEIVEENTRY;--收料通知单SELECT*INTOT_STK_INSTOCKENTRY_BAK_20231001FROMT_STK_INSTOCKENTRY;--采购入库单SELECT*INTOT_PUR_MRAPPENTRY_BAK_20231001FROMT_PUR_MRAPPENTRY;--退料申请单SELECT*INTOT_PUR_MRBENTRY_BAK_20231001FROMT_PUR_MRBENTRY;--采购退料单SELECT*INTOT_STK_INITINSTOCKENTRY_BAK_20231001FROMT_STK_INITINSTOCKENTRY;--期初采购入库单SELECT*INTOT_PUR_INITMRSENTRY_BAK_20231001FROMT_PUR_INITMRSENTRY;--期初采购退料单--1、处理采购订单数据UPDATET2SETT2.FSUBREQBILLNO=T1.WWFBILLNO,T2.FSUBREQBILLSEQ=T1.WWFSEQ,T2.FSUBREQENTRYID=T1.WWFENTRYIDFROMTEMP_SUBFENTRYIDT1INNERJOINT_PUR_POORDERENTRY_RT2ONT1.POFENTRYID=T2.FENTRYIDANDT1.POFENTRYIDISNOTNULLWHERET2.FSUBREQENTRYID=0;--2、处理收料通知单数据UPDATET2SETT2.FSUBREQBILLNO=T1.WWFBILLNO,T2.FSUBREQBILLSEQ=T1.WWFSEQ,T2.FSUBREQENTRYID=T1.WWFENTRYIDFROMTEMP_SUBFENTRYIDT1INNERJOINT_PUR_RECEIVEENTRYT2ONT1.RECFENTRYID=T2.FENTRYIDANDT1.RECFENTRYIDISNOTNULLWHERET2.FSUBREQENTRYID=0;--3、处理采购入库单数据UPDATET2SETT2.FSUBREQBILLNO=T1.WWFBILLNO,T2.FSUBREQBILLSEQ=T1.WWFSEQ,T2.FSUBREQENTRYID=T1.WWFENTRYIDFROMTEMP_SUBFENTRYIDT1INNERJOINT_STK_INSTOCKENTRYT2ONT1.STKFENTRYID=T2.FENTRYIDANDT1.STKFENTRYIDISNOTNULLWHERET2.FSUBREQENTRYID=0;--4、处理退料申请单数据UPDATET2SETT2.FSUBREQBILLNO=T1.WWFBILLNO,T2.FSUBREQBILLSEQ=T1.WWFSEQ,T2.FSUBREQENTRYID=T1.WWFENTRYIDFROMTEMP_SUBFENTRYIDT1INNERJOINT_PUR_MRAPPENTRYT2ONT1.MRAFENTRYID=T2.FENTRYIDANDT1.MRAFENTRYIDISNOTNULLWHERET2.FSUBREQENTRYID=0;--5、处理采购退料单数据UPDATET2SETT2.FSUBREQBILLNO=T1.WWFBILLNO,T2.FSUBREQBILLSEQ=T1.WWFSEQ,T2.FSUBREQENTRYID=T1.WWFENTRYIDFROMTEMP_SUBFENTRYIDT1INNERJOINT_PUR_MRBENTRYT2ONT1.MRBFENTRYID=T2.FENTRYIDANDT1.MRBFENTRYIDISNOTNULLWHERET2.FSUBREQENTRYID=0;--6、处理期初采购入库单数据UPDATET2SETT2.FSUBREQBILLNO=T1.WWFBILLNO,T2.FSUBREQBILLSEQ=T1.WWFSEQ,T2.FSUBREQENTRYID=T1.WWFENTRYIDFROMTEMP_SUBFENTRYIDT1INNERJOINT_STK_INITINSTOCKENTRYT2ONT1.INITSTKFENTRYID=T2.FENTRYIDANDT1.INITSTKFENTRYIDISNOTNULLWHERET2.FSUBREQENTRYID=0;--7、处理期初采购退料单数据UPDATET2SETT2.FSUBREQBILLNO=T1.WWFBILLNO,T2.FSUBREQBILLSEQ=T1.WWFSEQ,T2.FSUBREQENTRYID=T1.WWFENTRYIDFROMTEMP_SUBFENTRYIDT1INNERJOINT_PUR_INITMRSENTRYT2ONT1.INITMRSFENTRYID=T2.FENTRYIDANDT1.INITMRSFENTRYIDISNOTNULLWHERET2.FSUBREQENTRYID=0;
