[Home] [Help]
PACKAGE: APPS.AHL_PRD_WO_PUB
Source
1 PACKAGE AHL_PRD_WO_PUB AS
2 /* $Header: AHLPWOSS.pls 120.0.12010000.3 2008/11/30 22:22:04 sikumar noship $ */
3
4 TYPE WO_DETAILS_REC_TYPE IS RECORD
5 (
6 WorkorderId NUMBER,
7 ObjectVersionNumber NUMBER,
8 WorkorderNumber VARCHAR2(80),
9 Description VARCHAR2(240),
10 StatusCode VARCHAR2(30),
11 Status VARCHAR2(80),
12 Priority VARCHAR2(80),
13 OrganizationId NUMBER,
14 OrganizationName VARCHAR2(240),
15 DepartmentId NUMBER,
16 DepartmentName VARCHAR2(240),
17 ScheduledStartDate DATE,
18 ScheduledEndDate DATE,
19 ActualStartDate DATE,
20 ActualEndDate DATE,
21 UnitHeaderId NUMBER,
22 UnitName VARCHAR2(4000),
23 WorkorderItemNumber VARCHAR2(40),
24 SerialNumber VARCHAR2(30),
25 LotNumber VARCHAR2(80),
26 VisitId NUMBER,
27 VisitNumber NUMBER,
28 VisitTaskId NUMBER,
29 VisitTaskNumber NUMBER,
30 VisitStatusCode VARCHAR2(30),
31 VisitStartDate DATE,
32 VisitEndDate DATE,
33 EnigmaDocumentID VARCHAR2(240),
34 EnigmaDocumentTitle VARCHAR2(240),
35 ATACode VARCHAR2(240),
36 Model VARCHAR2(240),
37 RoutePublishingDate DATE,
38 MrHeaderId NUMBER,
39 MrTitle VARCHAR2(80),
40 MrRouteId NUMBER,
41 RouteId NUMBER,
42 RouteTitle VARCHAR2(2000),
43 RouteNumber VARCHAR2(30),
44 ProjectName VARCHAR2(30),
45 ProjectTaskName VARCHAR2(20),
46 UnitEffectivityId NUMBER,
47 NonRoutineId NUMBER,
48 NonRoutineNumber VARCHAR2(64),
49 HoldReasonCode VARCHAR2(30),
50 HoldReason VARCHAR2(80),
51 IsUnitQuarantined VARCHAR2(1),
52 IsCompleteEnabled VARCHAR2(1),
53 IsPartsChangeEnabled VARCHAR2(1),
54 IsNonRoutineCreationEnabled VARCHAR2(1),
55 IsUpdateEnabled VARCHAR2(1),
56 IsResTxnEnabled VARCHAR2(1),
57 IsQualityEnabled VARCHAR2(1)
58 );
59
60 ------------------------
61 -- Declare Procedures --
62 ------------------------
63
64 PROCEDURE get_workorder_details
65 (
66 p_api_version IN NUMBER := 1.0,
67 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
68 p_commit IN VARCHAR2 := FND_API.G_FALSE,
69 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
70 p_default IN VARCHAR2 := FND_API.G_FALSE,
71 p_module_type IN VARCHAR2,
72 p_userid IN VARCHAR2 := NULL,
73 p_WorkorderId IN NUMBER,
74 p_WorkorderNumber IN VARCHAR2,
75 x_return_status OUT NOCOPY VARCHAR2,
76 x_msg_count OUT NOCOPY NUMBER,
77 x_msg_data OUT NOCOPY VARCHAR2,
78 x_WO_DETAILS_REC OUT NOCOPY WO_DETAILS_REC_TYPE
79 );
80
81 TYPE OP_DETAILS_REC_TYPE IS RECORD
82 (
83 WorkorderOperationId NUMBER,
84 ObjectVersionNumber NUMBER,
85 OperationSequenceNumber NUMBER,
86 WorkorderId NUMBER,
87 OperationCode VARCHAR2(216),
88 Description VARCHAR2(4000),
89 StatusCode VARCHAR2(30),
90 Status VARCHAR2(80),
91 OperationTypeCode VARCHAR2(30),
92 OperationType VARCHAR2(80),
93 DepartmentId NUMBER,
94 DepartmentName VARCHAR2(240),
95 ScheduledStartDate DATE,
96 ScheduledEndDate DATE,
97 ActualStartDate DATE,
98 ActualEndDate DATE,
99 IsUpdateEnabled VARCHAR2(1),
100 IsQualityEnabled VARCHAR2(1)
101 );
102
103 TYPE OP_TBL_TYPE IS TABLE OF OP_DETAILS_REC_TYPE INDEX BY BINARY_INTEGER;
104
105
106 PROCEDURE get_wo_operations_details
107 (
108 p_api_version IN NUMBER := 1.0,
109 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
110 p_commit IN VARCHAR2 := FND_API.G_FALSE,
111 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
112 p_default IN VARCHAR2 := FND_API.G_FALSE,
113 p_module_type IN VARCHAR2,
114 p_userid IN VARCHAR2 := NULL,
115 p_WorkorderId IN NUMBER,
116 p_WorkorderNumber IN VARCHAR2,
117 p_WoOperationId IN NUMBER,
118 p_OperationSequence IN NUMBER,
119 x_return_status OUT NOCOPY VARCHAR2,
120 x_msg_count OUT NOCOPY NUMBER,
121 x_msg_data OUT NOCOPY VARCHAR2,
122 x_Operations OUT NOCOPY OP_TBL_TYPE
123 );
124
125 TYPE MTL_REQMTS_REC_TYPE IS RECORD
126 (
127 ScheduledMaterialId NUMBER,
128 WorkorderId NUMBER,
129 OperationSequenceNumber NUMBER,
130 InventoryItemId NUMBER,
131 ItemNumber VARCHAR2(40),
132 ItemDescription VARCHAR2(240),
133 RequiredQuantity NUMBER,
134 PartUOM VARCHAR2(25),
135 RequiredDate DATE,
136 ScheduledQuantity NUMBER,
137 ScheduledDate DATE,
138 IssuedQuantity NUMBER
139 );
140
141 TYPE MTL_REQMTS_TBL_TYPE IS TABLE OF MTL_REQMTS_REC_TYPE INDEX BY BINARY_INTEGER;
142
143
144 PROCEDURE get_wo_mtl_reqmts
145 (
146 p_api_version IN NUMBER := 1.0,
147 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
148 p_commit IN VARCHAR2 := FND_API.G_FALSE,
149 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
150 p_default IN VARCHAR2 := FND_API.G_FALSE,
151 p_module_type IN VARCHAR2,
152 p_userid IN VARCHAR2 := NULL,
153 p_WorkorderId IN NUMBER,
154 p_WorkorderNumber IN VARCHAR2,
155 p_WoOperationId IN NUMBER,
156 p_OperationSequence IN NUMBER,
157 x_return_status OUT NOCOPY VARCHAR2,
158 x_msg_count OUT NOCOPY NUMBER,
159 x_msg_data OUT NOCOPY VARCHAR2,
160 x_MaterialRequirementDetails OUT NOCOPY MTL_REQMTS_TBL_TYPE
161 );
162
163 TYPE ASSOC_DOCS_REC_TYPE IS RECORD
164 (
165 DocumentNumber VARCHAR2(30),
166 DocumentTitle VARCHAR2(240),
167 AsoObjectTypeDesc VARCHAR2(80),
168 RevisionNumber VARCHAR2(30),
169 Chapter VARCHAR2(30),
170 Section VARCHAR2(30),
171 Subject VARCHAR2(240),
172 Page VARCHAR2(5),
173 Figure VARCHAR2(30),
174 Note VARCHAR2(2000)
175 );
176
177 TYPE ASSOC_DOCS_TBL_TYPE IS TABLE OF ASSOC_DOCS_REC_TYPE INDEX BY BINARY_INTEGER;
178
179
180
181 PROCEDURE get_wo_assoc_documents
182 (
183 p_api_version IN NUMBER := 1.0,
184 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
185 p_commit IN VARCHAR2 := FND_API.G_FALSE,
186 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
187 p_default IN VARCHAR2 := FND_API.G_FALSE,
188 p_module_type IN VARCHAR2,
189 p_userid IN VARCHAR2 := NULL,
190 p_WorkorderId IN NUMBER,
191 p_WorkorderNumber IN VARCHAR2,
192 x_return_status OUT NOCOPY VARCHAR2,
193 x_msg_count OUT NOCOPY NUMBER,
194 x_msg_data OUT NOCOPY VARCHAR2,
195 x_AssociatedDocuments OUT NOCOPY ASSOC_DOCS_TBL_TYPE
196 );
197
198 TYPE TURNOVER_NOTES_REC_TYPE IS RECORD
199 (
200 JtfNoteId NUMBER,
201 SourceObjectId NUMBER,
202 SourceObjectCode VARCHAR2(255),
203 EnteredDate DATE,
204 EnteredBy NUMBER,
205 EnteredByName VARCHAR2(4000),
206 Notes VARCHAR2(2000)
207 );
208
209 TYPE TURNOVER_NOTES_TBL_TYPE IS TABLE OF TURNOVER_NOTES_REC_TYPE INDEX BY BINARY_INTEGER;
210
211
212 PROCEDURE get_wo_turnover_notes
213 (
214 p_api_version IN NUMBER := 1.0,
215 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
216 p_commit IN VARCHAR2 := FND_API.G_FALSE,
217 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
218 p_default IN VARCHAR2 := FND_API.G_FALSE,
219 p_module_type IN VARCHAR2,
220 p_userid IN VARCHAR2 := NULL,
221 p_WorkorderId IN NUMBER,
222 p_WorkorderNumber IN VARCHAR2,
223 x_return_status OUT NOCOPY VARCHAR2,
224 x_msg_count OUT NOCOPY NUMBER,
225 x_msg_data OUT NOCOPY VARCHAR2,
226 x_TurnoverNotes OUT NOCOPY TURNOVER_NOTES_TBL_TYPE
227 );
228
229 TYPE RES_TXNS_REC_TYPE IS RECORD
230 (
231 TransactionId NUMBER,
232 WorkorderId NUMBER,
233 OperationSequenceNumber NUMBER,
234 ResourceSequenceNumber NUMBER,
235 ResourceId NUMBER,
236 ResourceCode VARCHAR2(10),
237 ResourceDescription VARCHAR2(240),
238 ResourceType VARCHAR2(30),
239 ResourceTypeCode VARCHAR2(255),
240 EmployeeId NUMBER,
241 EmployeeNumber VARCHAR2(30),
242 EmployeeName VARCHAR2(240),
243 SerialNumber VARCHAR2(30),
244 StartTime DATE,
245 EndTime DATE,
246 Quantity NUMBER,
247 UOMCode VARCHAR2(3),
248 UOM VARCHAR2(25),
249 UsageRateOrAmount NUMBER,
250 ActivityId NUMBER,
251 Activity VARCHAR2(10),
252 ReasonId NUMBER,
253 Reason VARCHAR2(30),
254 Reference VARCHAR2(240),
255 TransactionDate DATE,
256 TransactionStatus VARCHAR2(80)
257 );
258
259 TYPE RES_TXNS_TBL_TYPE IS TABLE OF RES_TXNS_REC_TYPE INDEX BY BINARY_INTEGER;
260
261
262 PROCEDURE get_wo_res_txns
263 (
264 p_api_version IN NUMBER := 1.0,
265 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
266 p_commit IN VARCHAR2 := FND_API.G_FALSE,
267 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
268 p_default IN VARCHAR2 := FND_API.G_FALSE,
269 p_module_type IN VARCHAR2,
270 p_userid IN VARCHAR2 := NULL,
271 p_WorkorderId IN NUMBER,
272 p_WorkorderNumber IN VARCHAR2,
273 p_WoOperationId IN NUMBER,
274 p_OperationSequence IN NUMBER,
275 x_return_status OUT NOCOPY VARCHAR2,
276 x_msg_count OUT NOCOPY NUMBER,
277 x_msg_data OUT NOCOPY VARCHAR2,
278 x_DefaultResourceTransactions OUT NOCOPY RES_TXNS_TBL_TYPE,
279 x_ResourceTransactions OUT NOCOPY RES_TXNS_TBL_TYPE
280 );
281
282 TYPE QA_PLAN_ATR_REC_TYPE IS RECORD
283 (
284 CharId NUMBER,
285 PromptSequence NUMBER,
286 Prompt VARCHAR2(240),
287 DefaultValue VARCHAR2(240),
288 IsListOfValue VARCHAR2(1),
289 IsDisplayed VARCHAR2(1),
290 IsMandatory VARCHAR2(1),
291 IsReadOnly VARCHAR2(1),
292 DisplayLength NUMBER,
293 DataType VARCHAR2(30)
294 );
295
296 TYPE QA_PLAN_ATR_TBL_TYPE IS TABLE OF QA_PLAN_ATR_REC_TYPE INDEX BY BINARY_INTEGER;
297
298 TYPE QA_PLAN_REC_TYPE IS RECORD
299 (
300 PlanId NUMBER,
301 OrganizationId NUMBER,
302 PlanName VARCHAR2(80),
303 PlanDescription VARCHAR2(240),
304 QA_PLAN_ATR_TBL QA_PLAN_ATR_TBL_TYPE
305 );
306
307 TYPE QA_PLAN_ATRVAL_REC_TYPE IS RECORD
308 (
309 CharId NUMBER,
310 AttributeValue VARCHAR2(4000)
311 );
312
313 TYPE QA_PLAN_ATRVAL_TBL_TYPE IS TABLE OF QA_PLAN_ATRVAL_REC_TYPE INDEX BY BINARY_INTEGER;
314
315 TYPE QA_RESULT_REC_TYPE IS RECORD
316 (
317 Occurence NUMBER,
318 QA_PLAN_ATRVAL_TBL QA_PLAN_ATRVAL_TBL_TYPE
319 );
320
321 TYPE QA_RESULT_TBL_TYPE IS TABLE OF QA_RESULT_REC_TYPE INDEX BY BINARY_INTEGER;
322
323 TYPE QA_RESULTS_REC_TYPE IS RECORD
324 (
325 CollectionId NUMBER,
326 PlanId NUMBER,
327 QA_RESULT_TBL QA_RESULT_TBL_TYPE
328 );
329
330
331
332 PROCEDURE get_qa_plan_results
333 (
334 p_api_version IN NUMBER := 1.0,
335 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
336 p_commit IN VARCHAR2 := FND_API.G_FALSE,
337 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
338 p_default IN VARCHAR2 := FND_API.G_FALSE,
339 p_module_type IN VARCHAR2,
340 p_userid IN VARCHAR2 := NULL,
341 p_WorkorderId IN NUMBER,
342 p_WorkorderNumber IN VARCHAR2,
343 p_WoOperationId IN NUMBER,
344 p_OperationSequence IN NUMBER,
345 x_return_status OUT NOCOPY VARCHAR2,
346 x_msg_count OUT NOCOPY NUMBER,
347 x_msg_data OUT NOCOPY VARCHAR2,
348 x_QaPlan OUT NOCOPY QA_PLAN_REC_TYPE,
349 x_QaResults OUT NOCOPY QA_RESULTS_REC_TYPE
350 );
351
352 TYPE OP_ALL_DETAILS_REC_TYPE IS RECORD
353 (
354 WorkorderOperationId NUMBER,
355 ObjectVersionNumber NUMBER,
356 OperationSequenceNumber NUMBER,
357 WorkorderId NUMBER,
358 OperationCode VARCHAR2(216),
359 Description VARCHAR2(4000),
360 StatusCode VARCHAR2(30),
361 Status VARCHAR2(80),
362 OperationTypeCode VARCHAR2(30),
363 OperationType VARCHAR2(80),
364 DepartmentId NUMBER,
365 DepartmentName VARCHAR2(240),
366 ScheduledStartDate DATE,
367 ScheduledEndDate DATE,
368 ActualStartDate DATE,
369 ActualEndDate DATE,
370 IsUpdateEnabled VARCHAR2(1),
371 IsQualityEnabled VARCHAR2(1),
372 QAResults QA_RESULTS_REC_TYPE
373 );
374
375 TYPE OP_ALL_DETAILS_TBL IS TABLE OF OP_ALL_DETAILS_REC_TYPE INDEX BY BINARY_INTEGER;
376
377
378
379 PROCEDURE process_workorder
380 (
381 p_api_version IN NUMBER := 1.0,
382 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
383 p_commit IN VARCHAR2 := FND_API.G_FALSE,
384 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
385 p_default IN VARCHAR2 := FND_API.G_FALSE,
386 p_module_type IN VARCHAR2,
387 p_userid IN VARCHAR2 := NULL,
388 p_WO_DETAILS_REC IN WO_DETAILS_REC_TYPE,
389 p_Operations IN OP_ALL_DETAILS_TBL,
390 p_TurnoverNotes IN TURNOVER_NOTES_TBL_TYPE,
391 p_MaterialRequirementDetails IN MTL_REQMTS_TBL_TYPE,
392 p_WO_QaResults IN QA_RESULTS_REC_TYPE,
393 p_ResourceTransactions IN RES_TXNS_TBL_TYPE,
394 x_return_status OUT NOCOPY VARCHAR2,
395 x_msg_count OUT NOCOPY NUMBER,
396 x_msg_data OUT NOCOPY VARCHAR2
397 );
398
399 FUNCTION GET_MSG_DATA(p_msg_count IN NUMBER) RETURN VARCHAR2;
400
401 END AHL_PRD_WO_PUB;