2026组织迁移记录

组织迁移ccs主要需要修改：
厂商购销关系，客户所属事业部，合同有效期。本年组织切换饲料的厂商购销关系（包括青冈的1230公司）和其它事业部切换有区别。饲料的购销关系，客户所属事业部都是新增。其它事业部是通过我导出的数据，根据id修改部门或则购销关系的成本中心。

sql记录：
先导出以下数据，发给业务进行调整
1 导出需要修改的购销关系
饲料：
```
SELECT
bilh.CUST_CODE ,bilh.CUST_NAME,bilh.SALE_ORG_CODE,bilh.SALE_ORG_NAME ,
  bill.ITEM_CODE ,
  (
  SELECT
    bi.ITEM_DESC
  FROM
    BASE_CENTER.BASE_ITEM bi
  WHERE
    bi.ITEM_CODE = bill.ITEM_CODE AND rownum=1 ) ITEM_DESC,
  bilcc.COST_CENTER_CODE ,
  bilcc.COST_CENTER_NAME ,

(SELECT su.LOGIN_ID FROM SYSTEM_CENTER."SYSTEM_USER" su WHERE su.USER_ID = bill.SALESMAN_ID) 业务员工号,
  bill.SALESMAN_NAME ,
  
  (SELECT su.LOGIN_ID FROM SYSTEM_CENTER."SYSTEM_USER" su WHERE su.USER_ID = bill.MANAGER_ID) 业务经理工号,
  bill.MANAGER_NAME ,
  
  (SELECT su.LOGIN_ID FROM SYSTEM_CENTER."SYSTEM_USER" su WHERE su.USER_ID = bill.GENERAL_MANAGER_ID) 总经理工号,
  bill.GENERAL_MANAGER_NAME ,
          (SELECT
        bdi.ITEM_NAME
        FROM
        BASE_CENTER.BASE_DICT bd
        INNER JOIN BASE_CENTER.BASE_DICT_ITEM bdi ON
        bd.DICT_ID = bdi.DICT_ID
        WHERE
        bd.CODE = 'itemLine'
        AND bdi.ITEM_VALUE = ITEM.SERIES_NAME) PRO_LINE_NAME,
    bilcc.LAYOUT_COST_CENTER_ID,
  bilh.SETS_OF_BOOKS_ID
FROM
  BASE_CENTER.BASE_ITEM_LAYOUT_HEAD bilh
LEFT JOIN BASE_CENTER.BASE_ITEM_LAYOUT_LINE bill ON
  bill.LAYOUT_HEAD_ID = bilh.LAYOUT_HEAD_ID
LEFT JOIN BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER bilcc ON
  bilcc.LAYOUT_LINE_ID = bill.LAYOUT_LINE_ID
left join BASE_CENTER.BASE_ITEM ITEM on bill.ITEM_ID = ITEM.ITEM_ID
WHERE
  bilh.USABLE = 2
  AND bilh.APPLY_STATUS = 4 and (bilh.sets_of_books_id = 5 or bilh.sale_org_code='1230');
```

其它事业部购销关系：

```
SELECT
bilh.CUST_CODE ,bilh.CUST_NAME,bilh.SALE_ORG_CODE,bilh.SALE_ORG_NAME ,
  bill.ITEM_CODE ,
  (
  SELECT
    bi.ITEM_DESC
  FROM
    BASE_CENTER.BASE_ITEM bi
  WHERE
    bi.ITEM_CODE = bill.ITEM_CODE AND rownum=1 ) ITEM_DESC,
  bilcc.COST_CENTER_CODE ,
  bilcc.COST_CENTER_NAME ,
  bilcc.LAYOUT_COST_CENTER_ID,
  bilh.SETS_OF_BOOKS_ID
FROM
  BASE_CENTER.BASE_ITEM_LAYOUT_HEAD bilh
LEFT JOIN BASE_CENTER.BASE_ITEM_LAYOUT_LINE bill ON
  bill.LAYOUT_HEAD_ID = bilh.LAYOUT_HEAD_ID
LEFT JOIN BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER bilcc ON
  bilcc.LAYOUT_LINE_ID = bill.LAYOUT_LINE_ID
WHERE
  bilh.USABLE = 2
  AND bilh.APPLY_STATUS = 4 and bilh.sets_of_books_id = 2;
  
  SELECT
bilh.CUST_CODE ,bilh.CUST_NAME,bilh.SALE_ORG_CODE,bilh.SALE_ORG_NAME ,
  bill.ITEM_CODE ,
  (
  SELECT
    bi.ITEM_DESC
  FROM
    BASE_CENTER.BASE_ITEM bi
  WHERE
    bi.ITEM_CODE = bill.ITEM_CODE AND rownum=1 ) ITEM_DESC,
  bilcc.COST_CENTER_CODE ,
  bilcc.COST_CENTER_NAME ,
  bilcc.LAYOUT_COST_CENTER_ID,
  bilh.SETS_OF_BOOKS_ID
FROM
  BASE_CENTER.BASE_ITEM_LAYOUT_HEAD bilh
LEFT JOIN BASE_CENTER.BASE_ITEM_LAYOUT_LINE bill ON
  bill.LAYOUT_HEAD_ID = bilh.LAYOUT_HEAD_ID
LEFT JOIN BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER bilcc ON
  bilcc.LAYOUT_LINE_ID = bill.LAYOUT_LINE_ID
WHERE
  bilh.USABLE = 2
  AND bilh.APPLY_STATUS = 4 and bilh.sets_of_books_id = 3;
  
  SELECT
bilh.CUST_CODE ,bilh.CUST_NAME,bilh.SALE_ORG_CODE,bilh.SALE_ORG_NAME ,
  bill.ITEM_CODE ,
  (
  SELECT
    bi.ITEM_DESC
  FROM
    BASE_CENTER.BASE_ITEM bi
  WHERE
    bi.ITEM_CODE = bill.ITEM_CODE AND rownum=1 ) ITEM_DESC,
  bilcc.COST_CENTER_CODE ,
  bilcc.COST_CENTER_NAME ,
  bilcc.LAYOUT_COST_CENTER_ID,
  bilh.SETS_OF_BOOKS_ID
FROM
  BASE_CENTER.BASE_ITEM_LAYOUT_HEAD bilh
LEFT JOIN BASE_CENTER.BASE_ITEM_LAYOUT_LINE bill ON
  bill.LAYOUT_HEAD_ID = bilh.LAYOUT_HEAD_ID
LEFT JOIN BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER bilcc ON
  bilcc.LAYOUT_LINE_ID = bill.LAYOUT_LINE_ID
WHERE
  bilh.USABLE = 2
  AND bilh.APPLY_STATUS = 4 and bilh.sets_of_books_id = 4;
  
  SELECT
bilh.CUST_CODE ,bilh.CUST_NAME,bilh.SALE_ORG_CODE,bilh.SALE_ORG_NAME ,
  bill.ITEM_CODE ,
  (
  SELECT
    bi.ITEM_DESC
  FROM
    BASE_CENTER.BASE_ITEM bi
  WHERE
    bi.ITEM_CODE = bill.ITEM_CODE AND rownum=1 ) ITEM_DESC,
  bilcc.COST_CENTER_CODE ,
  bilcc.COST_CENTER_NAME ,
  bilcc.LAYOUT_COST_CENTER_ID,
  bilh.SETS_OF_BOOKS_ID
FROM
  BASE_CENTER.BASE_ITEM_LAYOUT_HEAD bilh
LEFT JOIN BASE_CENTER.BASE_ITEM_LAYOUT_LINE bill ON
  bill.LAYOUT_HEAD_ID = bilh.LAYOUT_HEAD_ID
LEFT JOIN BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER bilcc ON
  bilcc.LAYOUT_LINE_ID = bill.LAYOUT_LINE_ID
WHERE
  bilh.USABLE = 2
  AND bilh.APPLY_STATUS = 4 and bilh.sets_of_books_id = 6;
  
  SELECT
bilh.CUST_CODE ,bilh.CUST_NAME,bilh.SALE_ORG_CODE,bilh.SALE_ORG_NAME ,
  bill.ITEM_CODE ,
  (
  SELECT
    bi.ITEM_DESC
  FROM
    BASE_CENTER.BASE_ITEM bi
  WHERE
    bi.ITEM_CODE = bill.ITEM_CODE AND rownum=1 ) ITEM_DESC,
  bilcc.COST_CENTER_CODE ,
  bilcc.COST_CENTER_NAME ,
  bilcc.LAYOUT_COST_CENTER_ID,
  bilh.SETS_OF_BOOKS_ID
FROM
  BASE_CENTER.BASE_ITEM_LAYOUT_HEAD bilh
LEFT JOIN BASE_CENTER.BASE_ITEM_LAYOUT_LINE bill ON
  bill.LAYOUT_HEAD_ID = bilh.LAYOUT_HEAD_ID
LEFT JOIN BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER bilcc ON
  bilcc.LAYOUT_LINE_ID = bill.LAYOUT_LINE_ID
WHERE
  bilh.USABLE = 2
  AND bilh.APPLY_STATUS = 4 and bilh.sets_of_books_id = 7;
  
```

所属事业部导出：

```

SELECT
  aa.AG_CODE ,aa.AG_NAME ,so.CODE ,so.NAME_PATH ,aasm.AG_SUP_MAP_ID
FROM
  ARCHIVES_CENTER.ARCHIVES_AGENCY aa
INNER JOIN ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP aasm ON
  aasm.AG_CODE = aa.AG_CODE
INNER JOIN SYSTEM_CENTER.SYSTEM_ORG so ON
  so.ORG_ID = aasm.ORG_ID
  WHERE aasm.SETS_OF_BOOKS_ID =2 AND aa.USABLE =2;
  
  
  SELECT
  aa.AG_CODE ,aa.AG_NAME ,so.CODE ,so.NAME_PATH ,aasm.AG_SUP_MAP_ID
FROM
  ARCHIVES_CENTER.ARCHIVES_AGENCY aa
INNER JOIN ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP aasm ON
  aasm.AG_CODE = aa.AG_CODE
INNER JOIN SYSTEM_CENTER.SYSTEM_ORG so ON
  so.ORG_ID = aasm.ORG_ID
  WHERE aasm.SETS_OF_BOOKS_ID =3 AND aa.USABLE =2;
  
  
  SELECT
  aa.AG_CODE ,aa.AG_NAME ,so.CODE ,so.NAME_PATH ,aasm.AG_SUP_MAP_ID
FROM
  ARCHIVES_CENTER.ARCHIVES_AGENCY aa
INNER JOIN ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP aasm ON
  aasm.AG_CODE = aa.AG_CODE
INNER JOIN SYSTEM_CENTER.SYSTEM_ORG so ON
  so.ORG_ID = aasm.ORG_ID
  WHERE aasm.SETS_OF_BOOKS_ID =4 AND aa.USABLE =2;
  
  
  SELECT
  aa.AG_CODE ,aa.AG_NAME ,so.CODE ,so.NAME_PATH ,aasm.AG_SUP_MAP_ID
FROM
  ARCHIVES_CENTER.ARCHIVES_AGENCY aa
INNER JOIN ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP aasm ON
  aasm.AG_CODE = aa.AG_CODE
INNER JOIN SYSTEM_CENTER.SYSTEM_ORG so ON
  so.ORG_ID = aasm.ORG_ID
  WHERE aasm.SETS_OF_BOOKS_ID =5 AND aa.USABLE =2;
  
  
  SELECT
  aa.AG_CODE ,aa.AG_NAME ,so.CODE ,so.NAME_PATH ,aasm.AG_SUP_MAP_ID
FROM
  ARCHIVES_CENTER.ARCHIVES_AGENCY aa
INNER JOIN ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP aasm ON
  aasm.AG_CODE = aa.AG_CODE
INNER JOIN SYSTEM_CENTER.SYSTEM_ORG so ON
  so.ORG_ID = aasm.ORG_ID
  WHERE aasm.SETS_OF_BOOKS_ID =6 AND aa.USABLE =2;
  
  
  SELECT
  aa.AG_CODE ,aa.AG_NAME ,so.CODE ,so.NAME_PATH ,aasm.AG_SUP_MAP_ID
FROM
  ARCHIVES_CENTER.ARCHIVES_AGENCY aa
INNER JOIN ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP aasm ON
  aasm.AG_CODE = aa.AG_CODE
INNER JOIN SYSTEM_CENTER.SYSTEM_ORG so ON
  so.ORG_ID = aasm.ORG_ID
  WHERE aasm.SETS_OF_BOOKS_ID =7 AND aa.USABLE =2;
  
```
更新购销关系

