[Home] [Help]
51: -- 04/05/01 ARPATEL Added processing for copy hierarchy in proc 'Copy Territory'
52: -- 04/12/01 jdochert Added PROCEDURE chk_num_copy_terr
53: -- 04/20/01 ARPATEL Added Concurrent_Copy_Territory procedure for concurrent requests
54: -- 04/28/01 ARPATEL Added FUNCTION conc_req_copy_terr returning concurrent request ID.
55: -- 05/22/01 ARPATEL Added CNR_GROUP_ID to cursors selecting from JTF_TERR_VALUES.
56: -- 07/13/01 ARPATEL Added validation for territory templates without 'DYNAMIC QUALIFIERS'
57: -- 09/06/01 ARPATEL Added VALUE1_ID, VALUE2_ID AND VALUE3_ID to JTF_TERR_VALUES cursors
58: -- and procedure create_new_terr_value_rec
59: -- 09/18/01 ARPATEL Removed debug message level checks to enable form to pick up these messages
53: -- 04/20/01 ARPATEL Added Concurrent_Copy_Territory procedure for concurrent requests
54: -- 04/28/01 ARPATEL Added FUNCTION conc_req_copy_terr returning concurrent request ID.
55: -- 05/22/01 ARPATEL Added CNR_GROUP_ID to cursors selecting from JTF_TERR_VALUES.
56: -- 07/13/01 ARPATEL Added validation for territory templates without 'DYNAMIC QUALIFIERS'
57: -- 09/06/01 ARPATEL Added VALUE1_ID, VALUE2_ID AND VALUE3_ID to JTF_TERR_VALUES cursors
58: -- and procedure create_new_terr_value_rec
59: -- 09/18/01 ARPATEL Removed debug message level checks to enable form to pick up these messages
60: -- 12/03/04 achanda Added value4_id : bug # 3726007
61: --
590: -- Get all the Territories in the Hierarchy
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;
2751: l_terr_value_id := NULL;
2752: END IF;
2753:
2754: --
2755: --dbms_output('Create_Terr_Value PVT: Before Calling JTF_TERR_VALUES_PKG.Insert_Row');
2756: JTF_TERR_VALUES_PKG.Insert_Row(x_Rowid => l_rowid,
2757: x_TERR_VALUE_ID => l_terr_value_id,
2758: x_LAST_UPDATED_BY => P_Terr_Value_Rec.LAST_UPDATED_BY,
2759: x_LAST_UPDATE_DATE => P_Terr_Value_Rec.LAST_UPDATE_DATE,
2752: END IF;
2753:
2754: --
2755: --dbms_output('Create_Terr_Value PVT: Before Calling JTF_TERR_VALUES_PKG.Insert_Row');
2756: JTF_TERR_VALUES_PKG.Insert_Row(x_Rowid => l_rowid,
2757: x_TERR_VALUE_ID => l_terr_value_id,
2758: x_LAST_UPDATED_BY => P_Terr_Value_Rec.LAST_UPDATED_BY,
2759: x_LAST_UPDATE_DATE => P_Terr_Value_Rec.LAST_UPDATE_DATE,
2760: x_CREATED_BY => P_Terr_Value_Rec.CREATED_BY,
4189: ID_USED_FLAG,
4190: LOW_VALUE_CHAR_ID,
4191: ORG_ID,
4192: CNR_GROUP_ID
4193: From JTF_TERR_VALUES_ALL
4194: Where TERR_VALUE_ID = l_TERR_VALUE_ID
4195: For Update NOWAIT;
4196:
4197: --Local variable declaration
4271: --dbms_output('Update_Terr_Value REC: NO-RCORDS-FOUND');
4272: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
4273: THEN
4274: FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MISSING_UPDT_TARGET');
4275: FND_MESSAGE.Set_Token('TABLE_NAME', 'JTF_TERR_VALUES');
4276: FND_MESSAGE.Set_Token('PK_ID', TO_CHAR(P_Terr_Value_Rec.TERR_VALUE_ID));
4277: FND_MSG_PUB.Add;
4278: END IF;
4279: raise FND_API.G_EXC_ERROR;
4282: CLOSE C_GetTerritoryValue;
4283: --
4284: -- Add check for concurrency
4285: --
4286: --dbms_output('Update_Terr_Value REC: Before Calling JTF_TERR_VALUES_PKG.Update_Row');
4287: --call the table handler
4288: JTF_TERR_VALUES_PKG.Update_Row(x_Rowid => l_rowid,
4289: x_TERR_VALUE_ID => P_Terr_Value_Rec.TERR_VALUE_ID,
4290: x_LAST_UPDATED_BY => P_Terr_Value_Rec.LAST_UPDATED_BY,
4284: -- Add check for concurrency
4285: --
4286: --dbms_output('Update_Terr_Value REC: Before Calling JTF_TERR_VALUES_PKG.Update_Row');
4287: --call the table handler
4288: JTF_TERR_VALUES_PKG.Update_Row(x_Rowid => l_rowid,
4289: x_TERR_VALUE_ID => P_Terr_Value_Rec.TERR_VALUE_ID,
4290: x_LAST_UPDATED_BY => P_Terr_Value_Rec.LAST_UPDATED_BY,
4291: x_LAST_UPDATE_DATE => P_Terr_Value_Rec.LAST_UPDATE_DATE,
4292: x_CREATED_BY => P_Terr_Value_Rec.created_by,
4968: NULL;
4969: END;
4970:
4971: /* Do a bulk delete of Territory Qualifier VALUES */
4972: DELETE jtf_terr_values_ALL
4973: WHERE terr_qual_id = p_terr_qual_id
4974: RETURNING terr_value_id
4975: BULK COLLECT INTO deletedIds;
4976:
5112:
5113: --Initialize the return status to success
5114: x_return_status := FND_API.G_RET_STS_SUCCESS;
5115: --
5116: --dbms_output('Delete_Terr_Value: Before Calling JTF_TERR_VALUES_PKG.Delete_Row');
5117:
5118: JTF_TERR_VALUES_PKG.Delete_Row(X_TERR_VALUE_ID => P_Terr_Value_Id);
5119: --
5120: --Prepare message name
5114: x_return_status := FND_API.G_RET_STS_SUCCESS;
5115: --
5116: --dbms_output('Delete_Terr_Value: Before Calling JTF_TERR_VALUES_PKG.Delete_Row');
5117:
5118: JTF_TERR_VALUES_PKG.Delete_Row(X_TERR_VALUE_ID => P_Terr_Value_Id);
5119: --
5120: --Prepare message name
5121: FND_MESSAGE.SET_NAME('JTF','JTF_TERR_RECORD_DELETED');
5122: IF SQL%FOUND THEN
5125: l_row_count := SQL%ROWCOUNT;
5126: END IF;
5127: --Prepare message token
5128: FND_MESSAGE.SET_TOKEN('NO_OF_REC', TO_CHAR(l_row_count));
5129: FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'JTF_TERR_VALUES');
5130: FND_MSG_PUB.ADD();
5131:
5132: -- Debug Message
5133: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
5157: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5158: l_row_count := 0;
5159: --Prepare message token
5160: FND_MESSAGE.SET_TOKEN('NO_OF_REC', TO_CHAR(l_row_count));
5161: FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'JTF_TERR_VALUES');
5162: --Add message to API message list
5163: FND_MSG_PUB.ADD();
5164: --
5165: FND_MSG_PUB.Count_And_Get
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'
7258: , j1.VALUE1_ID
7259: , j1.VALUE2_ID
7260: , j1.VALUE3_ID
7261: , j1.VALUE4_ID
7262: FROM jtf_terr_values j1
7263: WHERE j1.terr_qual_id = p_terr_qual_id;
7264:
7265: /* local record variables */
7266: l_tmpl_terr_qual_rec Terr_Qual_Rec_Type;
7412: , j1.VALUE1_ID
7413: , j1.VALUE2_ID
7414: , j1.VALUE3_ID
7415: , j1.VALUE4_ID
7416: FROM jtf_terr_values j1
7417: WHERE j1.terr_qual_id = p_terr_qual_id;
7418:
7419: /* local record variables */
7420: l_tmpl_terr_qual_rec Terr_Qual_Rec_Type;
7558: , j1.VALUE1_ID
7559: , j1.VALUE2_ID
7560: , j1.VALUE3_ID
7561: , j1.VALUE4_ID
7562: FROM jtf_terr_values j1
7563: WHERE j1.terr_qual_id = p_terr_qual_id
7564: ORDER BY j1.value_set;
7565:
7566: l_tmpl_terr_qual_rec Terr_Qual_Rec_Type;
8280: -- checked only under a single parent.
8281: ---------------------------------------------------------------------
8282: FUNCTION Overlap_Exists(p_Parent_Terr_Id IN NUMBER,
8283: p_Qual_Usg_Id IN NUMBER,
8284: p_terr_value_record IN jtf_terr_values%ROWTYPE )
8285: RETURN VARCHAR2
8286: AS
8287: dummy NUMBER := 0;
8288: l_found NUMBER := 0;
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
9151: );
9152: END validate_parent;
9153:
9154:
9155: /* Function used in JTF_TERR_VALUES_DESC_V to return
9156: ** descriptive values for ids and lookup_codes
9157: */
9158: FUNCTION get_terr_value_desc (
9159: p_convert_to_id_flag VARCHAR2