DBA Data[Home] [Help]

PACKAGE: APPS.AHL_UC_INSTANCE_PVT

Source


1 PACKAGE AHL_UC_INSTANCE_PVT AUTHID CURRENT_USER AS
2 /* $Header: AHLVUCIS.pls 120.6.12020000.2 2012/12/07 15:41:29 sareepar 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
302 -- This API is used to assign an existing instance to a UC node.
303 --
304 -- Procedure name  : install_existing_instance
305 -- Type        	: Private
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
318 --
315 --                      triggers this functionality is from Production.
316 --   p_validate_rule_flag  IN VARCHAR2  Optional, to bypass the completeness validation.
317 -- Version : Initial Version   1.0
319 -- End of Comments  --
320 PROCEDURE install_existing_instance(
321   p_api_version           IN  NUMBER := 1.0,
322   p_init_msg_list         IN  VARCHAR2 := FND_API.G_FALSE,
323   p_commit                IN  VARCHAR2 := FND_API.G_FALSE,
324   p_validation_level      IN  NUMBER := FND_API.G_VALID_LEVEL_FULL,
325   x_return_status         OUT NOCOPY VARCHAR2,
326   x_msg_count             OUT NOCOPY NUMBER,
327   x_msg_data              OUT NOCOPY VARCHAR2,
328   p_uc_header_id          IN  NUMBER,
329   p_parent_instance_id    IN  NUMBER,
330   p_instance_id           IN  NUMBER,
331   p_instance_number       IN  csi_item_instances.instance_number%TYPE := NULL,
332   p_relationship_id       IN  NUMBER,
333   p_csi_ii_ovn            IN  NUMBER,
334   p_prod_user_flag        IN  VARCHAR2,
335   -- SURRKUMA :: 13694898 :: Flag to bypass the validation of position completeness, 29-FEB-2012
336   p_validate_rule_flag    IN  VARCHAR2  := FND_API.G_TRUE,
337   x_warning_msg_tbl       OUT NOCOPY ahl_uc_validation_pub.error_tbl_type);
338 
339 -- Start of Comments  --
340 -- Define procedure swap_instances
341 -- This API is used by Production user to make parts change: replace an old instance
342 -- with a new one in a UC tree.
343 --
344 -- Procedure name  : swap_instance
345 -- Type        	: Private
346 -- Function    	: To replace an old instance a new one in a UC tree.
347 -- Pre-reqs    	:
348 --
349 -- swap_instances parameters :
350 --   p_uc_header_id     IN NUMBER  Required, UC header identifier
351 --   p_parent_instance_id IN NUMBER  Required, parent instance_id of the instanceto be replaced
352 --   p_old_instance_id  IN NUMBER  Required, the instance to be replaced
353 --   p_new_instance_rec IN uc_instance_rec_type Required, the new instance to replace the old instance
354 --   p_prod_user_flag   IN VARCHAR2(1)  Required, to indicate whether the user who
355 --                      triggers this functionality is from Production.
356 -- Version : Initial Version   1.0
357 --
358 -- End of Comments  --
359 PROCEDURE swap_instance(
360   p_api_version           IN  NUMBER := 1.0,
361   p_init_msg_list         IN  VARCHAR2 := FND_API.G_FALSE,
362   p_commit                IN  VARCHAR2 := FND_API.G_FALSE,
363   p_validation_level      IN  NUMBER := FND_API.G_VALID_LEVEL_FULL,
364   x_return_status         OUT NOCOPY VARCHAR2,
365   x_msg_count             OUT NOCOPY NUMBER,
366   x_msg_data              OUT NOCOPY VARCHAR2,
367   p_uc_header_id          IN  NUMBER,
368   p_parent_instance_id    IN  NUMBER,
369   p_old_instance_id       IN  NUMBER,
370   p_new_instance_id       IN  NUMBER,
371   p_new_instance_number   IN  csi_item_instances.instance_number%TYPE := NULL,
372   p_relationship_id       IN  NUMBER,
373   p_csi_ii_ovn            IN  NUMBER,
374   p_prod_user_flag        IN  VARCHAR2,
375   x_warning_msg_tbl       OUT NOCOPY ahl_uc_validation_pub.error_tbl_type);
376 
377 -- Start of Comments  --
378 -- Define procedure get_available_instances
379 -- This API is used to get all the alternate instances for a given node in a UC tree.
380 --
381 -- Procedure name  : get_available_instances
382 -- Type        	: Private
383 -- Function    	:  get all the alternate instances for a given node in a UC tree.
384 -- Pre-reqs    	:
385 --
386 -- get_available_instances parameters :
387 --   p_relationship_id  IN NUMBER  Required, to indicate the MC position.
388 --   x_csi_instance_tbl OUT csi_instance_tbl_type Required, to store all the alternate
389 --                     instances which could be installed in position p_relationship_id.
390 -- Version : Initial Version   1.0
391 --
392 --  End of Comments  --
393 PROCEDURE get_available_instances(
394   p_api_version            IN  NUMBER := 1.0,
395   p_init_msg_list          IN  VARCHAR2 := FND_API.G_FALSE,
396   --p_commit                 IN  VARCHAR2 := FND_API.G_FALSE,
397   p_validation_level       IN  NUMBER := FND_API.G_VALID_LEVEL_FULL,
398   x_return_status          OUT NOCOPY VARCHAR2,
399   x_msg_count              OUT NOCOPY NUMBER,
400   x_msg_data               OUT NOCOPY VARCHAR2,
401   p_parent_instance_id     IN  NUMBER, --in order to include the extra siblings
402   p_relationship_id        IN  NUMBER,
403   p_item_number            IN  VARCHAR2 :='%',
404   p_serial_number          IN  VARCHAR2 :='%',
405   p_instance_number        IN  VARCHAR2 :='%',
406   p_workorder_id           IN  NUMBER := NULL, --required by Part Changes
407   p_start_row_index        IN  NUMBER,
408   p_max_rows               IN  NUMBER,
409   x_available_instance_tbl OUT NOCOPY available_instance_tbl_type,
410   x_tbl_count              OUT NOCOPY NUMBER);
411 
412   --****************************************************************************
413    -- Procedure for getting all instances that are available in sub inventory and
414    -- available for installation at a particular UC position.
415    -- Balaji added for OGMA issue # 86
416    --****************************************************************************
417    PROCEDURE Get_Avail_Subinv_Instances(
418      p_api_version            IN  NUMBER := 1.0,
419      p_init_msg_list          IN  VARCHAR2 := FND_API.G_FALSE,
420      p_validation_level       IN  NUMBER := FND_API.G_VALID_LEVEL_FULL,
421      x_return_status          OUT NOCOPY VARCHAR2,
422      x_msg_count              OUT NOCOPY NUMBER,
423      x_msg_data               OUT NOCOPY VARCHAR2,
424      p_relationship_id        IN  NUMBER,
425      p_item_number            IN  VARCHAR2 :='%',
426      p_serial_number          IN  VARCHAR2 :='%',
427      p_instance_number        IN  VARCHAR2 :='%',
428      p_workorder_id           IN  NUMBER := NULL, --required by Part Changes
429      p_start_row_index        IN  NUMBER,
430      p_max_rows               IN  NUMBER,
431      x_avail_subinv_instance_tbl OUT NOCOPY available_instance_tbl_type
432    );
433 
434 -- Start of Comments  --
435 -- Define procedure create_unassigned_instance.
436 -- This API is used to create a new instance in csi_item_instances and assign it
437 -- to the UC root node as extra node.
438 --
439 -- Procedure name: create_unassigned_instance
440 -- Type          : Private
441 -- Function      : To create a new instance in csi_item_instances and assign it
442 --                 to the UC root node as extra node.
443 -- Pre-reqs:
444 --
445 -- create_unassigned_instance parameters:
446 --   p_uc_header_id       IN     NUMBER                Required
447 --   p_x_uc_instance_rec  IN OUT uc_instance_rec_type  Required
448 --
449 --  Version : Initial Version   1.0
450 --
451 --
452 --  18-Nov-2008    SATHAPLI    FP ER 6504147 - Created new API create_unassigned_instance.
453 --
454 --  End of Comments  --
455 PROCEDURE create_unassigned_instance(
456     p_api_version           IN            NUMBER   := 1.0,
457     p_init_msg_list         IN            VARCHAR2 := FND_API.G_FALSE,
458     p_commit                IN            VARCHAR2 := FND_API.G_FALSE,
459     p_validation_level      IN            NUMBER   := FND_API.G_VALID_LEVEL_FULL,
460     x_return_status         OUT    NOCOPY VARCHAR2,
461     x_msg_count             OUT    NOCOPY NUMBER,
462     x_msg_data              OUT    NOCOPY VARCHAR2,
463     p_uc_header_id          IN            NUMBER,
464     p_x_uc_instance_rec     IN OUT NOCOPY uc_instance_rec_type);
465 
466 -- Start of Comments  --
467 -- Procedure get_instances_for_req
468 -- This API is used to get all the instances for a given Position Based Material Requirement.
469 -- It is similar to get_available_instances, but differs in the following ways:
470 -- 1. It gets instances in Inventory also
471 -- 2. It populates the global temp table AHL_APPLICABLE_INSTANCES instead of returing a table of instances.
472 --    (POSITION_ID is set to p_relationship_id and not to the path position id)
473 -- 3. It validates the item against the SB rules to return only instances that conform to SB rules if applicable.
474 --
475 -- Procedure name  : get_instances_for_req
476 -- Type        	   : Private
477 -- Function    	   : Get all applicable instances for a given Position Based Material Requirement.
478 -- Pre-reqs    	:
479 --
480 -- get_instances_for_req parameters :
481 --   p_schedule_material_id  IN NUMBER  Required: Specifies the Position Based Material Requirement
482 --   p_inventory_item_id     IN NUMBER DEFAULT NULL: If given, gets instances of this item only
483 --   p_supply_location_type  IN  VARCHAR2 DEFAULT NULL: Can be 'INVENTORY' to get only instances in inventory
484 --   p_organization_name     IN  VARCHAR2 DEFAULT '%': Used only if p_supply_location_type is INVENTORY.
485 --                                                     Name of inv org to look for instances; Supports wildcards
486 --   p_serial_number         IN NUMBER DEFAULT '%': If given, gets instances with this serial only; Supports wildcards
487 --
488 -- Version : Initial Version   1.0 (written for the USAF Marshaling project by jaramana on 07-FEB-2012
489 --
490 --  End of Comments  --
491 PROCEDURE get_instances_for_req(
492   p_api_version            IN  NUMBER := 1.0,
493   p_init_msg_list          IN  VARCHAR2 := FND_API.G_FALSE,
494   x_return_status          OUT NOCOPY VARCHAR2,
495   x_msg_count              OUT NOCOPY NUMBER,
496   x_msg_data               OUT NOCOPY VARCHAR2,
497   p_schedule_material_id   IN  NUMBER,
498   p_inventory_item_id      IN  NUMBER DEFAULT NULL,
499   p_supply_location_type   IN  VARCHAR2 DEFAULT NULL,
500   p_organization_name      IN  VARCHAR2 DEFAULT '%',
501   p_serial_number          IN  VARCHAR2 DEFAULT '%');
502 
503 FUNCTION is_instance_valid_for_pos(
504    p_instance_id          IN NUMBER,
505    p_relationship_id      IN NUMBER,
506    p_schedule_material_id IN NUMBER DEFAULT NULL)
507 RETURN VARCHAR2;
508 
509 END AHL_UC_INSTANCE_PVT; -- Package spec