1 PACKAGE AHL_OSP_QUERIES_PVT AUTHID CURRENT_USER AS
2 /* $Header: AHLVOSQS.pls 120.1 2006/08/23 11:27:26 mpothuku noship $ */
3
4 ---------------------------------------------------------------------
5 -- Define Record Types for record structures needed by the APIs --
6 ---------------------------------------------------------------------
7 TYPE Search_Order_Rec_Type IS RECORD (
8 Order_Number VARCHAR2(40) ,
9 Description VARCHAR2(2000),
10 Order_Type_Code VARCHAR2(30) ,
11 Order_Status_Code VARCHAR2(30) ,
12 Job_Number VARCHAR2(80) ,
13 Project_Name VARCHAR2(30) ,
14 Task_Name VARCHAR2(20) ,
15 Part_Number VARCHAR2(40) ,
16 Serial_Number VARCHAR2(30) ,
17 Has_New_PO_Line VARCHAR2(1) ,
18 vendor VARCHAR2(240) ,
19 department_id NUMBER ,
20 department VARCHAR2(10)
21 );
22
23 Type Search_WO_Rec_Type IS RECORD (
24 job_number VARCHAR2(80) ,
25 description VARCHAR2(2000) ,
26 project_name VARCHAR2(30) ,
27 task_name VARCHAR2(20) ,
28 Part_number VARCHAR2(40) ,
29 Instance_number VARCHAR2(30) ,
30 Serial_number VARCHAR2(30) ,
31 Svc_item_number VARCHAR2(40) ,
32 department_id NUMBER ,
33 Department VARCHAR2(10)
34
35 );
36
37
38 Type Results_Order_Rec_Type IS RECORD (
39 OSP_ID NUMBER ,
40 Object_version_number NUMBER ,
41 Order_Number NUMBER ,
42 Order_Date DATE ,
43 Description VARCHAR2(2000) ,
44 order_type_code VARCHAR2(30) ,
45 Order_Type VARCHAR2(80) ,
46 status_code VARCHAR2(30) ,
47 Order_Status VARCHAR2(80) ,
48 po_header_id NUMBER ,
49 PO_Number VARCHAR2(20) ,
50 oe_header_id NUMBER ,
51 Shipment_Number NUMBER ,
52 po_interface_header_id NUMBER
53 );
54
55 Type Results_WO_Rec_Type IS RECORD (
56 Workorder_ID NUMBER ,
57 job_number VARCHAR2(80) ,
58 Part_number VARCHAR2(40) ,
59 Instance_number VARCHAR2(30) ,
60 Serial_number VARCHAR2(30) ,
61 Svc_item_number VARCHAR2(40) ,
62 Svc_Description VARCHAR2(240) ,
63 Suggested_Vendor VARCHAR2(240) ,
64 Department VARCHAR2(10)
65
66 );
67
68 Type order_header_rec_Type IS RECORD(
69 OSP_ID NUMBER,
70 OBJECT_VERSION_NUMBER NUMBER,
71 ORDER_NUMBER NUMBER,
72 ORDER_DESCRIPTION VARCHAR2(1000),
73 ORDER_TYPE_CODE VARCHAR(30),
74 ORDER_TYPE VARCHAR2(80),
75 ORDER_STATUS_CODE VARCHAR2(30),
76 ORDER_STATUS VARCHAR2(80),
77 ORDER_DATE DATE,
78 VENDOR_ID NUMBER,
79 VENDOR_NAME VARCHAR2(240),
80 VENDOR_SITE_ID NUMBER,
81 VENDOR_LOCATION VARCHAR2(15),
82 CUSTOMER_ID NUMBER,
83 CUSTOMER VARCHAR2(360),
84 SINGLE_INSTANCE_FLAG VARCHAR2(20),
85 SINGLE_INSTANCE_MEANING VARCHAR2(80),
86 PO_AGENT_ID NUMBER,
87 BUYER_NAME VARCHAR2(240),
88 PO_HEADER_ID NUMBER,
89 PO_NUMBER VARCHAR2(80),
90 PO_SYNCH_FLAG VARCHAR2(20),
91 OE_HEADER_ID NUMBER,
92 SHIPMENT_NUMBER NUMBER,
93 CONTRACT_ID NUMBER,
94 CONTRACT_NUMBER VARCHAR2(120)
95 );
96
97 Type order_line_rec_type IS RECORD (
98 OSP_ORDER_LINE_ID NUMBER,
99 OBJECT_VERSION_NUMBER NUMBER,
100 OSP_ORDER_ID NUMBER,
101 OSP_LINE_NUMBER NUMBER,
102 STATUS_CODE VARCHAR2(30),
103 STATUS VARCHAR2(80),
104 PO_LINE_TYPE_ID NUMBER,
105 PO_LINE_TYPE VARCHAR2(25),
106 SERVICE_ITEM_ID NUMBER,
107 SERVICE_ITEM_NUMBER VARCHAR2(40),
108 SERVICE_ITEM_DESCRIPTION VARCHAR2(2000),
109 SERVICE_ITEM_UOM_CODE VARCHAR2(3),
110 NEED_BY_DATE DATE,
111 SHIP_BY_DATE DATE,
112 PO_LINE_ID NUMBER,
113 OE_SHIP_LINE_ID NUMBER,
114 OE_RETURN_LINE_ID NUMBER,
115 WORKORDER_ID NUMBER,
116 JOB_NUMBER VARCHAR2(80),
117 OPERATION_ID NUMBER,
118 ATTRIBUTE_CATEGORY VARCHAR2(30),
119 WO_PART_NUMBER VARCHAR2(40),
120 QUANTITY NUMBER,
121 ITEM_INSTANCE_ID NUMBER,
122 ITEM_INSTANCE_NUMBER VARCHAR2(30),
123 EXCHANGE_INSTANCE_NUMBER VARCHAR2(30),
124 EXCHANGE_INSTANCE_ID NUMBER,
125 PROJECT_ID NUMBER,
126 PROJECT_NAME VARCHAR2(30),
127 PRJ_TASK_ID NUMBER,
128 PRJ_TASK_NAME VARCHAR2(20)
129
130
131
132 );
133
134 Type Work_Id_Rec_Type IS RECORD (
135 work_order_id NUMBER
136 );
137 ----------------------------------------------
138 -- Define Table Type for records structures --
139 ----------------------------------------------
140 TYPE Results_Order_Tbl_Type IS TABLE OF Results_Order_Rec_Type INDEX BY BINARY_INTEGER;
141
142 Type Results_WO_Tbl_Type IS TABLE OF Results_WO_Rec_Type INDEX BY BINARY_INTEGER;
143
144 Type order_line_Tbl_type IS TABLE OF order_line_rec_type INDEX BY BINARY_INTEGER;
145
146 Type work_id_tbl_type IS TABLE OF Work_Id_Rec_Type INDEX BY BINARY_INTEGER;
147
148
149 ----------------------------------------
150 -- Declare Procedures for Search OSP --
151 ----------------------------------------
152 -- This procedure Search for osp order based on the search criteria specify in parameter P_search_order_rec
153 -- The search result will be populated into x_results_order_tbl.
154 -- Start of Comments --
155 -- Procedure name : Search_OSP
156 -- Type : Public
157 -- Function : Search OSP Order
158 -- Pre-reqs :
159 -- Parameters :
160 --
161 -- Standard IN Parameters :
162 -- p_api_version IN NUMBER Required
163 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
164 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
165 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
166 -- p_module_type IN VARCHAR2 Default Null
167 -- Standard OUT Parameters :
168 -- x_return_status OUT VARCHAR2 Required
169 -- x_msg_count OUT NUMBER Required
170 -- x_msg_data OUT VARCHAR2 Required
171 --
172 -- p_module_type IN VARCHAR2 Required.
173 -- This parameter indicates the front-end form interface. The default value is null. If the value
174 -- is JSP, then this API clears out all id columns and validations are done using the values;based
175 -- on which the Id's are populated.
176 --
177 -- Search_OSP Parameters :
178 -- p_start_row IN NUMBER specify the start row to populate into search result table
179 -- p_rows_per_page IN NUMBER specify the number of row to be populated in the search result table
180 -- P_search_order_rec IN Search_Order_rec_type, specify the search criteria
181 -- x_results_order_tbl OUT Results_Order_Tbl_Type, the search Result table
182 -- x_results_count OUT NUMBER, row count from the query, this number can be more than the number of row in search result table
183 --
184 -- Version :
185 -- Initial Version 1.0
186 --
187 -- End of Comments.
188
189 PROCEDURE Search_OSP_Orders
190 (
191 p_api_version IN NUMBER,
192 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
193 p_commit IN VARCHAR2 := FND_API.G_FALSE,
194 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
195 p_default IN VARCHAR2 := FND_API.G_TRUE,
196 p_module_type IN VARCHAR2 ,
197 p_start_row IN NUMBER,
198 p_rows_per_page IN NUMBER,
199 P_search_order_rec IN AHL_OSP_QUERIES_PVT.Search_Order_rec_type,
200 x_results_order_tbl OUT NOCOPY AHL_OSP_QUERIES_PVT.Results_Order_Tbl_Type,
201 x_results_count OUT NOCOPY NUMBER,
202 x_return_status OUT NOCOPY VARCHAR2,
203 x_msg_count OUT NOCOPY NUMBER,
204 x_msg_data OUT NOCOPY VARCHAR2
205 );
206
207
208
209 ----------------------------------------
210 -- Declare Procedures for Search Work Orders --
211 ----------------------------------------
212 -- This procedure Search for Work orders based on the search criteria specify in parameter P_search_WO_rec
213 -- The search result will be populated into x_results_order_tbl.
214 -- Start of Comments --
215 -- Procedure name : Search_OSP
216 -- Type : Public
217 -- Function : Search OSP Order
218 -- Pre-reqs :
219 -- Parameters :
220 --
221 -- Standard IN Parameters :
222 -- p_api_version IN NUMBER Required
223 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
224 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
225 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
226 -- p_module_type IN VARCHAR2 Default Null
227 -- Standard OUT Parameters :
228 -- x_return_status OUT VARCHAR2 Required
229 -- x_msg_count OUT NUMBER Required
230 -- x_msg_data OUT VARCHAR2 Required
231 --
232 -- p_module_type IN VARCHAR2 Required.
233 -- This parameter indicates the front-end form interface. The default value is null. If the value
234 -- is JSP, then this API clears out all id columns and validations are done using the values;based
235 -- on which the Id's are populated.
236 --
237 -- Search_OSP Parameters :
238 -- p_start_row IN NUMBER specify the start row to populate into search result table
239 -- p_rows_per_page IN NUMBER specify the number of row to be populated in the search result table
240 -- P_search_order_rec IN Search_Order_rec_type, specify the search criteria
241 -- x_results_order_tbl OUT Results_Order_Tbl_Type, the search Result table
242 -- x_results_count OUT NUMBER, row count from the query, this number can be more than the number of row in search result table
243 --
244 -- Version :
245 -- Initial Version 1.0
246 --
247 -- End of Comments.
248 PROCEDURE Search_WO
249 (
250 p_api_version IN NUMBER,
251 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
252 p_commit IN VARCHAR2 := FND_API.G_FALSE,
253 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
254 p_module_type IN VARCHAR2 ,
255 p_start_row IN NUMBER,
256 p_rows_per_page IN NUMBER,
257 p_search_WO_rec IN AHL_OSP_QUERIES_PVT.Search_WO_Rec_Type,
258 x_result_WO_tbl OUT NOCOPY AHL_OSP_QUERIES_PVT.Results_WO_Tbl_Type,
259 x_results_count OUT NOCOPY NUMBER,
260 x_return_status OUT NOCOPY VARCHAR2,
261 x_msg_count OUT NOCOPY NUMBER,
262 x_msg_data OUT NOCOPY VARCHAR2
263
264 );
265
266 ----------------------------------------
267 -- Declare Procedures for GET_HEADER_AND_LINES --
268 ----------------------------------------
269 -- This procedure Search for OSP Order Header and order lines based on the input parameter P_osp_id.
270 -- When the input parameter p_osp_id is null it will use input parameter P_work_order_ids to search for workorders
271 -- and populate into order line table.
272
273 -- Start of Comments --
274 -- Procedure name : Search_OSP
275 -- Type : Public
276 -- Function : Search OSP Order
277 -- Pre-reqs :
278 -- Parameters :
279 --
280 -- Standard IN Parameters :
281 -- p_api_version IN NUMBER Required
282 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
283 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
284 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
285 -- p_module_type IN VARCHAR2 Default Null
286 -- Standard OUT Parameters :
287 -- x_return_status OUT VARCHAR2 Required
288 -- x_msg_count OUT NUMBER Required
289 -- x_msg_data OUT VARCHAR2 Required
290 --
291 -- p_module_type IN VARCHAR2 Required.
292 -- This parameter indicates the front-end form interface. The default value is null. If the value
293 -- is JSP, then this API clears out all id columns and validations are done using the values;based
294 -- on which the Id's are populated.
295 --
296 -- Search_OSP Parameters :
297 -- P_osp_id IN NUMBER, id that the search will be based on.
298 -- P_work_order_ids IN work_id_tbl_type, List of workorder id that search will be based on if p_osp_id is null
299 -- x_order_header_rec IN order_header_rec_Type, store order header
300 -- x_order_lines_tbl OUT order_line_tbl_Type, Store order order lines rows
301 -- x_msg_count OUT NUMBER,
302 -- Version :
303 -- Initial Version 1.0
304 --
305 -- End of Comments.
306 PROCEDURE GET_HEADER_AND_LINES
307 (
308 p_api_version IN NUMBER,
309 p_init_msg_list IN VARCHAR2,
313 P_osp_id IN NUMBER,
310 p_commit IN VARCHAR2,
311 p_validation_level IN NUMBER,
312 p_module_type IN VARCHAR2,
314 P_work_order_ids IN AHL_OSP_QUERIES_PVT.work_id_tbl_type,
315 x_order_header_rec OUT NOCOPY AHL_OSP_QUERIES_PVT.order_header_rec_Type,
316 x_order_lines_tbl OUT NOCOPY AHL_OSP_QUERIES_PVT.order_line_tbl_Type,
317 x_return_status OUT NOCOPY VARCHAR2,
318 x_msg_count OUT NOCOPY NUMBER,
319 x_msg_data OUT NOCOPY VARCHAR2
320
321 );
322
323
324
325 ----------------------------------------
326 -- Declare Procedures for GET_HEADER_AND_LINES --
327 ----------------------------------------
328 -- This procedure Search for OSP Order lines based on the input parameter P_osp_id.
329 -- When the input parameter p_osp_id is null it will use input parameter P_work_order_ids to search for workorders
330 -- and populate into order line table.
331
332 -- Start of Comments --
333 -- Procedure name : Search_OSP
334 -- Type : Public
335 -- Function : Search OSP Order
336 -- Pre-reqs :
337 -- Parameters :
338 --
339 -- Standard IN Parameters :
340 -- p_api_version IN NUMBER Required
341 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
342 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
343 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
344 -- p_module_type IN VARCHAR2 Default Null
345 -- Standard OUT Parameters :
346 -- x_return_status OUT VARCHAR2 Required
347 -- x_msg_count OUT NUMBER Required
348 -- x_msg_data OUT VARCHAR2 Required
349 --
350 -- p_module_type IN VARCHAR2 Required.
351 -- This parameter indicates the front-end form interface. The default value is null. If the value
352 -- is JSP, then this API clears out all id columns and validations are done using the values;based
353 -- on which the Id's are populated.
354 --
355 -- Search_OSP Parameters :
356 -- P_osp_id IN NUMBER, id that the search will be based on.
357 -- P_work_order_ids IN work_id_tbl_type, List of workorder id that search will be based on if p_osp_id is null
358 -- x_order_lines_tbl OUT order_line_tbl_Type, Store order order lines rows
359 -- x_msg_count OUT NUMBER,
360 -- Version :
361 -- Initial Version 1.0
362 --
363 -- End of Comments.
364 PROCEDURE GET_ORDER_LINES
365 (
366 p_api_version IN NUMBER,
367 p_init_msg_list IN VARCHAR2,
368 p_commit IN VARCHAR2,
369 p_validation_level IN NUMBER,
370 p_module_type IN VARCHAR2,
371 P_osp_id IN NUMBER,
372 P_work_order_ids IN AHL_OSP_QUERIES_PVT.work_id_tbl_type,
373 x_order_lines_tbl OUT NOCOPY AHL_OSP_QUERIES_PVT.order_line_tbl_Type,
374 x_return_status OUT NOCOPY VARCHAR2,
375 x_msg_count OUT NOCOPY NUMBER,
376 x_msg_data OUT NOCOPY VARCHAR2
377
378 );
379
380 -------------------------------GET SUGGESTED VENDOR-------------------------
381 -- Return the suggested vendor for a work order
382 -- If there are no suggested vendors, returns null
383 -- If there are multiple suggested vendors, returns '*'
384 -- If there is only one vendor, returns the vendor name
385 ----------------------------------------------------------------------------
386 FUNCTION Get_Suggested_Vendor(p_work_order_id IN NUMBER)
387 RETURN VARCHAR2;
388
389 /* Added by mpothuku on 03-17-05 for calculating the onhand quantity for an inventory item */
390 -------------------------------GET ON HAND QUANTITY FOR AN ITEM-------------------------
391
392 FUNCTION Get_Onhand_Quantity(p_org_id IN NUMBER, p_subinventory_code IN VARCHAR2, p_inventory_item_id IN NUMBER,
393 --Added by mpothuku on 23rd Aug, 06 to fix the Bug 5252627
394 p_lot_number IN VARCHAR2)
395 RETURN NUMBER;
396
397 END AHL_OSP_QUERIES_PVT;
[Home] [Help]