The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE add_insert_clause_to_list ( p_dimension_required IN VARCHAR2,
p_prefix IN VARCHAR2,
p_rownum IN OUT NOCOPY NUMBER,
p_column_name IN VARCHAR2)
IS
BEGIN
IF (p_dimension_required = 'Y') THEN
ad_ddl.build_statement(' ' || p_prefix || p_column_name || ', ', p_rownum); p_rownum:=p_rownum+1;
END add_insert_clause_to_list;
PROCEDURE build_insert_clause_list (p_rownum IN OUT NOCOPY NUMBER,
p_prefix IN VARCHAR2)
IS
l_dim_info gcs_utility_pkg.t_hash_gcs_dimension_info := gcs_utility_pkg.g_gcs_dimension_info;
add_insert_clause_to_list(gcs_utility_pkg.get_dimension_required('CHANNEL_ID'),
p_prefix,
p_rownum,
'channel_id');
add_insert_clause_to_list(gcs_utility_pkg.get_dimension_required('CUSTOMER_ID'),
p_prefix,
p_rownum,
'customer_id');
add_insert_clause_to_list(gcs_utility_pkg.get_dimension_required('FINANCIAL_ELEM_ID'),
p_prefix,
p_rownum,
'financial_elem_id');
add_insert_clause_to_list(gcs_utility_pkg.get_dimension_required('LINE_ITEM_ID'),
p_prefix,
p_rownum,
'line_item_id');
add_insert_clause_to_list(gcs_utility_pkg.get_dimension_required('NATURAL_ACCOUNT_ID'),
p_prefix,
p_rownum,
'natural_account_id');
add_insert_clause_to_list(gcs_utility_pkg.get_dimension_required('PRODUCT_ID'),
p_prefix,
p_rownum,
'product_id');
add_insert_clause_to_list(gcs_utility_pkg.get_dimension_required('PROJECT_ID'),
p_prefix,
p_rownum,
'project_id');
add_insert_clause_to_list(gcs_utility_pkg.get_dimension_required('TASK_ID'),
p_prefix,
p_rownum,
'task_id');
add_insert_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM10_ID'),
p_prefix,
p_rownum,
'user_dim10_id');
add_insert_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM1_ID'),
p_prefix,
p_rownum,
'user_dim1_id');
add_insert_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM2_ID'),
p_prefix,
p_rownum,
'user_dim2_id');
add_insert_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM3_ID'),
p_prefix,
p_rownum,
'user_dim3_id');
add_insert_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM4_ID'),
p_prefix,
p_rownum,
'user_dim4_id');
add_insert_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM5_ID'),
p_prefix,
p_rownum,
'user_dim5_id');
add_insert_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM6_ID'),
p_prefix,
p_rownum,
'user_dim6_id');
add_insert_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM7_ID'),
p_prefix,
p_rownum,
'user_dim7_id');
add_insert_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM8_ID'),
p_prefix,
p_rownum,
'user_dim8_id');
add_insert_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM9_ID'),
p_prefix,
p_rownum,
'user_dim9_id');
END build_insert_clause_list;
PROCEDURE add_select_clause_to_list ( p_dimension_required IN VARCHAR2,
p_table_alias IN VARCHAR2,
p_rownum IN OUT NOCOPY NUMBER,
p_column_name IN VARCHAR2)
IS
BEGIN
IF (p_dimension_required = 'Y') THEN
ad_ddl.build_statement(' , ' || p_table_alias || p_column_name, p_rownum); p_rownum := p_rownum +1;
END add_select_clause_to_list;
PROCEDURE build_select_clause_list (p_rownum IN OUT NOCOPY NUMBER,
p_table_alias IN VARCHAR2)
IS
l_dim_info gcs_utility_pkg.t_hash_gcs_dimension_info := gcs_utility_pkg.g_gcs_dimension_info;
add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('CHANNEL_ID'),
p_table_alias,
p_rownum,
'channel_id');
add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('CUSTOMER_ID'),
p_table_alias,
p_rownum,
'customer_id');
add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('FINANCIAL_ELEM_ID'),
p_table_alias,
p_rownum,
'financial_elem_id');
add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('LINE_ITEM_ID'),
p_table_alias,
p_rownum,
'line_item_id');
add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('NATURAL_ACCOUNT_ID'),
p_table_alias,
p_rownum,
'natural_account_id');
add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('PRODUCT_ID'),
p_table_alias,
p_rownum,
'product_id');
add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('PROJECT_ID'),
p_table_alias,
p_rownum,
'project_id');
add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('TASK_ID'),
p_table_alias,
p_rownum,
'task_id');
add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM10_ID'),
p_table_alias,
p_rownum,
'user_dim10_id');
add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM1_ID'),
p_table_alias,
p_rownum,
'user_dim1_id');
add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM2_ID'),
p_table_alias,
p_rownum,
'user_dim2_id');
add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM3_ID'),
p_table_alias,
p_rownum,
'user_dim3_id');
add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM4_ID'),
p_table_alias,
p_rownum,
'user_dim4_id');
add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM5_ID'),
p_table_alias,
p_rownum,
'user_dim5_id');
add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM6_ID'),
p_table_alias,
p_rownum,
'user_dim6_id');
add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM7_ID'),
p_table_alias,
p_rownum,
'user_dim7_id');
add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM8_ID'),
p_table_alias,
p_rownum,
'user_dim8_id');
add_select_clause_to_list(gcs_utility_pkg.get_dimension_required('USER_DIM9_ID'),
p_table_alias,
p_rownum,
'user_dim9_id');
END build_select_clause_list;
build_select_clause_list(r,'b.');
build_select_clause_list(r,'src_');
build_select_clause_list(r,'tgt_');
build_select_clause_list(r, 'off_');
ad_ddl.build_statement(' g_core_insert_stmt VARCHAR2(2000) := ', r); r:=r+1;
ad_ddl.build_statement(' ''INSERT INTO gcs_entries_gt( ', r); r:=r+1;
ad_ddl.build_statement(' ''SELECT :rid , ', r); r:=r+1;
ad_ddl.build_statement(' -- Generated SQL statement to insert data into gcs_entry_lines from gcs_entries_gt ', r); r:=r+1;
ad_ddl.build_statement(' -- p_row_count: #of rows inserted ', r); r:=r+1;
ad_ddl.build_statement(' -- Generated SQL statement to insert data into gcs_entry_lines from gcs_entries_gt ', r); r:=r+1;
ad_ddl.build_statement(' -- p_row_count: #of rows inserted ', r); r:=r+1;
ad_ddl.build_statement(' select ''N'' ', r); r:=r+1;
ad_ddl.build_statement(' insert into gcs_entry_lines ', r); r:=r+1;
build_insert_clause_list(p_rownum => r,
p_prefix => null);
ad_ddl.build_statement(' last_updated_by, ', r); r:=r+1;
ad_ddl.build_statement(' last_update_date, ', r); r:=r+1;
ad_ddl.build_statement(' last_update_login ', r); r:=r+1;
ad_ddl.build_statement(' SELECT p_entry_id, ', r); r:=r+1;
build_insert_clause_list(p_rownum => r,
p_prefix => 'geg.tgt_');
build_group_by_clause(r,'geg.tgt_', 'insertassignment');
ad_ddl.build_statement(' --check number of rows inserted ', r); r:=r+1;
ad_ddl.build_statement(' --insert rows if the offset flag was used ', r); r:=r+1;
ad_ddl.build_statement(' insert into gcs_entry_lines ', r); r:=r+1;
build_insert_clause_list(p_rownum => r,
p_prefix => null);
ad_ddl.build_statement(' last_updated_by, ', r); r:=r+1;
ad_ddl.build_statement(' last_update_date, ', r); r:=r+1;
ad_ddl.build_statement(' last_update_login ', r); r:=r+1;
ad_ddl.build_statement(' SELECT p_entry_id, ', r); r:=r+1;
build_insert_clause_list(p_rownum => r,
p_prefix => 'geg.off_');
build_group_by_clause(r,'geg.off_', 'insertassignment');
ad_ddl.build_statement(' insert into gcs_entry_lines ', r); r:=r+1;
build_insert_clause_list(p_rownum => r,
p_prefix => null);
ad_ddl.build_statement(' last_updated_by, ', r); r:=r+1;
ad_ddl.build_statement(' last_update_date, ', r); r:=r+1;
ad_ddl.build_statement(' last_update_login ', r); r:=r+1;
ad_ddl.build_statement(' SELECT p_entry_id, ', r); r:=r+1;
build_insert_clause_list(p_rownum => r,
p_prefix => 'geg.tgt_');
build_group_by_clause(r,'geg.tgt_', 'insertassignment');
ad_ddl.build_statement(' --check number of rows inserted ', r); r:=r+1;
ad_ddl.build_statement(' --insert rows if the offset flag was used ', r); r:=r+1;
ad_ddl.build_statement(' insert into gcs_entry_lines ', r); r:=r+1;
build_insert_clause_list(p_rownum => r,
p_prefix => null);
ad_ddl.build_statement(' last_updated_by, ', r); r:=r+1;
ad_ddl.build_statement(' last_update_date, ', r); r:=r+1;
ad_ddl.build_statement(' last_update_login ', r); r:=r+1;
ad_ddl.build_statement(' SELECT p_entry_id, ', r); r:=r+1;
build_insert_clause_list(p_rownum => r,
p_prefix => 'geg.off_');
build_group_by_clause(r,'geg.off_', 'insertassignment');