```
--创建导入表 成本中心id添加索引 更新的时候快些
create table BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP260101_3
(
  LAYOUT_COST_CENTER_ID VARCHAR2(500),
COST_CENTER_CODE VARCHAR2(500),
COST_CENTER_NAME VARCHAR2(500)
);

select * from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP260101_3 T where T.COST_CENTER_CODE is null;

--备份需要更新的成本中心
create table BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_back260101_3_2 as
select * from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER bilcc
where exists (select 1 from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP260101_3 T where T.LAYOUT_COST_CENTER_ID = bilcc.layout_cost_center_id);

--恢复成本中心
select count(1) from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_back260101_3_2

select count(1) from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_back260101_3_2;

39379条，25129
select count(1) from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP260101_3 bilcc where bilcc."Impl_Type" =1

select count(1) from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP260101_3 T group by T.LAYOUT_COST_CENTER_ID having count(T.LAYOUT_COST_CENTER_ID) > 1

update BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP260101_3 T set T."Impl_Type" = 1;

update BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP260101_3 T set T."Impl_Type" = 2 where exists (select 1 from BASE_CENTER."test" t WHERE t."namea" = T.LAYOUT_COST_CENTER_ID)

--查询重复数据
-- 统计重复分组的数量（按3个字段分组）
SELECT 
    LAYOUT_COST_CENTER_ID,
    COST_CENTER_CODE,
    COST_CENTER_NAME,
    COUNT(*) AS repeat_count  -- 重复条数
FROM BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP260101_3
GROUP BY LAYOUT_COST_CENTER_ID, COST_CENTER_CODE, COST_CENTER_NAME
HAVING COUNT(*) > 1;  -- 仅显示重复的分组

--删除重复数据 没执行过 执行前先测试
-- 步骤2：删除重复数据（保留每组第一条）
DELETE FROM BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP260101_3 t1
WHERE EXISTS (
    SELECT 1
    FROM (
        -- 按3个字段分组，生成行号（rn=1保留，rn>1删除）
        SELECT 
            rid,
            ROW_NUMBER() OVER (
                PARTITION BY LAYOUT_COST_CENTER_ID, COST_CENTER_CODE, COST_CENTER_NAME
                ORDER BY 1  -- 按任意顺序保留第一条，如需按特定规则（如最新），替换为具体字段（如CREATE_DATE DESC）
            ) AS rn
        FROM (
            -- 取行物理地址（ROWID），精准定位每条记录
            SELECT 
                ROWID AS rid,
                LAYOUT_COST_CENTER_ID,
                COST_CENTER_CODE,
                COST_CENTER_NAME
            FROM BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP260101_3
        ) t
    ) t2
    WHERE t2.rn > 1  -- 仅删除行号>1的重复记录
      AND t1.ROWID = t2.rid;

--批量更新厂商购销关系成本中心
update BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER bilcc 
set bilcc.cost_center_code = (select T.COST_CENTER_CODE from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP260101_3 T where T.LAYOUT_COST_CENTER_ID = bilcc.layout_cost_center_id and rownum=1)
,bilcc.cost_center_name = (select T.COST_CENTER_NAME from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP260101_3 T where T.LAYOUT_COST_CENTER_ID = bilcc.layout_cost_center_id and rownum=1)
,bilcc.update_data_remarks = '组织切换更新成本中心260101_3'
where exists (select 1 from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP260101_3 T where T.LAYOUT_COST_CENTER_ID = bilcc.layout_cost_center_id
--and T."Impl_Type" = 1
);

--批量恢复厂商购销关系成本中心
update BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER bilcc 
set bilcc.cost_center_code = (select T.COST_CENTER_CODE from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_back260101_3_2 T where T.LAYOUT_COST_CENTER_ID = bilcc.layout_cost_center_id and rownum=1)
,bilcc.cost_center_name = (select T.COST_CENTER_NAME from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_back260101_3_2 T where T.LAYOUT_COST_CENTER_ID = bilcc.layout_cost_center_id and rownum=1)
,bilcc.update_data_remarks = '组织切换恢复成本中心260101_3'
where exists (select 1 from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_back260101_3_2 T where T.LAYOUT_COST_CENTER_ID = bilcc.layout_cost_center_id
--and T."Impl_Type" = 1
);

select * from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER bilcc where bilcc.update_data_remarks = '组织切换更新成本中心260101_3'

update BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER bilcc set bilcc.cost_center_code = (select T.COST_CENTER_CODE from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP260101_3 T where T.LAYOUT_COST_CENTER_ID = bilcc.layout_cost_center_id)
,bilcc.cost_center_name = (select T.COST_CENTER_NAME from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP260101_3 T where T.LAYOUT_COST_CENTER_ID = bilcc.layout_cost_center_id)
,bilcc.update_data_remarks = '组织切换更新成本中心260101_3-2'
where exists (select 1 from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP260101_3 T where T.LAYOUT_COST_CENTER_ID = bilcc.layout_cost_center_id
and T."Impl_Type" = 2
);

-- 饲料需要更新业务员 直接用这个表删除历史数据就可以了 有索引
create table BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP_SL
(
  LAYOUT_COST_CENTER_ID VARCHAR2(500),
    LAYOUT_LINE_ID VARCHAR2(500),
COST_CENTER_CODE VARCHAR2(500),
COST_CENTER_NAME VARCHAR2(500),

SALESMAN_ID VARCHAR2(500),
SALESMAN_NAME VARCHAR2(500),
SALESMAN_NAME_LOGIN VARCHAR2(500),
MANAGER_ID VARCHAR2(500),
MANAGER_NAME VARCHAR2(500),
MANAGER_NAME_LOGIN VARCHAR2(500),
GENERAL_MANAGER_ID VARCHAR2(500),
GENERAL_MANAGER_NAME VARCHAR2(500),
GENERAL_MANAGER_LOGIN VARCHAR2(500)
);

select * from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP_SL T where T.General_Manager_Id is null;

create table BASE_CENTER.BASE_ITEM_LAYOUT_LINE_back260101_3 as
select * from BASE_CENTER.BASE_ITEM_LAYOUT_LINE bilcc 
where exists (select 1 from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP_SL T where T.LAYOUT_LINE_ID = bilcc.LAYOUT_LINE_ID
);
64581

select * from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP_SL T where T.Salesman_Id is null

--更新业务id
update BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP_SL T set T.Salesman_Id = (select su.user_id from SYSTEM_CENTER."SYSTEM_USER" su where su.login_id = T.Salesman_Name_Login)

--更新业务经理id
update BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP_SL T set T.Manager_Id = (select su.user_id from SYSTEM_CENTER."SYSTEM_USER" su where su.login_id = T.Manager_Name_Login)
--更新总经理id
update BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP_SL T set T.General_Manager_Id = (select su.user_id from SYSTEM_CENTER."SYSTEM_USER" su where su.login_id = T.General_Manager_Login)

--备份需要更新的业务员
create table BASE_CENTER.BASE_ITEM_LAYOUT_LINE_back260101_3 as
select * from BASE_CENTER.BASE_ITEM_LAYOUT_LINE

--批量更新厂商购销关系成本中心
update BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER bilcc set 
bilcc.cost_center_code = (select T.COST_CENTER_CODE from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP_SL T where T.LAYOUT_COST_CENTER_ID = bilcc.layout_cost_center_id and rownum=1)
,bilcc.cost_center_name = (select T.COST_CENTER_NAME from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP_SL T where T.LAYOUT_COST_CENTER_ID = bilcc.layout_cost_center_id and rownum=1)
,bilcc.update_data_remarks = '组织切换更新成本中心260101_3'
where exists (select 1 from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP_SL T where T.LAYOUT_COST_CENTER_ID = bilcc.layout_cost_center_id
);

select * from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER bilcc where bilcc.update_data_remarks = '组织切换更新成本中心241231_sl'

-- 批量更新业务员
update BASE_CENTER.BASE_ITEM_LAYOUT_LINE bill set 
bill.salesman_id = (select T.Salesman_Id from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP_SL T where T.LAYOUT_LINE_ID = bill.LAYOUT_LINE_ID and rownum=1)
,bill.Salesman_Name = (select T.Salesman_Name from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP_SL T where T.LAYOUT_LINE_ID = bill.LAYOUT_LINE_ID and rownum=1)

,bill.manager_id = (select T.manager_id from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP_SL T where T.LAYOUT_LINE_ID = bill.LAYOUT_LINE_ID and rownum=1)
,bill.Manager_Name = (select T.Manager_Name from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP_SL T where T.LAYOUT_LINE_ID = bill.LAYOUT_LINE_ID and rownum=1)

,bill.general_manager_id = (select T.general_manager_id from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP_SL T where T.LAYOUT_LINE_ID = bill.LAYOUT_LINE_ID and rownum=1)
,bill.General_Manager_Name = (select T.General_Manager_Name from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP_SL T where T.LAYOUT_LINE_ID = bill.LAYOUT_LINE_ID and rownum=1)
,bill.ATTRIBUTE3 = '组织切换更新成本中心260101_3_sl'
where exists (select 1 from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP_SL T where T.LAYOUT_LINE_ID = bill.LAYOUT_LINE_ID
);

select * from BASE_CENTER.BASE_ITEM_LAYOUT_LINE bill where bill.ATTRIBUTE3 = '组织切换更新成本中心260101_3_sl'

-- 更新成本中心备份表备注
  UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_BACK bilccb SET bilccb.UPDATE_DATA_REMARKS =bilccb.UPDATE_DATA_REMARKS || '删除恢复'
  WHERE
  EXISTS (
  SELECT
    1
  FROM
    BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP251231 bilcci
  WHERE
    bilcci.LAYOUT_COST_CENTER_ID = BILCCB.LAYOUT_COST_CENTER_ID);
    
    select * from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_BACK bilccb where bilccb.UPDATE_DATA_REMARKS like '%删除恢复%'

--重备份表恢复数据

INSERT INTO BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER 
  SELECT 
  t.LAYOUT_COST_CENTER_ID,
t.LAYOUT_HEAD_ID,
t.LAYOUT_LINE_ID,
t.TENANT_ID,
t.ITEM_ID,
t.ITEM_CODE,
t.COST_CENTER_CODE,
t.COST_CENTER_NAME,
t.NOTE,
t.USABLE,
t.VERSION,
t.CREATED_BY,
t.CREATION_DATE,
t.LAST_UPDATED_BY,
t.LAST_UPDATE_DATE,
t.ATTRIBUTE1,
t.ATTRIBUTE2,
t.ATTRIBUTE3,
t.ATTRIBUTE4,
t.ATTRIBUTE5,
'删除恢复260101' UPDATE_DATA_REMARKS,
'' OLD_COST_CENTER_CODE,
'' OLD_COST_CENTER_NAME
FROM 
  (SELECT
  bilccb.*

FROM
  BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_BACK bilccb
WHERE
  EXISTS (
  SELECT
    1
  FROM
    BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP251231 bilcci
  WHERE
    bilcci.LAYOUT_COST_CENTER_ID = BILCCB.LAYOUT_COST_CENTER_ID)) t

```

更新所属事业部部门

```

--创建所属事业部导入模板
CREATE TABLE ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP_tmp260101_5 
(
  AG_SUP_MAP_ID VARCHAR2(500),
ORG_ID VARCHAR2(500),
ORG_CODE VARCHAR2(500)
);

select * from ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP_tmp260101_5 T where T.Ag_Sup_Map_Id = 7868785

-- 备份所属事业部
CREATE TABLE ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP_back260101_5 AS
SELECT * FROM ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP aasm 
WHERE EXISTS (SELECT
    1
  FROM
    ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP_tmp260101_5 T
  WHERE
    T.AG_SUP_MAP_ID = aasm.AG_SUP_MAP_ID );
    
    select * from ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP_back260101_5

--更新部门id
update ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP_tmp260101_5 
T set T.ORG_ID = (SELECT so.org_id FROM SYSTEM_CENTER.SYSTEM_ORG so where so.code = T.ORG_CODE)
where T.org_id is null;

select * from ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP_tmp260101_5 T where T.Org_Id is null 
  
  --更新部门信息
UPDATE
  ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP aasm
SET
  aasm.ORG_ID = (
  SELECT
    T.ORG_ID
  FROM
    ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP_tmp260101_5 T
  WHERE
    T.AG_SUP_MAP_ID = aasm.AG_SUP_MAP_ID and rownum=1),
    aasm.ATTRIBUTE1 ='260101_5更新部门id-'||aasm.ORG_ID 
  
    WHERE EXISTS (  SELECT
    1
  FROM
    ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP_tmp260101_5 T
  WHERE
    T.AG_SUP_MAP_ID = aasm.AG_SUP_MAP_ID );
    
      --恢复更新部门信息
UPDATE
  ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP aasm
SET
  aasm.ORG_ID = (
  SELECT
    T.ORG_ID
  FROM
    ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP_back260101_5 T
  WHERE
    T.AG_SUP_MAP_ID = aasm.AG_SUP_MAP_ID and rownum=1),
    aasm.ATTRIBUTE1 ='260101_5恢复更新部门id-'||aasm.ORG_ID 
  
    WHERE EXISTS (  SELECT
    1
  FROM
    ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP_back260101_5 T
  WHERE
    T.AG_SUP_MAP_ID = aasm.AG_SUP_MAP_ID );
  
  
  select * from ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP aasm where aasm.attribute1 like '%260101_5更新部门id%'
  
  -- 修改推送状态
  UPDATE ARCHIVES_CENTER.ARCHIVES_AGENCY aa SET aa.SYNC_STATUS =1
  WHERE EXISTS (SELECT 1 FROM ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP aasm WHERE aasm.AG_ID = aa.AG_ID AND aasm.ATTRIBUTE1 LIKE '%260101_5更新部门id%')

```

新增购销关系，并且将老的购销关系作废

