[Home] [Help]
PACKAGE: APPS.AHL_PRD_MRSHL_PVT
Source
1 PACKAGE AHL_PRD_MRSHL_PVT AS
2 /* $Header: AHLVPMLS.pls 120.2.12010000.1 2008/07/28 08:33:36 appldev ship $ */
3
4 TYPE Mrshl_search_rec_type IS RECORD
5 (
6 Visit_id NUMBER,
7 item_instance_id NUMBER,
8 Workorder_id NUMBER,
9 Workorder_name VARCHAR2(80),
10 Item_name VARCHAR2(240),
11 Item_Desc VARCHAR2(240),
12 Search_mode VARCHAR2(30)
13 );
14
15 TYPE Unavailable_items_rec_type IS RECORD
16 (
17 Scheduled_material_id NUMBER,
18 Inventory_item_id NUMBER,
19 Item_name VARCHAR2(240),
20 Item_Desc VARCHAR2(240),
21 Workorder_id NUMBER,
22 Workorder_name VARCHAR2(80),
23 Wip_Entity_Id NUMBER,
24 Organization_id NUMBER,
25 Visit_id NUMBER,
26 wo_status_code VARCHAR2(30),
27 wo_status VARCHAR2(80),
28 Wo_operation_id NUMBER,
29 Op_seq NUMBER,
30 Quantity NUMBER,
31 UOM VARCHAR2(3),
32 UOM_DESC VARCHAR2(25),
33 Required_date DATE,
34 Required_quantity NUMBER,
35 Issued_Quantity NUMBER,
36 Scheduled_date DATE,
37 Scheduled_quantity NUMBER,
38 Exception_date DATE,
39 Reserved_quantity NUMBER,
40 Onhand_quantity NUMBER,
41 Qty_per_assembly NUMBER,
42 Subinventory VARCHAR2(10),
43 Locator_id NUMBER,
44 Locator_segments VARCHAR2(240),
45 Serial_Number VARCHAR2(30),
46 Lot VARCHAR2(30),
47 Revision VARCHAR2(3),
48 Is_serialized VARCHAR2(1),
49 Is_Lot_Controlled VARCHAR2(1),
50 Is_Revision_Controlled VARCHAR2(1),
51 diposition_id NUMBER,
52 diposition_name VARCHAR2(80)
53
54 );
55
56 TYPE Unavailable_items_tbl_type IS TABLE OF Unavailable_items_rec_type INDEX BY BINARY_INTEGER;
57
58 TYPE Available_items_rec_type IS RECORD
59 (
60 Scheduled_material_id NUMBER,
61 Inventory_item_id NUMBER,
62 Item_name VARCHAR2(240),
63 Item_Desc VARCHAR2(240),
64 Workorder_id NUMBER,
65 Workorder_name VARCHAR2(80),
66 Wip_Entity_Id NUMBER,
67 Organization_id NUMBER,
68 Visit_id NUMBER,
69 wo_status_code VARCHAR2(30),
70 wo_status VARCHAR2(80),
71 Wo_operation_id NUMBER,
72 Op_seq NUMBER,
73 Quantity NUMBER,
74 UOM VARCHAR2(3),
75 UOM_DESC VARCHAR2(25),
76 Required_date DATE,
77 Required_quantity NUMBER,
78 Scheduled_date DATE,
79 Scheduled_quantity NUMBER,
80 Issued_Quantity NUMBER,
81 Exception_date DATE,
82 Reserved_quantity NUMBER,
83 Onhand_quantity NUMBER,
84 Qty_per_assembly NUMBER,
85 Subinventory VARCHAR2(10),
86 Locator_id NUMBER,
87 Locator_segments VARCHAR2(240),
88 Serial_Number VARCHAR2(30),
89 Lot VARCHAR2(30),
90 Revision VARCHAR2(3),
91 Item_source_wo_id NUMBER,
92 Item_source_wo_name VARCHAR2(80),
93 Item_source_wop_id NUMBER,
94 Item_source_wop_seq NUMBER,
95 Is_serialized VARCHAR2(1),
96 Is_Lot_Controlled VARCHAR2(1),
97 Is_Revision_Controlled VARCHAR2(1),
98 diposition_id NUMBER,
99 diposition_name VARCHAR2(80)
100 );
101
102 TYPE Available_items_tbl_type IS TABLE OF Available_items_rec_type INDEX BY BINARY_INTEGER;
103
104
105 TYPE mrshl_details_rec_type IS RECORD
106 (
107 Unit_Header_id NUMBER,
108 Unit_Name VARCHAR2(80),
109 relationship_id NUMBER,
110 parent_rel_id NUMBER,
111 root_instance_id NUMBER,
112 POSITION VARCHAR2(240),
113 IS_POSITION_SER_CTRLD VARCHAR2(1),
114 CURR_ITEM_ID NUMBER,
115 CURR_INSTANCE_ID NUMBER,
116 parent_instance_id NUMBER,
117 ALLOWED_QTY NUMBER,
118 CURR_ITEM_NUMBER VARCHAR2(240),
119 CURR_SERIAL_NUMBER VARCHAR2(30),
120 CURR_INSTLD_QTY NUMBER,
121 REQ_QTY NUMBER,
122 ISSUED_QTY NUMBER,
123 AVAILABLE_QTY NUMBER,
124 NOT_AVAILABLE_QTY NUMBER,
125 COMPL_WO_COUNT NUMBER,
126 TOTAL_WO_COUNT NUMBER,
127 CUMM_REQ_QTY NUMBER,
128 CUMM_ISSUED_QTY NUMBER,
129 CUMM_AVAILABLE_QTY NUMBER,
130 CUMM_NOT_AVAILABLE_QTY NUMBER,
131 CUMM_COMPL_WO_COUNT NUMBER,
132 CUMM_TOTAL_WO_COUNT NUMBER
133 );
134
135 TYPE mrshl_details_tbl_type IS TABLE OF mrshl_details_rec_type INDEX BY BINARY_INTEGER;
136
137
138
139 PROCEDURE Get_unavailable_items
140 (
141 p_api_version IN NUMBER := 1.0,
142 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
143 p_commit IN VARCHAR2 := FND_API.G_FALSE,
144 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
145 p_default IN VARCHAR2 := FND_API.G_FALSE,
146 p_module_type IN VARCHAR2 := NULL,
147 p_Mrshl_search_rec IN Mrshl_search_rec_type,
148 x_Unavailable_items_tbl OUT NOCOPY Unavailable_items_Tbl_Type,
149 x_return_status OUT NOCOPY VARCHAR2,
150 x_msg_count OUT NOCOPY NUMBER,
151 x_msg_data OUT NOCOPY VARCHAR2
152 );
153
154 PROCEDURE Get_available_items
155 (
156 p_api_version IN NUMBER := 1.0,
157 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
158 p_commit IN VARCHAR2 := FND_API.G_FALSE,
159 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
160 p_default IN VARCHAR2 := FND_API.G_FALSE,
161 p_module_type IN VARCHAR2 := NULL,
162 p_Mrshl_search_rec IN Mrshl_search_rec_type,
163 x_available_items_tbl OUT NOCOPY Available_items_Tbl_Type,
164 x_return_status OUT NOCOPY VARCHAR2,
165 x_msg_count OUT NOCOPY NUMBER,
166 x_msg_data OUT NOCOPY VARCHAR2
167 );
168
169 FUNCTION Get_workorder_count
170 (
171 p_visit_id IN NUMBER,
172 p_item_instance_id IN NUMBER,
173 p_mode IN VARCHAR2
174 ) RETURN NUMBER;
175
176 FUNCTION Get_item_count
177 (
178 p_visit_id IN NUMBER,
179 p_item_instance_id IN NUMBER :=NULL,
180 p_mode IN VARCHAR2
181 ) RETURN NUMBER;
182
183 FUNCTION Get_visit_completion_perc
184 (
185 p_visit_id IN NUMBER
186 ) RETURN NUMBER;
187
188 PROCEDURE Get_mrshl_details
189 (
190 p_api_version IN NUMBER := 1.0,
191 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
192 p_commit IN VARCHAR2 := FND_API.G_FALSE,
193 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
194 p_default IN VARCHAR2 := FND_API.G_FALSE,
195 p_module_type IN VARCHAR2 := NULL,
196 p_unit_header_id IN NUMBER,
197 p_item_instance_id IN NUMBER,
198 p_visit_id IN NUMBER,
199 x_mrshl_details_tbl OUT NOCOPY mrshl_details_tbl_type,
200 x_return_status OUT NOCOPY VARCHAR2,
201 x_msg_count OUT NOCOPY NUMBER,
202 x_msg_data OUT NOCOPY VARCHAR2
203 );
204
205 FUNCTION GET_ONHAND_AVAILABLE(
206 P_ORG_ID IN NUMBER,
207 P_ITEM_ID IN NUMBER,
208 p_SUBINVENTORY VARCHAR2,
209 p_locator_id NUMBER) RETURN NUMBER;
210
211 FUNCTION GET_ONHAND_NOTAVAILABLE(
212 P_ORG_ID IN NUMBER,
213 P_ITEM_ID IN NUMBER,
214 p_SUBINVENTORY VARCHAR2,
215 p_locator_id NUMBER) RETURN NUMBER;
216
217 END AHL_PRD_MRSHL_PVT;