[Home] [Help]
21: --
22: -- HISTORY
23: -- 07/29/99 VNEDUNGA Created
24: -- 12/22/99 NEDUNGA Making changes to confirm to
25: -- JTF_TERR_RSC_ALL table change
26: -- 01/06/00 VNEDUNGA Fixing problem with the build rule
27: -- expression
28: -- 01/16/00 VNEDUNGA Commenting out dbms_output
29: -- 01/17/00 VNEDUNGA Cahnging the the hard code value for
97:
98: --check if duplicate resource_id, group, role exists for this territory
99: cursor c_res (p_terr_id NUMBER)is
100: Select JTR2.start_date_active, nvl(JTR2.end_date_active,to_date('31/12/4712','DD/MM/RRRR')) end_date_active
101: from JTF_TERR_RSC_ALL JTR1, JTF_TERR_RSC_ALL JTR2
102: where JTR2.TERR_ID = p_Terr_Id
103: AND JTR1.TERR_RSC_ID = P_TerrRsc_Rec.Terr_Rsc_Id
104: --resource with same role and group assigned to this territory
105: AND JTR2.RESOURCE_ID = decode(P_TerrRsc_Rec.Resource_Id, FND_API.G_MISS_NUM, JTR1.RESOURCE_ID, P_TerrRsc_Rec.Resource_Id)
116: --Get the missing values from the database to check the duplicate resource.
117: BEGIN
118: SELECT terr_id, start_date_active, nvl(end_date_active,to_date('31/12/4712','DD/MM/RRRR')) end_date_active
119: INTO l_terr_id,l_start_date_active, l_end_date_active
120: FROM JTF_TERR_RSC_ALL
121: WHERE TERR_RSC_ID = P_TerrRsc_Rec.Terr_Rsc_Id;
122:
123: IF ( P_TerrRsc_Rec.START_DATE_ACTIVE IS NOT NULL AND P_TerrRsc_Rec.START_DATE_ACTIVE <> FND_API.G_MISS_DATE ) THEN
124: l_start_date_active := P_TerrRsc_Rec.START_DATE_ACTIVE;
204: l_Terr_Id NUMBER;
205:
206: cursor c_res is
207: Select start_date_active, nvl(end_date_active,to_date('31/12/4712','DD/MM/RRRR')) end_date_active
208: from JTF_TERR_RSC_ALL
209: where TERR_ID = P_TerrRsc_Rec.Terr_Id
210: --resource with same role and group assigned to this territory
211: AND RESOURCE_ID = P_TerrRsc_Rec.Resource_Id
212: AND ( (RESOURCE_TYPE IS NULL and ( ( P_TerrRsc_Rec.Resource_TYPE IS NULL ) OR (P_TerrRsc_Rec.Resource_TYPE = FND_API.G_MISS_CHAR) ) )
343: -- ***************************************************
344: -- API name : Create_TerrResource
345: -- Type : PUBLIC
346: -- Function : To create Territory Resources - which will insert
347: -- records into jtf_terr_rsc_access_all, jtf_terr_rsc_all
348: -- tables.
349: --
350: -- Pre-reqs :
351: -- Parameters:
517: -- ***************************************************
518: -- API name : Create_TerrResource
519: -- Type : PUBLIC
520: -- Function : To create Territory Resources - which will insert
521: -- records into jtf_terr_rsc_access_all, jtf_terr_rsc_all
522: -- tables.
523: --
524: -- Pre-reqs :
525: -- Parameters:
1058: -- ***************************************************
1059: -- API name : Update_TerrResource
1060: -- Type : PUBLIC
1061: -- Function : To Update Territory Resources - which will update
1062: -- records into jtf_terr_rsc_access_all, jtf_terr_rsc_all
1063: -- tables.
1064: --
1065: -- Pre-reqs :
1066: -- Parameters:
1344: BEGIN
1345: SELECT JSA.RSC_LOV_SQL
1346: INTO l_rsc_lov_sql
1347: FROM JTF_TERR_ALL JTA,
1348: JTF_TERR_RSC_ALL JTR,
1349: JTF_TERR_USGS_ALL JTU,
1350: JTF_SOURCES_ALL JSA
1351: WHERE JTR.terr_rsc_id = P_TerrRsc_Rec.Terr_Rsc_Id
1352: AND JTR.TERR_ID = JTA.Terr_Id
1363: --Get the missing values from the database to Valiadte the resource.
1364: BEGIN
1365: SELECT resource_id, group_id, role, resource_type
1366: INTO l_resource_id, l_group_id, l_role, l_resource_type
1367: FROM JTF_TERR_RSC_ALL
1368: WHERE TERR_RSC_ID = P_TerrRsc_Rec.Terr_Rsc_Id;
1369: EXCEPTION
1370: WHEN NO_DATA_FOUND THEN
1371: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2428: --Get the missing values from the database to check with the territory dates.
2429: BEGIN
2430: SELECT terr_id, start_date_active, nvl(end_date_active,to_date('31/12/4712','DD/MM/RRRR')) end_date_active
2431: INTO l_terr_id, l_res_start_date_active, l_res_end_date_active
2432: FROM JTF_TERR_RSC_ALL
2433: WHERE TERR_RSC_ID = P_TerrRsc_Rec.Terr_Rsc_Id;
2434:
2435: IF ( P_TerrRsc_Rec.START_DATE_ACTIVE IS NOT NULL AND P_TerrRsc_Rec.START_DATE_ACTIVE <> FND_API.G_MISS_DATE ) THEN
2436: l_res_start_date_active := P_TerrRsc_Rec.START_DATE_ACTIVE;
2614:
2615: Cursor C_GetTerrResource(l_TerrRsc_id Number) IS
2616: Select Rowid, TERR_RSC_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
2617: LAST_UPDATE_LOGIN, TERR_ID, RESOURCE_ID, RESOURCE_TYPE, ROLE, PRIMARY_CONTACT_FLAG, ORG_ID
2618: From jtf_terr_rsc_ALL
2619: Where TERR_RSC_ID = l_TerrRsc_id
2620: FOR Update NOWAIT;
2621:
2622: --Local variable declaration
3726: -- Validate the territory Id
3727: l_Validate_id := p_TerrRsc_Id;
3728: If l_Validate_id IS NOT NULL Then
3729: -- --dbms_output.put_line('Validate_Terr_Qtype_Usage: TERR_ID(' || to_char(l_Validate_id) || ')');
3730: If JTF_CTM_UTILITY_PVT.fk_id_is_valid(l_Validate_id, 'TERR_RSC_ID', 'JTF_TERR_RSC_ALL') <> FND_API.G_TRUE Then
3731: --dbms_output.put_line('Validate_Foreign_Key: l_status <> FND_API.G_TRUE');
3732: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3733: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_INVALID_FOREIGN_KEY');
3734: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_RSC');
4379: start_date_active,
4380: end_date_active,
4381: full_access_flag,
4382: org_id
4383: FROM jtf_terr_rsc_ALL
4384: WHERE terr_id = lp_terr_id;
4385:
4386:
4387: CURSOR csr_rsc_access_all (lp_terr_rsc_id NUMBER) IS
4586:
4587:
4588:
4589: /* procedure to check that UK constraint is not
4590: ** being violated on JTF_TERR_RSC_ALL table
4591: ** -- jdochert 09/19
4592: */
4593: PROCEDURE validate_terr_rsc_access_UK(
4594: p_Terr_Rsc_Id IN NUMBER,
4722: l_terr_ids_tbl Terr_Ids_Tbl_Type;
4723:
4724: CURSOR csr_terr_rsc(l_resource_id NUMBER, l_resource_type VARCHAR) IS
4725: select j.terr_id
4726: from jtf_terr_rsc_ALL j, jtf_terr_ALL jt
4727: where j.resource_id = l_resource_id
4728: and j.resource_type = l_resource_type
4729: and j.terr_id = jt.terr_id
4730: and jt.template_flag = 'N'
4736: CURSOR csr_unassigned_terrs IS
4737: select terr_id
4738: from JTF_TERR_ALL jt
4739: where NOT EXISTS (select jtr.terr_id
4740: from jtf_terr_rsc_ALL jtr
4741: where jt.terr_id = jtr.terr_id
4742: )
4743: and jt.template_flag = 'N'
4744: and jt.escalation_territory_flag = 'N'
4802:
4803: IF p_add_flag = 'Y'
4804: THEN
4805: FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
4806: INSERT INTO JTF_TERR_RSC_ALL(
4807: TERR_RSC_ID,
4808: LAST_UPDATE_DATE,
4809: LAST_UPDATED_BY,
4810: CREATION_DATE,
4845: IF p_delete_flag = 'Y'
4846: THEN
4847:
4848: FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
4849: INSERT INTO JTF_TERR_RSC_ALL(
4850: TERR_RSC_ID,
4851: LAST_UPDATE_DATE,
4852: LAST_UPDATED_BY,
4853: CREATION_DATE,
4906: raa.access_type,
4907: p_dest_resource_rec.ORG_ID
4908: FROM
4909: JTF_TERR_RSC_ACCESS_ALL raa
4910: ,JTF_TERR_RSC_ALL tra -- use old record to find access_type
4911: ,JTF_TERR_RSC_ALL ntra -- pick up new records from above
4912: WHERE
4913: tra.terr_rsc_id = NVL(raa.terr_rsc_id, tra.terr_rsc_id)
4914: AND tra.resource_id = p_source_resource_rec.resource_id
4907: p_dest_resource_rec.ORG_ID
4908: FROM
4909: JTF_TERR_RSC_ACCESS_ALL raa
4910: ,JTF_TERR_RSC_ALL tra -- use old record to find access_type
4911: ,JTF_TERR_RSC_ALL ntra -- pick up new records from above
4912: WHERE
4913: tra.terr_rsc_id = NVL(raa.terr_rsc_id, tra.terr_rsc_id)
4914: AND tra.resource_id = p_source_resource_rec.resource_id
4915: AND ntra.terr_id = tra.terr_id
4919:
4920: --Do all the deleting of old records at the end
4921:
4922: FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
4923: DELETE from jtf_terr_rsc_ALL
4924: where terr_id = l_terr_ids_tbl(i)
4925: and resource_id = p_source_resource_rec.resource_id;
4926:
4927:
4931:
4932: ELSE
4933:
4934: FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
4935: INSERT INTO JTF_TERR_RSC_ALL(
4936: TERR_RSC_ID,
4937: LAST_UPDATE_DATE,
4938: LAST_UPDATED_BY,
4939: CREATION_DATE,
4992: raa.access_type,
4993: p_dest_resource_rec.ORG_ID
4994: FROM
4995: JTF_TERR_RSC_ACCESS_ALL raa
4996: ,JTF_TERR_RSC_ALL tra -- use old record to find access_type
4997: ,JTF_TERR_RSC_ALL ntra -- pick up new records from above
4998: WHERE
4999: tra.terr_rsc_id = NVL(raa.terr_rsc_id, tra.terr_rsc_id)
5000: AND tra.resource_id = p_source_resource_rec.resource_id
4993: p_dest_resource_rec.ORG_ID
4994: FROM
4995: JTF_TERR_RSC_ACCESS_ALL raa
4996: ,JTF_TERR_RSC_ALL tra -- use old record to find access_type
4997: ,JTF_TERR_RSC_ALL ntra -- pick up new records from above
4998: WHERE
4999: tra.terr_rsc_id = NVL(raa.terr_rsc_id, tra.terr_rsc_id)
5000: AND tra.resource_id = p_source_resource_rec.resource_id
5001: AND ntra.terr_id = tra.terr_id
5004: ;
5005:
5006: --UPDATE old rsc to soft delete - end date
5007: FORALL i IN l_terr_ids_tbl.FIRST..l_terr_ids_tbl.LAST
5008: UPDATE jtf_terr_rsc_all j
5009: SET j.end_date_active = SYSDATE
5010: WHERE j.resource_id = p_source_resource_rec.RESOURCE_ID
5011: AND j.resource_type = p_source_resource_rec.RESOURCE_TYPE
5012: AND j.terr_id = l_terr_ids_tbl(i);