[Home] [Help]
22: --
23: -- HISTORY
24: -- 07/29/99 VNEDUNGA Created
25: -- 12/22/99 NEDUNGA Making changes to confirm to
26: -- JTF_TERR_RSC_ALL table change
27: -- 01/06/00 VNEDUNGA Fixing problem with the build rule
28: -- expression
29: -- 01/16/00 VNEDUNGA Commenting out dbms_output
30: -- 01/17/00 VNEDUNGA Cahnging the the hard code value for
96:
97: --check if duplicate resource_id, group, role exists for this territory
98: cursor c_res is
99: Select start_date_active, nvl(end_date_active,to_date('31/12/4712','DD/MM/RRRR')) end_date_active
100: from JTF_TERR_RSC_ALL
101: where TERR_ID = P_TerrRsc_Rec.Terr_Id
102: --resource with same role and group assigned to this territory
103: and RESOURCE_ID = P_TerrRsc_Rec.Resource_Id
104: and ( (RESOURCE_TYPE is null and P_TerrRsc_Rec.Resource_TYPE is null) or (RESOURCE_TYPE = P_TerrRsc_Rec.Resource_TYPE))
163: l_Terr_Id NUMBER;
164:
165: cursor c_res is
166: Select start_date_active, nvl(end_date_active,to_date('31/12/4712','DD/MM/RRRR')) end_date_active
167: from JTF_TERR_RSC_ALL
168: where TERR_ID = P_TerrRsc_Rec.Terr_Id
169: --resource with same role and group assigned to this territory
170: and RESOURCE_ID = P_TerrRsc_Rec.Resource_Id
171: and ( (RESOURCE_TYPE is null and P_TerrRsc_Rec.Resource_TYPE is null) or (RESOURCE_TYPE = P_TerrRsc_Rec.Resource_TYPE))
228: l_Terr_Id NUMBER;
229:
230: cursor c_res is
231: Select start_date_active, nvl(end_date_active,to_date('31/12/4712','DD/MM/RRRR')) end_date_active
232: from JTF_TERR_RSC_ALL
233: where TERR_ID = P_TerrRsc_Rec.Terr_Id
234: --resource with same role and group assigned to this territory
235: and RESOURCE_ID = P_TerrRsc_Rec.Resource_Id
236: and ( (RESOURCE_TYPE is null and P_TerrRsc_Rec.Resource_TYPE is null) or (RESOURCE_TYPE = P_TerrRsc_Rec.Resource_TYPE))
302: For l_Res_Counter IN p_TerrRsc_Tbl.first .. p_TerrRsc_Tbl.count LOOP
303:
304: --check if duplicate resource_id, group, role exists for this territory
305: Select 'X' into l_temp
306: from JTF_TERR_RSC_ALL
307: where TERR_ID = p_TerrRsc_Tbl(l_res_counter).Terr_Id
308: and RESOURCE_ID = p_TerrRsc_Tbl(l_res_counter).Resource_Id
309: and RESOURCE_TYPE = p_TerrRsc_Tbl(l_res_counter).Resource_TYPE
310: and GROUP_ID = p_TerrRsc_Tbl(l_res_counter).GROUP_ID
366: For l_Res_Counter IN p_TerrRsc_Tbl.first .. p_TerrRsc_Tbl.count LOOP
367:
368: --check if duplicate resource_id, group, role exists for this territory
369: Select 'X' into l_temp
370: from JTF_TERR_RSC_ALL
371: where TERR_ID = p_TerrRsc_Tbl(l_res_counter).Terr_Id
372: and RESOURCE_ID = p_TerrRsc_Tbl(l_res_counter).Resource_Id
373: and RESOURCE_TYPE = p_TerrRsc_Tbl(l_res_counter).Resource_TYPE
374: and GROUP_ID = p_TerrRsc_Tbl(l_res_counter).GROUP_ID
414: -- ***************************************************
415: -- API name : Create_TerrResource
416: -- Type : PUBLIC
417: -- Function : To create Territory Resources - which will insert
418: -- records into jtf_terr_rsc_access_all, jtf_terr_rsc_all
419: -- tables.
420: --
421: -- Pre-reqs :
422: -- Parameters:
680: -- ***************************************************
681: -- API name : Create_TerrResource
682: -- Type : PUBLIC
683: -- Function : To create Territory Resources - which will insert
684: -- records into jtf_terr_rsc_access_all, jtf_terr_rsc_all
685: -- tables.
686: --
687: -- Pre-reqs :
688: -- Parameters:
1125: -- ***************************************************
1126: -- API name : Update_TerrResource
1127: -- Type : PUBLIC
1128: -- Function : To Update Territory Resources - which will update
1129: -- records into jtf_terr_rsc_access_all, jtf_terr_rsc_all
1130: -- tables.
1131: --
1132: -- Pre-reqs :
1133: -- Parameters:
2484:
2485: Cursor C_GetTerrResource(l_TerrRsc_id Number) IS
2486: Select Rowid, TERR_RSC_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
2487: LAST_UPDATE_LOGIN, TERR_ID, RESOURCE_ID, RESOURCE_TYPE, ROLE, PRIMARY_CONTACT_FLAG, ORG_ID
2488: From jtf_terr_rsc_ALL
2489: Where TERR_RSC_ID = l_TerrRsc_id
2490: FOR Update NOWAIT;
2491:
2492: --Local variable declaration
4524: start_date_active,
4525: end_date_active,
4526: full_access_flag,
4527: org_id
4528: FROM jtf_terr_rsc_ALL
4529: WHERE terr_id = lp_terr_id;
4530:
4531:
4532: CURSOR csr_rsc_access_all (lp_terr_rsc_id NUMBER) IS
4731:
4732:
4733:
4734: /* procedure to check that UK constraint is not
4735: ** being violated on JTF_TERR_RSC_ALL table
4736: ** -- jdochert 09/19
4737: */
4738: PROCEDURE validate_terr_rsc_access_UK(
4739: p_Terr_Rsc_Id IN NUMBER,
4867: l_terr_ids_tbl Terr_Ids_Tbl_Type;
4868:
4869: CURSOR csr_terr_rsc(l_resource_id NUMBER, l_resource_type VARCHAR) IS
4870: select j.terr_id
4871: from jtf_terr_rsc_ALL j, jtf_terr_ALL jt
4872: where j.resource_id = l_resource_id
4873: and j.resource_type = l_resource_type
4874: and j.terr_id = jt.terr_id
4875: and jt.template_flag = 'N'
4881: CURSOR csr_unassigned_terrs IS
4882: select terr_id
4883: from JTF_TERR_ALL jt
4884: where NOT EXISTS (select jtr.terr_id
4885: from jtf_terr_rsc_ALL jtr
4886: where jt.terr_id = jtr.terr_id
4887: )
4888: and jt.template_flag = 'N'
4889: and jt.escalation_territory_flag = 'N'
4947:
4948: IF p_add_flag = 'Y'
4949: THEN
4950: FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
4951: INSERT INTO JTF_TERR_RSC_ALL(
4952: TERR_RSC_ID,
4953: LAST_UPDATE_DATE,
4954: LAST_UPDATED_BY,
4955: CREATION_DATE,
4990: IF p_delete_flag = 'Y'
4991: THEN
4992:
4993: FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
4994: INSERT INTO JTF_TERR_RSC_ALL(
4995: TERR_RSC_ID,
4996: LAST_UPDATE_DATE,
4997: LAST_UPDATED_BY,
4998: CREATION_DATE,
5051: raa.access_type,
5052: p_dest_resource_rec.ORG_ID
5053: FROM
5054: JTF_TERR_RSC_ACCESS_ALL raa
5055: ,JTF_TERR_RSC_ALL tra -- use old record to find access_type
5056: ,JTF_TERR_RSC_ALL ntra -- pick up new records from above
5057: WHERE
5058: tra.terr_rsc_id = NVL(raa.terr_rsc_id, tra.terr_rsc_id)
5059: AND tra.resource_id = p_source_resource_rec.resource_id
5052: p_dest_resource_rec.ORG_ID
5053: FROM
5054: JTF_TERR_RSC_ACCESS_ALL raa
5055: ,JTF_TERR_RSC_ALL tra -- use old record to find access_type
5056: ,JTF_TERR_RSC_ALL ntra -- pick up new records from above
5057: WHERE
5058: tra.terr_rsc_id = NVL(raa.terr_rsc_id, tra.terr_rsc_id)
5059: AND tra.resource_id = p_source_resource_rec.resource_id
5060: AND ntra.terr_id = tra.terr_id
5064:
5065: --Do all the deleting of old records at the end
5066:
5067: FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
5068: DELETE from jtf_terr_rsc_ALL
5069: where terr_id = l_terr_ids_tbl(i)
5070: and resource_id = p_source_resource_rec.resource_id;
5071:
5072:
5076:
5077: ELSE
5078:
5079: FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
5080: INSERT INTO JTF_TERR_RSC_ALL(
5081: TERR_RSC_ID,
5082: LAST_UPDATE_DATE,
5083: LAST_UPDATED_BY,
5084: CREATION_DATE,
5137: raa.access_type,
5138: p_dest_resource_rec.ORG_ID
5139: FROM
5140: JTF_TERR_RSC_ACCESS_ALL raa
5141: ,JTF_TERR_RSC_ALL tra -- use old record to find access_type
5142: ,JTF_TERR_RSC_ALL ntra -- pick up new records from above
5143: WHERE
5144: tra.terr_rsc_id = NVL(raa.terr_rsc_id, tra.terr_rsc_id)
5145: AND tra.resource_id = p_source_resource_rec.resource_id
5138: p_dest_resource_rec.ORG_ID
5139: FROM
5140: JTF_TERR_RSC_ACCESS_ALL raa
5141: ,JTF_TERR_RSC_ALL tra -- use old record to find access_type
5142: ,JTF_TERR_RSC_ALL ntra -- pick up new records from above
5143: WHERE
5144: tra.terr_rsc_id = NVL(raa.terr_rsc_id, tra.terr_rsc_id)
5145: AND tra.resource_id = p_source_resource_rec.resource_id
5146: AND ntra.terr_id = tra.terr_id
5149: ;
5150:
5151: --UPDATE old rsc to soft delete - end date
5152: FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
5153: UPDATE jtf_terr_rsc_all j
5154: SET j.end_date_active = SYSDATE
5155: WHERE j.resource_id = p_source_resource_rec.RESOURCE_ID
5156: AND j.resource_type = p_source_resource_rec.RESOURCE_TYPE
5157: AND j.terr_id = l_terr_ids_tbl(i);