--==================================================================== -- NAME: PRO_SD_WEEKLY_IMPORT_TRANS -- DESC: ---执行WEEKLY导入正式库事务操作---- -- IN : IN_BATCH_ID -- : -- : -- OUT : -- HISTORY: 2008-06-11 CREATED -- NOTE: -- --================================================================== PROCEDURE PRO_SD_WEEKLY_IMPORT_TRANS(P_BATCH_ID NUMBER) AS X_DEALERCODE VARCHAR2(10); X_STORE_CATEGORY VARCHAR2(20); X_DITDISTINCT VARCHAR2(20); X_PROVINCE VARCHAR2(60); X_CITY VARCHAR2(60); X_DITNAME VARCHAR2(100); X_ADDRESS VARCHAR2(200); X_PHONE VARCHAR2(200); X_CONTACT VARCHAR2(60); X_MANAGER VARCHAR2(60); X_IS_SNX VARCHAR2(2); X_ZIPCODE VARCHAR2(10); X_IS_WAR VARCHAR2(2); X_RIGHTRECORDS NUMBER(10) := 0; X_DEALERS_DUP NUMBER(10) := 0; X_SALES_DUP NUMBER(10) := 0; X_DEALER_TEMP_ID NUMBER(10); X_SALE_TEMP_ID NUMBER(10) := -1; X_SAD_TEMP_ID NUMBER(10) := -1; X_NEW_DEALER_TEMP_ID NUMBER(10); X_NEW_SALE_TEMP_ID NUMBER(10); X_TEMP_NO VARCHAR2(10); -- 取得本批次所有临时数据id CURSOR CUR_DEALER_TEMP( P_BATCH_ID NUMBER) IS SELECT DIT_ID FROM DEALER_IMPORT_TEMP WHERE DIT_SDB_ID= P_BATCH_ID AND DIT_DEALERCODE IS NOT NULL; -- 取出无效经销商销售代表对应关系的历史ID CURSOR CUR_DEALERS_HIS IS SELECT DHI_ID FROM DEALERS_HISTORY WHERE DHI_UPDATE_DATE IS NULL AND DHI_DLR_IS_W= 'W' AND DHI_STATUS='Y' AND DHI_SAD_ID IN (SELECT SAD_ID FROM SALES_DEALERS WHERE SAD_DMO_ID=-1 AND SAD_STATUS = 'N'); BEGIN --循环以前，把所有的DEALER 状态设置为N UPDATE DEALERS SET DLR_STATUS = 'N' WHERE DLR_ID IS NOT NULL; COMMIT; UPDATE SALES_DEALERS SET SAD_STATUS = 'N' WHERE SAD_DMO_ID = -1 AND SAD_ID IS NOT NULL; COMMIT; FOR REC_DATA IN CUR_DEALER_TEMP( P_BATCH_ID) LOOP X_TEMP_NO := REC_DATA.DIT_ID; --搜出临时库中一条数据， --导入到正式表中，顺序为：SALES 和 DEALERS -》 SALESDEALER SELECT DIT_DEALERCODE, DIT_STORE_CATEGORY, DIT_DISTINCT, DIT_PROVINCE, DIT_CITY, DIT_NAME, DIT_ADDRESS, DIT_PHONE, DIT_CONTACT, DIT_MANAGER, DIT_IS_SNX, DIT_ZIPCODE, DIT_IS_WAR INTO X_DEALERCODE, X_STORE_CATEGORY, X_DITDISTINCT, X_PROVINCE, X_CITY, X_DITNAME, X_ADDRESS, X_PHONE, X_CONTACT, X_MANAGER, X_IS_SNX, X_ZIPCODE, X_IS_WAR FROM DEALER_IMPORT_TEMP WHERE DIT_ID = X_TEMP_NO; -- 如果DEALER_CODE 或 SALES_NAME 为空 ，结束操作，不插入任何一条记录 IF X_DEALERCODE IS NULL OR X_MANAGER IS NULL THEN ROLLBACK; END IF; --正式库记录加一 X_RIGHTRECORDS := X_RIGHTRECORDS + 1; BEGIN --首先判断DEALER的CODE是否存在,如果原记录中有相应的DEALER 更新DEALERS 表, SELECT DLR_ID INTO X_DEALER_TEMP_ID FROM DEALERS WHERE DLR_CODE = X_DEALERCODE; EXCEPTION WHEN NO_DATA_FOUND THEN X_DEALER_TEMP_ID := -1; END; IF X_DEALER_TEMP_ID > 0 THEN UPDATE DEALERS SET DLR_PROVINCE = X_PROVINCE, DLR_CITY = X_CITY, DLR_NAME = X_DITNAME, DLR_ADDRESS = X_ADDRESS, DLR_TEL = X_PHONE, DLR_CONTACT = X_CONTACT, DLR_ONWAY_FLAG = X_IS_SNX, DLR_CATEGORY = X_STORE_CATEGORY, DLR_TYPE = X_STORE_CATEGORY, DLR_POSTCODE = X_ZIPCODE, DLR_WAR_FLAG = X_IS_WAR, DLR_UPDATE_DATE = SYSDATE, DLR_STATUS = 'Y', DLR_LBR_BATCH_NUMBER_UPDATE = P_BATCH_ID WHERE DLR_ID = X_DEALER_TEMP_ID; COMMIT; X_DEALERS_DUP := X_DEALERS_DUP + 1; X_NEW_DEALER_TEMP_ID := X_DEALER_TEMP_ID; ELSE INSERT INTO DEALERS (DLR_ID, DLR_CODE, DLR_TYPE, DLR_CREATE_DATE, DLR_PROVINCE, DLR_CITY, DLR_NAME, DLR_ADDRESS, DLR_TEL, DLR_CONTACT, DLR_ONWAY_FLAG, DLR_CATEGORY, DLR_POSTCODE, DLR_WAR_FLAG, DLR_STATUS, DLR_LBR_BATCH_NUMBER_CREATE) VALUES (DLR_ID_S.NEXTVAL, X_DEALERCODE, X_STORE_CATEGORY, SYSDATE, X_PROVINCE, X_CITY, X_DITNAME, X_ADDRESS, X_PHONE, X_CONTACT, X_IS_SNX, X_STORE_CATEGORY, X_ZIPCODE, X_IS_WAR, 'Y', P_BATCH_ID); COMMIT; SELECT DLR_ID_S.CURRVAL INTO X_NEW_DEALER_TEMP_ID FROM DUAL; END IF; --如果SAL_NAME不对应则更新 SALES表，否则插入一条记录 BEGIN SELECT SAL_ID INTO X_SALE_TEMP_ID FROM SALES WHERE SAL_NAME = X_MANAGER AND SAL_CATEGORY = 'W'; EXCEPTION WHEN NO_DATA_FOUND THEN X_SALE_TEMP_ID := -1; END; IF X_SALE_TEMP_ID > 0 THEN UPDATE SALES SET SAL_OFFICE = X_DITDISTINCT, SAL_UPDATE_DATE = SYSDATE WHERE SAL_ID = X_SALE_TEMP_ID; COMMIT; X_SALES_DUP := X_SALES_DUP + 1; X_NEW_SALE_TEMP_ID := X_SALE_TEMP_ID; ELSE INSERT INTO SALES (SAL_ID, SAL_NAME, SAL_OFFICE, SAL_CATEGORY, SAL_CREATE_DATE) VALUES (SAL_ID_S.NEXTVAL, X_MANAGER, X_DITDISTINCT, 'W', SYSDATE); COMMIT; SELECT SAL_ID_S.CURRVAL INTO X_NEW_SALE_TEMP_ID FROM DUAL; END IF; --实现SALES和DEALERS表的关系映射 BEGIN SELECT SAD_ID INTO X_SAD_TEMP_ID FROM SALES_DEALERS WHERE SAD_DLR_ID = X_NEW_DEALER_TEMP_ID AND SAD_SAL_ID = X_NEW_SALE_TEMP_ID AND SAD_DMO_ID = -1; EXCEPTION WHEN NO_DATA_FOUND THEN X_SAD_TEMP_ID := -1; END; IF X_SAD_TEMP_ID > 0 THEN UPDATE SALES_DEALERS SET SAD_STATUS = 'Y' WHERE SAD_ID = X_SAD_TEMP_ID; COMMIT; SELECT DHI_STATUS INTO X_TEMP_NO FROM DEALERS_HISTORY WHERE DHI_SAD_ID = X_SAD_TEMP_ID AND DHI_DLR_IS_W = 'W' AND DHI_UPDATE_DATE IS NULL; --上一次对应关系，历史无效，新增历史对应关系，标记原有历史结束时间 IF X_TEMP_NO = 'N' THEN INSERT INTO DEALERS_HISTORY (DHI_ID, DHI_CREATE_DATE, DHI_STATUS, DHI_DLR_IS_W, DHI_SAD_ID) VALUES (DHI_ID_S.NEXTVAL, SYSDATE, 'Y', 'W', X_SAD_TEMP_ID); UPDATE DEALERS_HISTORY SET DHI_UPDATE_DATE = SYSDATE WHERE DHI_SAD_ID = X_SAD_TEMP_ID AND DHI_STATUS = 'N' AND DHI_DLR_IS_W = 'W' AND DHI_UPDATE_DATE IS NULL; COMMIT; END IF; ELSE INSERT INTO SALES_DEALERS (SAD_ID, SAD_DLR_ID, SAD_SAL_ID, SAD_DMO_ID, SAD_STATUS) VALUES (SAD_ID_S.NEXTVAL, X_NEW_DEALER_TEMP_ID, X_NEW_SALE_TEMP_ID, -1, 'Y'); INSERT INTO DEALERS_HISTORY (DHI_ID, DHI_CREATE_DATE, DHI_STATUS, DHI_DLR_IS_W, DHI_SAD_ID) VALUES (DHI_ID_S.NEXTVAL, SYSDATE, 'Y', 'W', SAD_ID_S.CURRVAL); COMMIT; END IF; END LOOP; FOR REC_DEALER IN CUR_DEALERS_HIS LOOP X_TEMP_NO := REC_DEALER.DHI_ID; SELECT DHI_SAD_ID INTO X_SAD_TEMP_ID FROM DEALERS_HISTORY WHERE DHI_ID = X_TEMP_NO; INSERT INTO DEALERS_HISTORY (DHI_ID, DHI_CREATE_DATE, DHI_STATUS, DHI_DLR_IS_W, DHI_SAD_ID) VALUES (DHI_ID_S.NEXTVAL, SYSDATE, 'N', 'W', X_SAD_TEMP_ID); COMMIT; UPDATE DEALERS_HISTORY SET DHI_UPDATE_DATE = SYSDATE WHERE DHI_ID = X_TEMP_NO; COMMIT; END LOOP; --导入到正式表结束，更新经销商销售代表导入批次表记录 --有：导入状态，正式库记录 --导入正式库结束时间 UPDATE SALES_DEALERS_BATCH SET SDB_LOAD_STATUS = 3, SDB_ERROR_COUNT = SDB_TEMP_COUNT - X_RIGHTRECORDS, SDB_FORMER_COUNT = X_RIGHTRECORDS, SDB_DEALERS_DUPLICATE = X_DEALERS_DUP, SDB_SALES_DUPLICATE = X_SALES_DUP, SDB_DB_END = SYSDATE WHERE SDB_ID = P_BATCH_ID; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; END PRO_SD_WEEKLY_IMPORT_TRANS;
