[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT num_rows
INTO x_num_rows
FROM all_tables
WHERE owner = l_jtf_schema
AND table_name = p_table_name;
SELECT COUNT(*)
INTO num_of_terr
FROM jtf_terr_qtype_usgs_all jtqu
, jtf_terr_usgs_all jtu
, 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_object_type_id
AND jtu.source_id = p_source_id
AND jtu.terr_id = jt1.terr_id
AND NVL(jt1.end_date_active, lp_sysdate) >= lp_sysdate
AND jt1.start_date_active <= lp_sysdate
AND EXISTS (
SELECT jtrs.terr_rsc_id
FROM jtf_terr_rsc_all jtrs
WHERE NVL(jtrs.end_date_active, lp_sysdate) >= lp_sysdate
AND NVL(jtrs.start_date_active, lp_sysdate) <= lp_sysdate
AND jtrs.terr_id = jt1.terr_id )
AND NOT EXISTS (
SELECT jt.terr_id
FROM jtf_terr_all jt
WHERE NVL(jt.end_date_active, lp_sysdate + 1) < lp_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 ';
d_statement := ' UPDATE ' || l_trans_target || ' SET worker_id = 1';
' INSERT INTO ' ||
l_winners_target || ' i ' ||
' ( ' ||
' TRANS_OBJECT_ID ' ||
' ,TRANS_DETAIL_OBJECT_ID ' ||
' ,WORKER_ID ' || /* ARPATEL 05/03/2004 Bug#3608474 */
/*
** 07/17/03 JDOCHERT: NOT USED
**' ,HEADER_ID1 ' ||
**' ,HEADER_ID2 ' ||
*/
' ,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 ' ||
' ,PRIMARY_CONTACT_FLAG ' ||
' ,PERSON_ID ' ||
' ,ORG_ID ' ||
' ,TERR_RSC_ID ' ||
' ,FULL_ACCESS_FLAG ' ||
' ) ' ||
' ( ' ||
--
-- 10/02/02: JDOCHERT: BUG#2594526 and BUG#2602646
--
--' SELECT /*+ ' ||
--' INDEX (jtr JTF_TERR_RSC_N1) ' ||
--' INDEX (jtra JTF_TERR_RSC_ACCESS_N1) ' ||
--' */ ' ||
--
' SELECT DISTINCT ' ||
' WINNERS.trans_object_id ' ||
' , WINNERS.trans_detail_object_id ' ||
' , 1 ' || /* ARPATEL 05/03/2004 Bug#3608474 Default value to 1 for non-parallel get_winners */
/*
** 07/17/03 JDOCHERT: NOT USED
**' , 0 header_id1 ' || -- o_dttm.header_id1 ' ||
**' , 0 header_id2 ' || -- o_dttm.header_id2 ' ||
*/
' , :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.WIN_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 ' ||
' , jtr.role ' ||
' , jtr.primary_contact_flag ' ||
' , jtr.PERSON_ID ' ||
' , jtr.org_id ' ||
' , jtr.terr_rsc_id ' ||
' , jtr.full_access_flag ' ||
' FROM ( /* WINNERS ILV */ ' ||
' SELECT LX.trans_object_id ' ||
' , LX.trans_detail_object_id ' ||
' , LX.WIN_TERR_ID ' ||
' FROM ' || l_terr_L1_target || ' LX ' ||
' , ( SELECT trans_object_id ' ||
' , trans_detail_object_id ' ||
' , WIN_TERR_ID WIN_TERR_ID ' ||
' FROM ' || l_terr_L1_target ||
' MINUS ' ||
' SELECT trans_object_id ' ||
' , trans_detail_object_id ' ||
' , ul_terr_id WIN_TERR_ID ' ||
' FROM ' || l_terr_L2_target || ' ) ILV ' ||
' WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id OR ' ||
' LX.trans_detail_object_id IS NULL ) ' ||
' AND LX.trans_object_id = ILV.trans_object_id ' ||
' AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID ' ||
' UNION ALL ' ||
' SELECT LX.trans_object_id ' ||
' , LX.trans_detail_object_id ' ||
' , LX.WIN_TERR_ID ' ||
' FROM ' || l_terr_L2_target || ' LX ' ||
' , ( SELECT trans_object_id ' ||
' , trans_detail_object_id ' ||
' , WIN_TERR_ID WIN_TERR_ID ' ||
' FROM ' || l_terr_L2_target ||
' MINUS ' ||
' SELECT trans_object_id ' ||
' , trans_detail_object_id ' ||
' , ul_terr_id WIN_TERR_ID ' ||
' FROM ' || l_terr_L3_target || ' ) ILV ' ||
' WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id OR ' ||
' LX.trans_detail_object_id IS NULL ) ' ||
' AND LX.trans_object_id = ILV.trans_object_id ' ||
' AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID ' ||
' UNION ALL ' ||
' SELECT LX.trans_object_id ' ||
' , LX.trans_detail_object_id ' ||
' , LX.WIN_TERR_ID ' ||
' FROM ' || l_terr_L3_target || ' LX ' ||
' , ( SELECT trans_object_id ' ||
' , trans_detail_object_id ' ||
' , WIN_TERR_ID WIN_TERR_ID ' ||
' FROM ' || l_terr_L3_target ||
' MINUS ' ||
' SELECT trans_object_id ' ||
' , trans_detail_object_id ' ||
' , ul_terr_id WIN_TERR_ID ' ||
' FROM ' || l_terr_L4_target || ' ) ILV ' ||
' WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id OR ' ||
' LX.trans_detail_object_id IS NULL ) ' ||
' AND LX.trans_object_id = ILV.trans_object_id ' ||
' AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID ' ||
' UNION ALL ' ||
' SELECT LX.trans_object_id ' ||
' , LX.trans_detail_object_id ' ||
' , LX.WIN_TERR_ID ' ||
' FROM ' || l_terr_L4_target || ' LX ' ||
' , ( SELECT trans_object_id ' ||
' , trans_detail_object_id ' ||
' , WIN_TERR_ID WIN_TERR_ID ' ||
' FROM ' || l_terr_L4_target ||
' MINUS ' ||
' SELECT trans_object_id ' ||
' , trans_detail_object_id ' ||
' , ul_terr_id WIN_TERR_ID ' ||
' FROM ' || l_terr_L5_target || ' ) ILV ' ||
' WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id OR ' ||
' LX.trans_detail_object_id IS NULL ) ' ||
' AND LX.trans_object_id = ILV.trans_object_id ' ||
' AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID ' ||
' UNION ALL ' ||
' SELECT LX.trans_object_id ' ||
' , LX.trans_detail_object_id ' ||
' , LX.WIN_TERR_ID ' ||
' FROM ' || l_terr_L5_target || ' LX ' ||
' , ( SELECT trans_object_id ' ||
' , trans_detail_object_id ' ||
' , WIN_TERR_ID WIN_TERR_ID ' ||
' FROM ' || l_terr_L5_target ||
' MINUS ' ||
' SELECT trans_object_id ' ||
' , trans_detail_object_id ' ||
' , ul_terr_id WIN_TERR_ID ' ||
' FROM ' || l_terr_WT_target || ' ) ILV ' ||
' WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id OR ' ||
' LX.trans_detail_object_id IS NULL ) ' ||
' AND LX.trans_object_id = ILV.trans_object_id ' ||
' AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID ' ||
' UNION ALL ' ||
' SELECT trans_object_id ' ||
' , trans_detail_object_id ' ||
' , WIN_TERR_ID ' ||
' FROM ' || l_terr_WT_target ||
' ) WINNERS ' ||
' , jtf_terr_rsc_all jtr ' ||
' , jtf_terr_rsc_access_all jtra ' ||
' WHERE WINNERS.WIN_terr_id = jtr.terr_id ' ||
' AND ( ( jtr.end_date_active IS NULL OR jtr.end_date_active >= :BV1_SYSDATE ) AND ' ||
' ( jtr.start_date_active IS NULL OR jtr.start_date_active <= :BV2_SYSDATE ) ' ||
' ) ' ||
' AND jtr.terr_rsc_id = jtra.terr_rsc_id ' ||
' AND jtra.access_type = ' || l_access_list ||
' ) ';
, 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 */
, p_request_id /* :BV1_REQUEST_ID */
, G_APPL_ID /* :BV1_PROGRAM_APPLICATION_ID */
, G_PROGRAM_ID /* :BV1_PROGRAM_ID */
, lp_sysdate /* :BV1_PROGRAM_UPDATE_DATE */
, lp_sysdate /* :BV1_SYSDATE */
, lp_sysdate; /* :BV2_SYSDATE */
JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'Number of records inserted into ' || l_winners_target ||
' = ' || SQL%ROWCOUNT );
SELECT COUNT(*)
INTO num_of_terr
FROM jtf_terr_qtype_usgs_all jtqu
, jtf_terr_usgs_all jtu
, 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_object_type_id
AND jtu.source_id = p_source_id
AND jtu.terr_id = jt1.terr_id
AND NVL(jt1.end_date_active, lp_sysdate) >= lp_sysdate
AND jt1.start_date_active <= lp_sysdate
AND EXISTS (
SELECT jtrs.terr_rsc_id
FROM jtf_terr_rsc_all jtrs
WHERE NVL(jtrs.end_date_active, lp_sysdate) >= lp_sysdate
AND NVL(jtrs.start_date_active, lp_sysdate) <= lp_sysdate
AND jtrs.terr_id = jt1.terr_id )
AND NOT EXISTS (
SELECT jt.terr_id
FROM jtf_terr_all jt
WHERE NVL(jt.end_date_active, lp_sysdate + 1) < lp_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 ';
l_trans_count_sql := 'SELECT COUNT(*) FROM ' || l_trans_target ||
' where worker_id = ' || p_worker_id || ' and rownum < 2 ';
' INSERT INTO ' ||
l_winners_target || ' i ' ||
' ( ' ||
' TRANS_OBJECT_ID ' ||
' ,TRANS_DETAIL_OBJECT_ID ' ||
' ,WORKER_ID ' ||
/*
** 07/17/03 JDOCHERT: NOT USED
**' ,HEADER_ID1 ' ||
**' ,HEADER_ID2 ' ||
*/
' ,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 ' ||
' ,PRIMARY_CONTACT_FLAG ' ||
' ,PERSON_ID ' ||
' ,ORG_ID ' ||
' ,TERR_RSC_ID ' ||
' ,FULL_ACCESS_FLAG ' ||
' ) ' ||
' ( ' ||
--
-- 10/02/02: JDOCHERT: BUG#2594526 and BUG#2602646
--
--' SELECT /*+ ' ||
--' INDEX (jtr JTF_TERR_RSC_N1) ' ||
--' INDEX (jtra JTF_TERR_RSC_ACCESS_N1) ' ||
--' */ ' ||
--
/* remove the distinct clause as suggested by appsperf : bug 4322586 */
' SELECT ' || -- DISTINCT ' ||
' WINNERS.trans_object_id ' ||
' , WINNERS.trans_detail_object_id ' ||
' , :bv_worker_id ' || --p_worker_id ||
/*
** 07/17/03 JDOCHERT: NOT USED
**' , 0 header_id1 ' || -- o_dttm.header_id1 ' ||
**' , 0 header_id2 ' || -- o_dttm.header_id2 ' ||
*/
' , :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.WIN_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 ' ||
' , jtr.role ' ||
' , jtr.primary_contact_flag ' ||
' , jtr.PERSON_ID ' ||
' , jtr.org_id ' ||
' , jtr.terr_rsc_id ' ||
' , jtr.full_access_flag ' ||
' FROM ( /* WINNERS ILV */ ' ||
' SELECT LX.trans_object_id ' ||
' , LX.trans_detail_object_id ' ||
' , LX.WIN_TERR_ID ' ||
' FROM ' || l_terr_L1_target || ' LX ' ||
' , ( SELECT trans_object_id ' ||
' , trans_detail_object_id ' ||
' , WIN_TERR_ID WIN_TERR_ID ' ||
' FROM ' || l_terr_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_terr_L2_target ||
' WHERE WORKER_ID = :bv_worker_id ' ||
' ) ILV ' ||
' WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id OR ' ||
' LX.trans_detail_object_id IS NULL ) ' ||
' AND LX.trans_object_id = ILV.trans_object_id ' ||
' AND LX.WORKER_ID = :bv_worker_id ' ||
' AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID ' ||
' UNION ALL ' ||
' SELECT LX.trans_object_id ' ||
' , LX.trans_detail_object_id ' ||
' , LX.WIN_TERR_ID ' ||
' FROM ' || l_terr_L2_target || ' LX ' ||
' , ( SELECT trans_object_id ' ||
' , trans_detail_object_id ' ||
' , WIN_TERR_ID WIN_TERR_ID ' ||
' FROM ' || l_terr_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_terr_L3_target ||
' WHERE WORKER_ID = :bv_worker_id ' ||
' ) ILV ' ||
' WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id OR ' ||
' LX.trans_detail_object_id IS NULL ) ' ||
' AND LX.trans_object_id = ILV.trans_object_id ' ||
' AND LX.WORKER_ID = :bv_worker_id ' ||
' AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID ' ||
' UNION ALL ' ||
' SELECT LX.trans_object_id ' ||
' , LX.trans_detail_object_id ' ||
' , LX.WIN_TERR_ID ' ||
' FROM ' || l_terr_L3_target || ' LX ' ||
' , ( SELECT trans_object_id ' ||
' , trans_detail_object_id ' ||
' , WIN_TERR_ID WIN_TERR_ID ' ||
' FROM ' || l_terr_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_terr_L4_target ||
' WHERE WORKER_ID = :bv_worker_id ' ||
' ) ILV ' ||
' WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id OR ' ||
' LX.trans_detail_object_id IS NULL ) ' ||
' AND LX.trans_object_id = ILV.trans_object_id ' ||
' AND LX.WORKER_ID = :bv_worker_id ' ||
' AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID ' ||
' UNION ALL ' ||
' SELECT LX.trans_object_id ' ||
' , LX.trans_detail_object_id ' ||
' , LX.WIN_TERR_ID ' ||
' FROM ' || l_terr_L4_target || ' LX ' ||
' , ( SELECT trans_object_id ' ||
' , trans_detail_object_id ' ||
' , WIN_TERR_ID WIN_TERR_ID ' ||
' FROM ' || l_terr_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_terr_L5_target ||
' WHERE WORKER_ID = :bv_worker_id ' ||
' ) ILV ' ||
' WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id OR ' ||
' LX.trans_detail_object_id IS NULL ) ' ||
' AND LX.trans_object_id = ILV.trans_object_id ' ||
' AND LX.WORKER_ID = :bv_worker_id ' ||
' AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID ' ||
' UNION ALL ' ||
' SELECT LX.trans_object_id ' ||
' , LX.trans_detail_object_id ' ||
' , LX.WIN_TERR_ID ' ||
' FROM ' || l_terr_L5_target || ' LX ' ||
' , ( SELECT trans_object_id ' ||
' , trans_detail_object_id ' ||
' , WIN_TERR_ID WIN_TERR_ID ' ||
' FROM ' || l_terr_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_terr_WT_target ||
' WHERE WORKER_ID = :bv_worker_id ' ||
' ) ILV ' ||
' WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id OR ' ||
' LX.trans_detail_object_id IS NULL ) ' ||
' AND LX.trans_object_id = ILV.trans_object_id ' ||
' AND LX.WORKER_ID = :bv_worker_id ' ||
' AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID ' ||
' UNION ALL ' ||
' SELECT trans_object_id ' ||
' , trans_detail_object_id ' ||
' , WIN_TERR_ID ' ||
' FROM ' || l_terr_WT_target ||
' WHERE WORKER_ID = :bv_worker_id ' ||
' ) WINNERS ' ||
' , jtf_terr_rsc_all jtr ' ||
' , jtf_terr_rsc_access_all jtra ' ||
' WHERE WINNERS.WIN_terr_id = jtr.terr_id ' ||
' AND ( ( jtr.end_date_active IS NULL OR jtr.end_date_active >= :BV1_SYSDATE ) AND ' ||
' ( jtr.start_date_active IS NULL OR jtr.start_date_active <= :BV2_SYSDATE ) ' ||
' ) ' ||
' AND jtr.terr_rsc_id = jtra.terr_rsc_id ' ||
' AND jtra.access_type = ' || l_access_list ||
' ) ';
, 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 */
, p_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 */
, p_worker_id /* :bv_worker_id */
, p_worker_id /* :bv_worker_id */
, p_worker_id /* :bv_worker_id */
, p_worker_id /* :bv_worker_id */ --15
, p_worker_id /* :bv_worker_id */
, lp_sysdate /* :BV1_SYSDATE */
, lp_sysdate; /* :BV2_SYSDATE */
'Number of records inserted into ' || l_winners_target ||
' = ' || SQL%ROWCOUNT );
'INSERT INTO ' || p_terr_LEVEL_target_tbl ||
' ( ' ||
' trans_object_id ' ||
' , trans_detail_object_id ' ||
' , 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.CL_WIN_TERR_ID ' ||
' , TL.UL_terr_id ' ||
' , TL.LL_terr_id ' ||
' , TL.LL_num_winners ' ||
' , :B_WORKER_ID ' || --p_worker_id || bug#3391453
' FROM ( ' ||
' SELECT ';
'INSERT INTO ' || p_terr_LEVEL_target_tbl ||
' ( ' ||
' trans_object_id ' ||
' , trans_detail_object_id ' ||
' , 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.CL_WIN_TERR_ID ' ||
' , TL.UL_terr_id ' ||
' , TL.LL_terr_id ' ||
' , TL.LL_num_winners ' ||
' , :B_WORKER_ID ' || --p_worker_id || bug#3391453
' FROM ( ' || /* NL */
' SELECT ' ||
' CL.trans_object_id ' ||
' , CL.trans_detail_object_id ' ||
' , CL.CL_WIN_TERR_ID ' ||
--' , CL.CL_ABS_RANK ' ||
' , CL.UL_terr_id ';
' SELECT ';
' SELECT ';
'INSERT INTO ' || p_terr_LEVEL_target_tbl ||
' ( ' ||
' trans_object_id ' ||
' , trans_detail_object_id ' ||
' , WIN_TERR_ID ' ||
' , UL_TERR_ID ' ||
' , LL_TERR_ID ' ||
' , worker_id ' ||
' ) ' ||
' ( SELECT ' ||
' TL.trans_object_id ' ||
' , TL.trans_detail_object_id ' ||
' , TL.WIN_TERR_ID ' ||
' , TL.UL_terr_id ' ||
' , TL.terr_id ' ||
' , :B_WORKER_ID ' || --p_worker_id ||
' FROM ( ' ||
' SELECT ';
UPDATE JTF_TAE_1001_ACCOUNT_TRANS
SET WORKER_ID = 1
WHERE WORKER_ID <> 1;
UPDATE JTF_TAE_1001_LEAD_TRANS
SET WORKER_ID = 1
WHERE WORKER_ID <> 1;
UPDATE JTF_TAE_1001_OPPOR_TRANS
SET WORKER_ID = 1
WHERE WORKER_ID <> 1;
PROCEDURE DELETE_CHANGED_TERR_RECS
( p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_SQL_Trace IN VARCHAR2,
p_Debug_Flag IN VARCHAR2,
p_request_id IN VARCHAR2,
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
BEGIN
DELETE FROM JTF_CHANGED_TERR_ALL
WHERE REQUEST_ID = p_request_id;
ERRBUF := 'JTF_TAE_ASSIGN_PUB.DELETE_CHANGED_TERR_RECS: [END] OTHERS: ' ||
SQLERRM;
END DELETE_CHANGED_TERR_RECS;