/*
############################################################
# Purpose : SQL Script for updating tables for DOX.
# Author  : Artem Kushnir
############################################################
*/
DECLARE
  type t_string IS TABLE OF VARCHAR2(50);
  t_table_name t_string        := t_string('DOX_STAGE');

  type t_column IS TABLE OF  t_string;
  t_columns_name t_column        := t_column( 
                                t_string('AGREEMENT_TEMPLATE', 'VARCHAR2(100 CHAR)')
                                );
  
  V_TABLE_OWNER      VARCHAR2(100) := 'FDD';
  v_table_name       VARCHAR2(100);
  v_Column_Name      VARCHAR2(100);
  v_Constraint		 VARCHAR2(100) := 'DOX_STAGE_PK'; 
  v_alter_table_stmt VARCHAR2(3000);
  v_cnt              NUMBER;
  v_sel_qry          VARCHAR2(1000);
BEGIN
  FOR i IN 1..t_table_name.count
  LOOP
    v_table_name := t_table_name(i);
    v_sel_qry    := 'SELECT COUNT(CONSTRAINT_NAME) FROM DBA_CONSTRAINTS WHERE OWNER = :tblowner AND TABLE_NAME = :tblname AND CONSTRAINT_NAME = :const';
    EXECUTE IMMEDIATE v_sel_qry INTO v_cnt USING v_Table_Owner,v_Table_Name, v_Constraint;
    
    IF v_cnt = 0 THEN
       
    /*ADDING COLUMN*/
        FOR i IN 1..t_columns_name.count LOOP    
            v_Column_Name := t_columns_name(i)(1);
            v_sel_qry     := 'SELECT COUNT(COLUMN_NAME) FROM DBA_IND_COLUMNS WHERE TABLE_OWNER = :tblowner AND TABLE_NAME = :tblname AND COLUMN_NAME = :colname';
            EXECUTE IMMEDIATE v_sel_qry INTO v_cnt USING v_Table_Owner,v_Table_Name, v_Column_Name;
            IF v_cnt              = 0 THEN
                --v_alter_table_stmt := 'ALTER TABLE ' || v_table_owner || '.' || v_table_name || ' DROP CONSTRAINT '||v_Constraint;
                --EXECUTE IMMEDIATE v_alter_table_stmt;
                v_alter_table_stmt := 'ALTER TABLE ' || v_table_owner || '.' || v_table_name || ' ADD CONSTRAINT('||v_Constraint||' PRIMARY KEY (BATCH_ID , AGRMT_ID, NETTCOL_ID, CRXM_ID, COMP_CODE, CRXMCNTRY, BRANCH_ID, PRODUCT_ID, CP_BRANCH, IFRS_NET_IND, RELATIONSHIP_TYPE, IFRS_DOX_TYPE, AGREEMENT_TEMPLATE) USING INDEX TABLESPACE REFERENCE_DATA';
                EXECUTE IMMEDIATE v_alter_table_stmt;
                DBMS_OUTPUT.PUT_LINE('Table '|| v_Table_Owner||'.'||v_Table_Name||' column '|| v_Column_Name || ' constraint ' || v_Constraint || ' added');
            END IF;
        END LOOP;
      /*ADDING COLUMN*/
      
    
    ELSE
      DBMS_OUTPUT.PUT_LINE('Constraint '|| v_Constraint||' not exists');
    END IF;
  END LOOP;
END;
/