SQL & PL/SQL

Hi Experts,

We have a requirement to archive and purge the tables dynamically based on the control table input.
For that we have to design a control table to gather the necessary information and passed to generate the queries.

I have designed the table as below.But in this case I am not able to handle the parent and child relation ship.

Suppose one table needs to be archived and purged and that table is parent table and it is having 2 child tables,
so first required data will be inserted into target table and delete from source parent and child tables.
so before deleting from parent we have to delete data from all 2 child tables.

Suppose one table needs to be purged and that table is parent table and it is having 5 child tables,
so before deleting from parent we have to delete data from all 5 child tables.

To handle this scenario how can I design my control table.

For archive and purge the query like this.

INSERT INTO towner_name.ttable_name
(SELECT * FROM sowner_name.stable_name WHERE condition_column<=(sysdate-30));
DELETE FROM sowner_name.stable_name WHERE condition_column<=(sysdate-30);

for purge the quey is like this.

DELETE FROM sowner_name.stable_name WHERE condition_column<=(sysdate-30);

This is my control table and I have 300 tables list to archive and purge.

CID  SOWNER_NAME STABLE_NAME       TOWNER_NAME   TTABLE_NAME       CONDITION_COLUMN PERIOD UNIT TYPE
1     wedb_au    OFFER_HEADER       wedb_au      OFFER_HEADER       LAST_DATE        30     D    A
1     wedb_sa    OFFER_CUSTOMER     wedb_sa      OFFER_CUSTOMER     LAST_DATE        60     D    A
1     wedb_au    OFFER_SERVICE                                      LAST_DATE         1     Y    P 
1     wedb_us    OFFER_CUSTOMER                                     LAST_DATE        90     D    P
1     wedb_cn    OFFER_CARDS                                        UPDATE_DT        2      Y    P
2     wedb_au    ORDER_HEAD         wedb_au      ORDER_HEAD         LAST_DATE        120    D    A 
2     wedb_us    ORDER_CUSTOMER     wedb_us      ORDER_CUSTOMER     LAST_DATE        150    D    A
2     wedb_sa    ORDER_HEAD         wedb_sa      ORDER_HEAD         CREATION_DT      1      Y    A
3     wedb_us    DELIVERY_HEAD      wedb_us      DELIVERY_HEAD      UPDATE_DT        50     D    A
3     wedb_au    DELIVERY_CARDS     wedb_au      DELIVERY_CARDS     UPDATE_DT        200    D    A
3     wedb_au    DELIVERY_SERVICE   wedb_au      DELIVERY_SERVICE   LAST_DT          100    D    A

WHERE TYPE=P means insert and delete
TYPE=A means only delete

wedb_au.OFFER_HEADER is Parent Table.
child tables for wedb_au.OFFER_HEADER are wedb_au.OFFER_SERVICE,wedb_au.OFFER_BODY,wedb_au.OFFER_EMAIL,OFFER_TAX.

wedb_au.OFFER_SERVICE is child table and parent for this table is wedb_au.OFFER_HEADER

wedb_sa.OFFER_CUSTOMER Stand alone table no relationship

wedb_us.OFFER_CUSTOMER Stand alone table no relationship

wedb_cn.OFFER_CARDS is parent table.
child tables for wedb_cn.OFFER_CARDS are wedb_cn.OFFER_OPTION,wedb_cn.OFFER_SERIES

wedb_au.ORDER_HEAD is parent table.
child tables for wedb_au.ORDER_HEAD are wedb_au.ORDER_CUSTOMER,wedb_au.ORDER_SERVICE

wedb_us.ORDER_CUSTOMER is parent table.
child tables for wedb_us.ORDER_CUSTOMER are wedb_us.ORDER_TAx,wedb_us.ORDER_SERIES.

wedb_sa.ORDER_HEAD is stand alone table no relationship.

wedb_us.DELIVERY_HEAD is parent table
child tables for wedb_us.DELIVERY_HEAD are wedb_us.DELIVERY_SERVICE,wedb_us.DELIVERY_BODY

wedb_au.DELIVERY_CARDS is child table and parent for this table is wedb_au.DELIVERY_OPTION

wedb_au.DELIVERY_SERVICE is child table and parent for this table is wedb_au.DELIVERY_HEAD

Please help me to design the control table.

Thanks in advance.