Source: DMF/PuC/AIF/DR1 scripts/DMF_CLEARANCE_VALIDATIONS.sql
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Truncate validation tables
------------------------------------------------------------------------------------------------------------------------------------------------------------------
TRUNCATE TABLE ORI_CLEARANCE_REJECTED;
TRUNCATE TABLE ORI_CLEARANCE_TMP;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- INSERT REJECTED RECORDS TO ORI_CLEARANCE_REJECTED
------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO ORI_CLEARANCE_REJECTED
(LOG_LVL, ERROR_MSG,
PROD_NUM,ORG_NUM,SUPPLIER_NUM,EVENT_TYPE,EFFECTIVE_FROM_DATE,EFFECTIVE_TO_DATE,RESET_FLG,CHANGE_AMOUNT,CHANGE_UOM,CHANGE_CURR_CODE,CHANGE_TYPE,CONFLICT_IND,REASON_CODE,REASON_CODE_DESC,CREATED_ON_DT,CHANGED_ON_DT,DATASOURCE_NUM_ID
,INSERTED_AT)
---------------------------------------------------------------------------------
-- 1. Combine CLEARANCE data from multiple sources
---------------------------------------------------------------------------------
-- Historical
WITH hist_clearance AS (
SELECT
ITEM AS PROD_NUM -- PK 1
,STORE AS ORG_NUM -- PK 2
,'-1' AS SUPPLIER_NUM
,'CRE' AS EVENT_TYPE
,START_DATE AS EFFECTIVE_FROM_DATE
,END_DATE AS EFFECTIVE_TO_DATE
,'N' AS RESET_FLG
,PRICE AS CHANGE_AMOUNT
,'EA' AS CHANGE_UOM
,DOC_CURR_CODE AS CHANGE_CURR_CODE
,2 AS CHANGE_TYPE
,0 AS CONFLICT_IND
,NULL AS REASON_CODE
,NULL AS REASON_CODE_DESC
,SYSDATE AS CREATED_ON_DT
,SYSDATE AS CHANGED_ON_DT
,1 AS DATASOURCE_NUM_ID -- PK 4
FROM PRICE_HISTORY_RAW
WHERE PRICE_CHANGE_TRAN_TYPE = 8
)
-- Current
, curr_clearance AS (
SELECT
ITEM AS PROD_NUM -- PK 1
,STORE AS ORG_NUM -- PK 2
,'-1' AS SUPPLIER_NUM
,'CRE' AS EVENT_TYPE
,START_DATE AS EFFECTIVE_FROM_DATE
,END_DATE AS EFFECTIVE_TO_DATE
,'N' AS RESET_FLG
,PRICE AS CHANGE_AMOUNT
,'EA' AS CHANGE_UOM
,DOC_CURR_CODE AS CHANGE_CURR_CODE
,2 AS CHANGE_TYPE
,0 AS CONFLICT_IND
,NULL AS REASON_CODE
,NULL AS REASON_CODE_DESC
,SYSDATE AS CREATED_ON_DT
,SYSDATE AS CHANGED_ON_DT
,1 AS DATASOURCE_NUM_ID -- PK 4
FROM ORI_PRICE_CLEARANCE_RAW
WHERE PRICE_CHANGE_TRAN_TYPE = 8
)
, full_clearance AS (
SELECT * FROM hist_clearance
UNION
SELECT * FROM curr_clearance
)
---------------------------------------------------------------------------------
-- 2. Filter Items and Locations not migrated
---------------------------------------------------------------------------------
-- ITEM
, error_rows_missing_items AS (
SELECT 'E' AS LOG_LVL, 'ERROR_MISSING_ITEM' AS ERROR_MSG, p.*, SYSDATE AS INSERTED_AT FROM full_clearance p
WHERE NOT EXISTS (SELECT 1 FROM ITEM_MASTER_CTRL c WHERE c.ctrl_status = 'C' AND c.ITEM = p.PROD_NUM )
)
-- LOCATION
, error_rows_missing_location AS (
SELECT 'E' AS LOG_LVL, 'ERROR_MISSING_LOCATION' AS ERROR_MSG, p.*, SYSDATE AS INSERTED_AT FROM full_clearance p
WHERE NOT EXISTS (SELECT 1 FROM ( SELECT STORE AS LOC FROM STORE_ADD_CTRL WHERE ctrl_status = 'C'
UNION ALL SELECT WH AS LOC FROM WH_CTRL WHERE ctrl_status = 'C') c WHERE c.LOC = p.ORG_NUM )
)
-- ITEM/LOCATION
, error_rows_missing_item_location AS (
SELECT 'E' AS LOG_LVL, 'ERROR_MISSING_ITEM_LOCATION' AS ERROR_MSG, p.*, SYSDATE AS INSERTED_AT FROM full_clearance p
WHERE NOT EXISTS (SELECT 1 FROM ITEM_LOC_CTRL c WHERE c.ctrl_status = 'C'
AND c.ITEM = p.PROD_NUM
AND c.LOC = p.ORG_NUM)
)
, filtered_clearance AS (
SELECT p.* FROM full_clearance p
WHERE EXISTS (SELECT 1 FROM ITEM_MASTER_CTRL c WHERE c.ctrl_status = 'C' AND c.ITEM = p.PROD_NUM )
AND EXISTS (SELECT 1 FROM (SELECT STORE AS LOC FROM STORE_ADD_CTRL WHERE ctrl_status = 'C' UNION ALL SELECT WH AS LOC FROM WH_CTRL WHERE ctrl_status = 'C') c WHERE c.LOC = p.ORG_NUM )
AND EXISTS (SELECT 1 FROM ITEM_LOC_CTRL c WHERE c.ctrl_status = 'C' AND c.ITEM = p.PROD_NUM AND c.LOC = p.ORG_NUM)
)
---------------------------------------------------------------------------------
-- 3. Validate duplicate keys
---------------------------------------------------------------------------------
, error_rows_duplicate_keys AS (
SELECT 'E' AS LOG_LVL, 'ERROR_DUPLICATE_KEY' AS ERROR_MSG,
p.PROD_NUM,
p.ORG_NUM,
p.SUPPLIER_NUM,
p.EVENT_TYPE,
p.EFFECTIVE_FROM_DATE,
p.EFFECTIVE_TO_DATE,
p.RESET_FLG,
p.CHANGE_AMOUNT,
p.CHANGE_UOM,
p.CHANGE_CURR_CODE,
p.CHANGE_TYPE,
p.CONFLICT_IND,
p.REASON_CODE,
p.REASON_CODE_DESC,
p.CREATED_ON_DT,
p.CHANGED_ON_DT,
p.DATASOURCE_NUM_ID,
SYSDATE AS INSERTED_AT
FROM (SELECT p.*, COUNT(*) OVER (PARTITION BY PROD_NUM, ORG_NUM, EFFECTIVE_FROM_DATE) AS duplicate_cnt FROM filtered_clearance p) p WHERE p.duplicate_cnt > 1
)
---------------------------------------------------------------------------------
-- 4. Validate mandatory fields are not null
---------------------------------------------------------------------------------
, error_rows_null_fields AS (
SELECT 'E' AS LOG_LVL, 'ERROR_INVALID_NULL' AS ERROR_MSG, p.*, SYSDATE AS INSERTED_AT FROM filtered_clearance p
WHERE PROD_NUM IS NULL
OR ORG_NUM IS NULL
OR SUPPLIER_NUM IS NULL
OR EVENT_TYPE IS NULL
OR EVENT_TYPE IS NULL
OR EFFECTIVE_FROM_DATE IS NULL
OR EFFECTIVE_TO_DATE IS NULL
OR RESET_FLG IS NULL
)
---------------------------------------------------------------------------------
-- 5. Return all rows with errors/warnings
---------------------------------------------------------------------------------
, all_error_rows AS (
SELECT * FROM error_rows_missing_items
UNION ALL
SELECT * FROM error_rows_missing_location
UNION ALL
SELECT * FROM error_rows_missing_item_location
UNION ALL
SELECT * FROM error_rows_null_fields
UNION ALL
SELECT * FROM error_rows_duplicate_keys
)
SELECT * FROM all_error_rows;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Insert valid rows to ORI_CLEARANCE_TMP
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Rows that passed validation are inserted in ORI_PRICE_TMP
INSERT INTO ORI_CLEARANCE_TMP
(PROD_NUM,ORG_NUM,SUPPLIER_NUM,CLEARANCE_ID,EVENT_TYPE,EFFECTIVE_FROM_DATE,EFFECTIVE_TO_DATE,RESET_FLG,CHANGE_AMOUNT,CHANGE_UOM,CHANGE_CURR_CODE,CHANGE_TYPE,CLEARANCE_DISPLAY_ID,CLEARANCE_GROUP_ID,CONFLICT_IND,MARKDOWN_NBR,MARKDOWN_NBR_DESC,REASON_CODE,REASON_CODE_DESC,CREATED_ON_DT,CHANGED_ON_DT,DATASOURCE_NUM_ID,INTEGRATION_ID
,INSERTED_AT)
-- Historical
WITH hist_clearance AS (
SELECT
ITEM AS PROD_NUM -- PK 1
,STORE AS ORG_NUM -- PK 2
,'-1' AS SUPPLIER_NUM
,'CRE' AS EVENT_TYPE
,START_DATE AS EFFECTIVE_FROM_DATE
,END_DATE AS EFFECTIVE_TO_DATE
,'N' AS RESET_FLG
,PRICE AS CHANGE_AMOUNT
,'EA' AS CHANGE_UOM
,DOC_CURR_CODE AS CHANGE_CURR_CODE
,2 AS CHANGE_TYPE
,0 AS CONFLICT_IND
,NULL AS REASON_CODE
,NULL AS REASON_CODE_DESC
,SYSDATE AS CREATED_ON_DT
,SYSDATE AS CHANGED_ON_DT
,1 AS DATASOURCE_NUM_ID -- PK 4
FROM PRICE_HISTORY_RAW
WHERE PRICE_CHANGE_TRAN_TYPE = 8
)
-- Current
, curr_clearance AS (
SELECT
ITEM AS PROD_NUM -- PK 1
,STORE AS ORG_NUM -- PK 2
,'-1' AS SUPPLIER_NUM
,'CRE' AS EVENT_TYPE
,START_DATE AS EFFECTIVE_FROM_DATE
,END_DATE AS EFFECTIVE_TO_DATE
,'N' AS RESET_FLG
,PRICE AS CHANGE_AMOUNT
,'EA' AS CHANGE_UOM
,DOC_CURR_CODE AS CHANGE_CURR_CODE
,2 AS CHANGE_TYPE
,0 AS CONFLICT_IND
,NULL AS REASON_CODE
,NULL AS REASON_CODE_DESC
,SYSDATE AS CREATED_ON_DT
,SYSDATE AS CHANGED_ON_DT
,1 AS DATASOURCE_NUM_ID -- PK 4
FROM ORI_PRICE_CLEARANCE_RAW
WHERE PRICE_CHANGE_TRAN_TYPE = 8
)
, full_clearance AS (
SELECT * FROM hist_clearance
UNION
SELECT * FROM curr_clearance
)
, final_data AS (
select --a.*,
PROD_NUM AS PROD_NUM -- PK 1
,ORG_NUM AS ORG_NUM -- PK 2
,SUPPLIER_NUM AS SUPPLIER_NUM
,CLEARANCE_ID AS CLEARANCE_ID -- PK 3
,EVENT_TYPE AS EVENT_TYPE
,EFFECTIVE_FROM_DATE AS EFFECTIVE_FROM_DATE
,EFFECTIVE_TO_DATE AS EFFECTIVE_TO_DATE
,RESET_FLG AS RESET_FLG
,CHANGE_AMOUNT AS CHANGE_AMOUNT
,CHANGE_UOM AS CHANGE_UOM
,CHANGE_CURR_CODE AS CHANGE_CURR_CODE
,CHANGE_TYPE AS CHANGE_TYPE
,CLEARANCE_ID AS CLEARANCE_DISPLAY_ID
,CLEARANCE_ID AS CLEARANCE_GROUP_ID
,CONFLICT_IND AS CONFLICT_IND
,MARKDOWN_NBR AS MARKDOWN_NBR
,MARKDOWN_NBR AS MARKDOWN_NBR_DESC
,REASON_CODE AS REASON_CODE
,REASON_CODE_DESC AS REASON_CODE_DESC
,CREATED_ON_DT AS CREATED_ON_DT
,CHANGED_ON_DT AS CHANGED_ON_DT
,DATASOURCE_NUM_ID AS DATASOURCE_NUM_ID -- PK 4
,PROD_NUM || '~' || ORG_NUM || '~' || CLEARANCE_ID || '~' || DATASOURCE_NUM_ID AS INTEGRATION_ID
,SYSDATE AS INSERTED_AT
FROM (
select fc.*,
900000000000000 + ROW_NUMBER() OVER (ORDER BY PROD_NUM, ORG_NUM, EFFECTIVE_FROM_DATE) - 1 AS CLEARANCE_ID,
ROW_NUMBER() OVER (PARTITION BY PROD_NUM, ORG_NUM ORDER BY EFFECTIVE_FROM_DATE) AS MARKDOWN_NBR
from full_clearance fc-- 3 227 588
-- filter rejected before creating sequences
WHERE NOT EXISTS (SELECT 1 FROM ORI_CLEARANCE_REJECTED r WHERE r.PROD_NUM = fc.PROD_NUM AND r.ORG_NUM = fc.ORG_NUM AND r.EFFECTIVE_FROM_DATE = fc.EFFECTIVE_FROM_DATE)
) a
)
SELECT * FROM final_data;
COMMIT;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- REJECTION SUMMARY
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Retrieve an approximate summary by error type
SELECT
ERROR_MSG,
COUNT(*) AS num_records,
APPROX_COUNT_DISTINCT(item) AS num_items,
APPROX_COUNT_DISTINCT(org_num) AS num_locs
FROM ORI_CLEARANCE_REJECTED
GROUP BY ERROR_MSG;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- CREATE ORI_CLEARANCE_CTRL TABLE
------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*
--insert table
insert into migr_tables (TABLE_DOMAIN, TABLE_NAME, FLOW_ID, INTERFACE_ID, DESTINATION, LOAD_CTRL_TO_FINAL_TYPE, PARALLEL_IND, PARTITION_IND, PARTITION_COLUMN, STATUS)
values (NULL, 'ORI_CLEARANCE', 5000, 5000, '2', 'T', NULL, NULL, NULL, 'A');
--insert columns
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','PROD_NUM','VARCHAR2(30)','N',null,null,'A',null,'ORI_CLEARANCE','PROD_NUM','<CLOB>',null,1,'PROD_NUM');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','ORG_NUM','VARCHAR2(30)','N',null,null,'A',null,'ORI_CLEARANCE','ORG_NUM','<CLOB>',null,2,'ORG_NUM');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','SUPPLIER_NUM','VARCHAR2(30)','N',null,null,'A',null,'ORI_CLEARANCE','SUPPLIER_NUM','<CLOB>',null,3,'SUPPLIER_NUM');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','CLEARANCE_ID','VARCHAR2(30)','N',null,null,'A',null,'ORI_CLEARANCE','CLEARANCE_ID','<CLOB>',null,4,'CLEARANCE_ID');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','EVENT_TYPE','VARCHAR2(30)','N',null,null,'A',null,'ORI_CLEARANCE','EVENT_TYPE','<CLOB>',null,5,'EVENT_TYPE');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','EFFECTIVE_FROM_DATE','DATE','N',null,null,'A',null,'ORI_CLEARANCE','EFFECTIVE_FROM_DATE','<CLOB>',null,6,'EFFECTIVE_FROM_DATE');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','EFFECTIVE_TO_DATE','DATE','N',null,null,'A',null,'ORI_CLEARANCE','EFFECTIVE_TO_DATE','<CLOB>',null,7,'EFFECTIVE_TO_DATE');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','RESET_FLG','CHAR(1)','N',null,null,'A',null,'ORI_CLEARANCE','RESET_FLG','<CLOB>',null,8,'RESET_FLG');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','RESET_ID','VARCHAR2(30)','N',null,null,'A',null,'ORI_CLEARANCE','RESET_ID','<CLOB>',null,9,'RESET_ID');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','EXTRACTION_DATE','DATE','N',null,null,'A',null,'ORI_CLEARANCE','EXTRACTION_DATE','<CLOB>',null,10,'EXTRACTION_DATE');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','CHANGE_AMOUNT','NUMBER(20,4)','N',null,null,'A',null,'ORI_CLEARANCE','CHANGE_AMOUNT','<CLOB>',null,11,'CHANGE_AMOUNT');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','CHANGE_UOM','VARCHAR2(30)','N',null,null,'A',null,'ORI_CLEARANCE','CHANGE_UOM','<CLOB>',null,12,'CHANGE_UOM');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','CHANGE_CURR_CODE','VARCHAR2(30)','N',null,null,'A',null,'ORI_CLEARANCE','CHANGE_CURR_CODE','<CLOB>',null,13,'CHANGE_CURR_CODE');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','APPROVAL_DATE','DATE','N',null,null,'A',null,'ORI_CLEARANCE','APPROVAL_DATE','<CLOB>',null,14,'APPROVAL_DATE');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','CHANGE_TYPE','VARCHAR2(30)','N',null,null,'A',null,'ORI_CLEARANCE','CHANGE_TYPE','<CLOB>',null,15,'CHANGE_TYPE');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','CLEARANCE_DISPLAY_ID','VARCHAR2(30)','N',null,null,'A',null,'ORI_CLEARANCE','CLEARANCE_DISPLAY_ID','<CLOB>',null,16,'CLEARANCE_DISPLAY_ID');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','CLEARANCE_GROUP_ID','VARCHAR2(30)','N',null,null,'A',null,'ORI_CLEARANCE','CLEARANCE_GROUP_ID','<CLOB>',null,17,'CLEARANCE_GROUP_ID');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','CLEARANCE_GROUP_DESC','VARCHAR2(1000)','N',null,null,'A',null,'ORI_CLEARANCE','CLEARANCE_GROUP_DESC','<CLOB>',null,18,'CLEARANCE_GROUP_DESC');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','CLEARANCE_GROUP_DISPLAY_ID','VARCHAR2(30)','N',null,null,'A',null,'ORI_CLEARANCE','CLEARANCE_GROUP_DISPLAY_ID','<CLOB>',null,19,'CLEARANCE_GROUP_DISPLAY_ID');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','CONFLICT_IND','NUMBER(1,0)','N',null,null,'A',null,'ORI_CLEARANCE','CONFLICT_IND','<CLOB>',null,20,'CONFLICT_IND');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','MARKDOWN_NBR','VARCHAR2(30)','N',null,null,'A',null,'ORI_CLEARANCE','MARKDOWN_NBR','<CLOB>',null,21,'MARKDOWN_NBR');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','MARKDOWN_NBR_DESC','VARCHAR2(250)','N',null,null,'A',null,'ORI_CLEARANCE','MARKDOWN_NBR_DESC','<CLOB>',null,22,'MARKDOWN_NBR_DESC');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','OUT_OF_STOCK_DATE','DATE','N',null,null,'A',null,'ORI_CLEARANCE','OUT_OF_STOCK_DATE','<CLOB>',null,23,'OUT_OF_STOCK_DATE');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','REASON_CODE','VARCHAR2(30)','N',null,null,'A',null,'ORI_CLEARANCE','REASON_CODE','<CLOB>',null,24,'REASON_CODE');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','REASON_CODE_DESC','VARCHAR2(250)','N',null,null,'A',null,'ORI_CLEARANCE','REASON_CODE_DESC','<CLOB>',null,25,'REASON_CODE_DESC');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','STATE','VARCHAR2(30)','N',null,null,'A',null,'ORI_CLEARANCE','STATE','<CLOB>',null,26,'STATE');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','CREATED_BY_ID','VARCHAR2(80)','N',null,null,'A',null,'ORI_CLEARANCE','CREATED_BY_ID','<CLOB>',null,27,'CREATED_BY_ID');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','CHANGED_BY_ID','VARCHAR2(80)','N',null,null,'A',null,'ORI_CLEARANCE','CHANGED_BY_ID','<CLOB>',null,28,'CHANGED_BY_ID');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','CREATED_ON_DT','DATE','N',null,null,'A',null,'ORI_CLEARANCE','CREATED_ON_DT','<CLOB>',null,29,'CREATED_ON_DT');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','CHANGED_ON_DT','DATE','N',null,null,'A',null,'ORI_CLEARANCE','CHANGED_ON_DT','<CLOB>',null,30,'CHANGED_ON_DT');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','AUX1_CHANGED_ON_DT','DATE','N',null,null,'A',null,'ORI_CLEARANCE','AUX1_CHANGED_ON_DT','<CLOB>',null,31,'AUX1_CHANGED_ON_DT');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','AUX2_CHANGED_ON_DT','DATE','N',null,null,'A',null,'ORI_CLEARANCE','AUX2_CHANGED_ON_DT','<CLOB>',null,32,'AUX2_CHANGED_ON_DT');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','AUX3_CHANGED_ON_DT','DATE','N',null,null,'A',null,'ORI_CLEARANCE','AUX3_CHANGED_ON_DT','<CLOB>',null,33,'AUX3_CHANGED_ON_DT');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','AUX4_CHANGED_ON_DT','DATE','N',null,null,'A',null,'ORI_CLEARANCE','AUX4_CHANGED_ON_DT','<CLOB>',null,34,'AUX4_CHANGED_ON_DT');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','SRC_EFF_FROM_DT','DATE','N',null,null,'A',null,'ORI_CLEARANCE','SRC_EFF_FROM_DT','<CLOB>',null,35,'SRC_EFF_FROM_DT');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','SRC_EFF_TO_DT','DATE','N',null,null,'A',null,'ORI_CLEARANCE','SRC_EFF_TO_DT','<CLOB>',null,36,'SRC_EFF_TO_DT');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','DELETE_FLG','CHAR(1)','N',null,null,'A',null,'ORI_CLEARANCE','DELETE_FLG','<CLOB>',null,37,'DELETE_FLG');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','DATASOURCE_NUM_ID','NUMBER(10,0)','N',null,null,'A',null,'ORI_CLEARANCE','DATASOURCE_NUM_ID','<CLOB>',null,38,'DATASOURCE_NUM_ID');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','INTEGRATION_ID','VARCHAR2(80)','N',null,null,'A',null,'ORI_CLEARANCE','INTEGRATION_ID','<CLOB>',null,39,'INTEGRATION_ID');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','TENANT_ID','VARCHAR2(80)','N',null,null,'A',null,'ORI_CLEARANCE','TENANT_ID','<CLOB>',null,40,'TENANT_ID');
INSERT INTO MIGR_COLUMNS (TABLE_NAME, COLUMN_NAME, DATA_TYPE, MANDATORY_FLG, LOAD_TS, ORIG_ROWID, STATUS, ERRORS, DSA_TABLE_NAME, DSA_COLUMN_NAME, DSA_CALC, SEQUENCE_NAME, DC_FILE_COLUMN_SEQUENCE, DC_COLUMN_NAME) VALUES ('ORI_CLEARANCE','X_CUSTOM','VARCHAR2(10)','N',null,null,'A',null,'ORI_CLEARANCE','X_CUSTOM','<CLOB>',null,41,'X_CUSTOM');
--gen:
BEGIN
-- Cria o procedure que vai criar as CTRL tables (MIGR)
PCK_MIGR_GEN.GEN_CREATE_CONTROL_TABLES_2 (P_table_name => 'ORI_CLEARANCE');
END;
--create:
BEGIN
CREATE_CTRL_TABLES_2;
END;
*/
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- INSERT DATA INTO ORI_CLEARANCE_CTRL TABLE
------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO ORI_CLEARANCE_CTRL
(PROD_NUM,ORG_NUM,SUPPLIER_NUM,CLEARANCE_ID,EVENT_TYPE,EFFECTIVE_FROM_DATE,EFFECTIVE_TO_DATE,RESET_FLG,CHANGE_AMOUNT,CHANGE_UOM,CHANGE_CURR_CODE,CHANGE_TYPE,CLEARANCE_DISPLAY_ID,CLEARANCE_GROUP_ID,CONFLICT_IND,MARKDOWN_NBR,MARKDOWN_NBR_DESC,REASON_CODE,REASON_CODE_DESC,CREATED_BY_ID,CHANGED_BY_ID,CREATED_ON_DT,DATASOURCE_NUM_ID,INTEGRATION_ID)
SELECT
PROD_NUM,ORG_NUM,SUPPLIER_NUM,CLEARANCE_ID,EVENT_TYPE,EFFECTIVE_FROM_DATE,EFFECTIVE_TO_DATE,RESET_FLG,CHANGE_AMOUNT,CHANGE_UOM,CHANGE_CURR_CODE,CHANGE_TYPE,CLEARANCE_DISPLAY_ID,CLEARANCE_GROUP_ID,CONFLICT_IND,MARKDOWN_NBR,MARKDOWN_NBR_DESC,REASON_CODE,REASON_CODE_DESC,CREATED_BY_ID,CHANGED_BY_ID,CREATED_ON_DT,DATASOURCE_NUM_ID,INTEGRATION_ID
FROM ORI_CLEARANCE_TMP;
COMMIT;