[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT tup.real_time_match_dea_sql
INTO l_matching_sql
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 tup.real_time_match_sql
INTO l_matching_sql
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;
/* Execute the matching SQL, which will insert matching territories */
/* corresponding to the transaction objects into jtf_terr_results_gt_mt */
DELETE jtf_terr_results_gt_mt;
SELECT COUNT(*)
INTO l_match_no_of_records
FROM jtf_terr_results_gt_mt;
'Number of records inserted into jtf_terr_results_gt_mt table : ' || l_match_no_of_records);
SELECT DISTINCT
WT.trans_object_id
,WT.trans_detail_object_id
,WT.txn_date
,WT.terr_id
,jta.org_id
,jtr.person_id
,jta.start_date_active
,jta.end_date_active
,jtr.terr_rsc_id
,jta.name
,null top_level_terr_id
,jta.absolute_rank absolute_rank
,jtr.resource_id
,jtr.start_date_active
,jtr.end_date_active
,jtr.resource_type
,jtr.group_id
,inv.role_id
,jtr.role
,decode(jtra.trans_access_code, 'FULL_ACCESS', 'Y', 'N')
,decode(jtra.trans_access_code, 'TEAM_LEADER', 'Y', 'PRIMARY_CONTACT', 'Y', 'N') --jtr.primary_contact_flag
,jta.attribute_category terr_attr_category
,jta.attribute1 terr_attribute1
,jta.attribute2 terr_attribute2
,jta.attribute3 terr_attribute3
,jta.attribute4 terr_attribute4
,jta.attribute5 terr_attribute5
,jta.attribute6 terr_attribute6
,jta.attribute7 terr_attribute7
,jta.attribute8 terr_attribute8
,jta.attribute9 terr_attribute9
,jta.attribute10 terr_attribute10
,jta.attribute11 terr_attribute11
,jta.attribute12 terr_attribute12
,jta.attribute13 terr_attribute13
,jta.attribute14 terr_attribute14
,jta.attribute15 terr_attribute15
,jtr.attribute_category rsc_attr_category
,jtr.attribute1 rsc_attribute1
,jtr.attribute2 rsc_attribute2
,jtr.attribute3 rsc_attribute3
,jtr.attribute4 rsc_attribute4
,jtr.attribute5 rsc_attribute5
,jtr.attribute6 rsc_attribute6
,jtr.attribute7 rsc_attribute7
,jtr.attribute8 rsc_attribute8
,jtr.attribute9 rsc_attribute9
,jtr.attribute10 rsc_attribute10
,jtr.attribute11 rsc_attribute11
,jtr.attribute12 rsc_attribute12
,jtr.attribute13 rsc_attribute13
,jtr.attribute14 rsc_attribute14
,jtr.attribute15 rsc_attribute15
BULK COLLECT INTO
x_winners_rec.trans_object_id
,x_winners_rec.trans_detail_object_id
,x_winners_rec.txn_date
,x_winners_rec.terr_id
,x_winners_rec.org_id
,x_winners_rec.person_id
,x_winners_rec.terr_start_date
,x_winners_rec.terr_end_date
,x_winners_rec.terr_rsc_id
,x_winners_rec.terr_name
,x_winners_rec.top_level_terr_id
,x_winners_rec.absolute_rank
,x_winners_rec.resource_id
,x_winners_rec.rsc_start_date
,x_winners_rec.rsc_end_date
,x_winners_rec.resource_type
,x_winners_rec.group_id
,x_winners_rec.role_id
,x_winners_rec.role
,x_winners_rec.full_access_flag
,x_winners_rec.primary_contact_flag
,x_winners_rec.terr_attr_category
,x_winners_rec.terr_attribute1
,x_winners_rec.terr_attribute2
,x_winners_rec.terr_attribute3
,x_winners_rec.terr_attribute4
,x_winners_rec.terr_attribute5
,x_winners_rec.terr_attribute6
,x_winners_rec.terr_attribute7
,x_winners_rec.terr_attribute8
,x_winners_rec.terr_attribute9
,x_winners_rec.terr_attribute10
,x_winners_rec.terr_attribute11
,x_winners_rec.terr_attribute12
,x_winners_rec.terr_attribute13
,x_winners_rec.terr_attribute14
,x_winners_rec.terr_attribute15
,x_winners_rec.rsc_attr_category
,x_winners_rec.rsc_attribute1
,x_winners_rec.rsc_attribute2
,x_winners_rec.rsc_attribute3
,x_winners_rec.rsc_attribute4
,x_winners_rec.rsc_attribute5
,x_winners_rec.rsc_attribute6
,x_winners_rec.rsc_attribute7
,x_winners_rec.rsc_attribute8
,x_winners_rec.rsc_attribute9
,x_winners_rec.rsc_attribute10
,x_winners_rec.rsc_attribute11
,x_winners_rec.rsc_attribute12
,x_winners_rec.rsc_attribute13
,x_winners_rec.rsc_attribute14
,x_winners_rec.rsc_attribute15
FROM
( 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 jtf_terr_results_GT_MT i ) 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_all jta
, jtf_terr_rsc_all jtr
, jtf_terr_rsc_access_all jtra
, jtf_qual_types_all jqta
, jtf_rs_roles_b inv
WHERE WT.terr_id = jta.terr_id
AND WT.terr_id = jtr.terr_id
AND ( (TRUNC(jtr.start_date_active) BETWEEN trunc(p_plan_start_date) AND trunc(p_plan_end_date) ) OR
( TRUNC(jtr.end_date_active) BETWEEN trunc(p_plan_start_date) AND trunc(p_plan_end_date) ) OR
( TRUNC(jtr.start_date_active) <= trunc(p_plan_start_date) AND TRUNC(jtr.end_date_active) >= trunc(p_plan_end_date) )
)
AND jtr.resource_type <> 'RS_ROLE'
AND jtr.terr_rsc_id = jtra.terr_rsc_id
AND jtra.access_type = jqta.name
AND jtra.trans_access_code NOT IN ('NONE', 'ESC_OWNER')
AND (( p_role IS NULL ) OR ( jtr.role = p_role ))
AND jtr.role = inv.role_code(+)
AND (( p_resource_type IS NULL) OR ( jtr.resource_type = p_resource_type ))
AND jqta.qual_type_id = p_trans_id
ORDER BY WT.trans_object_id, jta.org_id, WT.terr_id, jtr.resource_id;
SELECT DISTINCT
WT.trans_object_id
,WT.trans_detail_object_id
,WT.txn_date
,WT.terr_id
,jta.org_id
,jtr.person_id
,jta.start_date_active
,jta.end_date_active
,jtr.terr_rsc_id
,jta.name
,null top_level_terr_id
,jta.absolute_rank absolute_rank
,jtr.resource_id
,jtr.start_date_active
,jtr.end_date_active
,jtr.resource_type
,jtr.group_id
,inv.role_id
,jtr.role
,decode(jtra.trans_access_code, 'FULL_ACCESS', 'Y', 'N')
,decode(jtra.trans_access_code, 'TEAM_LEADER', 'Y', 'PRIMARY_CONTACT', 'Y', 'N') --jtr.primary_contact_flag
,jta.attribute_category terr_attr_category
,jta.attribute1 terr_attribute1
,jta.attribute2 terr_attribute2
,jta.attribute3 terr_attribute3
,jta.attribute4 terr_attribute4
,jta.attribute5 terr_attribute5
,jta.attribute6 terr_attribute6
,jta.attribute7 terr_attribute7
,jta.attribute8 terr_attribute8
,jta.attribute9 terr_attribute9
,jta.attribute10 terr_attribute10
,jta.attribute11 terr_attribute11
,jta.attribute12 terr_attribute12
,jta.attribute13 terr_attribute13
,jta.attribute14 terr_attribute14
,jta.attribute15 terr_attribute15
,jtr.attribute_category rsc_attr_category
,jtr.attribute1 rsc_attribute1
,jtr.attribute2 rsc_attribute2
,jtr.attribute3 rsc_attribute3
,jtr.attribute4 rsc_attribute4
,jtr.attribute5 rsc_attribute5
,jtr.attribute6 rsc_attribute6
,jtr.attribute7 rsc_attribute7
,jtr.attribute8 rsc_attribute8
,jtr.attribute9 rsc_attribute9
,jtr.attribute10 rsc_attribute10
,jtr.attribute11 rsc_attribute11
,jtr.attribute12 rsc_attribute12
,jtr.attribute13 rsc_attribute13
,jtr.attribute14 rsc_attribute14
,jtr.attribute15 rsc_attribute15
BULK COLLECT INTO
x_winners_rec.trans_object_id
,x_winners_rec.trans_detail_object_id
,x_winners_rec.txn_date
,x_winners_rec.terr_id
,x_winners_rec.org_id
,x_winners_rec.person_id
,x_winners_rec.terr_start_date
,x_winners_rec.terr_end_date
,x_winners_rec.terr_rsc_id
,x_winners_rec.terr_name
,x_winners_rec.top_level_terr_id
,x_winners_rec.absolute_rank
,x_winners_rec.resource_id
,x_winners_rec.rsc_start_date
,x_winners_rec.rsc_end_date
,x_winners_rec.resource_type
,x_winners_rec.group_id
,x_winners_rec.role_id
,x_winners_rec.role
,x_winners_rec.full_access_flag
,x_winners_rec.primary_contact_flag
,x_winners_rec.terr_attr_category
,x_winners_rec.terr_attribute1
,x_winners_rec.terr_attribute2
,x_winners_rec.terr_attribute3
,x_winners_rec.terr_attribute4
,x_winners_rec.terr_attribute5
,x_winners_rec.terr_attribute6
,x_winners_rec.terr_attribute7
,x_winners_rec.terr_attribute8
,x_winners_rec.terr_attribute9
,x_winners_rec.terr_attribute10
,x_winners_rec.terr_attribute11
,x_winners_rec.terr_attribute12
,x_winners_rec.terr_attribute13
,x_winners_rec.terr_attribute14
,x_winners_rec.terr_attribute15
,x_winners_rec.rsc_attr_category
,x_winners_rec.rsc_attribute1
,x_winners_rec.rsc_attribute2
,x_winners_rec.rsc_attribute3
,x_winners_rec.rsc_attribute4
,x_winners_rec.rsc_attribute5
,x_winners_rec.rsc_attribute6
,x_winners_rec.rsc_attribute7
,x_winners_rec.rsc_attribute8
,x_winners_rec.rsc_attribute9
,x_winners_rec.rsc_attribute10
,x_winners_rec.rsc_attribute11
,x_winners_rec.rsc_attribute12
,x_winners_rec.rsc_attribute13
,x_winners_rec.rsc_attribute14
,x_winners_rec.rsc_attribute15
FROM
( 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 jtf_terr_results_GT_MT i ) 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_all jta
, jtf_terr_rsc_all jtr
, jtf_terr_rsc_access_all jtra
, jtf_qual_types_all jqta
, jtf_rs_roles_b inv
WHERE WT.terr_id = jta.terr_id
AND 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 jtra.access_type = jqta.name
AND jtra.trans_access_code NOT IN ('NONE', 'ESC_OWNER')
AND (( p_role IS NULL ) OR ( jtr.role = p_role ))
AND jtr.role = inv.role_code(+)
AND (( p_resource_type IS NULL) OR ( jtr.resource_type = p_resource_type ))
AND jqta.qual_type_id = p_trans_id
ORDER BY WT.trans_object_id, jta.org_id, WT.terr_id, jtr.resource_id;
SELECT DISTINCT
WT.trans_object_id
,WT.trans_detail_object_id
,WT.txn_date
,WT.terr_id
,jta.org_id
,jta.start_date_active
,jta.end_date_active
,jta.name
,null top_level_terr_id
,jta.absolute_rank absolute_rank
,jta.attribute_category terr_attr_category
,jta.attribute1 terr_attribute1
,jta.attribute2 terr_attribute2
,jta.attribute3 terr_attribute3
,jta.attribute4 terr_attribute4
,jta.attribute5 terr_attribute5
,jta.attribute6 terr_attribute6
,jta.attribute7 terr_attribute7
,jta.attribute8 terr_attribute8
,jta.attribute9 terr_attribute9
,jta.attribute10 terr_attribute10
,jta.attribute11 terr_attribute11
,jta.attribute12 terr_attribute12
,jta.attribute13 terr_attribute13
,jta.attribute14 terr_attribute14
,jta.attribute15 terr_attribute15
BULK COLLECT INTO
x_winners_rec.trans_object_id
,x_winners_rec.trans_detail_object_id
,x_winners_rec.txn_date
,x_winners_rec.terr_id
,x_winners_rec.org_id
,x_winners_rec.terr_start_date
,x_winners_rec.terr_end_date
,x_winners_rec.terr_name
,x_winners_rec.top_level_terr_id
,x_winners_rec.absolute_rank
,x_winners_rec.terr_attr_category
,x_winners_rec.terr_attribute1
,x_winners_rec.terr_attribute2
,x_winners_rec.terr_attribute3
,x_winners_rec.terr_attribute4
,x_winners_rec.terr_attribute5
,x_winners_rec.terr_attribute6
,x_winners_rec.terr_attribute7
,x_winners_rec.terr_attribute8
,x_winners_rec.terr_attribute9
,x_winners_rec.terr_attribute10
,x_winners_rec.terr_attribute11
,x_winners_rec.terr_attribute12
,x_winners_rec.terr_attribute13
,x_winners_rec.terr_attribute14
,x_winners_rec.terr_attribute15
FROM
( 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 jtf_terr_results_GT_MT i ) 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_all jta
WHERE WT.terr_id = jta.terr_id
AND EXISTS (
SELECT 1
FROM
jtf_terr_rsc_all jtr
, jtf_terr_rsc_access_all jtra
, jtf_qual_types_all jqta
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 jtra.trans_access_code NOT IN ('NONE', 'ESC_OWNER')
AND jtra.access_type = jqta.name
AND (( p_role IS NULL ) OR ( jtr.role = p_role ))
AND (( p_resource_type IS NULL) OR ( jtr.resource_type = p_resource_type ))
AND jqta.qual_type_id = p_trans_id);
SELECT /*+ PUSH_PRED(rsv) */ DISTINCT
WT.trans_object_id
,WT.trans_detail_object_id
,WT.txn_date
,WT.terr_id
,jta.org_id
,jtr.person_id
,jta.start_date_active
,jta.end_date_active
,jtr.terr_rsc_id
,jta.name
,null top_level_terr_id
,jta.absolute_rank absolute_rank
,jtr.resource_id
,jtr.start_date_active
,jtr.end_date_active
,jtr.resource_type
,jtr.group_id
,role.role_id
,jtr.role
,decode(jtra.trans_access_code, 'FULL_ACCESS', 'Y', 'N')
,decode(jtra.trans_access_code, 'TEAM_LEADER', 'Y', 'PRIMARY_CONTACT', 'Y', 'N') --jtr.primary_contact_flag
,rsv.resource_name
,rsc.source_job_title resource_job_title
,rsc.source_phone resource_phone
,rsc.source_email resource_email
,rsc.source_mgr_name resource_mgr_name
,mgr.source_phone resource_mgr_phone
,mgr.source_email resource_mgr_email
,jta.name property1
,role.role_name property2
,jta_p.name property3
,rsc.attribute4 property4
,rsc.attribute5 property5
,rsc.attribute6 property6
,rsc.attribute7 property7
,rsc.attribute8 property8
,rsc.attribute9 property9
,rsc.attribute10 property10
,rsc.attribute11 property11
,rsc.attribute12 property12
,rsc.attribute13 property13
,rsc.attribute14 property14
,rsc.attribute15 property15
,jta.attribute_category terr_attr_category
,jta.attribute1 terr_attribute1
,jta.attribute2 terr_attribute2
,jta.attribute3 terr_attribute3
,jta.attribute4 terr_attribute4
,jta.attribute5 terr_attribute5
,jta.attribute6 terr_attribute6
,jta.attribute7 terr_attribute7
,jta.attribute8 terr_attribute8
,jta.attribute9 terr_attribute9
,jta.attribute10 terr_attribute10
,jta.attribute11 terr_attribute11
,jta.attribute12 terr_attribute12
,jta.attribute13 terr_attribute13
,jta.attribute14 terr_attribute14
,jta.attribute15 terr_attribute15
,jtr.attribute_category rsc_attr_category
,jtr.attribute1 rsc_attribute1
,jtr.attribute2 rsc_attribute2
,jtr.attribute3 rsc_attribute3
,jtr.attribute4 rsc_attribute4
,jtr.attribute5 rsc_attribute5
,jtr.attribute6 rsc_attribute6
,jtr.attribute7 rsc_attribute7
,jtr.attribute8 rsc_attribute8
,jtr.attribute9 rsc_attribute9
,jtr.attribute10 rsc_attribute10
,jtr.attribute11 rsc_attribute11
,jtr.attribute12 rsc_attribute12
,jtr.attribute13 rsc_attribute13
,jtr.attribute14 rsc_attribute14
,jtr.attribute15 rsc_attribute15
BULK COLLECT INTO
x_winners_rec.trans_object_id
,x_winners_rec.trans_detail_object_id
,x_winners_rec.txn_date
,x_winners_rec.terr_id
,x_winners_rec.org_id
,x_winners_rec.person_id
,x_winners_rec.terr_start_date
,x_winners_rec.terr_end_date
,x_winners_rec.terr_rsc_id
,x_winners_rec.terr_name
,x_winners_rec.top_level_terr_id
,x_winners_rec.absolute_rank
,x_winners_rec.resource_id
,x_winners_rec.rsc_start_date
,x_winners_rec.rsc_end_date
,x_winners_rec.resource_type
,x_winners_rec.group_id
,x_winners_rec.role_id
,x_winners_rec.role
,x_winners_rec.full_access_flag
,x_winners_rec.primary_contact_flag
,x_winners_rec.resource_name
,x_winners_rec.resource_job_title
,x_winners_rec.resource_phone
,x_winners_rec.resource_email
,x_winners_rec.resource_mgr_name
,x_winners_rec.resource_mgr_phone
,x_winners_rec.resource_mgr_email
,x_winners_rec.property1
,x_winners_rec.property2
,x_winners_rec.property3
,x_winners_rec.property4
,x_winners_rec.property5
,x_winners_rec.property6
,x_winners_rec.property7
,x_winners_rec.property8
,x_winners_rec.property9
,x_winners_rec.property10
,x_winners_rec.property11
,x_winners_rec.property12
,x_winners_rec.property13
,x_winners_rec.property14
,x_winners_rec.property15
,x_winners_rec.terr_attr_category
,x_winners_rec.terr_attribute1
,x_winners_rec.terr_attribute2
,x_winners_rec.terr_attribute3
,x_winners_rec.terr_attribute4
,x_winners_rec.terr_attribute5
,x_winners_rec.terr_attribute6
,x_winners_rec.terr_attribute7
,x_winners_rec.terr_attribute8
,x_winners_rec.terr_attribute9
,x_winners_rec.terr_attribute10
,x_winners_rec.terr_attribute11
,x_winners_rec.terr_attribute12
,x_winners_rec.terr_attribute13
,x_winners_rec.terr_attribute14
,x_winners_rec.terr_attribute15
,x_winners_rec.rsc_attr_category
,x_winners_rec.rsc_attribute1
,x_winners_rec.rsc_attribute2
,x_winners_rec.rsc_attribute3
,x_winners_rec.rsc_attribute4
,x_winners_rec.rsc_attribute5
,x_winners_rec.rsc_attribute6
,x_winners_rec.rsc_attribute7
,x_winners_rec.rsc_attribute8
,x_winners_rec.rsc_attribute9
,x_winners_rec.rsc_attribute10
,x_winners_rec.rsc_attribute11
,x_winners_rec.rsc_attribute12
,x_winners_rec.rsc_attribute13
,x_winners_rec.rsc_attribute14
,x_winners_rec.rsc_attribute15
FROM
( 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 jtf_terr_results_GT_MT i ) 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_all jta
, jtf_terr_all jta_p
, jtf_terr_rsc_all jtr
, jtf_terr_rsc_access_all jtra
, jtf_qual_types_all jqta
, (select a.group_id resource_id, a.group_name resource_name, 'RS_GROUP' resource_type
from jtf_rs_groups_tl a
where a.language = userenv('LANG')
union all
select a.team_id resource_id, a.team_name resource_name, 'RS_TEAM' resource_type
from jtf_rs_teams_tl a
where a.language = userenv('LANG')
union all
select a.resource_id resource_id, a.resource_name resource_name,
decode(a.category ,'EMPLOYEE', 'RS_EMPLOYEE','PARTNER', 'RS_PARTNER','SUPPLIER_CONTACT',
'RS_SUPPLIER_CONTACT' , 'PARTY', 'RS_PARTY' , 'OTHER',
'RS_OTHER', 'TBH', 'RS_TBH') RESOURCE_TYPE
from jtf_rs_resource_extns_tl a
where a.language = userenv('LANG')
) rsv
, jtf_rs_resource_extns rsc
, jtf_rs_resource_extns mgr
, jtf_rs_roles_vl role
WHERE WT.terr_id = jta.terr_id
AND 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 jtra.trans_access_code NOT IN ('NONE', 'ESC_OWNER')
AND jtra.access_type = jqta.name
AND (( p_role IS NULL ) OR ( jtr.role = p_role ))
AND (( p_resource_type IS NULL) OR ( jtr.resource_type = p_resource_type ))
AND jqta.qual_type_id = p_trans_id
AND jtr.resource_id = rsv.resource_id
AND jtr.resource_type = rsv.resource_type
AND decode(jtr.resource_type, 'RS_EMPLOYEE', jtr.resource_id, -999) = rsc.resource_id(+)
AND rsc.source_mgr_id = mgr.source_id(+)
AND rsc.category = mgr.category(+)
AND jta.parent_territory_id = jta_p.terr_id
AND jtr.role = role.role_code(+);
DELETE FROM JTF_TERR_RESULTS_GT_L1;
DELETE FROM JTF_TERR_RESULTS_GT_L2;
DELETE FROM JTF_TERR_RESULTS_GT_L3;
DELETE FROM JTF_TERR_RESULTS_GT_L4;
DELETE FROM JTF_TERR_RESULTS_GT_L5;
DELETE FROM JTF_TERR_RESULTS_GT_WT;
SELECT COUNT(*)
INTO l_winner_no_of_records
FROM jtf_terr_results_gt_l1;
'Number of records inserted into jtf_terr_results_gt_l1 table : ' || l_winner_no_of_records);
SELECT COUNT(*)
INTO l_winner_no_of_records
FROM jtf_terr_results_gt_l2;
'Number of records inserted into jtf_terr_results_gt_l2 table : ' || l_winner_no_of_records);
SELECT COUNT(*)
INTO l_winner_no_of_records
FROM jtf_terr_results_gt_l3;
'Number of records inserted into jtf_terr_results_gt_l3 table : ' || l_winner_no_of_records);
SELECT COUNT(*)
INTO l_winner_no_of_records
FROM jtf_terr_results_gt_l4;
'Number of records inserted into jtf_terr_results_gt_l4 table : ' || l_winner_no_of_records);
SELECT COUNT(*)
INTO l_winner_no_of_records
FROM jtf_terr_results_gt_l5;
'Number of records inserted into jtf_terr_results_gt_l5 table : ' || l_winner_no_of_records);
SELECT COUNT(*)
INTO l_winner_no_of_records
FROM jtf_terr_results_gt_wt;
'Number of records inserted into jtf_terr_results_gt_wt table : ' || l_winner_no_of_records);
SELECT DISTINCT
WINNERS.trans_object_id
,WINNERS.trans_detail_object_id
,WINNERS.txn_date
,WINNERS.win_terr_id
,jta.org_id
,jtr.person_id
,jta.start_date_active
,jta.end_date_active
,jtr.terr_rsc_id
,jta.name
,null top_level_terr_id
,jta.absolute_rank absolute_rank
,jtr.resource_id
,jtr.start_date_active
,jtr.end_date_active
,jtr.resource_type
,jtr.group_id
,inv.role_id
,jtr.role
,decode(jtra.trans_access_code, 'FULL_ACCESS', 'Y', 'N')
,jtr.primary_contact_flag
,jta.attribute_category terr_attr_category
,jta.attribute1 terr_attribute1
,jta.attribute2 terr_attribute2
,jta.attribute3 terr_attribute3
,jta.attribute4 terr_attribute4
,jta.attribute5 terr_attribute5
,jta.attribute6 terr_attribute6
,jta.attribute7 terr_attribute7
,jta.attribute8 terr_attribute8
,jta.attribute9 terr_attribute9
,jta.attribute10 terr_attribute10
,jta.attribute11 terr_attribute11
,jta.attribute12 terr_attribute12
,jta.attribute13 terr_attribute13
,jta.attribute14 terr_attribute14
,jta.attribute15 terr_attribute15
,jtr.attribute_category rsc_attr_category
,jtr.attribute1 rsc_attribute1
,jtr.attribute2 rsc_attribute2
,jtr.attribute3 rsc_attribute3
,jtr.attribute4 rsc_attribute4
,jtr.attribute5 rsc_attribute5
,jtr.attribute6 rsc_attribute6
,jtr.attribute7 rsc_attribute7
,jtr.attribute8 rsc_attribute8
,jtr.attribute9 rsc_attribute9
,jtr.attribute10 rsc_attribute10
,jtr.attribute11 rsc_attribute11
,jtr.attribute12 rsc_attribute12
,jtr.attribute13 rsc_attribute13
,jtr.attribute14 rsc_attribute14
,jtr.attribute15 rsc_attribute15
BULK COLLECT INTO
x_winners_rec.trans_object_id
,x_winners_rec.trans_detail_object_id
,x_winners_rec.txn_date
,x_winners_rec.terr_id
,x_winners_rec.org_id
,x_winners_rec.person_id
,x_winners_rec.terr_start_date
,x_winners_rec.terr_end_date
,x_winners_rec.terr_rsc_id
,x_winners_rec.terr_name
,x_winners_rec.top_level_terr_id
,x_winners_rec.absolute_rank
,x_winners_rec.resource_id
,x_winners_rec.rsc_start_date
,x_winners_rec.rsc_end_date
,x_winners_rec.resource_type
,x_winners_rec.group_id
,x_winners_rec.role_id
,x_winners_rec.role
,x_winners_rec.full_access_flag
,x_winners_rec.primary_contact_flag
,x_winners_rec.terr_attr_category
,x_winners_rec.terr_attribute1
,x_winners_rec.terr_attribute2
,x_winners_rec.terr_attribute3
,x_winners_rec.terr_attribute4
,x_winners_rec.terr_attribute5
,x_winners_rec.terr_attribute6
,x_winners_rec.terr_attribute7
,x_winners_rec.terr_attribute8
,x_winners_rec.terr_attribute9
,x_winners_rec.terr_attribute10
,x_winners_rec.terr_attribute11
,x_winners_rec.terr_attribute12
,x_winners_rec.terr_attribute13
,x_winners_rec.terr_attribute14
,x_winners_rec.terr_attribute15
,x_winners_rec.rsc_attr_category
,x_winners_rec.rsc_attribute1
,x_winners_rec.rsc_attribute2
,x_winners_rec.rsc_attribute3
,x_winners_rec.rsc_attribute4
,x_winners_rec.rsc_attribute5
,x_winners_rec.rsc_attribute6
,x_winners_rec.rsc_attribute7
,x_winners_rec.rsc_attribute8
,x_winners_rec.rsc_attribute9
,x_winners_rec.rsc_attribute10
,x_winners_rec.rsc_attribute11
,x_winners_rec.rsc_attribute12
,x_winners_rec.rsc_attribute13
,x_winners_rec.rsc_attribute14
,x_winners_rec.rsc_attribute15
FROM
(
/* WINNERS ILV */
SELECT LX.trans_object_id
, LX.trans_detail_object_id
, LX.WIN_TERR_ID
, LX.txn_date
FROM jtf_terr_results_GT_L1 LX
, ( SELECT trans_object_id
, trans_detail_object_id
, WIN_TERR_ID WIN_TERR_ID
FROM JTF_terr_results_GT_L1
MINUS
SELECT trans_object_id
, trans_detail_object_id
, ul_terr_id WIN_TERR_ID
FROM JTF_terr_results_GT_L2 ) 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
, LX.txn_date
FROM jtf_terr_results_GT_L2 LX
, ( SELECT trans_object_id
, trans_detail_object_id
, WIN_TERR_ID WIN_TERR_ID
FROM JTF_terr_results_GT_L2
MINUS
SELECT trans_object_id
, trans_detail_object_id
, ul_terr_id WIN_TERR_ID
FROM JTF_terr_results_GT_L3 ) 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
, LX.txn_date
FROM jtf_terr_results_GT_L3 LX
, ( SELECT trans_object_id
, trans_detail_object_id
, WIN_TERR_ID WIN_TERR_ID
FROM JTF_terr_results_GT_L3
MINUS
SELECT trans_object_id
, trans_detail_object_id
, ul_terr_id WIN_TERR_ID
FROM JTF_terr_results_GT_L4 ) 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
, LX.txn_date
FROM jtf_terr_results_GT_L4 LX
, ( SELECT trans_object_id
, trans_detail_object_id
, WIN_TERR_ID WIN_TERR_ID
FROM JTF_terr_results_GT_L4
MINUS
SELECT trans_object_id
, trans_detail_object_id
, ul_terr_id WIN_TERR_ID
FROM JTF_terr_results_GT_L5 ) 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
, LX.txn_date
FROM jtf_terr_results_GT_L5 LX
, ( SELECT trans_object_id
, trans_detail_object_id
, WIN_TERR_ID WIN_TERR_ID
FROM JTF_terr_results_GT_L5
MINUS
SELECT trans_object_id
, trans_detail_object_id
, ul_terr_id WIN_TERR_ID
FROM JTF_terr_results_GT_WT ) 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
, txn_date
FROM jtf_terr_results_GT_wt
) WINNERS
, jtf_terr_all jta
, jtf_terr_rsc_all jtr
, jtf_terr_rsc_access_all jtra
, jtf_qual_types_all jqta
, jtf_rs_roles_b inv
WHERE WINNERS.win_terr_id = jta.terr_id
AND WINNERS.win_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 jtra.trans_access_code <> 'NONE'
AND jtra.access_type = jqta.name
AND (( p_role IS NULL ) OR ( jtr.role = p_role ))
AND jtr.role = inv.role_code(+)
AND (( p_resource_type IS NULL) OR ( jtr.resource_type = p_resource_type ))
AND jqta.qual_type_id = p_trans_id;
SELECT DISTINCT
WINNERS.trans_object_id
,WINNERS.trans_detail_object_id
,WINNERS.txn_date
,WINNERS.win_terr_id
,jta.org_id
,jta.start_date_active
,jta.end_date_active
,jta.name
,null top_level_terr_id
,jta.absolute_rank absolute_rank
,jta.attribute_category terr_attr_category
,jta.attribute1 terr_attribute1
,jta.attribute2 terr_attribute2
,jta.attribute3 terr_attribute3
,jta.attribute4 terr_attribute4
,jta.attribute5 terr_attribute5
,jta.attribute6 terr_attribute6
,jta.attribute7 terr_attribute7
,jta.attribute8 terr_attribute8
,jta.attribute9 terr_attribute9
,jta.attribute10 terr_attribute10
,jta.attribute11 terr_attribute11
,jta.attribute12 terr_attribute12
,jta.attribute13 terr_attribute13
,jta.attribute14 terr_attribute14
,jta.attribute15 terr_attribute15
BULK COLLECT INTO
x_winners_rec.trans_object_id
,x_winners_rec.trans_detail_object_id
,x_winners_rec.txn_date
,x_winners_rec.terr_id
,x_winners_rec.org_id
,x_winners_rec.terr_start_date
,x_winners_rec.terr_end_date
,x_winners_rec.terr_name
,x_winners_rec.top_level_terr_id
,x_winners_rec.absolute_rank
,x_winners_rec.terr_attr_category
,x_winners_rec.terr_attribute1
,x_winners_rec.terr_attribute2
,x_winners_rec.terr_attribute3
,x_winners_rec.terr_attribute4
,x_winners_rec.terr_attribute5
,x_winners_rec.terr_attribute6
,x_winners_rec.terr_attribute7
,x_winners_rec.terr_attribute8
,x_winners_rec.terr_attribute9
,x_winners_rec.terr_attribute10
,x_winners_rec.terr_attribute11
,x_winners_rec.terr_attribute12
,x_winners_rec.terr_attribute13
,x_winners_rec.terr_attribute14
,x_winners_rec.terr_attribute15
FROM
(
/* WINNERS ILV */
SELECT LX.trans_object_id
, LX.trans_detail_object_id
, LX.WIN_TERR_ID
, LX.txn_date
FROM jtf_terr_results_GT_L1 LX
, ( SELECT trans_object_id
, trans_detail_object_id
, WIN_TERR_ID WIN_TERR_ID
FROM JTF_terr_results_GT_L1
MINUS
SELECT trans_object_id
, trans_detail_object_id
, ul_terr_id WIN_TERR_ID
FROM JTF_terr_results_GT_L2 ) 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
, LX.txn_date
FROM jtf_terr_results_GT_L2 LX
, ( SELECT trans_object_id
, trans_detail_object_id
, WIN_TERR_ID WIN_TERR_ID
FROM JTF_terr_results_GT_L2
MINUS
SELECT trans_object_id
, trans_detail_object_id
, ul_terr_id WIN_TERR_ID
FROM JTF_terr_results_GT_L3 ) 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
, LX.txn_date
FROM jtf_terr_results_GT_L3 LX
, ( SELECT trans_object_id
, trans_detail_object_id
, WIN_TERR_ID WIN_TERR_ID
FROM JTF_terr_results_GT_L3
MINUS
SELECT trans_object_id
, trans_detail_object_id
, ul_terr_id WIN_TERR_ID
FROM JTF_terr_results_GT_L4 ) 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
, LX.txn_date
FROM jtf_terr_results_GT_L4 LX
, ( SELECT trans_object_id
, trans_detail_object_id
, WIN_TERR_ID WIN_TERR_ID
FROM JTF_terr_results_GT_L4
MINUS
SELECT trans_object_id
, trans_detail_object_id
, ul_terr_id WIN_TERR_ID
FROM JTF_terr_results_GT_L5 ) 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
, LX.txn_date
FROM jtf_terr_results_GT_L5 LX
, ( SELECT trans_object_id
, trans_detail_object_id
, WIN_TERR_ID WIN_TERR_ID
FROM JTF_terr_results_GT_L5
MINUS
SELECT trans_object_id
, trans_detail_object_id
, ul_terr_id WIN_TERR_ID
FROM JTF_terr_results_GT_WT ) 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
, txn_date
FROM jtf_terr_results_GT_wt
) WINNERS
, jtf_terr_all jta
WHERE WINNERS.win_terr_id = jta.terr_id
AND EXISTS (
SELECT 1
FROM
jtf_terr_rsc_all jtr
, jtf_terr_rsc_access_all jtra
, jtf_qual_types_all jqta
WHERE WINNERS.win_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 jtra.trans_access_code <> 'NONE'
AND jtra.access_type = jqta.name
AND (( p_role IS NULL ) OR ( jtr.role = p_role ))
AND (( p_resource_type IS NULL) OR ( jtr.resource_type = p_resource_type ))
AND jqta.qual_type_id = p_trans_id);
SELECT /*+ PUSH_PRED(rsv) */ DISTINCT
WINNERS.trans_object_id
,WINNERS.trans_detail_object_id
,WINNERS.txn_date
,WINNERS.win_terr_id
,jta.org_id
,jtr.person_id
,jta.start_date_active
,jta.end_date_active
,jtr.terr_rsc_id
,jta.name
,null top_level_terr_id
,jta.absolute_rank absolute_rank
,jtr.resource_id
,jtr.start_date_active
,jtr.end_date_active
,jtr.resource_type
,jtr.group_id
,role.role_id
,jtr.role
,decode(jtra.trans_access_code, 'FULL_ACCESS', 'Y', 'N')
,jtr.primary_contact_flag
,rsv.resource_name
,rsc.source_job_title resource_job_title
,rsc.source_phone resource_phone
,rsc.source_email resource_email
,rsc.source_mgr_name resource_mgr_name
,mgr.source_phone resource_mgr_phone
,mgr.source_email resource_mgr_email
,jta.name property1
,role.role_name property2
,jta_p.name property3
,rsc.attribute4 property4
,rsc.attribute5 property5
,rsc.attribute6 property6
,rsc.attribute7 property7
,rsc.attribute8 property8
,rsc.attribute9 property9
,rsc.attribute10 property10
,rsc.attribute11 property11
,rsc.attribute12 property12
,rsc.attribute13 property13
,rsc.attribute14 property14
,rsc.attribute15 property15
,jta.attribute_category terr_attr_category
,jta.attribute1 terr_attribute1
,jta.attribute2 terr_attribute2
,jta.attribute3 terr_attribute3
,jta.attribute4 terr_attribute4
,jta.attribute5 terr_attribute5
,jta.attribute6 terr_attribute6
,jta.attribute7 terr_attribute7
,jta.attribute8 terr_attribute8
,jta.attribute9 terr_attribute9
,jta.attribute10 terr_attribute10
,jta.attribute11 terr_attribute11
,jta.attribute12 terr_attribute12
,jta.attribute13 terr_attribute13
,jta.attribute14 terr_attribute14
,jta.attribute15 terr_attribute15
,jtr.attribute_category rsc_attr_category
,jtr.attribute1 rsc_attribute1
,jtr.attribute2 rsc_attribute2
,jtr.attribute3 rsc_attribute3
,jtr.attribute4 rsc_attribute4
,jtr.attribute5 rsc_attribute5
,jtr.attribute6 rsc_attribute6
,jtr.attribute7 rsc_attribute7
,jtr.attribute8 rsc_attribute8
,jtr.attribute9 rsc_attribute9
,jtr.attribute10 rsc_attribute10
,jtr.attribute11 rsc_attribute11
,jtr.attribute12 rsc_attribute12
,jtr.attribute13 rsc_attribute13
,jtr.attribute14 rsc_attribute14
,jtr.attribute15 rsc_attribute15
BULK COLLECT INTO
x_winners_rec.trans_object_id
,x_winners_rec.trans_detail_object_id
,x_winners_rec.txn_date
,x_winners_rec.terr_id
,x_winners_rec.org_id
,x_winners_rec.person_id
,x_winners_rec.terr_start_date
,x_winners_rec.terr_end_date
,x_winners_rec.terr_rsc_id
,x_winners_rec.terr_name
,x_winners_rec.top_level_terr_id
,x_winners_rec.absolute_rank
,x_winners_rec.resource_id
,x_winners_rec.rsc_start_date
,x_winners_rec.rsc_end_date
,x_winners_rec.resource_type
,x_winners_rec.group_id
,x_winners_rec.role_id
,x_winners_rec.role
,x_winners_rec.full_access_flag
,x_winners_rec.primary_contact_flag
,x_winners_rec.resource_name
,x_winners_rec.resource_job_title
,x_winners_rec.resource_phone
,x_winners_rec.resource_email
,x_winners_rec.resource_mgr_name
,x_winners_rec.resource_mgr_phone
,x_winners_rec.resource_mgr_email
,x_winners_rec.property1
,x_winners_rec.property2
,x_winners_rec.property3
,x_winners_rec.property4
,x_winners_rec.property5
,x_winners_rec.property6
,x_winners_rec.property7
,x_winners_rec.property8
,x_winners_rec.property9
,x_winners_rec.property10
,x_winners_rec.property11
,x_winners_rec.property12
,x_winners_rec.property13
,x_winners_rec.property14
,x_winners_rec.property15
,x_winners_rec.terr_attr_category
,x_winners_rec.terr_attribute1
,x_winners_rec.terr_attribute2
,x_winners_rec.terr_attribute3
,x_winners_rec.terr_attribute4
,x_winners_rec.terr_attribute5
,x_winners_rec.terr_attribute6
,x_winners_rec.terr_attribute7
,x_winners_rec.terr_attribute8
,x_winners_rec.terr_attribute9
,x_winners_rec.terr_attribute10
,x_winners_rec.terr_attribute11
,x_winners_rec.terr_attribute12
,x_winners_rec.terr_attribute13
,x_winners_rec.terr_attribute14
,x_winners_rec.terr_attribute15
,x_winners_rec.rsc_attr_category
,x_winners_rec.rsc_attribute1
,x_winners_rec.rsc_attribute2
,x_winners_rec.rsc_attribute3
,x_winners_rec.rsc_attribute4
,x_winners_rec.rsc_attribute5
,x_winners_rec.rsc_attribute6
,x_winners_rec.rsc_attribute7
,x_winners_rec.rsc_attribute8
,x_winners_rec.rsc_attribute9
,x_winners_rec.rsc_attribute10
,x_winners_rec.rsc_attribute11
,x_winners_rec.rsc_attribute12
,x_winners_rec.rsc_attribute13
,x_winners_rec.rsc_attribute14
,x_winners_rec.rsc_attribute15
FROM
(
/* WINNERS ILV */
SELECT LX.trans_object_id
, LX.trans_detail_object_id
, LX.WIN_TERR_ID
, LX.txn_date
FROM jtf_terr_results_GT_L1 LX
, ( SELECT trans_object_id
, trans_detail_object_id
, WIN_TERR_ID WIN_TERR_ID
FROM JTF_terr_results_GT_L1
MINUS
SELECT trans_object_id
, trans_detail_object_id
, ul_terr_id WIN_TERR_ID
FROM JTF_terr_results_GT_L2 ) 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
, LX.txn_date
FROM jtf_terr_results_GT_L2 LX
, ( SELECT trans_object_id
, trans_detail_object_id
, WIN_TERR_ID WIN_TERR_ID
FROM JTF_terr_results_GT_L2
MINUS
SELECT trans_object_id
, trans_detail_object_id
, ul_terr_id WIN_TERR_ID
FROM JTF_terr_results_GT_L3 ) 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
, LX.txn_date
FROM jtf_terr_results_GT_L3 LX
, ( SELECT trans_object_id
, trans_detail_object_id
, WIN_TERR_ID WIN_TERR_ID
FROM JTF_terr_results_GT_L3
MINUS
SELECT trans_object_id
, trans_detail_object_id
, ul_terr_id WIN_TERR_ID
FROM JTF_terr_results_GT_L4 ) 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
, LX.txn_date
FROM jtf_terr_results_GT_L4 LX
, ( SELECT trans_object_id
, trans_detail_object_id
, WIN_TERR_ID WIN_TERR_ID
FROM JTF_terr_results_GT_L4
MINUS
SELECT trans_object_id
, trans_detail_object_id
, ul_terr_id WIN_TERR_ID
FROM JTF_terr_results_GT_L5 ) 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
, LX.txn_date
FROM jtf_terr_results_GT_L5 LX
, ( SELECT trans_object_id
, trans_detail_object_id
, WIN_TERR_ID WIN_TERR_ID
FROM JTF_terr_results_GT_L5
MINUS
SELECT trans_object_id
, trans_detail_object_id
, ul_terr_id WIN_TERR_ID
FROM JTF_terr_results_GT_WT ) 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
, txn_date
FROM jtf_terr_results_GT_wt
) WINNERS
, jtf_terr_all jta
, jtf_terr_all jta_p
, jtf_terr_rsc_all jtr
, jtf_terr_rsc_access_all jtra
, jtf_qual_types_all jqta
, (select a.group_id resource_id, a.group_name resource_name, 'RS_GROUP' resource_type
from jtf_rs_groups_tl a
where a.language = userenv('LANG')
union all
select a.team_id resource_id, a.team_name resource_name, 'RS_TEAM' resource_type
from jtf_rs_teams_tl a
where a.language = userenv('LANG')
union all
select a.resource_id resource_id, a.resource_name resource_name,
decode(a.category ,'EMPLOYEE', 'RS_EMPLOYEE','PARTNER', 'RS_PARTNER','SUPPLIER_CONTACT',
'RS_SUPPLIER_CONTACT' , 'PARTY', 'RS_PARTY' , 'OTHER',
'RS_OTHER', 'TBH', 'RS_TBH') RESOURCE_TYPE
from jtf_rs_resource_extns_tl a
where a.language = userenv('LANG')
) rsv
, jtf_rs_resource_extns rsc
, jtf_rs_resource_extns mgr
, jtf_rs_roles_vl role
WHERE WINNERS.win_terr_id = jta.terr_id
AND WINNERS.win_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 jtra.trans_access_code <> 'NONE'
AND jtra.access_type = jqta.name
AND (( p_role IS NULL ) OR ( jtr.role = p_role ))
AND (( p_resource_type IS NULL) OR ( jtr.resource_type = p_resource_type ))
AND jqta.qual_type_id = p_trans_id
AND jtr.resource_id = rsv.resource_id
AND jtr.resource_type = rsv.resource_type
AND decode(jtr.resource_type, 'RS_EMPLOYEE', jtr.resource_id, -999) = rsc.resource_id(+)
AND rsc.source_mgr_id = mgr.source_id(+)
AND rsc.category = mgr.category(+)
AND jta.parent_territory_id = jta_p.terr_id
AND jtr.role = role.role_code(+);
SELECT tup.multi_level_winning_flag
INTO 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;
l_delete_stmt VARCHAR2(100);
l_insert_stmt VARCHAR2(32767);
SELECT tup.real_time_trans_table_name
INTO l_real_time_trans_table_name
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 stmt is used instead of truncate as # of rows will be small */
l_delete_stmt := 'DELETE FROM ' || l_real_time_trans_table_name;
EXECUTE IMMEDIATE l_delete_stmt;
/* Code to insert the transaction objects into TRANS table */
IF (p_param_passing_mechanism = 'PBR') THEN -- if parameter passing mechanism is pass by reference
BEGIN
SELECT tups.real_time_insert
INTO l_insert_stmt
FROM jty_trans_usg_pgm_sql tups
WHERE tups.source_id = p_source_id
AND tups.trans_type_id = p_trans_id
AND tups.program_name = p_program_name
AND tups.enabled_flag = 'Y';
IF (l_insert_stmt IS NULL) THEN
RAISE NO_TXN_SQL_ERROR;
substr(l_insert_stmt, 1, 4000));
/* Dynammic pl/sql block to bind the PK values and execute the insert statement */
l_plsql_block :=
'declare ' ||
'l_trans_object_id1 number; ' ||
l_insert_stmt ||
'exception ' ||
'when others then raise; ' ||
l_debug_stmt := 'SELECT COUNT(*) FROM ' || l_real_time_trans_table_name;
'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.get_winners.insert_trans_data_pbr',
'Number of records inserted into TRANS table : ' || l_trans_no_of_records);
INSERT INTO jty_terr_nvp_trans_gt (
attribute_name
,char_value
,num_value
,date_value )
VALUES (
p_name_value_pair.attribute_name(i)
,p_name_value_pair.char_value(i)
,p_name_value_pair.num_value(i)
,p_name_value_pair.date_value(i));
SELECT COUNT(*)
INTO l_trans_no_of_records
FROM jty_terr_nvp_trans_gt;
'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.get_winners.insert_trans_data_pbv',
'Number of records inserted into TRANS table : ' || l_trans_no_of_records);