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