[Home] [Help]
PACKAGE BODY: APPS.JTF_TERR_SERVICE_PUB
Source
1 Package Body JTF_TERR_SERVICE_PUB AS
2 /* $Header: jtfptsvb.pls 120.3.12010000.2 2008/12/11 07:36:19 vpalle ship $ */
3 -- ---------------------------------------------------
4 -- Start of Comments
5 -- ---------------------------------------------------
6 -- PACKAGE NAME: JTF_TERR_SERVICE_PUB
7 -- ---------------------------------------------------
8 -- PURPOSE
9 -- Joint task force core 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/02/99 VNEDUNGA Changing the dynamic SQL corresponsing
22 -- to new record defnition.
23 -- 12/06/99 VNEDUNGA CHanging the dynamic SQL to take out
24 -- interest_Type_id's depenency
25 -- 12/22/99 VNEDUNGA Making changes to confirm to
26 -- new assignement manager requirement
27 -- 01/07/00 VNEDUNGA Changing the dynamic to confirm to new
28 -- qualifer list
29 -- 01/11/00 VNEDUNGA Changing Get_WinningTerritories API
30 -- 01/22/00 VNEDUNGA Changing company_name_range to comp_name_range
31 -- 02/01/00 VNEDUNGA Changing the get resource SQL
32 -- 02/08/00 VNEDUNGA Fixing bug 1184799, local rec declaration
33 -- typo
34 -- 02/24/00 vnedunga Making chnages to call the newly designed
35 -- Generated Engine packages
36 -- 02/24/00 vnedunga Adding the code to rerturn Catch all
37 -- if there was no qualifying Ter
38 -- 03/23/00 vnedunga Making changes to return full_access_flag
39 -- 05/01/00 VNEDUNGA Taking out for update clause from resource cursor
40 -- 06/14/00 vnedunga Changeing the get winning Terr memeber api
41 -- to return group_id
42 -- 05/08/01 arpatel taken out Get_WinningTerritories for service requests.
43 -- Implemented jtf_bulk_trans_rec_type generic type in Get_WinningTerrMembers.
44 -- Directly call jtf_terr_1002_service_dyn.search_terr_rules in Get_WinningTerrMembers.
45 -- 05/08/01 arpatel taken out Get_WinningTerritories for service requests/tasks.
46 -- 07/12/01 arpatel changing 'country' to 'county' for squal_char06 values.
47 -- 08/02/01 arpatel added new bulk qualifier mappings for Oracle Service/Service-task
48 -- 12/03/04 achanda added new mapping for component and subcomponent : bug # 3726007
49 -- 05/25/05 achanda Modified to the new 12.0 architecture
50 --
51 -- End of Comments
52 --
53 -- ***************************************************
54 -- GLOBAL VARIABLES
55 -- ***************************************************
56 G_PKG_NAME CONSTANT VARCHAR2(30):='JTF_TERR_SERVICE_PUB';
57 G_FILE_NAME CONSTANT VARCHAR2(12):='jtfptsvb.pls';
58
59 -- Start of Comments
60 -- ***************************************************
61 -- start of comments
62 -- ***************************************************
63 -- api name : Get_WinningTerrMembers
64 -- type : public.
65 -- function : Get winning territories members for an SERVICE_REQUEST
66 -- pre-reqs : Territories needs to be setup first
67 -- parameters :
68 --
69 -- IN:
70 -- p_api_version_number IN number required
71 -- p_init_msg_list IN varchar2 optional --default = fnd_api.g_false
72 -- p_commit IN varchar2 optional --default = fnd_api.g_false
73 -- p_Org_Id IN number required
74 -- p_TerrServReq_Rec IN JTF_Serv_Req_rec_type
75 -- p_Resource_Type IN varchar2
76 -- p_Role IN varchar2
77 -- p_plan_start_date IN DATE DEFAULT NULL
78 -- p_plan_end_date IN DATE DEFAULT NULL
79 --
80 -- out:
81 -- x_return_status out varchar2(1)
82 -- x_msg_count out number
83 -- x_msg_data out varchar2(2000)
84 -- x_TerrRes_tbl out TerrRes_tbl_type
85 --
86 -- requirements :
87 -- business rules :
88 -- version : current version 1.0
89 -- initial version: initial version 1.0
90 --
91 -- end of comments
92 procedure Get_WinningTerrMembers
93 ( p_api_version_number IN number,
94 p_init_msg_list IN varchar2 := fnd_api.g_false,
95 p_TerrServReq_Rec IN JTF_TERRITORY_PUB.JTF_Serv_Req_rec_type,
96 p_Resource_Type IN varchar2,
97 p_Role IN varchar2,
98 p_plan_start_date IN DATE DEFAULT NULL,
99 p_plan_end_date IN DATE DEFAULT NULL,
100 x_return_status OUT NOCOPY varchar2,
101 x_msg_count OUT NOCOPY number,
102 X_msg_data OUT NOCOPY varchar2,
103 x_TerrResource_tbl OUT NOCOPY JTF_TERRITORY_PUB.WinningTerrMember_tbl_type
104 )
105 AS
106 l_api_name CONSTANT VARCHAR2(30) := 'Get_WinningTerrMembers';
107 l_api_version_number CONSTANT NUMBER := 1.0;
108
109 l_Counter NUMBER;
110
111 lx_winners_rec JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type;
112 BEGIN
113
114 -- debug message
115 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
116 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
117 'jtf.plsql.jtf_terr_service_pub.get_winningterrmembers.start',
118 'Start of the procedure jtf_terr_service_pub.get_winningterrmembers');
119 END IF;
120
121 -- Standard call to check for call compatibility.
122 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
123 p_api_version_number,
124 l_api_name,
125 G_PKG_NAME)
126 THEN
127 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
128 END IF;
129
130 -- Initialize message list if p_init_msg_list is set to TRUE.
131 IF FND_API.to_Boolean( p_init_msg_list )
132 THEN
133 FND_MSG_PUB.initialize;
134 END IF;
135
136 --
137 -- API body
138 x_return_status := FND_API.G_RET_STS_SUCCESS;
139
140 -- debug message
141 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
142 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
143 'jtf.plsql.jtf_terr_service_pub.get_winningterrmembers.parameters',
144 'Country : ' || p_TerrServReq_Rec.COUNTRY || ' City : ' || p_TerrServReq_Rec.CITY ||
145 ' Postal Code : ' || p_TerrServReq_Rec.POSTAL_CODE || ' State : ' || p_TerrServReq_Rec.STATE ||
146 ' Area Code : ' || p_TerrServReq_Rec.AREA_CODE || ' County : ' || p_TerrServReq_Rec.COUNTY ||
147 ' Company Name Range : ' || p_TerrServReq_Rec.COMP_NAME_RANGE || ' Province : ' || p_TerrServReq_Rec.PROVINCE ||
148 ' Problem Code : ' || p_TerrServReq_Rec.PROBLEM_CODE ||
149 ' sr creation channel : ' || p_TerrServReq_Rec.SR_CREATION_CHANNEL ||
150 ' vip customer : ' || p_TerrServReq_Rec.squal_char11 || ' sr problem code : ' || p_TerrServReq_Rec.squal_char12 ||
151 ' sr customer contact preference : ' || p_TerrServReq_Rec.squal_char13 ||
152 ' sr service contact coverage : ' || p_TerrServReq_Rec.squal_char21 ||
153 ' sr language : ' || p_TerrServReq_Rec.squal_char20 ||
154 ' Number of Employees : ' || p_TerrServReq_Rec.NUM_OF_EMPLOYEES || ' Party ID : ' || p_TerrServReq_Rec.PARTY_ID ||
155 ' Party Site ID : ' || p_TerrServReq_Rec.PARTY_SITE_ID ||
156 ' Incident Type ID : ' || p_TerrServReq_Rec.INCIDENT_TYPE_ID ||
157 ' Incident severity ID : ' || p_TerrServReq_Rec.INCIDENT_SEVERITY_ID ||
158 ' Incident urgency ID : ' || p_TerrServReq_Rec.INCIDENT_URGENCY_ID ||
159 ' Incident status ID : ' || p_TerrServReq_Rec.INCIDENT_STATUS_ID ||
160 ' platform ID : ' || p_TerrServReq_Rec.PLATFORM_ID || ' Support Site ID : ' || p_TerrServReq_Rec.SUPPORT_SITE_ID ||
161 ' Cust Site ID : ' || p_TerrServReq_Rec.CUSTOMER_SITE_ID ||
162 ' Inventory Item ID : ' || p_TerrServReq_Rec.INVENTORY_ITEM_ID ||
163 ' SR Platform Inventory Item ID : ' || p_TerrServReq_Rec.SQUAL_NUM12 ||
164 ' SR Platform Org ID : ' || p_TerrServReq_Rec.SQUAL_NUM13 ||
165 ' SR Product Category ID : ' || p_TerrServReq_Rec.SQUAL_NUM14 ||
166 ' PCS Inventory Item ID : ' || p_TerrServReq_Rec.SQUAL_NUM15 ||
167 ' PCS Org ID : ' || p_TerrServReq_Rec.SQUAL_NUM16 ||
168 ' PCS Component ID : ' || p_TerrServReq_Rec.SQUAL_NUM23 ||
169 ' PCS Subcomponent ID : ' || p_TerrServReq_Rec.SQUAL_NUM24 ||
170 ' SR Group Owner ID : ' || p_TerrServReq_Rec.SQUAL_NUM17 ||
171 ' SSI Inventory Item ID : ' || p_TerrServReq_Rec.SQUAL_NUM18 ||
172 ' SSI Org ID : ' || p_TerrServReq_Rec.SQUAL_NUM19||
173 ' p_plan_start_date: ' || p_plan_start_date ||
174 ' p_plan_end_date: ' || p_plan_end_date);
175 END IF;
176
177 /* delete and insert all the attributes into the trans table as name - value pair */
178 DELETE jty_terr_nvp_trans_gt;
179 INSERT INTO jty_terr_nvp_trans_gt (
180 attribute_name
181 ,num_value
182 ,char_value
183 ,date_value )
184 ( SELECT 'COUNTRY' attribute_name
185 ,null num_value
186 ,p_TerrServReq_Rec.COUNTRY char_value
187 ,null date_value
188 FROM DUAL
189 UNION ALL
190 SELECT 'CITY' attribute_name
191 ,null num_value
192 ,p_TerrServReq_Rec.CITY char_value
193 ,null date_value
194 FROM DUAL
195 UNION ALL
196 SELECT 'POSTAL_CODE' attribute_name
197 ,null num_value
198 ,p_TerrServReq_Rec.POSTAL_CODE char_value
199 ,null date_value
200 FROM DUAL
201 UNION ALL
202 SELECT 'STATE' attribute_name
203 ,null num_value
204 ,p_TerrServReq_Rec.STATE char_value
205 ,null date_value
206 FROM DUAL
207 UNION ALL
208 SELECT 'AREA_CODE' attribute_name
209 ,null num_value
210 ,p_TerrServReq_Rec.AREA_CODE char_value
211 ,null date_value
212 FROM DUAL
213 UNION ALL
214 SELECT 'COUNTY' attribute_name
215 ,null num_value
216 ,p_TerrServReq_Rec.COUNTY char_value
217 ,null date_value
218 FROM DUAL
219 UNION ALL
220 SELECT 'COMP_NAME_RANGE' attribute_name
221 ,null num_value
222 ,p_TerrServReq_Rec.COMP_NAME_RANGE char_value
223 ,null date_value
224 FROM DUAL
225 UNION ALL
226 SELECT 'PROVINCE' attribute_name
227 ,null num_value
228 ,p_TerrServReq_Rec.PROVINCE char_value
229 ,null date_value
230 FROM DUAL
231 UNION ALL
232 SELECT 'PROBLEM_CODE' attribute_name
233 ,null num_value
234 ,p_TerrServReq_Rec.PROBLEM_CODE char_value
235 ,null date_value
236 FROM DUAL
237 UNION ALL
238 SELECT 'SR_CREATION_CHANNEL' attribute_name
239 ,null num_value
240 ,p_TerrServReq_Rec.SR_CREATION_CHANNEL char_value
241 ,null date_value
242 FROM DUAL
243 UNION ALL
244 SELECT 'VIP_CUSTOMER' attribute_name
245 ,null num_value
246 ,p_TerrServReq_Rec.squal_char11 char_value
247 ,null date_value
248 FROM DUAL
249 UNION ALL
250 SELECT 'SR_PROBLEM_CODE' attribute_name
251 ,null num_value
252 ,p_TerrServReq_Rec.squal_char12 char_value
253 ,null date_value
254 FROM DUAL
255 UNION ALL
256 SELECT 'SR_CUST_CNTCT_PREF' attribute_name
257 ,null num_value
258 ,p_TerrServReq_Rec.squal_char13 char_value
259 ,null date_value
260 FROM DUAL
261 UNION ALL
262 SELECT 'SR_SRVC_CNTCT_CVG' attribute_name
263 ,null num_value
264 ,p_TerrServReq_Rec.squal_char21 char_value
265 ,null date_value
266 FROM DUAL
267 UNION ALL
268 SELECT 'SR_LANGUAGE' attribute_name
269 ,null num_value
270 ,p_TerrServReq_Rec.squal_char20 char_value
271 ,null date_value
272 FROM DUAL
273 UNION ALL
274 SELECT 'PARTY_ID' attribute_name
275 ,p_TerrServReq_Rec.PARTY_ID num_value
276 ,null char_value
277 ,null date_value
278 FROM DUAL
279 UNION ALL
280 SELECT 'PARTY_SITE_ID' attribute_name
281 ,p_TerrServReq_Rec.PARTY_SITE_ID num_value
282 ,null char_value
283 ,null date_value
284 FROM DUAL
285 UNION ALL
286 SELECT 'NUM_OF_EMPLOYEES' attribute_name
287 ,p_TerrServReq_Rec.NUM_OF_EMPLOYEES num_value
288 ,null char_value
289 ,null date_value
290 FROM DUAL
291 UNION ALL
292 SELECT 'INCIDENT_TYPE_ID' attribute_name
293 ,p_TerrServReq_Rec.INCIDENT_TYPE_ID num_value
294 ,null char_value
295 ,null date_value
296 FROM DUAL
297 UNION ALL
298 SELECT 'INCIDENT_SEVERITY_ID' attribute_name
299 ,p_TerrServReq_Rec.INCIDENT_SEVERITY_ID num_value
300 ,null char_value
301 ,null date_value
302 FROM DUAL
303 UNION ALL
304 SELECT 'INCIDENT_URGENCY_ID' attribute_name
305 ,p_TerrServReq_Rec.INCIDENT_URGENCY_ID num_value
306 ,null char_value
307 ,null date_value
308 FROM DUAL
309 UNION ALL
310 SELECT 'INCIDENT_STATUS_ID' attribute_name
311 ,p_TerrServReq_Rec.INCIDENT_STATUS_ID num_value
312 ,null char_value
313 ,null date_value
314 FROM DUAL
315 UNION ALL
316 SELECT 'PLATFORM_ID' attribute_name
317 ,p_TerrServReq_Rec.PLATFORM_ID num_value
321 UNION ALL
318 ,null char_value
319 ,null date_value
320 FROM DUAL
322 SELECT 'SUPPORT_SITE_ID' attribute_name
323 ,p_TerrServReq_Rec.SUPPORT_SITE_ID num_value
324 ,null char_value
325 ,null date_value
326 FROM DUAL
327 UNION ALL
328 SELECT 'CUSTOMER_SITE_ID' attribute_name
329 ,p_TerrServReq_Rec.CUSTOMER_SITE_ID num_value
330 ,null char_value
331 ,null date_value
332 FROM DUAL
333 UNION ALL
334 SELECT 'INVENTORY_ITEM_ID' attribute_name
335 ,p_TerrServReq_Rec.INVENTORY_ITEM_ID num_value
336 ,null char_value
337 ,null date_value
338 FROM DUAL
339 UNION ALL
340 SELECT 'SRP_INVENTORY_ITEM_ID' attribute_name
341 ,p_TerrServReq_Rec.SQUAL_NUM12 num_value
342 ,null char_value
343 ,null date_value
344 FROM DUAL
345 UNION ALL
346 SELECT 'SRP_ORG_ID' attribute_name
347 ,p_TerrServReq_Rec.SQUAL_NUM13 num_value
348 ,null char_value
349 ,null date_value
350 FROM DUAL
351 UNION ALL
352 SELECT 'SPC_CATEGORY_ID' attribute_name
353 ,p_TerrServReq_Rec.SQUAL_NUM14 num_value
354 ,null char_value
355 ,null date_value
356 FROM DUAL
357 UNION ALL
358 SELECT 'PCS_INVENTORY_ITEM_ID' attribute_name
359 ,p_TerrServReq_Rec.SQUAL_NUM15 num_value
360 ,null char_value
361 ,null date_value
362 FROM DUAL
363 UNION ALL
364 SELECT 'PCS_ORG_ID' attribute_name
365 ,p_TerrServReq_Rec.SQUAL_NUM16 num_value
366 ,null char_value
367 ,null date_value
368 FROM DUAL
369 UNION ALL
370 SELECT 'PCS_COMPONENT_ID' attribute_name
371 ,p_TerrServReq_Rec.SQUAL_NUM23 num_value
372 ,null char_value
373 ,null date_value
374 FROM DUAL
375 UNION ALL
376 SELECT 'PCS_SUBCOMPONENT_ID' attribute_name
377 ,p_TerrServReq_Rec.SQUAL_NUM24 num_value
378 ,null char_value
379 ,null date_value
380 FROM DUAL
381 UNION ALL
382 SELECT 'SR_GROUP_OWNER_ID' attribute_name
383 ,p_TerrServReq_Rec.SQUAL_NUM17 num_value
384 ,null char_value
385 ,null date_value
386 FROM DUAL
387 UNION ALL
388 SELECT 'SSI_INVENTORY_ITEM_ID' attribute_name
389 ,p_TerrServReq_Rec.SQUAL_NUM18 num_value
390 ,null char_value
391 ,null date_value
392 FROM DUAL
393 UNION ALL
394 SELECT 'SSI_ORG_ID' attribute_name
395 ,p_TerrServReq_Rec.SQUAL_NUM19 num_value
396 ,null char_value
397 ,null date_value
398 FROM DUAL
399 );
400
401 /*
402 lp_Rec.squal_num01 := jtf_terr_number_list(p_TerrServReq_Rec.party_id);
403 lp_Rec.squal_num02 := jtf_terr_number_list(p_TerrServReq_Rec.party_site_id);
404 lp_Rec.squal_num03 := jtf_terr_number_list(p_TerrServReq_Rec.num_of_employees);
405 lp_Rec.squal_num04 := jtf_terr_number_list(p_TerrServReq_Rec.incident_type_id);
406 lp_Rec.squal_num05 := jtf_terr_number_list(p_TerrServReq_Rec.incident_severity_id);
407 lp_Rec.squal_num06 := jtf_terr_number_list(p_TerrServReq_Rec.incident_urgency_id);
408 lp_Rec.squal_num07 := jtf_terr_number_list(p_TerrServReq_Rec.incident_status_id);
409 lp_Rec.squal_num08 := jtf_terr_number_list(p_TerrServReq_Rec.platform_id);
410 lp_Rec.squal_num09 := jtf_terr_number_list(p_TerrServReq_Rec.support_site_id);
411 lp_Rec.squal_num10 := jtf_terr_number_list(p_TerrServReq_Rec.customer_site_id);
412 lp_Rec.squal_num11 := jtf_terr_number_list(p_TerrServReq_Rec.inventory_item_id);
413
414 --arpatel 08/02
415 Qualifier: SR Platform
416 lp_Rec.squal_num12 := jtf_terr_number_list(p_TerrServReq_Rec.squal_num12); -- Inventory Item Id
417 lp_Rec.squal_num13 := jtf_terr_number_list(p_TerrServReq_Rec.squal_num13); -- Organization Id
418
419 Qualifier: SR Product Category
420 lp_Rec.squal_num14 := jtf_terr_number_list(p_TerrServReq_Rec.squal_num14); -- Category Id
421
422 Qualifier: SR Product, Component and Subcomponent
423 lp_Rec.squal_num15 := jtf_terr_number_list(p_TerrServReq_Rec.squal_num15); -- Inventory Item Id
424 lp_Rec.squal_num16 := jtf_terr_number_list(p_TerrServReq_Rec.squal_num16); -- Organization Id
425 lp_Rec.squal_num23 := jtf_terr_number_list(p_TerrServReq_Rec.squal_num23); -- Component ID
426 lp_Rec.squal_num24 := jtf_terr_number_list(p_TerrServReq_Rec.squal_num24); -- Subcomponent ID
427
428 Qualifier: SR Group Owner
429 lp_Rec.squal_num17 := jtf_terr_number_list(p_TerrServReq_Rec.squal_num17);
430
431 Contract Support Service Item
432 lp_Rec.squal_num18 := jtf_terr_number_list(p_TerrServReq_Rec.squal_num18); -- Inventory Item Id
433 lp_Rec.squal_num19 := jtf_terr_number_list(p_TerrServReq_Rec.squal_num19); -- Organization Id
434
435 lp_Rec.squal_char01 := jtf_terr_char_360list(p_TerrServReq_Rec.country);
436 lp_Rec.squal_char02 := jtf_terr_char_360list(p_TerrServReq_Rec.city);
437 lp_Rec.squal_char03 := jtf_terr_char_360list(p_TerrServReq_Rec.postal_code);
438 lp_Rec.squal_char04 := jtf_terr_char_360list(p_TerrServReq_Rec.state);
439 lp_Rec.squal_char05 := jtf_terr_char_360list(p_TerrServReq_Rec.area_code);
440 lp_Rec.squal_char06 := jtf_terr_char_360list(p_TerrServReq_Rec.county);
441 lp_Rec.squal_char07 := jtf_terr_char_360list(p_TerrServReq_Rec.comp_name_range);
442 lp_Rec.squal_char08 := jtf_terr_char_360list(p_TerrServReq_Rec.province);
443 lp_Rec.squal_char09 := jtf_terr_char_360list(p_TerrServReq_Rec.problem_code);
444 lp_Rec.squal_char10 := jtf_terr_char_360list(p_TerrServReq_Rec.sr_creation_channel);
445
446 --arpatel 08/02
447 VIP Customers
448 lp_Rec.squal_char11 := jtf_terr_char_360list(p_TerrServReq_Rec.squal_char11);
449
450 Qualifier: SR Problem Code
451 lp_Rec.squal_char12 := jtf_terr_char_360list(p_TerrServReq_Rec.squal_char12);
452
453 Qualifier: SR Customer Contact Preference
454 lp_Rec.squal_char13 := jtf_terr_char_360list(p_TerrServReq_Rec.squal_char13);
455
456 Qualifier: SR Service Contract Coverage
457 lp_Rec.squal_char21 := jtf_terr_char_360list(p_TerrServReq_Rec.squal_char21);
458
459 SR Language -JDOCHERT 12/17/01 - bug#2152253
460 lp_Rec.squal_char20 := jtf_terr_char_360list(p_TerrServReq_Rec.squal_char20);
461 */
462
463 JTY_ASSIGN_REALTIME_PUB.process_match (
464 p_source_id => -1002
465 ,p_trans_id => -1005
466 ,p_program_name => 'SERVICE/SERVICE REQUEST PROGRAM'
467 ,p_mode => 'REAL TIME:RESOURCE'
468 ,x_return_status => x_return_status
469 ,x_msg_count => x_msg_count
470 ,x_msg_data => x_msg_data);
471
472 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
473 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
474 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
475 'jtf.plsql.jtf_terr_service_pub.get_winningterrmembers.process_match',
476 'API JTY_ASSIGN_REALTIME_PUB.process_match has failed');
477 END IF;
478 RAISE FND_API.G_EXC_ERROR;
479 END IF;
480
481 -- debug message
482 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
483 FND_LOG.string(FND_LOG.LEVEL_EVENT,
484 'jtf.plsql.jtf_terr_service_pub.get_winningterrmembers.process_match',
485 'Finish calling procedure JTY_ASSIGN_REALTIME_PUB.process_match');
486 END IF;
487
488 JTY_ASSIGN_REALTIME_PUB.process_winners (
489 p_source_id => -1002
490 ,p_trans_id => -1005
491 ,p_program_name => 'SERVICE/SERVICE REQUEST PROGRAM'
492 ,p_mode => 'REAL TIME:RESOURCE'
493 ,p_role => p_role
494 ,p_resource_type => p_resource_type
495 ,p_plan_start_date => p_plan_start_date
496 ,p_plan_end_date => p_plan_end_date
497 ,x_return_status => x_return_status
498 ,x_msg_count => x_msg_count
499 ,x_msg_data => x_msg_data
500 ,x_winners_rec => lx_winners_rec);
501
502 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
503 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
504 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
505 'jtf.plsql.jtf_terr_service_pub.get_winningterrmembers.process_winners',
506 'API JTY_ASSIGN_REALTIME_PUB.process_winners has failed');
507 END IF;
508 RAISE FND_API.G_EXC_ERROR;
509 END IF;
510
511 -- debug message
512 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
513 FND_LOG.string(FND_LOG.LEVEL_EVENT,
514 'jtf.plsql.jtf_terr_service_pub.get_winningterrmembers.process_winners',
515 'Finish calling procedure JTY_ASSIGN_REALTIME_PUB.process_winners');
516 END IF;
517
518 /*
519 jtf_terr_1002_serv_req_dyn.search_terr_rules(
520 p_rec => lp_rec
521 , x_rec => lx_rec
522 , p_role => p_role
523 , p_resource_type => p_resource_type );
524 */
525
526 l_counter := lx_winners_rec.terr_id.FIRST;
527
528 WHILE (l_counter <= lx_winners_rec.terr_id.LAST) LOOP
529
530 x_TerrResource_tbl(l_counter).TERR_RSC_ID := lx_winners_rec.terr_rsc_id(l_counter);
534 x_TerrResource_tbl(l_counter).ROLE := lx_winners_rec.role(l_counter);
531 x_TerrResource_tbl(l_counter).RESOURCE_ID := lx_winners_rec.resource_id(l_counter);
532 x_TerrResource_tbl(l_counter).RESOURCE_TYPE := lx_winners_rec.resource_type(l_counter);
533 x_TerrResource_tbl(l_counter).GROUP_ID := lx_winners_rec.group_id(l_counter);
535 x_TerrResource_tbl(l_counter).PRIMARY_CONTACT_FLAG := lx_winners_rec.PRIMARY_CONTACT_FLAG(l_counter);
536 x_TerrResource_tbl(l_counter).FULL_ACCESS_FLAG := lx_winners_rec.FULL_ACCESS_FLAG(l_counter);
537 x_TerrResource_tbl(l_counter).TERR_ID := lx_winners_rec.terr_id(l_counter);
538 x_TerrResource_tbl(l_counter).START_DATE := lx_winners_rec.rsc_start_date(l_counter);
539 x_TerrResource_tbl(l_counter).END_DATE := lx_winners_rec.rsc_end_date(l_counter);
540 x_TerrResource_tbl(l_counter).ABSOLUTE_RANK := lx_winners_rec.absolute_rank(l_counter);
541
542 l_counter := l_counter + 1;
543
544 END LOOP;
545
546 -- debug message
547 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
548 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
549 'jtf.plsql.jtf_terr_service_pub.get_winningterrmembers.end',
550 'End of the procedure jtf_terr_service_pub.get_winningterrmembers');
551 END IF;
552
553 EXCEPTION
554 WHEN FND_API.G_EXC_ERROR THEN
555 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
556 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
557 'jtf.plsql.jtf_terr_service_pub.get_winningterrmembers.g_exc_error',
558 substr(x_msg_data, 1, 4000));
559 END IF;
560
561 WHEN OTHERS THEN
562 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
563 x_msg_data := SQLCODE || ' : ' || SQLERRM;
564 x_msg_count := 1;
565 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
566 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
567 'jtf.plsql.jtf_terr_service_pub.get_winningterrmembers.other',
568 substr(x_msg_data, 1, 4000));
569 END IF;
570
571 End Get_WinningTerrMembers;
572
573
574 -- ***************************************************
575 -- start of comments
576 -- ***************************************************
577 -- api name : Get_WinningTerrMembers
578 -- type : public.
579 -- function : Get winning territories members for an SERVICE_REQUEST/TASK
580 -- pre-reqs : Territories needs to be setup first
581 -- parameters :
582 --
583 -- IN:
584 -- p_api_version_number IN number required
585 -- p_init_msg_list IN varchar2 optional --default = fnd_api.g_false
586 -- p_commit IN varchar2 optional --default = fnd_api.g_false
587 -- p_Org_Id IN number required
588 -- p_TerrSrvTask_Rec IN JTF_srv_Task_rec_type
589 -- p_Resource_Type IN varchar2
590 -- p_Role IN varchar2
591 -- p_plan_start_date IN DATE DEFAULT NULL
592 -- p_plan_end_date IN DATE DEFAULT NULL
593 --
594 -- out:
595 -- x_return_status out varchar2(1)
596 -- x_msg_count out number
597 -- x_msg_data out varchar2(2000)
598 -- x_TerrRes_tbl out TerrRes_tbl_type
599 --
600 -- requirements :
601 -- business rules :
602 -- version : current version 1.0
603 -- initial version: initial version 1.0
604 --
605 -- notes: Public API for retreving a set of winning
606 -- territories resources. This is an overloaded
607 -- procedure for accounts,lead, oppor, service
608 -- requests, and collections.
609 --
610 -- end of comments
611 procedure Get_WinningTerrMembers
612 ( p_api_version_number IN number,
613 p_init_msg_list IN varchar2 := fnd_api.g_false,
614 p_TerrSrvTask_Rec IN JTF_TERRITORY_PUB.JTF_Srv_Task_rec_type,
615 p_Resource_Type IN varchar2,
616 p_Role IN varchar2,
617 p_plan_start_date IN DATE DEFAULT NULL,
618 p_plan_end_date IN DATE DEFAULT NULL,
619 x_return_status OUT NOCOPY varchar2,
620 x_msg_count OUT NOCOPY number,
621 X_msg_data OUT NOCOPY varchar2,
622 x_TerrResource_tbl OUT NOCOPY JTF_TERRITORY_PUB.WinningTerrMember_tbl_type
623 )
624 AS
625 l_api_name CONSTANT VARCHAR2(30) := 'Get_WinningTerrMembers';
626 l_api_version_number CONSTANT NUMBER := 1.0;
627
628 l_Counter NUMBER := 0;
629
630 lx_winners_rec JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type;
631 BEGIN
632 -- debug message
633 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
634 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
635 'jtf.plsql.jtf_terr_service_pub.get_winningterrmembers.start',
636 'Start of the procedure jtf_terr_service_pub.get_winningterrmembers');
637 END IF;
638
639 -- Standard call to check for call compatibility.
640 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
641 p_api_version_number,
645 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
642 l_api_name,
643 G_PKG_NAME)
644 THEN
646 END IF;
647
648 -- Initialize message list if p_init_msg_list is set to TRUE.
649 IF FND_API.to_Boolean( p_init_msg_list )
650 THEN
651 FND_MSG_PUB.initialize;
652 END IF;
653
654 --
655 -- API body
656 --
657 x_return_status := FND_API.G_RET_STS_SUCCESS;
658
659 -- debug message
660 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
661 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
662 'jtf.plsql.jtf_terr_service_pub.get_winningterrmembers.parameters',
663 'Country : ' || p_TerrSrvTask_Rec.COUNTRY || ' City : ' || p_TerrSrvTask_Rec.CITY ||
664 ' Postal Code : ' || p_TerrSrvTask_Rec.POSTAL_CODE || ' State : ' || p_TerrSrvTask_Rec.STATE ||
665 ' Area Code : ' || p_TerrSrvTask_Rec.AREA_CODE || ' County : ' || p_TerrSrvTask_Rec.COUNTY ||
666 ' Company Name Range : ' || p_TerrSrvTask_Rec.COMP_NAME_RANGE || ' Province : ' || p_TerrSrvTask_Rec.PROVINCE ||
667 ' Problem Code : ' || p_TerrSrvTask_Rec.PROBLEM_CODE ||
668 ' sr creation channel : ' || p_TerrSrvTask_Rec.SR_CREATION_CHANNEL ||
669 ' vip customer : ' || p_TerrSrvTask_Rec.squal_char11 || ' sr problem code : ' || p_TerrSrvTask_Rec.squal_char12 ||
670 ' sr customer contact preference : ' || p_TerrSrvTask_Rec.squal_char13 ||
671 ' sr service contact coverage : ' || p_TerrSrvTask_Rec.squal_char21 ||
672 ' sr language : ' || p_TerrSrvTask_Rec.squal_char20 ||
673 ' Number of Employees : ' || p_TerrSrvTask_Rec.NUM_OF_EMPLOYEES || ' Party ID : ' || p_TerrSrvTask_Rec.PARTY_ID ||
674 ' Party Site ID : ' || p_TerrSrvTask_Rec.PARTY_SITE_ID ||
675 ' Incident Type ID : ' || p_TerrSrvTask_Rec.INCIDENT_TYPE_ID ||
676 ' Incident severity ID : ' || p_TerrSrvTask_Rec.INCIDENT_SEVERITY_ID ||
677 ' Incident urgency ID : ' || p_TerrSrvTask_Rec.INCIDENT_URGENCY_ID ||
678 ' Incident status ID : ' || p_TerrSrvTask_Rec.INCIDENT_STATUS_ID ||
679 ' platform ID : ' || p_TerrSrvTask_Rec.PLATFORM_ID || ' Support Site ID : ' || p_TerrSrvTask_Rec.SUPPORT_SITE_ID ||
680 ' Cust Site ID : ' || p_TerrSrvTask_Rec.CUSTOMER_SITE_ID ||
681 ' Inventory Item ID : ' || p_TerrSrvTask_Rec.INVENTORY_ITEM_ID ||
682 ' Task Type ID : ' || p_TerrSrvTask_Rec.TASK_TYPE_ID ||
683 ' Task Status ID : ' || p_TerrSrvTask_Rec.TASK_STATUS_ID ||
684 ' Task Priority ID : ' || p_TerrSrvTask_Rec.TASK_PRIORITY_ID ||
685 ' SR Platform Inventory Item ID : ' || p_TerrSrvTask_Rec.SQUAL_NUM12 ||
686 ' SR Platform Org ID : ' || p_TerrSrvTask_Rec.SQUAL_NUM13 ||
687 ' SR Product Category ID : ' || p_TerrSrvTask_Rec.SQUAL_NUM14 ||
688 ' PCS Inventory Item ID : ' || p_TerrSrvTask_Rec.SQUAL_NUM15 ||
689 ' PCS Org ID : ' || p_TerrSrvTask_Rec.SQUAL_NUM16 ||
690 ' PCS Component ID : ' || p_TerrSrvTask_Rec.SQUAL_NUM23 ||
691 ' PCS Subcomponent ID : ' || p_TerrSrvTask_Rec.SQUAL_NUM24 ||
692 ' SR Group Owner ID : ' || p_TerrSrvTask_Rec.SQUAL_NUM17 ||
693 ' SSI Inventory Item ID : ' || p_TerrSrvTask_Rec.SQUAL_NUM18 ||
694 ' SSI Org ID : ' || p_TerrSrvTask_Rec.SQUAL_NUM19||
695 ' p_plan_start_date: ' || p_plan_start_date ||
696 ' p_plan_end_date: ' || p_plan_end_date);
697 END IF;
698
699 /* delete and insert all the attributes into the trans table as name - value pair */
700 DELETE jty_terr_nvp_trans_gt;
701 INSERT INTO jty_terr_nvp_trans_gt (
702 attribute_name
703 ,num_value
704 ,char_value
705 ,date_value )
706 ( SELECT 'COUNTRY' attribute_name
707 ,null num_value
708 ,p_TerrSrvTask_Rec.COUNTRY char_value
709 ,null date_value
710 FROM DUAL
711 UNION ALL
712 SELECT 'CITY' attribute_name
713 ,null num_value
714 ,p_TerrSrvTask_Rec.CITY char_value
715 ,null date_value
716 FROM DUAL
717 UNION ALL
718 SELECT 'POSTAL_CODE' attribute_name
719 ,null num_value
720 ,p_TerrSrvTask_Rec.POSTAL_CODE char_value
721 ,null date_value
722 FROM DUAL
723 UNION ALL
724 SELECT 'STATE' attribute_name
725 ,null num_value
726 ,p_TerrSrvTask_Rec.STATE char_value
727 ,null date_value
728 FROM DUAL
729 UNION ALL
730 SELECT 'AREA_CODE' attribute_name
731 ,null num_value
732 ,p_TerrSrvTask_Rec.AREA_CODE char_value
733 ,null date_value
734 FROM DUAL
735 UNION ALL
736 SELECT 'COUNTY' attribute_name
737 ,null num_value
738 ,p_TerrSrvTask_Rec.COUNTY char_value
739 ,null date_value
740 FROM DUAL
741 UNION ALL
742 SELECT 'COMP_NAME_RANGE' attribute_name
746 FROM DUAL
743 ,null num_value
744 ,p_TerrSrvTask_Rec.COMP_NAME_RANGE char_value
745 ,null date_value
747 UNION ALL
748 SELECT 'PROVINCE' attribute_name
749 ,null num_value
750 ,p_TerrSrvTask_Rec.PROVINCE char_value
751 ,null date_value
752 FROM DUAL
753 UNION ALL
754 SELECT 'PROBLEM_CODE' attribute_name
755 ,null num_value
756 ,p_TerrSrvTask_Rec.PROBLEM_CODE char_value
757 ,null date_value
758 FROM DUAL
759 UNION ALL
760 SELECT 'SR_CREATION_CHANNEL' attribute_name
761 ,null num_value
762 ,p_TerrSrvTask_Rec.SR_CREATION_CHANNEL char_value
763 ,null date_value
764 FROM DUAL
765 UNION ALL
766 SELECT 'VIP_CUSTOMER' attribute_name
767 ,null num_value
768 ,p_TerrSrvTask_Rec.squal_char11 char_value
769 ,null date_value
770 FROM DUAL
771 UNION ALL
772 SELECT 'SR_PROBLEM_CODE' attribute_name
773 ,null num_value
774 ,p_TerrSrvTask_Rec.squal_char12 char_value
775 ,null date_value
776 FROM DUAL
777 UNION ALL
778 SELECT 'SR_CUST_CNTCT_PREF' attribute_name
779 ,null num_value
780 ,p_TerrSrvTask_Rec.squal_char13 char_value
781 ,null date_value
782 FROM DUAL
783 UNION ALL
784 SELECT 'SR_SRVC_CNTCT_CVG' attribute_name
785 ,null num_value
786 ,p_TerrSrvTask_Rec.squal_char21 char_value
787 ,null date_value
788 FROM DUAL
789 UNION ALL
790 SELECT 'SR_LANGUAGE' attribute_name
791 ,null num_value
792 ,p_TerrSrvTask_Rec.squal_char20 char_value
793 ,null date_value
794 FROM DUAL
795 UNION ALL
796 SELECT 'PARTY_ID' attribute_name
797 ,p_TerrSrvTask_Rec.PARTY_ID num_value
798 ,null char_value
799 ,null date_value
800 FROM DUAL
801 UNION ALL
802 SELECT 'PARTY_SITE_ID' attribute_name
803 ,p_TerrSrvTask_Rec.PARTY_SITE_ID num_value
804 ,null char_value
805 ,null date_value
806 FROM DUAL
807 UNION ALL
808 SELECT 'NUM_OF_EMPLOYEES' attribute_name
809 ,p_TerrSrvTask_Rec.NUM_OF_EMPLOYEES num_value
810 ,null char_value
811 ,null date_value
812 FROM DUAL
813 UNION ALL
814 SELECT 'INCIDENT_TYPE_ID' attribute_name
815 ,p_TerrSrvTask_Rec.INCIDENT_TYPE_ID num_value
816 ,null char_value
817 ,null date_value
818 FROM DUAL
819 UNION ALL
820 SELECT 'INCIDENT_SEVERITY_ID' attribute_name
821 ,p_TerrSrvTask_Rec.INCIDENT_SEVERITY_ID num_value
822 ,null char_value
823 ,null date_value
824 FROM DUAL
825 UNION ALL
826 SELECT 'INCIDENT_URGENCY_ID' attribute_name
827 ,p_TerrSrvTask_Rec.INCIDENT_URGENCY_ID num_value
828 ,null char_value
829 ,null date_value
830 FROM DUAL
831 UNION ALL
832 SELECT 'INCIDENT_STATUS_ID' attribute_name
833 ,p_TerrSrvTask_Rec.INCIDENT_STATUS_ID num_value
834 ,null char_value
835 ,null date_value
836 FROM DUAL
837 UNION ALL
838 SELECT 'PLATFORM_ID' attribute_name
839 ,p_TerrSrvTask_Rec.PLATFORM_ID num_value
840 ,null char_value
841 ,null date_value
842 FROM DUAL
843 UNION ALL
844 SELECT 'SUPPORT_SITE_ID' attribute_name
845 ,p_TerrSrvTask_Rec.SUPPORT_SITE_ID num_value
846 ,null char_value
847 ,null date_value
848 FROM DUAL
849 UNION ALL
850 SELECT 'CUSTOMER_SITE_ID' attribute_name
851 ,p_TerrSrvTask_Rec.CUSTOMER_SITE_ID num_value
852 ,null char_value
853 ,null date_value
854 FROM DUAL
855 UNION ALL
856 SELECT 'INVENTORY_ITEM_ID' attribute_name
857 ,p_TerrSrvTask_Rec.INVENTORY_ITEM_ID num_value
858 ,null char_value
862 SELECT 'TASK_TYPE_ID' attribute_name
859 ,null date_value
860 FROM DUAL
861 UNION ALL
863 ,p_TerrSrvTask_Rec.TASK_TYPE_ID num_value
864 ,null char_value
865 ,null date_value
866 FROM DUAL
867 UNION ALL
868 SELECT 'TASK_STATUS_ID' attribute_name
869 ,p_TerrSrvTask_Rec.TASK_STATUS_ID num_value
870 ,null char_value
871 ,null date_value
872 FROM DUAL
873 UNION ALL
874 SELECT 'TASK_PRIORITY_ID' attribute_name
875 ,p_TerrSrvTask_Rec.TASK_PRIORITY_ID num_value
876 ,null char_value
877 ,null date_value
878 FROM DUAL
879 UNION ALL
880 SELECT 'SRP_INVENTORY_ITEM_ID' attribute_name
881 ,p_TerrSrvTask_Rec.SQUAL_NUM12 num_value
882 ,null char_value
883 ,null date_value
884 FROM DUAL
885 UNION ALL
886 SELECT 'SRP_ORG_ID' attribute_name
887 ,p_TerrSrvTask_Rec.SQUAL_NUM13 num_value
888 ,null char_value
889 ,null date_value
890 FROM DUAL
891 UNION ALL
892 SELECT 'SPC_CATEGORY_ID' attribute_name
893 ,p_TerrSrvTask_Rec.SQUAL_NUM14 num_value
894 ,null char_value
895 ,null date_value
896 FROM DUAL
897 UNION ALL
898 SELECT 'PCS_INVENTORY_ITEM_ID' attribute_name
899 ,p_TerrSrvTask_Rec.SQUAL_NUM15 num_value
900 ,null char_value
901 ,null date_value
902 FROM DUAL
903 UNION ALL
904 SELECT 'PCS_ORG_ID' attribute_name
905 ,p_TerrSrvTask_Rec.SQUAL_NUM16 num_value
906 ,null char_value
907 ,null date_value
908 FROM DUAL
909 UNION ALL
910 SELECT 'PCS_COMPONENT_ID' attribute_name
911 ,p_TerrSrvTask_Rec.SQUAL_NUM23 num_value
912 ,null char_value
913 ,null date_value
914 FROM DUAL
915 UNION ALL
916 SELECT 'PCS_SUBCOMPONENT_ID' attribute_name
917 ,p_TerrSrvTask_Rec.SQUAL_NUM24 num_value
918 ,null char_value
919 ,null date_value
920 FROM DUAL
921 UNION ALL
922 SELECT 'SR_GROUP_OWNER_ID' attribute_name
923 ,p_TerrSrvTask_Rec.SQUAL_NUM17 num_value
924 ,null char_value
925 ,null date_value
926 FROM DUAL
927 UNION ALL
928 SELECT 'SSI_INVENTORY_ITEM_ID' attribute_name
929 ,p_TerrSrvTask_Rec.SQUAL_NUM18 num_value
930 ,null char_value
931 ,null date_value
932 FROM DUAL
933 UNION ALL
934 SELECT 'SSI_ORG_ID' attribute_name
935 ,p_TerrSrvTask_Rec.SQUAL_NUM19 num_value
936 ,null char_value
937 ,null date_value
938 FROM DUAL
939 );
940
941 /*
942 lp_Rec.squal_num01 := jtf_terr_number_list(p_TerrSrvTask_Rec.party_id);
943 lp_Rec.squal_num02 := jtf_terr_number_list(p_TerrSrvTask_Rec.party_site_id);
944 lp_Rec.squal_num03 := jtf_terr_number_list(p_TerrSrvTask_Rec.num_of_employees);
945 lp_Rec.squal_num04 := jtf_terr_number_list(p_TerrSrvTask_Rec.incident_type_id);
946 lp_Rec.squal_num05 := jtf_terr_number_list(p_TerrSrvTask_Rec.incident_severity_id);
947 lp_Rec.squal_num06 := jtf_terr_number_list(p_TerrSrvTask_Rec.incident_urgency_id);
948 lp_Rec.squal_num07 := jtf_terr_number_list(p_TerrSrvTask_Rec.incident_status_id);
949 lp_Rec.squal_num08 := jtf_terr_number_list(p_TerrSrvTask_Rec.platform_id);
950 lp_Rec.squal_num09 := jtf_terr_number_list(p_TerrSrvTask_Rec.support_site_id);
951 lp_Rec.squal_num10 := jtf_terr_number_list(p_TerrSrvTask_Rec.customer_site_id);
952 lp_Rec.squal_num11 := jtf_terr_number_list(p_TerrSrvTask_Rec.inventory_item_id);
953
954 lp_rec.squal_num20 := jtf_terr_number_list(p_TerrSrvTask_Rec.task_type_id);
955 lp_rec.squal_num21 := jtf_terr_number_list(p_TerrSrvTask_Rec.task_status_id);
956 lp_rec.squal_num22 := jtf_terr_number_list(p_TerrSrvTask_Rec.task_priority_id);
957
958 --arpatel 08/02
959 Qualifier: SR Platform
960 lp_Rec.squal_num12 := jtf_terr_number_list(p_TerrSrvTask_Rec.squal_num12); -- Inventory Item Id
961 lp_Rec.squal_num13 := jtf_terr_number_list(p_TerrSrvTask_Rec.squal_num13); -- Organization Id
962
963 Qualifier: SR Product Category
964 lp_Rec.squal_num14 := jtf_terr_number_list(p_TerrSrvTask_Rec.squal_num14); -- Category Id
965
966 Qualifier: SR Product, Component, Subcomponent
967 lp_Rec.squal_num15 := jtf_terr_number_list(p_TerrSrvTask_Rec.squal_num15); -- Inventory Item Id
971
968 lp_Rec.squal_num16 := jtf_terr_number_list(p_TerrSrvTask_Rec.squal_num16); -- Organization Id
969 lp_Rec.squal_num23 := jtf_terr_number_list(p_TerrSrvTask_Rec.squal_num23); -- Component ID
970 lp_Rec.squal_num24 := jtf_terr_number_list(p_TerrSrvTask_Rec.squal_num24); -- Subcomponent ID
972 Qualifier: SR Group Owner
973 lp_Rec.squal_num17 := jtf_terr_number_list(p_TerrSrvTask_Rec.squal_num17);
974
975 Contract Support Service Item
976 lp_Rec.squal_num18 := jtf_terr_number_list(p_TerrSrvTask_Rec.squal_num18); -- Inventory Item Id
977 lp_Rec.squal_num19 := jtf_terr_number_list(p_TerrSrvTask_Rec.squal_num19); -- Organization Id
978
979 lp_Rec.squal_char01 := jtf_terr_char_360list(p_TerrSrvTask_Rec.country);
980 lp_Rec.squal_char02 := jtf_terr_char_360list(p_TerrSrvTask_Rec.city);
981 lp_Rec.squal_char03 := jtf_terr_char_360list(p_TerrSrvTask_Rec.postal_code);
982 lp_Rec.squal_char04 := jtf_terr_char_360list(p_TerrSrvTask_Rec.state);
983 lp_Rec.squal_char05 := jtf_terr_char_360list(p_TerrSrvTask_Rec.area_code);
984 lp_Rec.squal_char06 := jtf_terr_char_360list(p_TerrSrvTask_Rec.county);
985 lp_Rec.squal_char07 := jtf_terr_char_360list(p_TerrSrvTask_Rec.comp_name_range);
986 lp_Rec.squal_char08 := jtf_terr_char_360list(p_TerrSrvTask_Rec.province);
987 lp_Rec.squal_char09 := jtf_terr_char_360list(p_TerrSrvTask_Rec.problem_code);
988 lp_Rec.squal_char10 := jtf_terr_char_360list(p_TerrSrvTask_Rec.sr_creation_channel);
989
990 --arpatel 08/02
991 VIP Customers
992 lp_Rec.squal_char11 := jtf_terr_char_360list(p_TerrSrvTask_Rec.squal_char11);
993
994 Qualifier: SR Problem Code
995 lp_Rec.squal_char12 := jtf_terr_char_360list(p_TerrSrvTask_Rec.squal_char12);
996
997 Qualifier: SR Customer Contact Preference
998 lp_Rec.squal_char13 := jtf_terr_char_360list(p_TerrSrvTask_Rec.squal_char13);
999
1000 Qualifier: SR Service Contract Coverage
1001 lp_Rec.squal_char21 := jtf_terr_char_360list(p_TerrSrvTask_Rec.squal_char21);
1002
1003 SR Language -JDOCHERT 12/17/01 - bug#2152253
1004 lp_Rec.squal_char20 := jtf_terr_char_360list(p_TerrSrvTask_Rec.squal_char20);
1005 */
1006
1007 JTY_ASSIGN_REALTIME_PUB.process_match (
1008 p_source_id => -1002
1009 ,p_trans_id => -1009
1010 ,p_program_name => 'SERVICE/SERVICE REQUEST AND TASKS PROGRAM'
1011 ,p_mode => 'REAL TIME:RESOURCE'
1012 ,x_return_status => x_return_status
1013 ,x_msg_count => x_msg_count
1014 ,x_msg_data => x_msg_data);
1015
1016 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1017 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1018 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1019 'jtf.plsql.jtf_terr_service_pub.get_winningterrmembers.process_match',
1020 'API JTY_ASSIGN_REALTIME_PUB.process_match has failed');
1021 END IF;
1022 RAISE FND_API.G_EXC_ERROR;
1023 END IF;
1024
1025 -- debug message
1026 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1027 FND_LOG.string(FND_LOG.LEVEL_EVENT,
1028 'jtf.plsql.jtf_terr_service_pub.get_winningterrmembers.process_match',
1029 'Finish calling procedure JTY_ASSIGN_REALTIME_PUB.process_match');
1030 END IF;
1031
1032 JTY_ASSIGN_REALTIME_PUB.process_winners (
1033 p_source_id => -1002
1034 ,p_trans_id => -1009
1035 ,p_program_name => 'SERVICE/SERVICE REQUEST AND TASKS PROGRAM'
1036 ,p_mode => 'REAL TIME:RESOURCE'
1037 ,p_role => p_role
1038 ,p_resource_type => p_resource_type
1039 ,p_plan_start_date => p_plan_start_date
1040 ,p_plan_end_date => p_plan_end_date
1041 ,x_return_status => x_return_status
1042 ,x_msg_count => x_msg_count
1043 ,x_msg_data => x_msg_data
1044 ,x_winners_rec => lx_winners_rec);
1045
1046 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1047 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1048 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1049 'jtf.plsql.jtf_terr_service_pub.get_winningterrmembers.process_winners',
1050 'API JTY_ASSIGN_REALTIME_PUB.process_winners has failed');
1051 END IF;
1052 RAISE FND_API.G_EXC_ERROR;
1053 END IF;
1054
1055 -- debug message
1056 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1057 FND_LOG.string(FND_LOG.LEVEL_EVENT,
1058 'jtf.plsql.jtf_terr_service_pub.get_winningterrmembers.process_winners',
1059 'Finish calling procedure JTY_ASSIGN_REALTIME_PUB.process_winners');
1060 END IF;
1061
1062 /*
1063 jtf_terr_1002_srv_task_dyn.search_terr_rules(
1064 p_rec => lp_rec
1065 , x_rec => lx_rec
1066 , p_role => p_role
1067 , p_resource_type => p_resource_type );
1068 */
1069
1070 l_counter := lx_winners_rec.terr_id.FIRST;
1071
1072 WHILE (l_counter <= lx_winners_rec.terr_id.LAST) LOOP
1073
1074 x_TerrResource_tbl(l_counter).TERR_RSC_ID := lx_winners_rec.terr_rsc_id(l_counter);
1075 x_TerrResource_tbl(l_counter).RESOURCE_ID := lx_winners_rec.resource_id(l_counter);
1076 x_TerrResource_tbl(l_counter).RESOURCE_TYPE := lx_winners_rec.resource_type(l_counter);
1077 x_TerrResource_tbl(l_counter).GROUP_ID := lx_winners_rec.group_id(l_counter);
1078 x_TerrResource_tbl(l_counter).ROLE := lx_winners_rec.role(l_counter);
1079 x_TerrResource_tbl(l_counter).PRIMARY_CONTACT_FLAG := lx_winners_rec.PRIMARY_CONTACT_FLAG(l_counter);
1080 x_TerrResource_tbl(l_counter).FULL_ACCESS_FLAG := lx_winners_rec.FULL_ACCESS_FLAG(l_counter);
1081 x_TerrResource_tbl(l_counter).TERR_ID := lx_winners_rec.terr_id(l_counter);
1082 x_TerrResource_tbl(l_counter).START_DATE := lx_winners_rec.rsc_start_date(l_counter);
1083 x_TerrResource_tbl(l_counter).END_DATE := lx_winners_rec.rsc_end_date(l_counter);
1084 x_TerrResource_tbl(l_counter).ABSOLUTE_RANK := lx_winners_rec.absolute_rank(l_counter);
1085
1086 l_counter := l_counter + 1;
1087
1088 END LOOP;
1089
1090 -- debug message
1091 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1092 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1093 'jtf.plsql.jtf_terr_service_pub.get_winningterrmembers.end',
1094 'End of the procedure jtf_terr_service_pub.get_winningterrmembers');
1095 END IF;
1096
1097 EXCEPTION
1098 WHEN FND_API.G_EXC_ERROR THEN
1099 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1100 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1101 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1102 'jtf.plsql.jtf_terr_service_pub.get_winningterrmembers.g_exc_error',
1103 substr(x_msg_data, 1, 4000));
1104 END IF;
1105
1106 WHEN OTHERS THEN
1107 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1108 x_msg_data := SQLCODE || ' : ' || SQLERRM;
1109 x_msg_count := 1;
1110 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1111 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1112 'jtf.plsql.jtf_terr_service_pub.get_winningterrmembers.other',
1113 substr(x_msg_data, 1, 4000));
1114 END IF;
1115
1116 End Get_WinningTerrMembers;
1117
1118 END JTF_TERR_SERVICE_PUB;