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