DMF.sql

Source: DMF/PuC/AIF/DMF.sql

 
--1
BEGIN
    -- Cria o procedure que vai criar as DSA tables
    PCK_MIGR_GEN.GEN_CREATE_DSA_TABLES (I_table_name => 'ORI_PROMO_DETAIL');
END;
/
--2
BEGIN
    -- Procedure que cria as DSA (STG) tables
    CREATE_DSA_TABLES(DROP_TABLE => 'Y');
END;
/
--3
BEGIN
    -- Cria o procedure que vai criar as CTRL tables (MIGR)
    PCK_MIGR_GEN.GEN_CREATE_CONTROL_TABLES (P_table_name => 'ORI_PROMO_DETAIL'); --Pode ter mais parametros
END;
/
--4
BEGIN
    -- Procedure que cria as CTRL tables
    CREATE_CTRL_TABLES;
END;
 
 
-- CRIA PROCEDURE DA DSA PARA CTRL
 
BEGIN
    -- Cria o procedure que vai passar os dados das DSA tables para as CTRL tables
    PCK_MIGR_GEN.GEN_LOAD_CTRL_TABLES (P_table_name => 'PARTNER_ORG_UNIT');
END;
/
 
-- PROCEDURE PARA EXECUTAR O ENVIO DA STG PARA CTRL
BEGIN
    -- Procedure que passa os dados das DSA tables para as CTRL tables
    LOAD_CTRL_TABLES;
END;
/
 
 
-- VALIDATE RULES
 
DECLARE
    
 
    -- PCK_MIGR_VALIDATE_RULES.VALIDATE_RULES_TABLE
    L_validate_rules_table_rc VARCHAR2 (255); -- Guarda o resultado da execution
    L_validate_rules_table_err VARCHAR2 (4000); -- Guarda a mensagem de erro
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_LOC',
                                                  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;
/
 
ALTER SESSION DISABLE PARALLEL DML;
SET SERVEROUTPUT ON;
 
DECLARE
    -- PCK_MIGR_VALIDATE_RULES.VALIDATE_RULES_TABLE
    L_validate_rules_table_rc VARCHAR2 (255); -- Guarda o resultado da execution
    L_validate_rules_table_err VARCHAR2 (4000); -- Guarda a mensagem de erro
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  => 'PARTNER_ORG_UNIT',
                                                  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;
/
 
 
--- CREATE FILE
DECLARE
    L_error_message VARCHAR2(4000);
BEGIN
    CREATE_FILE (
                 I_string_table_query => 'SUPS',
                 I_replace_columns    => 'SUP_NAME,SUP_NAME_SECONDARY,TERMS',
                 O_error_message      => L_error_message
                );
                
    DBMS_OUTPUT.PUT_LINE(L_error_message);
END;
/
 
DECLARE
    L_error_message VARCHAR2(4000);
BEGIN
    CREATE_FILE (
                 I_string_table_query => 'ADDR',
                 I_replace_columns    => 'ADD_1,CITY,CONTACT_NAME',
                 O_error_message      => L_error_message
                );
                
    DBMS_OUTPUT.PUT_LINE(L_error_message);
END;
/
 
DECLARE
    L_error_message VARCHAR2(4000);
BEGIN
    CREATE_FILE (
                 I_string_table_query => 'PARTNER_ORG_UNIT',
                 I_replace_columns    => '',
                 O_error_message      => L_error_message
                );
                
    DBMS_OUTPUT.PUT_LINE(L_error_message);
END;
/
 
ALTER SESSION DISABLE PARALLEL DML;
SET SERVEROUTPUT ON;
 
DECLARE
    -- PCK_MIGR_VALIDATE_RULES.VALIDATE_RULES_TABLE
    L_validate_rules_table_rc VARCHAR2 (255); -- Guarda o resultado da execution
    L_validate_rules_table_err VARCHAR2 (4000); -- Guarda a mensagem de erro
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  => 'UDA_ITEM_LOV',
                                                  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;
/
 
DECLARE
    L_error_message VARCHAR2(4000);
BEGIN
    CREATE_FILE_APS (
                 I_string_table_query => 'ITEM_MASTER_CFA_EXT',
                 I_replace_columns    => '',
                 O_error_message      => L_error_message
                );
                
    DBMS_OUTPUT.PUT_LINE(L_error_message);
END;
/
 
ALTER SESSION DISABLE PARALLEL DML;
SET SERVEROUTPUT ON;
 
DECLARE
    -- PCK_MIGR_VALIDATE_RULES.VALIDATE_RULES_TABLE
    L_validate_rules_table_rc VARCHAR2 (255); -- Guarda o resultado da execution
    L_validate_rules_table_err VARCHAR2 (4000); -- Guarda a mensagem de erro
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_LOC_SOH',
                                                  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;
/
 
select ctrl_status, count(1) from UDA_ITEM_LOV_ctrl group by ctrl_status;
select count(1) from UDA_ITEM_LOV_ctrl;
SELECT count(1) FROM ITEM_MASTER_CFA_EXT_CTRL;
SELECT *FROM ITEM_MASTER_CFA_EXT_CTRL;
 
SELECT * FROM MIGR_ERROR ORDER BY ERROR_ID DESC;
SELECT * FROM MIGR_RULES WHERE RULE_ID = 2725;
 
select * from migr_rule_control where table_name = 'ITEM_LOC_SOH' AND error_ind = 'Y'; --ver se as regras deram erro 
UPDATE MIGR_RULES SET STATUS = 'I' WHERE RULE_ID = 2079 ;
 
select * from migr_rule_control order by exec_timestamp desc;
 
update RPM_ZONE_LOCATION_ctrl set ctrl_status = 'I';
select * from migr_destinations_extract where table_name = 'RPM_ZONE_LOCATION';
select * from migr_destinations_extract where table_name = 'COST_ZONE';
 
DELETE FROM migr_destinations_extract where table_name = 'RPM_ZONE_LOCATION' 
INSERT INTO migr_destinations_extract SELECT 'RPM_ZONE_LOCATION','LOCATION',2,1,NULL FROM DUAL
INSERT INTO migr_destinations_extract SELECT 'RPM_ZONE_LOCATION','LOC_TYPE',2,2,NULL FROM DUAL
INSERT INTO migr_destinations_extract SELECT 'RPM_ZONE_LOCATION','ZONE_DISPLAY_ID',2,3,NULL FROM DUAL