```
用户角色导入步骤：
1.根据表格内容创建临时表。
2.将表格数据导入到临时表。
3.执行下面除建表语句外的SQL脚本。
4.将导入错误的数据导出给业务重新填充收集对应的信息。

--备注：
圣迪乐账套：2
食品账套：3
猪业账套：4
食品账套：5
--单据号编码：ITEM_LAYOUT_CODE

---备份上一次导入的数据：命名规则，最后在导入的临时表上加上事业部及数据量描述：
如：导入1000条，实际表头有效300条：则表头备份为：xxx_BAK20250804_5_300,详情：xxx_BAK20250804_5_1000
create table BASE_CENTER.BASE_ITEM_LAYOUT_HEAD_TEMP_BAK250804 as select * from BASE_CENTER.BASE_ITEM_LAYOUT_HEAD_TEMP; 
create table BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP_BAK250804 as select * from BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP;

----------------拆分表头
--创建表头临时表：
--创建临时头表
DROP TABLE BASE_CENTER.BASE_ITEM_LAYOUT_HEAD_TEMP PURGE;
CREATE TABLE BASE_CENTER.BASE_ITEM_LAYOUT_HEAD_TEMP (
TEMP_ID NUMBER(38,0),
SETS_OF_BOOKS_ID NUMBER(38,0),
SALE_ORG_CODE VARCHAR2(1280),
CUST_CODE VARCHAR2(1280),
LAYOUT_NO VARCHAR2(1280)
 );
COMMENT ON TABLE BASE_CENTER.BASE_ITEM_LAYOUT_HEAD_TEMP IS '厂商购销关系-临时头表';
COMMENT ON COLUMN BASE_CENTER.BASE_ITEM_LAYOUT_HEAD_TEMP.TEMP_ID IS '临时表主键-取的excel的列ID';
COMMENT ON COLUMN BASE_CENTER.BASE_ITEM_LAYOUT_HEAD_TEMP.SALE_ORG_CODE IS '销售组织代码';
COMMENT ON COLUMN BASE_CENTER.BASE_ITEM_LAYOUT_HEAD_TEMP.CUST_CODE IS '客户编码';
COMMENT ON COLUMN BASE_CENTER.BASE_ITEM_LAYOUT_HEAD_TEMP.SETS_OF_BOOKS_ID IS '事业部账套';
COMMENT ON COLUMN BASE_CENTER.BASE_ITEM_LAYOUT_HEAD_TEMP.LAYOUT_NO IS '单据号';

-----------------------

--创建临时明细表 excel数据导入到这个表
DROP TABLE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP PURGE;
CREATE TABLE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP (
TEMP_ID NUMBER(38,0),
SETS_OF_BOOKS_NAME VARCHAR2(1280),
SALE_ORG_CODE VARCHAR2(1280),
SALE_ORG_NAME VARCHAR2(1280),
CUST_ID NUMBER(38,0),
CUST_CODE VARCHAR2(1280),
CUST_NAME VARCHAR2(1280),
ITEM_ID NUMBER(38,0),
ITEM_CODE VARCHAR2(1280),
ITEM_NAME VARCHAR2(1280),
COST_CENTER_CODE VARCHAR2(1280),
COST_CENTER_NAME VARCHAR2(1280),
UOM_ID NUMBER(38,0),
UOM_CODE VARCHAR2(1280),
UOM_NAME VARCHAR2(1280),
PAYMENT_TERMS VARCHAR2(1280),
SETS_OF_BOOKS_ID NUMBER(38,0),
BSETS_OF_BOOKS_ID NUMBER(38,0),
LAYOUT_NO VARCHAR2(1280),
SALESMAN_CODE VARCHAR2(1280),
MANAGER_CODE VARCHAR2(1280),
GENERAL_MANAGER_CODE VARCHAR2(1280),
SALESMAN_ID VARCHAR2(1280),
MANAGER_ID VARCHAR2(1280),
GENERAL_MANAGER_ID VARCHAR2(1280),
SALESMAN_NAME VARCHAR2(1280),
MANAGER_NAME VARCHAR2(1280),
GENERAL_MANAGER_NAME VARCHAR2(1280),
LAYOUT_HEAD_ID NUMBER(38,0),
LAYOUT_LINE_ID NUMBER(38,0),
STATUS VARCHAR2(128),
ERR_MSG VARCHAR2(128)
 );
COMMENT ON TABLE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP IS '厂商购销关系-临时表';
COMMENT ON COLUMN BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP.TEMP_ID IS '临时表主键-取的excel的列ID';
COMMENT ON COLUMN BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP.SETS_OF_BOOKS_NAME IS '用户所属的事业部名称';
COMMENT ON COLUMN BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP.SALE_ORG_CODE IS '销售组织代码';
COMMENT ON COLUMN BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP.SALE_ORG_NAME IS '销售组织代码名称';
COMMENT ON COLUMN BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP.CUST_ID IS '客户ID';
COMMENT ON COLUMN BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP.CUST_CODE IS '客户编码';
COMMENT ON COLUMN BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP.CUST_NAME IS '客户名称';
COMMENT ON COLUMN BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP.ITEM_ID IS '商品ID';
COMMENT ON COLUMN BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP.ITEM_CODE IS '商品编码';
COMMENT ON COLUMN BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP.ITEM_NAME IS '商品名称';
COMMENT ON COLUMN BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP.COST_CENTER_CODE IS '成本中心编码';
COMMENT ON COLUMN BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP.COST_CENTER_NAME IS '成本中心名称';
COMMENT ON COLUMN BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP.UOM_ID IS '单位ID';
COMMENT ON COLUMN BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP.UOM_CODE IS '单位编码';
COMMENT ON COLUMN BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP.UOM_NAME IS '单位名称';
COMMENT ON COLUMN BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP.PAYMENT_TERMS IS '付款条件';
COMMENT ON COLUMN BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP.SETS_OF_BOOKS_ID IS '事业部账套';
COMMENT ON COLUMN BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP.BSETS_OF_BOOKS_ID IS '客户的账套';

COMMENT ON COLUMN BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP.LAYOUT_NO IS '单据号';
COMMENT ON COLUMN BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP.LAYOUT_HEAD_ID IS '表头ID';
COMMENT ON COLUMN BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP.LAYOUT_LINE_ID IS '行ID';
COMMENT ON COLUMN BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP.STATUS IS '数据检查的状态:SUCCESS-成功;ERROR-检查存在错误';
COMMENT ON COLUMN BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP.ERR_MSG IS '错误信息';

delete from BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T where T.Cust_Code is null;
--更新导入的事业部账套
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.SETS_OF_BOOKS_ID=6 WHERE T.SETS_OF_BOOKS_NAME LIKE '新兴%';
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.SETS_OF_BOOKS_ID=5 WHERE T.SETS_OF_BOOKS_NAME LIKE '饲料%';
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.SETS_OF_BOOKS_ID=4 WHERE T.SETS_OF_BOOKS_NAME LIKE '猪业事业部%';
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.SETS_OF_BOOKS_ID=3 WHERE T.SETS_OF_BOOKS_NAME LIKE '食品事业部%';
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.SETS_OF_BOOKS_ID=2 WHERE T.SETS_OF_BOOKS_NAME LIKE '圣迪乐%';
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.SETS_OF_BOOKS_ID=7 WHERE T.SETS_OF_BOOKS_NAME LIKE '青冈%';

select * from BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T where T.Sets_Of_Books_Id is  null ;

-- 收集的excel没有填写 更新客户名称，销售组织名称，成本中心名称 需要手动补充
update BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T set T.Cust_Name = (SELECT aa.ag_name FROM ARCHIVES_CENTER.ARCHIVES_AGENCY aa WHERE aa.ag_code = T.Cust_Code)

update BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T set T.Sale_Org_Name = (SELECT bpc.sale_org_name FROM BASE_CENTER.BASE_PLANT_CONFIG bpc  where bpc.sale_org_code = T.Sale_Org_Code and rownum = 1)

select T.Cost_Center_Code from BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T group by T.Cost_Center_Code

update BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T set T.Cost_Center_Name = (
SELECT COST_CENTER_NAME  FROM BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER bilcc WHERE COST_CENTER_CODE IN ('0230002040',
'0230002035',
'0230002034',
'0230002037',
'0230002036',
'0230002003',
'0230002039',
'0230002038',
'0230002041',
'0230002042') and bilcc.cost_center_code = T.Cost_Center_Code and rownum=1)

select T.Cust_Code,T.Sale_Org_Code from BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T group by T.Cust_Code,T.Sale_Org_Code

-- 去空格
update BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T set T.Cust_Code = REPLACE(T.Cust_Code, ' ', '');

---查询唯一的购销关系信息，导出来填充单据号，然后导入BASE_ITEM_LAYOUT_HEAD_TEMP表
SELECT  
DISTINCT 
T.SETS_OF_BOOKS_ID, 
T.CUST_CODE,
T.SALE_ORG_CODE
FROM BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T

---前置操作，在excel表格中增加单据号列，
圣迪乐账套：2
食品账套：3
猪业账套：4
食品账套：5
--1.
SELECT * FROM  BASE_CENTER.BASE_CODE_RULE WHERE RULE_CODE ='ITEM_LAYOUT_CODE' AND SETS_OF_BOOKS_ID =5;
如果没有，查询-1并创建对应账套的编码。
INSERT INTO BASE_CENTER.BASE_CODE_RULE 
SELECT BASE_CENTER.SEQ_BASE_CODE_RULE.nextval RULE_ID ,
TT.TENANT_ID TENANT_ID,
4 SETS_OF_BOOKS_ID, 
TT.RULE_CODE RULE_CODE,
TT.FIRST_PREFIX_TYPE FIRST_PREFIX_TYPE,
TT.SECOND_PREFIX_TYPE SECOND_PREFIX_TYPE,
TT.FIRST_PREFIX FIRST_PREFIX,
TT.SECOND_PREFIX SECOND_PREFIX,
TT.FIRST_SUFFIX_TYPE FIRST_SUFFIX_TYPE,
TT.SECOND_SUFFIX_TYPE SECOND_SUFFIX_TYPE,
TT.FIRST_SUFFIX FIRST_SUFFIX,
TT.SECOND_SUFFIX SECOND_SUFFIX,
TT.SN_LENGTH SN_LENGTH,
TT.FILL_ZERO_FLAG FILL_ZERO_FLAG,
TT.CYCLE_FLAG CYCLE_FLAG,
TT.SN_MAX_VALUE SN_MAX_VALUE,
TT.SN_MIN_VALUE SN_MIN_VALUE,
TT.CYCLE_TYPE CYCLE_TYPE,
TT.REMARK REMARK,
TT.EFFECTIVE_DATE EFFECTIVE_DATE,
TT.EXPIRY_DATE EXPIRY_DATE,
TT.RULE_TYPE RULE_TYPE,
0 VERSION,
TT.CREATED_BY CREATED_BY,
TT.CREATION_DATE CREATION_DATE,
TT.LAST_UPDATED_BY LAST_UPDATED_BY,
TT.LAST_UPDATE_DATE LAST_UPDATE_DATE,
TT.UNAVAILABLE_FLAG UNAVAILABLE_FLAG
FROM (
SELECT  
TT.TENANT_ID TENANT_ID,
TT.SETS_OF_BOOKS_ID SETS_OF_BOOKS_ID, 
TT.RULE_CODE RULE_CODE,
TT.FIRST_PREFIX_TYPE FIRST_PREFIX_TYPE,
TT.SECOND_PREFIX_TYPE SECOND_PREFIX_TYPE,
TT.FIRST_PREFIX FIRST_PREFIX,
TT.SECOND_PREFIX SECOND_PREFIX,
TT.FIRST_SUFFIX_TYPE FIRST_SUFFIX_TYPE,
TT.SECOND_SUFFIX_TYPE SECOND_SUFFIX_TYPE,
TT.FIRST_SUFFIX FIRST_SUFFIX,
TT.SECOND_SUFFIX SECOND_SUFFIX,
TT.SN_LENGTH SN_LENGTH,
TT.FILL_ZERO_FLAG FILL_ZERO_FLAG,
TT.CYCLE_FLAG CYCLE_FLAG,
TT.SN_MAX_VALUE SN_MAX_VALUE,
TT.SN_MIN_VALUE SN_MIN_VALUE,
TT.CYCLE_TYPE CYCLE_TYPE,
TT.REMARK REMARK,
TT.EFFECTIVE_DATE EFFECTIVE_DATE,
TT.EXPIRY_DATE EXPIRY_DATE,
TT.RULE_TYPE RULE_TYPE,
TT.CREATED_BY CREATED_BY,
TT.CREATION_DATE CREATION_DATE,
TT.LAST_UPDATED_BY LAST_UPDATED_BY,
TT.LAST_UPDATE_DATE LAST_UPDATE_DATE,
TT.UNAVAILABLE_FLAG UNAVAILABLE_FLAG
FROM BASE_CENTER.BASE_CODE_RULE TT WHERE TT.SETS_OF_BOOKS_ID=-1 AND TT.RULE_CODE='ITEM_LAYOUT_CODE' AND NOT EXISTS
(SELECT 1 FROM BASE_CENTER.BASE_CODE_RULE LH WHERE LH.SETS_OF_BOOKS_ID=4 AND LH.RULE_CODE=TT.RULE_CODE)
) TT;
1.2 然后将字段FIRST_PREFIX的前4个0换成对应事业部的账套码
--2.然后查询对应的主键，如：
SELECT * FROM  BASE_CENTER.BASE_CODE_RULE WHERE RULE_CODE ='ITEM_LAYOUT_CODE' AND SETS_OF_BOOKS_ID =3;
--3.查询对应的序列： 修改 searal_number = 实际条数+数据库已有的（searal_number），如果last_update_date不是当天，直接从101，（比如100条）开始累计算，把时间更新成当天
SELECT * FROM  BASE_CENTER.BASE_CODE_SEQ  WHERE RULE_ID ='19856' for update;

SELECT * FROM  BASE_CENTER.BASE_CODE_SEQ  WHERE RULE_ID ='14223'

SELECT * FROM  BASE_CENTER.BASE_CODE_SEQ  WHERE SEQ_ID =17608;

update BASE_CENTER.BASE_CODE_SEQ set SERIAL_NUMBER =SERIAL_NUMBER+1480 ,last_update_date = sysdate where SEQ_ID =17608;

--ABCHLC2508040003
select * from BASE_CENTER.BASE_ITEM_LAYOUT_HEAD T where T.Sets_Of_Books_Id=3 order by T.Creation_Date desc;

没有的话，创建对应的序列：查询的是-1的序列，主键id=1112
INSERT INTO BASE_CENTER.BASE_CODE_SEQ 
SELECT BASE_CENTER.SEQ_BASE_CODE_SEQ.nextval SEQ_ID ,
TT.TENANT_ID TENANT_ID,
20100 RULE_ID, 
TT.SEQ_NAME SEQ_NAME,
1 SERIAL_NUMBER,
TT.CYCLE_INDEX CYCLE_INDEX,
1 VERSION,
TT.CREATED_BY CREATED_BY,
TT.CREATION_DATE CREATION_DATE,
TT.LAST_UPDATED_BY LAST_UPDATED_BY,
SYSDATE LAST_UPDATE_DATE
FROM (
SELECT  
T.TENANT_ID TENANT_ID,
T.SEQ_NAME SEQ_NAME,
T.CYCLE_INDEX CYCLE_INDEX,
T.CREATED_BY CREATED_BY,
T.CREATION_DATE CREATION_DATE,
T.LAST_UPDATED_BY LAST_UPDATED_BY
FROM BASE_CENTER.BASE_CODE_SEQ T WHERE T.RULE_ID=1112
) TT;

----导入的表格的数据有几条，序列加多少存进去。如表示数据有1000条，则，序列号更新：
UPDATE BASE_CENTER.BASE_CODE_SEQ set SERIAL_NUMBER=SERIAL_NUMBER+1000 where SEQ_ID =27828;

--更新导入的事业部账套
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.SETS_OF_BOOKS_ID=5 WHERE T.SETS_OF_BOOKS_NAME LIKE '饲料事业部%';
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.SETS_OF_BOOKS_ID=4 WHERE T.SETS_OF_BOOKS_NAME LIKE '猪业事业部%';
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.SETS_OF_BOOKS_ID=3 WHERE T.SETS_OF_BOOKS_NAME LIKE '食品事业部%';
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.SETS_OF_BOOKS_ID=2 WHERE T.SETS_OF_BOOKS_NAME LIKE '圣迪乐%';

---查询唯一的购销关系信息，导出来填充单据号，然后导入 BASE_ITEM_LAYOUT_HEAD_TEMP 表
-- 修改单据号 ABCJLC2107300001，将单据号递增导入BASE_ITEM_LAYOUT_HEAD_TEMP表
SELECT  
DISTINCT 
T.SETS_OF_BOOKS_ID, 
T.CUST_CODE,
T.SALE_ORG_CODE
FROM BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T

------------------开始校验导入的数据

--更新导入的事业部账套
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.SETS_OF_BOOKS_ID=5 WHERE T.SETS_OF_BOOKS_NAME LIKE '饲料事业部%';
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.SETS_OF_BOOKS_ID=4 WHERE T.SETS_OF_BOOKS_NAME LIKE '猪业事业部%';
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.SETS_OF_BOOKS_ID=3 WHERE T.SETS_OF_BOOKS_NAME LIKE '食品事业部%';
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.SETS_OF_BOOKS_ID=2 WHERE T.SETS_OF_BOOKS_NAME LIKE '圣迪乐%';

select * from BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T where T.Payment_Terms is null
--导入数据账期不是编码需要处理
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T set T.Payment_Terms='Z001' where T.Payment_Terms='现款现货';
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T set T.Payment_Terms='Z002' where T.Payment_Terms='3天帐期';
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T set T.Payment_Terms='Z003' where T.Payment_Terms='7天账期';
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T set T.Payment_Terms='Z004' where T.Payment_Terms='15天帐期';
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T set T.Payment_Terms='Z005' where T.Payment_Terms='30天账期';
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T set T.Payment_Terms='Z006' where T.Payment_Terms='45天账期';
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T set T.Payment_Terms='Z007' where T.Payment_Terms='60天账期';
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T set T.Payment_Terms='Z008' where T.Payment_Terms='70天账期';
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T set T.Payment_Terms='Z009' where T.Payment_Terms='90天账期';
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T set T.Payment_Terms='Z0010' where T.Payment_Terms='内部交易15天账期';

-- 临时处理1580替换成圣迪乐的
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T
   set T.Sets_Of_Books_Name = '圣迪乐村事业部' and T.Sets_Of_Books_Id = '2'
 where T.Sale_Org_Code = '1580'
   and T.Sets_Of_Books_Id = 6;

---更新明细表单据号为头表的单据号
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T
   SET T.LAYOUT_NO =
       (SELECT HT.LAYOUT_NO
          FROM BASE_CENTER.BASE_ITEM_LAYOUT_HEAD_TEMP HT
         WHERE HT.SETS_OF_BOOKS_ID = T.SETS_OF_BOOKS_ID
           AND HT.SALE_ORG_CODE = T.SALE_ORG_CODE
           AND HT.CUST_CODE = T.CUST_CODE);

--更新成本中心，前面补0，不够10位的
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.COST_CENTER_CODE=LPAD(T.COST_CENTER_CODE , 10 , '0') WHERE LENGTH(T.COST_CENTER_CODE) < 10;

--更新商品信息
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T
   SET T.ITEM_ID =
       (SELECT BI.ITEM_ID
          FROM BASE_CENTER.BASE_ITEM BI
         WHERE T.ITEM_CODE = BI.ITEM_CODE)
 WHERE T.ITEM_ID IS NULL;
 
 select * from BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T where T.CUST_NAME is null

--账期有填小写的
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T  SET T.PAYMENT_TERMS= INITCAP(T.PAYMENT_TERMS);

--更新销售组织名称
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.SALE_ORG_NAME = (SELECT S.SALE_ORG_NAME FROM BASE_CENTER.BASE_PLANT_CONFIG_VIEW S WHERE S.SALE_ORG_CODE =T.SALE_ORG_CODE AND rownum = 1) WHERE T.SALE_ORG_CODE IS NOT NULL;
--更新客户名称为最新的
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.CUST_NAME=(SELECT aa.AG_NAME FROM  ARCHIVES_CENTER.ARCHIVES_AGENCY aa WHERE aa.AG_CODE =T.CUST_CODE) WHERE T.CUST_CODE IS  NOT NULL;
--更新客户的账套
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.BSETS_OF_BOOKS_ID=(SELECT SOB.ID FROM  SYSTEM_CENTER.SYSTEM_SETS_OF_BOOKS SOB WHERE SOB.MAST_CODE =T.CUST_CODE) WHERE T.BSETS_OF_BOOKS_ID IS NULL;

--更新合同的客户ID
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T
   SET T.CUST_ID =
       (SELECT C.CUST_ID
          FROM BASE_CENTER.BASE_CUSTOMER C
         WHERE C.CUST_CODE = T.CUST_CODE
           AND C.SETS_OF_BOOKS_ID = T.SETS_OF_BOOKS_ID
           AND rownum = 1)
 WHERE T.CUST_ID IS NULL;
 
 select * from BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T where T.Cust_Id is null;

--更新商品的销售单位
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.UOM_CODE=(SELECT SM.SALES_UNIT FROM BASE_CENTER.BASE_ITEM_SUP_MAP SM WHERE T.ITEM_ID=SM.ITEM_ID AND rownum=1) WHERE T.UOM_CODE IS NULL;
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.UOM_ID=(SELECT SM.UOM_ID FROM BASE_CENTER.BASE_UOM SM WHERE T.UOM_CODE=SM.UOM_CODE AND rownum=1) WHERE T.UOM_ID IS NULL;
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.UOM_NAME=(SELECT SM.UOM_NAME FROM BASE_CENTER.BASE_UOM SM WHERE T.UOM_CODE=SM.UOM_CODE AND rownum=1) WHERE T.UOM_NAME IS NULL;

--更新已导入的头表ID
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T
   SET T.LAYOUT_HEAD_ID =
       (SELECT BH.LAYOUT_HEAD_ID
          FROM BASE_CENTER.BASE_ITEM_LAYOUT_HEAD BH
         WHERE BH.SETS_OF_BOOKS_ID = T.SETS_OF_BOOKS_ID
           AND BH.SALE_ORG_CODE = T.SALE_ORG_CODE
           AND BH.CUST_CODE = T.CUST_CODE and bh.apply_status='4')
 WHERE T.LAYOUT_HEAD_ID IS NULL;
 
 select * from BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T where T.LAYOUT_HEAD_ID is not null;

--更新已导入的行表ID
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T
   SET T.LAYOUT_LINE_ID =
       (SELECT BH.LAYOUT_LINE_ID
          FROM BASE_CENTER.BASE_ITEM_LAYOUT_LINE BH
         WHERE BH.LAYOUT_HEAD_ID = T.LAYOUT_HEAD_ID
           AND BH.ITEM_CODE = T.ITEM_CODE)
 WHERE T.LAYOUT_LINE_ID IS NULL;
 
 select * from BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T where T.LAYOUT_LINE_ID is not null;
 
 --临时处理
 select Payment_Terms from BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T where T.Err_Msg='付款条件输入错误;' group by T.Payment_Terms
 update BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T set T.Payment_Terms='Z010' where T.Err_Msg='付款条件输入错误;' and T.Payment_Terms='Z010';
  update BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T set T.Payment_Terms='Z009' where T.Err_Msg='付款条件输入错误;' and T.Payment_Terms='Z009';

---更新单据号
--UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.LAYOUT_NO =REPLACE (T.LAYOUT_NO,'ABCJLC210525', 'ABCJLC210523') WHERE T.LAYOUT_NO LIKE 'ABCJLC210525%';
--UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.LAYOUT_NO =REPLACE (T.LAYOUT_NO,'ABCJLC210526', 'ABCJLC210522') WHERE T.LAYOUT_NO LIKE 'ABCJLC210526%';
--UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.LAYOUT_NO =REPLACE (T.LAYOUT_NO,'ABCJLC210527', 'ABCJLC210521') WHERE T.LAYOUT_NO LIKE 'ABCJLC210527%';

--更新各种错误信息到错误字段
update BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.STATUS='',T.ERR_MSG ='';

UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.STATUS='ERROR',T.ERR_MSG =T.ERR_MSG || '销售组织不能为空;' WHERE T.SALE_ORG_CODE IS NULL;
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.STATUS='ERROR',T.ERR_MSG =T.ERR_MSG || '付款条件不能为空;' WHERE T.PAYMENT_TERMS IS NULL;
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.STATUS='ERROR',T.ERR_MSG =T.ERR_MSG || '付款条件输入错误;' WHERE T.PAYMENT_TERMS NOT LIKE 'Z0%';
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.STATUS='ERROR',T.ERR_MSG =T.ERR_MSG || '客户不存在;' WHERE T.CUST_ID IS NULL;
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.STATUS='ERROR',T.ERR_MSG =T.ERR_MSG || '销售组织不存在;' WHERE T.SALE_ORG_NAME IS NULL;
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.STATUS='ERROR',T.ERR_MSG =T.ERR_MSG || '商品不存在;' WHERE T.ITEM_ID IS NULL;

select ERR_MSG from BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T where T.STATUS='ERROR' group by T.ERR_MSG;
select * from BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T where T.STATUS='ERROR';
select * from BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T where T.BSETS_OF_BOOKS_ID is null;

--剩下的就是成功的
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T SET T.STATUS='SUCCESS' WHERE T.STATUS IS NULL;

select * from BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T where T.LAYOUT_HEAD_ID is null ;

--导入表头：
INSERT INTO BASE_CENTER.BASE_ITEM_LAYOUT_HEAD 
SELECT BASE_CENTER.SEQ_BASE_ITEM_LAYOUT_HEAD.nextval LAYOUT_HEAD_ID ,
-1 TENANT_ID,
TT.SETS_OF_BOOKS_ID SETS_OF_BOOKS_ID, 
TT.BSETS_OF_BOOKS_ID BSETS_OF_BOOKS_ID,
TT.LAYOUT_NO LAYOUT_NO,
TT.CUST_ID CUST_ID,
TT.CUST_CODE CUST_CODE,
TT.CUST_NAME CUST_NAME,
TT.SALE_ORG_CODE SALE_ORG_CODE,
TT.SALE_ORG_NAME SALE_ORG_NAME,
TRUNC(SYSDATE) LAYOUT_DATE,
5 STATUS,
'系统初始化导入250804' NOTE,
2 USABLE,
1 VERSION,
'dataInitor' CREATED_BY,
SYSDATE CREATION_DATE,
'dataInitor' LAST_UPDATED_BY,
SYSDATE LAST_UPDATE_DATE,
NULL ATTRIBUTE1,
NULL ATTRIBUTE2,
NULL ATTRIBUTE3,
NULL ATTRIBUTE4,
NULL ATTRIBUTE5,
NULL VENDOR_ID,
NULL CREATED_BY_USER_NAME,
NULL LAST_UPDATED_BY_USER_NAME,
null PROCESS_INSTANCE_ID,
'4' APPLY_STATUS,
null FILE_RELATION_IDS,
null QUOTE_LAYOUT_HEAD_ID,
'系统初始化导入250804' UPDATE_DATA_REMARKS,
null UNIFY_FILL_ID,
null SELF_FILL
FROM (
SELECT  
DISTINCT 
T.SETS_OF_BOOKS_ID, 
T.BSETS_OF_BOOKS_ID,
T.CUST_ID,
T.CUST_CODE,
T.CUST_NAME,
T.SALE_ORG_CODE,
T.SALE_ORG_NAME,
T.LAYOUT_NO 
FROM BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T WHERE T.STATUS ='SUCCESS' AND T.LAYOUT_HEAD_ID IS NULL 
--AND NOT EXISTS
--(SELECT 1 FROM BASE_CENTER.BASE_ITEM_LAYOUT_HEAD LH WHERE LH.SETS_OF_BOOKS_ID=T.SETS_OF_BOOKS_ID AND LH.SALE_ORG_CODE=T.SALE_ORG_CODE AND LH.CUST_CODE=T.CUST_CODE)
) 
TT;

delete from BASE_CENTER.BASE_ITEM_LAYOUT_HEAD where NOTE='系统初始化导入250804';

UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_HEAD bilh SET bilh.NOTE ='系统初始化导入250804' WHERE note = '系统初始化导入211231_2_1'
  AND TO_CHAR(CREATION_DATE , 'yyyy-mm-dd HH24') = '2022-01-01 09'

--更新头表ID进去
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T
   SET T.LAYOUT_HEAD_ID =
       (SELECT BH.LAYOUT_HEAD_ID
          FROM BASE_CENTER.BASE_ITEM_LAYOUT_HEAD BH
         WHERE BH.SETS_OF_BOOKS_ID = T.SETS_OF_BOOKS_ID
           AND BH.SALE_ORG_CODE = T.SALE_ORG_CODE
           AND BH.CUST_CODE = T.CUST_CODE 
           and BH.Note='系统初始化导入250804'
           )
 WHERE T.LAYOUT_HEAD_ID IS NULL;

select * from BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T where T.Layout_Head_Id is null

SELECT BH.SETS_OF_BOOKS_ID,BH.SALE_ORG_CODE,BH.CUST_CODE 
          FROM BASE_CENTER.BASE_ITEM_LAYOUT_HEAD BH
         WHERE 1=1 group by BH.SETS_OF_BOOKS_ID,BH.SALE_ORG_CODE,BH.CUST_CODE having count(1)> 1;

--更新单据号进实体表
--UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_HEAD T SET T.LAYOUT_NO=(SELECT BH.LAYOUT_NO FROM BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP BH WHERE BH.SETS_OF_BOOKS_ID=T.SETS_OF_BOOKS_ID AND BH.SALE_ORG_CODE=T.SALE_ORG_CODE AND BH.CUST_CODE=T.CUST_CODE AND rownum=1)WHERE T.LAYOUT_NO='-1';

update BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T set T.Layout_Line_Id=null;
select * from BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T WHERE T.STATUS ='SUCCESS' AND T.LAYOUT_LINE_ID IS NULL AND T.LAYOUT_HEAD_ID IS NOT NULL ;

--更新行表业务员id
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T set T.Salesman_Id = (select su.user_id from SYSTEM_CENTER."SYSTEM_USER" su  where su.login_id = T.Salesman_Code);
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T set T.Manager_Id = (select su.user_id from SYSTEM_CENTER."SYSTEM_USER" su  where su.login_id = T.Manager_Code);
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T set T.General_Manager_Id = (select su.user_id from SYSTEM_CENTER."SYSTEM_USER" su  where su.login_id = T.General_Manager_Code);

UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T set T.Salesman_NAME = (select su.Name from SYSTEM_CENTER."SYSTEM_USER" su  where su.login_id = T.Salesman_Code);
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T set T.Manager_NAME = (select su.Name from SYSTEM_CENTER."SYSTEM_USER" su  where su.login_id = T.Manager_Code);
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T set T.General_Manager_NAME = (select su.Name from SYSTEM_CENTER."SYSTEM_USER" su  where su.login_id = T.General_Manager_Code);

select * from BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T where T.Layout_Line_Id is null;

--导入行表
INSERT INTO BASE_CENTER.BASE_ITEM_LAYOUT_LINE 
SELECT BASE_CENTER.SEQ_BASE_ITEM_LAYOUT_LINE.nextval LAYOUT_LINE_ID ,
TT.LAYOUT_HEAD_ID LAYOUT_HEAD_ID,
-1 TENANT_ID,
TT.ITEM_ID ITEM_ID, 
TT.ITEM_CODE ITEM_CODE,
'系统初始化导入250804' NOTE,
2 USABLE,
1 VERSION,
'dataInitor' CREATED_BY,
SYSDATE CREATION_DATE,
'dataInitor' LAST_UPDATED_BY,
SYSDATE LAST_UPDATE_DATE,
NULL ATTRIBUTE1,
NULL ATTRIBUTE2,
NULL ATTRIBUTE3,
NULL ATTRIBUTE4,
NULL ATTRIBUTE5,
NULL COST_CENTER_CODE,
NULL COST_CENTER_NAME,
TT.PAYMENT_TERMS PAYMENT_TERMS,
TT.UOM_ID UOM_ID,
TT.Salesman_Id Salesman_Id,
TT.Salesman_NAME Salesman_NAME,
TT.Manager_Id Manager_Id,
TT.Manager_NAME Manager_NAME,
TT.General_Manager_Id General_Manager_Id,
TT.General_Manager_NAME General_Manager_NAME,
'' OLD_SALESMAN_ID,
'' OLD_SALESMAN_NAME,
'' OLD_MANAGER_ID,
'' OLD_MANAGER_NAME,
'' OLD_GENERAL_MANAGER_ID,
'' OLD_GENERAL_MANAGER_NAME
FROM (
SELECT  
DISTINCT 
T.LAYOUT_HEAD_ID, 
T.ITEM_ID, 
T.ITEM_CODE,
T.PAYMENT_TERMS,
T.UOM_ID,
T.Salesman_Id,
T.Salesman_NAME,
T.Manager_Id,
T.Manager_NAME,
T.General_Manager_Id,
T.General_Manager_NAME
FROM BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T WHERE T.STATUS ='SUCCESS' AND T.LAYOUT_LINE_ID IS NULL AND T.LAYOUT_HEAD_ID IS NOT NULL 
--AND NOT EXISTS
--(SELECT 1 FROM BASE_CENTER.BASE_ITEM_LAYOUT_LINE LL WHERE LL.LAYOUT_HEAD_ID=T.LAYOUT_HEAD_ID AND LL.ITEM_CODE=T.ITEM_CODE)
) TT;

create table BASE_CENTER.BASE_ITEM_LAYOUT_LINE_back220101 as 
select * from BASE_CENTER.BASE_ITEM_LAYOUT_LINE where NOTE='生产初始化导入250804'

-----更新行表ID到临时表
UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T
   SET T.LAYOUT_LINE_ID =
       (SELECT BH.LAYOUT_LINE_ID
          FROM BASE_CENTER.BASE_ITEM_LAYOUT_LINE BH
         WHERE BH.LAYOUT_HEAD_ID = T.LAYOUT_HEAD_ID
           AND BH.ITEM_CODE = T.ITEM_CODE
           and bh.note ='系统初始化导入250804' and rownum=1
           )
 WHERE T.LAYOUT_LINE_ID IS NULL;
 
 select * from BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T where T.LAYOUT_LINE_ID IS NULL;
 
 delete from BASE_CENTER.BASE_ITEM_LAYOUT_LINE BH where bh.note ='生产初始化导入250804' and to_char(bh.creation_date,'yyyy-mm-dd')='2021-12-31'
 
  delete from BASE_CENTER.BASE_ITEM_LAYOUT_HEAD BH where bh.note ='系统初始化导入250804' and to_char(bh.creation_date,'yyyy-mm-dd')='2021-12-31'

---插入成本中心子表。
INSERT INTO BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER 
SELECT BASE_CENTER.SEQ_BASE_ITEM_LAYOUT_COST_CENTER.nextval LAYOUT_COST_CENTER_ID ,
TT.LAYOUT_HEAD_ID LAYOUT_HEAD_ID,
TT.LAYOUT_LINE_ID LAYOUT_LINE_ID,
-1 TENANT_ID,
TT.ITEM_ID ITEM_ID, 
TT.ITEM_CODE ITEM_CODE,
TT.COST_CENTER_CODE,
TT.COST_CENTER_NAME,
'系统初始化导入250804' NOTE,
2 USABLE,
1 VERSION,
'dataInitor' CREATED_BY,
SYSDATE CREATION_DATE,
'dataInitor' LAST_UPDATED_BY,
SYSDATE LAST_UPDATE_DATE,
NULL ATTRIBUTE1,
NULL ATTRIBUTE2,
NULL ATTRIBUTE3,
NULL ATTRIBUTE4,
NULL ATTRIBUTE5,
'' UPDATE_DATA_REMARKS,
'' OLD_COST_CENTER_CODE,
'' OLD_COST_CENTER_NAME
FROM (
SELECT  
DISTINCT 
T.LAYOUT_HEAD_ID, 
T.LAYOUT_LINE_ID,
T.ITEM_ID, 
T.ITEM_CODE,
T.COST_CENTER_CODE,
T.COST_CENTER_NAME
FROM BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T WHERE T.STATUS ='SUCCESS' AND T.LAYOUT_LINE_ID IS NOT NULL AND T.LAYOUT_HEAD_ID IS 
NOT NULL AND NOT EXISTS
(SELECT 1 FROM BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER CC WHERE CC.ITEM_CODE =T.ITEM_CODE AND CC.COST_CENTER_CODE=T.COST_CENTER_CODE AND  CC.LAYOUT_HEAD_ID =T.LAYOUT_HEAD_ID AND CC.LAYOUT_LINE_ID =T.LAYOUT_LINE_ID )
) TT;
--临时处理
create table BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_back20220121 as 
select * from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER
delete from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER  where NOTE='生产初始化导入20220121'

--修改成本中心
update BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER bilcc
   set bilcc.cost_center_code   =
       (select T.Cost_Center_Code
          from BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T
         where T.Layout_Line_Id = bilcc.layout_line_id),
       bilcc.cost_center_name   =
       (select T.Cost_Center_Name
          from BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T
         where T.Layout_Line_Id = bilcc.layout_line_id),
       bilcc.update_data_remarks = bilcc.update_data_remarks || ';修改成本中心220121'
 where exists (select 1
          from BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T
         where T.Layout_Line_Id = bilcc.layout_line_id);
         
         select * from BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER bilcc where bilcc.update_data_remarks like '%修改成本中心220121%';
         
select bilcc.layout_line_id
  from BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP bilcc
 group by bilcc.layout_line_id
having count(bilcc.layout_line_id) > 1;

select * from BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T where T.Layout_Line_Id = 401945;

SELECT * FROM BASE_CENTER.BASE_ITEM_LAYOUT_LINE bill WHERE NOTE ='系统初始化导入250804'

--备份数据
create table BASE_CENTER.BASE_ITEM_LAYOUT_HEAD_back250804 as
  select * from BASE_CENTER.BASE_ITEM_LAYOUT_HEAD bilh ;
  
  UPDATE BASE_CENTER.BASE_ITEM_LAYOUT_HEAD bilh SET bilh.ATTRIBUTE2 ='' WHERE ATTRIBUTE2 IN ('组织切换批量作废2','组织切换批量作废');

-- 作废
  UPDATE
  BASE_CENTER.BASE_ITEM_LAYOUT_HEAD bilh
SET
  BILH.APPLY_STATUS = '6',
    BILH.Status = '99',
  USABLE = '1',
  ATTRIBUTE2 = '组织切换批量作废250804'
WHERE
  EXISTS (
  SELECT
    1
  FROM
    BASE_CENTER.BASE_ITEM_LAYOUT_HEAD_TEMP T
  WHERE
    T.cust_code = bilh.CUST_CODE
    AND T.sale_org_code = bilh.SALE_ORG_CODE
    and T.sets_of_books_id = bilh.sets_of_books_id
    ) and (bilh.note !='系统初始化导入250804' or bilh.note is null) and bilh.usable='2';
    
    select * from BASE_CENTER.BASE_ITEM_LAYOUT_HEAD bilh where bilh.note='系统初始化导入250804' and bilh.cust_code='C0017440' and bilh.sale_org_code='2060';
  select * from BASE_CENTER.BASE_ITEM_LAYOUT_HEAD bilh where bilh.ATTRIBUTE2='组织切换批量作废250804';

----------------------
--错误的信息导出SQL
SELECT 
T.SETS_OF_BOOKS_NAME AS "事业部名称",
T.SALE_ORG_CODE AS "销售组织编码",
T.SALE_ORG_NAME AS "销售组织名称",
T.CUST_CODE AS "客户编码",
T.ITEM_CODE AS "商品编码",
T.PAYMENT_TERMS AS "账期",
T.COST_CENTER_CODE AS "成本中心编码",
T.COST_CENTER_NAME AS "成本中心名称",
T.ERR_MSG AS "错误信息"
FROM  BASE_CENTER.BASE_ITEM_LAYOUT_DETAILS_TEMP T 
WHERE T.STATUS ='ERROR' ORDER BY TEMP_ID ASC;

```

