[Home] [Help]
591: FOR c in c_Terr LOOP
592: --
593: --Delete Territory Values
594: DELETE from JTF_TERR_VALUES_ALL WHERE TERR_QUAL_ID IN
595: ( SELECT TERR_QUAL_ID FROM JTF_TERR_QUAL_ALL WHERE TERR_ID = c.Terr_Id );
596:
597: --Delete Territory Qualifer records
598: DELETE from JTF_TERR_QUAL_ALL WHERE TERR_ID = c.Terr_Id;
599:
594: DELETE from JTF_TERR_VALUES_ALL WHERE TERR_QUAL_ID IN
595: ( SELECT TERR_QUAL_ID FROM JTF_TERR_QUAL_ALL WHERE TERR_ID = c.Terr_Id );
596:
597: --Delete Territory Qualifer records
598: DELETE from JTF_TERR_QUAL_ALL WHERE TERR_ID = c.Terr_Id;
599:
600: --Delete Territory qual type usgs
601: DELETE from JTF_TERR_QTYPE_USGS_ALL WHERE TERR_ID = c.Terr_Id;
602:
1377: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1378: RAISE FND_API.G_EXC_ERROR;
1379: END IF;
1380: --
1381: -- Call api to insert records into jtf_terr_qualtype_usgs
1382: --
1383: --dbms_output('Create_Territory_Header PVT: Before Calling Create_Terr_QualType_Usage');
1384:
1385: Create_Terr_QualType_Usage(P_Api_Version_Number => P_Api_Version_Number,
2262:
2263: UPDATE jtf_terr_ALL jt
2264: SET jt.num_qual = (
2265: SELECT COUNT(jtq.qual_usg_id)
2266: FROM jtf_terr_qual_ALL jtq, jtf_qual_usgs_ALL jqu, jtf_qual_type_usgs_ALL jqtu
2267: WHERE jtq.terr_id = jt.terr_id
2268: AND jtq.qual_usg_id = jqu.qual_usg_id
2269: AND jqu.qual_type_usg_id = jqtu.qual_type_usg_id
2270: AND jqtu.qual_type_id = p_qual_type_id
2392: l_terr_qual_id := NULL;
2393: END IF;
2394:
2395: -- Call insert terr_Qual_Type_Usgs table handler
2396: JTF_TERR_QUAL_PKG.Insert_Row(x_Rowid => l_rowid,
2397: x_TERR_QUAL_ID => l_terr_qual_id,
2398: x_LAST_UPDATE_DATE => P_Terr_Qual_Rec.LAST_UPDATE_DATE,
2399: x_LAST_UPDATED_BY => P_Terr_Qual_Rec.LAST_UPDATED_BY,
2400: x_CREATION_DATE => P_Terr_Qual_Rec.CREATION_DATE,
3895: QUAL_USG_ID,
3896: USE_TO_NAME_FLAG,
3897: GENERATE_FLAG,
3898: OVERLAP_ALLOWED_FLAG
3899: From JTF_TERR_QUAL_ALL
3900: Where terr_qual_id = l_terr_qual_id
3901: FOR Update NOWAIT;
3902: --Local variable declaration
3903: l_api_name CONSTANT VARCHAR2(30) := 'Update_Terr_Qualifier';
3963: --dbms_output('Update_Terr_Qualifier REC: NO-RCORDS-FOUND');
3964: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3965: THEN
3966: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
3967: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_QUAL');
3968: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(P_Terr_Qual_Rec.terr_qual_id));
3969: FND_MSG_PUB.Add;
3970: END IF;
3971: raise FND_API.G_EXC_ERROR;
3971: raise FND_API.G_EXC_ERROR;
3972: End if;
3973: CLOSE C_GetTerrQualifier;
3974:
3975: --dbms_output('Update_Terr_Qualifier REC: Before Calling JTF_TERR_QUAL_PKG.Update_Row');
3976: JTF_TERR_QUAL_PKG.Update_Row(x_Rowid => l_rowid,
3977: x_TERR_QUAL_ID => P_Terr_Qual_Rec.terr_qual_id,
3978: x_LAST_UPDATE_DATE => P_Terr_Qual_Rec.LAST_UPDATE_DATE,
3979: x_LAST_UPDATED_BY => P_Terr_Qual_Rec.LAST_UPDATED_BY,
3972: End if;
3973: CLOSE C_GetTerrQualifier;
3974:
3975: --dbms_output('Update_Terr_Qualifier REC: Before Calling JTF_TERR_QUAL_PKG.Update_Row');
3976: JTF_TERR_QUAL_PKG.Update_Row(x_Rowid => l_rowid,
3977: x_TERR_QUAL_ID => P_Terr_Qual_Rec.terr_qual_id,
3978: x_LAST_UPDATE_DATE => P_Terr_Qual_Rec.LAST_UPDATE_DATE,
3979: x_LAST_UPDATED_BY => P_Terr_Qual_Rec.LAST_UPDATED_BY,
3980: x_CREATION_DATE => P_Terr_Qual_Rec.creation_date,
4958: /* anonymous block to get territory id */
4959: BEGIN
4960: SELECT terr_id
4961: INTO lp_terr_id
4962: FROM jtf_terr_qual_ALL jtq
4963: WHERE jtq.terr_qual_id = p_terr_qual_id;
4964: EXCEPTION
4965: WHEN NO_DATA_FOUND THEN
4966: NULL;
4973: WHERE terr_qual_id = p_terr_qual_id
4974: RETURNING terr_value_id
4975: BULK COLLECT INTO deletedIds;
4976:
4977: JTF_TERR_QUAL_PKG.Delete_Row(x_terr_qual_id => P_Terr_Qual_Id );
4978: --
4979: --Prepare message name
4980: FND_MESSAGE.SET_NAME('JTF','JTF_TERR_RECORD_DELETED');
4981: IF SQL%FOUND THEN
4989: update_terr_num_qual(lp_terr_id, -1002);
4990:
4991: --Prepare message token
4992: FND_MESSAGE.SET_TOKEN('NO_OF_REC', TO_CHAR(l_row_count));
4993: FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'JTF_TERR_QUALIFIERS');
4994: FND_MSG_PUB.ADD();
4995:
4996: -- Debug Message
4997: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
5020: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5021: l_row_count := 0;
5022: --Prepare message token
5023: FND_MESSAGE.SET_TOKEN('NO_OF_REC', TO_CHAR(l_row_count));
5024: FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'JTF_TERR_QUALIFIERS');
5025: --Add message to API message list
5026: FND_MSG_PUB.ADD();
5027: --
5028: FND_MSG_PUB.Count_And_Get
6007: AS
6008: Cursor C_QualDef1 IS
6009: -- Get the qualifier usage related information
6010: select jqu.qual_col1_datatype, jqu.display_type, jqu.convert_to_id_flag
6011: from jtf_qual_usgs_ALL jqu, jtf_terr_qual_ALL jtq
6012: where jqu.qual_usg_id = jtq.qual_Usg_Id and
6013: jqu.org_id = jtq.org_id AND
6014: jtq.terr_qual_id = p_Terr_Qual_Id;
6015:
6347:
6348: /* (2) check that at least 1 dynamic is specified for use in generation */
6349: CURSOR c_chk_qual_mode (p_template_terr_id NUMBER) IS
6350: SELECT COUNT(*)
6351: FROM jtf_terr_qual j
6352: WHERE j.terr_id = p_template_terr_id
6353: AND j.qualifier_mode = 'DYNAMIC';
6354:
6355: l_terr_id NUMBER := 0;
6513:
6514: /* cursor to get the number of territories that will be generated */
6515: CURSOR c_get_max_value_set (p_template_terr_id NUMBER) IS
6516: SELECT MAX (j1.VALUE_SET)
6517: FROM jtf_terr_values j1, jtf_terr_qual j2, jtf_terr j3
6518: WHERE j1.terr_qual_id = j2.terr_qual_id
6519: AND j2.terr_id = j3.terr_id
6520: AND j3.terr_id = p_template_terr_id
6521: AND j2.qualifier_mode = 'DYNAMIC'
7226: , j1.GENERATE_FLAG
7227: , j1.OVERLAP_ALLOWED_FLAG
7228: , j1.QUALIFIER_MODE
7229: , j1.ORG_ID
7230: FROM jtf_terr_qual j1
7231: WHERE j1.terr_id = p_template_terr_id;
7232:
7233: /* cursor to get values for STATIC qualifiers' values */
7234: CURSOR c_get_terr_value (p_terr_qual_id NUMBER) IS
7379: , j1.GENERATE_FLAG
7380: , j1.OVERLAP_ALLOWED_FLAG
7381: , j1.QUALIFIER_MODE
7382: , j1.ORG_ID
7383: FROM jtf_terr_qual j1
7384: WHERE j1.terr_id = p_template_terr_id
7385: AND j1.qualifier_mode = 'STATIC';
7386:
7387: /* cursor to get values for STATIC qualifiers' values */
7525: , j1.GENERATE_FLAG
7526: , j1.OVERLAP_ALLOWED_FLAG
7527: , j1.QUALIFIER_MODE
7528: , j1.ORG_ID
7529: FROM jtf_terr_qual j1
7530: WHERE j1.terr_id = p_template_terr_id
7531: AND j1.qualifier_mode = 'DYNAMIC';
7532:
7533: /* cursor to get values for dynamic qualifier values */
7910: l_tmpl_terr_qual_rec.overlap_allowed_flag := l_dyn_qual_tbl(i).OVERLAP_ALLOWED_FLAG;
7911: l_tmpl_terr_qual_rec.qualifier_mode := l_dyn_qual_tbl(i).QUALIFIER_MODE;
7912: l_tmpl_terr_qual_rec.org_id := l_dyn_qual_tbl(i).ORG_ID;
7913:
7914: /* insert record IN JTF_TERR_QUAL_ALL */
7915: create_new_terr_qual_rec(l_new_terr_id, l_tmpl_terr_qual_rec, l_new_terr_qual_id);
7916:
7917: l_rec_index := l_dyn_qual_tbl(i).start_record + l_dyn_qual_tbl(i).current_record;
7918:
8305: Then
8306: If p_terr_value_record.low_value_char is NOT NULL Then
8307: Select count(*)
8308: into dummy
8309: from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
8310: Where jt.parent_territory_id = p_Parent_Terr_Id
8311: and jtq.terr_id = jt.terr_id
8312: and jtq.qual_usg_id = p_Qual_Usg_Id
8313: and jtv.terr_qual_id = jtq.terr_qual_id
8332: -- Check the high_value_char
8333: If p_terr_value_record.high_value_char is NOT NULL Then
8334: Select count(*)
8335: into dummy
8336: from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
8337: Where jt.parent_territory_id = p_Parent_Terr_Id
8338: and jtq.terr_id = jt.terr_id
8339: and jtq.qual_usg_id = p_Qual_Usg_Id
8340: and jtv.terr_qual_id = jtq.terr_qual_id
8363: -- only check for a subset of the availabel operator as these are the only
8364: -- available one for this combination
8365: Select count(*)
8366: into dummy
8367: from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
8368: Where jt.parent_territory_id = p_Parent_Terr_Id
8369: and jtq.terr_id = jt.terr_id
8370: and jtq.qual_usg_id = p_Qual_Usg_Id
8371: and jtv.terr_qual_id = jtq.terr_qual_id
8381:
8382: If p_terr_value_record.low_value_number is NOT NULL Then
8383: Select 1
8384: into dummy
8385: from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
8386: Where jt.parent_territory_id = p_Parent_Terr_Id
8387: and jtq.terr_id = jt.terr_id
8388: and jtq.qual_usg_id = p_Qual_Usg_Id
8389: and jtv.terr_qual_id = jtq.terr_qual_id
8403:
8404: If p_terr_value_record.high_value_number is NOT NULL Then
8405: Select 1
8406: into dummy
8407: from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
8408: Where jt.parent_territory_id = p_Parent_Terr_Id
8409: and jtq.terr_id = jt.terr_id
8410: and jtq.qual_usg_id = p_Qual_Usg_Id
8411: and jtv.terr_qual_id = jtq.terr_qual_id
8431:
8432: If p_terr_value_record.low_value_number is NOT NULL Then
8433: Select count(*)
8434: into dummy
8435: from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
8436: Where jt.parent_territory_id = p_Parent_Terr_Id
8437: and jtq.terr_id = jt.terr_id
8438: and jtq.qual_usg_id = p_Qual_Usg_Id
8439: and jtv.terr_qual_id = jtq.terr_qual_id
8453:
8454: If p_terr_value_record.high_value_number is NOT NULL Then
8455: Select count(*)
8456: into dummy
8457: from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
8458: Where jt.parent_territory_id = p_Parent_Terr_Id
8459: and jtq.terr_id = jt.terr_id
8460: and jtq.qual_usg_id = p_Qual_Usg_Id
8461: and jtv.terr_qual_id = jtq.terr_qual_id
8479: l_convert_to_id_flag is NULL ) )
8480: Then
8481: Select count(*)
8482: into dummy
8483: from jtf_terr_values jtv, jtf_terr_qual jtq, jtf_terr jt
8484: Where jt.parent_territory_id = p_Parent_Terr_Id
8485: and jtq.terr_id = jt.terr_id
8486: and jtq.qual_usg_id = p_Qual_Usg_Id
8487: and jtv.terr_qual_id = jtq.terr_qual_id