DR2_item_dmf_validation.sql

Source: DMF/PuC/AIF/DR2/DR2_item_dmf_validation.sql

UPDATE ITEM_MASTER_CTRL SET CTRL_STATUS = 'I';
DECLARE
    -- PCK_MIGR_VALIDATE_RULES.VALIDATE_RULES_TABLE
    L_validate_rules_table_rc VARCHAR2 (255);
    L_validate_rules_table_err VARCHAR2 (4000);
BEGIN
    -- Valida as rules das tabelas de CTRL
    PCK_MIGR_VALIDATE_RULES.VALIDATE_RULES_TABLE (P_rule_type   => NULL,
                                                  P_rule_id     => NULL,
                                                  P_table_name  => 'ITEM_MASTER',
                                                  return_code   => L_validate_rules_table_rc,
                                                  error_message => L_validate_rules_table_err);
    -- Print das vars
    DBMS_OUTPUT.PUT_LINE('Return Code: ' || L_validate_rules_table_rc);
    DBMS_OUTPUT.PUT_LINE('Error Message: ' || L_validate_rules_table_err);
END;
/
COMMIT;
 
 
DECLARE
    -- PCK_MIGR_VALIDATE_RULES.VALIDATE_RULES_TABLE
    L_validate_rules_table_rc VARCHAR2 (255);
    L_validate_rules_table_err VARCHAR2 (4000);
BEGIN
    -- Valida as rules das tabelas de CTRL
    PCK_MIGR_VALIDATE_RULES.VALIDATE_RULES_TABLE (P_rule_type   => NULL,
                                                  P_rule_id     => NULL,
                                                  P_table_name  => 'ITEM_SEASONS',
                                                  return_code   => L_validate_rules_table_rc,
                                                  error_message => L_validate_rules_table_err);
    -- Print das vars
    DBMS_OUTPUT.PUT_LINE('Return Code: ' || L_validate_rules_table_rc);
    DBMS_OUTPUT.PUT_LINE('Error Message: ' || L_validate_rules_table_err);
END;
/
COMMIT;
 
DECLARE
    -- PCK_MIGR_VALIDATE_RULES.VALIDATE_RULES_TABLE
    L_validate_rules_table_rc VARCHAR2 (255);
    L_validate_rules_table_err VARCHAR2 (4000);
BEGIN
    -- Valida as rules das tabelas de CTRL
    PCK_MIGR_VALIDATE_RULES.VALIDATE_RULES_TABLE (P_rule_type   => NULL,
                                                  P_rule_id     => NULL,
                                                  P_table_name  => 'ITEM_MASTER_TL',
                                                  return_code   => L_validate_rules_table_rc,
                                                  error_message => L_validate_rules_table_err);
    -- Print das vars
    DBMS_OUTPUT.PUT_LINE('Return Code: ' || L_validate_rules_table_rc);
    DBMS_OUTPUT.PUT_LINE('Error Message: ' || L_validate_rules_table_err);
END;
/
COMMIT;
 
DECLARE
    -- PCK_MIGR_VALIDATE_RULES.VALIDATE_RULES_TABLE
    L_validate_rules_table_rc VARCHAR2 (255);
    L_validate_rules_table_err VARCHAR2 (4000);
BEGIN
    -- Valida as rules das tabelas de CTRL
    PCK_MIGR_VALIDATE_RULES.VALIDATE_RULES_TABLE (P_rule_type   => NULL,
                                                  P_rule_id     => NULL,
                                                  P_table_name  => 'ITEM_SUPPLIER',
                                                  return_code   => L_validate_rules_table_rc,
                                                  error_message => L_validate_rules_table_err);
    -- Print das vars
    DBMS_OUTPUT.PUT_LINE('Return Code: ' || L_validate_rules_table_rc);
    DBMS_OUTPUT.PUT_LINE('Error Message: ' || L_validate_rules_table_err);
END;
/
COMMIT;
 
DECLARE
    -- PCK_MIGR_VALIDATE_RULES.VALIDATE_RULES_TABLE
    L_validate_rules_table_rc VARCHAR2 (255);
    L_validate_rules_table_err VARCHAR2 (4000);
