-- step1 根据参数查找 是否有死循环的数据

SELECT *

FROM cst_pac_quantity_layers cpql

WHERE pac_period_id = 13013 --pac_period_id

AND cost_group_id = 1201 --cost_group_id

AND NOT EXISTS

(SELECT 1

FROM cst_pac_item_costs cpic

WHERE cpic.cost_layer_id = cpql.cost_layer_id

AND pac_period_id = cpql.pac_period_id

AND cost_group_id = cpql.cost_group_id

AND inventory_item_id = cpql.inventory_item_id);

-- step2 备份如下表的数据

-- 注意 表后面加日期 不重名

CREATE TABLE CPQL_BKP_180607 AS

SELECT * FROM CST_PAC_QUANTITY_LAYERS

WHERE PAC_PERIOD_ID = 13013 --pac_period_id

AND COST_GROUP_ID = 1201 --cost_group_id

;

CREATE TABLE CPICD_BKP_180607 AS

SELECT * FROM CST_PAC_ITEM_COST_DETAILS

WHERE COST_LAYER_ID IN (SELECT COST_LAYER_ID

FROM CST_PAC_ITEM_COSTS

WHERE PAC_PERIOD_ID = 13013 --pac_period_id

AND COST_GROUP_ID = 1201 --cost_group_id

);

CREATE TABLE CPIC_BKP_180607 AS

SELECT * FROM CST_PAC_ITEM_COSTS

WHERE PAC_PERIOD_ID = 13013 --pac_period_id

AND COST_GROUP_ID = 1201 --cost_group_id

;

CREATE TABLE MPTCD_BKP_180607 AS

SELECT * FROM MTL_PAC_TXN_COST_DETAILS

WHERE PAC_PERIOD_ID = 13013 --pac_period_id

AND COST_GROUP_ID = 1201 --cost_group_id

;

CREATE TABLE MPACD_BKP_180607 AS

SELECT * FROM MTL_PAC_ACTUAL_COST_DETAILS

WHERE PAC_PERIOD_ID = 13013 --pac_period_id

AND COST_GROUP_ID = 1201 --cost_group_id

;

CREATE TABLE CPPB_BKP_180607 AS

SELECT * FROM CST_PAC_PERIOD_BALANCES

WHERE PAC_PERIOD_ID = 13013 --pac_period_id

AND COST_GROUP_ID = 1201 --cost_group_id

;

CREATE TABLE WPPB_BKP_180607 AS

SELECT * FROM WIP_PAC_PERIOD_BALANCES

WHERE PAC_PERIOD_ID = 13013 --pac_period_id

AND COST_GROUP_ID = 1201 --cost_group_id

;

-- step 3

Make sure no PAC processors are running for the the PAC cost_group_id and the PAC period ID in question.

-- step 4 Perform cleaning of wrong data using PAC cost_group_id and PAC period_id.

DELETE FROM cst_pac_quantity_layers

WHERE pac_period_id = 13013 --pac_period_id

AND cost_group_id = 1201 --cost_group_id

;

DELETE CST_PAC_ITEM_COST_DETAILS

WHERE COST_LAYER_ID IN (SELECT COST_LAYER_ID

FROM CST_PAC_ITEM_COSTS

WHERE PAC_PERIOD_ID = 13013 --pac_period_id

AND COST_GROUP_ID = 1201 --cost_group_id

);

DELETE CST_PAC_ITEM_COSTS

WHERE PAC_PERIOD_ID = 13013 --pac_period_id

AND COST_GROUP_ID = 1201 -- cost_group_id

;

DELETE MTL_PAC_TXN_COST_DETAILS

WHERE PAC_PERIOD_ID = 13013 --pac_period_id

AND COST_GROUP_ID = 1201 --cost_group_id

;

DELETE MTL_PAC_ACTUAL_COST_DETAILS

WHERE PAC_PERIOD_ID = 13013 --pac_period_id

AND COST_GROUP_ID = 1201 --cost_group_id

;

DELETE CST_PAC_PERIOD_BALANCES

WHERE PAC_PERIOD_ID = 13013 --pac_period_id

AND COST_GROUP_ID = 1201 --cost_group_id

;

DELETE WIP_PAC_PERIOD_BALANCES

WHERE PAC_PERIOD_ID = 13013 --pac_period_id

AND COST_GROUP_ID = 1201 --cost_group_id

;

Commit;

-- step 5

Rerun Query from Step1 and verify that no more problematic records are remaining in the system.