1 PACKAGE AHL_UC_INSTANCE_PVT AS
2 /* $Header: AHLVUCIS.pls 120.3.12010000.4 2008/11/20 11:42:13 sathapli ship $ */
3
4 -- Define Record Type for AHL Unit Configuration Header Record --
5 TYPE uc_header_rec_type IS RECORD (
6 uc_header_id NUMBER,
7 uc_name VARCHAR2(80),
8 mc_header_id NUMBER,
9 mc_name VARCHAR2(80),
10 mc_revision VARCHAR2(30),
11 parent_uc_header_id NUMBER,
12 unit_config_status_code VARCHAR2(30),
13 active_uc_status_code VARCHAR2(30),
14 instance_id NUMBER,
15 instance_number VARCHAR2(30),
16 active_start_date DATE,
17 active_end_date DATE,
18 object_version_number NUMBER, --refers ovn of unit config header record
19 attribute_category VARCHAR2(30),
20 attribute1 VARCHAR2(150),
21 attribute2 VARCHAR2(150),
22 attribute3 VARCHAR2(150),
23 attribute4 VARCHAR2(150),
24 attribute5 VARCHAR2(150),
25 attribute6 VARCHAR2(150),
26 attribute7 VARCHAR2(150),
27 attribute8 VARCHAR2(150),
28 attribute9 VARCHAR2(150),
29 attribute10 VARCHAR2(150),
30 attribute11 VARCHAR2(150),
31 attribute12 VARCHAR2(150),
32 attribute13 VARCHAR2(150),
33 attribute14 VARCHAR2(150),
34 attribute15 VARCHAR2(150));
35
36 -- Define Record Type for CSI Item Instance Record --
37 -- SATHAPLI::FP ER 6453212, 11-Nov-2008, add flexfield segments to the record type
38 TYPE uc_instance_rec_type IS RECORD (
39 inventory_item_id NUMBER,
40 inventory_org_id NUMBER,
41 inventory_org_code VARCHAR2(3),
42 -- Changed by jaramana on 16-APR-2008 for bug 6977832
43 -- mtl_system_items_kfv.concatenated_segments%TYPE will make the size of this column
44 -- dependent on the definition of the view mtl_system_items_kfv.
45 -- So, hardcode this to a large value of 240 instead so that Rosetta also will declare
46 -- these as VARCHAR2(300) instead of VARCHAR2(100).
47 item_number VARCHAR2(240),
48 instance_id NUMBER,
49 instance_number VARCHAR2(30),
50 serial_number VARCHAR2(30),
51 sn_tag_code VARCHAR2(30),
52 sn_tag_meaning VARCHAR2(80),
53 lot_number MTL_LOT_NUMBERS.LOT_NUMBER%TYPE,
54 quantity NUMBER,
55 uom_code VARCHAR2(3),
56 revision VARCHAR2(3),
57 mfg_date DATE,
58 install_date DATE,
59 relationship_id NUMBER, --refers to relationship_id in ahl_mc_relationships
60 object_version_number NUMBER, --refers to ovn of csi item instance
61 context VARCHAR2(30),
62 attribute1 VARCHAR2(240),
63 attribute2 VARCHAR2(240),
64 attribute3 VARCHAR2(240),
65 attribute4 VARCHAR2(240),
66 attribute5 VARCHAR2(240),
67 attribute6 VARCHAR2(240),
68 attribute7 VARCHAR2(240),
69 attribute8 VARCHAR2(240),
70 attribute9 VARCHAR2(240),
71 attribute10 VARCHAR2(240),
72 attribute11 VARCHAR2(240),
73 attribute12 VARCHAR2(240),
74 attribute13 VARCHAR2(240),
75 attribute14 VARCHAR2(240),
76 attribute15 VARCHAR2(240),
77 attribute16 VARCHAR2(240),
78 attribute17 VARCHAR2(240),
79 attribute18 VARCHAR2(240),
80 attribute19 VARCHAR2(240),
81 attribute20 VARCHAR2(240),
82 attribute21 VARCHAR2(240),
83 attribute22 VARCHAR2(240),
84 attribute23 VARCHAR2(240),
85 attribute24 VARCHAR2(240),
86 attribute25 VARCHAR2(240),
87 attribute26 VARCHAR2(240),
88 attribute27 VARCHAR2(240),
89 attribute28 VARCHAR2(240),
90 attribute29 VARCHAR2(240),
91 attribute30 VARCHAR2(240));
92
93 -- Define Record Type for immediate children of a given UC node --
94 TYPE uc_child_rec_type IS RECORD (
95 node_type VARCHAR2(1),
96 instance_id NUMBER,
97 relationship_id NUMBER,
98 leaf_node_flag VARCHAR2(1),
99 with_subunit_flag VARCHAR2(1));
100 TYPE uc_child_tbl_type IS TABLE OF uc_child_rec_type INDEX BY BINARY_INTEGER;
101
102 -- Define Record Type for all descendants of a given UC root node --
103 TYPE uc_descendant_rec_type IS RECORD (
104 node_type VARCHAR2(1),
105 instance_id NUMBER,
106 parent_instance_id NUMBEr,
107 relationship_id NUMBER,
108 parent_rel_id NUMBER,
109 leaf_node_flag VARCHAR2(1),
110 with_submc_flag VARCHAR2(1));
111 TYPE uc_descendant_tbl_type IS TABLE OF uc_descendant_rec_type INDEX BY BINARY_INTEGER;
112
113 -- Define Record Type for alternate CSI item instances --
114 TYPE available_instance_rec_type IS RECORD (
115 csi_item_instance_id NUMBER,
116 csi_object_version_number NUMBER,
117 inventory_item_id NUMBER,
118 inventory_org_id NUMBER,
119 organization_code VARCHAR2(3),
120 -- Changed by jaramana on 16-APR-2008 for bug 6977832
121 -- Increase this from 40 to 240 so that Rosetta will declare these
122 -- as VARCHAR2(300) instead of VARCHAR2(100).
123 item_number VARCHAR2(240),
124 item_description VARCHAR2(240),
125 csi_instance_number csi_item_instances.instance_number%type,
126 serial_number VARCHAR2(30),
127 lot_number MTL_LOT_NUMBERS.LOT_NUMBER%TYPE,
128 revision VARCHAR2(3),
129 uom_code VARCHAR2(3),
130 quantity NUMBER,
131 priority NUMBER,
132 install_date DATE,
133 mfg_date DATE,
134 location_description VARCHAR2(4000),
135 party_type VARCHAR2(30),
136 owner_id NUMBER,
137 owner_number VARCHAR2(360),
138 owner_name VARCHAR2(360),
139 owner_site_id NUMBER,
140 owner_site_number VARCHAR2(30),
141 csi_party_object_version_num NUMBER,
142 status VARCHAR2(80),
143 condition VARCHAR2(240),
144 uc_header_id NUMBER,
145 uc_name VARCHAR2(80),
146 uc_status VARCHAR2(80),
147 mc_header_id NUMBER,
148 mc_name VARCHAR2(80),
149 mc_revision VARCHAR2(30),
150 mc_status VARCHAR2(80),
151 position_ref VARCHAR2(80),
152 wip_entity_name VARCHAR2(240),
153 csi_ii_relationship_ovn NUMBER, --Added in order for assigning an existing extra sibling
154 --node to an sibling empty position
155 subinventory_code VARCHAR2(10),
156 inventory_locator_id NUMBER,
157 locator_segments VARCHAR2(204)
158 );
159 TYPE available_instance_tbl_type IS TABLE OF available_instance_rec_type INDEX BY BINARY_INTEGER;
160
161 -- Start of Comments --
162 -- Define Procedure unassociate_instance_pos
163 -- This API is used to to remove a child instance's position reference but keep
164 -- the parent-child relationship in a UC tree structure (in other word, to make
165 -- the child instance as an extra node in the UC).
166 --
167 -- Procedure name : unassociate_instance_pos
168 -- Type : Private
169 -- Function : To remove a child instance's position reference but keep
170 -- the parent-child relationship in a UC tree structure.
171 -- Pre-reqs :
172 --
173 -- unassociate_instance_pos parameters :
174 -- p_uc_header_id IN NUMBER Required
175 -- p_instance_id IN NUMBER Required
176 -- p_csi_ii_ovn IN NUMBER Required, the origianl object_version_number of the record
177 -- in table csi_ii_relationships where p_instance_id is the subject_id
178 -- p_prod_user_flag IN VARCHAR2(1) Required, to indicate whether the user who
179 -- triggers this functionality is from Production.
180 -- Version : Initial Version 1.0
181 --
182 -- End of Comments --
183 PROCEDURE unassociate_instance_pos (
184 p_api_version IN NUMBER := 1.0,
185 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
186 p_commit IN VARCHAR2 := FND_API.G_FALSE,
187 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
188 x_return_status OUT NOCOPY VARCHAR2,
189 x_msg_count OUT NOCOPY NUMBER,
190 x_msg_data OUT NOCOPY VARCHAR2,
191 p_uc_header_id IN NUMBER,
192 p_instance_id IN NUMBER,
193 p_csi_ii_ovn IN NUMBER,
194 p_prod_user_flag IN VARCHAR2);
195
196 -- Start of Comments --
197 -- Define Procedure remove_instance
198 -- This API is used to to remove to remove an instance (leaf, branch node or
199 -- sub-unit) from a UC node.
200 --
201 -- Procedure name : remove_instance
202 -- Type : Private
203 -- Function : To remove an instance (leaf, branch node or
204 -- sub-unit) from a UC node..
205 -- Pre-reqs :
206 --
207 -- remove_instance parameters :
208 -- p_uc_header_id IN NUMBER Required
209 -- p_instance_id In NUMBER Required
210 -- p_prod_user_flag IN VARCHAR2(1) Required, to indicate whether the user who
211 -- triggers this functionality is from Production.
212 -- Version : Initial Version 1.0
213 --
214 -- End of Comments --
215 PROCEDURE remove_instance (
216 p_api_version IN NUMBER := 1.0,
217 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
218 p_commit IN VARCHAR2 := FND_API.G_FALSE,
219 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
220 x_return_status OUT NOCOPY VARCHAR2,
221 x_msg_count OUT NOCOPY NUMBER,
222 x_msg_data OUT NOCOPY VARCHAR2,
223 p_uc_header_id IN NUMBER,
224 p_instance_id IN NUMBER,
225 p_csi_ii_ovn IN NUMBER,
226 p_prod_user_flag IN VARCHAR2);
227
228 -- Start of Comments --
229 -- Define Procedure update_instance_attr
230 -- This API is used to update an instance's (top node or non top node) attributes
231 -- (serial Number, serial_number_tag, lot_number, revision, mfg_date and etc.)
232 --
233 -- Procedure name : update_instance_attr
234 -- Type : Private
235 -- Function : To remove an instance (leaf, branch node or
236 -- sub-unit) from a UC node.
237 -- Pre-reqs :
238 --
239 -- update_instance_attr parameters :
240 -- p_uc_header_id IN NUMBER Required
241 -- p_instance_id In NUMBER Required
242 -- p_prod_user_flag IN VARCHAR2(1) Required, to indicate whether the user who
243 -- triggers this functionality is from Production.
244 -- Version : Initial Version 1.0
245 --
246 -- End of Comments --
247 PROCEDURE update_instance_attr(
248 p_api_version IN NUMBER := 1.0,
249 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
250 p_commit IN VARCHAR2 := FND_API.G_FALSE,
251 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
252 x_return_status OUT NOCOPY VARCHAR2,
253 x_msg_count OUT NOCOPY NUMBER,
254 x_msg_data OUT NOCOPY VARCHAR2,
255 p_uc_header_id IN NUMBER,
256 p_uc_instance_rec IN uc_instance_rec_type,
257 p_prod_user_flag IN VARCHAR2);
258
259 -- Start of Comments --
260 -- Define procedure install_new_instance
261 -- This API is used to create a new instance in csi_item_instances and assign it
262 -- to a UC node.
263 --
264 -- Procedure name: install_new_instance
265 -- Type: Private
266 -- Function: To create a new instance in csi_item_instances and assign it
267 -- to a UC node.
268 -- Pre-reqs:
269 --
270 -- install_new_instance parameters:
271 -- p_uc_header_id IN NUMBER Required
272 -- p_parent_instance_id IN NUMBER Required, indicates the parent instance_id
273 -- p_x_uc_instance_rec In OUT uc_instance_rec_type Required
274 -- p_x_sub_uc_rec IN OUT uc_header_rec_type
275 -- to store the sub UC header information if also creating a
276 -- sub UC simultaneously
277 -- p_uc_relationship_rec IN uc_relationship_rec_type
278 -- to store the relationship between the parent and the child
279 -- instance
280 -- p_prod_user_flag IN VARCHAR2(1) Required, to indicate whether the user who
281 -- triggers this functionality is from Production.
282 -- Version : Initial Version 1.0
283 --
284 -- End of Comments --
285 PROCEDURE install_new_instance(
286 p_api_version IN NUMBER := 1.0,
287 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
288 p_commit IN VARCHAR2 := FND_API.G_FALSE,
289 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
290 x_return_status OUT NOCOPY VARCHAR2,
291 x_msg_count OUT NOCOPY NUMBER,
292 x_msg_data OUT NOCOPY VARCHAR2,
293 p_uc_header_id IN NUMBER,
294 p_parent_instance_id IN NUMBER,
295 p_prod_user_flag IN VARCHAR2,
296 p_x_uc_instance_rec IN OUT NOCOPY uc_instance_rec_type,
297 p_x_sub_uc_rec IN OUT NOCOPY uc_header_rec_type,
298 x_warning_msg_tbl OUT NOCOPY ahl_uc_validation_pub.error_tbl_type);
299
300 -- Start of Comments --
301 -- Define procedure install_existing_instance
305 -- Type : Private
302 -- This API is used to assign an existing instance to a UC node.
303 --
304 -- Procedure name : install_existing_instance
306 -- Function : To assign an existing instance in csi_item_instances to a UC node.
307 -- Pre-reqs :
308 --
309 -- install_existing_instance parameters :
310 -- p_uc_header_id IN NUMBER Required
311 -- p_parent_instance_id IN NUMBER Required, indicates the parent instance_id
312 -- p_uc_instance_rec IN uc_instance_rec_type Required
313 -- to store the existing instance to be installed
314 -- p_prod_user_flag IN VARCHAR2(1) Required, to indicate whether the user who
315 -- triggers this functionality is from Production.
316 -- Version : Initial Version 1.0
317 --
318 -- End of Comments --
319 PROCEDURE install_existing_instance(
320 p_api_version IN NUMBER := 1.0,
321 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
322 p_commit IN VARCHAR2 := FND_API.G_FALSE,
323 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
324 x_return_status OUT NOCOPY VARCHAR2,
325 x_msg_count OUT NOCOPY NUMBER,
326 x_msg_data OUT NOCOPY VARCHAR2,
327 p_uc_header_id IN NUMBER,
328 p_parent_instance_id IN NUMBER,
329 p_instance_id IN NUMBER,
330 p_instance_number IN csi_item_instances.instance_number%TYPE := NULL,
331 p_relationship_id IN NUMBER,
332 p_csi_ii_ovn IN NUMBER,
333 p_prod_user_flag IN VARCHAR2,
334 x_warning_msg_tbl OUT NOCOPY ahl_uc_validation_pub.error_tbl_type);
335
336 -- Start of Comments --
337 -- Define procedure swap_instances
338 -- This API is used by Production user to make parts change: replace an old instance
339 -- with a new one in a UC tree.
340 --
341 -- Procedure name : swap_instance
342 -- Type : Private
343 -- Function : To replace an old instance a new one in a UC tree.
344 -- Pre-reqs :
345 --
346 -- swap_instances parameters :
347 -- p_uc_header_id IN NUMBER Required, UC header identifier
348 -- p_parent_instance_id IN NUMBER Required, parent instance_id of the instanceto be replaced
349 -- p_old_instance_id IN NUMBER Required, the instance to be replaced
350 -- p_new_instance_rec IN uc_instance_rec_type Required, the new instance to replace the old instance
351 -- p_prod_user_flag IN VARCHAR2(1) Required, to indicate whether the user who
352 -- triggers this functionality is from Production.
353 -- Version : Initial Version 1.0
354 --
355 -- End of Comments --
356 PROCEDURE swap_instance(
357 p_api_version IN NUMBER := 1.0,
358 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
359 p_commit IN VARCHAR2 := FND_API.G_FALSE,
360 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
361 x_return_status OUT NOCOPY VARCHAR2,
362 x_msg_count OUT NOCOPY NUMBER,
363 x_msg_data OUT NOCOPY VARCHAR2,
364 p_uc_header_id IN NUMBER,
365 p_parent_instance_id IN NUMBER,
369 p_relationship_id IN NUMBER,
366 p_old_instance_id IN NUMBER,
367 p_new_instance_id IN NUMBER,
368 p_new_instance_number IN csi_item_instances.instance_number%TYPE := NULL,
370 p_csi_ii_ovn IN NUMBER,
371 p_prod_user_flag IN VARCHAR2,
372 x_warning_msg_tbl OUT NOCOPY ahl_uc_validation_pub.error_tbl_type);
373
374 -- Start of Comments --
375 -- Define procedure get_available_instances
376 -- This API is used to get all the alternate instances for a given node in a UC tree.
377 --
378 -- Procedure name : get_available_instances
379 -- Type : Private
380 -- Function : get all the alternate instances for a given node in a UC tree.
381 -- Pre-reqs :
382 --
383 -- get_available_instances parameters :
384 -- p_relationship_id IN NUMBER Required, to indicate the MC position.
385 -- x_csi_instance_tbl OUT csi_instance_tbl_type Required, to store all the alternate
386 -- instances which could be installed in position p_relationship_id.
387 -- Version : Initial Version 1.0
388 --
389 -- End of Comments --
390 PROCEDURE get_available_instances(
391 p_api_version IN NUMBER := 1.0,
392 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
393 --p_commit IN VARCHAR2 := FND_API.G_FALSE,
394 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
395 x_return_status OUT NOCOPY VARCHAR2,
396 x_msg_count OUT NOCOPY NUMBER,
397 x_msg_data OUT NOCOPY VARCHAR2,
398 p_parent_instance_id IN NUMBER, --in order to include the extra siblings
399 p_relationship_id IN NUMBER,
400 p_item_number IN VARCHAR2 :='%',
401 p_serial_number IN VARCHAR2 :='%',
402 p_instance_number IN VARCHAR2 :='%',
403 p_workorder_id IN NUMBER := NULL, --required by Part Changes
404 p_start_row_index IN NUMBER,
405 p_max_rows IN NUMBER,
406 x_available_instance_tbl OUT NOCOPY available_instance_tbl_type,
407 x_tbl_count OUT NOCOPY NUMBER);
408
409 --****************************************************************************
410 -- Procedure for getting all instances that are available in sub inventory and
411 -- available for installation at a particular UC position.
412 -- Balaji added for OGMA issue # 86
413 --****************************************************************************
414 PROCEDURE Get_Avail_Subinv_Instances(
415 p_api_version IN NUMBER := 1.0,
416 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
417 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
418 x_return_status OUT NOCOPY VARCHAR2,
419 x_msg_count OUT NOCOPY NUMBER,
420 x_msg_data OUT NOCOPY VARCHAR2,
421 p_relationship_id IN NUMBER,
422 p_item_number IN VARCHAR2 :='%',
423 p_serial_number IN VARCHAR2 :='%',
424 p_instance_number IN VARCHAR2 :='%',
425 p_workorder_id IN NUMBER := NULL, --required by Part Changes
426 p_start_row_index IN NUMBER,
427 p_max_rows IN NUMBER,
428 x_avail_subinv_instance_tbl OUT NOCOPY available_instance_tbl_type
429 );
430
431 -- Start of Comments --
432 -- Define procedure create_unassigned_instance.
433 -- This API is used to create a new instance in csi_item_instances and assign it
434 -- to the UC root node as extra node.
435 --
436 -- Procedure name: create_unassigned_instance
437 -- Type : Private
438 -- Function : To create a new instance in csi_item_instances and assign it
439 -- to the UC root node as extra node.
440 -- Pre-reqs:
441 --
442 -- create_unassigned_instance parameters:
443 -- p_uc_header_id IN NUMBER Required
444 -- p_x_uc_instance_rec IN OUT uc_instance_rec_type Required
445 --
446 -- Version : Initial Version 1.0
447 --
448 --
449 -- 18-Nov-2008 SATHAPLI FP ER 6504147 - Created new API create_unassigned_instance.
450 --
451 -- End of Comments --
452 PROCEDURE create_unassigned_instance(
453 p_api_version IN NUMBER := 1.0,
454 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
455 p_commit IN VARCHAR2 := FND_API.G_FALSE,
456 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
457 x_return_status OUT NOCOPY VARCHAR2,
458 x_msg_count OUT NOCOPY NUMBER,
459 x_msg_data OUT NOCOPY VARCHAR2,
460 p_uc_header_id IN NUMBER,
461 p_x_uc_instance_rec IN OUT NOCOPY uc_instance_rec_type);
462
463 END AHL_UC_INSTANCE_PVT; -- Package spec
[Home] [Help]