[Home] [Help]
PACKAGE BODY: APPS.JTY_TERR_SPARES_PVT
Source
1 PACKAGE BODY JTY_TERR_SPARES_PVT AS
2 /* $Header: jtftsprmgb.pls 120.2 2010/09/21 11:08:43 sseshaiy noship $ */
3 -- Start of Comments
4 -- ---------------------------------------------------
5 -- PACKAGE NAME: JTY_TERR_SPARES_PVT
6 -- PURPOSE
7 --
8 -- Procedures:
9 -- (see below for specification)
10 -- This package covers migration of territory data
11 --
12 -- The various procedures are for
13 -- 1. Territory Type Creation for Spares (add_seeded_territory_types) :
14 -- This procedure will create the
15 -- geographic territory type for use by Spares
16 --
17 -- NOTES
18 --
19 --
20 -- HISTORY
21 -- 03/03/2010 RAJUKUM CREATED
22 --
23 -- End of Comments
24 --
25
26
27
28
29
30 -- This procedure will create the
31 -- territory types for spares use for service usages and for all the
32 -- existing organizations
33
34 Procedure add_seeded_territory_types
35 IS
36 l_migration_complete varchar2(1);
37 l_description varchar2(240);
38 BEGIN
39
40
41 -- sql to check whethe migration script already ran
42 -- on this environment
43
44 BEGIN
45 select 'Y'
46 into l_migration_complete
47 from jtf_terr_types_all
48 where terr_type_id = -9
49 and created_by = 9
50 and rownum < 2;
51 EXCEPTION
52 when FND_API.G_EXC_ERROR then
53 -- Add proper error logging
54 -- dbms_output.put_line('21' || sqlerrm );
55 NULL;
56 when FND_API.G_EXC_UNEXPECTED_ERROR then
57 -- Add proper error logging
58 -- dbms_output.put_line('22' || sqlerrm );
59 NULL;
60 when others then
61 -- Add proper error logging
62 -- dbms_output.put_line('23' || sqlerrm );
63 l_migration_complete := 'N';
64 NULL;
65 END;
66
67 -- If the migration script already executed on this env
68 -- then delete the records.
69
70 if l_migration_complete <> 'Y' then
71
72 BEGIN
73
74 l_description := 'Territory Type defined to create other Territories to be used by Spares Management ' ||
75 'Advanced Return Routing Rules';
76
77 -- Insert territory types for service usages
78 -- and org_id
79
80 insert into jtf_terr_types_all
81 ( TERR_TYPE_ID
82 , LAST_UPDATED_BY
83 , LAST_UPDATE_DATE
84 , CREATED_BY
85 , CREATION_DATE
86 , APPLICATION_SHORT_NAME
87 , NAME
88 , ENABLED_FLAG
89 , DESCRIPTION
90 , START_DATE_ACTIVE
91 , ORG_ID
92 , ORIG_SYSTEM_REFERENCE_ID)
93 select -9
94 , 9
95 , sysdate
96 , 9
97 , sysdate
98 , 'CSP'
99 , 'Spares Management Return Routing'
100 , 'Y'
101 , l_description
102 , sysdate
103 , -3113
104 , -1002
105 from dual;
106
107
108 EXCEPTION
109 when FND_API.G_EXC_ERROR then
110 -- Add proper error logging
111 -- dbms_output.put_line('28' || sqlerrm );
112 NULL;
113 when FND_API.G_EXC_UNEXPECTED_ERROR then
114 -- Add proper error logging
115 -- dbms_output.put_line('29' || sqlerrm );
116 NULL;
117 when others then
118 -- Add proper error logging
119 -- dbms_output.put_line('30' || sqlerrm );
120 NULL;
121 END;
122
123 BEGIN
124 -- For every row in jtf_terr_type_all table
125 -- insert a corresponding row in
126 -- jtf_terr_type_usgs_all table
127
128 insert into jtf_terr_type_usgs_all
129 ( TERR_TYPE_USG_ID
130 , LAST_UPDATED_BY
131 , LAST_UPDATE_DATE
132 , CREATED_BY
133 , CREATION_DATE
134 , TERR_TYPE_ID
135 , SOURCE_ID
136 , ORG_ID
137 )
138 select jtf_terr_type_usgs_s.nextval
139 , 9
140 , sysdate
141 , 9
142 , sysdate
143 , terr_type_id
144 , ORIG_SYSTEM_REFERENCE_ID -- Used this column to temp store source_id
145 , org_id
146 from jtf_terr_types_all
147 where ORIG_SYSTEM_REFERENCE_ID is not null
148 and terr_type_id = -9
149 and CREATED_BY = 9;
150
151 EXCEPTION
152 when FND_API.G_EXC_ERROR then
153 -- Add proper error logging
154 -- dbms_output.put_line('31' || sqlerrm );
155 NULL;
156 when FND_API.G_EXC_UNEXPECTED_ERROR then
157 -- Add proper error logging
158 -- dbms_output.put_line('32' || sqlerrm );
159 NULL;
160 when others then
161 -- Add proper error logging
162 -- dbms_output.put_line('33' || sqlerrm );
163 NULL;
164 END;
165
166 BEGIN
167
168 -- populate jtf_type_qtype_usgs_all table with
169 -- qual_type_usg_id and terr_type_id
170 -- intersection table for jtf_qual_type_usgs_all and
171 -- jtf_terr_types_all tables
172
173 insert into jtf_type_qtype_usgs_all
174 ( TYPE_QTYPE_USG_ID
175 , LAST_UPDATED_BY
176 , LAST_UPDATE_DATE
177 , CREATED_BY
178 , CREATION_DATE
179 , last_update_login
180 , TERR_TYPE_ID
181 , QUAL_TYPE_USG_ID
182 , ORG_ID)
183 select jtf_type_qtype_usgs_s.nextval
184 , 9
185 , sysdate
186 , 9
187 , sysdate
188 , 9
189 , jttu.terr_type_id
190 , jqtu.qual_type_usg_id
191 , jttu.org_id
192 from jtf_terr_type_usgs_all jttu
193 , jtf_qual_type_usgs_all jqtu
194 where jqtu.source_id = jttu.source_id
195 and jttu.source_id = -1002
196 and jttu.terr_type_id = -9
197 and jqtu.qual_type_usg_id <> -1005 -- Exclude service account transaction type
198 and jttu.created_by = 9;
199
200 EXCEPTION
201 when FND_API.G_EXC_ERROR then
202 -- Add proper error logging
203 -- dbms_output.put_line('34' || sqlerrm );
204 NULL;
205 when FND_API.G_EXC_UNEXPECTED_ERROR then
206 -- Add proper error logging
207 -- dbms_output.put_line('35' || sqlerrm );
208 NULL;
209 when others then
210 -- Add proper error logging
211 -- dbms_output.put_line('36' || sqlerrm );
212 NULL;
213 END;
214
215 BEGIN
216
217 -- populate jtf_terr_type_qual_all table with
218 -- QUAL_USG_ID and terr_type_id
219 -- intersection table for jtf_qual_usgs_all and
220 -- jtf_terr_types_all tables
221 insert into jtf_terr_type_qual_all
222 (TERR_TYPE_QUAL_ID
223 , LAST_UPDATED_BY
224 , LAST_UPDATE_DATE
225 , CREATED_BY
226 , CREATION_DATE
227 , QUAL_USG_ID
228 , TERR_TYPE_ID
229 , ORG_ID)
230 select jtf_terr_type_qual_s.nextval
231 , 9
232 , sysdate
233 , 9
234 , sysdate
235 , jqu.QUAL_USG_ID
236 , jttu.terr_type_id
237 , jttu.org_id
238 from jtf_terr_type_usgs_all jttu
239 , jtf_qual_type_usgs_all jqtu
240 , jtf_qual_usgs_all jqu
241 where jttu.created_by = 9
242 and jttu.terr_type_id = -9
243 and jttu.source_id = -1002
244 and jqu.QUAL_TYPE_USG_ID = jqtu.qual_type_usg_id
245 and jqtu.source_id = jttu.source_id
246 and jttu.org_id = jqu.org_id
247 and jqu.hierarchy_type = 'GEOGRAPHY'
248 and jttu.terr_type_id <> -1 ;-- For NA territories this table is not populated
249 --and jqu.ENABLED_FLAG = 'Y';
250
251 EXCEPTION
252 when FND_API.G_EXC_ERROR then
253 -- Add proper error logging
254 -- dbms_output.put_line('37' || sqlerrm );
255 NULL;
256 when FND_API.G_EXC_UNEXPECTED_ERROR then
257 -- Add proper error logging
258 -- dbms_output.put_line('38' || sqlerrm );
259 NULL;
260 when others then
261 -- Add proper error logging
262 -- dbms_output.put_line('39' || sqlerrm );
263 NULL;
264 END;
265
266 commit;
267 end if;
268
269 EXCEPTION
270 when FND_API.G_EXC_ERROR then
271 -- Add proper error logging
272 -- dbms_output.put_line('13' || sqlerrm );
273 NULL;
274 when FND_API.G_EXC_UNEXPECTED_ERROR then
275 -- Add proper error logging
276 -- dbms_output.put_line('14' || sqlerrm );
277 NULL;
278 when others then
279 -- Add proper error logging
280 -- dbms_output.put_line('15' || sqlerrm );
281 NULL;
282
283 END add_seeded_territory_types;
284
285
286 -- Main procedure that will run all the
287 -- necessary procedures for creations R12 seeded data for Spares.
288
289 Procedure run_r12_seeded_terr_for_spares
290 IS
291
292 BEGIN
293
294
295 add_seeded_territory_types;
296
297
298 commit;
299
300 EXCEPTION
301 when FND_API.G_EXC_ERROR then
302 -- Add proper error logging
303 -- dbms_output.put_line('16' || sqlerrm );
304 NULL;
305 when FND_API.G_EXC_UNEXPECTED_ERROR then
306 -- Add proper error logging
307 -- dbms_output.put_line('17' || sqlerrm );
308 NULL;
309 when others then
310 -- Add proper error logging
311 -- dbms_output.put_line('18' || sqlerrm );
312 NULL;
313 END run_r12_seeded_terr_for_spares;
314
315 -- ***************************************************
316 -- API Specifications
317 -- ***************************************************
318 -- api name : Process_match_terr_spares
319 -- type : public.
320 -- function : Called by spares APIs
321 -- pre-reqs : Territories needs to be setup first
322 -- notes :
323 --
324 PROCEDURE process_match_terr_spares
325 ( p_api_version_number IN number,
326 p_init_msg_list IN varchar2 := fnd_api.g_false,
327 p_TerrServReq_Rec IN JTF_TERRITORY_PUB.JTF_Serv_Req_rec_type,
328 p_Resource_Type IN varchar2,
329 p_Role IN varchar2,
330 p_plan_start_date IN DATE DEFAULT NULL,
331 p_plan_end_date IN DATE DEFAULT NULL,
332 x_return_status OUT NOCOPY varchar2,
333 x_msg_count OUT NOCOPY number,
334 X_msg_data OUT NOCOPY varchar2
335 )
336 AS
337 l_api_name CONSTANT VARCHAR2(30) := 'process_match_terr_spares';
338 l_api_version_number CONSTANT NUMBER := 1.0;
339
340 l_Counter NUMBER;
341
342 lx_winners_rec JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type;
343 BEGIN
344
345 -- debug message
346 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
347 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
348 'jtf.plsql.jtf_terr_service_pub.process_match_terr_spares.start',
349 'Start of the procedure jtf_terr_service_pub.process_match_terr_spares');
350 END IF;
351
352 -- Standard call to check for call compatibility.
353 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
354 p_api_version_number,
355 l_api_name,
356 G_PKG_NAME)
357 THEN
358 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
359 END IF;
360
361 -- Initialize message list if p_init_msg_list is set to TRUE.
362 IF FND_API.to_Boolean( p_init_msg_list )
363 THEN
364 FND_MSG_PUB.initialize;
365 END IF;
366
367 --
368 -- API body
369 x_return_status := FND_API.G_RET_STS_SUCCESS;
370
371 -- debug message
372 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
373 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
374 'jtf.plsql.JTY_TERR_SPARES_PVT.process_match_terr_spares.parameters',
375 'Country : ' || p_TerrServReq_Rec.COUNTRY || ' City : ' || p_TerrServReq_Rec.CITY ||
376 ' Postal Code : ' || p_TerrServReq_Rec.POSTAL_CODE || ' State : ' || p_TerrServReq_Rec.STATE ||
377 ' Area Code : ' || p_TerrServReq_Rec.AREA_CODE || ' County : ' || p_TerrServReq_Rec.COUNTY ||
378 ' Company Name Range : ' || p_TerrServReq_Rec.COMP_NAME_RANGE || ' Province : ' || p_TerrServReq_Rec.PROVINCE ||
379 ' Problem Code : ' || p_TerrServReq_Rec.PROBLEM_CODE ||
380 ' sr creation channel : ' || p_TerrServReq_Rec.SR_CREATION_CHANNEL ||
381 ' vip customer : ' || p_TerrServReq_Rec.squal_char11 || ' sr problem code : ' || p_TerrServReq_Rec.squal_char12 ||
382 ' sr customer contact preference : ' || p_TerrServReq_Rec.squal_char13 ||
383 ' sr service contact coverage : ' || p_TerrServReq_Rec.squal_char21 ||
384 ' sr language : ' || p_TerrServReq_Rec.squal_char20 ||
385 ' Number of Employees : ' || p_TerrServReq_Rec.NUM_OF_EMPLOYEES || ' Party ID : ' || p_TerrServReq_Rec.PARTY_ID ||
386 ' Party Site ID : ' || p_TerrServReq_Rec.PARTY_SITE_ID ||
387 ' Incident Type ID : ' || p_TerrServReq_Rec.INCIDENT_TYPE_ID ||
388 ' Incident severity ID : ' || p_TerrServReq_Rec.INCIDENT_SEVERITY_ID ||
389 ' Incident urgency ID : ' || p_TerrServReq_Rec.INCIDENT_URGENCY_ID ||
390 ' Incident status ID : ' || p_TerrServReq_Rec.INCIDENT_STATUS_ID ||
391 ' platform ID : ' || p_TerrServReq_Rec.PLATFORM_ID || ' Support Site ID : ' || p_TerrServReq_Rec.SUPPORT_SITE_ID ||
392 ' Cust Site ID : ' || p_TerrServReq_Rec.CUSTOMER_SITE_ID ||
393 ' Inventory Item ID : ' || p_TerrServReq_Rec.INVENTORY_ITEM_ID ||
394 ' SR Platform Inventory Item ID : ' || p_TerrServReq_Rec.SQUAL_NUM12 ||
395 ' SR Platform Org ID : ' || p_TerrServReq_Rec.SQUAL_NUM13 ||
396 ' SR Product Category ID : ' || p_TerrServReq_Rec.SQUAL_NUM14 ||
397 ' PCS Inventory Item ID : ' || p_TerrServReq_Rec.SQUAL_NUM15 ||
398 ' PCS Org ID : ' || p_TerrServReq_Rec.SQUAL_NUM16 ||
399 ' PCS Component ID : ' || p_TerrServReq_Rec.SQUAL_NUM23 ||
400 ' PCS Subcomponent ID : ' || p_TerrServReq_Rec.SQUAL_NUM24 ||
401 ' SR Group Owner ID : ' || p_TerrServReq_Rec.SQUAL_NUM17 ||
402 ' SSI Inventory Item ID : ' || p_TerrServReq_Rec.SQUAL_NUM18 ||
403 ' SSI Org ID : ' || p_TerrServReq_Rec.SQUAL_NUM19||
404 ' p_plan_start_date: ' || p_plan_start_date ||
405 ' p_plan_end_date: ' || p_plan_end_date||
406 ' Day OF Week : ' || p_TerrServReq_Rec.DAY_OF_WEEK ||
407 ' Time OF Day : ' || p_TerrServReq_Rec.TIME_OF_DAY);
408
409 END IF;
410
411 /* delete and insert all the attributes into the trans table as name - value pair */
412 DELETE jty_terr_nvp_trans_gt;
413 INSERT INTO jty_terr_nvp_trans_gt (
414 attribute_name
415 ,num_value
416 ,char_value
417 ,date_value )
418 ( SELECT 'COUNTRY' attribute_name
419 ,null num_value
420 ,p_TerrServReq_Rec.COUNTRY char_value
421 ,null date_value
422 FROM DUAL
423 UNION ALL
424 SELECT 'CITY' attribute_name
425 ,null num_value
426 ,p_TerrServReq_Rec.CITY char_value
427 ,null date_value
428 FROM DUAL
429 UNION ALL
430 SELECT 'POSTAL_CODE' attribute_name
431 ,null num_value
432 ,p_TerrServReq_Rec.POSTAL_CODE char_value
433 ,null date_value
434 FROM DUAL
435 UNION ALL
436 SELECT 'STATE' attribute_name
437 ,null num_value
438 ,p_TerrServReq_Rec.STATE char_value
439 ,null date_value
440 FROM DUAL
441 UNION ALL
442 SELECT 'AREA_CODE' attribute_name
443 ,null num_value
444 ,p_TerrServReq_Rec.AREA_CODE char_value
445 ,null date_value
446 FROM DUAL
447 UNION ALL
448 SELECT 'COUNTY' attribute_name
449 ,null num_value
450 ,p_TerrServReq_Rec.COUNTY char_value
451 ,null date_value
452 FROM DUAL
453 UNION ALL
454 SELECT 'COMP_NAME_RANGE' attribute_name
455 ,null num_value
456 ,p_TerrServReq_Rec.COMP_NAME_RANGE char_value
457 ,null date_value
458 FROM DUAL
459 UNION ALL
460 SELECT 'PROVINCE' attribute_name
461 ,null num_value
462 ,p_TerrServReq_Rec.PROVINCE char_value
463 ,null date_value
464 FROM DUAL
465 UNION ALL
466 SELECT 'PROBLEM_CODE' attribute_name
467 ,null num_value
468 ,p_TerrServReq_Rec.PROBLEM_CODE char_value
469 ,null date_value
470 FROM DUAL
471 UNION ALL
472 SELECT 'SR_CREATION_CHANNEL' attribute_name
473 ,null num_value
474 ,p_TerrServReq_Rec.SR_CREATION_CHANNEL char_value
475 ,null date_value
476 FROM DUAL
477 UNION ALL
478 SELECT 'VIP_CUSTOMER' attribute_name
479 ,null num_value
480 ,p_TerrServReq_Rec.squal_char11 char_value
481 ,null date_value
482 FROM DUAL
483 UNION ALL
484 SELECT 'SR_PROBLEM_CODE' attribute_name
485 ,null num_value
486 ,p_TerrServReq_Rec.squal_char12 char_value
487 ,null date_value
488 FROM DUAL
489 UNION ALL
490 SELECT 'SR_CUST_CNTCT_PREF' attribute_name
491 ,null num_value
492 ,p_TerrServReq_Rec.squal_char13 char_value
493 ,null date_value
494 FROM DUAL
495 UNION ALL
496 SELECT 'SR_SRVC_CNTCT_CVG' attribute_name
497 ,null num_value
498 ,p_TerrServReq_Rec.squal_char21 char_value
499 ,null date_value
500 FROM DUAL
501 UNION ALL
502 SELECT 'SR_LANGUAGE' attribute_name
503 ,null num_value
504 ,p_TerrServReq_Rec.squal_char20 char_value
505 ,null date_value
506 FROM DUAL
507 UNION ALL
508 SELECT 'PARTY_ID' attribute_name
509 ,p_TerrServReq_Rec.PARTY_ID num_value
510 ,null char_value
511 ,null date_value
512 FROM DUAL
513 UNION ALL
514 SELECT 'PARTY_SITE_ID' attribute_name
515 ,p_TerrServReq_Rec.PARTY_SITE_ID num_value
516 ,null char_value
517 ,null date_value
518 FROM DUAL
519 UNION ALL
520 SELECT 'NUM_OF_EMPLOYEES' attribute_name
521 ,p_TerrServReq_Rec.NUM_OF_EMPLOYEES num_value
522 ,null char_value
523 ,null date_value
524 FROM DUAL
525 UNION ALL
526 SELECT 'INCIDENT_TYPE_ID' attribute_name
527 ,p_TerrServReq_Rec.INCIDENT_TYPE_ID num_value
528 ,null char_value
529 ,null date_value
530 FROM DUAL
531 UNION ALL
532 SELECT 'INCIDENT_SEVERITY_ID' attribute_name
533 ,p_TerrServReq_Rec.INCIDENT_SEVERITY_ID num_value
534 ,null char_value
535 ,null date_value
536 FROM DUAL
537 UNION ALL
538 SELECT 'INCIDENT_URGENCY_ID' attribute_name
539 ,p_TerrServReq_Rec.INCIDENT_URGENCY_ID num_value
540 ,null char_value
541 ,null date_value
542 FROM DUAL
543 UNION ALL
544 SELECT 'INCIDENT_STATUS_ID' attribute_name
545 ,p_TerrServReq_Rec.INCIDENT_STATUS_ID num_value
546 ,null char_value
547 ,null date_value
548 FROM DUAL
549 UNION ALL
550 SELECT 'PLATFORM_ID' attribute_name
551 ,p_TerrServReq_Rec.PLATFORM_ID num_value
552 ,null char_value
553 ,null date_value
554 FROM DUAL
555 UNION ALL
556 SELECT 'SUPPORT_SITE_ID' attribute_name
557 ,p_TerrServReq_Rec.SUPPORT_SITE_ID num_value
558 ,null char_value
559 ,null date_value
560 FROM DUAL
561 UNION ALL
562 SELECT 'CUSTOMER_SITE_ID' attribute_name
563 ,p_TerrServReq_Rec.CUSTOMER_SITE_ID num_value
564 ,null char_value
565 ,null date_value
566 FROM DUAL
567 UNION ALL
568 SELECT 'INVENTORY_ITEM_ID' attribute_name
569 ,p_TerrServReq_Rec.INVENTORY_ITEM_ID num_value
570 ,null char_value
571 ,null date_value
572 FROM DUAL
573 UNION ALL
574 SELECT 'SRP_INVENTORY_ITEM_ID' attribute_name
575 ,p_TerrServReq_Rec.SQUAL_NUM12 num_value
576 ,null char_value
577 ,null date_value
578 FROM DUAL
579 UNION ALL
580 SELECT 'SRP_ORG_ID' attribute_name
581 ,p_TerrServReq_Rec.SQUAL_NUM13 num_value
582 ,null char_value
583 ,null date_value
584 FROM DUAL
585 UNION ALL
586 SELECT 'SPC_CATEGORY_ID' attribute_name
587 ,p_TerrServReq_Rec.SQUAL_NUM14 num_value
588 ,null char_value
589 ,null date_value
590 FROM DUAL
591 UNION ALL
592 SELECT 'PCS_INVENTORY_ITEM_ID' attribute_name
593 ,p_TerrServReq_Rec.SQUAL_NUM15 num_value
594 ,null char_value
595 ,null date_value
596 FROM DUAL
597 UNION ALL
598 SELECT 'PCS_ORG_ID' attribute_name
599 ,p_TerrServReq_Rec.SQUAL_NUM16 num_value
600 ,null char_value
601 ,null date_value
602 FROM DUAL
603 UNION ALL
604 SELECT 'PCS_COMPONENT_ID' attribute_name
605 ,p_TerrServReq_Rec.SQUAL_NUM23 num_value
606 ,null char_value
607 ,null date_value
608 FROM DUAL
609 UNION ALL
610 SELECT 'PCS_SUBCOMPONENT_ID' attribute_name
611 ,p_TerrServReq_Rec.SQUAL_NUM24 num_value
612 ,null char_value
613 ,null date_value
614 FROM DUAL
615 UNION ALL
616 SELECT 'SR_GROUP_OWNER_ID' attribute_name
617 ,p_TerrServReq_Rec.SQUAL_NUM17 num_value
618 ,null char_value
619 ,null date_value
620 FROM DUAL
621 UNION ALL
622 SELECT 'SSI_INVENTORY_ITEM_ID' attribute_name
623 ,p_TerrServReq_Rec.SQUAL_NUM18 num_value
624 ,null char_value
625 ,null date_value
626 FROM DUAL
627 UNION ALL
628 SELECT 'SSI_ORG_ID' attribute_name
629 ,p_TerrServReq_Rec.SQUAL_NUM19 num_value
630 ,null char_value
631 ,null date_value
632 FROM DUAL
633 UNION ALL
634 SELECT 'DAY_OF_WEEK' attribute_name
635 ,null num_value
636 --,p_TerrServReq_Rec.DAY_OF_WEEK char_value
637 , DECODE(p_TerrServReq_Rec.DAY_OF_WEEK,FND_API.G_MISS_CHAR,null,
638 p_TerrServReq_Rec.DAY_OF_WEEK) char_value
639 ,null date_value
640 FROM DUAL
641 UNION ALL
642 SELECT 'TIME_OF_DAY' attribute_name
643 , null num_value
644 --, p_TerrServReq_Rec.TIME_OF_DAY char_value
645 , DECODE(p_TerrServReq_Rec.TIME_OF_DAY,FND_API.G_MISS_CHAR,null,
646 p_TerrServReq_Rec.TIME_OF_DAY) char_value
647 ,null date_value
648 FROM DUAL
649
650 );
651
652
653 JTY_ASSIGN_REALTIME_PUB.process_match (
654 p_source_id => -1002
655 ,p_trans_id => -1005
656 ,p_program_name => 'SERVICE/SERVICE REQUEST PROGRAM'
657 ,p_mode => 'REAL TIME:RESOURCE'
658 ,x_return_status => x_return_status
659 ,x_msg_count => x_msg_count
660 ,x_msg_data => x_msg_data);
661
662 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
663 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
664 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
665 'jtf.plsql.JTY_TERR_SPARES_PVT.process_match_terr_spares.process_match',
666 'API JTY_ASSIGN_REALTIME_PUB.process_match has failed');
667 END IF;
668 RAISE FND_API.G_EXC_ERROR;
669 END IF;
670
671 -- debug message
672 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
673 FND_LOG.string(FND_LOG.LEVEL_EVENT,
674 'jtf.plsql.jtf_terr_service_pub.process_match_terr_spares.process_match',
675 'Finish calling procedure JTY_ASSIGN_REALTIME_PUB.process_match');
676 END IF;
677
678
679
680 EXCEPTION
681
682 WHEN OTHERS THEN
683 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
684 x_msg_data := SQLCODE || ' : ' || SQLERRM;
685 x_msg_count := 1;
686 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
687 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
688 'jtf.plsql.JTY_TERR_SPARES_PVT.process_match_terr_spares.other',
689 substr(x_msg_data, 1, 4000));
690 END IF;
691 END process_match_terr_spares;
692
693 END JTY_TERR_SPARES_PVT;