[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_NM_Trans_Data
( p_source_id IN NUMBER,
p_trans_id IN NUMBER,
p_program_name IN VARCHAR2,
p_request_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2
)
AS
l_trans_target VARCHAR2(30);
l_insert_stmt VARCHAR2(3000);
l_select_stmt VARCHAR2(3000);
SELECT column_name
FROM all_tab_columns
WHERE table_name = p_table_name
AND owner = p_owner
AND column_name not in ('SECURITY_GROUP_ID', 'OBJECT_VERSION_NUMBER', 'WORKER_ID', 'LAST_UPDATE_DATE',
'LAST_UPDATED_BY', 'CREATION_DATE', 'CREATED_BY', 'LAST_UPDATE_LOGIN', 'REQUEST_ID',
'PROGRAM_APPLICATION_ID', 'PROGRAM_ID', 'PROGRAM_UPDATE_DATE', 'TXN_DATE');
'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_nm_trans_data.begin',
'Start of the procedure JTY_ASSIGN_BULK_PUB.insert_nm_trans_data ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
SELECT tup.batch_nm_trans_table_name
INTO l_trans_target
FROM jty_trans_usg_pgm_details tup
WHERE tup.source_id = p_source_id
AND tup.trans_type_id = p_trans_id
AND tup.program_name = p_program_name;
SELECT incr_reassign_sql
INTO l_seeded_sql
FROM jty_trans_usg_pgm_sql tus
WHERE tus.source_id = p_source_id
AND tus.trans_type_id = p_trans_id
AND tus.program_name = p_program_name
AND tus.enabled_flag = 'Y';
/* Form the insert statement to insert transaction objects into TRANS table */
l_insert_stmt := 'INSERT /*+ APPEND PARALLEL(' || l_trans_target || ') */ INTO ' || l_trans_target || '(';
l_select_stmt := '(SELECT ';
l_insert_stmt := l_insert_stmt || g_new_line || l_indent || column_names.column_name;
l_select_stmt := l_select_stmt || g_new_line || l_indent || column_names.column_name;
l_insert_stmt := l_insert_stmt || g_new_line || l_indent || ',' || column_names.column_name;
l_select_stmt := l_select_stmt || g_new_line || l_indent || ',' || column_names.column_name;
l_insert_stmt := l_insert_stmt || g_new_line || l_indent || ',LAST_UPDATE_DATE ' ||
g_new_line || l_indent || ',LAST_UPDATED_BY ' ||
g_new_line || l_indent || ',CREATION_DATE ' ||
g_new_line || l_indent || ',CREATED_BY ' ||
g_new_line || l_indent || ',LAST_UPDATE_LOGIN ' ||
g_new_line || l_indent || ',REQUEST_ID ' ||
g_new_line || l_indent || ',PROGRAM_APPLICATION_ID ' ||
g_new_line || l_indent || ',PROGRAM_ID ' ||
g_new_line || l_indent || ',PROGRAM_UPDATE_DATE ' ||
g_new_line || l_indent || ',WORKER_ID ' ||
g_new_line || l_indent || ',TXN_DATE ' ||
g_new_line || ')';
l_select_stmt := l_select_stmt || g_new_line || l_indent || ',''' || l_sysdate || '''' ||
g_new_line || l_indent || ',''' || g_user_id || '''' ||
g_new_line || l_indent || ',''' || l_sysdate || '''' ||
g_new_line || l_indent || ',''' || g_user_id || '''' ||
g_new_line || l_indent || ',''' || g_login_id || '''' ||
g_new_line || l_indent || ',''' || p_request_id || '''' ||
g_new_line || l_indent || ',''' || g_appl_id || '''' ||
g_new_line || l_indent || ',''' || g_program_id || '''' ||
g_new_line || l_indent || ',''' || l_sysdate || '''' ||
g_new_line || l_indent || ',1' ||
g_new_line || l_indent || ',''' || l_sysdate || '''';
l_final_sql := l_insert_stmt || l_select_stmt ||
g_new_line || 'FROM ( ' ||
g_new_line || l_seeded_sql ||
g_new_line || ' ) ) ';
'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_nm_trans_data.final_sql',
substr('Insert satement : ' || l_final_sql, 1, 4000));
'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_nm_trans_data.num_rows',
'Number of rows inserted : ' || SQL%ROWCOUNT);
'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_nm_trans_data.end',
'End of the procedure JTY_ASSIGN_BULK_PUB.insert_nm_trans_data ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_nm_trans_data.l_seed_data_notfound',
x_msg_data);
x_msg_data := 'JTY_ASSIGN_BULK_PUB.insert_nm_trans_data: SCHEMA NAME NOT FOUND CORRESPONDING TO JTF APPLICATION. ';
'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_nm_trans_data.l_schema_notfound',
x_msg_data);
'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_nm_trans_data.no_data_found',
x_msg_data);
'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_nm_trans_data.other',
substr(x_msg_data, 1, 4000));
END Insert_NM_Trans_Data;
PROCEDURE Insert_Trans_Data
( p_source_id IN NUMBER,
p_trans_id IN NUMBER,
p_program_name IN VARCHAR2,
p_mode IN VARCHAR2,
p_where IN VARCHAR2,
p_no_of_workers IN NUMBER,
p_request_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2,
p_oic_mode IN VARCHAR2 DEFAULT 'NOOIC'
)
AS
l_trans_target VARCHAR2(30);
l_insert_stmt VARCHAR2(3000);
l_select_stmt VARCHAR2(3000);
SELECT column_name
FROM all_tab_columns
WHERE table_name = p_table_name
AND owner = p_owner
AND column_name not in ('SECURITY_GROUP_ID', 'OBJECT_VERSION_NUMBER', 'WORKER_ID', 'LAST_UPDATE_DATE',
'LAST_UPDATED_BY', 'CREATION_DATE', 'CREATED_BY', 'LAST_UPDATE_LOGIN', 'REQUEST_ID',
'PROGRAM_APPLICATION_ID', 'PROGRAM_ID', 'PROGRAM_UPDATE_DATE', 'TXN_DATE');
'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.begin',
'Start of the procedure JTY_ASSIGN_BULK_PUB.insert_trans_data ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
ELSIF (p_oic_mode = 'INSERT') THEN
IF ( (p_no_of_workers < 1)) THEN
RAISE L_INVALID_WORKERS;
SELECT decode(p_mode, 'TOTAL', tup.batch_trans_table_name
, 'INCREMENTAL', tup.batch_nm_trans_table_name
, 'DATE EFFECTIVE', tup.batch_dea_trans_table_name)
INTO l_trans_target
FROM jty_trans_usg_pgm_details tup
WHERE tup.source_id = p_source_id
AND tup.trans_type_id = p_trans_id
AND tup.program_name = p_program_name;
SELECT decode(p_mode, 'TOTAL', tus.batch_total_sql
, 'INCREMENTAL', tus.batch_incr_sql
, 'DATE EFFECTIVE', decode(tus.use_total_for_dea_flag, 'Y', tus.batch_total_sql, tus.batch_dea_sql))
INTO l_seeded_sql
FROM jty_trans_usg_pgm_sql tus
WHERE tus.source_id = p_source_id
AND tus.trans_type_id = p_trans_id
AND tus.program_name = p_program_name
AND tus.enabled_flag = 'Y';
'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.trans_table_name',
'TRANS table name : ' || l_trans_target);
'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.trans_table_name',
' Seeded SQL : ' || substr(l_seeded_sql, 1, 4000));
'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.trans_table_name',
' Where clause : ' || substr(p_where, 1, 4000));
/* Form the insert statement to insert transaction objects into TRANS table */
IF p_oic_mode = 'INSERT'
THEN
l_insert_stmt := 'INSERT INTO ' || l_trans_target || '(';
l_select_stmt := '(SELECT ';
l_insert_stmt := 'INSERT /*+ APPEND PARALLEL(' || l_trans_target || ') */ INTO ' || l_trans_target || '(';
l_select_stmt := '(SELECT /*+ PARALLEL */';
l_insert_stmt := l_insert_stmt || g_new_line || l_indent || column_names.column_name;
l_select_stmt := l_select_stmt || g_new_line || l_indent || column_names.column_name;
l_insert_stmt := l_insert_stmt || g_new_line || l_indent || ',' || column_names.column_name;
l_select_stmt := l_select_stmt || g_new_line || l_indent || ',' || column_names.column_name;
l_insert_stmt := l_insert_stmt || g_new_line || l_indent || ',LAST_UPDATE_DATE ' ||
g_new_line || l_indent || ',LAST_UPDATED_BY ' ||
g_new_line || l_indent || ',CREATION_DATE ' ||
g_new_line || l_indent || ',CREATED_BY ' ||
g_new_line || l_indent || ',LAST_UPDATE_LOGIN ' ||
g_new_line || l_indent || ',REQUEST_ID ' ||
g_new_line || l_indent || ',PROGRAM_APPLICATION_ID ' ||
g_new_line || l_indent || ',PROGRAM_ID ' ||
g_new_line || l_indent || ',PROGRAM_UPDATE_DATE ' ||
g_new_line || l_indent || ',WORKER_ID ' ||
g_new_line || l_indent || ',TXN_DATE ' ||
g_new_line || ')';
l_select_stmt := l_select_stmt || g_new_line || l_indent || ',''' || l_sysdate || '''' ||
g_new_line || l_indent || ',''' || g_user_id || '''' ||
g_new_line || l_indent || ',''' || l_sysdate || '''' ||
g_new_line || l_indent || ',''' || g_user_id || '''' ||
g_new_line || l_indent || ',''' || g_login_id || '''' ||
g_new_line || l_indent || ',''' || p_request_id || '''' ||
g_new_line || l_indent || ',''' || g_appl_id || '''' ||
g_new_line || l_indent || ',''' || g_program_id || '''' ||
g_new_line || l_indent || ',''' || l_sysdate || '''';
l_select_stmt := l_select_stmt || g_new_line || l_indent || ',1';
l_select_stmt := l_select_stmt || g_new_line || l_indent || ',mod(trans_object_id ,' || p_no_of_workers || ') + 1';
ELSIF p_oic_mode = 'INSERT'
THEN
l_select_stmt := l_select_stmt || g_new_line || l_indent || ',mod(floor(trans_object_id/1000) ,' || p_no_of_workers || ') + 1';
l_select_stmt := l_select_stmt || g_new_line || l_indent || ',txn_date';
l_select_stmt := l_select_stmt || g_new_line || l_indent || ',''' || l_sysdate || '''';
l_final_sql := l_insert_stmt || l_select_stmt ||
g_new_line || 'FROM ( ' ||
g_new_line || l_seeded_sql ||
g_new_line || ' ) ';
'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.final_sql',
substr('Insert satement : ' || l_final_sql, 1, 4000));
/* Insert all the transaction objects into the TRANS table */
EXECUTE IMMEDIATE l_final_sql;
'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.num_rows',
'Number of rows inserted : ' || SQL%ROWCOUNT);
'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.end',
'End of the procedure JTY_ASSIGN_BULK_PUB.insert_trans_data ' || to_char(sysdate,'dd-mm-rrrr HH24:MI:SS'));
x_msg_data := 'JTY_ASSIGN_BULK_PUB.insert_trans_data: Invalid number of workers : Valid range from 1 - 10';
'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.l_invalid_workers',
x_msg_data);
'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.l_seed_data_notfound',
x_msg_data);
x_msg_data := 'JTY_ASSIGN_BULK_PUB.insert_trans_data: SCHEMA NAME NOT FOUND CORRESPONDING TO JTF APPLICATION. ';
'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.l_schema_notfound',
x_msg_data);
'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.no_data_found',
x_msg_data);
'jtf.plsql.JTY_ASSIGN_BULK_PUB.insert_trans_data.other',
substr(x_msg_data, 1, 4000));
END Insert_Trans_Data;
SELECT
decode(p_mode, 'TOTAL', tup.batch_trans_table_name
, 'INCREMENTAL', tup.batch_nm_trans_table_name
, 'DATE EFFECTIVE', tup.batch_dea_trans_table_name)
,tup.batch_match_table_name
,tup.batch_unique_match_table_name
,tup.batch_winner_table_name
,tup.batch_unique_winner_table_name
,tup.batch_l1_winner_table_name
,tup.batch_l2_winner_table_name
,tup.batch_l3_winner_table_name
,tup.batch_l4_winner_table_name
,tup.batch_l5_winner_table_name
,tup.batch_wt_winner_table_name
INTO
l_trans_target
,l_match_target
,l_umatch_target
,l_winner_target
,l_uwinner_target
,l_L1_target
,l_L2_target
,l_L3_target
,l_L4_target
,l_L5_target
,l_WT_target
FROM
jty_trans_usg_pgm_details tup
WHERE tup.source_id = p_source_id
AND tup.trans_type_id = p_trans_id
AND tup.program_name = p_program_name;
DELETE jty_changed_terrs
WHERE source_id = p_source_id
AND star_request_id IS NOT NULL;
DELETE jty_changed_terrs
WHERE source_id = p_source_id
AND tap_request_id IS NOT NULL
AND tap_request_id <> p_request_id;
SELECT 1
INTO l_dummy
FROM jty_changed_terrs
WHERE source_id = p_source_id
AND tap_request_id = p_request_id
AND rownum <= 1;
UPDATE jty_changed_terrs
SET tap_request_id = p_request_id
WHERE source_id = p_source_id
AND star_request_id IS NOT NULL;
SELECT
decode(p_mode, 'TOTAL', tup.batch_trans_table_name,
'INCREMENTAL', tup.batch_nm_trans_table_name,
'DATE EFFECTIVE', tup.batch_dea_trans_table_name)
,tup.batch_match_table_name
,tup.batch_unique_match_table_name
,tup.batch_winner_table_name
,tup.batch_unique_winner_table_name
,tup.batch_l1_winner_table_name
,tup.batch_l2_winner_table_name
,tup.batch_l3_winner_table_name
,tup.batch_l4_winner_table_name
,tup.batch_l5_winner_table_name
,tup.batch_wt_winner_table_name
INTO
l_trans_target
,l_umatch_target
,l_match_target
,l_winner_target
,l_uwinner_target
,l_L1_target
,l_L2_target
,l_L3_target
,l_L4_target
,l_L5_target
,l_WT_target
FROM
jty_trans_usg_pgm_details tup
WHERE tup.source_id = p_source_id
AND tup.trans_type_id = p_trans_id
AND tup.program_name = p_program_name;
SELECT COUNT(*)
INTO num_of_terr
FROM jtf_terr_qtype_usgs_all jtqu
, jtf_terr_all jt1
, jtf_qual_type_usgs jqtu
WHERE jtqu.terr_id = jt1.terr_id
AND jqtu.qual_type_usg_id = jtqu.qual_type_usg_id
AND jqtu.qual_type_id = p_trans_id
AND jqtu.source_id = p_source_id
AND jt1.end_date_active >= l_sysdate
AND jt1.start_date_active <= l_sysdate
AND EXISTS (
SELECT 1
FROM jtf_terr_rsc_all jtr,
jtf_terr_rsc_access_all jtra,
jtf_qual_types_all jqta
WHERE jtr.terr_id = jt1.terr_id
AND jtr.end_date_active >= l_sysdate
AND jtr.start_date_active <= l_sysdate
AND jtr.resource_type <> 'RS_ROLE'
AND jtr.terr_rsc_id = jtra.terr_rsc_id
AND jtra.access_type = jqta.name
AND jqta.qual_type_id = p_trans_id
AND jtra.trans_access_code <> 'NONE')
AND NOT EXISTS (
SELECT jt.terr_id
FROM jtf_terr_all jt
WHERE jt.end_date_active < l_sysdate
CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
START WITH jt.terr_id = jt1.terr_id)
AND jqtu.qual_type_id <> -1001;
d_statement := ' SELECT COUNT(*) FROM ' || l_trans_target || ' WHERE rownum < 2 ';
/* if mode is incremental, update the worker_id column for the TRANS table */
IF (p_mode = 'INCREMENTAL') THEN
d_statement := 'UPDATE ' || l_trans_target ||
' SET worker_id = mod(trans_object_id, :no_of_workers) + 1';
'INSERT INTO ' || p_terr_LEVEL_target_tbl ||
' ( ' ||
' trans_object_id ' ||
' , trans_detail_object_id ' ||
' , txn_date ' ||
' , WIN_TERR_ID ' ||
' , UL_TERR_ID ' ||
' , LL_TERR_ID ' ||
' , LL_NUM_WINNERS ' ||
' , WORKER_ID ' ||
' ) ' ||
' (SELECT ' ||
' TL.trans_object_id ' ||
' , TL.trans_detail_object_id ' ||
' , TL.txn_date ' ||
' , TL.CL_WIN_TERR_ID ' ||
' , TL.UL_terr_id ' ||
' , TL.LL_terr_id ' ||
' , TL.LL_num_winners ' ||
' , :B_WORKER_ID ' ||
' FROM ( ' ||
' SELECT ';
'INSERT INTO ' || p_terr_LEVEL_target_tbl ||
' ( ' ||
' trans_object_id ' ||
' , trans_detail_object_id ' ||
' , txn_date ' ||
' , WIN_TERR_ID ' ||
' , UL_TERR_ID ' ||
' , LL_TERR_ID ' ||
' , LL_NUM_WINNERS ' ||
' , WORKER_ID ' ||
' ) ' ||
' (SELECT ' ||
' TL.trans_object_id ' ||
' , TL.trans_detail_object_id ' ||
' , TL.txn_date ' ||
' , TL.CL_WIN_TERR_ID ' ||
' , TL.UL_terr_id ' ||
' , TL.LL_terr_id ' ||
' , TL.LL_num_winners ' ||
' , :B_WORKER_ID ' ||
' FROM ( ' || /* NL */
' SELECT ' ||
' CL.trans_object_id ' ||
' , CL.trans_detail_object_id ' ||
' , CL.txn_date ' ||
' , CL.CL_WIN_TERR_ID ' ||
' , CL.UL_terr_id ';
' SELECT ';
' SELECT ';
'Number of rows inserted into ' || p_terr_LEVEL_target_tbl || ' : ' || SQL%ROWCOUNT);
'INSERT INTO ' || p_terr_LEVEL_target_tbl ||
' ( ' ||
' trans_object_id ' ||
' , trans_detail_object_id ' ||
' , txn_date ' ||
' , WIN_TERR_ID ' ||
' , UL_TERR_ID ' ||
' , LL_TERR_ID ' ||
' , worker_id ' ||
' ) ' ||
' (SELECT ' ||
' TL.trans_object_id ' ||
' , TL.trans_detail_object_id ' ||
' , TL.txn_date ' ||
' , TL.WIN_TERR_ID ' ||
' , TL.UL_terr_id ' ||
' , TL.terr_id ' ||
' , :B_WORKER_ID ' || --p_worker_id ||
' FROM ( ' ||
' SELECT ';
'Number of rows inserted into ' || p_terr_LEVEL_target_tbl || ' : ' || SQL%ROWCOUNT);
l_insert_stmt VARCHAR2(3000);
l_select_stmt VARCHAR2(3000);
l_delete_sql VARCHAR2(3000);
SELECT jtqp.relation_product
FROM jtf_tae_qual_products jtqp
WHERE jtqp.source_id = cl_source_id
AND jtqp.trans_object_type_id = cl_trans_id
ORDER BY jtqp.relation_product DESC;
SELECT column_name
FROM all_tab_columns
WHERE table_name = p_table_name
AND owner = p_owner
AND column_name not in ('SECURITY_GROUP_ID', 'OBJECT_VERSION_NUMBER', 'WORKER_ID', 'LAST_UPDATE_DATE',
'LAST_UPDATED_BY', 'CREATION_DATE', 'CREATED_BY', 'LAST_UPDATE_LOGIN', 'REQUEST_ID',
'PROGRAM_APPLICATION_ID', 'PROGRAM_ID', 'PROGRAM_UPDATE_DATE', 'TXN_DATE');
SELECT tup.batch_trans_table_name
,tup.batch_nm_trans_table_name
INTO l_trans_target
,l_nm_trans_target
FROM jty_trans_usg_pgm_details tup
WHERE tup.source_id = p_source_id
AND tup.trans_type_id = p_trans_id
AND tup.program_name = p_program_name;
/* Delete from TRANS the txn objs present in NM_TRANS table */
commit;
l_delete_sql :=
'DELETE FROM ' || l_trans_target || ' A' || g_new_LINE ||
'WHERE EXISTS ( ' || g_new_line ||
' SELECT 1 ' || g_new_line ||
' FROM ' || l_nm_trans_target || ' B' || g_new_line ||
' WHERE A.trans_object_id = B.trans_object_id )';
EXECUTE IMMEDIATE l_delete_sql;
/* Form the insert statement to insert transaction objects into TRANS table */
l_insert_stmt := 'INSERT INTO ' || l_trans_target || '(';
l_select_stmt := '(SELECT ';
l_insert_stmt := l_insert_stmt || g_new_line || l_indent || column_names.column_name;
l_select_stmt := l_select_stmt || g_new_line || l_indent || column_names.column_name;
l_insert_stmt := l_insert_stmt || g_new_line || l_indent || ',' || column_names.column_name;
l_select_stmt := l_select_stmt || g_new_line || l_indent || ',' || column_names.column_name;
l_insert_stmt := l_insert_stmt || g_new_line || l_indent || ',LAST_UPDATE_DATE ' ||
g_new_line || l_indent || ',LAST_UPDATED_BY ' ||
g_new_line || l_indent || ',CREATION_DATE ' ||
g_new_line || l_indent || ',CREATED_BY ' ||
g_new_line || l_indent || ',LAST_UPDATE_LOGIN ' ||
g_new_line || l_indent || ',REQUEST_ID ' ||
g_new_line || l_indent || ',PROGRAM_APPLICATION_ID ' ||
g_new_line || l_indent || ',PROGRAM_ID ' ||
g_new_line || l_indent || ',PROGRAM_UPDATE_DATE ' ||
g_new_line || l_indent || ',TXN_DATE ' ||
g_new_line || l_indent || ',WORKER_ID ' ||
g_new_line || ')';
l_select_stmt := l_select_stmt || g_new_line || l_indent || ',''' || l_sysdate || '''' ||
g_new_line || l_indent || ',''' || g_user_id || '''' ||
g_new_line || l_indent || ',''' || l_sysdate || '''' ||
g_new_line || l_indent || ',''' || g_user_id || '''' ||
g_new_line || l_indent || ',''' || g_login_id || '''' ||
g_new_line || l_indent || ',''' || p_request_id || '''' ||
g_new_line || l_indent || ',''' || g_appl_id || '''' ||
g_new_line || l_indent || ',''' || g_program_id || '''' ||
g_new_line || l_indent || ',''' || l_sysdate || '''' ||
g_new_line || l_indent || ',''' || l_sysdate || '''' ||
g_new_line || l_indent || ', 1 ';
l_final_sql := l_insert_stmt || g_new_line ||
l_select_stmt || g_new_line || 'FROM ' || l_nm_trans_target || g_new_line || ' ) ';
substr('Insert satement : ' || l_final_sql, 1, 4000));
/* Insert all the transaction objects into the TRANS table */
EXECUTE IMMEDIATE l_final_sql;
SELECT batch_nmc_match_sql
INTO l_match_sql
FROM jty_tae_attr_products_sql
WHERE source_id = p_source_id
AND trans_type_id = p_trans_id
AND program_name = p_program_name
AND attr_relation_product = jtf_csr.relation_product;
'Number of records inserted for qualifier combination ' || jtf_csr.relation_product || ' : ' || SQL%ROWCOUNT);
IF p_oic_mode = 'NOOIC' or p_oic_mode = 'INSERT' THEN
/* Insert the txn objects into TRANS table */
insert_trans_data (
p_source_id => p_source_id,
p_trans_id => p_trans_id,
p_program_name => p_program_name,
p_mode => p_mode,
p_where => p_where,
p_no_of_workers => p_no_of_workers,
p_request_id => p_request_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
errbuf => errbuf,
retcode => retcode,
p_oic_mode => p_oic_mode
);
x_msg_data := 'API JTY_ASSIGN_BULK_PUB.insert_trans_data has failed';
'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.insert_trans_data',
x_msg_data);
'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.end_insert_trans_data',
'API insert_trans_data completed successfully');
END IF; -- p_oic_mode = 'NOOIC' or p_oic_mode = 'INSERT'
/* Synchronize trans and nm_trans table and insert objects */
/* that satisfy the modified territory definition */
process_nmc_match (
p_source_id => p_source_id,
p_trans_id => p_trans_id,
p_program_name => p_program_name,
p_request_id => p_request_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
errbuf => errbuf,
retcode => retcode);
/* insert into NM_TRANS txn objs that are assigned to changed territories */
insert_nm_trans_data (
p_source_id => p_source_id,
p_trans_id => p_trans_id,
p_program_name => p_program_name,
p_request_id => p_request_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
errbuf => errbuf,
retcode => retcode);
x_msg_data := 'API JTY_ASSIGN_BULK_PUB.insert_nm_trans_data has failed';
'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.insert_nm_trans_data',
x_msg_data);
'jtf.plsql.JTY_ASSIGN_BULK_PUB.collect_trans_data.end_insert_nm_trans_data',
'API insert_nm_trans_data completed successfully');
SELECT batch_match_table_name
,batch_unique_match_table_name
,batch_l1_winner_table_name
,batch_l2_winner_table_name
,batch_l3_winner_table_name
,batch_l4_winner_table_name
,batch_l5_winner_table_name
,batch_wt_winner_table_name
,batch_winner_table_name
,batch_unique_winner_table_name
,batch_mp_winner_table_name || p_worker_id
,batch_dmc_winner_table_name || p_worker_id
,multi_level_winning_flag
INTO l_match_target
,l_umatch_target
,l_l1_target
,l_l2_target
,l_l3_target
,l_l4_target
,l_l5_target
,l_wt_target
,l_winner_target
,l_uwinner_target
,l_mp_winner_target
,l_dmc_winner_target
,l_multi_level_winning_flag
FROM jty_trans_usg_pgm_details tup
WHERE tup.source_id = p_source_id
AND tup.trans_type_id = p_trans_id
AND tup.program_name = p_program_name;
' INSERT INTO ' || l_winner_target || ' i ' ||
' ( ' ||
' TRANS_OBJECT_ID ' ||
' ,TRANS_DETAIL_OBJECT_ID ' ||
' ,WORKER_ID ' ||
' ,SOURCE_ID ' ||
' ,TRANS_OBJECT_TYPE_ID ' ||
' ,LAST_UPDATE_DATE ' ||
' ,LAST_UPDATED_BY ' ||
' ,CREATION_DATE ' ||
' ,CREATED_BY ' ||
' ,LAST_UPDATE_LOGIN ' ||
' ,REQUEST_ID ' ||
' ,PROGRAM_APPLICATION_ID ' ||
' ,PROGRAM_ID ' ||
' ,PROGRAM_UPDATE_DATE ' ||
' ,TERR_ID ' ||
' ,ABSOLUTE_RANK ' ||
' ,TOP_LEVEL_TERR_ID ' ||
' ,RESOURCE_ID ' ||
' ,RESOURCE_TYPE ' ||
' ,GROUP_ID ' ||
' ,ROLE_ID ' ||
' ,ROLE ' ||
' ,PRIMARY_CONTACT_FLAG ' ||
' ,PERSON_ID ' ||
' ,ORG_ID ' ||
' ,TERR_RSC_ID ' ||
' ,FULL_ACCESS_FLAG ' ||
' ) ' ||
' ( ' ||
' SELECT ' || -- DISTINCT ' ||
' WT.trans_object_id ' ||
' , WT.trans_detail_object_id ' ||
' , :bv_worker_id ' || --p_worker_id ||
' , :BV1_SOURCE_ID ' ||
' , :BV1_TRANS_OBJECT_TYPE_ID ' ||
' , :BV1_LAST_UPDATE_DATE ' ||
' , :BV1_LAST_UPDATED_BY ' ||
' , :BV1_CREATION_DATE ' ||
' , :BV1_CREATED_BY ' ||
' , :BV1_LAST_UPDATE_LOGIN ' ||
' , :BV1_REQUEST_ID ' ||
' , :BV1_PROGRAM_APPLICATION_ID ' ||
' , :BV1_PROGRAM_ID ' ||
' , :BV1_PROGRAM_UPDATE_DATE ' ||
' , WT.terr_id ' ||
' , null absolute_rank ' || /* o_dttm.absolute_rank ' || */
' , null top_level_terr_id ' || /* o_dttm.top_level_terr_id ' || */
' , jtr.resource_id ' ||
' , jtr.resource_type ' ||
' , jtr.group_id ' ||
' , inv.role_id ' ||
' , jtr.role ' ||
' , jtr.primary_contact_flag ' ||
' , jtr.PERSON_ID ' ||
' , jtr.org_id ' ||
' , jtr.terr_rsc_id ' ||
' , decode(jtra.trans_access_code, ''FULL_ACCESS'', ''Y'', ''N'') ' ||
' FROM ( /* WINNERS ILV */ ' ||
' SELECT ' ||
' o.trans_object_id ' ||
' ,o.trans_detail_object_id ' ||
' ,o.terr_id ' ||
' ,o.txn_date ' ||
' FROM ' ||
' ( SELECT ' ||
' i.trans_id ' ||
' ,i.trans_object_id ' ||
' ,i.trans_detail_object_id ' ||
' ,i.terr_id ' ||
' ,i.top_level_terr_id ' ||
' ,i.txn_date ' ||
' ,RANK() OVER ( PARTITION BY ' ||
' i.trans_id ' ||
' , i.trans_object_id ' ||
' , i.trans_detail_object_id ' ||
' , i.top_level_terr_id ' ||
' ORDER BY i.absolute_rank DESC, i.terr_id) AS TERR_RANK ' ||
' FROM ' || l_match_target || ' i ' ||
' WHERE i.worker_id = :bv_worker_id ) o ' ||
' WHERE o.TERR_RANK <= (SELECT NVL(t.num_winners, 1) FROM jtf_terr_all t WHERE t.terr_id = o.top_level_terr_id) ' ||
' ) WT ' ||
' , jtf_terr_rsc_all jtr ' ||
' , jtf_terr_rsc_access_all jtra ' ||
' , jtf_qual_types_all jqta ' ||
' , (SELECT ' ||
' max(role_id) role_id ' ||
' ,role_code role_code ' ||
' FROM jtf_rs_roles_b ' ||
' GROUP BY role_code ) inv ' ||
' WHERE WT.terr_id = jtr.terr_id ' ||
' AND jtr.end_date_active >= WT.txn_date ' ||
' AND jtr.start_date_active <= WT.txn_date ' ||
' AND jtr.resource_type <> ''RS_ROLE'' ' ||
' AND jtr.terr_rsc_id = jtra.terr_rsc_id ' ||
' AND jtr.role = inv.role_code(+) ' ||
' AND jtra.access_type = jqta.name ' ||
' AND jtra.trans_access_code <> ''NONE'' ' ||
' AND jqta.qual_type_id = :bv_trans_id ';
, lp_sysdate /* :BV1_LAST_UPDATE_DATE */
, G_USER_ID /* :BV1_LAST_UPDATED_BY */
, lp_sysdate /* :BV1_CREATION_DATE */
, G_USER_ID /* :BV1_CREATED_BY */
, G_LOGIN_ID /* :BV1_LAST_UPDATE_LOGIN */
, G_REQUEST_ID /* :BV1_REQUEST_ID */
, G_APPL_ID /* :BV1_PROGRAM_APPLICATION_ID */
, G_PROGRAM_ID /* :BV1_PROGRAM_ID */
, lp_sysdate /* :BV1_PROGRAM_UPDATE_DATE */
, p_worker_id /* :bv_worker_id */
, p_trans_id;
'Number of records inserted into ' || l_winner_target || ' for worker_id : ' || p_worker_id || ' : ' || SQL%ROWCOUNT);
'SELECT max(count(terr_id)) ' ||
'FROM ' || l_match_target || ' ' ||
'WHERE worker_id = :worker_id ' ||
'GROUP BY trans_object_id, trans_detail_object_id';
' select trans_object_id, trans_detail_object_id, txn_date, max(rownum) link ';
' (select trans_object_id, trans_detail_object_id, terr_id, txn_date, ' ||
' dense_rank() over(partition by trans_object_id, trans_detail_object_id order by terr_id) trank ' ||
' from ' || l_match_target ||
' where worker_id = ' || p_worker_id || ' ) ' ||
' group by trans_object_id, trans_detail_object_id, txn_date )';
' select max(rownum) link ';
/* Insert into umatch table the unique combination of matching territories */
l_dyn_str :=
'insert into ' || l_umatch_target ||
' (trans_object_id, ' ||
' trans_detail_object_id, ' ||
' terr_id, ' ||
' absolute_rank, ' ||
' txn_date, ' ||
-- ' LAST_UPDATE_DATE, ' ||
-- ' LAST_UPDATED_BY, ' ||
-- ' CREATION_DATE, ' ||
-- ' CREATED_BY, ' ||
-- ' LAST_UPDATE_LOGIN, ' ||
-- ' REQUEST_ID, ' ||
-- ' PROGRAM_APPLICATION_ID, ' ||
-- ' PROGRAM_ID, ' ||
-- ' PROGRAM_UPDATE_DATE, ' ||
' worker_id) ( ';
'select ' ||
' ilv.trans_object_id, ' ||
' -1, ' || --trans_detail_object_id
' ilv.terr_id, ' ||
' jt.absolute_rank, ' ||
' null, ' ||
p_worker_id ||
' from (' ;
'select ' ||
' a.link trans_object_id, ' ||
-- ' -1 trans_detail_object_id, ' ||
' a.terr_id' || i || ' terr_id ' ||
-- ' b.absolute_rank, ' ||
'from ' || l_dmc_winner_target || ' a ';
'select ' ||
' a.link trans_object_id, ' ||
' -1 trans_detail_object_id, ' ||
' a.terr_id' || i || ' terr_id, ' ||
' b.absolute_rank, ' ||
' null, ' ||
-- ' ''' || lp_sysdate || ''', ' ||
-- ' ' || g_user_id || ', ' ||
-- ' ''' || lp_sysdate || ''', ' ||
-- ' ' || g_user_id || ', ' ||
-- ' ' || g_login_id || ', ' ||
-- ' ' || g_request_id || ', ' ||
-- ' ' || g_appl_id || ', ' ||
-- ' ' || g_program_id || ', ' ||
-- ' ''' || lp_sysdate || ''', ' ||
' ' || p_worker_id || ' ' ||
'from ' || l_dmc_winner_target || ' a, jtf_terr_all b ' ||
'where a.terr_id' || i || ' = b.terr_id ';
'Data successfully inserted in umatch table');
/* update the temporary table to maintain the link */
/* between txn objects and unique terr matching combinations */
l_dyn_str :=
'update ' || l_mp_winner_target || ' a ' ||
' set link = ( ' ||
' select /*+ use_index(' || l_dmc_winner_target || '_N1) */ link from ' || l_dmc_winner_target || ' b ' ||
' where a.terr_id1 = b.terr_id1 ';
'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.l_mp_winner_update',
substr(l_dyn_str, 1, 4000));
'jtf.plsql.JTY_ASSIGN_BULK_PUB.process_winners.l_mp_winner_update',
'Link successfully updated in ' || l_dmc_winner_target || ' table');
' INSERT INTO ' || l_uwinner_target || ' i ' ||
' ( ' ||
' TRANS_OBJECT_ID ' ||
' ,TRANS_DETAIL_OBJECT_ID ' ||
' ,WORKER_ID ' ||
' ,LAST_UPDATE_DATE ' ||
' ,LAST_UPDATED_BY ' ||
' ,CREATION_DATE ' ||
' ,CREATED_BY ' ||
' ,LAST_UPDATE_LOGIN ' ||
' ,REQUEST_ID ' ||
' ,PROGRAM_APPLICATION_ID ' ||
' ,PROGRAM_ID ' ||
' ,PROGRAM_UPDATE_DATE ' ||
' ,TERR_ID ' ||
' ,ABSOLUTE_RANK ' ||
' ,TOP_LEVEL_TERR_ID ' ||
' ) ' ||
' ( ' ||
' SELECT ' || -- DISTINCT ' ||
' WINNERS.trans_object_id ' ||
' , WINNERS.trans_detail_object_id ' ||
' , :bv_worker_id ' || --p_worker_id ||
' , :BV1_LAST_UPDATE_DATE ' ||
' , :BV1_LAST_UPDATED_BY ' ||
' , :BV1_CREATION_DATE ' ||
' , :BV1_CREATED_BY ' ||
' , :BV1_LAST_UPDATE_LOGIN ' ||
' , :BV1_REQUEST_ID ' ||
' , :BV1_PROGRAM_APPLICATION_ID ' ||
' , :BV1_PROGRAM_ID ' ||
' , :BV1_PROGRAM_UPDATE_DATE ' ||
' , WINNERS.WIN_terr_id ' ||
' , null absolute_rank ' || /* o_dttm.absolute_rank ' || */
' , null top_level_terr_id ' || /* o_dttm.top_level_terr_id ' || */
' FROM ( /* WINNERS ILV */ ' ||
' SELECT ILV.trans_object_id ' ||
' , ILV.trans_detail_object_id ' ||
' , ILV.WIN_TERR_ID ' ||
' FROM ( SELECT trans_object_id ' ||
' , trans_detail_object_id ' ||
' , WIN_TERR_ID WIN_TERR_ID ' ||
' FROM ' || l_L1_target ||
' WHERE WORKER_ID = :bv_worker_id ' ||
' MINUS ' ||
' SELECT trans_object_id ' ||
' , trans_detail_object_id ' ||
' , ul_terr_id WIN_TERR_ID ' ||
' FROM ' || l_L2_target ||
' WHERE WORKER_ID = :bv_worker_id ' ||
' ) ILV ' ||
' UNION ALL ' ||
' SELECT ILV.trans_object_id ' ||
' , ILV.trans_detail_object_id ' ||
' , ILV.WIN_TERR_ID ' ||
' FROM ( SELECT trans_object_id ' ||
' , trans_detail_object_id ' ||
' , WIN_TERR_ID WIN_TERR_ID ' ||
' FROM ' || l_L2_target ||
' WHERE WORKER_ID = :bv_worker_id ' ||
' MINUS ' ||
' SELECT trans_object_id ' ||
' , trans_detail_object_id ' ||
' , ul_terr_id WIN_TERR_ID ' ||
' FROM ' || l_L3_target ||
' WHERE WORKER_ID = :bv_worker_id ' ||
' ) ILV ' ||
' UNION ALL ' ||
' SELECT ILV.trans_object_id ' ||
' , ILV.trans_detail_object_id ' ||
' , ILV.WIN_TERR_ID ' ||
' FROM ( SELECT trans_object_id ' ||
' , trans_detail_object_id ' ||
' , WIN_TERR_ID WIN_TERR_ID ' ||
' FROM ' || l_L3_target ||
' WHERE WORKER_ID = :bv_worker_id ' ||
' MINUS ' ||
' SELECT trans_object_id ' ||
' , trans_detail_object_id ' ||
' , ul_terr_id WIN_TERR_ID ' ||
' FROM ' || l_L4_target ||
' WHERE WORKER_ID = :bv_worker_id ' ||
' ) ILV ' ||
' UNION ALL ' ||
' SELECT ILV.trans_object_id ' ||
' , ILV.trans_detail_object_id ' ||
' , ILV.WIN_TERR_ID ' ||
' FROM ( SELECT trans_object_id ' ||
' , trans_detail_object_id ' ||
' , WIN_TERR_ID WIN_TERR_ID ' ||
' FROM ' || l_L4_target ||
' WHERE WORKER_ID = :bv_worker_id ' ||
' MINUS ' ||
' SELECT trans_object_id ' ||
' , trans_detail_object_id ' ||
' , ul_terr_id WIN_TERR_ID ' ||
' FROM ' || l_L5_target ||
' WHERE WORKER_ID = :bv_worker_id ' ||
' ) ILV ' ||
' UNION ALL ' ||
' SELECT ILV.trans_object_id ' ||
' , ILV.trans_detail_object_id ' ||
' , ILV.WIN_TERR_ID ' ||
' FROM ( SELECT trans_object_id ' ||
' , trans_detail_object_id ' ||
' , WIN_TERR_ID WIN_TERR_ID ' ||
' FROM ' || l_L5_target ||
' WHERE WORKER_ID = :bv_worker_id ' ||
' MINUS ' ||
' SELECT trans_object_id ' ||
' , trans_detail_object_id ' ||
' , ul_terr_id WIN_TERR_ID ' ||
' FROM ' || l_WT_target ||
' WHERE WORKER_ID = :bv_worker_id ' ||
' ) ILV ' ||
' UNION ALL ' ||
' SELECT trans_object_id ' ||
' , trans_detail_object_id ' ||
' , WIN_TERR_ID ' ||
' FROM ' || l_WT_target ||
' WHERE WORKER_ID = :bv_worker_id ' ||
' ) WINNERS ' ||
' ) ';
, lp_sysdate /* :BV1_LAST_UPDATE_DATE */
, G_USER_ID /* :BV1_LAST_UPDATED_BY */
, lp_sysdate /* :BV1_CREATION_DATE */
, G_USER_ID /* :BV1_CREATED_BY */
, G_LOGIN_ID /* :BV1_LAST_UPDATE_LOGIN */
, G_REQUEST_ID /* :BV1_REQUEST_ID */
, G_APPL_ID /* :BV1_PROGRAM_APPLICATION_ID */
, G_PROGRAM_ID /* :BV1_PROGRAM_ID */
, lp_sysdate /* :BV1_PROGRAM_UPDATE_DATE */
, p_worker_id /* :bv_worker_id */ --1
, p_worker_id /* :bv_worker_id */
, p_worker_id /* :bv_worker_id */
, p_worker_id /* :bv_worker_id */
, p_worker_id /* :bv_worker_id */ --5
, p_worker_id /* :bv_worker_id */
, p_worker_id /* :bv_worker_id */
, p_worker_id /* :bv_worker_id */
, p_worker_id /* :bv_worker_id */
, p_worker_id /* :bv_worker_id */ --10
, p_worker_id; /* :bv_worker_id */
'Number of records inserted into ' || l_winner_target || ' for worker_id : ' || p_worker_id || ' : ' || SQL%ROWCOUNT);
' INSERT INTO ' || l_winner_target || ' i ' ||
' ( ' ||
' TRANS_OBJECT_ID ' ||
' ,TRANS_DETAIL_OBJECT_ID ' ||
' ,WORKER_ID ' ||
' ,SOURCE_ID ' ||
' ,TRANS_OBJECT_TYPE_ID ' ||
' ,LAST_UPDATE_DATE ' ||
' ,LAST_UPDATED_BY ' ||
' ,CREATION_DATE ' ||
' ,CREATED_BY ' ||
' ,LAST_UPDATE_LOGIN ' ||
' ,REQUEST_ID ' ||
' ,PROGRAM_APPLICATION_ID ' ||
' ,PROGRAM_ID ' ||
' ,PROGRAM_UPDATE_DATE ' ||
' ,TERR_ID ' ||
' ,ABSOLUTE_RANK ' ||
' ,TOP_LEVEL_TERR_ID ' ||
' ,RESOURCE_ID ' ||
' ,RESOURCE_TYPE ' ||
' ,GROUP_ID ' ||
' ,ROLE_ID ' ||
' ,ROLE ' ||
' ,PRIMARY_CONTACT_FLAG ' ||
' ,PERSON_ID ' ||
' ,ORG_ID ' ||
' ,TERR_RSC_ID ' ||
' ,FULL_ACCESS_FLAG ' ||
' ) ' ||
' ( ' ||
' SELECT ' || -- DISTINCT ' ||
' WINNERS.trans_object_id ' ||
' , WINNERS.trans_detail_object_id ' ||
' , :bv_worker_id ' || --p_worker_id ||
' , :BV1_SOURCE_ID ' ||
' , :BV1_TRANS_OBJECT_TYPE_ID ' ||
' , :BV1_LAST_UPDATE_DATE ' ||
' , :BV1_LAST_UPDATED_BY ' ||
' , :BV1_CREATION_DATE ' ||
' , :BV1_CREATED_BY ' ||
' , :BV1_LAST_UPDATE_LOGIN ' ||
' , :BV1_REQUEST_ID ' ||
' , :BV1_PROGRAM_APPLICATION_ID ' ||
' , :BV1_PROGRAM_ID ' ||
' , :BV1_PROGRAM_UPDATE_DATE ' ||
' , WINNERS.terr_id ' ||
' , null absolute_rank ' || /* o_dttm.absolute_rank ' || */
' , null top_level_terr_id ' || /* o_dttm.top_level_terr_id ' || */
' , jtr.resource_id ' ||
' , jtr.resource_type ' ||
' , jtr.group_id ' ||
' , inv.role_id ' ||
' , jtr.role ' ||
' , jtr.primary_contact_flag ' ||
' , jtr.PERSON_ID ' ||
' , jtr.org_id ' ||
' , jtr.terr_rsc_id ' ||
' , decode(jtra.trans_access_code, ''FULL_ACCESS'', ''Y'', ''N'') ' ||
' FROM ( /* WINNERS ILV */ ' ||
' SELECT a.trans_object_id ' ||
' , a.trans_detail_object_id ' ||
' , b.TERR_ID ' ||
' , a.txn_date ' ||
' FROM ' || l_mp_winner_target || ' a, ' || l_uwinner_target || ' b ' ||
' WHERE b.WORKER_ID = :bv_worker_id ' ||
' AND a.link = b.trans_object_id ' ||
' ) WINNERS ' ||
' , jtf_terr_rsc_all jtr ' ||
' , jtf_terr_rsc_access_all jtra ' ||
' , jtf_qual_types_all jqta ' ||
' , (SELECT ' ||
' max(role_id) role_id ' ||
' ,role_code role_code ' ||
' FROM jtf_rs_roles_b ' ||
' GROUP BY role_code ) inv ' ||
' WHERE WINNERS.terr_id = jtr.terr_id ' ||
' AND jtr.end_date_active >= WINNERS.txn_date ' ||
' AND jtr.start_date_active <= WINNERS.txn_date ' ||
' AND jtr.resource_type <> ''RS_ROLE'' ' ||
' AND jtr.terr_rsc_id = jtra.terr_rsc_id ' ||
' AND jtr.role = inv.role_code(+) ' ||
' AND jtra.access_type = jqta.name ' ||
' AND jtra.trans_access_code <> ''NONE'' ' ||
' AND jqta.qual_type_id = :bv_trans_id ' ||
' ) ';
, lp_sysdate /* :BV1_LAST_UPDATE_DATE */
, G_USER_ID /* :BV1_LAST_UPDATED_BY */
, lp_sysdate /* :BV1_CREATION_DATE */
, G_USER_ID /* :BV1_CREATED_BY */
, G_LOGIN_ID /* :BV1_LAST_UPDATE_LOGIN */
, G_REQUEST_ID /* :BV1_REQUEST_ID */
, G_APPL_ID /* :BV1_PROGRAM_APPLICATION_ID */
, G_PROGRAM_ID /* :BV1_PROGRAM_ID */
, lp_sysdate /* :BV1_PROGRAM_UPDATE_DATE */
, p_worker_id /* :bv_worker_id */ --1
, p_trans_id;
'Number of records inserted into ' || l_winner_target || ' for worker_id : ' || p_worker_id || ' : ' || SQL%ROWCOUNT);
SELECT jtqp.relation_product
FROM jtf_tae_qual_products jtqp
WHERE jtqp.source_id = cl_source_id
AND jtqp.trans_object_type_id = cl_trans_id
ORDER BY jtqp.relation_product DESC;
SELECT jtqp.attr_relation_product
FROM jty_dea_attr_products jtqp
WHERE jtqp.source_id = cl_source_id
AND jtqp.trans_type_id = cl_trans_id
ORDER BY jtqp.attr_relation_product DESC;
SELECT decode(p_mode, 'TOTAL', tup.batch_trans_table_name
, 'INCREMENTAL', tup.batch_nm_trans_table_name
, 'DATE EFFECTIVE', tup.batch_dea_trans_table_name)
,batch_match_table_name
INTO l_trans_target
,l_match_target
FROM jty_trans_usg_pgm_details tup
WHERE tup.source_id = p_source_id
AND tup.trans_type_id = p_trans_id
AND tup.program_name = p_program_name;
l_sql_stmt := 'SELECT COUNT(*) FROM ' || l_trans_target || ' WHERE worker_id = :bv_worker_id';
SELECT batch_dea_match_sql
INTO l_match_sql
FROM jty_dea_attr_products_sql
WHERE source_id = p_source_id
AND trans_type_id = p_trans_id
AND program_name = p_program_name
AND attr_relation_product = jtf_csr.attr_relation_product;
'Number of records inserted for qualifier combination ' || jtf_csr.attr_relation_product || ' : ' || SQL%ROWCOUNT);
SELECT batch_dea_match_sql_with_terr
INTO l_match_sql
FROM jty_dea_attr_products_sql
WHERE source_id = p_source_id
AND trans_type_id = p_trans_id
AND program_name = p_program_name
AND attr_relation_product = jtf_csr.attr_relation_product;
'Number of records inserted for qualifier combination ' || jtf_csr.attr_relation_product || ' : ' || SQL%ROWCOUNT);
SELECT decode(p_mode, 'TOTAL', batch_match_sql, 'INCREMENTAL', batch_nm_match_sql)
INTO l_match_sql
FROM jty_tae_attr_products_sql
WHERE source_id = p_source_id
AND trans_type_id = p_trans_id
AND program_name = p_program_name
AND attr_relation_product = jtf_csr.relation_product;
'Number of records inserted for qualifier combination ' || jtf_csr.relation_product || ' : ' || SQL%ROWCOUNT);