[Home] [Help]
PACKAGE BODY: APPS.JTF_TERR_TASK_PUB
Source
1 Package Body JTF_TERR_TASK_PUB AS
2 /* $Header: jtfpttsb.pls 120.3 2005/11/18 15:07:17 achanda ship $ */
3 -- ---------------------------------------------------
4 -- Start of Comments
5 -- ---------------------------------------------------
6 -- PACKAGE NAME: JTF_TERR_TASK_PUB
7 -- ---------------------------------------------------
8 -- PURPOSE
9 -- Joint task force core Sales territory manager public api's.
10 -- This package is a public API for getting winning territories
11 -- or territory resources.
12 --
13 -- Procedures:
14 -- (see below for specification)
15 --
16 -- NOTES
17 -- This package is publicly available for use
18 --
19 -- HISTORY
20 -- 09/14/99 VNEDUNGA Created
21 -- 12/09/99 VNEDUNGA Making changes to get_WinningTerritories
22 -- procedure
23 -- 01/07/99 VNEDUNGA Changing the procedure to reflect
24 -- qualifer chnages
25 -- 02/01/00 VNEDUNGA Changing the get resource SQL
26 -- 02/08/00 VNEDUNGA Fixing bug 1184799, local rec declaration
27 -- typo
28 -- 02/24/00 vnedunga Making chnages to call the newly designed
29 -- Generated Engine packages
30 -- 02/24/00 vnedunga Adding the code to rerturn Catch all
31 -- if there was no qualifying Ter
32 -- 03/23/00 vnedunga Making changes to return full_access_flag
33 -- 05/01/00 vnedunga Taking out FOR UPDATE clause from Resource
34 -- cursor
35 -- 06/14/00 vnedunga Changeing the get winning Terr memeber api
36 -- to return group_id
37 -- 05/07/01 EIHSU GetWinningTerritories removed
38 --
39 -- 05/24/05 ACHANDA Modified to the new 12.0 architecture
40 --
41 -- End of Comments
42 --
43 -- ***************************************************
44 -- GLOBAL VARIABLES
45 -- ***************************************************
46 G_PKG_NAME CONSTANT VARCHAR2(30):='JTF_TERR_TASK_PUB';
47 G_FILE_NAME CONSTANT VARCHAR2(12):='jtfpttsb.pls';
48
49 --
50 -- ***************************************************
51 -- start of comments
52 -- ***************************************************
53 -- api name : Get_WinningTerrMembers
54 -- type : public.
55 -- function : Get winning territories members for an ACCOUNT
56 -- pre-reqs : Territories needs to be setup first
57 -- parameters :
58 --
59 -- IN:
60 -- p_api_version_number IN number required
61 -- p_init_msg_list IN varchar2 optional --default = fnd_api.g_false
62 -- p_commit IN varchar2 optional --default = fnd_api.g_false
63 -- p_Org_Id IN number required
64 -- p_TerrTask_Rec IN JTF_ServiceReqst_rec_type
65 --
66 -- out:
67 -- x_return_status out varchar2(1)
68 -- x_msg_count out number
69 -- x_msg_data out varchar2(2000)
70 -- x_TerrRes_tbl out TerrRes_tbl_type
71 --
72 -- requirements :
73 -- business rules :
74
75 -- version : current version 1.0
76 -- initial version: initial version 1.0
77 --
78 -- notes: Public API for retreving a set of winning
79 -- territories resources. This is an overloaded
80 -- procedure for accounts,lead, oppor, service
81 -- requests, and collections.
82 --
83 -- end of comments
84 procedure Get_WinningTerrMembers
85 ( p_api_version_number IN number,
86 p_init_msg_list IN varchar2 := fnd_api.g_false,
87 p_TerrTask_Rec IN JTF_TERRITORY_PUB.JTF_Task_rec_type,
88 p_Resource_Type IN varchar2,
89 p_Role IN varchar2,
90 x_return_status OUT NOCOPY varchar2,
91 x_msg_count OUT NOCOPY number,
92 X_msg_data OUT NOCOPY varchar2,
93 x_TerrResource_tbl OUT NOCOPY JTF_TERRITORY_PUB.WinningTerrMember_tbl_type
94 )
95 AS
96
97 l_api_name CONSTANT VARCHAR2(30) := 'Get_WinningTerrMembers';
98 l_api_version_number CONSTANT NUMBER := 1.0;
99
100 l_Counter NUMBER;
101
102 lx_winners_rec JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type;
103
104 BEGIN
105
106 -- debug message
107 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
108 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
109 'jtf.plsql.jtf_terr_task_pub.get_winningterrmembers.begin',
110 'Start of the procedure jtf_terr_task_pub.get_winningterrmembers');
111 END IF;
112
113 -- Standard call to check for call compatibility.
114 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
115 p_api_version_number,
116 l_api_name,
117 G_PKG_NAME)
118 THEN
119 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
120 END IF;
121
122 -- Initialize message list if p_init_msg_list is set to TRUE.
123 IF FND_API.to_Boolean( p_init_msg_list )
124 THEN
125 FND_MSG_PUB.initialize;
126 END IF;
127
128 ------------------
129 -- API body
130 ------------------
131 x_return_status := FND_API.G_RET_STS_SUCCESS;
132
133 -- debug message
134 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
135 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
136 'jtf.plsql.jtf_terr_task_pub.get_winningterrmembers.parameters',
137 'Country : ' || p_TerrTask_rec.COUNTRY || ' City : ' || p_TerrTask_rec.CITY || ' Postal Code : ' ||
138 p_TerrTask_rec.POSTAL_CODE || ' State : ' || p_TerrTask_rec.STATE || ' Area Code : ' || p_TerrTask_rec.AREA_CODE ||
139 ' County : ' || p_TerrTask_rec.COUNTY || ' Company Name Range : ' || p_TerrTask_rec.COMP_NAME_RANGE ||
140 ' Province : ' || p_TerrTask_rec.PROVINCE || ' Number of Employees : ' || p_TerrTask_rec.NUM_OF_EMPLOYEES ||
141 ' Party ID : ' || p_TerrTask_rec.PARTY_ID || ' Party Site ID : ' || p_TerrTask_rec.PARTY_SITE_ID || ' Task Type ID : ' ||
142 p_TerrTask_rec.TASK_TYPE_ID || ' Task Status ID : ' || p_TerrTask_rec.TASK_STATUS_ID || ' Task Priority ID : ' ||
143 p_TerrTask_rec.TASK_PRIORITY_ID);
144 END IF;
145
146 /* insert all the attributes into the trans table as name - value pair */
147 DELETE jty_terr_nvp_trans_gt;
148 INSERT INTO jty_terr_nvp_trans_gt (
149 attribute_name
150 ,num_value
151 ,char_value
152 ,date_value )
153 ( SELECT 'COUNTRY' attribute_name
154 ,null num_value
155 ,p_TerrTask_rec.COUNTRY char_value
156 ,null date_value
157 FROM DUAL
158 UNION ALL
159 SELECT 'CITY' attribute_name
160 ,null num_value
161 ,p_TerrTask_rec.CITY char_value
162 ,null date_value
163 FROM DUAL
164 UNION ALL
165 SELECT 'POSTAL_CODE' attribute_name
166 ,null num_value
167 ,p_TerrTask_rec.POSTAL_CODE char_value
168 ,null date_value
169 FROM DUAL
170 UNION ALL
171 SELECT 'STATE' attribute_name
172 ,null num_value
173 ,p_TerrTask_rec.STATE char_value
174 ,null date_value
175 FROM DUAL
176 UNION ALL
177 SELECT 'AREA_CODE' attribute_name
178 ,null num_value
179 ,p_TerrTask_rec.AREA_CODE char_value
180 ,null date_value
181 FROM DUAL
182 UNION ALL
183 SELECT 'COUNTY' attribute_name
184 ,null num_value
185 ,p_TerrTask_rec.COUNTY char_value
186 ,null date_value
187 FROM DUAL
188 UNION ALL
189 SELECT 'COMP_NAME_RANGE' attribute_name
190 ,null num_value
191 ,p_TerrTask_rec.COMP_NAME_RANGE char_value
192 ,null date_value
193 FROM DUAL
194 UNION ALL
195 SELECT 'PROVINCE' attribute_name
196 ,null num_value
197 ,p_TerrTask_rec.PROVINCE char_value
198 ,null date_value
199 FROM DUAL
200 UNION ALL
201 SELECT 'PARTY_ID' attribute_name
202 ,p_TerrTask_rec.PARTY_ID num_value
203 ,null char_value
204 ,null date_value
205 FROM DUAL
206 UNION ALL
207 SELECT 'PARTY_SITE_ID' attribute_name
208 ,p_TerrTask_rec.PARTY_SITE_ID num_value
209 ,null char_value
210 ,null date_value
211 FROM DUAL
212 UNION ALL
213 SELECT 'NUM_OF_EMPLOYEES' attribute_name
214 ,p_TerrTask_rec.NUM_OF_EMPLOYEES num_value
215 ,null char_value
216 ,null date_value
217 FROM DUAL
218 UNION ALL
219 SELECT 'TASK_TYPE_ID' attribute_name
220 ,p_TerrTask_rec.TASK_TYPE_ID num_value
221 ,null char_value
222 ,null date_value
223 FROM DUAL
224 UNION ALL
225 SELECT 'TASK_STATUS_ID' attribute_name
226 ,p_TerrTask_rec.TASK_STATUS_ID num_value
227 ,null char_value
228 ,null date_value
229 FROM DUAL
230 UNION ALL
231 SELECT 'TASK_PRIORITY_ID' attribute_name
232 ,p_TerrTask_rec.TASK_PRIORITY_ID num_value
233 ,null char_value
234 ,null date_value
235 FROM DUAL
236 UNION ALL
237 SELECT 'TASK_ID' attribute_name
238 ,p_TerrTask_rec.TASK_ID num_value
239 ,null char_value
240 ,null date_value
241 FROM DUAL
242 UNION ALL
243 SELECT 'ORGANIZATION_ID' attribute_name
244 ,p_TerrTask_rec.ORGANIZATION_ID num_value
245 ,null char_value
246 ,null date_value
247 FROM DUAL
248 );
249
250 /*
251 lp_Rec.squal_char01 := jtf_terr_char_360list(p_TerrTask_rec.COUNTRY);
252 lp_Rec.squal_char02 := jtf_terr_char_360list(p_TerrTask_rec.CITY);
253 lp_Rec.squal_char03 := jtf_terr_char_360list(p_TerrTask_rec.POSTAL_CODE);
254 lp_Rec.squal_char04 := jtf_terr_char_360list(p_TerrTask_rec.STATE);
255 lp_Rec.squal_char05 := jtf_terr_char_360list(p_TerrTask_rec.AREA_CODE);
256 lp_Rec.squal_char06 := jtf_terr_char_360list(p_TerrTask_rec.COUNTY);
257 lp_Rec.squal_char07 := jtf_terr_char_360list(p_TerrTask_rec.COMP_NAME_RANGE);
258 lp_Rec.squal_char08 := jtf_terr_char_360list(p_TerrTask_rec.PROVINCE);
259
260 lp_Rec.squal_num01 := jtf_terr_number_list(p_TerrTask_rec.PARTY_ID);
261 lp_Rec.squal_num02 := jtf_terr_number_list(p_TerrTask_rec.PARTY_SITE_ID);
262 lp_Rec.squal_num03 := jtf_terr_number_list(p_TerrTask_rec.NUM_OF_EMPLOYEES);
263 lp_Rec.squal_num20 := jtf_terr_number_list(p_TerrTask_rec.TASK_TYPE_ID);
264 lp_Rec.squal_num21 := jtf_terr_number_list(p_TerrTask_rec.TASK_STATUS_ID);
265 lp_Rec.squal_num22 := jtf_terr_number_list(p_TerrTask_rec.TASK_PRIORITY_ID);
266 */
267
268 JTY_ASSIGN_REALTIME_PUB.process_match (
269 p_source_id => -1002
270 ,p_trans_id => -1006
271 ,p_mode => 'REAL TIME:RESOURCE'
272 ,p_program_name => 'SERVICE/TASKS PROGRAM'
273 ,x_return_status => x_return_status
274 ,x_msg_count => x_msg_count
275 ,x_msg_data => x_msg_data);
276
277 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
278 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
279 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
280 'jtf.plsql.jtf_terr_task_pub.get_winningterrmembers.process_match',
281 'API JTY_ASSIGN_REALTIME_PUB.process_match has failed');
282 END IF;
283 RAISE FND_API.G_EXC_ERROR;
284 END IF;
285
286 -- debug message
287 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
288 FND_LOG.string(FND_LOG.LEVEL_EVENT,
289 'jtf.plsql.jtf_terr_task_pub.get_winningterrmembers.process_match',
290 'Finish calling procedure JTY_ASSIGN_REALTIME_PUB.process_match');
291 END IF;
292
293 JTY_ASSIGN_REALTIME_PUB.process_winners (
294 p_source_id => -1002
295 ,p_trans_id => -1006
296 ,p_program_name => 'SERVICE/TASKS PROGRAM'
297 ,p_mode => 'REAL TIME:RESOURCE'
298 ,p_role => p_role
299 ,p_resource_type => p_resource_type
300 ,x_return_status => x_return_status
301 ,x_msg_count => x_msg_count
302 ,x_msg_data => x_msg_data
303 ,x_winners_rec => lx_winners_rec);
304
305 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
306 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
307 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
308 'jtf.plsql.jtf_terr_task_pub.get_winningterrmembers.process_winners',
309 'API JTY_ASSIGN_REALTIME_PUB.process_winners has failed');
310 END IF;
311 RAISE FND_API.G_EXC_ERROR;
312 END IF;
313
314 -- debug message
315 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
316 FND_LOG.string(FND_LOG.LEVEL_EVENT,
317 'jtf.plsql.jtf_terr_task_pub.get_winningterrmembers.process_winners',
318 'Finish calling procedure JTY_ASSIGN_REALTIME_PUB.process_winners');
319 END IF;
320
321 /*
322 jtf_terr_1002_task_dyn.search_terr_rules(
323 p_rec => lp_rec
324 , x_rec => lx_rec
325 , p_role => p_role
326 , p_resource_type => p_resource_type );
327 */
328
329 l_counter := lx_winners_rec.terr_id.FIRST;
330 WHILE (l_counter <= lx_winners_rec.terr_id.LAST) LOOP
331
332 x_TerrResource_tbl(l_counter).TERR_RSC_ID := lx_winners_rec.terr_rsc_id(l_counter);
333 x_TerrResource_tbl(l_counter).RESOURCE_ID := lx_winners_rec.resource_id(l_counter);
334 x_TerrResource_tbl(l_counter).RESOURCE_TYPE := lx_winners_rec.resource_type(l_counter);
335 x_TerrResource_tbl(l_counter).GROUP_ID := lx_winners_rec.group_id(l_counter);
336 x_TerrResource_tbl(l_counter).ROLE := lx_winners_rec.role(l_counter);
337 x_TerrResource_tbl(l_counter).PRIMARY_CONTACT_FLAG := lx_winners_rec.PRIMARY_CONTACT_FLAG(l_counter);
338 x_TerrResource_tbl(l_counter).FULL_ACCESS_FLAG := lx_winners_rec.FULL_ACCESS_FLAG(l_counter);
339 x_TerrResource_tbl(l_counter).TERR_ID := lx_winners_rec.terr_id(l_counter);
340 x_TerrResource_tbl(l_counter).START_DATE := lx_winners_rec.terr_start_date(l_counter);
341 x_TerrResource_tbl(l_counter).END_DATE := lx_winners_rec.terr_end_date(l_counter);
342 x_TerrResource_tbl(l_counter).ABSOLUTE_RANK := lx_winners_rec.absolute_rank(l_counter);
343
344 l_counter := l_counter + 1;
345
346 END LOOP;
347
348 -- debug message
349 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
350 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
351 'jtf.plsql.jtf_terr_task_pub.get_winningterrmembers.end',
352 'End of the procedure jtf_terr_task_pub.get_winningterrmembers');
353 END IF;
354
355 EXCEPTION
356 WHEN FND_API.G_EXC_ERROR THEN
357 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
358 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
359 'jtf.plsql.jtf_terr_task_pub.get_winningterrmembers.g_exc_error',
360 substr(x_msg_data, 1, 4000));
361 END IF;
362
363 WHEN OTHERS THEN
364 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
365 x_msg_data := SQLCODE || ' : ' || SQLERRM;
366 x_msg_count := 1;
367 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
368 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
369 'jtf.plsql.jtf_terr_task_pub.get_winningterrmembers.other',
370 substr(x_msg_data, 1, 4000));
371 END IF;
372
373 End Get_WinningTerrMembers;
374
375 END JTF_TERR_TASK_PUB;