Union of Queries Stored in Variables

[mokee]

I am currently trying to recode a part of package which extracts data from a source system and processes in the datawarehouse. Basically what the code does is:

**If it is a daily processing processing (p_run_type <> 1) and p_processing_location is satisfied, distinct product ids will be inserted into v_table_name (which is basically a variable) from 3 different tables (using Union - to eliminate duplicate records)

**If it is not a daily processing (p_run_type = 1) and p_processing_location is satisfied, the same process will run as above, only, product ids will be inserted into v_table_name from 4 different tables (the same union of 3 tables above will be used plus union of 1 new table (which is only included if it is not a daily processing)

The code works fine, however, it is not well done coz given the current structure, if an update needs to be done, it should in two places - one on the first If condition (for the non daily processing) and another on the 2nd If condition (for the daily processing) which makes it very prone to errors.

The requirement is to rewrite the code in such a way that it is easy to efficiently manage updates and to reduce redundant queries.

I have two options which I am not sure if feasible at all (and considered to be best practice given the scenario). I am not a hard-core programmer and I need some help in determining the best possible option to make the code better:

OPTION A
1) Put the main query (3 tables) into a variable (named main_query) and the small query (1 table) into a variable (named sub_query)
2) If it is adjusted processing, execute main_query union sub_query
3) Else execute main_query

OPTION B

1) Run first the main query regardless of any processing day. This will insert data into v_table_name
2) If it is adjusted, run a query which is something like this

Insert into v_table_name
Select distinct product_ids
from opd_trans_product
where product_ids not in (select product_ids from v_table_name)

Below is the part of the code that needs to be modified:

IF p_run_type = 1 AND (p_processing_location = 'AMS' OR p_processing_location = 'ALL') THEN
EXECUTE IMMEDIATE
'INSERT INTO ' || v_table_name || '
SELECT DISTINCT To_Number(product_id) product_id
FROM opo_enriched_tb_extract tb
WHERE
(
((''' || p_processing_location || '''= ''PAC'' OR ''' || p_processing_location || '''= ''ALL'') AND (processing_location = ''PAC'' AND business_date = To_Date(''' || To_Char(lv_cob_date_PAC, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_id_PAC) || '))
OR ((''' || p_processing_location || '''= ''EUR'' OR ''' || p_processing_location || '''= ''ALL'') AND (processing_location = ''EUR'' AND business_date = To_Date(''' || To_Char(lv_cob_date_EUR, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_id_EUR) || '))
OR ((''' || p_processing_location || '''= ''AMS'' OR ''' || p_processing_location || '''= ''ALL'') AND (processing_location = ''AMS'' AND business_date = To_Date(''' || To_Char(lv_cob_date_AMS, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_id_AMS) || '))
)
AND product_id_type_cd = ''PIDC''
AND product_id IS NOT NULL
AND RTrim(product_id,'' 1234567890'') IS NULL

-- UNDERLYING
UNION
SELECT DISTINCT To_Number(product_underlying_id) product_id
FROM opo_enriched_tb_extract tb
WHERE
(
((''' || p_processing_location || '''= ''PAC'' OR ''' || p_processing_location || '''= ''ALL'') AND (processing_location = ''PAC'' AND business_date = To_Date(''' || To_Char(lv_cob_date_PAC, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_id_PAC) || '))
OR ((''' || p_processing_location || '''= ''EUR'' OR ''' || p_processing_location || '''= ''ALL'') AND (processing_location = ''EUR'' AND business_date = To_Date(''' || To_Char(lv_cob_date_EUR, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_id_EUR) || '))
OR ((''' || p_processing_location || '''= ''AMS'' OR ''' || p_processing_location || '''= ''ALL'') AND (processing_location = ''AMS'' AND business_date = To_Date(''' || To_Char(lv_cob_date_AMS, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_id_AMS) || '))
)
AND product_underlying_id_type_cd = ''PIDC''
AND product_underlying_id IS NOT NULL
AND RTrim(product_underlying_id,'' 1234567890'') IS NULL

-- ISSUER TB PRODUCTS
UNION
SELECT DISTINCT To_Number(ISSUER_NOTL_PRODUCT_ID) product_id
FROM OPO_ENRICHED_ISSUER_TB tb
WHERE
(
((''' || p_processing_location || '''= ''PAC'' OR ''' || p_processing_location || '''= ''ALL'') AND (processing_location = ''PAC'' AND business_date = To_Date(''' || To_Char(lv_cob_date_PAC, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_id_PAC) || '))
OR ((''' || p_processing_location || '''= ''EUR'' OR ''' || p_processing_location || '''= ''ALL'') AND (processing_location = ''EUR'' AND business_date = To_Date(''' || To_Char(lv_cob_date_EUR, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_id_EUR) || '))
OR ((''' || p_processing_location || '''= ''AMS'' OR ''' || p_processing_location || '''= ''ALL'') AND (processing_location = ''AMS'' AND business_date = To_Date(''' || To_Char(lv_cob_date_AMS, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_id_AMS) || '))
)
AND ISSUER_NOTL_PRODUCT_ID_TYPE_CD = ''PIDC''
AND ISSUER_NOTL_PRODUCT_ID IS NOT NULL
AND RTrim(ISSUER_NOTL_PRODUCT_ID,'' 1234567890'') IS NULL

-- POSITION FILES RECORDS
UNION
SELECT DISTINCT To_Number(p_id_c) product_id
FROM insight_security tb
WHERE ref_date = To_Date(''' || To_Char(lv_cob_date, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'')
AND p_id_c IS NOT NULL
AND RTrim(p_id_c,'' 1234567890'') IS NULL

-- TICS TRANS PROD RECORDS
UNION
SELECT DISTINCT To_Number(product_id) product_id
FROM opd_trans_product tp
WHERE
(
((''' || p_processing_location || '''= ''AMS'' OR ''' || p_processing_location || '''= ''ALL'') AND (processing_location = ''AMS'' AND business_date = To_Date(''' || To_Char(lv_cob_date_AMS, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_id_AMS) || '))
)
AND allocation_id_type_cd = ''TICS''
AND product_id IS NOT NULL
AND RTrim(product_id,'' 1234567890'') IS NULL';

ELSE
EXECUTE IMMEDIATE
'INSERT INTO ' || v_table_name || '

SELECT DISTINCT To_Number(product_id) product_id
FROM opo_enriched_tb_extract tb
WHERE
(
((''' || p_processing_location || '''= ''PAC'' OR ''' || p_processing_location || '''= ''ALL'') AND (processing_location = ''PAC'' AND business_date = To_Date(''' || To_Char(lv_cob_date_PAC, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_id_PAC) || '))
OR ((''' || p_processing_location || '''= ''EUR'' OR ''' || p_processing_location || '''= ''ALL'') AND (processing_location = ''EUR'' AND business_date = To_Date(''' || To_Char(lv_cob_date_EUR, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_id_EUR) || '))
OR ((''' || p_processing_location || '''= ''AMS'' OR ''' || p_processing_location || '''= ''ALL'') AND (processing_location = ''AMS'' AND business_date = To_Date(''' || To_Char(lv_cob_date_AMS, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_id_AMS) || '))
)
AND product_id_type_cd = ''PIDC''
AND product_id IS NOT NULL
AND RTrim(product_id,'' 1234567890'') IS NULL

-- UNDERLYING
UNION
SELECT DISTINCT To_Number(product_underlying_id) product_id
FROM opo_enriched_tb_extract tb
WHERE
(
((''' || p_processing_location || '''= ''PAC'' OR ''' || p_processing_location || '''= ''ALL'') AND (processing_location = ''PAC'' AND business_date = To_Date(''' || To_Char(lv_cob_date_PAC, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_id_PAC) || '))
OR ((''' || p_processing_location || '''= ''EUR'' OR ''' || p_processing_location || '''= ''ALL'') AND (processing_location = ''EUR'' AND business_date = To_Date(''' || To_Char(lv_cob_date_EUR, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_id_EUR) || '))
OR ((''' || p_processing_location || '''= ''AMS'' OR ''' || p_processing_location || '''= ''ALL'') AND (processing_location = ''AMS'' AND business_date = To_Date(''' || To_Char(lv_cob_date_AMS, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_id_AMS) || '))
)
AND product_underlying_id_type_cd = ''PIDC''
AND product_underlying_id IS NOT NULL
AND RTrim(product_underlying_id,'' 1234567890'') IS NULL

-- ISSUER TB PRODUCTS
UNION
SELECT DISTINCT To_Number(ISSUER_NOTL_PRODUCT_ID) product_id
FROM OPO_ENRICHED_ISSUER_TB tb
WHERE
(
((''' || p_processing_location || '''= ''PAC'' OR ''' || p_processing_location || '''= ''ALL'') AND (processing_location = ''PAC'' AND business_date = To_Date(''' || To_Char(lv_cob_date_PAC, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_id_PAC) || '))
OR ((''' || p_processing_location || '''= ''EUR'' OR ''' || p_processing_location || '''= ''ALL'') AND (processing_location = ''EUR'' AND business_date = To_Date(''' || To_Char(lv_cob_date_EUR, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_id_EUR) || '))
OR ((''' || p_processing_location || '''= ''AMS'' OR ''' || p_processing_location || '''= ''ALL'') AND (processing_location = ''AMS'' AND business_date = To_Date(''' || To_Char(lv_cob_date_AMS, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'') AND run_id = ' || To_Char(lv_run_id_AMS) || '))
)
AND ISSUER_NOTL_PRODUCT_ID_TYPE_CD = ''PIDC''
AND ISSUER_NOTL_PRODUCT_ID IS NOT NULL
AND RTrim(ISSUER_NOTL_PRODUCT_ID,'' 1234567890'') IS NULL

-- POSITION FILES RECORDS
UNION
SELECT DISTINCT To_Number(p_id_c) product_id
FROM insight_security tb
WHERE ref_date = To_Date(''' || To_Char(lv_cob_date, 'MM.DD.YYYY') || ''', ''MM.DD.YYYY'')
AND p_id_c IS NOT NULL
AND RTrim(p_id_c,'' 1234567890'') IS NULL';

END IF;
COMMIT;

I am hoping that you could help me out on this.