1 PACKAGE AHL_VWP_RULES_PVT AS
2 /* $Header: AHLVRULS.pls 120.1 2007/12/28 08:09:41 rnahata ship $ */
3 -----------------------------------------------------------
4 -- PACKAGE
5 -- AHL_VWP_RULES_PVT
6 --
7 -- PURPOSE
8 -- This package specification is a Private API for managing
9 -- Planning --> Visit Work Package --> VISIT, TASKS, PROJECT, PRODUCTION, PRICING, COSTING
10 -- related RULES as procedures in Complex Maintainance, Repair and Overhauling(CMRO).
11 --
12 -- It defines used pl/sql records and tables datatypes
13 --
14 -- Check_Serial_No_by_UConfig (see below for specification)
15 -- Check_Item_name_Or_Id (see below for specification)
16 -- Check_Serial_name_Or_Id (see below for specification)
17 -- Check_SR_request_number_Or_Id (see below for specification)
18 -- Check_Visit_Task_Number_Or_Id (see below for specification)
19 -- Check_Lookup_name_Or_Id (see below for specification)
20 -- Check_Org_Name_Or_Id (see below for specification)
21 -- Check_Dept_Desc_Or_Id (see below for specification)
22 -- Check_Visit_is_Simulated (see below for specification)
23 -- Check_Project_Template_Or_Id (see below for specification)
24 -- Check_Proj_Responsibility (see below for specification)
25 -- Check_Cost_Parent_Loop (see below for specification)
26 -- Check_Origin_Task_Loop (see below for specification)
27
28 -- Create_Tasks_for_MR (see below for specification)
29 -- Get_Serial_Item_by_Unit (see below for specification)
30 -- Tech_Dependency (see below for specification)
31 -- Insert_Tasks (see below for specification)
32
33 -- FUNCTION
34 -- Get_Cost_Originating_Id (see below for specification)
35 -- Get_Visit_Task_Id (see below for specification)
36 -- Get_Visit_Task_Number (see below for specification)
37 --
38 -- NOTES
39 --
40 --
41 -- HISTORY
42 -- 03-MAY-2003 shbhanda Created.
43 -- 06-AUG-2003 SHBHANDA 11.5.10 Changes.
44 --------------------------------------------------------------------
45
46 ---------------------------------------------------------------------
47 -- Define Record Types for record structures needed by the APIs --
48 ---------------------------------------------------------------------
49 -- Record for Visit Task
50 TYPE Task_Rec_Type IS RECORD (
51 VISIT_TASK_ID NUMBER := NULL,
52 VISIT_TASK_NUMBER NUMBER := NULL,
53 VISIT_ID NUMBER := NULL,
54 TEMPLATE_FLAG VARCHAR2(1) := 'N',
55 INVENTORY_ITEM_ID NUMBER := NULL,
56 ITEM_ORGANIZATION_ID NUMBER := NULL,
57 ITEM_NAME VARCHAR2(40) := NULL,
58 COST_PARENT_ID NUMBER := NULL,
59 COST_PARENT_NUMBER NUMBER := NULL,
60 MR_ROUTE_ID NUMBER := NULL,
61 ROUTE_NUMBER VARCHAR2(30) := NULL,
62 MR_ID NUMBER := NULL,
63 MR_TITLE VARCHAR2(80) := NULL,
64 UNIT_EFFECTIVITY_ID NUMBER := NULL,
65 DEPARTMENT_ID NUMBER := NULL,
66 DEPT_NAME VARCHAR2(240) := NULL,
67 SERVICE_REQUEST_ID NUMBER := NULL,
68 SERVICE_REQUEST_NUMBER VARCHAR2(30) := NULL,
69 ORIGINATING_TASK_ID NUMBER := NULL,
70 ORGINATING_TASK_NUMBER NUMBER := NULL,
71 INSTANCE_ID NUMBER := NULL,
72 SERIAL_NUMBER VARCHAR2(30) := NULL,
73 PROJECT_TASK_ID NUMBER := NULL,
74 PROJECT_TASK_NUMBER NUMBER := NULL,
75 PRIMARY_VISIT_TASK_ID NUMBER := NULL,
76 START_FROM_HOUR NUMBER := NULL,
77 DURATION NUMBER := NULL,
78 TASK_TYPE_CODE VARCHAR2(30) := 'UNASSOCIATED',
79 TASK_TYPE_VALUE VARCHAR2(80) := NULL,
80 VISIT_TASK_NAME VARCHAR2(80) := NULL,
81 DESCRIPTION VARCHAR2(4000) := NULL,
82 TASK_STATUS_CODE VARCHAR2(30) := NULL,
83 TASK_STATUS_VALUE VARCHAR2(80) := NULL,
84 OBJECT_VERSION_NUMBER NUMBER := NULL,
85 LAST_UPDATE_DATE DATE := NULL,
86 LAST_UPDATED_BY NUMBER := NULL,
87 CREATION_DATE DATE := NULL,
88 CREATED_BY NUMBER := NULL,
89 LAST_UPDATE_LOGIN NUMBER := NULL,
90 ATTRIBUTE_CATEGORY VARCHAR2(30) := NULL,
91 ATTRIBUTE1 VARCHAR2(150) := NULL,
92 ATTRIBUTE2 VARCHAR2(150) := NULL,
93 ATTRIBUTE3 VARCHAR2(150) := NULL,
94 ATTRIBUTE4 VARCHAR2(150) := NULL,
95 ATTRIBUTE5 VARCHAR2(150) := NULL,
96 ATTRIBUTE6 VARCHAR2(150) := NULL,
97 ATTRIBUTE7 VARCHAR2(150) := NULL,
98 ATTRIBUTE8 VARCHAR2(150) := NULL,
99 ATTRIBUTE9 VARCHAR2(150) := NULL,
100 ATTRIBUTE10 VARCHAR2(150) := NULL,
101 ATTRIBUTE11 VARCHAR2(150) := NULL,
102 ATTRIBUTE12 VARCHAR2(150) := NULL,
103 ATTRIBUTE13 VARCHAR2(150) := NULL,
104 ATTRIBUTE14 VARCHAR2(150) := NULL,
105 ATTRIBUTE15 VARCHAR2(150) := NULL,
106 TASK_START_DATE DATE := NULL,
107 TASK_END_DATE DATE := NULL,
108 DUE_BY_DATE DATE := NULL,
109 ZONE_NAME VARCHAR2(30) := NULL,
110 SUB_ZONE_NAME VARCHAR2(30) := NULL,
111 TOLERANCE_AFTER NUMBER := NULL,
112 TOLERANCE_BEFORE NUMBER := NULL,
113 TOLERANCE_UOM VARCHAR2(50) := NULL,
114 WORKORDER_ID NUMBER := NULL,
115 WO_NAME VARCHAR2(255) := NULL,
116 WO_STATUS VARCHAR2(30) := NULL,
117 WO_START_DATE DATE := NULL,
118 WO_END_DATE DATE := NULL,
119 OPERATION_FLAG VARCHAR2(2) := NULL,
120 IS_PRODUCTION_FLAG VARCHAR2(1) := NULL,
121 CREATE_JOB_FLAG VARCHAR2(1) := NULL,
122 STAGE_ID NUMBER := NULL,
123 STAGE_NAME VARCHAR2(80) := NULL,
124 --Begin changes by rnahata for Issue 105
125 QUANTITY NUMBER := NULL,
126 UOM CSI_ITEM_INSTANCES.UNIT_OF_MEASURE%TYPE := NULL,
127 INSTANCE_NUMBER CSI_ITEM_INSTANCES.INSTANCE_NUMBER%TYPE := NULL
128 --End changes by rnahata for Issue 105
129 );
130
131 -- Record of Maintainance Requirements Id and Serial Id
132 -- while importing technical dependency
133 TYPE MR_Serial_Rec_Type IS RECORD (
134 MR_ID NUMBER := NULL,
135 SERIAL_ID NUMBER := NULL
136 );
137
138 -- Record of Items which are filtered to only unique item among all items
139 TYPE Item_Rec_Type IS RECORD (
140 Item_Id NUMBER := NULL,
141 Visit_Task_Id NUMBER := NULL,
142 Quantity NUMBER := NULL,
143 Duration NUMBER := NULL,
144 Effective_Date DATE := NULL,
145 UOM_Code VARCHAR2(3) := NULL
146 );
147 ---------------------------------------------------------------------
148 -- Define Table Types for table structures of records needed by the APIs --
149 ---------------------------------------------------------------------
150 -- Table type for storing 'MR_Serial_Rec_Type' record datatype
151 TYPE MR_Serial_Tbl_Type IS TABLE OF MR_Serial_Rec_Type
152 INDEX BY BINARY_INTEGER;
153
154 -- Table type for storing 'Item_Rec_Type' record datatype
155 TYPE Item_Tbl_Type IS TABLE OF Item_Rec_Type
156 INDEX BY BINARY_INTEGER;
157
158 TYPE Task_Tbl_Type IS TABLE OF Task_Rec_Type
159 INDEX BY BINARY_INTEGER;
160
161 --------------------------------------------------------------------
162 -- Procedure name : Check_Item_Name_Or_Id
163 -- Type : Private
164 -- Purpose : To check if Item Name,Item Id and Item Organization Id exits.
165 -- Parameters :
166 --
167 -- Standard OUT Parameters :
168 -- x_return_status OUT VARCHAR2 Required
169 -- x_error_msg_code OUT VARCHAR2 Required
170 --
171 -- Check_Item_Name_Or_Id IN Parameters:
172 -- p_item_id IN NUMBER Required,
173 -- p_org_id IN NUMBER Required,
174 -- p_item_name IN VARCHAR2 Required,
175 --
176 -- Check_Item_Name_Or_Id OUT Parameters:
177 -- x_item_id OUT NUMBER Required,
178 -- x_org_id OUT NUMBER Required,
179 -- x_item_name OUT VARCHAR2 Required,
180 --
181 -- Version :
182 -- Initial Version 1.0
183 --------------------------------------------------------------------
184 PROCEDURE Check_Item_Name_Or_Id
185 (p_item_id IN NUMBER,
186 p_org_id IN NUMBER,
187 p_item_name IN VARCHAR2,
188
189 x_item_id OUT NOCOPY NUMBER,
190 x_org_id OUT NOCOPY NUMBER,
191 x_item_name OUT NOCOPY VARCHAR2,
192 x_return_status OUT NOCOPY VARCHAR2,
193 x_error_msg_code OUT NOCOPY VARCHAR2
194 );
195
196 --------------------------------------------------------------------
197 -- Procedure name : Check_Serial_Name_Or_Id
198 -- Type : Private
199 -- Purpose : Converts Serial Number to Instance Id.
200 -- Parameters :
201 --
202 -- Standard OUT Parameters :
203 -- x_return_status OUT VARCHAR2 Required
204 -- x_error_msg_code OUT VARCHAR2 Required
205 --
206 -- Check_Serial_Name_Or_Id IN Parameters:
207 -- p_item_id IN NUMBER Required,
208 -- p_org_id IN NUMBER Required,
209 -- p_serial_id IN NUMBER Required,
210 -- p_serial_number IN VARCHAR2 Required,
211 --
212 -- Check_Serial_Name_Or_Id OUT Parameters:
213 -- x_serial_id OUT NUMBER Required,
214 --
215 -- Version :
216 -- Initial Version 1.0
217 --
218 -------------------------------------------------------------------
219 PROCEDURE Check_Serial_Name_Or_Id
220 (p_item_id IN NUMBER,
221 p_org_id IN NUMBER,
222 p_serial_id IN NUMBER,
223 p_serial_number IN VARCHAR2,
224
225 x_serial_id OUT NOCOPY NUMBER,
226 x_return_status OUT NOCOPY VARCHAR2,
227 x_error_msg_code OUT NOCOPY VARCHAR2
228 );
229
230 --------------------------------------------------------------------
231 -- Procedure name : Check_Org_Name_Or_Id
232 -- Type : Private
233 -- Purpose : To Converts Organization Name to Organization ID.
234 -- Parameters :
235 --
236 -- Standard OUT Parameters :
237 -- x_return_status OUT VARCHAR2 Required
238 -- x_error_msg_code OUT VARCHAR2 Required
239 --
240 -- Check_Org_Name_Or_Id IN Parameters:
241 -- p_organization_id IN NUMBER Required,
242 -- p_org_name IN VARCHAR2 Required,
243 --
244 -- Check_Org_Name_Or_Id OUT Parameters:
245 -- x_organization_id OUT NUMBER Required,
246 --
247 -- Version :
248 -- Initial Version 1.0
249 --------------------------------------------------------------------
250 PROCEDURE Check_Org_Name_Or_Id
251 (p_organization_id IN NUMBER,
252 p_org_name IN VARCHAR2,
253
254 x_organization_id OUT NOCOPY NUMBER,
255 x_return_status OUT NOCOPY VARCHAR2,
256 x_error_msg_code OUT NOCOPY VARCHAR2
257 );
258
259 --------------------------------------------------------------------
260 -- Procedure name : Check_Dept_Desc_Or_Id
261 -- Type : Private
262 -- Purpose : Converts Department Description to Department ID
263 -- Parameters :
264 --
265 -- Standard OUT Parameters :
266 -- x_return_status OUT VARCHAR2 Required
267 -- x_error_msg_code OUT VARCHAR2 Required
268 --
269 -- Check_Dept_Desc_Or_Id IN Parameters:
270 -- p_organization_id IN NUMBER Required,
271 -- p_department_id IN NUMBER Required,
272 -- p_dept_name IN NUMBER Required,
273 --
274 -- Check_Dept_Desc_Or_Id OUT Parameters:
275 -- x_department_id OUT NUMBER Required,
276 --
277 -- Version :
278 -- Initial Version 1.0
279 --------------------------------------------------------------------
280 PROCEDURE Check_Dept_Desc_Or_Id
281 (p_organization_id IN NUMBER,
282 p_department_id IN NUMBER,
283 p_dept_name IN VARCHAR2,
284
285 x_department_id OUT NOCOPY NUMBER,
286 x_return_status OUT NOCOPY VARCHAR2,
287 x_error_msg_code OUT NOCOPY VARCHAR2
288 );
289
290 --------------------------------------------------------------------
291 -- Procedure name : Check_SR_Request_Number_Or_Id
292 -- Type : Private
293 -- Purpose : To Converts Servie request Number to ID or Vice versa.
294 -- Parameters :
295 --
296 -- Standard OUT Parameters :
297 -- x_return_status OUT VARCHAR2 Required
298 -- x_error_msg_code OUT VARCHAR2 Required
299 --
300 -- Check_SR_Request_Number_Or_Id IN Parameters:
301 -- p_service_id IN NUMBER Required,
302 -- p_service_number IN NUMBER Required,
303 --
304 -- Check_SR_Request_Number_Or_Id OUT Parameters:
305 -- x_service_id OUT NUMBER Required,
306 --
307 -- Version :
308 -- Initial Version 1.0
309 --------------------------------------------------------------------
310 PROCEDURE Check_SR_Request_Number_Or_Id
311 (p_service_id IN NUMBER,
312 p_service_number IN VARCHAR2,
313
314 x_service_id OUT NOCOPY NUMBER,
315 x_return_status OUT NOCOPY VARCHAR2,
316 x_error_msg_code OUT NOCOPY VARCHAR2
317 );
318
319 --------------------------------------------------------------------
320 -- Procedure name : Check_Visit_is_Simulated
321 -- Type : Private
322 -- Purpose : To check if the visit is simulated or not.
323 -- Parameters :
324 --
325 -- Standard OUT Parameters :
326 -- x_return_status OUT VARCHAR2 Required
327 -- x_error_msg_code OUT VARCHAR2 Required
328 --
332 -- Check_Visit_is_Simulated OUT Parameters:
329 -- Check_Visit_is_Simulated IN Parameters:
330 -- p_Visit_id IN NUMBER Required,
331 --
333 -- x_bln_flag OUT VARCHAR2 Required,
334 --
335 -- Version :
336 -- Initial Version 1.0
337 --------------------------------------------------------------------
338 PROCEDURE Check_Visit_is_Simulated
339 (p_Visit_id IN NUMBER,
340
341 x_bln_flag OUT NOCOPY VARCHAR2,
342 x_return_status OUT NOCOPY VARCHAR2,
343 x_error_msg_code OUT NOCOPY VARCHAR2
344 );
345
346 --------------------------------------------------------------------
347 -- Procedure name : Check_Project_Template_Or_Id
348 -- Type : Private
349 -- Purpose : To check project template name and retrieve project id
350 -- Parameters :
351 --
352 -- Standard OUT Parameters :
353 -- x_return_status OUT VARCHAR2 Required
354 -- x_error_msg_code OUT VARCHAR2 Required
355 --
356 -- Check_Project_Template_Or_Id IN Parameters:
357 -- p_proj_temp_name IN VARCHAR2 Required,
358 --
359 -- Check_Project_Template_Or_Id OUT Parameters:
360 -- x_project_id OUT NUMBER Required,
361 --
362 -- Version :
363 -- Initial Version 1.0
364 --------------------------------------------------------------------
365 PROCEDURE Check_Project_Template_Or_Id
366 (p_proj_temp_name IN VARCHAR2,
367 x_project_id OUT NOCOPY NUMBER,
368 x_return_status OUT NOCOPY VARCHAR2,
369 x_error_msg_code OUT NOCOPY VARCHAR2
370 );
371
372 --------------------------------------------------------------------
373 -- Procedure name : Check_Serial_No_by_UConfig
374 -- Type : DEPRECATED-- Code removed in POST11510
375 -- Purpose : To derive the serial numbers that are part
376 -- of unit configuration of the item
377 -- Parameters :
378 --
379 -- Standard OUT Parameters :
380 -- x_return_status OUT VARCHAR2 Required
381 -- x_error_msg_code OUT VARCHAR2 Required
382 --
383 -- Check_Serial_No_by_UConfig IN Parameters:
384 -- p_visit_id IN NUMBER Required,
385 -- p_item_id IN NUMBER Required,
386 -- p_serial_id IN NUMBER Required,
387 -- p_org_id IN NUMBER Required,
388 --
389 -- Check_Serial_No_by_UConfig OUT Parameters:
390 -- x_check_flag OUT VARCHAR2 Required,
391 --
392 -- Version :
393 -- Initial Version 1.0
394 --------------------------------------------------------------------
395 /*PROCEDURE Check_Serial_No_by_UConfig
396 (p_visit_id IN NUMBER,
397 p_item_id IN NUMBER,
398 p_org_id IN NUMBER,
399 p_serial_id IN NUMBER,
400 x_check_flag OUT NOCOPY VARCHAR2,
401 x_return_status OUT NOCOPY VARCHAR2,
402 x_error_msg_code OUT NOCOPY VARCHAR2
403 );*/
404 -----------------------------------------------------------------------
405 -- FUNCTION
406 -- instance_in_config_tree
407 --
408 -- PURPOSE
409 -- Check whether p_instance_id belongs to the instance of p_visit_id
410 -- Return 'Y' for the following cases:
411 -- 1. p_visit_id doesn't have instance_id associated at all
412 -- 2. The instance_id of p_visit_id = p_instance_id regardless whether
413 -- the instance of p_visit_id has components or not
414 -- 3. p_instance_id is a component of the instance of p_visit_id regardless
415 -- whether it is a UC tree or IB tree
416 -- Return 'N' otherwise
417 -----------------------------------------------------------------------
418 FUNCTION instance_in_config_tree(p_visit_id NUMBER, p_instance_id NUMBER)
419 RETURN VARCHAR2;
420
421 --------------------------------------------------------------------
422 -- Procedure name : Check_Visit_Task_Number_OR_Id
423 -- Type : Private
424 -- Purpose : To convert Visit Task Number to Id or Vice versa
425 -- Parameters :
426 --
427 -- Standard OUT Parameters :
428 -- x_return_status OUT VARCHAR2 Required
429 -- x_error_msg_code OUT VARCHAR2 Required
430 --
431 -- Check_Visit_Task_Number_OR_Id IN Parameters:
432 -- p_visit_task_id IN NUMBER Required,
433 -- p_visit_task_number IN NUMBER Required,
434 -- p_visit_id IN NUMBER Required
435
436 -- Check_Visit_Task_Number_OR_Id OUT Parameters:
437 -- x_visit_task_id OUT NUMBER Required,
438 --
439 -- Version :
440 -- Initial Version 1.0
441 --------------------------------------------------------------------
442 PROCEDURE Check_Visit_Task_Number_Or_Id
443 (p_visit_task_id IN NUMBER,
444 p_visit_task_number IN NUMBER,
445 p_visit_id IN NUMBER,
446
447 x_visit_task_id OUT NOCOPY NUMBER,
448 x_return_status OUT NOCOPY VARCHAR2,
449 x_error_msg_code OUT NOCOPY VARCHAR2
450 );
451
452 --------------------------------------------------------------------
453 -- Procedure name : Check_Lookup_Name_Or_Id
454 -- Type : Private
455 -- Purpose : To derive the lookup code and values
456 -- Parameters :
457 --
461 --
458 -- Standard OUT Parameters :
459 -- x_return_status OUT VARCHAR2 Required
460 -- x_error_msg_code OUT VARCHAR2 Required
462 -- Check_Lookup_Name_Or_Id IN Parameters:
463 -- p_lookup_type IN VARCHAR2 Required,
464 -- p_lookup_code IN VARCHAR2 Required,
465 -- p_meaning IN VARCHAR2 Required,
466 -- p_check_id_flag IN VARCHAR2 Required
467 --
468 -- Check_Lookup_Name_Or_Id OUT Parameters:
469 -- x_lookup_code OUT VARCHAR2 Required,
470 --
471 -- Version :
472 -- Initial Version 1.0
473 --------------------------------------------------------------------
474 PROCEDURE Check_Lookup_Name_Or_Id
475 ( p_lookup_type IN FND_LOOKUPS.lookup_type%TYPE,
476 p_lookup_code IN FND_LOOKUPS.lookup_code%TYPE,
477 p_meaning IN FND_LOOKUPS.meaning%TYPE,
478 p_check_id_flag IN VARCHAR2,
479 x_lookup_code OUT NOCOPY VARCHAR2,
480 x_return_status OUT NOCOPY VARCHAR2);
481
482
483 --------------------------------------------------------------------
484 -- Procedure name : Check_Project_Responsibilities
485 -- Type : Private
486 -- Purpose : To verify project superuser reponsibilities
487 -- Parameters :
488 --
489 -- Standard OUT Parameters :
490 -- x_return_status OUT VARCHAR2 Required
491 -- x_error_msg_code OUT VARCHAR2 Required
492 --
493 -- Check_Project_Responsibilities OUT Parameters:
494 -- x_check_project OUT VARCHAR2 Required,
495 --
496 -- Version :
497 -- Initial Version 1.0
498 --------------------------------------------------------------------
499 PROCEDURE Check_Proj_Responsibility
500 ( x_check_project OUT NOCOPY VARCHAR2,
501 x_return_status OUT NOCOPY VARCHAR2);
502
503 --------------------------------------------------------------------
504 -- Procedure name : Get_Serial_Item_by_Unit
505 -- Type : Private
506 -- Purpose : To derive the Serial Id and Inventory Item Id from Unit Name
507 -- Parameters :
508 --
509 -- Standard OUT Parameters :
510 -- x_return_status OUT VARCHAR2 Required
511 -- x_error_msg_code OUT VARCHAR2 Required
512 --
513 -- Get_Serial_Item_by_Unit IN Parameters:
514 -- p_unit_name IN VARCHAR2 Required,
515 --
516 -- Get_Serial_Item_by_Unit OUT Parameters:
517 -- x_instance_id OUT NUMBER Required,
518 -- x_item_id OUT NUMBER Required,
519 -- x_item_org_id OUT NUMBER Required,
520 --
521 -- Version :
522 -- Initial Version 1.0
523 --------------------------------------------------------------------
524 PROCEDURE Get_Serial_Item_by_Unit
525 ( p_unit_name IN VARCHAR2,
526 x_instance_id OUT NOCOPY NUMBER,
527 x_item_id OUT NOCOPY NUMBER,
528 x_item_org_id OUT NOCOPY NUMBER,
529 x_return_status OUT NOCOPY VARCHAR2,
530 x_error_msg_code OUT NOCOPY VARCHAR2);
531
532 --------------------------------------------------------------------
533 -- Procedure name : Get_Cost_Originating_Id
534 -- Type : Private
535 -- Purpose : To derive the parent MR from the current MR and root MR.
536 -- for deriving cost parent and originating task ID's while creating
537 -- planned/unplanned tasks..
538 -- Parameters :
539 --
540 -- Get_Cost_Originating_Id IN Parameters:
541 -- p_mr_main_id IN NUMBER Required,
542 -- p_mr_header_id IN NUMBER Required,
543 --
544 -- Get_Cost_Originating_Id OUT Parameters:
545 -- x_parent_id OUT NUMBER Required,
546 --
547 -- Version :
548 -- Initial Version 1.0
549 --------------------------------------------------------------------
550 FUNCTION Get_Cost_Originating_Id (p_mr_main_id IN NUMBER, p_mr_header_id IN NUMBER)
551 RETURN NUMBER;
552
553 --------------------------------------------------------------------
554 -- Procedure name : Get_Visit_Task_Id
555 -- Type : Private
556 -- Purpose : To derive the primary attribute visit_task_id
557 -- from the task entity
558 -- Parameters :
559 --
560 -- Get_Visit_Task_Id OUT Parameters:
561 -- x_Visit_Task_Id OUT NUMBER Required,
562 --
563 -- Version :
564 -- Initial Version 1.0
565 --------------------------------------------------------------------
566 FUNCTION Get_Visit_Task_Id
567 RETURN NUMBER;
568
569 --------------------------------------------------------------------
570 -- Procedure name : Get_Visit_Task_Number
571 -- Type : Private
572 -- Purpose : To derive the attribute visit_task_number from the task entity.
573 -- Parameters :
574 --
575 -- Get_Cost_Originating_Id IN Parameters:
576 -- p_visit_id IN NUMBER Required,
577 --
578 -- Get_Visit_Task_Number OUT Parameters:
579 -- x_Visit_Task_Number OUT NUMBER Required,
580 --
581 -- Version :
582 -- Initial Version 1.0
583 --------------------------------------------------------------------
584 FUNCTION Get_Visit_Task_Number (p_visit_id IN NUMBER)
585 RETURN NUMBER;
586
590 -- Purpose : Called from Creation of Planned/Unplanned tasks
587 --------------------------------------------------------------------
588 -- Procedure name : Insert_Tasks
589 -- Type : Private
591 -- under various other circumstances
592 -- Parameters :
593 --
594 -- Standard OUT Parameters :
595 -- x_return_status OUT VARCHAR2 Required
596 -- x_msg_count OUT VARCHAR2 Required
597 -- x_msg_data OUT VARCHAR2 Required
598 --
599 -- Insert_Tasks IN Parameters:
600 -- p_visit_id IN NUMBER Required,
601 -- p_unit_id IN NUMBER Required,
602 -- p_serial_id IN NUMBER Required,
603 -- p_service_id IN NUMBER Required,
604 -- p_dept_id IN NUMBER Required,
605 -- p_item_id IN NUMBER Required,
606 -- p_item_org_id IN NUMBER Required,
607 -- p_mr_id IN NUMBER Required,
608 -- p_mr_route_id IN NUMBER Not Required,
609 -- p_parent_id IN NUMBER Required,
610 -- p_flag IN VARHCAR2 Required,
611 --
612 -- Insert_Tasks OUT Parameters:
613 -- x_task_id OUT NUMBER Required,
614 --
615 -- Version :
616 -- Initial Version 1.0
617 --------------------------------------------------------------------
618 PROCEDURE Insert_Tasks (
619 p_visit_id IN NUMBER,
620 p_unit_id IN NUMBER,
621 p_serial_id IN NUMBER,
622 p_service_id IN NUMBER,
623 p_dept_id IN NUMBER,
624 p_item_id IN NUMBER,
625 p_item_org_id IN NUMBER,
626 p_mr_id IN NUMBER,
627 p_mr_route_id IN NUMBER,
628 p_parent_id IN NUMBER,
629 p_flag IN VARCHAR2,
630 p_stage_id IN NUMBER := NULL,
631 p_quantity IN NUMBER := NULL, -- Added by rnahata for Issue 105
632 x_task_id OUT NOCOPY NUMBER,
633 x_return_status OUT NOCOPY VARCHAR2,
634 x_msg_count OUT NOCOPY NUMBER,
635 x_msg_data OUT NOCOPY VARCHAR2
636 );
637
638 --------------------------------------------------------------------
639 -- Procedure name : Tech_Dependency
640 -- Type : Private
641 -- Purpose : To associated Technical dependency while creating Planned/Unplanned Tasks.
642 -- Parameters :
643 --
644 -- Standard OUT Parameters :
645 -- x_return_status OUT VARCHAR2 Required
646 --
647 -- Tech_Dependency Parameters:
648 -- p_visit_id IN NUMBER Required,
649 -- p_MR_Serial_Tbl OUT MR_Serial_Tbl_Type Required,
650 --
651 -- Version :
652 -- Initial Version 1.0
653 --
654 --------------------------------------------------------------------
655 PROCEDURE Tech_Dependency (
656 p_visit_id IN NUMBER,
657 p_task_type IN VARCHAR2,
658 p_MR_Serial_Tbl IN MR_Serial_Tbl_Type,
659 x_return_status OUT NOCOPY VARCHAR2);
660
661 --------------------------------------------------------------------
662 -- Procedure name : Create_Tasks_for_MR
663 -- Type : Private
664 -- Purpose : To create Tasks for MR.
665 -- Parameters :
666 --
667 -- Standard OUT Parameters :
668 -- x_return_status OUT VARCHAR2 Required
669 -- Create_Tasks_for_MR Parameters:
670 -- p_department_id IN NUMBER Required,
671 -- p_visit_id IN NUMBER Required,
672 -- p_serial_id IN NUMBER Required,
673 -- p_mr_id IN NUMBER Required,
674 -- p_unit_id IN NUMBER Required,
675 -- p_service_req_id IN NUMBER Required,
676 -- p_x_parent_MR_Id IN OUT NUMBER Required
677 --
678 -- Version :
679 -- Initial Version 1.0
680 --
681 --------------------------------------------------------------------
682 PROCEDURE Create_Tasks_for_MR (
683 p_visit_id IN NUMBER,
684 p_unit_id IN NUMBER,
685 p_item_id IN NUMBER,
686 p_org_id IN NUMBER,
687 p_serial_id IN NUMBER,
688 p_mr_id IN NUMBER,
689 p_department_id IN NUMBER,
690 p_service_req_id IN NUMBER,
691 -- Added by rnahata for Issue 105
692 p_quantity IN NUMBER,
693 p_x_parent_MR_Id IN OUT NOCOPY NUMBER,
694 x_return_status OUT NOCOPY VARCHAR2
695 );
696
697 --------------------------------------------------------------------
698 -- Procedure name : Check_Cost_Parent_Loop
699 -- Type : Private
700 -- Purpose : To check if the cost parent task not forming loop among other tasks
701 -- Parameters :
702 --
703 -- Check_Cost_Parent_Loop IN Parameters :
704 -- p_visit_id IN NUMBER Required
705 -- p_visit_task_id IN NUMBER Required
706 -- p_cost_parent_id IN NUMBER Required
707 --
708 -- Version :
709 -- Initial Version 1.0
710 --
711 --------------------------------------------------------------------
712 PROCEDURE Check_Cost_Parent_Loop
713 ( p_visit_id IN NUMBER,
714 p_visit_task_id IN NUMBER,
715 p_cost_parent_id IN NUMBER
716 );
717
721 -- Purpose : To check if the originating task not forming loop among other tasks
718 --------------------------------------------------------------------
719 -- Procedure name : Check_Origin_Task_Loop
720 -- Type : Private
722 -- Parameters :
723 --
724 -- Check_Origin_Task_Loop IN Parameters :
725 -- p_visit_id IN NUMBER Required
726 -- p_visit_task_id IN NUMBER Required
727 -- p_originating_task_id IN NUMBER Required
728 --
729 -- Version :
730 -- Initial Version 1.0
731 --
732 --------------------------------------------------------------------
733 PROCEDURE Check_Origin_Task_Loop
734 ( p_visit_id IN NUMBER,
735 p_visit_task_id IN NUMBER,
736 p_originating_task_id IN NUMBER);
737
738 --------------------------------------------------------------------
739 -- PROCEDURE : Update_Visit_Task_Flag
740 -- Type : Private
741 -- Purpose : To update visit entity any_task_chg_flag attribute whenever there
742 -- are changes in visit task - either addition or deletion or change
743 -- in cost parent of any task
744 -- Parameters :
745 --
746 -- Update_Visit_Task_Flag IN Parameters :
747 -- p_visit_id IN NUMBER Required
748 -- p_flag_id IN NUMBER Required
749 --
750 -- Update_Visit_Task_Flag OUT Parameters :
751 -- x_return_status OUT VARCAHR2 Required
752 --
753 -- Version :
754 -- Initial Version 1.0
755 --
756 --------------------------------------------------------------------
757 PROCEDURE Update_Visit_Task_Flag
758 (p_visit_id IN NUMBER,
759 p_flag IN VARCHAR2,
760 x_return_status OUT NOCOPY VARCHAR2
761 );
762
763 --------------------------------------------------------------------
764 -- PROCEDURE
765 -- Check_Price_List_Name_Or_Id
766 --
767 -- PURPOSE
768 -- To find out price list id for price list name for a visit or tasks
769 -- PROCEDURE : Update_Visit_Task_Flag
770 -- Type : Private
771 -- Purpose : To update visit entity any_task_chg_flag attribute whenever there
772 -- are changes in visit task - either addition or deletion or change
773 -- in cost parent of any task
774 -- Parameters :
775 --
776 -- Check_Price_List_Name_Or_Id IN Parameters :
777 -- p_visit_id IN NUMBER Required
778 -- p_flag_id IN NUMBER Required
779 --
780 -- Check_Price_List_Name_Or_Id OUT Parameters :
781 -- x_return_status OUT VARCAHR2 Required
782 --
783 -- Version :
784 -- Initial Version 1.0
785 --
786 --------------------------------------------------------------------
787 PROCEDURE Check_Price_List_Name_Or_Id(
788 p_visit_id IN NUMBER,
789 p_price_list_name IN VARCHAR2,
790 x_price_list_id OUT NOCOPY NUMBER,
791 x_return_status OUT NOCOPY VARCHAR2
792 );
793
794 --------------------------------------------------------------------
795 -- Procedure : Update_Cost_Origin_Task
796 -- Type : Private
797 -- Purpose : To update all tasks which have the deleting task as cost or originating task
798 -- Parameters :
799 --
800 -- Update_Cost_Origin_Task IN Parameters :
801 -- p_visit_task_id IN NUMBER Required
802 --
803 -- Update_Cost_Origin_Task OUT Parameters :
804 -- x_return_status OUT VARCHAR2 Required
805 --
806 -- Version :
807 -- Initial Version 1.0
808 --
809 --------------------------------------------------------------------
810 PROCEDURE Update_Cost_Origin_Task
811 (p_visit_task_id IN NUMBER,
812 x_return_status OUT NOCOPY VARCHAR2
813 );
814
815 --------------------------------------------------------------------
816 -- Procedure : Update_Visit_Task_Flag
817 -- Type : Private
818 -- Purpose : To merge two item tables and remove the redundant items
819 -- in table for which no price is defined
820 -- Parameters :
821 --
822 -- Merge_for_Unique_Items IN Parameters :
823 -- p_item_tbl1 IN Item_Tbl_Type Required
824 -- p_item_tbl2 IN Item_Tbl_Type Required
825 --
826 -- Merge_for_Unique_Items OUT Parameters :
827 -- x_item_tbl OUT Item_Tbl_Type Required
828 --
829 -- Version :
830 -- Initial Version 1.0
831 --
832 --------------------------------------------------------------------
833 PROCEDURE Merge_for_Unique_Items
834 (p_item_tbl1 IN Item_Tbl_Type,
835 p_item_tbl2 IN Item_Tbl_Type,
836 x_item_tbl OUT NOCOPY Item_Tbl_Type
837 );
838
839 --------------------------------------------------------------------
840 -- Procedure : Check_Item_in_Price_List
841 -- Type : Private
842 -- Purpose : To check if item of MR is defined in price list.
843 -- Parameters :
844 --
845 -- Check_Item_in_Price_List IN Parameters :
846 -- p_price_list IN NUMBER Required
847 -- p_item_id IN NUMBER Required
848 --
849 -- Check_Item_in_Price_List OUT Parameters :
850 -- x_item_chk_flag OUT NUMBER Required
851 --
852 -- Version :
853 -- Initial Version 1.0
854 --
855 --------------------------------------------------------------------
856 /*PROCEDURE Check_Item_in_Price_List
857 (p_price_list IN NUMBER,
858 p_item_id IN NUMBER,
859 x_item_chk_flag OUT NOCOPY NUMBER
860 );
861 */
862 --------------------------------------------------------------------
863 -- PROCEDURE : Check_Currency_for_Costing
864 -- Type : Private
865 -- Purpose : To retrieve currency code and pass as input parameter to Pricing API
866 -- Parameters :
867 --
868 -- Check_Currency_for_Costing IN Parameters :
869 -- p_visit_id IN NUMBER Required
870 --
871 -- Check_Currency_for_Costing OUT Parameters :
872 -- x_currency_code OUT VARCHAR2 Required
873 --
874 -- Version :
875 -- Initial Version 1.0
876 --
877 --------------------------------------------------------------------
878 PROCEDURE Check_Currency_for_Costing
879 (p_visit_id IN NUMBER,
880 x_currency_code OUT NOCOPY VARCHAR2
881 );
882
883 --------------------------------------------------------------------
884 -- PROCEDURE : Check_Job_Status
885 -- Type : Private
886 -- Purpose : To find out valid job status on shop floor for a Visit/MR/Task
887 -- Parameters :
888 --
889 -- Check_Job_Status IN Parameters :
890 -- p_id IN NUMBER Required
891 -- p_is_task_flag IN VARCHAR2 Required
892 -- - 'Y' for MR and Task
893 -- - 'N' for Visit
894 -- Check_Job_Status OUT Parameters :
895 -- x_status_code OUT NUMBER Required
896 -- x_status_meaning OUT VARCAHR2 Required
897 --
898 -- Version :
899 -- Initial Version 1.0
900 --
901 --------------------------------------------------------------------
902 PROCEDURE Check_Job_Status
903 (p_id IN NUMBER,
904 p_is_task_flag IN VARCHAR2,
905 x_status_code OUT NOCOPY NUMBER,
906 x_status_meaning OUT NOCOPY VARCHAR2
907 );
908
909 --------------------------------------------------------------------
910 -- PROCEDURE : CHECK_DEPARTMENT_SHIFT
911 -- Type : Private
912 -- Purpose : To find out if the respective dept has shifts defined in ahl_dept_shifts
913 -- Parameters :
914 --
915 -- CHECK_DEPARTMENT_SHIFT IN Parameters :
916 -- p_dept_id IN NUMBER Required
917
918 -- CHECK_DEPARTMENT_SHIFT OUT Parameters :
919 -- x_return_status OUT varchar2 Required
920 --
921 -- Version :
922 -- Initial Version 1.0
923 --
924 --------------------------------------------------------------------
925 PROCEDURE CHECK_DEPARTMENT_SHIFT(
926 p_dept_id IN NUMBER,
927 x_return_status OUT NOCOPY VARCHAR2
928 );
929
930
931 END AHL_VWP_RULES_PVT;
[Home] [Help]