1 PACKAGE CSD_REPair_JOB_XREF_PVT AUTHID CURRENT_USER as
2 /* $Header: csdvdrjs.pls 115.10 2003/09/15 21:33:56 sragunat ship $ */
3 -- Start of Comments
4 -- Package name : CSD_REPair_JOB_XREF_PVT
5 -- Purpose :
6 -- History : Added Columns Inventory_Item_ID and Item_Revision -- travi
7 -- History : 01/17/2002, TRAVI added column OBJECT_VERSION_NUMBER
8 -- History : 08/20/2003, Shiv Ragunathan, 11.5.10 Changes: Added
9 -- History : source_type_code, source_id1, ro_service_code_id,
10 -- History : job_name to record type REPJOBXREF_Rec_Type.
11 -- NOTE :
12 -- End of Comments
13
14 -- Default number of records fetch per call
15 G_DEFAULT_NUM_REC_FETCH NUMBER := 30;
16 -- *******************************************************
17 -- Start of Comments
18 -- -------------------------------------------------------
19 -- Record name:REPJOBXREF_Rec_Type
20 -- -------------------------------------------------------
21 -- Parameters:
22 -- REPAIR_JOB_XREF_ID
23 -- CREATED_BY
24 -- CREATION_DATE
25 -- LAST_UPDATED_BY
26 -- LAST_UPDATE_DATE
27 -- LAST_UPDATE_LOGIN
28 -- REPAIR_LINE_ID
29 -- WIP_ENTITY_ID
30 -- GROUP_ID
31 -- ORGANIZATION_ID
32 -- QUANTITY
33 -- INVENTORY_ITEM_ID
34 -- ITEM_REVISION
35 -- OBJECT_VERSION_NUMBER
36 -- ATTRIBUTE_CATEGORY
37 -- ATTRIBUTE1
38 -- ATTRIBUTE2
39 -- ATTRIBUTE3
40 -- ATTRIBUTE4
41 -- ATTRIBUTE5
42 -- ATTRIBUTE6
43 -- ATTRIBUTE7
44 -- ATTRIBUTE8
45 -- ATTRIBUTE9
46 -- ATTRIBUTE10
47 -- ATTRIBUTE11
48 -- ATTRIBUTE12
49 -- ATTRIBUTE13
50 -- ATTRIBUTE14
51 -- ATTRIBUTE15
52 --
53 -- Required:
54 -- Defaults:
55 -- Note: This is automatic generated record definition, it includes all columns
56 -- defined in the table, developer must manually add or delete some of the attributes.
57 --
58 -- End of Comments
59
60
61 TYPE REPJOBXREF_sort_rec_type IS RECORD
62 (
63 -- Please define your own sort by record here.
64 CREATED_BY NUMBER := NULL
65 );
66
67 -- travi changes
68 TYPE REPJOBXREF_Rec_Type IS RECORD
69 (
70 REPAIR_JOB_XREF_ID NUMBER := FND_API.G_MISS_NUM,
71 CREATED_BY NUMBER := FND_API.G_MISS_NUM,
72 CREATION_DATE DATE := FND_API.G_MISS_DATE,
73 LAST_UPDATED_BY NUMBER := FND_API.G_MISS_NUM,
74 LAST_UPDATE_DATE DATE := FND_API.G_MISS_DATE,
75 LAST_UPDATE_LOGIN NUMBER := FND_API.G_MISS_NUM,
76 REPAIR_LINE_ID NUMBER := FND_API.G_MISS_NUM,
77 WIP_ENTITY_ID NUMBER := FND_API.G_MISS_NUM,
78 GROUP_ID NUMBER := FND_API.G_MISS_NUM,
79 ORGANIZATION_ID NUMBER := FND_API.G_MISS_NUM,
80 QUANTITY NUMBER := FND_API.G_MISS_NUM,
81 INVENTORY_ITEM_ID NUMBER := FND_API.G_MISS_NUM,
82 ITEM_REVISION VARCHAR2(30) := FND_API.G_MISS_CHAR,
83 SOURCE_TYPE_CODE VARCHAR2(30) := FND_API.G_MISS_CHAR,
84 SOURCE_ID1 NUMBER := FND_API.G_MISS_NUM,
85 RO_SERVICE_CODE_ID NUMBER := FND_API.G_MISS_NUM,
86 JOB_NAME VARCHAR2(30) := FND_API.G_MISS_CHAR,
87 OBJECT_VERSION_NUMBER NUMBER := FND_API.G_MISS_NUM,
88 ATTRIBUTE_CATEGORY VARCHAR2(30) := FND_API.G_MISS_CHAR,
89 ATTRIBUTE1 VARCHAR2(150) := FND_API.G_MISS_CHAR,
90 ATTRIBUTE2 VARCHAR2(150) := FND_API.G_MISS_CHAR,
91 ATTRIBUTE3 VARCHAR2(150) := FND_API.G_MISS_CHAR,
92 ATTRIBUTE4 VARCHAR2(150) := FND_API.G_MISS_CHAR,
93 ATTRIBUTE5 VARCHAR2(150) := FND_API.G_MISS_CHAR,
94 ATTRIBUTE6 VARCHAR2(150) := FND_API.G_MISS_CHAR,
95 ATTRIBUTE7 VARCHAR2(150) := FND_API.G_MISS_CHAR,
96 ATTRIBUTE8 VARCHAR2(150) := FND_API.G_MISS_CHAR,
97 ATTRIBUTE9 VARCHAR2(150) := FND_API.G_MISS_CHAR,
98 ATTRIBUTE10 VARCHAR2(150) := FND_API.G_MISS_CHAR,
99 ATTRIBUTE11 VARCHAR2(150) := FND_API.G_MISS_CHAR,
100 ATTRIBUTE12 VARCHAR2(150) := FND_API.G_MISS_CHAR,
101 ATTRIBUTE13 VARCHAR2(150) := FND_API.G_MISS_CHAR,
102 ATTRIBUTE14 VARCHAR2(150) := FND_API.G_MISS_CHAR,
103 ATTRIBUTE15 VARCHAR2(150) := FND_API.G_MISS_CHAR,
104 QUANTITY_COMPLETED NUMBER := FND_API.G_MISS_NUM
105 );
106
107 G_MISS_REPJOBXREF_REC REPJOBXREF_Rec_Type;
108 TYPE REPJOBXREF_Tbl_Type IS TABLE OF REPJOBXREF_Rec_Type
109 INDEX BY BINARY_INTEGER;
110 G_MISS_REPJOBXREF_TBL REPJOBXREF_Tbl_Type;
111
112 -- *******************************************************
113 -- Start of Comments
114 -- *******************************************************
115 -- API Name: Create_repjobxref
116 -- Type : Private
117 -- Pre-Req :
118 -- Parameters:
119 -- IN
120 -- p_api_version_number IN NUMBER Required
121 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
122 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
123 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
124 -- P_REPJOBXREF_Rec IN REPJOBXREF_Rec_Type Required
125 --
126 -- OUT:
127 -- x_return_status OUT NOCOPY VARCHAR2
128 -- x_msg_count OUT NOCOPY NUMBER
129 -- x_msg_data OUT NOCOPY VARCHAR2
130 -- Version : Current version 2.0
131 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
132 -- and basic operation, developer must manually add parameters and business logic as necessary.
133 --
134 -- End of Comments
135 --
136 PROCEDURE Create_repjobxref(
137 P_Api_Version_Number IN NUMBER,
138 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
139 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
140 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
141 P_REPJOBXREF_Rec IN REPJOBXREF_Rec_Type := G_MISS_REPJOBXREF_REC,
142 --Hint: Add detail tables as parameter lists if it's master-detail relationship.
143 X_REPAIR_JOB_XREF_ID OUT NOCOPY NUMBER,
144 X_Return_Status OUT NOCOPY VARCHAR2,
145 X_Msg_Count OUT NOCOPY NUMBER,
146 X_Msg_Data OUT NOCOPY VARCHAR2
147 );
148
149 -- *******************************************************
150 -- Start of Comments
151 -- *******************************************************
152 -- API Name: Update_repjobxref
153 -- Type : Private
154 -- Pre-Req :
155 -- Parameters:
156 -- IN
157 -- p_api_version_number IN NUMBER Required
158 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
159 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
160 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
161 -- p_identity_salesforce_id IN NUMBER Optional Default = NULL
162 -- P_REPJOBXREF_Rec IN REPJOBXREF_Rec_Type Required
163 --
164 -- OUT:
165 -- x_return_status OUT NOCOPY VARCHAR2
166 -- x_msg_count OUT NOCOPY NUMBER
167 -- x_msg_data OUT NOCOPY VARCHAR2
168 -- Version : Current version 2.0
169 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
170 -- and basic operation, developer must manually add parameters and business logic as necessary.
171 --
172 -- End of Comments
173 --
174 -- Hint: Add corresponding update detail table procedures if it's master-detail relationship.
175 PROCEDURE Update_repjobxref(
176 P_Api_Version_Number IN NUMBER,
177 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
178 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
179 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
180 P_REPJOBXREF_Rec IN REPJOBXREF_Rec_Type,
181 X_Return_Status OUT NOCOPY VARCHAR2,
182 X_Msg_Count OUT NOCOPY NUMBER,
183 X_Msg_Data OUT NOCOPY VARCHAR2
184 );
185
186 -- *******************************************************
187 -- Start of Comments
188 -- *******************************************************
189 -- API Name: Delete_repjobxref
190 -- Type : Private
191 -- Pre-Req :
192 -- Parameters:
193 -- IN
194 -- p_api_version_number IN NUMBER Required
195 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
196 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
197 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
198 -- p_identity_salesforce_id IN NUMBER Optional Default = NULL
199 -- P_REPJOBXREF_Rec IN REPJOBXREF_Rec_Type Required
200 --
201 -- OUT:
202 -- x_return_status OUT NOCOPY VARCHAR2
203 -- x_msg_count OUT NOCOPY NUMBER
204 -- x_msg_data OUT NOCOPY VARCHAR2
205 -- Version : Current version 2.0
206 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
207 -- and basic operation, developer must manually add parameters and business logic as necessary.
208 --
209 -- End of Comments
210 --
211 -- Hint: Add corresponding delete detail table procedures if it's master-detail relationship.
212 -- The Master delete procedure may not be needed depends on different business requirements.
213 PROCEDURE Delete_repjobxref(
214 P_Api_Version_Number IN NUMBER,
215 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
216 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
217 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
218 P_REPJOBXREF_Rec IN REPJOBXREF_Rec_Type,
219 X_Return_Status OUT NOCOPY VARCHAR2,
220 X_Msg_Count OUT NOCOPY NUMBER,
221 X_Msg_Data OUT NOCOPY VARCHAR2
222 );
223
224 -- *******************************************************
225 -- Start of Comments
226 -- *******************************************************
227 -- API Name: Get_repjobxref
228 -- Type : Private
229 -- Pre-Req :
230 -- Parameters:
231 -- IN
232 -- p_api_version_number IN NUMBER Required
233 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
234 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
235 -- p_identity_salesforce_id IN NUMBER Optional Default = NULL
236 -- P_REPJOBXREF_Rec IN REPJOBXREF_Rec_Type Required
237 -- Hint: Add List of bind variables here
238 -- p_rec_requested IN NUMBER Optional Default = 30
239 -- p_start_rec_ptr IN NUMBER Optional Default = 1
240 --
241 -- Return Total Records Count Flag. This flag controls whether the total record count
242 -- and total record amount is returned.
243 --
244 -- p_return_tot_count IN VARCHAR2 Optional Default = FND_API.G_FALSE
245 -- Hint: User defined record type
246 -- p_order_by_tbl IN JTF_PLSQL_API.UTIL_ORDER_BY_TBL_TYPE;
247 --
248 -- OUT:
249 -- x_return_status OUT NOCOPY VARCHAR2
250 -- x_msg_count OUT NOCOPY NUMBER
251 -- x_msg_data OUT NOCOPY VARCHAR2
252 -- X_REPJOBXREF_Tbl OUT NOCOPY REPJOBXREF_Rec_Type
253 -- x_returned_rec_count OUT NOCOPY NUMBER
254 -- x_next_rec_ptr OUT NOCOPY NUMBER
255 -- x_tot_rec_count OUT NOCOPY NUMBER
256 -- other optional OUT NOCOPY parameters
257 -- x_tot_rec_amount OUT NOCOPY NUMBER
258 -- Version : Current version 2.0
259 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
260 -- and basic operation, developer must manually add parameters and business logic as necessary.
261 --
262 -- End of Comments
263 --
264 PROCEDURE Get_repjobxref(
265 P_Api_Version_Number IN NUMBER,
266 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
267 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
268 P_identity_salesforce_id IN NUMBER := NULL,
269 P_REPJOBXREF_Rec IN REPJOBXREF_Rec_Type,
270 -- Hint: Add list of bind variables here
271 p_rec_requested IN NUMBER := G_DEFAULT_NUM_REC_FETCH,
272 p_start_rec_prt IN NUMBER := 1,
273 p_return_tot_count IN NUMBER := FND_API.G_FALSE,
274 -- Hint: user defined record type
275 p_order_by_rec IN REPJOBXREF_sort_rec_type,
276 x_return_status OUT NOCOPY VARCHAR2,
277 x_msg_count OUT NOCOPY NUMBER,
278 x_msg_data OUT NOCOPY VARCHAR2,
279 X_REPJOBXREF_Tbl OUT NOCOPY REPJOBXREF_Tbl_Type,
280 x_returned_rec_count OUT NOCOPY NUMBER,
281 x_next_rec_ptr OUT NOCOPY NUMBER,
282 x_tot_rec_count OUT NOCOPY NUMBER
283 -- other optional parameters
284 -- x_tot_rec_amount OUT NOCOPY NUMBER
285 );
286
287
288 -- Start of Comments
289 --
290 -- Item level validation procedures
291 --
292 -- p_validation_mode is a constant defined in package
293 -- For create: G_CREATE, for update: G_UPDATE
294 -- Note: 1. This is automated generated item level validation procedure.
295 -- The actual validation detail is needed to be added.
296 --
297 -- End of Comments
298
299 PROCEDURE Validate_REPAIR_JOB_XREF_ID (
300 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
301 P_Validation_mode IN VARCHAR2,
302 P_REPAIR_JOB_XREF_ID IN NUMBER,
303 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
304 X_Return_Status OUT NOCOPY VARCHAR2,
305 X_Msg_Count OUT NOCOPY NUMBER,
306 X_Msg_Data OUT NOCOPY VARCHAR2
307 );
308
309 -- Start of Comments
310 --
311 -- Item level validation procedures
312 --
313 -- p_validation_mode is a constant defined in package
314 -- For create: G_CREATE, for update: G_UPDATE
315 -- Note: 1. This is automated generated item level validation procedure.
316 -- The actual validation detail is needed to be added.
317 --
318 -- End of Comments
319
320 PROCEDURE Validate_REPAIR_LINE_ID (
321 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
322 P_Validation_mode IN VARCHAR2,
323 P_REPAIR_LINE_ID IN NUMBER,
324 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
325 X_Return_Status OUT NOCOPY VARCHAR2,
326 X_Msg_Count OUT NOCOPY NUMBER,
327 X_Msg_Data OUT NOCOPY VARCHAR2
328 );
329
330 -- Start of Comments
331 --
332 -- Item level validation procedures
333 --
334 -- p_validation_mode is a constant defined in package
335 -- For create: G_CREATE, for update: G_UPDATE
336 -- Note: 1. This is automated generated item level validation procedure.
337 -- The actual validation detail is needed to be added.
338 --
339 -- End of Comments
340
341 PROCEDURE Validate_WIP_ENTITY_ID (
342 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
346 X_Return_Status OUT NOCOPY VARCHAR2,
343 P_Validation_mode IN VARCHAR2,
344 P_WIP_ENTITY_ID IN NUMBER,
345 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
347 X_Msg_Count OUT NOCOPY NUMBER,
348 X_Msg_Data OUT NOCOPY VARCHAR2
349 );
350
351 -- Start of Comments
352 --
353 -- Item level validation procedures
354 --
355 -- p_validation_mode is a constant defined in package
356 -- For create: G_CREATE, for update: G_UPDATE
357 -- Note: 1. This is automated generated item level validation procedure.
358 -- The actual validation detail is needed to be added.
359 --
360 -- End of Comments
361
362 PROCEDURE Validate_GROUP_ID (
363 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
364 P_Validation_mode IN VARCHAR2,
365 P_GROUP_ID IN NUMBER,
366 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
367 X_Return_Status OUT NOCOPY VARCHAR2,
368 X_Msg_Count OUT NOCOPY NUMBER,
369 X_Msg_Data OUT NOCOPY VARCHAR2
370 );
371
372 -- Start of Comments
373 --
374 -- Item level validation procedures
375 --
376 -- p_validation_mode is a constant defined in package
377 -- For create: G_CREATE, for update: G_UPDATE
378 -- Note: 1. This is automated generated item level validation procedure.
379 -- The actual validation detail is needed to be added.
380 --
381 -- End of Comments
382
383 PROCEDURE Validate_ORGANIZATION_ID (
384 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
385 P_Validation_mode IN VARCHAR2,
386 P_ORGANIZATION_ID IN NUMBER,
387 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
388 X_Return_Status OUT NOCOPY VARCHAR2,
389 X_Msg_Count OUT NOCOPY NUMBER,
390 X_Msg_Data OUT NOCOPY VARCHAR2
391 );
392
393 -- Start of Comments
394 --
395 -- Item level validation procedures
396 --
397 -- p_validation_mode is a constant defined in package
398 -- For create: G_CREATE, for update: G_UPDATE
399 -- Note: 1. This is automated generated item level validation procedure.
400 -- The actual validation detail is needed to be added.
401 --
402 -- End of Comments
403
404 PROCEDURE Validate_QUANTITY (
405 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
406 P_Validation_mode IN VARCHAR2,
407 P_QUANTITY IN NUMBER,
408 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
409 X_Return_Status OUT NOCOPY VARCHAR2,
410 X_Msg_Count OUT NOCOPY NUMBER,
411 X_Msg_Data OUT NOCOPY VARCHAR2
412 );
413
414 -- Start of Comments
415 --
416 -- Record level validation procedures
417 --
418 -- p_validation_mode is a constant defined in package
419 -- For create: G_CREATE, for update: G_UPDATE
420 -- Note: 1. This is automated generated item level validation procedure.
421 -- The actual validation detail is needed to be added.
422 -- 2. Developer can manually added inter-field level validation.
423 -- End of Comments
424
425 PROCEDURE Validate_REPJOBXREF_rec(
426 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
427 P_Validation_mode IN VARCHAR2,
428 P_REPJOBXREF_Rec IN REPJOBXREF_Rec_Type,
429 X_Return_Status OUT NOCOPY VARCHAR2,
430 X_Msg_Count OUT NOCOPY NUMBER,
431 X_Msg_Data OUT NOCOPY VARCHAR2
432 );
433
434 -- Start of Comments
435 --
436 -- validation procedures
437 --
438 -- p_validation_mode is a constant defined in package
439 -- For create: G_CREATE, for update: G_UPDATE
440 -- Note: 1. This is automated generated item level validation procedure.
441 -- The actual validation detail is needed to be added.
442 -- 2. We can also validate table instead of record. There will be an option for user to choose.
443 -- End of Comments
444
445 PROCEDURE Validate_repjobxref(
446 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
447 P_Validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
448 P_Validation_mode IN VARCHAR2,
449 P_REPJOBXREF_Rec IN REPJOBXREF_Rec_Type,
450 X_Return_Status OUT NOCOPY VARCHAR2,
451 X_Msg_Count OUT NOCOPY NUMBER,
452 X_Msg_Data OUT NOCOPY VARCHAR2
453 );
454 End CSD_REPair_JOB_XREF_PVT;