新增所属事业部，主要新增部门，其它字段任取当前所属事业部1条

```

--创建所属事业部导入模板
CREATE TABLE ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP_tmp251231 
(
  AG_SUP_MAP_ID VARCHAR2(500),
  ORG_ID VARCHAR2(500),
AG_CODE VARCHAR2(500),
NAME_PATH VARCHAR2(500),
ORG_CODE VARCHAR2(500),
SETS_OF_BOOKS_ID VARCHAR2(2)
);

-- 更新账套
UPDATE ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP_tmp251231 t SET t.SETS_OF_BOOKS_ID=2 WHERE t.NAME_PATH LIKE '%圣迪乐村事业部%';
UPDATE ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP_tmp251231 t SET t.SETS_OF_BOOKS_ID=5 WHERE t.NAME_PATH LIKE '%饲料事业部%';

UPDATE ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP_tmp251231 t SET t.SETS_OF_BOOKS_ID=3 WHERE t.NAME_PATH LIKE '%食品事业部%';

select *  from ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP_tmp251231 t WHERE t.NAME_PATH LIKE '%食品事业部%';
-- 更新orgid
update ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP_tmp251231 
T set T.ORG_ID = (SELECT so.org_id FROM SYSTEM_CENTER.SYSTEM_ORG so where so.code = T.ORG_CODE)
where T.org_id is null;

select * from ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP_tmp251231 T where T.org_id is null;

SELECT
	count(1)
FROM
	ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP_tmp251231 t
WHERE
1=1
--AND t.AG_CODE ='C0003496' AND ORG_CODE ='00010847'
	AND t.SETS_OF_BOOKS_ID IS NULL
	AND t.ORG_ID IS NULL ;

SELECT count(1) FROM 
(
SELECT
    aasm_sub.AG_ID,
    t.ORG_ID,
    aasm_sub.STATE,
    aasm_sub.ENTER_DATE,
    aasm_sub.FAILURE_DATE,
    aasm_sub.FREEZE_DATE,
    aasm_sub.BIG_CHANNEL_CLASS_ID,
    aasm_sub.MID_CHANNEL_CLASS_ID,
    aasm_sub.SML_CHANNEL_CLASS_ID,
    aasm_sub.PROVINCE_ID,
    aasm_sub.PROVINCE_CODE,
    aasm_sub.PROVINCE_NAME,
    aasm_sub.CITY_ID,
    aasm_sub.CITY_CODE,
    aasm_sub.CITY_NAME,
    aasm_sub.DISTRICT_ID,
    aasm_sub.DISTRICT_CODE,
    aasm_sub.DISTRICT_NAME,
    aasm_sub.TOWN_ID,
    aasm_sub.TOWN_CODE,
    aasm_sub.TOWN_NAME,
    aasm_sub.ADDR,
    aasm_sub.BUS_CAT_IDS,
    aasm_sub.BUSINESS_MODE,
    aasm_sub.BUS_BRAND_IDS,
    aasm_sub.CUST_LV,
    aasm_sub.REMARKS,
    'datainitor' CREATED_BY,
    sysdate CREATION_DATE,
    'datainitor' LAST_UPDATED_BY,
    sysdate LAST_UPDATE_DATE,
    aasm_sub.OPTIMISTIC_LOCKER,
    t.SETS_OF_BOOKS_ID  SETS_OF_BOOKS_ID,
    aasm_sub.TENANT_ID,
    '组织切换20251231' ATTRIBUTE1,
    aasm_sub.ATTRIBUTE2,
    aasm_sub.ATTRIBUTE3,
    aasm_sub.ATTRIBUTE4,
    aasm_sub.ATTRIBUTE5,
    aasm_sub.AG_CODE,
    aasm_sub.BUS_CAT_NAMES,
    aasm_sub.BUS_BRAND_NAMES,
    aasm_sub.CREATE_UUID,
    aasm_sub.STORE_CHAIN_BRAND_IDS,
    aasm_sub.STORE_CHAIN_BRAND_NAMES,
    aasm_sub.STORE_BUS_MODE
FROM
    ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP_tmp251231 t
INNER JOIN (
    SELECT
        aasm.*,
        ROW_NUMBER() OVER (
            PARTITION BY aasm.ag_code, aasm.SETS_OF_BOOKS_ID  
            ORDER BY aasm.AG_ID  
        ) AS rn
    FROM ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP aasm
) aasm_sub ON aasm_sub.ag_code = t.AG_CODE
           AND aasm_sub.SETS_OF_BOOKS_ID = t.SETS_OF_BOOKS_ID
           AND aasm_sub.rn = 1
) aasm

delete from ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP where ATTRIBUTE1 ='组织切换20251231'

--任意取当前客户1条事业部信息 导入 值更新orgId
INSERT INTO ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP
SELECT
ARCHIVES_CENTER.Seq_ARCHIVES_AGENCY_SUP_MAP.NEXTVAL AG_SUP_MAP_ID,
aasm.AG_ID,
aasm.ORG_ID,
1 STATE,
aasm.ENTER_DATE,
aasm.FAILURE_DATE,
aasm.FREEZE_DATE,
aasm.BIG_CHANNEL_CLASS_ID,
aasm.MID_CHANNEL_CLASS_ID,
aasm.SML_CHANNEL_CLASS_ID,
aasm.PROVINCE_ID,
aasm.PROVINCE_CODE,
aasm.PROVINCE_NAME,
aasm.CITY_ID,
aasm.CITY_CODE,
aasm.CITY_NAME,
aasm.DISTRICT_ID,
aasm.DISTRICT_CODE,
aasm.DISTRICT_NAME,
aasm.TOWN_ID,
aasm.TOWN_CODE,
aasm.TOWN_NAME,
aasm.ADDR,
aasm.BUS_CAT_IDS,
aasm.BUSINESS_MODE,
aasm.BUS_BRAND_IDS,
aasm.CUST_LV,
aasm.REMARKS,
'datainitor' CREATED_BY,
sysdate CREATION_DATE,
'datainitor' LAST_UPDATED_BY,
sysdate LAST_UPDATE_DATE,
aasm.OPTIMISTIC_LOCKER,
aasm.SETS_OF_BOOKS_ID,
aasm.TENANT_ID,
'组织切换20251231' ATTRIBUTE1,
aasm.ATTRIBUTE2,
aasm.ATTRIBUTE3,
aasm.ATTRIBUTE4,
aasm.ATTRIBUTE5,
aasm.AG_CODE,
aasm.BUS_CAT_NAMES,
aasm.BUS_BRAND_NAMES,
aasm.CREATE_UUID,
aasm.STORE_CHAIN_BRAND_IDS,
aasm.STORE_CHAIN_BRAND_NAMES,
aasm.STORE_BUS_MODE
 
 FROM  
(
SELECT
    aasm_sub.AG_ID,
    t.ORG_ID,
    aasm_sub.STATE,
    aasm_sub.ENTER_DATE,
    aasm_sub.FAILURE_DATE,
    aasm_sub.FREEZE_DATE,
    aasm_sub.BIG_CHANNEL_CLASS_ID,
    aasm_sub.MID_CHANNEL_CLASS_ID,
    aasm_sub.SML_CHANNEL_CLASS_ID,
    aasm_sub.PROVINCE_ID,
    aasm_sub.PROVINCE_CODE,
    aasm_sub.PROVINCE_NAME,
    aasm_sub.CITY_ID,
    aasm_sub.CITY_CODE,
    aasm_sub.CITY_NAME,
    aasm_sub.DISTRICT_ID,
    aasm_sub.DISTRICT_CODE,
    aasm_sub.DISTRICT_NAME,
    aasm_sub.TOWN_ID,
    aasm_sub.TOWN_CODE,
    aasm_sub.TOWN_NAME,
    aasm_sub.ADDR,
    aasm_sub.BUS_CAT_IDS,
    aasm_sub.BUSINESS_MODE,
    aasm_sub.BUS_BRAND_IDS,
    aasm_sub.CUST_LV,
    aasm_sub.REMARKS,
    'datainitor' CREATED_BY,
    sysdate CREATION_DATE,
    'datainitor' LAST_UPDATED_BY,
    sysdate LAST_UPDATE_DATE,
    aasm_sub.OPTIMISTIC_LOCKER,
    t.SETS_OF_BOOKS_ID  SETS_OF_BOOKS_ID,
    aasm_sub.TENANT_ID,
    '组织切换20251231' ATTRIBUTE1,
    aasm_sub.ATTRIBUTE2,
    aasm_sub.ATTRIBUTE3,
    aasm_sub.ATTRIBUTE4,
    aasm_sub.ATTRIBUTE5,
    aasm_sub.AG_CODE,
    aasm_sub.BUS_CAT_NAMES,
    aasm_sub.BUS_BRAND_NAMES,
    aasm_sub.CREATE_UUID,
    aasm_sub.STORE_CHAIN_BRAND_IDS,
    aasm_sub.STORE_CHAIN_BRAND_NAMES,
    aasm_sub.STORE_BUS_MODE
FROM
    ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP_tmp251231 t
INNER JOIN (
    SELECT
        aasm.*,
        ROW_NUMBER() OVER (
            PARTITION BY aasm.ag_code, aasm.SETS_OF_BOOKS_ID  
            ORDER BY aasm.AG_ID  
        ) AS rn
    FROM ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP aasm
) aasm_sub ON aasm_sub.ag_code = t.AG_CODE
           AND aasm_sub.SETS_OF_BOOKS_ID = t.SETS_OF_BOOKS_ID
           AND aasm_sub.rn = 1

) aasm

select * from ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP aasm where aasm.attribute1 like '%组织切换20251231%'
  
  -- 修改推送状态
  UPDATE ARCHIVES_CENTER.ARCHIVES_AGENCY aa SET aa.SYNC_STATUS =1
  WHERE EXISTS (SELECT 1 FROM ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP aasm WHERE aasm.AG_ID = aa.AG_ID AND aasm.ATTRIBUTE1 LIKE '%组织切换20251231%')

```