BEGIN
    -- Valida as rules das tabelas de CTRL
    PCK_MIGR_VALIDATE_RULES.VALIDATE_RULES_TABLE (P_rule_type   => NULL,
                                                  P_rule_id     => NULL,
                                                  P_table_name  => 'ITEM_SUPP_COUNTRY',
                                                  return_code   => L_validate_rules_table_rc,
                                                  error_message => L_validate_rules_table_err);
    -- Print das vars
    DBMS_OUTPUT.PUT_LINE('Return Code: ' || L_validate_rules_table_rc);
    DBMS_OUTPUT.PUT_LINE('Error Message: ' || L_validate_rules_table_err);
END;
/
COMMIT;
 
DECLARE
    -- PCK_MIGR_VALIDATE_RULES.VALIDATE_RULES_TABLE
    L_validate_rules_table_rc VARCHAR2 (255);
    L_validate_rules_table_err VARCHAR2 (4000);
BEGIN
    -- Valida as rules das tabelas de CTRL
    PCK_MIGR_VALIDATE_RULES.VALIDATE_RULES_TABLE (P_rule_type   => NULL,
                                                  P_rule_id     => NULL,
                                                  P_table_name  => 'ITEM_SUPP_COUNTRY_DIM',
                                                  return_code   => L_validate_rules_table_rc,
                                                  error_message => L_validate_rules_table_err);
    -- Print das vars
    DBMS_OUTPUT.PUT_LINE('Return Code: ' || L_validate_rules_table_rc);
    DBMS_OUTPUT.PUT_LINE('Error Message: ' || L_validate_rules_table_err);
END;
/
COMMIT;
 
DECLARE
    -- PCK_MIGR_VALIDATE_RULES.VALIDATE_RULES_TABLE
    L_validate_rules_table_rc VARCHAR2 (255);
    L_validate_rules_table_err VARCHAR2 (4000);
BEGIN
    -- Valida as rules das tabelas de CTRL
    PCK_MIGR_VALIDATE_RULES.VALIDATE_RULES_TABLE (P_rule_type   => NULL,
                                                  P_rule_id     => NULL,
                                                  P_table_name  => 'ITEM_SUPP_MANU_COUNTRY',
                                                  return_code   => L_validate_rules_table_rc,
                                                  error_message => L_validate_rules_table_err);
    -- Print das vars
    DBMS_OUTPUT.PUT_LINE('Return Code: ' || L_validate_rules_table_rc);
    DBMS_OUTPUT.PUT_LINE('Error Message: ' || L_validate_rules_table_err);
END;
/
COMMIT;
 
SELECT * FROM MIGR_ERROR ORDER BY ERROR_ID DESC;
SELECT * FROM MIGR_RULES WHERE RULE_ID = 2819;
select * from migr_rule_control where table_name = 'ITEM_MASTER' AND error_ind = 'Y';
 
select * from migr_rule_control where table_name = 'ITEM_SUPP_MANU_COUNTRY' AND error_ind = 'Y';
select * from ITEM_SUPP_MANU_COUNTRY_ctrl a WHERE A.PRIMARY_MANU_CTRY_IND = 'Y' AND EXISTS (SELECT 1 FROM ITEM_SUPP_MANU_COUNTRY_CTRL B WHERE B.ITEM = A.ITEM AND B.SUPPLIER = A.SUPPLIER AND B.MANU_COUNTRY_ID <> A.MANU_COUNTRY_ID AND B.PRIMARY_MANU_CTRY_IND = 'Y');
select * from ITEM_SUPP_MANU_COUNTRY_ctrl where item = 007558603;
select * from ITEM_SUPP_COUNTRY_ctrl where item = 007535380;
select * from ITEM_SUPPlier_ctrl where item = 9000000000000000000946300;
 
select item, supplier from ITEM_SUPP_COUNTRY_ctrl where primary_country_ind = 'Y' group by item, supplier having count(1) > 1;
delete from ITEM_SUPP_MANU_COUNTRY_ctrl ismc where not exists (select 1 from ITEM_SUPP_COUNTRY_ctrl isc where B.ITEM = A.ITEM AND B.SUPPLIER = A.SUPPLIER AND