1 PACKAGE JTF_ASSIGN_PUB AS
2 /* $Header: jtfamprs.pls 120.2.12000000.2 2007/07/04 12:53:02 shdwived ship $ */
3
4
5
6 -- *******************************************************************************
7
8 -- Start of comments
9
10 -- Functions : These functions are to get the FND_API
11 -- default values.
12
13 -- End of comments
14
15 -- *******************************************************************************
16
17
18 FUNCTION am_miss_num RETURN NUMBER;
19
20 FUNCTION am_miss_char RETURN VARCHAR2;
21
22 FUNCTION am_miss_date RETURN DATE;
23
24 FUNCTION am_false RETURN VARCHAR2;
25
26 FUNCTION am_true RETURN VARCHAR2;
27
28 FUNCTION am_valid_level_full RETURN VARCHAR2;
29
30 FUNCTION resource_type_change(p_res_type VARCHAR2) RETURN VARCHAR2;
31
32
33
34 -- ********************************************************************************
35
36 -- Start of Comments
37
38 -- Package Name : JTF_ASSIGN_PUB
39 -- Purpose : Joint Task Force Core Foundation Assignment Manager
40 -- Public APIs. This package is for finding the
41 -- a resource based on the customer preferences
42 -- or territory preferences and the availability of
43 -- the resource in the specified time frame.
44 -- Procedures : (See below for specification)
45 -- Notes : This package is publicly available for use
46 -- History : 11/02/99 ** VVUYYURU ** Vijay Vuyyuru ** created
47 --
48
49 -- End of Comments
50
51 -- *******************************************************************************
52 -- This record type and global variable will be used for Complex Work Assignments.
53 -- This will be used to populate the multiple available slots for a resource
54 -- The unique keys will be resource_id, resource_type, start_date and end_date
55 TYPE Resource_avail_type IS RECORD
56 (
57 RESOURCE_ID NUMBER := JTF_ASSIGN_PUB.AM_MISS_NUM,
58 RESOURCE_TYPE VARCHAR2(30) := JTF_ASSIGN_PUB.AM_MISS_CHAR,
59 START_DATE DATE := JTF_ASSIGN_PUB.AM_MISS_DATE,
60 END_DATE DATE := JTF_ASSIGN_PUB.AM_MISS_DATE,
61 SHIFT_CONSTRUCT_ID NUMBER := JTF_ASSIGN_PUB.AM_MISS_NUM);
62
63 TYPE Avail_tbl_type IS TABLE OF Resource_avail_type
64 INDEX BY BINARY_INTEGER;
65
66 g_resource_avail Avail_tbl_type;
67
68 -- Defining this function (repuri 02/04) for 'Complex Work Assignment' Enhancement
69 -- To access the above global variable 'g_resource_avail' in Forms.
70
71 --FUNCTION get_g_resource_avail RETURN Avail_tbl_type;
72
73 TYPE AssignResources_rec_type IS RECORD
74 (
75 TERR_RSC_ID NUMBER := JTF_ASSIGN_PUB.AM_MISS_NUM,
76 RESOURCE_ID NUMBER := JTF_ASSIGN_PUB.AM_MISS_NUM,
77 RESOURCE_TYPE VARCHAR2(30) := JTF_ASSIGN_PUB.AM_MISS_CHAR,
78 ROLE VARCHAR2(30) := JTF_ASSIGN_PUB.AM_MISS_CHAR,
79 START_DATE DATE := JTF_ASSIGN_PUB.AM_MISS_DATE,
80 END_DATE DATE := JTF_ASSIGN_PUB.AM_MISS_DATE,
81 SHIFT_CONSTRUCT_ID NUMBER := JTF_ASSIGN_PUB.AM_MISS_NUM,
82 TERR_ID NUMBER := JTF_ASSIGN_PUB.AM_MISS_NUM,
83 TERR_NAME VARCHAR2(240):= JTF_ASSIGN_PUB.AM_MISS_CHAR,
84 TERR_RANK NUMBER := JTF_ASSIGN_PUB.AM_MISS_NUM,
85 TRAVEL_TIME NUMBER := JTF_ASSIGN_PUB.AM_MISS_NUM,
86 TRAVEL_UOM VARCHAR2(10) := JTF_ASSIGN_PUB.AM_MISS_CHAR,
87 PREFERENCE_TYPE VARCHAR2(05) := JTF_ASSIGN_PUB.AM_MISS_CHAR,
88 PRIMARY_CONTACT_FLAG VARCHAR2(01) := JTF_ASSIGN_PUB.AM_MISS_CHAR,
89 FULL_ACCESS_FLAG VARCHAR2(01) := JTF_ASSIGN_PUB.AM_MISS_CHAR,
90 GROUP_ID NUMBER := JTF_ASSIGN_PUB.AM_MISS_NUM,
91 LOCATION VARCHAR2(60) := JTF_ASSIGN_PUB.AM_MISS_CHAR,
92 TRANS_OBJECT_ID NUMBER := JTF_ASSIGN_PUB.AM_MISS_NUM,
93 RESOURCE_SOURCE VARCHAR2(30) := NULL,
94 SOURCE_START_DATE DATE := JTF_ASSIGN_PUB.AM_MISS_DATE,
95 SOURCE_END_DATE DATE := JTF_ASSIGN_PUB.AM_MISS_DATE,
96 SUPPORT_SITE_ID NUMBER := NULL,
97 SUPPORT_SITE_NAME VARCHAR2(150):= NULL,
98 WEB_AVAILABILITY_FLAG VARCHAR2(01) := NULL,
99 SKILL_LEVEL NUMBER := NULL,
100 SKILL_NAME VARCHAR2(60) := NULL,
101 PRIMARY_FLAG VARCHAR2(10) := NULL
102 );
103
104 TYPE AssignResources_tbl_type IS TABLE OF AssignResources_rec_type
105 INDEX BY BINARY_INTEGER;
106
107
108
109 TYPE JTF_Serv_Req_rec_type IS RECORD
110 (
111 SERVICE_REQUEST_ID NUMBER,
112 PARTY_ID NUMBER,
113 COUNTRY VARCHAR2(60),
114 PARTY_SITE_ID NUMBER,
115 CITY VARCHAR2(60),
116 POSTAL_CODE VARCHAR2(60),
117 STATE VARCHAR2(60),
118 AREA_CODE VARCHAR2(10),
119 COUNTY VARCHAR2(60),
120 COMP_NAME_RANGE VARCHAR2(360),
121 PROVINCE VARCHAR2(60),
122 NUM_OF_EMPLOYEES NUMBER,
123 INCIDENT_TYPE_ID NUMBER,
124 INCIDENT_SEVERITY_ID NUMBER,
125 INCIDENT_URGENCY_ID NUMBER,
126 PROBLEM_CODE VARCHAR2(60),
127 INCIDENT_STATUS_ID NUMBER,
128 PLATFORM_ID NUMBER,
129 SUPPORT_SITE_ID NUMBER,
130 CUSTOMER_SITE_ID NUMBER,
131 SR_CREATION_CHANNEL VARCHAR2(150),
132 INVENTORY_ITEM_ID NUMBER,
133 ATTRIBUTE1 VARCHAR2(150),
134 ATTRIBUTE2 VARCHAR2(150),
135 ATTRIBUTE3 VARCHAR2(150),
136 ATTRIBUTE4 VARCHAR2(150),
137 ATTRIBUTE5 VARCHAR2(150),
138 ATTRIBUTE6 VARCHAR2(150),
139 ATTRIBUTE7 VARCHAR2(150),
140 ATTRIBUTE8 VARCHAR2(150),
141 ATTRIBUTE9 VARCHAR2(150),
142 ATTRIBUTE10 VARCHAR2(150),
143 ATTRIBUTE11 VARCHAR2(150),
144 ATTRIBUTE12 VARCHAR2(150),
145 ATTRIBUTE13 VARCHAR2(150),
146 ATTRIBUTE14 VARCHAR2(150),
147 ATTRIBUTE15 VARCHAR2(150),
148 ORGANIZATION_ID NUMBER,
149 SQUAL_NUM12 NUMBER, --INVENTORY ITEM ID / SR PLATFORM
150 SQUAL_NUM13 NUMBER, --ORGANIZATION ID / SR PLATFORM
151 SQUAL_NUM14 NUMBER, --CATEGORY ID / SR PRODUCT
152 SQUAL_NUM15 NUMBER, --INVENTORY ITEM ID / SR PRODUCT
153 SQUAL_NUM16 NUMBER, --ORGANIZATION ID / SR PRODUCT
154 SQUAL_NUM17 NUMBER, --SR GROUP OWNER
155 SQUAL_NUM18 NUMBER, --INVENTORY ITEM ID / CONTRACT SUPPORT SERVICE ITEM
156 SQUAL_NUM19 NUMBER, --ORGANIZATION ID / CONTRACT SUPPORT SERVICE ITEM
157 SQUAL_NUM30 NUMBER, --SR LANGUAGE ... should use squal_char20 instead
158 SQUAL_CHAR11 VARCHAR2(360), --VIP CUSTOMERS
159 SQUAL_CHAR12 VARCHAR2(360), --SR PROBLEM CODE
160 SQUAL_CHAR13 VARCHAR2(360), --SR CUSTOMER CONTACT PREFERENCE
161 SQUAL_CHAR20 VARCHAR2(360), --SR LANGUAGE ID for TERR REQ
162 SQUAL_CHAR21 VARCHAR2(360), --SR Service Contract Coverage
163 ITEM_COMPONENT NUMBER, -- Added by SBARAT on 10/01/2005 for Enh 4112155
164 ITEM_SUBCOMPONENT NUMBER -- Added by SBARAT on 10/01/2005 for Enh 4112155
165 );
166
167 pkg_sr_rec JTF_Serv_Req_rec_type;
168
169
170
171 TYPE JTF_Srv_Task_rec_type IS RECORD
172 (
173 TASK_ID NUMBER,
174 SERVICE_REQUEST_ID NUMBER,
175 PARTY_ID NUMBER,
176 COUNTRY VARCHAR2(60),
177 PARTY_SITE_ID NUMBER,
178 CITY VARCHAR2(60),
179 POSTAL_CODE VARCHAR2(60),
180 STATE VARCHAR2(60),
181 AREA_CODE VARCHAR2(10),
182 COUNTY VARCHAR2(60),
183 COMP_NAME_RANGE VARCHAR2(360),
184 PROVINCE VARCHAR2(60),
185 NUM_OF_EMPLOYEES NUMBER,
186 TASK_TYPE_ID NUMBER,
187 TASK_STATUS_ID NUMBER,
188 TASK_PRIORITY_ID NUMBER,
189 INCIDENT_TYPE_ID NUMBER,
190 INCIDENT_SEVERITY_ID NUMBER,
191 INCIDENT_URGENCY_ID NUMBER,
192 PROBLEM_CODE VARCHAR2(60),
193 INCIDENT_STATUS_ID NUMBER,
194 PLATFORM_ID NUMBER,
195 SUPPORT_SITE_ID NUMBER,
196 CUSTOMER_SITE_ID NUMBER,
197 SR_CREATION_CHANNEL VARCHAR2(150),
198 INVENTORY_ITEM_ID NUMBER,
199 ATTRIBUTE1 VARCHAR2(150),
200 ATTRIBUTE2 VARCHAR2(150),
201 ATTRIBUTE3 VARCHAR2(150),
202 ATTRIBUTE4 VARCHAR2(150),
203 ATTRIBUTE5 VARCHAR2(150),
204 ATTRIBUTE6 VARCHAR2(150),
205 ATTRIBUTE7 VARCHAR2(150),
206 ATTRIBUTE8 VARCHAR2(150),
207 ATTRIBUTE9 VARCHAR2(150),
208 ATTRIBUTE10 VARCHAR2(150),
209 ATTRIBUTE11 VARCHAR2(150),
210 ATTRIBUTE12 VARCHAR2(150),
211 ATTRIBUTE13 VARCHAR2(150),
212 ATTRIBUTE14 VARCHAR2(150),
213 ATTRIBUTE15 VARCHAR2(150),
214 ORGANIZATION_ID NUMBER,
215 SQUAL_NUM12 NUMBER, --INVENTORY ITEM ID / SR PLATFORM
216 SQUAL_NUM13 NUMBER, --ORGANIZATION ID / SR PLATFORM
217 SQUAL_NUM14 NUMBER, --CATEGORY ID / SR PRODUCT
218 SQUAL_NUM15 NUMBER, --INVENTORY ITEM ID / SR PRODUCT
219 SQUAL_NUM16 NUMBER, --ORGANIZATION ID / SR PRODUCT
220 SQUAL_NUM17 NUMBER, --SR GROUP OWNER
221 SQUAL_NUM18 NUMBER, --INVENTORY ITEM ID / CONTRACT SUPPORT SERVICE ITEM
222 SQUAL_NUM19 NUMBER, --ORGANIZATION ID / CONTRACT SUPPORT SERVICE ITEM
223 SQUAL_NUM30 NUMBER, --SR LANGUAGE ... should use squal_char20 instead
224 SQUAL_CHAR11 VARCHAR2(360), --VIP CUSTOMERS
225 SQUAL_CHAR12 VARCHAR2(360), --SR PROBLEM CODE
226 SQUAL_CHAR13 VARCHAR2(360), --SR CUSTOMER CONTACT PREFERENCE
227 SQUAL_CHAR20 VARCHAR2(360), --SR LANGUAGE ID for TERR REQ
228 SQUAL_CHAR21 VARCHAR2(360), --SR Service Contract Coverage
229 ITEM_COMPONENT NUMBER, -- Added by SBARAT on 10/01/2005 for Enh 4112155
230 ITEM_SUBCOMPONENT NUMBER -- Added by SBARAT on 10/01/2005 for Enh 4112155
231 );
232
233 pkg_sr_task_rec JTF_Srv_Task_rec_type;
234
235 /********** Added by SBARAT on 01/11/2004 for Enh-3919046 *********/
236
237 TYPE JTF_DR_rec_type IS RECORD
238 (
239 TASK_ID NUMBER,
240 SERVICE_REQUEST_ID NUMBER,
241 PARTY_ID NUMBER,
242 COUNTRY VARCHAR2(60),
243 PARTY_SITE_ID NUMBER,
244 CITY VARCHAR2(60),
245 POSTAL_CODE VARCHAR2(60),
246 STATE VARCHAR2(60),
247 AREA_CODE VARCHAR2(10),
248 COUNTY VARCHAR2(60),
249 COMP_NAME_RANGE VARCHAR2(360),
250 PROVINCE VARCHAR2(60),
251 NUM_OF_EMPLOYEES NUMBER,
252 TASK_TYPE_ID NUMBER,
253 TASK_STATUS_ID NUMBER,
254 TASK_PRIORITY_ID NUMBER,
255 INCIDENT_TYPE_ID NUMBER,
256 INCIDENT_SEVERITY_ID NUMBER,
257 INCIDENT_URGENCY_ID NUMBER,
258 PROBLEM_CODE VARCHAR2(60),
259 INCIDENT_STATUS_ID NUMBER,
260 PLATFORM_ID NUMBER,
261 SUPPORT_SITE_ID NUMBER,
262 CUSTOMER_SITE_ID NUMBER,
263 SR_CREATION_CHANNEL VARCHAR2(150),
264 INVENTORY_ITEM_ID NUMBER,
265 ATTRIBUTE1 VARCHAR2(150),
266 ATTRIBUTE2 VARCHAR2(150),
267 ATTRIBUTE3 VARCHAR2(150),
268 ATTRIBUTE4 VARCHAR2(150),
269 ATTRIBUTE5 VARCHAR2(150),
270 ATTRIBUTE6 VARCHAR2(150),
271 ATTRIBUTE7 VARCHAR2(150),
272 ATTRIBUTE8 VARCHAR2(150),
273 ATTRIBUTE9 VARCHAR2(150),
274 ATTRIBUTE10 VARCHAR2(150),
275 ATTRIBUTE11 VARCHAR2(150),
276 ATTRIBUTE12 VARCHAR2(150),
277 ATTRIBUTE13 VARCHAR2(150),
278 ATTRIBUTE14 VARCHAR2(150),
279 ATTRIBUTE15 VARCHAR2(150),
280 ORGANIZATION_ID NUMBER,
281 SQUAL_NUM12 NUMBER, --INVENTORY ITEM ID / SR PLATFORM
282 SQUAL_NUM13 NUMBER, --ORGANIZATION ID / SR PLATFORM
283 SQUAL_NUM14 NUMBER, --CATEGORY ID / SR PRODUCT
284 SQUAL_NUM15 NUMBER, --INVENTORY ITEM ID / SR PRODUCT
285 SQUAL_NUM16 NUMBER, --ORGANIZATION ID / SR PRODUCT
286 SQUAL_NUM17 NUMBER, --DR GROUP OWNER
287 SQUAL_NUM18 NUMBER, --INVENTORY ITEM ID / CONTRACT SUPPORT SERVICE ITEM
288 SQUAL_NUM19 NUMBER, --ORGANIZATION ID / CONTRACT SUPPORT SERVICE ITEM
289 SQUAL_NUM30 NUMBER, --DR LANGUAGE ... should use squal_char20 instead
290 SQUAL_CHAR11 VARCHAR2(360), --VIP CUSTOMERS
291 SQUAL_CHAR12 VARCHAR2(360), --DR PROBLEM CODE
292 SQUAL_CHAR13 VARCHAR2(360), --DR CUSTOMER CONTACT PREFERENCE
293 SQUAL_CHAR20 VARCHAR2(360), --DR LANGUAGE ID for TERR REQ
294 SQUAL_CHAR21 VARCHAR2(360) --DR Service Contract Coverage
295 );
296
297 pkg_dr_rec JTF_DR_rec_type;
298
299 /********* End of addition by SBARAT on 01/11/2004 for Enh-3919046 *********/
300
301
302 /*
303 TYPE JTF_Def_Mgmt_rec_type IS RECORD
304 (
305 DEFECT_ID NUMBER,
306 PARTY_ID NUMBER,
307 COUNTRY VARCHAR2(60),
308 PARTY_SITE_ID NUMBER,
309 CITY VARCHAR2(60),
310 POSTAL_CODE VARCHAR2(60),
311 STATE VARCHAR2(60),
312 AREA_CODE VARCHAR2(10),
313 COUNTY VARCHAR2(60),
314 COMP_NAME_RANGE VARCHAR2(360),
315 PROVINCE VARCHAR2(60),
316 NUM_OF_EMPLOYEES NUMBER,
317 PROBLEM_TYPE_ID NUMBER,
321 TIER_CODE VARCHAR2(30),
318 PHASE_ID NUMBER,
319 SEVERITY_ID NUMBER,
320 PRIORITY_ID NUMBER,
322 UI_DEFECT_FLAG VARCHAR2(1),
323 FUNC_DEFECT_FLAG VARCHAR2(1),
324 PLATFORM_SPECIFIC_FLAG VARCHAR2(1),
325 ERROR_CODE_ID NUMBER,
326 LANGUAGE_CODE_ID NUMBER,
327 PLATFORM_ID NUMBER,
328 PRODUCT_ID NUMBER,
329 COMPONENT_ID NUMBER,
330 SUB_COMPONENT_ID NUMBER,
331 ATTRIBUTE1 VARCHAR2(150),
332 ATTRIBUTE2 VARCHAR2(150),
333 ATTRIBUTE3 VARCHAR2(150),
334 ATTRIBUTE4 VARCHAR2(150),
335 ATTRIBUTE5 VARCHAR2(150),
336 ATTRIBUTE6 VARCHAR2(150),
337 ATTRIBUTE7 VARCHAR2(150),
338 ATTRIBUTE8 VARCHAR2(150),
339 ATTRIBUTE9 VARCHAR2(150),
340 ATTRIBUTE10 VARCHAR2(150),
341 ATTRIBUTE11 VARCHAR2(150),
342 ATTRIBUTE12 VARCHAR2(150),
343 ATTRIBUTE13 VARCHAR2(150),
344 ATTRIBUTE14 VARCHAR2(150),
345 ATTRIBUTE15 VARCHAR2(150)
346 );
347 */
348
349
350 TYPE JTF_DEF_MGMT_rec_type IS RECORD
351 (
352 SQUAL_CHAR01 VARCHAR2(360),
353 SQUAL_CHAR02 VARCHAR2(360),
354 SQUAL_CHAR03 VARCHAR2(360),
355 SQUAL_CHAR04 VARCHAR2(360),
356 SQUAL_CHAR05 VARCHAR2(360),
357 SQUAL_CHAR06 VARCHAR2(360),
358 SQUAL_CHAR07 VARCHAR2(360),
359 SQUAL_CHAR08 VARCHAR2(360),
360 SQUAL_CHAR09 VARCHAR2(360),
361 SQUAL_CHAR10 VARCHAR2(360),
362 SQUAL_CHAR11 VARCHAR2(360),
363 SQUAL_CHAR12 VARCHAR2(360),
364 SQUAL_CHAR13 VARCHAR2(360),
365 SQUAL_CHAR14 VARCHAR2(360),
366 SQUAL_CHAR15 VARCHAR2(360),
367 SQUAL_CHAR16 VARCHAR2(360),
368 SQUAL_CHAR17 VARCHAR2(360),
369 SQUAL_CHAR18 VARCHAR2(360),
370 SQUAL_CHAR19 VARCHAR2(360),
371 SQUAL_CHAR20 VARCHAR2(360),
372 SQUAL_CHAR21 VARCHAR2(360),
373 SQUAL_CHAR22 VARCHAR2(360),
374 SQUAL_CHAR23 VARCHAR2(360),
375 SQUAL_CHAR24 VARCHAR2(360),
376 SQUAL_CHAR25 VARCHAR2(360),
377 SQUAL_NUM01 NUMBER,
378 SQUAL_NUM02 NUMBER,
379 SQUAL_NUM03 NUMBER,
380 SQUAL_NUM04 NUMBER,
381 SQUAL_NUM05 NUMBER,
382 SQUAL_NUM06 NUMBER,
383 SQUAL_NUM07 NUMBER,
384 SQUAL_NUM08 NUMBER,
385 SQUAL_NUM09 NUMBER,
386 SQUAL_NUM10 NUMBER,
387 SQUAL_NUM11 NUMBER,
388 SQUAL_NUM12 NUMBER,
389 SQUAL_NUM13 NUMBER,
390 SQUAL_NUM14 NUMBER,
391 SQUAL_NUM15 NUMBER,
392 SQUAL_NUM16 NUMBER,
393 SQUAL_NUM17 NUMBER,
394 SQUAL_NUM18 NUMBER,
395 SQUAL_NUM19 NUMBER,
396 SQUAL_NUM20 NUMBER,
397 SQUAL_NUM21 NUMBER,
398 SQUAL_NUM22 NUMBER,
399 SQUAL_NUM23 NUMBER,
400 SQUAL_NUM24 NUMBER,
401 SQUAL_NUM25 NUMBER,
402 ATTRIBUTE1 VARCHAR2(150),
403 ATTRIBUTE2 VARCHAR2(150),
404 ATTRIBUTE3 VARCHAR2(150),
405 ATTRIBUTE4 VARCHAR2(150),
406 ATTRIBUTE5 VARCHAR2(150),
407 ATTRIBUTE6 VARCHAR2(150),
408 ATTRIBUTE7 VARCHAR2(150),
409 ATTRIBUTE8 VARCHAR2(150),
410 ATTRIBUTE9 VARCHAR2(150),
411 ATTRIBUTE10 VARCHAR2(150),
412 ATTRIBUTE11 VARCHAR2(150),
413 ATTRIBUTE12 VARCHAR2(150),
414 ATTRIBUTE13 VARCHAR2(150),
415 ATTRIBUTE14 VARCHAR2(150),
416 ATTRIBUTE15 VARCHAR2(150)
417 );
418
419 pkg_defect_rec JTF_Def_Mgmt_rec_type;
420
421
422
423 TYPE JTF_Oppor_rec_type IS RECORD
424 (
425 LEAD_ID NUMBER,
426 LEAD_LINE_ID NUMBER,
427 CITY VARCHAR2(60),
428 POSTAL_CODE VARCHAR2(60),
429 STATE VARCHAR2(60),
430 PROVINCE VARCHAR2(60),
431 COUNTY VARCHAR2(60),
432 COUNTRY VARCHAR2(60),
436 CONTACT_INTEREST_TYPE_ID NUMBER,
433 INTEREST_TYPE_ID NUMBER,
434 PRIMARY_INTEREST_ID NUMBER,
435 SECONDARY_INTEREST_ID NUMBER,
437 CONTACT_PRIMARY_INTEREST_ID NUMBER,
438 CONTACT_SECONDARY_INTEREST_ID NUMBER,
439 PARTY_SITE_ID NUMBER,
440 AREA_CODE VARCHAR2(10),
441 PARTY_ID NUMBER,
442 COMP_NAME_RANGE VARCHAR2(360),
443 PARTNER_ID NUMBER,
444 NUM_OF_EMPLOYEES NUMBER,
445 CATEGORY_CODE VARCHAR2(30),
446 PARTY_RELATIONSHIP_ID NUMBER,
447 SIC_CODE VARCHAR2(60),
448 TARGET_SEGMENT_CURRENT VARCHAR2(25),
449 TOTAL_AMOUNT NUMBER,
450 CURRENCY_CODE VARCHAR2(15),
451 PRICING_DATE DATE,
452 CHANNEL_CODE VARCHAR2(25),
453 INVENTORY_ITEM_ID NUMBER,
454 OPP_INTEREST_TYPE_ID NUMBER,
455 OPP_PRIMARY_INTEREST_ID NUMBER,
456 OPP_SECONDARY_INTEREST_ID NUMBER,
457 OPCLSS_INTEREST_TYPE_ID NUMBER,
458 OPCLSS_PRIMARY_INTEREST_ID NUMBER,
459 OPCLSS_SECONDARY_INTEREST_ID NUMBER,
460 ATTRIBUTE1 VARCHAR2(150),
461 ATTRIBUTE2 VARCHAR2(150),
462 ATTRIBUTE3 VARCHAR2(150),
463 ATTRIBUTE4 VARCHAR2(150),
464 ATTRIBUTE5 VARCHAR2(150),
465 ATTRIBUTE6 VARCHAR2(150),
466 ATTRIBUTE7 VARCHAR2(150),
467 ATTRIBUTE8 VARCHAR2(150),
468 ATTRIBUTE9 VARCHAR2(150),
469 ATTRIBUTE10 VARCHAR2(150),
470 ATTRIBUTE11 VARCHAR2(150),
471 ATTRIBUTE12 VARCHAR2(150),
472 ATTRIBUTE13 VARCHAR2(150),
473 ATTRIBUTE14 VARCHAR2(150),
474 ATTRIBUTE15 VARCHAR2(150),
475 ORG_ID NUMBER
476 );
477
478
479
480 TYPE JTF_Lead_rec_type IS RECORD
481 (
482 SALES_LEAD_ID NUMBER,
483 SALES_LEAD_LINE_ID NUMBER,
484 CITY VARCHAR2(60),
485 POSTAL_CODE VARCHAR2(60),
486 STATE VARCHAR2(60),
487 PROVINCE VARCHAR2(60),
488 COUNTY VARCHAR2(60),
489 COUNTRY VARCHAR2(60),
490 INTEREST_TYPE_ID NUMBER,
491 PRIMARY_INTEREST_ID NUMBER,
492 SECONDARY_INTEREST_ID NUMBER,
493 CONTACT_INTEREST_TYPE_ID NUMBER,
494 CONTACT_PRIMARY_INTEREST_ID NUMBER,
495 CONTACT_SECONDARY_INTEREST_ID NUMBER,
496 PARTY_SITE_ID NUMBER,
497 AREA_CODE VARCHAR2(10),
498 PARTY_ID NUMBER,
499 COMP_NAME_RANGE VARCHAR2(360),
500 PARTNER_ID NUMBER,
501 NUM_OF_EMPLOYEES NUMBER,
502 CATEGORY_CODE VARCHAR2(30),
503 PARTY_RELATIONSHIP_ID NUMBER,
504 SIC_CODE VARCHAR2(60),
505 BUDGET_AMOUNT NUMBER,
506 CURRENCY_CODE VARCHAR2(15),
507 PRICING_DATE DATE,
508 SOURCE_PROMOTION_ID NUMBER,
509 INVENTORY_ITEM_ID NUMBER,
510 LEAD_INTEREST_TYPE_ID NUMBER,
511 LEAD_PRIMARY_INTEREST_ID NUMBER,
512 LEAD_SECONDARY_INTEREST_ID NUMBER,
513 PURCHASE_AMOUNT NUMBER,
514 ATTRIBUTE1 VARCHAR2(150),
515 ATTRIBUTE2 VARCHAR2(150),
516 ATTRIBUTE3 VARCHAR2(150),
517 ATTRIBUTE4 VARCHAR2(150),
518 ATTRIBUTE5 VARCHAR2(150),
519 ATTRIBUTE6 VARCHAR2(150),
520 ATTRIBUTE7 VARCHAR2(150),
521 ATTRIBUTE8 VARCHAR2(150),
522 ATTRIBUTE9 VARCHAR2(150),
523 ATTRIBUTE10 VARCHAR2(150),
524 ATTRIBUTE11 VARCHAR2(150),
525 ATTRIBUTE12 VARCHAR2(150),
526 ATTRIBUTE13 VARCHAR2(150),
527 ATTRIBUTE14 VARCHAR2(150),
528 ATTRIBUTE15 VARCHAR2(150),
529 ORG_ID NUMBER
530 );
531
532
533
534 TYPE JTF_Account_rec_type IS RECORD
535 (
536 CITY VARCHAR2(60),
537 POSTAL_CODE VARCHAR2(60),
538 STATE VARCHAR2(60),
539 PROVINCE VARCHAR2(60),
540 COUNTY VARCHAR2(60),
541 COUNTRY VARCHAR2(60),
542 INTEREST_TYPE_ID NUMBER,
543 PRIMARY_INTEREST_ID NUMBER,
544 SECONDARY_INTEREST_ID NUMBER,
545 CONTACT_INTEREST_TYPE_ID NUMBER,
546 CONTACT_PRIMARY_INTEREST_ID NUMBER,
550 PARTY_ID NUMBER,
547 CONTACT_SECONDARY_INTEREST_ID NUMBER,
548 PARTY_SITE_ID NUMBER,
549 AREA_CODE VARCHAR2(10),
551 COMP_NAME_RANGE VARCHAR2(360),
552 PARTNER_ID NUMBER,
553 NUM_OF_EMPLOYEES NUMBER,
554 CATEGORY_CODE VARCHAR2(30),
555 PARTY_RELATIONSHIP_ID NUMBER,
556 SIC_CODE VARCHAR2(60),
557 ATTRIBUTE1 VARCHAR2(150),
558 ATTRIBUTE2 VARCHAR2(150),
559 ATTRIBUTE3 VARCHAR2(150),
560 ATTRIBUTE4 VARCHAR2(150),
561 ATTRIBUTE5 VARCHAR2(150),
562 ATTRIBUTE6 VARCHAR2(150),
563 ATTRIBUTE7 VARCHAR2(150),
564 ATTRIBUTE8 VARCHAR2(150),
565 ATTRIBUTE9 VARCHAR2(150),
566 ATTRIBUTE10 VARCHAR2(150),
567 ATTRIBUTE11 VARCHAR2(150),
568 ATTRIBUTE12 VARCHAR2(150),
569 ATTRIBUTE13 VARCHAR2(150),
570 ATTRIBUTE14 VARCHAR2(150),
571 ATTRIBUTE15 VARCHAR2(150),
572 ORG_ID NUMBER
573 );
574
575
576
577 TYPE Prfeng_rec_type IS RECORD
578 (
579 ENGINEER_ID NUMBER := NULL,
580 RESOURCE_TYPE VARCHAR2(30) := NULL,
581 PRIMARY_FLAG VARCHAR2(30) := NULL,
582 PREFERRED_FLAG VARCHAR2(30) := NULL,
583 RESOURCE_CLASS VARCHAR2(30) := NULL
584 );
585
586 TYPE Prfeng_tbl_type IS TABLE OF Prfeng_rec_type
587 INDEX BY BINARY_INTEGER;
588
589
590 TYPE Preferred_Engineers_rec_type IS RECORD
591 (
592 ENGINEER_ID NUMBER := NULL,
593 RESOURCE_TYPE VARCHAR2(30) := NULL,
594 PREFERENCE_TYPE VARCHAR2(5) := NULL,
595 PRIMARY_FLAG VARCHAR2(30) := NULL
596 );
597
598 TYPE Preferred_Engineers_tbl_type IS TABLE OF Preferred_Engineers_rec_type
599 INDEX BY BINARY_INTEGER;
600
601
602 TYPE Escalations_rec_type IS RECORD
603 (
604 SOURCE_OBJECT_ID NUMBER := JTF_ASSIGN_PUB.AM_MISS_NUM,
605 SOURCE_OBJECT_TYPE VARCHAR2(30) := JTF_ASSIGN_PUB.AM_MISS_CHAR
606 );
607
608 TYPE Escalations_tbl_type IS TABLE OF Escalations_rec_type
609 INDEX BY BINARY_INTEGER;
610
611
612 -- created a new record typr for use of Exckuded Resources
613 TYPE excluded_rec_type IS RECORD
614 (
615 RESOURCE_ID NUMBER := NULL,
616 RESOURCE_TYPE VARCHAR2(30) := NULL
617 );
618
619 TYPE excluded_tbl_type IS TABLE OF excluded_rec_type
620 INDEX BY BINARY_INTEGER;
621
622
623 -- global parameter for workflow
624 g_assign_resources_tbl JTF_ASSIGN_PUB.AssignResources_tbl_type;
625
626 -- global record type for component/subcomponent issue
627 -- to overcome build dependancy on Territory manager
628 -- for newly added two fields in territory record types
629 -- squal_num23, squal_num24
630 -- Added by SBARAT on 10/01/2004 for Enh 4112155
631
632 G_Terr_Serv_Req_Rec_Type JTF_TERRITORY_PUB.JTF_Serv_Req_rec_type;
633 G_Terr_Srv_Task_Rec_Type JTF_TERRITORY_PUB.JTF_Srv_Task_rec_type;
634
635
636
637 -- *******************************************************************************
638
639 -- Start of comments
640
641 -- API name : GET_ASSIGN_RESOURCES
642 -- Type : Public
643 -- Function : Determine the resources based on the customer
644 -- preferences or territory preferences and the
645 -- availability.
646 -- Pre-reqs : None
647
648 -- Parameters :
649
650 -- IN : p_api_version IN NUMBER Required
651 -- p_init_msg_list IN VARCHAR2 Optional
652 -- DEFAULT JTF_ASSIGN_PUB.AM_FALSE
653 -- p_commit IN VARCHAR2 optional
654 -- DEFAULT JTF_ASSIGN_PUB.AM_FALSE
655
656 -- Assignment Manager Specific Parameters
657
658 -- This determines the Resource, Resource Type
659 -- and Resource Role required by the calling document
660 -- p_resource_id NUMBER
661 -- p_resource_type VARCHAR2(30)
662 -- p_role VARCHAR2(30)
663
664 -- This determines the number of resources required
665 -- by the calling document
666 -- Defaulted to 1
667 -- p_no_of_resources NUMBER,
668
669 -- This is for sending out the qualified resource directly
670 -- to the calling form.
671 -- Defaulted to 'Y'(Profile Value)
672 -- p_auto_select_flag VARCHAR2(1)
673 -- : value of Y or N
674
678 -- : value of Y or N
675 -- This is to set the preference of CONTRACTS PREFERRED ENGINEERS
676 -- Defaulted to 'N'(Profile Value)
677 -- p_contracts_preferred_engineer VARCHAR2(1)
679
680 -- This is to set the preference of INSTALL BASE PREFERRED ENGINEERS
681 -- Defaulted to 'N'(Profile Value)
682 -- p_ib_preferred_engineer VARCHAR2(1)
683 -- : value of Y or N
684
685 -- This is to fetch the CONTRACTS PREFERRED ENGINEERS
686 -- p_contract_id NUMBER
687
688 -- This is to fetch the INSTALL BASE PREFERRED ENGINEERS
689 -- p_customer_product_id NUMBER
690
691 -- The Effort required is determined by these two parameters
692 -- p_effort_duration NUMBER
693 -- p_effort_uom VARCHAR2(10)
694
695 -- The Dates in which the resource is required, is determined
696 -- by these two parameters
697 -- p_start_date DATE
698 -- p_end_date DATE
699
700
701 -- The Territory Manager is accessed based on the value set
702 -- Defaulted to Y
703 -- p_territory_flag VARCHAR2(1)
704 -- : value of Y or N
705
706 -- The Resource Availability is checked based on the value set
707 -- Defaulted to Y
708 -- p_calendar_flag VARCHAR2(1)
709 -- : value of Y or N
710
711 -- This parameter contains the Calling Document ID
712 -- which could be a TASK_ID etc.
713 -- p_calling_doc_id NUMBER
714 -- REQUIRED for TASKS
715
716 -- This parameter contains the Calling Document Type
717 -- which could be :
718 -- 'TASK' when the calling doc is TASK
719 -- or 'SR' when the calling doc is SERVICE REQUEST
720 -- or 'OPPR' when the calling doc is OPPORTUNITIES
721 -- or 'DEF' when the calling doc is DEFECT MANAGEMENT
722 -- It is mandatory to enter a value for this parameter
723 -- to find proper qualified resources
724 -- p_calling_doc_type VARCHAR2
725
726 -- This parameter contains list of qualifier columns from the
727 -- UI which have been selected to re-query the resources.
728 -- Strictly for the use of User Interface of Assignment Manager.
729 -- p_column_list VARCHAR2
730
731 -- These parameters contain the Qualifier Values for
732 -- the Calling Document
733 -- p_sr_rec JTF_ASSIGN_PUB.
734 -- JTF_Serv_Req_rec_type
735 -- p_sr_task_rec JTF_ASSIGN_PUB.
736 -- JTF_Srv_Task_rec_type
737 -- p_defect_rec JTF_ASSIGN_PUB.
738 -- JTF_Def_Mgmt_rec_type
739
740 -- OUT : x_return_status OUT VARCHAR2(1)
741 -- x_msg_count OUT NUMBER
742 -- x_msg_data OUT VARCHAR2(2000)
743 -- x_assign_resources_tbl OUT JTF_ASSIGN_PUB.
744 -- AssignResources_tbl_type
745
746
747 -- Version : Current version 1.0
748 -- Initial version 1.0
749 --
750 -- Notes :
751 --
752
753 -- End of comments
754
755 -- *********************************************************************************
756
757
758 /* Package variables */
759
760 G_PKG_NAME CONSTANT VARCHAR2(30):= 'JTF_ASSIGN_PUB';
761
762
763 -- Main Procedure definition with the parameters
764 -- This procedure in turn calls the relevant procedure to
765 -- process the requests for assignment of resources
766
767
768 PROCEDURE GET_ASSIGN_RESOURCES
769 ( p_api_version IN NUMBER,
770 p_init_msg_list IN VARCHAR2 DEFAULT JTF_ASSIGN_PUB.AM_FALSE,
771 p_commit IN VARCHAR2 DEFAULT JTF_ASSIGN_PUB.AM_FALSE,
772 p_resource_id IN NUMBER DEFAULT NULL,
773 p_resource_type IN VARCHAR2 DEFAULT NULL,
774 p_role IN VARCHAR2 DEFAULT NULL,
775 p_no_of_resources IN NUMBER DEFAULT 1,
776 p_auto_select_flag IN VARCHAR2 DEFAULT NULL,
777 p_contracts_preferred_engineer IN VARCHAR2 DEFAULT NULL,
778 p_ib_preferred_engineer IN VARCHAR2 DEFAULT NULL,
779 p_contract_id IN NUMBER DEFAULT NULL,
780 p_customer_product_id IN NUMBER DEFAULT NULL,
781 p_effort_duration IN NUMBER DEFAULT NULL,
782 p_effort_uom IN VARCHAR2 DEFAULT NULL,
783 --p_breakdown IN NUMBER DEFAULT NULL,
784 --p_breakdown_uom IN VARCHAR2 DEFAULT NULL,
785 p_start_date IN DATE DEFAULT NULL,
786 p_end_date IN DATE DEFAULT NULL,
790 p_category_id IN NUMBER DEFAULT NULL,
787 p_territory_flag IN VARCHAR2 DEFAULT 'Y',
788 p_calendar_flag IN VARCHAR2 DEFAULT 'Y',
789 p_web_availability_flag IN VARCHAR2 DEFAULT NULL,
791 p_inventory_item_id IN NUMBER DEFAULT NULL,
792 p_inventory_org_id IN NUMBER DEFAULT NULL,
793 p_problem_code IN VARCHAR2 DEFAULT NULL,
794 p_calling_doc_id IN NUMBER,
795 p_calling_doc_type IN VARCHAR2,
796 p_column_list IN VARCHAR2 DEFAULT NULL,
797 p_sr_rec IN JTF_ASSIGN_PUB.JTF_Serv_Req_rec_type DEFAULT pkg_sr_rec,
798 p_sr_task_rec IN JTF_ASSIGN_PUB.JTF_Srv_Task_rec_type DEFAULT pkg_sr_task_rec,
799 p_defect_rec IN JTF_ASSIGN_PUB.JTF_Def_Mgmt_rec_type DEFAULT pkg_defect_rec,
800 p_business_process_id IN NUMBER DEFAULT NULL,
801 p_business_process_date IN DATE DEFAULT NULL,
802 p_filter_excluded_resource IN VARCHAR2 DEFAULT 'N',
803 x_assign_resources_tbl OUT NOCOPY JTF_ASSIGN_PUB.AssignResources_tbl_type,
804 x_return_status OUT NOCOPY VARCHAR2,
805 x_msg_count OUT NOCOPY NUMBER,
806 x_msg_data OUT NOCOPY VARCHAR2,
807 --Added for Bug # 5386560
808 p_inventory_component_id IN NUMBER DEFAULT NULL,
809 --Added for Bug # 5386560 Ends here
810 --Added for Bug # 5573916
811 p_calendar_check IN VARCHAR2 DEFAULT 'Y'
812 --Added for Bug # 5573916 Ends here
813 );
814
815
816
817
818
819 -- Procedure definition with the parameters when the
820 -- Source Document is TASK
821
822 PROCEDURE GET_ASSIGN_TASK_RESOURCES
823 (
824 p_api_version IN NUMBER,
825 p_init_msg_list IN VARCHAR2,
826 p_resource_type IN VARCHAR2,
827 p_role IN VARCHAR2,
828 p_no_of_resources IN NUMBER,
829 p_auto_select_flag IN VARCHAR2,
830 p_contracts_preferred_engineer IN VARCHAR2,
831 p_ib_preferred_engineer IN VARCHAR2,
832 p_effort_duration IN NUMBER,
833 p_effort_uom IN VARCHAR2,
834 p_start_date IN DATE,
835 p_end_date IN DATE,
836 --p_breakdown IN NUMBER DEFAULT NULL ,
837 --p_breakdown_uom IN VARCHAR2 DEFAULT NULL,
838 p_territory_flag IN VARCHAR2,
839 p_calendar_flag IN VARCHAR2,
840 p_web_availability_flag IN VARCHAR2,
841 p_task_id IN JTF_TASKS_VL.TASK_ID%TYPE,
842 p_column_list IN VARCHAR2,
843 p_business_process_id IN NUMBER DEFAULT NULL,
844 p_business_process_date IN DATE DEFAULT NULL,
845 p_filter_excluded_resource IN VARCHAR2 DEFAULT 'N',
846 x_assign_resources_tbl OUT NOCOPY JTF_ASSIGN_PUB.AssignResources_tbl_type,
847 x_return_status OUT NOCOPY VARCHAR2,
848 x_msg_count OUT NOCOPY NUMBER,
849 x_msg_data OUT NOCOPY VARCHAR2,
850 --Added for Bug # 5573916
851 p_calendar_check IN VARCHAR2 DEFAULT 'Y'
852 --Added for Bug # 5573916 Ends here
853 );
854
855
856
857
858 -- Procedure definition with the parameters when the
859 -- Source Document is SERVICE REQUEST
860
861 PROCEDURE GET_ASSIGN_SR_RESOURCES
862 (
863 p_api_version IN NUMBER,
864 p_init_msg_list IN VARCHAR2,
865 p_resource_type IN VARCHAR2,
866 p_role IN VARCHAR2,
867 p_no_of_resources IN NUMBER,
868 p_auto_select_flag IN VARCHAR2,
869 p_contracts_preferred_engineer IN VARCHAR2,
870 p_ib_preferred_engineer IN VARCHAR2,
871 p_contract_id IN NUMBER,
872 p_customer_product_id IN NUMBER,
873 p_effort_duration IN NUMBER,
874 p_effort_uom IN VARCHAR2,
875 p_start_date IN DATE,
876 p_end_date IN DATE,
877 --p_breakdown IN NUMBER DEFAULT NULL ,
878 --p_breakdown_uom IN VARCHAR2 DEFAULT NULL,
879 p_territory_flag IN VARCHAR2,
880 p_calendar_flag IN VARCHAR2,
881 p_web_availability_flag IN VARCHAR2,
882 p_category_id IN NUMBER,
883 p_inventory_item_id IN NUMBER,
884 p_inventory_org_id IN NUMBER,
885 p_problem_code IN VARCHAR2,
889 p_business_process_id IN NUMBER DEFAULT NULL,
886 p_sr_id IN NUMBER,
887 p_sr_rec IN JTF_TERRITORY_PUB.JTF_Serv_Req_rec_type,
888 p_sr_task_rec IN JTF_TERRITORY_PUB.JTF_Srv_Task_rec_type,
890 p_business_process_date IN DATE DEFAULT NULL,
891 p_filter_excluded_resource IN VARCHAR2 DEFAULT 'N',
892 x_assign_resources_tbl OUT NOCOPY JTF_ASSIGN_PUB.AssignResources_tbl_type,
893 x_return_status OUT NOCOPY VARCHAR2,
894 x_msg_count OUT NOCOPY NUMBER,
895 x_msg_data OUT NOCOPY VARCHAR2,
896 --Added for Bug # 5386560
897 p_inventory_component_id IN NUMBER DEFAULT NULL,
898 --Added for Bug # 5386560 Ends here
899 --Added for Bug # 5573916
900 p_calendar_check IN VARCHAR2 DEFAULT 'Y'
901 --Added for Bug # 5573916 Ends here
902 );
903
904
905 /************ Added by SBARAT on 01/11/2004 for Enh-3919046 **********/
906
907 -- Procedure definition with the parameters when the
908 -- Source Document is DEPOT REPAIR
909
910 PROCEDURE GET_ASSIGN_DR_RESOURCES
911 ( p_api_version IN NUMBER,
912 p_init_msg_list IN VARCHAR2 ,
913 p_resource_type IN VARCHAR2 ,
914 p_role IN VARCHAR2 ,
915 p_no_of_resources IN NUMBER ,
916 p_auto_select_flag IN VARCHAR2 ,
917 p_contracts_preferred_engineer IN VARCHAR2 ,
918 p_ib_preferred_engineer IN VARCHAR2 ,
919 p_contract_id IN NUMBER ,
920 p_customer_product_id IN NUMBER ,
921 p_effort_duration IN NUMBER ,
922 p_effort_uom IN VARCHAR2 ,
923 p_start_date IN DATE ,
924 p_end_date IN DATE ,
925 p_territory_flag IN VARCHAR2 ,
926 p_calendar_flag IN VARCHAR2 ,
927 p_web_availability_flag IN VARCHAR2 ,
928 p_category_id IN NUMBER ,
929 p_inventory_item_id IN NUMBER ,
930 p_inventory_org_id IN NUMBER ,
931 p_problem_code IN VARCHAR2 ,
932 p_dr_id IN NUMBER,
933 p_column_list IN VARCHAR2 ,
934 p_dr_rec IN JTF_ASSIGN_PUB.JTF_DR_rec_type ,
935 p_business_process_id IN NUMBER,
936 p_business_process_date IN DATE,
937 p_filter_excluded_resource IN VARCHAR2,
938 x_assign_resources_tbl OUT NOCOPY JTF_ASSIGN_PUB.AssignResources_tbl_type,
939 x_return_status OUT NOCOPY VARCHAR2,
940 x_msg_count OUT NOCOPY NUMBER,
941 x_msg_data OUT NOCOPY VARCHAR2,
942 --Added for Bug # 5573916
943 p_calendar_check IN VARCHAR2 DEFAULT 'Y'
944 --Added for Bug # 5573916 Ends here
945 );
946
947 /*********** End of addition by SBARAT on 01/11/2004 for Enh-3919046 *********/
948
949
950 -- Procedure definition with the parameters when the
951 -- Source Document is OPPORTUNITIES
952
953 PROCEDURE GET_ASSIGN_OPPR_RESOURCES
954 (
955 p_api_version IN NUMBER,
956 p_init_msg_list IN VARCHAR2 DEFAULT JTF_ASSIGN_PUB.am_false,
957 p_resource_type IN VARCHAR2 DEFAULT NULL,
958 p_role IN VARCHAR2 DEFAULT NULL,
959 p_no_of_resources IN NUMBER DEFAULT 1,
960 p_auto_select_flag IN VARCHAR2 DEFAULT NULL,
961 p_effort_duration IN NUMBER DEFAULT NULL,
962 p_effort_uom IN VARCHAR2 DEFAULT NULL,
963 p_start_date IN DATE DEFAULT NULL,
964 p_end_date IN DATE DEFAULT NULL,
965 --p_breakdown IN NUMBER DEFAULT NULL ,
966 --p_breakdown_uom IN VARCHAR2 DEFAULT NULL,
967 p_territory_flag IN VARCHAR2 DEFAULT 'Y',
968 p_calendar_flag IN VARCHAR2 DEFAULT 'Y',
969 p_opportunity_rec IN JTF_ASSIGN_PUB.JTF_Oppor_rec_type,
970 p_business_process_id IN NUMBER DEFAULT NULL,
971 p_business_process_date IN DATE DEFAULT NULL,
972 x_assign_resources_tbl OUT NOCOPY JTF_ASSIGN_PUB.AssignResources_tbl_type,
973 x_return_status OUT NOCOPY VARCHAR2,
974 x_msg_count OUT NOCOPY NUMBER,
975 x_msg_data OUT NOCOPY VARCHAR2
976 );
977
978
979
980 -- Procedure definition with the parameters when the
981 -- Source Document is LEADS (For BULK Record)
982
983 PROCEDURE GET_ASSIGN_LEAD_RESOURCES
984 (
985 p_api_version IN NUMBER,
989 p_no_of_resources IN NUMBER DEFAULT 1,
986 p_init_msg_list IN VARCHAR2 DEFAULT JTF_ASSIGN_PUB.am_false,
987 p_resource_type IN VARCHAR2 DEFAULT NULL,
988 p_role IN VARCHAR2 DEFAULT NULL,
990 p_auto_select_flag IN VARCHAR2 DEFAULT NULL,
991 p_effort_duration IN NUMBER DEFAULT NULL,
992 p_effort_uom IN VARCHAR2 DEFAULT NULL,
993 p_start_date IN DATE DEFAULT NULL,
994 p_end_date IN DATE DEFAULT NULL,
995 --p_breakdown IN NUMBER DEFAULT NULL ,
996 --p_breakdown_uom IN VARCHAR2 DEFAULT NULL,
997 p_territory_flag IN VARCHAR2 DEFAULT 'Y',
998 p_calendar_flag IN VARCHAR2 DEFAULT 'Y',
999 p_lead_rec IN JTF_TERRITORY_PUB.JTF_Lead_BULK_rec_type,
1000 --x_assign_resources_bulk_rec OUT NOCOPY JTF_TERRITORY_PUB.WINNING_BULK_REC_TYPE,
1001 p_business_process_id IN NUMBER DEFAULT NULL,
1002 p_business_process_date IN DATE DEFAULT NULL,
1003 x_assign_resources_tbl OUT NOCOPY JTF_ASSIGN_PUB.AssignResources_tbl_type,
1004 x_return_status OUT NOCOPY VARCHAR2,
1005 x_msg_count OUT NOCOPY NUMBER,
1006 x_msg_data OUT NOCOPY VARCHAR2
1007 );
1008
1009
1010
1011 -- Procedure definition with the parameters when the
1012 -- Source Document is LEADS (For SINGLE Record)
1013
1014 PROCEDURE GET_ASSIGN_LEAD_RESOURCES
1015 (
1016 p_api_version IN NUMBER,
1017 p_init_msg_list IN VARCHAR2 DEFAULT JTF_ASSIGN_PUB.am_false,
1018 p_resource_type IN VARCHAR2 DEFAULT NULL,
1019 p_role IN VARCHAR2 DEFAULT NULL,
1020 p_no_of_resources IN NUMBER DEFAULT 1,
1021 p_auto_select_flag IN VARCHAR2 DEFAULT NULL,
1022 p_effort_duration IN NUMBER DEFAULT NULL,
1023 p_effort_uom IN VARCHAR2 DEFAULT NULL,
1024 p_start_date IN DATE DEFAULT NULL,
1025 p_end_date IN DATE DEFAULT NULL,
1026 p_territory_flag IN VARCHAR2 DEFAULT 'Y',
1027 p_calendar_flag IN VARCHAR2 DEFAULT 'Y',
1028 p_lead_rec IN JTF_ASSIGN_PUB.JTF_Lead_rec_type,
1029 p_business_process_id IN NUMBER DEFAULT NULL,
1030 p_business_process_date IN DATE DEFAULT NULL,
1031 x_assign_resources_tbl OUT NOCOPY JTF_ASSIGN_PUB.AssignResources_tbl_type,
1032 x_return_status OUT NOCOPY VARCHAR2,
1033 x_msg_count OUT NOCOPY NUMBER,
1034 x_msg_data OUT NOCOPY VARCHAR2
1035 );
1036
1037
1038
1039 -- Procedure definition with the parameters when the
1040 -- Source Document is ACCOUNTS
1041
1042
1043 PROCEDURE GET_ASSIGN_ACCOUNT_RESOURCES
1044 (
1045 p_api_version IN NUMBER,
1046 p_init_msg_list IN VARCHAR2 DEFAULT JTF_ASSIGN_PUB.am_false,
1047 p_resource_type IN VARCHAR2 DEFAULT NULL,
1048 p_role IN VARCHAR2 DEFAULT NULL,
1049 p_no_of_resources IN NUMBER DEFAULT 1,
1050 p_auto_select_flag IN VARCHAR2 DEFAULT NULL,
1051 p_effort_duration IN NUMBER DEFAULT NULL,
1052 p_effort_uom IN VARCHAR2 DEFAULT NULL,
1053 p_start_date IN DATE DEFAULT NULL,
1054 p_end_date IN DATE DEFAULT NULL,
1055 --p_breakdown IN NUMBER DEFAULT NULL ,
1056 --p_breakdown_uom IN VARCHAR2 DEFAULT NULL,
1057 p_territory_flag IN VARCHAR2 DEFAULT 'Y',
1058 p_calendar_flag IN VARCHAR2 DEFAULT 'Y',
1059 p_account_rec IN JTF_ASSIGN_PUB.JTF_Account_rec_type,
1060 p_business_process_id IN NUMBER DEFAULT NULL,
1061 p_business_process_date IN DATE DEFAULT NULL,
1062 x_assign_resources_tbl OUT NOCOPY JTF_ASSIGN_PUB.AssignResources_tbl_type,
1063 x_return_status OUT NOCOPY VARCHAR2,
1064 x_msg_count OUT NOCOPY NUMBER,
1065 x_msg_data OUT NOCOPY VARCHAR2
1066 );
1067
1068
1069
1070 -- Procedure definition with the parameters when the
1071 -- Source Document is DEFECT MANAGEMENT SYSTEM
1072
1073 PROCEDURE GET_ASSIGN_DEFECT_RESOURCES
1074 (
1075 p_api_version IN NUMBER,
1076 p_init_msg_list IN VARCHAR2,
1077 p_resource_type IN VARCHAR2,
1078 p_role IN VARCHAR2,
1079 p_no_of_resources IN NUMBER,
1080 p_auto_select_flag IN VARCHAR2,
1081 p_effort_duration IN NUMBER,
1082 p_effort_uom IN VARCHAR2,
1083 p_start_date IN DATE,
1084 p_end_date IN DATE,
1085 --p_breakdown IN NUMBER DEFAULT NULL,
1086 --p_breakdown_uom IN VARCHAR2 DEFAULT NULL,
1087 p_territory_flag IN VARCHAR2,
1088 p_calendar_flag IN VARCHAR2,
1089 p_defect_rec IN JTF_TERRITORY_PUB.JTF_Def_Mgmt_rec_type,
1090 p_business_process_id IN NUMBER DEFAULT NULL,
1091 p_business_process_date IN DATE DEFAULT NULL,
1092 x_assign_resources_tbl OUT NOCOPY JTF_ASSIGN_PUB.AssignResources_tbl_type,
1093 x_return_status OUT NOCOPY VARCHAR2,
1094 x_msg_count OUT NOCOPY NUMBER,
1095 x_msg_data OUT NOCOPY VARCHAR2
1096 );
1097
1098
1099
1100 -- Procedure definition with the parameters when the
1101 -- Source Document is ESCALATIONS
1102
1103 PROCEDURE GET_ASSIGN_ESC_RESOURCES
1104 (
1105 p_api_version IN NUMBER,
1106 p_init_msg_list IN VARCHAR2 DEFAULT JTF_ASSIGN_PUB.AM_FALSE,
1107 p_resource_type IN VARCHAR2 DEFAULT NULL,
1108 p_role IN VARCHAR2 DEFAULT NULL,
1109 p_no_of_resources IN NUMBER DEFAULT 1,
1110 p_auto_select_flag IN VARCHAR2 DEFAULT NULL,
1111 p_effort_duration IN NUMBER DEFAULT NULL,
1112 p_effort_uom IN VARCHAR2 DEFAULT NULL,
1113 p_start_date IN DATE DEFAULT NULL,
1114 p_end_date IN DATE DEFAULT NULL,
1115 --p_breakdown IN NUMBER DEFAULT NULL ,
1116 --p_breakdown_uom IN VARCHAR2 DEFAULT NULL,
1117 p_territory_flag IN VARCHAR2 DEFAULT 'Y',
1118 p_calendar_flag IN VARCHAR2 DEFAULT 'Y',
1119 p_web_availability_flag IN VARCHAR2 DEFAULT NULL,
1120 p_esc_tbl IN JTF_ASSIGN_PUB.Escalations_tbl_type,
1121 p_business_process_id IN NUMBER DEFAULT NULL,
1122 p_business_process_date IN DATE DEFAULT NULL,
1123 x_assign_resources_tbl OUT NOCOPY JTF_ASSIGN_PUB.AssignResources_tbl_type,
1124 x_return_status OUT NOCOPY VARCHAR2,
1125 x_msg_count OUT NOCOPY NUMBER,
1126 x_msg_data OUT NOCOPY VARCHAR2
1127 );
1128
1129
1130 -- this is a procedure added on 2nd July 2002 to get the Excluded Resources for the AM UI
1131 -- when working in assisted Mode
1132 PROCEDURE GET_EXCLUDED_RESOURCES
1133 ( p_api_version IN NUMBER,
1134 p_init_msg_list IN VARCHAR2 DEFAULT JTF_ASSIGN_PUB.AM_FALSE,
1135 p_commit IN VARCHAR2 DEFAULT JTF_ASSIGN_PUB.AM_FALSE,
1136 p_contract_id IN NUMBER DEFAULT NULL,
1137 p_customer_product_id IN NUMBER DEFAULT NULL,
1138 p_calling_doc_id IN NUMBER,
1139 p_calling_doc_type IN VARCHAR2,
1140 p_sr_rec IN JTF_ASSIGN_PUB.JTF_Serv_Req_rec_type DEFAULT pkg_sr_rec,
1141 p_sr_task_rec IN JTF_ASSIGN_PUB.JTF_Srv_Task_rec_type DEFAULT pkg_sr_task_rec,
1142 p_dr_rec IN JTF_ASSIGN_PUB.JTF_DR_rec_type DEFAULT pkg_dr_rec, -- Added by SBARAT on 01/11/2004 for Enh-3919046
1143 p_business_process_id IN NUMBER DEFAULT NULL,
1144 p_business_process_date IN DATE DEFAULT NULL,
1145 x_excluded_resouurce_tbl OUT NOCOPY JTF_ASSIGN_PUB.excluded_tbl_type,
1146 x_return_status OUT NOCOPY VARCHAR2,
1150
1147 x_msg_count OUT NOCOPY NUMBER,
1148 x_msg_data OUT NOCOPY VARCHAR2
1149 );
1151 -- this is a wrapper for get_available_resource
1152 -- this is to be used only from AM UI to get the available slots for the resources fetched in
1153 -- Unassisted mode
1154 PROCEDURE GET_RESOURCE_AVAILABILITY
1155 ( p_api_version IN NUMBER,
1156 p_init_msg_list IN VARCHAR2 DEFAULT JTF_ASSIGN_PUB.AM_FALSE,
1157 p_commit IN VARCHAR2 DEFAULT JTF_ASSIGN_PUB.AM_FALSE,
1158 p_calendar_flag IN VARCHAR2,
1159 p_effort_duration IN NUMBER,
1160 p_effort_uom IN VARCHAR2,
1161 p_breakdown IN NUMBER,
1162 p_breakdown_uom IN VARCHAR2,
1163 p_planned_start_date IN DATE,
1164 p_planned_end_date IN DATE,
1165 p_continuous_task IN VARCHAR2 DEFAULT 'N',
1166 x_return_status IN OUT NOCOPY VARCHAR2,
1167 x_msg_count IN OUT NOCOPY NUMBER,
1168 x_msg_data IN OUT NOCOPY VARCHAR2,
1169 x_assign_resources_tbl IN OUT NOCOPY JTF_ASSIGN_PUB.AssignResources_tbl_type
1170 );
1171
1172 END JTF_ASSIGN_PUB;
[Home] [Help]