跟进人导入

```
--初始化客户-业务员配属关系
--#ccs_oracle_prd/FRevzkXRY2
--1、创建临时表并导入模板数据
--DROP TABLE BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 PURGE;
CREATE TABLE BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101(
TEMP_ID NUMBER,
Set_of_books_id NUMBER,
Set_of_books_name VARCHAR2(128),
cust_code VARCHAR2(128),
cust_name VARCHAR2(256),
cust_org_path VARCHAR2(256),
menber_code VARCHAR2(128),
menber_name VARCHAR2(256),
REL_TYPE VARCHAR2(128),
REL_TYPE_name VARCHAR2(128),
stat  VARCHAR2(128),
massage  VARCHAR2(256),
cust_org_id NUMBER,
user_org_id NUMBER,
user_id NUMBER,
cust_id NUMBER,
rel_cust_org_id Varchar2(256),
rel_user_org_id number,
rel_user_org_PATH Varchar2(256),
rel_cust_org_PATH Varchar2(2000)
);
--alter table BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 add (rel_user_org_PATH Varchar2(256));
--alter table BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 drop column rel_user_org_PATH; 
--SELECT COUNT(1) FROM BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101;
SELECT * FROM BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 FOR UPDATE;
--2、进行数据校验：人员是否存在、客户是否存在、人员结构是否正确、客户机构是否存在、人员机构与客户机构是否匹配
--更新事业部账套号
UPDATE BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101
   SET SET_OF_BOOKS_ID = 4
 WHERE SET_OF_BOOKS_NAME = '猪业事业部';
COMMIT;

UPDATE BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 TE
   SET 
       te.cust_org_path = REPLACE(TE.cust_org_path, '_', '-')
 WHERE 1 = 1;
--更新USER_ID
--UPDATE BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 TE SET te.USER_ID = '';
UPDATE BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 TE
   SET TE.USER_ID =
       (SELECT SS.USER_ID
          FROM SYSTEM_CENTER.SYSTEM_USER_STAFF         SS,
               SYSTEM_CENTER.SYSTEM_SETS_OF_BOOKS_USER BU
         WHERE SS.USER_ID = BU.USER_ID
           AND BU.SETS_OF_BOOKS_ID = TE.SET_OF_BOOKS_ID
           AND SS.EMPLOYEE_NUMBER = TE.MENBER_CODE)
 WHERE TE.USER_ID IS NULL;
COMMIT;

--更新cust_org_id
--UPDATE BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 TE SET te.CUST_ORG_ID = '';
UPDATE BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 TE
   SET TE.CUST_ORG_ID =
       (SELECT SO.ORG_ID
          FROM SYSTEM_CENTER.SYSTEM_ORG                SO,
               ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP SM
         WHERE SO.SETS_OF_BOOKS_ID = TE.SET_OF_BOOKS_ID
           AND SM.SETS_OF_BOOKS_ID = TE.SET_OF_BOOKS_ID
           AND SM.AG_CODE = TE.CUST_CODE
           AND SO.ORG_ID = SM.ORG_ID
         --  AND sm.org_id = te.user_org_id
           AND SO.NAME_PATH = TE.CUST_ORG_PATH
           )
 WHERE TE.CUST_ORG_ID IS NULL;
COMMIT;

--核实USER_ORG(包括兼职)是否与CUST_ORG一致，如一致，USER_ORG_ID=CUST_ORG_ID
update BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 TE
   set te.user_org_id = te.cust_org_id
 where 1 = 1
   and exists (SELECT 1
          FROM SYSTEM_CENTER.SYSTEM_ORG       SO,
               SYSTEM_CENTER.SYSTEM_USER      SU,
               SYSTEM_CENTER.SYSTEM_ORG_STAFF OS
         WHERE SO.SETS_OF_BOOKS_ID = TE.SET_OF_BOOKS_ID
           AND SO.SETS_OF_BOOKS_ID = OS.SETS_OF_BOOKS_ID
           AND SU.USER_ID = OS.USER_ID
           AND SO.Name_Path = te.cust_org_path and OS.PART_TIME_STATUS = 1);

--更新User_Org_Id

--UPDATE BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 TE SET te.User_Org_Id = '';
UPDATE BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 TE
   SET TE.USER_ORG_ID =
       (SELECT SO.ORG_ID
          FROM SYSTEM_CENTER.SYSTEM_ORG       SO,
               SYSTEM_CENTER.SYSTEM_USER      SU,
               SYSTEM_CENTER.SYSTEM_ORG_STAFF OS
         WHERE SO.SETS_OF_BOOKS_ID = TE.SET_OF_BOOKS_ID
           AND SO.SETS_OF_BOOKS_ID = OS.SETS_OF_BOOKS_ID
           AND SU.USER_ID = OS.USER_ID
           AND SU.USER_ID = TE.USER_ID
           AND SO.ORG_ID = OS.ORG_ID
           AND OS.ORG_TYPE = 1
           --AND SO.NAME_PATH = TE.USER_ORG_PATH
           )
 WHERE TE.USER_ORG_ID IS NULL;
COMMIT;

--更新CUST_ID
--UPDATE BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 TE SET te.cust_id = '';
UPDATE BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 TE
   SET TE.Cust_Id =
       (SELECT ag.ag_id
          FROM ARCHIVES_CENTER.ARCHIVES_AGENCY ag,ARCHIVES_CENTER.ARCHIVES_AGENCY_sup_map sm
         WHERE ag.ag_id = sm.ag_id
           AND sm.sets_of_books_id = te.set_of_books_ID
           AND ag.ag_code = TE.Cust_Code
           AND ROWNUM = 1)
 WHERE TE.Cust_Id IS NULL;
COMMIT;

--输出系统中实存客户所在机构
--UPDATE BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 TE SET te.Rel_User_Org_Id = '',te.Rel_User_Org_path = '';
UPDATE BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 TE
   SET TE.REL_CUST_ORG_ID =
       (SELECT LISTAGG(SO.ORG_ID, ',') WITHIN GROUP(ORDER BY SM.AG_CODE) ORG_ID_LIST
          FROM SYSTEM_CENTER.SYSTEM_ORG                SO,
               ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP SM
         WHERE SO.SETS_OF_BOOKS_ID = TE.SET_OF_BOOKS_ID
           AND SM.SETS_OF_BOOKS_ID = TE.SET_OF_BOOKS_ID
           AND SO.ORG_ID = SM.ORG_ID
           AND SM.AG_CODE = TE.CUST_CODE)
 WHERE TE.REL_CUST_ORG_ID IS NULL;
COMMIT;

--输出系统中实存用户所在机构
--UPDATE BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 TE SET te.Rel_User_Org_Id = '',te.Rel_User_Org_path = '';
UPDATE BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 TE
   SET TE.Rel_User_Org_Id =
       (SELECT SO.ORG_ID
          FROM SYSTEM_CENTER.SYSTEM_ORG       SO,
               SYSTEM_CENTER.SYSTEM_USER      SU,
               SYSTEM_CENTER.SYSTEM_ORG_STAFF OS
         WHERE SO.SETS_OF_BOOKS_ID = TE.SET_OF_BOOKS_ID
           AND SO.SETS_OF_BOOKS_ID = OS.SETS_OF_BOOKS_ID
           AND SU.USER_ID = OS.USER_ID
           AND SU.USER_ID = TE.USER_ID
           AND SO.ORG_ID = OS.ORG_ID
           AND OS.ORG_TYPE = 1),
       TE.Rel_User_Org_path =
       (SELECT SO.Name_Path
          FROM SYSTEM_CENTER.SYSTEM_ORG       SO,
               SYSTEM_CENTER.SYSTEM_USER      SU,
               SYSTEM_CENTER.SYSTEM_ORG_STAFF OS
         WHERE SO.SETS_OF_BOOKS_ID = TE.SET_OF_BOOKS_ID
           AND SO.SETS_OF_BOOKS_ID = OS.SETS_OF_BOOKS_ID
           AND SU.USER_ID = OS.USER_ID
           AND SU.USER_ID = TE.USER_ID
           AND SO.ORG_ID = OS.ORG_ID
           AND OS.ORG_TYPE = 1)
 WHERE TE.Rel_User_Org_path IS NULL;
COMMIT;

--设置为跟进人员
UPDATE BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 TE set rel_type='1';

UPDATE BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 SET MASSAGE='',STAT='';

--数据检验
--检查客户是否存在
--UPDATE BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 TE SET te.STAT = '',te.MASSAGE = '';
UPDATE BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 TE
   SET TE.STAT = 'ERROR', TE.MASSAGE = TE.MASSAGE || '业务员不存在;'
 WHERE TE.USER_ID IS NULL;
COMMIT;
UPDATE BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 TE
   SET TE.STAT = 'ERROR', TE.MASSAGE = TE.MASSAGE || '业务员机构不存在;'
 WHERE TE.USER_ORG_ID IS NULL;
COMMIT;
UPDATE BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 TE
   SET TE.STAT = 'ERROR', TE.MASSAGE = TE.MASSAGE || '客户不存在;'
 WHERE TE.CUST_ID IS NULL;
COMMIT;
UPDATE BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 TE
   SET TE.STAT    = 'ERROR',
       TE.MASSAGE = TE.MASSAGE || '客户机构不存在或与业务员机构不匹配;'
 WHERE TE.REL_TYPE = 1
   AND (TE.CUST_ORG_ID != TE.USER_ORG_ID
   OR TE.CUST_ORG_ID IS NULL);
COMMIT;
/*UPDATE BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 TE
   SET TE.STAT = 'ERROR', TE.MASSAGE = TE.MASSAGE || '业务员-客户机构不匹配;'
 WHERE TE.CUST_ORG_ID IS NOT NULL
   AND TE.USER_ORG_ID IS NOT NULL
   AND TE.CUST_ORG_ID != TE.USER_ORG_ID;
COMMIT;*/
UPDATE BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 TE
   SET TE.STAT = 'SUCCESS'
 WHERE 
 1=1
-- and TE.CUST_ORG_ID IS NOT NULL
   AND TE.USER_ORG_ID IS NOT NULL
   AND te.user_id IS NOT NULL
   AND te.cust_id IS NOT NULL
--   AND TE.CUST_ORG_ID = TE.USER_ORG_ID
   AND te.rel_type = '1';
COMMIT;
UPDATE BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 TE
   SET TE.STAT = 'SUCCESS'
 WHERE TE.CUST_ORG_ID IS NOT NULL
   AND TE.USER_ORG_ID IS NOT NULL
   AND te.user_id IS NOT NULL
   AND te.cust_id IS NOT NULL
   AND te.rel_type = '2';
COMMIT;

select * from BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 TE where TE.stat='ERROR';
--3、将校验通过数据导入正式表
INSERT INTO BUSUSER.BUS_CUSTOMER_RELATIONSHIP 
SELECT BUSUSER.SEQ_BUS_CUSTOMER_RELATIONSHIP.NEXTVAL REL_ID,
       TE.SET_OF_BOOKS_ID SETS_OF_BOOKS_ID,
       -1 TENANT_ID,
       TE.USER_ID USER_ID,
       TE.CUST_ID CUST_ID,
       te.rel_type REL_TYPE,
       '2' USABLE,
       1.0 VERSION_NO,
       'dataInitor' CREATED_BY,
       SYSDATE CREATION_DATE,
       'dataInitor' LAST_UPDATED_BY,
       SYSDATE LAST_UPDATE_DATE,
       '初始化导入_2021260101' ATTRIBUTE_1,
       '' ATTRIBUTE_2,
       '' ATTRIBUTE_3,
       '' ATTRIBUTE_4,
       '' ATTRIBUTE_5,
       TE.USER_ORG_ID ORG_ID,
       TE.CUST_CODE CUST_CODE
  FROM BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 TE
 WHERE TE.STAT = 'SUCCESS'
   AND te.rel_type = '1'
   AND NOT EXISTS (SELECT 1
          FROM BUSUSER.BUS_CUSTOMER_RELATIONSHIP RE
         WHERE RE.SETS_OF_BOOKS_ID = TE.SET_OF_BOOKS_ID
           AND RE.CUST_ID = TE.CUST_ID
           AND RE.User_Id = TE.User_Id
--           AND RE.ORG_ID = TE.Cust_Org_Id
           --客户业务员配属关系
           AND re.rel_type = te.rel_type);

--rel_type类型为2
INSERT INTO BUSUSER.BUS_CUSTOMER_RELATIONSHIP 
SELECT --BUSUSER.SEQ_BUS_CUSTOMER_RELATIONSHIP.NEXTVAL REL_ID,
       TE.SET_OF_BOOKS_ID SETS_OF_BOOKS_ID,
       -1 TENANT_ID,
       TE.USER_ID USER_ID,
       TE.CUST_ID CUST_ID,
       te.rel_type REL_TYPE,
       '2' USABLE,
       1.0 VERSION_NO,
       'dataInitor' CREATED_BY,
       SYSDATE CREATION_DATE,
       'dataInitor' LAST_UPDATED_BY,
       SYSDATE LAST_UPDATE_DATE,
       '初始化导入_2021260101' ATTRIBUTE_1,
       '' ATTRIBUTE_2,
       '' ATTRIBUTE_3,
       '' ATTRIBUTE_4,
       '' ATTRIBUTE_5,
       TE.USER_ORG_ID ORG_ID,
       TE.CUST_CODE CUST_CODE
  FROM BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 TE
 WHERE TE.STAT = 'SUCCESS'
   AND te.rel_type = '2'
   AND NOT EXISTS (SELECT 1
          FROM BUSUSER.BUS_CUSTOMER_RELATIONSHIP RE
         WHERE RE.SETS_OF_BOOKS_ID = TE.SET_OF_BOOKS_ID
           AND RE.USER_ID = TE.USER_ID
           AND RE.CUST_ID = TE.CUST_ID
           --服务人员
           AND re.rel_type = te.rel_type);
           
           
           --更新已存在数据
/*按照业务要求选择是否执行*/

SELECT *
  FROM BUSUSER.BUS_CUSTOMER_RELATIONSHIP RE
 WHERE 1 = 1
   AND EXISTS (SELECT 1
          FROM BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP0518 TE
         WHERE RE.SETS_OF_BOOKS_ID = TE.SET_OF_BOOKS_ID
           AND RE.CUST_ID = TE.CUST_ID
           AND RE.ORG_ID = TE.CUST_ORG_ID
           AND RE.REL_TYPE = TE.REL_TYPE
           AND RE.USER_ID != TE.USER_ID
           and TE.STAT = 'SUCCESS'
           AND TE.REL_TYPE = '1');

UPDATE BUSUSER.BUS_CUSTOMER_RELATIONSHIP RE
   SET RE.USER_ID         =
       (SELECT TE.USER_ID
          FROM BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP0518 TE
         WHERE RE.SETS_OF_BOOKS_ID = TE.SET_OF_BOOKS_ID
           AND RE.CUST_ID = TE.CUST_ID
           AND RE.ORG_ID = TE.CUST_ORG_ID
           AND RE.REL_TYPE = TE.REL_TYPE
           AND RE.USER_ID != TE.USER_ID
           AND TE.STAT = 'SUCCESS'
           AND TE.REL_TYPE = '1'),
       RE.ATTRIBUTE_1      = RE.ATTRIBUTE_1 || ',更改',
       RE.LAST_UPDATED_BY  = 'dataInitor',
       RE.LAST_UPDATE_DATE = SYSDATE
 WHERE 1 = 1
   AND EXISTS (SELECT 1
          FROM BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP0518 TE
         WHERE RE.SETS_OF_BOOKS_ID = TE.SET_OF_BOOKS_ID
           AND RE.CUST_ID = TE.CUST_ID
           AND RE.ORG_ID = TE.CUST_ORG_ID
           AND RE.REL_TYPE = TE.REL_TYPE
           AND RE.USER_ID != TE.USER_ID
           AND TE.STAT = 'SUCCESS'
           AND TE.REL_TYPE = '1');

--4、数据导出修正
SELECT te.temp_id,
       DECODE(TE.SET_OF_BOOKS_ID, 3, '食品事业部', 4, '猪业事业部',5,'饲料事业部') 事业部,
       TE.CUST_CODE 客户编码,
       TE.CUST_NAME 客户名称,
       TE.CUST_ORG_PATH 客户机构路径,
       TE.MENBER_CODE 工号,
       TE.MENBER_NAME 姓名,
       TE.REL_TYPE_NAME 配置类型,
       te.massage 失败原因
  FROM BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 TE
 WHERE 1 = 1
   AND NVL(TE.STAT, 'ERROR') = 'ERROR'
 ORDER BY 1;

SELECT * FROM  BUSUSER.BUS_CUSTOMER_RELATIONSHIP_TEMP260101 TE WHERE te.cust_code = 'C0001103';

SELECT COUNT(1) FROM BUSUSER.BUS_CUSTOMER_RELATIONSHIP ;

```

