[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT a.terr_id,
a.rank_calc_flag,
a.process_attr_values_flag,
a.matching_sql_flag,
a.hier_processing_flag
FROM jty_changed_terrs a
WHERE a.star_request_id = cl_request_id
AND a.source_id = p_source_id
AND (a.rank_calc_flag <> 'N' OR a.process_attr_values_flag <> 'N' OR
a.matching_sql_flag <> 'N' OR a.hier_processing_flag <> 'N')
AND NOT EXISTS (
SELECT jt.terr_id
FROM jtf_terr_all jt
WHERE jt.end_date_active < sysdate
OR jt.start_date_active > sysdate
CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
START WITH jt.terr_id = a.terr_id )
UNION ALL
SELECT a.terr_id,
'N',
'D',
'Y',
'D'
FROM jtf_terr_all a
WHERE (a.start_date_active > sysdate
OR a.end_date_active < sysdate)
AND exists (
SELECT 1
FROM jty_changed_terrs b
WHERE b.terr_id = a.terr_id
AND b.star_request_id = cl_request_id
AND b.source_id = p_source_id);
SELECT terr_id
FROM jtf_terr_all
START WITH terr_id = cl_terr_id
CONNECT BY PRIOR terr_id = parent_territory_id;
/* Insert into jty_changed_terrs the territories that */
/* have become active after the last run of STAR */
MERGE INTO jty_changed_terrs A
USING
( SELECT
a.terr_id terr_id,
b.source_id source_id
FROM jtf_terr_all a,
jtf_terr_usgs_all b
WHERE a.terr_id = b.terr_id
AND b.source_id = p_source_id
AND a.start_date_active >
(SELECT max(end_date)
FROM jty_conc_req_summ a
WHERE a.program_name = 'JTY_STAR'
AND a.param1 = to_char(p_source_id)
AND a.retcode = 0)
AND a.start_date_active < sysdate
AND a.end_date_active > sysdate
AND NOT EXISTS (
SELECT jt.terr_id
FROM jtf_terr_all jt
WHERE jt.end_date_active < sysdate
OR jt.start_date_active > sysdate
CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
START WITH jt.terr_id = a.terr_id ) ) S
ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
WHEN MATCHED THEN
UPDATE SET
A.rank_calc_flag = 'Y'
,A.process_attr_values_flag = 'I'
,A.matching_sql_flag = 'Y'
,A.hier_processing_flag = 'I'
WHEN NOT MATCHED THEN
INSERT (
A.CHANGED_TERRITORY_ID
,A.OBJECT_VERSION_NUMBER
,A.TERR_ID
,A.SOURCE_ID
,A.CHANGE_TYPE
,A.RANK_CALC_FLAG
,A.PROCESS_ATTR_VALUES_FLAG
,A.MATCHING_SQL_FLAG
,A.HIER_PROCESSING_FLAG)
VALUES (
jty_changed_terrs_s.nextval
,0
,S.terr_id
,S.source_id
,'UPDATE'
,'Y'
,'I'
,'Y'
,'I');
/* Insert into jty_changed_terrs the territories that */
/* have become inactive after the last run of STAR */
MERGE INTO jty_changed_terrs A
USING
( SELECT
a.terr_id terr_id,
b.source_id source_id
FROM jtf_terr_all a,
jtf_terr_usgs_all b
WHERE a.terr_id = b.terr_id
AND b.source_id = p_source_id
AND a.end_date_active >
(SELECT max(end_date)
FROM jty_conc_req_summ a
WHERE a.program_name = 'JTY_STAR'
AND a.param1 = to_char(p_source_id)
AND a.retcode = 0)
AND a.end_date_active < sysdate ) S
ON ( A.terr_id = S.terr_id AND A.star_request_id IS NULL )
WHEN MATCHED THEN
UPDATE SET
A.rank_calc_flag = 'N'
,A.process_attr_values_flag = 'D'
,A.matching_sql_flag = 'Y'
,A.hier_processing_flag = 'D'
WHEN NOT MATCHED THEN
INSERT (
A.CHANGED_TERRITORY_ID
,A.OBJECT_VERSION_NUMBER
,A.TERR_ID
,A.SOURCE_ID
,A.CHANGE_TYPE
,A.RANK_CALC_FLAG
,A.PROCESS_ATTR_VALUES_FLAG
,A.MATCHING_SQL_FLAG
,A.HIER_PROCESSING_FLAG)
VALUES (
jty_changed_terrs_s.nextval
,0
,S.terr_id
,S.source_id
,'UPDATE'
,'N'
,'D'
,'Y'
,'D');
DELETE jty_changed_terrs_gt;
UPDATE jty_changed_terrs a
SET a.star_request_id = p_request_id
WHERE a.star_request_id IS NULL
AND a.source_id = p_source_id;
INSERT INTO jty_changed_terrs_gt (
terr_id
,rank_calc_flag
,process_attr_values_flag
,matching_sql_flag
,hier_processing_flag)
VALUES (
l_terr_id_tbl(i)
,l_rank_tbl(i)
,l_attr_values_tbl(i)
,l_match_sql_tbl(i)
,l_hier_tbl(i));
UPDATE jty_changed_terrs_gt
SET rank_calc_flag = decode(rank_calc_flag, 'Y', 'Y', l_rank_tbl(i)),
process_attr_values_flag =
decode(process_attr_values_flag,
'I', 'I',
'D', decode(l_attr_values_tbl(i), 'I', 'I', 'D'),
l_attr_values_tbl(i)),
matching_sql_flag = decode(matching_sql_flag, 'Y', 'Y', l_match_sql_tbl(i)),
hier_processing_flag =
decode(hier_processing_flag,
'I', 'I',
'D', decode(l_hier_tbl(i), 'I', 'I', 'D'),
l_hier_tbl(i))
WHERE terr_id = child_terrs.terr_id;
INSERT INTO jty_changed_terrs_gt (
terr_id
,rank_calc_flag
,process_attr_values_flag
,matching_sql_flag
,hier_processing_flag)
VALUES (
child_terrs.terr_id
,l_rank_tbl(i)
,l_attr_values_tbl(i)
,l_match_sql_tbl(i)
,l_hier_tbl(i));
SELECT
a.terr_id
,a.rank_calc_flag
,a.process_attr_values_flag
,a.matching_sql_flag
,a.hier_processing_flag
,b.rank
,b.parent_territory_id
,JTY_TERR_DENORM_RULES_PVT.get_level_from_root(a.terr_id)
,b.num_winners
,b.org_id
,c.num_winners
,b.start_date_active
,b.end_date_active
BULK COLLECT INTO
p_terr_change_tab.terr_id
,p_terr_change_tab.rank_calc_flag
,p_terr_change_tab.attr_processing_flag
,p_terr_change_tab.matching_sql_flag
,p_terr_change_tab.hier_processing_flag
,p_terr_change_tab.terr_rank
,p_terr_change_tab.parent_terr_id
,p_terr_change_tab.level_from_root
,p_terr_change_tab.num_winners
,p_terr_change_tab.org_id
,p_terr_change_tab.parent_num_winners
,p_terr_change_tab.start_date
,p_terr_change_tab.end_date
FROM
jty_changed_terrs_gt a
,jtf_terr_all b
,jtf_terr_all c
WHERE a.terr_id = b.terr_id(+)
AND b.parent_territory_id = c.terr_id(+)
AND b.org_id = c.org_id(+);
SELECT qual_type_id
FROM jtf_qual_type_usgs_all
WHERE source_id = cl_source_id
AND qual_type_id <> -1001;
select distinct jtqu.qual_relation_product
from jtf_terr_qtype_usgs_all jtqu
,jtf_qual_type_usgs_all jqtu
where jqtu.source_id = cl_source_id
and jqtu.qual_type_id = cl_qual_type_id
and jtqu.qual_type_usg_id = jqtu.qual_type_usg_id
and jtqu.qual_relation_product <> 1
and exists (
select /*+ index_ffs(jtdr jtf_terr_denorm_rules_n1) */ 1
from jtf_terr_denorm_rules_all jtdr
where jtdr.terr_id = jtqu.terr_id
and jtqu.terr_id = jtdr.related_terr_id );
select distinct jtqu.qual_relation_product
from jtf_terr_qtype_usgs_all jtqu
,jtf_qual_type_usgs_all jqtu
where jqtu.source_id = cl_source_id
and jqtu.qual_type_id = cl_qual_type_id
and jtqu.qual_type_usg_id = jqtu.qual_type_usg_id
and jtqu.qual_relation_product <> 1
and exists (
select /*+ index_ffs(jtdr jty_denorm_dea_rules_n1) */ 1
from jty_denorm_dea_rules_all jtdr
where jtdr.terr_id = jtqu.terr_id
and jtqu.terr_id = jtdr.related_terr_id );
/* delete the old records from the tables jtf_tae_qual_products, jtf_tae_qual_factors */
/* and jtf_tae_qual_prod_factors if mode is total or incremental */
/* delete the old records from the tables jtf_dea_attr_products, jtf_dea_attr_factors */
/* and jtf_dea_attr_prod_factors if mode is date effective */
JTY_TAE_CONTROL_PVT.delete_combinations(
p_source_id => p_source_id,
p_trans_id => l_qual_type_id_tbl(i),
p_mode => p_mode,
x_Return_Status => x_return_status,
x_Msg_Count => x_msg_count,
x_Msg_Data => x_msg_data,
ERRBUF => errbuf,
RETCODE => retcode);
'jtf.plsql.JTY_TERR_ENGINE_GEN_PVT.gen_matching_sql.delete_combinations',
'JTY_TAE_CONTROL_PVT.delete_combinations API has failed');
SELECT a.qual_relation_product
INTO l_qual_relation_product
FROM jtf_terr_qtype_usgs_all a,
jtf_qual_type_usgs_all b,
jtf_terr_all c
WHERE a.qual_type_usg_id = b.qual_type_usg_id
AND b.source_id = p_source_id
AND b.qual_type_id = l_qual_type_id_tbl(i)
AND a.terr_id = p_terr_change_tab.terr_id(j)
AND c.terr_id = a.terr_id
AND c.start_date_active < sysdate
AND c.end_date_active > sysdate
AND a.qual_relation_product <> 1
AND NOT EXISTS (
SELECT 1
FROM jtf_tae_qual_products c
WHERE c.source_id = p_source_id
AND c.trans_object_type_id = l_qual_type_id_tbl(i)
AND c.relation_product = a.qual_relation_product);
/* insert the current qual rel prd into the pl/sql table only if it does not exist */
IF (l_new_qual_prd) THEN
l_qual_prd_tbl.EXTEND();
SELECT jta1.terr_id terr_id
,NVL(jta1.rank, 999999999) rank
,jta1.num_winners num_winners
,jta1.org_id org_id
,jta1.parent_territory_id parent_territory_id
,JTY_TERR_DENORM_RULES_PVT.get_level_from_root(jta1.terr_id) level_from_root
,jta2.num_winners parent_num_winners
,'Y' rank_calc_flag
,'I' attr_processing_flag
,'I' hier_processing_flag
,'Y' matching_sql_flag
,jta1.start_date_active start_date
,jta1.end_date_active end_date
FROM jtf_terr_usgs_all jtu
, jtf_terr_all jta1
, jtf_terr_all jta2
WHERE jtu.source_id = lp_source_id
AND jtu.terr_id = jta1.terr_id
AND jta1.terr_id <> 1
AND jta1.end_date_active >= lp_start_date
AND jta1.start_date_active <= lp_end_date
AND jta2.terr_id = jta1.parent_territory_id
AND ( jta1.org_id = jta2.org_id OR
(jta1.org_id IS NULL AND jta2.org_id IS NULL) )
AND NOT EXISTS (
SELECT jt.terr_id
FROM jtf_terr_all jt
WHERE jt.end_date_active < lp_start_date
OR jt.start_date_active > lp_end_date
CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
START WITH jt.terr_id = jta1.terr_id );
UPDATE jty_changed_terrs
SET star_request_id = g_request_id
WHERE source_id = p_source_id
AND star_request_id IS NULL;
SELECT count(*)
INTO l_count
FROM jty_conc_req_summ a
WHERE a.program_name = 'JTY_STAR'
AND a.param1 = p_source_id
AND a.param2 = 'TOTAL'
AND a.retcode = 0;
UPDATE jtf_terr_rsc_all jtr
SET jtr.person_id =
( SELECT jrrev.source_id
FROM jtf_rs_resource_extns_vl jrrev
WHERE jrrev.category = 'EMPLOYEE'
AND jrrev.resource_id = jtr.resource_id )
WHERE jtr.resource_type= 'RS_EMPLOYEE'
AND jtr.terr_id = l_terr_change_tab.terr_id(i);
SELECT denorm_dea_value_table_name
INTO l_table_name
FROM jtf_sources_all
WHERE source_id = p_source_id;
SELECT denorm_value_table_name
INTO l_table_name
FROM jtf_sources_all
WHERE source_id = p_source_id;
UPDATE jtf_terr_denorm_rules_all jtda
SET absolute_rank = ( select absolute_rank from jtf_terr_all jta where jta.terr_id = jtda.terr_id)
where jtda.source_id = -1002;
EXECUTE IMMEDIATE 'SELECT count(*) FROM ' || l_table_name || ' where ROWNUM = 1' INTO l_denorm_count;
/* if batch mode is not enabled for the usage, delete all the entries processed from the changed table */
IF ((p_mode = 'TOTAL') OR (p_mode = 'INCREMENTAL')) THEN
SELECT count(*)
INTO l_batch_enabled
FROM jty_trans_usg_pgm_details a
WHERE a.source_id = p_source_id
AND a.batch_enable_flag = 'Y';
DELETE jty_changed_terrs
WHERE star_request_id = g_request_id;
UPDATE JTY_CONC_REQ_SUMM
SET requested_by = l_user_id
,request_date = l_start_date
,responsibility_application_id = l_resp_appl_id
,responsibility_id = l_resp_id
,last_updated_by = l_user_id
,last_update_date = l_start_date
,last_update_login = l_login_id
,start_date = l_start_date
,end_date = l_end_date
,param2 = p_mode
,param3 = TO_CHAR(l_param_start_date, 'DD/MM/YYYY HH24:MI:SS')
,param4 = TO_CHAR(l_param_end_date, 'DD/MM/YYYY HH24:MI:SS')
,param5 = null
,program_application_id = l_pgm_appl_id
,errbuf = errbuf
,request_id = g_request_id
,conc_program_id = l_conc_pgm_id
WHERE program_name = 'JTY_STAR'
AND param1 = to_char(p_source_id)
AND retcode = retcode
AND param2 = p_mode;
INSERT INTO JTY_CONC_REQ_SUMM (
conc_req_id
,requested_by
,request_date
,responsibility_application_id
,responsibility_id
,last_updated_by
,last_update_date
,last_update_login
,start_date
,end_date
,param1
,param2
,param3
,param4
,param5
,program_application_id
,program_name
,retcode
,errbuf
,request_id
,conc_program_id
,object_version_number)
VALUES (
jty_conc_req_summ_s.nextval
,l_user_id
,l_start_date
,l_resp_appl_id
,l_resp_id
,l_user_id
,l_start_date
,l_login_id
,l_start_date
,l_end_date
,TO_CHAR(p_source_id)
,p_mode
,TO_CHAR(l_param_start_date, 'DD/MM/YYYY HH24:MI:SS')
,TO_CHAR(l_param_end_date, 'DD/MM/YYYY HH24:MI:SS')
,null
,l_pgm_appl_id
,l_pgm_name
,retcode
,errbuf
,g_request_id
,l_conc_pgm_id
,0);