本次切换临时操作的sql记录

```

--重备份表恢复数据

INSERT INTO BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER 
  SELECT 
  t.LAYOUT_COST_CENTER_ID,
t.LAYOUT_HEAD_ID,
t.LAYOUT_LINE_ID,
t.TENANT_ID,
t.ITEM_ID,
t.ITEM_CODE,
t.COST_CENTER_CODE,
t.COST_CENTER_NAME,
t.NOTE,
t.USABLE,
t.VERSION,
t.CREATED_BY,
t.CREATION_DATE,
t.LAST_UPDATED_BY,
t.LAST_UPDATE_DATE,
t.ATTRIBUTE1,
t.ATTRIBUTE2,
t.ATTRIBUTE3,
t.ATTRIBUTE4,
t.ATTRIBUTE5,
'删除恢复260101' UPDATE_DATA_REMARKS,
'' OLD_COST_CENTER_CODE,
'' OLD_COST_CENTER_NAME
FROM 
  (SELECT
  bilccb.*

FROM
  BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_BACK bilccb
WHERE
  EXISTS (
  SELECT
    1
  FROM
    BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_IMP251231 bilcci
  WHERE
    bilcci.LAYOUT_COST_CENTER_ID = BILCCB.LAYOUT_COST_CENTER_ID)) t;
    
--所属事业部org重复的问题
 -- 统计重复分组的数量（按ORG_ID+AG_ID分组）
SELECT 
    ORG_ID,
    AG_ID,
    COUNT(*) AS repeat_count, 
    MIN(AG_SUP_MAP_ID) AS keep_id  
FROM ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP aasm
WHERE AG_CODE ='C0014584' AND SETS_OF_BOOKS_ID =3
GROUP BY ORG_ID, AG_ID
HAVING COUNT(*) > 1;

DELETE FROM ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP t1
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT 
            AG_SUP_MAP_ID,
            ROW_NUMBER() OVER (
                PARTITION BY ORG_ID, AG_ID 
                ORDER BY AG_SUP_MAP_ID ASC  
            ) AS rn
        FROM ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP
        WHERE SETS_OF_BOOKS_ID =3
    ) t2
    WHERE t2.rn > 1  
      AND t1.AG_SUP_MAP_ID = t2.AG_SUP_MAP_ID
);

CREATE TABLE ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP_251231_6 AS 
SELECT *  FROM ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP t1
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT 
            AG_SUP_MAP_ID,
            ROW_NUMBER() OVER (
                PARTITION BY ORG_ID, AG_ID 
                ORDER BY AG_SUP_MAP_ID ASC  
            ) AS rn
        FROM ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP
        WHERE SETS_OF_BOOKS_ID =3
    ) t2
    WHERE t2.rn > 1  
      AND t1.AG_SUP_MAP_ID = t2.AG_SUP_MAP_ID
);

-- 成本中心重复的删除
  SELECT 
    LAYOUT_LINE_ID,
    ITEM_CODE,
    COST_CENTER_CODE,
    COUNT(*) AS repeat_count,  
    MIN(LAYOUT_COST_CENTER_ID) AS keep_id  
FROM BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER bilcc
WHERE EXISTS (SELECT 1 FROM BASE_CENTER.BASE_ITEM_LAYOUT_HEAD bilh WHERE bilh.LAYOUT_HEAD_ID = bilcc.LAYOUT_HEAD_ID AND bilh.SETS_OF_BOOKS_ID =2)
GROUP BY LAYOUT_LINE_ID, ITEM_CODE, COST_CENTER_CODE
HAVING COUNT(*) > 1;

-- 1. 创建备份表（仅存储待删除的重复数据）
CREATE TABLE BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER_DELETE_BACKUP260101 
AS 
SELECT t.* 
FROM (
    -- 先筛选SETS_OF_BOOKS_ID=2的记录，再按重复维度分组标记行号
    SELECT 
        bilcc.*,
        ROW_NUMBER() OVER (
            PARTITION BY bilcc.LAYOUT_LINE_ID, bilcc.ITEM_CODE, bilcc.COST_CENTER_CODE
            ORDER BY bilcc.LAYOUT_COST_CENTER_ID ASC  -- 与删除逻辑的保留规则一致（rn=1保留，rn>1删除）
        ) AS rn
    FROM BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER bilcc
    WHERE EXISTS (
        SELECT 1 FROM BASE_CENTER.BASE_ITEM_LAYOUT_HEAD bilh 
        WHERE bilh.LAYOUT_HEAD_ID = bilcc.LAYOUT_HEAD_ID 
        AND bilh.SETS_OF_BOOKS_ID = 2
    )
) t
WHERE t.rn > 1;

DELETE FROM BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER t1
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT 
            LAYOUT_COST_CENTER_ID,
            ROW_NUMBER() OVER (
                PARTITION BY LAYOUT_LINE_ID, ITEM_CODE, COST_CENTER_CODE  
                ORDER BY LAYOUT_COST_CENTER_ID ASC 
            ) AS rn
        FROM BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER bilcc
        WHERE EXISTS (
            SELECT 1 FROM BASE_CENTER.BASE_ITEM_LAYOUT_HEAD bilh 
            WHERE bilh.LAYOUT_HEAD_ID = bilcc.LAYOUT_HEAD_ID 
            AND bilh.SETS_OF_BOOKS_ID =2
        )
    ) t2
    WHERE t2.rn > 1  
      AND t1.LAYOUT_COST_CENTER_ID = t2.LAYOUT_COST_CENTER_ID
);

-- 删除成本中心为空的商品行

DELETE
FROM
  BASE_CENTER.BASE_ITEM_LAYOUT_LINE b
WHERE
  EXISTS (
  SELECT
    1
  FROM
    (
    SELECT
      DISTINCT bill.LAYOUT_LINE_ID
    FROM
      BASE_CENTER.BASE_ITEM_LAYOUT_LINE bill
    LEFT JOIN BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER bilcc ON
      bilcc.LAYOUT_LINE_ID = bill.LAYOUT_LINE_ID
    LEFT JOIN BASE_CENTER.BASE_ITEM_LAYOUT_HEAD bilh ON
      bilh.LAYOUT_HEAD_ID = bill.LAYOUT_HEAD_ID
    WHERE
      bilcc.LAYOUT_COST_CENTER_ID IS NULL
      AND bilh.SETS_OF_BOOKS_ID =2
      ) T
  WHERE
    T.LAYOUT_LINE_ID = b.LAYOUT_LINE_ID);
    
    
    
    -- 创建备份表，仅存储即将删除的“无LINE关联”的HEAD记录
CREATE TABLE BASE_CENTER.BASE_ITEM_LAYOUT_HEAD_DELETE_BACKUP260101 
AS 
SELECT bilh.* 
FROM BASE_CENTER.BASE_ITEM_LAYOUT_HEAD bilh
WHERE NOT EXISTS (
    -- 核心条件：HEAD的LAYOUT_HEAD_ID在LINE表中无匹配记录
    SELECT 1 FROM BASE_CENTER.BASE_ITEM_LAYOUT_LINE bill 
    WHERE bill.LAYOUT_HEAD_ID = bilh.LAYOUT_HEAD_ID 
) AND bilh.SETS_OF_BOOKS_ID =2;

-- 验证备份结果（查看待删除的记录数）
SELECT COUNT(*) AS backup_count FROM BASE_CENTER.BASE_ITEM_LAYOUT_HEAD_DELETE_BACKUP;

-- 查看无LINE关联的HEAD记录详情（可按需调整字段）
SELECT bilh.LAYOUT_HEAD_ID, bilh.SETS_OF_BOOKS_ID, bilh.CREATION_DATE
FROM BASE_CENTER.BASE_ITEM_LAYOUT_HEAD bilh
WHERE NOT EXISTS (
    SELECT 1 FROM BASE_CENTER.BASE_ITEM_LAYOUT_LINE bill 
    WHERE bill.LAYOUT_HEAD_ID = bilh.LAYOUT_HEAD_ID 
) AND bilh.SETS_OF_BOOKS_ID =2;

-- 统计待删除的记录总数
SELECT COUNT(*) AS to_delete_count 
FROM BASE_CENTER.BASE_ITEM_LAYOUT_HEAD bilh
WHERE NOT EXISTS (
    SELECT 1 FROM BASE_CENTER.BASE_ITEM_LAYOUT_LINE bill 
    WHERE bill.LAYOUT_HEAD_ID = bilh.LAYOUT_HEAD_ID
)AND bilh.SETS_OF_BOOKS_ID =2;

-- 删除HEAD表中无LINE关联的记录（NOT EXISTS 比 LEFT JOIN + IS NULL 更高效）
DELETE FROM BASE_CENTER.BASE_ITEM_LAYOUT_HEAD bilh
WHERE NOT EXISTS (
    SELECT 1 FROM BASE_CENTER.BASE_ITEM_LAYOUT_LINE bill 
    WHERE bill.LAYOUT_HEAD_ID = bilh.LAYOUT_HEAD_ID
) AND bilh.SETS_OF_BOOKS_ID =2;

SELECT
bilh.CUST_CODE ,bilh.CUST_NAME,bilh.SALE_ORG_CODE,bilh.SALE_ORG_NAME ,
  bill.ITEM_CODE ,
  (
  SELECT
    bi.ITEM_DESC
  FROM
    BASE_CENTER.BASE_ITEM bi
  WHERE
    bi.ITEM_CODE = bill.ITEM_CODE AND rownum=1 ) ITEM_DESC,

(SELECT su.LOGIN_ID FROM SYSTEM_CENTER."SYSTEM_USER" su WHERE su.USER_ID = bill.SALESMAN_ID) 业务员工号,
  bill.SALESMAN_NAME ,
  
  (SELECT su.LOGIN_ID FROM SYSTEM_CENTER."SYSTEM_USER" su WHERE su.USER_ID = bill.MANAGER_ID) 业务经理工号,
  bill.MANAGER_NAME ,
  
  (SELECT su.LOGIN_ID FROM SYSTEM_CENTER."SYSTEM_USER" su WHERE su.USER_ID = bill.GENERAL_MANAGER_ID) 总经理工号,
  bill.GENERAL_MANAGER_NAME ,
          (SELECT
        bdi.ITEM_NAME
        FROM
        BASE_CENTER.BASE_DICT bd
        INNER JOIN BASE_CENTER.BASE_DICT_ITEM bdi ON
        bd.DICT_ID = bdi.DICT_ID
        WHERE
        bd.CODE = 'itemLine'
        AND bdi.ITEM_VALUE = ITEM.SERIES_NAME) PRO_LINE_NAME,
    
  bilh.SETS_OF_BOOKS_ID,
  bill.LAYOUT_LINE_ID 
FROM
  BASE_CENTER.BASE_ITEM_LAYOUT_HEAD bilh
LEFT JOIN BASE_CENTER.BASE_ITEM_LAYOUT_LINE bill ON
  bill.LAYOUT_HEAD_ID = bilh.LAYOUT_HEAD_ID

left join BASE_CENTER.BASE_ITEM ITEM on bill.ITEM_ID = ITEM.ITEM_ID
WHERE
  bilh.USABLE = 2
  AND bilh.APPLY_STATUS = 4 and (bilh.sets_of_books_id = 5 or bilh.sale_org_code='1230');
  
  
  
  
  
  SELECT
bilh.CUST_CODE ,bilh.CUST_NAME,bilh.SALE_ORG_CODE,bilh.SALE_ORG_NAME ,
  bill.ITEM_CODE ,
  (
  SELECT
    bi.ITEM_DESC
  FROM
    BASE_CENTER.BASE_ITEM bi
  WHERE
    bi.ITEM_CODE = bill.ITEM_CODE AND rownum=1 ) ITEM_DESC,
  bilcc.COST_CENTER_CODE ,
  bilcc.COST_CENTER_NAME ,

(SELECT su.LOGIN_ID FROM SYSTEM_CENTER."SYSTEM_USER" su WHERE su.USER_ID = bill.SALESMAN_ID) 业务员工号,
  bill.SALESMAN_NAME ,
  
  (SELECT su.LOGIN_ID FROM SYSTEM_CENTER."SYSTEM_USER" su WHERE su.USER_ID = bill.MANAGER_ID) 业务经理工号,
  bill.MANAGER_NAME ,
  
  (SELECT su.LOGIN_ID FROM SYSTEM_CENTER."SYSTEM_USER" su WHERE su.USER_ID = bill.GENERAL_MANAGER_ID) 总经理工号,
  bill.GENERAL_MANAGER_NAME ,
          (SELECT
        bdi.ITEM_NAME
        FROM
        BASE_CENTER.BASE_DICT bd
        INNER JOIN BASE_CENTER.BASE_DICT_ITEM bdi ON
        bd.DICT_ID = bdi.DICT_ID
        WHERE
        bd.CODE = 'itemLine'
        AND bdi.ITEM_VALUE = ITEM.SERIES_NAME) PRO_LINE_NAME,
    bilcc.LAYOUT_COST_CENTER_ID,
  bilh.SETS_OF_BOOKS_ID
FROM
  BASE_CENTER.BASE_ITEM_LAYOUT_HEAD bilh
LEFT JOIN BASE_CENTER.BASE_ITEM_LAYOUT_LINE bill ON
  bill.LAYOUT_HEAD_ID = bilh.LAYOUT_HEAD_ID
LEFT JOIN BASE_CENTER.BASE_ITEM_LAYOUT_COST_CENTER bilcc ON
  bilcc.LAYOUT_LINE_ID = bill.LAYOUT_LINE_ID
left join BASE_CENTER.BASE_ITEM ITEM on bill.ITEM_ID = ITEM.ITEM_ID
WHERE
  bilh.USABLE = 2
  AND bilh.APPLY_STATUS = 4 and (bilh.sets_of_books_id = 5 or bilh.sale_org_code='1230');

```