1 PACKAGE AHL_UTIL_UC_PKG AS
2 /* $Header: AHLUUCS.pls 120.4 2008/03/11 05:49:46 jaramana ship $ */
3
4
5 ---------------------------------------------
6 -- Define Record Type for Item Associations --
7 ----------------------------------------------
8 TYPE Instance_Rec_Type IS RECORD (
9 ITEM_GROUP_ID NUMBER,
10 INVENTORY_ITEM_ID NUMBER,
11 INVENTORY_ORG_ID NUMBER
12 );
13
14 TYPE Instance_Tbl_Type IS TABLE OF Instance_Rec_Type INDEX BY BINARY_INTEGER;
15
16
17 ------------------------------------------------
18 -- SATHAPLI::Bug# 4328454 fix
19 -- Define Record Type for Item Associations for
20 -- API AHL_UTIL_UC_PKG.Check_Invalidate_Instance
21 ------------------------------------------------
22 TYPE Instance_Rec_Type2 IS RECORD (
23 ITEM_GROUP_ID NUMBER,
24 INVENTORY_ITEM_ID NUMBER,
25 INVENTORY_ORG_ID NUMBER,
26 CONCATENATED_SEGMENTS VARCHAR2(40),
27 REVISION VARCHAR2(3),
28 INTERCHANGE_TYPE VARCHAR2(80)
29 );
30
31 TYPE Instance_Tbl_Type2 IS TABLE OF Instance_Rec_Type2 INDEX BY BINARY_INTEGER;
32
33
34 -- Define matched part-posn record structure.
35 TYPE matched_rec_type IS RECORD (
36 object_id NUMBER,
37 subject_id NUMBER,
38 mc_relationship_id NUMBER,
39 csi_ii_relationship_id NUMBER,
40 csi_ii_object_version NUMBER);
41
42
43 -- Define table for matched part-posn records.
44 TYPE matched_tbl_type IS TABLE OF matched_rec_type INDEX BY BINARY_INTEGER;
45
46 -----------------------------------------------------------
47 -- Function to get location description for csi instance --
48 -----------------------------------------------------------
49 FUNCTION GetCSI_LocationDesc(p_location_id IN NUMBER,
50 p_location_type_code IN VARCHAR2,
51 p_inventory_org_id IN NUMBER,
52 p_subinventory_name IN VARCHAR2,
53 p_inventory_locator_id IN NUMBER,
54 p_wip_job_id IN NUMBER)
55 RETURN VARCHAR2;
56
57 pragma restrict_references (GetCSI_LocationDesc, WNDS,WNPS, RNPS);
58
59
60 ------------------------------------------------------
61 -- Function to get location code for a csi instance --
62 ------------------------------------------------------
63 FUNCTION GetCSI_LocationCode(p_location_id IN NUMBER,
64 p_location_type_code IN VARCHAR2)
65
66 RETURN VARCHAR2;
67
68 pragma restrict_references (GetCSI_LocationCode, WNDS,WNPS, RNPS);
69
70
71 ---------------------------------------------------------
72 -- Procedure to get CSI Transaction ID given the code --
73 ---------------------------------------------------------
74 PROCEDURE GetCSI_Transaction_ID(p_txn_code IN VARCHAR2,
75 x_txn_type_id OUT NOCOPY NUMBER,
76 x_return_val OUT NOCOPY BOOLEAN);
77
78
79 ----------------------------------------------------------
80 -- Procedure to get CSI Status ID given the status-name --
81 ----------------------------------------------------------
82 PROCEDURE GetCSI_Status_ID (p_status_name IN VARCHAR2,
83 x_status_id OUT NOCOPY NUMBER,
84 x_return_val OUT NOCOPY BOOLEAN);
85
86
87 ----------------------------------------------------------
88 -- Procedure to get CSI Status name given the status-id --
89 ----------------------------------------------------------
90 PROCEDURE GetCSI_Status_Name (p_status_id IN NUMBER,
91 x_status_name OUT NOCOPY VARCHAR2,
92 x_return_val OUT NOCOPY BOOLEAN);
93
94
95 ---------------------------------------------------------------------
96 -- Procedure to get extended attribute ID given the attribute code --
97 ---------------------------------------------------------------------
98 PROCEDURE GetCSI_Attribute_ID (p_attribute_code IN VARCHAR2,
99 x_attribute_id OUT NOCOPY NUMBER,
100 x_return_val OUT NOCOPY BOOLEAN);
101
102
103 ---------------------------------------------------------------------
104 -- Procedure to get extended attribute value given the attribute code --
105 ---------------------------------------------------------------------
106 PROCEDURE GetCSI_Attribute_Value (p_csi_instance_id IN NUMBER,
107 p_attribute_code IN VARCHAR2,
108 x_attribute_value OUT NOCOPY VARCHAR2,
109 x_attribute_value_id OUT NOCOPY NUMBER,
110 x_object_version_number OUT NOCOPY NUMBER,
111 x_return_val OUT NOCOPY BOOLEAN);
112
113
114 ------------------------------------------------
115 -- Procedure to validate csi_item_instance_id --
116 ------------------------------------------------
117 PROCEDURE ValidateCSI_Item_Instance(p_instance_id IN NUMBER,
118 x_status_name OUT NOCOPY VARCHAR2,
119 x_location_type_code OUT NOCOPY VARCHAR2,
120 x_return_val OUT NOCOPY BOOLEAN);
121
122 ------------------------------------------------------------------------
123 -- Procedure to return lookup meaning given the code from CSI_Lookups --
124 ------------------------------------------------------------------------
125 PROCEDURE Convert_To_CSIMeaning (p_lookup_type IN VARCHAR2,
126 p_lookup_code IN VARCHAR2,
127 x_lookup_meaning OUT NOCOPY VARCHAR2,
128 x_return_val OUT NOCOPY BOOLEAN);
129
130
131 ----------------------------------------------------
132 -- Procedure to check existence of a relationship --
133 -- and if found, returns the position_ref_code --
134 ----------------------------------------------------
135 Procedure ValidateMC_Relationship(p_relationship_id IN NUMBER,
136 x_position_ref_code OUT NOCOPY VARCHAR2,
137 x_return_val OUT NOCOPY BOOLEAN);
138
139
140
141 ------------------------------------------------------------------------------
142 -- Procedure to validate if an inventory item can be assigned to a position --
143 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 04-Dec-2007
144 -- Added p_ignore_quant_vald parameter to allow the quantity checks to be ignored when called from Production.
145 ------------------------------------------------------------------------------
146 PROCEDURE Validate_for_Position(p_mc_relationship_id IN NUMBER,
147 p_Inventory_id IN NUMBER,
148 p_Organization_id IN NUMBER,
149 p_quantity IN NUMBER,
150 p_revision IN VARCHAR2,
151 p_uom_code IN VARCHAR2,
152 p_position_ref_meaning IN VARCHAR2,
153 p_ignore_quant_vald IN VARCHAR2 := 'N',
154 x_item_assoc_id OUT NOCOPY NUMBER);
155
156
157 -------------------------------------------------------
158 -- Procedure to check if item assigned to a position --
159 -------------------------------------------------------
160 PROCEDURE Check_Position_Assigned (p_csi_item_instance_id IN NUMBER,
161 p_mc_relationship_id IN NUMBER,
162 x_subject_id OUT NOCOPY NUMBER,
163 x_return_val OUT NOCOPY BOOLEAN);
164
165 -----------------------------------------------------------------------
166 -- Function will validate if an item is valid for a position or not. --
167 -- It is designed mainly to be used in SQL and views definitions. --
168 -- IT WILL IMPLICITLY INITIALIZE THE ERROR MESSAGE STACK. --
169 -- This will call Validate_for_Position procedure and will return : --
170 -- ahl_item_associations.item_association_id that has been matched --
171 -- else if no record matched, it will return 0(zero). --
172 -- OBSOLETED 10/24/2002.
173 -----------------------------------------------------------------------
174 FUNCTION Validate_Alternate_Item (p_mc_relationship_id IN NUMBER,
175 p_Inventory_id IN NUMBER,
176 p_Organization_id IN NUMBER,
177 p_quantity IN NUMBER,
178 p_revision IN VARCHAR2,
179 p_uom_code IN VARCHAR2) RETURN NUMBER;
180
181
182 ----------------------------------------------------------------------------------
183 -- Procedure to match the parts sub-tree(starting with p_csi_item_instance_id) --
184 -- with the master config tree (starting with p_mc_relationship_id) --
185 -- If the tree matches, x_match_flag returns true else it returns false. The --
186 -- error messages are written to the error stack. --
187 ----------------------------------------------------------------------------------
188 /* comment out by Jerry on 09/16/2004 for bug 3893965
189 PROCEDURE Match_Tree_Components (p_csi_item_instance_id IN NUMBER,
190 p_mc_relationship_id IN NUMBER,
191 x_match_part_posn_tbl OUT NOCOPY AHL_UTIL_UC_PKG.matched_tbl_type,
192 x_match_flag OUT NOCOPY BOOLEAN);
193
194 */
195 --------------------------------------------------------------------------------------------
196 -- Start of Comments --
197 --
198 -- Procedure name : Invalidate_Instance
199 -- Type : Private
200 -- Function : Removes the reference to an Instance that has been deleted
201 -- or referenced from an Item Group.
202 -- Pre-reqs :
203 --
204 -- Standard IN Parameters :
205 -- p_api_version IN NUMBER Required
206 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_TRUE
207 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
208 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
209 -- Standard OUT Parameters :
210 -- x_return_status OUT VARCHAR2 Required
211 -- x_msg_count OUT NUMBER Required
212 -- x_msg_data OUT VARCHAR2 Required
213 --
214 -- Invalidate_Instance parameters :
215 -- p_instance_table IN Instance_Tbl_Type
216 -- A table of inv item id, inv org id and item_group_id
217 --
218 -- History:
219 -- 06/03/03 SBethi CREATED
220 --
221 -- Version :
222 -- Initial Version 1.0
223 --
224 -- End of Comments.
225 --------------------------------------------------------------------------------------------
226
227 PROCEDURE Invalidate_Instance(
228 p_api_version IN NUMBER,
229 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
230 p_commit IN VARCHAR2 := FND_API.G_FALSE,
231 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
232 x_return_status OUT NOCOPY VARCHAR2,
233 x_msg_count OUT NOCOPY NUMBER,
234 x_msg_data OUT NOCOPY VARCHAR2,
235 p_instance_tbl IN Instance_Tbl_Type
236 );
237
238 -- Function to get the Status (Meaning) of a Unit Configuration
239 -- This function considers if the unit is installed in another unit, if it is expired etc.
240 -- It returns the concatenation of the status with the active status if the status
241 -- ic Complete or Incomplete
242 FUNCTION Get_UC_Status(p_uc_header_id IN NUMBER)
243 RETURN VARCHAR2;
244
245 -- Added by Jerry on 03/29/2005 in order for fixing a VWP bug 4251688(Siberian)
246 -- Function to get the Status (code) of a Unit Configuration
247 -- This function considers if the unit is installed in another unit, if it is expired etc.
248 -- This function is similar to the previous one but this one returns code instead of
249 -- meaning. It doesn't check the active status.
250 FUNCTION Get_UC_Status_code(p_uc_header_id IN NUMBER)
251 RETURN VARCHAR2;
252
253 -- Define Procedure copy_uc_header_to_history --
254 -- This common utility API is used to copy a UC header to history table whenever this UC is just newly created
255 -- or updated
256 PROCEDURE copy_uc_header_to_history (
257 p_uc_header_id IN NUMBER,
258 x_return_status OUT NOCOPY VARCHAR2
259 );
260 -- Start of Comments --
261 --
262 -- Procedure name : copy_uc_header_to_history
263 -- Type :
264 -- Function : to copy a UC header to UC header history table.
265 -- Pre-reqs :
266 --
267 -- migrate_uc_tree parameters :
268 -- p_uc_header_id IN NUMBER Required
269 --
270 -- Version :
271 -- Initial Version 1.0
272 --
273 -- End of Comments --
274
275 -- Define Procedure get_root_uc_attr --
276 -- This common utility is used to get the root UC's basic attributes for this particular UC
277 PROCEDURE get_root_uc_attr(
278 p_uc_header_id IN NUMBER,
279 x_uc_header_id OUT NOCOPY NUMBER,
280 x_instance_id OUT NOCOPY NUMBER,
281 x_uc_status_code OUT NOCOPY VARCHAR2,
282 x_active_uc_status_code OUT NOCOPY VARCHAR2,
283 x_uc_header_ovn OUT NOCOPY NUMBER);
284
285 -- Start of Comments --
286 --
287 -- Procedure name : get_root_uc_status_code
288 -- Type :
289 -- Function : to get the root UC's basic attributes for this particular UC
290 -- Pre-reqs :
291 --
292 -- migrate_uc_tree parameters :
293 -- p_uc_header_id IN NUMBER Required
294 --
295 -- Version :
296 -- Initial Version 1.0
297 --
298 -- End of Comments --
299
300 -- Define Function extra_node --
301 -- This common utility is used to check whether a given instance is an extra node
302 FUNCTION extra_node(p_instance_id IN NUMBER, p_top_instance_id NUMBER) RETURN BOOLEAN;
303
304 -- Start of Comments --
305 --
306 -- Procedure name : extra_node
307 -- Type :
308 -- Function : to check whether a given instance is an extra node
309 -- Pre-reqs :
310 --
311 -- migrate_uc_tree parameters :
312 -- p_instance_id IN NUMBER Required
313 -- p_top_instance_id IN NUMBER Required, the instance_id of the top node in which p_instance_id
314 -- is installed
315 --
316 -- Version :
317 -- Initial Version 1.0
318 --
319 -- End of Comments --
320
321 -- Define Function item_match --
322 -- This common utility is used to check whether a given item can be assigned to
323 -- a given position in MC
324 FUNCTION item_match(p_mc_relationship_id IN NUMBER,
325 p_inventory_item_id IN NUMBER,
326 p_organization_id IN NUMBER,
327 p_revision IN VARCHAR2,
328 p_quantity IN NUMBER,
329 p_uom_code IN VARCHAR2)
330 RETURN BOOLEAN;
331 -- Start of Comments --
332 --
333 -- Procedure name : extra_node
334 -- Type :
335 -- Function : to check whether a given instance is an extra node
336 -- Pre-reqs :
337 --
338 -- migrate_uc_tree parameters :
342 -- p_quantity IN NUMBER Required
339 -- p_mc_relationship_id IN NUMBER Required
340 -- p_inventory_item_id IN NUMBER Required
341 -- p_organization_id IN NUMBER Required
343 -- p_uom_code IN NUMBER Required
344 --
345 -- Version :
346 -- Initial Version 1.0
347 --
348 -- End of Comments --
349
350 -- Define procedure get_parent_uc_header --
351 -- This common utility is used to get the parent uc_header_id and parent instance_id
352 -- for a given instance_id. This procedure always returns the parent uc_header_id and
353 -- the instance_id of the parent_uc_header_id (not necessary to be the immediated parent
354 -- instance_id of itself). If the given instance happens to be a standalone unit instance,
355 -- then both the return variables will be null.
356 PROCEDURE get_parent_uc_header(p_instance_id IN NUMBER,
357 x_parent_uc_header_id OUT NOCOPY NUMBER,
358 x_parent_uc_instance_id OUT NOCOPY NUMBER);
359 -- Start of Comments --
360 --
361 -- Procedure name : get_parent_uc_header
362 -- Type :
363 -- Function : to get the parent uc_header_id for a given instance_id
364 -- Pre-reqs :
365 --
366 -- get_parent_uc_header parameters :
367 -- p_instance_id IN NUMBER Required
368 -- x_parent_uc_header_id OUT NUMBER
369 -- x_parent_uc_instance_id OUT NUMBER
370 --
371 -- Version :
372 -- Initial Version 1.0
373 --
374 -- End of Comments --
375
376 ------------------------------------------------------
377 -- Function to map an instance id to a relationship id
378 ------------------------------------------------------
379 FUNCTION Map_Instance_to_RelID(p_csi_ii_id IN NUMBER)
380 RETURN NUMBER;
381
382 -- Define procedure get_unit_name --
383 -- This common utility is used to get the root unit name for a given instance_id
384 -- The unit name is the highest standalone UC name to which the instance_id belongs.
385 -- IF the instance happens to be the root unit instance, then return the unit name
386 -- of itself
387 FUNCTION get_unit_name(p_instance_id IN NUMBER) RETURN VARCHAR2;
388 -- Start of Comments --
389 --
390 -- Procedure name: get_unit_name
391 -- Type:
392 -- Function: to get the root unit name for a given instance_id
393 -- Pre-reqs:
394 --
395 -- Parameters:
396 -- p_instance_id IN NUMBER Required
397 --
398 -- Version:
399 -- Initial Version 1.0
400 --
401 -- End of Comments --
402
403 -- Define procedure get_uc_header_id --
404 -- This common utility is used to get the root uc_header_id for a given instance_id
405 -- The uc_header_id is the highest standalone unit to which the instance belongs.
406 -- IF the instance happens to be the root unit instance, then return the uc_header_id
407 -- of itself
408 FUNCTION get_uc_header_id(p_instance_id IN NUMBER) RETURN NUMBER;
409 -- Start of Comments --
410 --
411 -- Procedure name: get_uc_header_id
412 -- Type:
413 -- Function: to get the root uc_header_id for a given instance_id
414 -- Pre-reqs:
415 --
416 -- Parameters:
417 -- p_instance_id IN NUMBER Required
418 --
419 -- Version:
420 -- Initial Version 1.0
421 --
422 -- End of Comments --
423
424 -- Define function get_sub_unit_name --
425 -- This common utility is used to get the sub unit name for a given instance_id
426 -- The unit name is the lowest sub UC name to which the instance_id belongs.
427 -- IF the instance happens to be the sub unit instance, then return the sub unit name
428 -- of itself
429 FUNCTION get_sub_unit_name(p_instance_id IN NUMBER) RETURN VARCHAR2;
430 -- Start of Comments --
431 --
432 -- Procedure name: get_sub_unit_name
433 -- Type:
434 -- Function: to get the lowerest sub unit name for a given instance_id
435 -- Pre-reqs:
436 --
437 -- Parameters:
438 -- p_instance_id IN NUMBER Required
439 --
440 -- Version:
441 -- Initial Version 1.0
442 --
443 -- End of Comments --
444
445 -- Define function get_sub_uc_header_id --
446 -- This common utility is used to get the sub uc_header_id for a given instance_id
447 -- The uc_header_id is the lowest sub uc_header_id to which the instance_id belongs.
448 -- IF the instance happens to be the sub unit instance, then return the sub uc_header_id
449 -- of itself
450 FUNCTION get_sub_uc_header_id(p_instance_id IN NUMBER) RETURN VARCHAR2;
451 -- Start of Comments --
452 --
453 -- Procedure name: get_sub_uc_header_id
454 -- Type:
455 -- Function: to get the lowerest sub uc_header_id of a given instance_id
456 -- Pre-reqs:
457 --
458 -- Parameters:
459 -- p_instance_id IN NUMBER Required
460 --
461 -- Version:
462 -- Initial Version 1.0
463 --
464 -- End of Comments --
465
466 -- ACL :: Added for R12 changes.
467 FUNCTION IS_UNIT_QUARANTINED(p_unit_header_id IN NUMBER, p_instance_id IN NUMBER) RETURN VARCHAR2;
468 -- Start of Comments --
469 --
470 -- Procedure name: IS_UNIT_QUARANTINED
471 -- Type:
472 -- Function: This API will return FND_API.G_TRUE if a UC is in Quarantine or Deactivate
473 -- Quarantine Status
474 --
475 -- Parameters:
476 -- p_unit_header_id IN NUMBER
477 -- p_instance_id IN NUMBER
478 --
479 -- Version:
480 -- Initial Version 1.0
481 --
482 -- End of Comments --
483
484 --------------------------------------------------------------------------------------------
485 -- Start of Comments --
486 --
487 -- Procedure name : Check_Invalidate_Instance
488 -- Type : Private
489 -- Function : Validates the updation of interchange_type_code in an item group
490 -- against active UCs where the item is installed.
491 -- Pre-reqs :
492 --
493 -- Standard IN Parameters :
494 -- p_api_version IN NUMBER Required
495 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_TRUE
496 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
497 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
498 -- Standard OUT Parameters :
499 -- x_return_status OUT VARCHAR2 Required
500 -- x_msg_count OUT NUMBER Required
501 -- x_msg_data OUT VARCHAR2 Required
502 --
503 -- Check_Invalidate_Instance parameters :
504 -- p_instance_table IN Instance_Tbl_Type2
505 -- A table of inv item id, inv org id, item_group_id, item name, item rev and
506 -- item interchange type
507 --
508 -- History:
509 -- 07-JUN-06 SATHAPLI CREATED
510 --
511 -- Version :
512 -- Initial Version 1.0
513 --
514 -- End of Comments.
515 --------------------------------------------------------------------------------------------
516
517 PROCEDURE Check_Invalidate_Instance
518 (
519 p_api_version IN NUMBER,
520 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_TRUE,
521 p_commit IN VARCHAR2 := FND_API.G_FALSE,
522 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
523 p_instance_tbl IN Instance_Tbl_Type2,
524 p_operator IN VARCHAR2,
525 x_return_status OUT NOCOPY VARCHAR2,
526 x_msg_count OUT NOCOPY NUMBER,
527 x_msg_data OUT NOCOPY VARCHAR2
528 );
529
530 -- Added by jaramana on March 10, 2008 for fixing the Bug 6723950 (FP of 6720010)
531 -- This API will validate if the instance can become the new item through part change.
532 -- p_instance_id can be currently in an IB Tree or UC or it may be a stand alone instance.
533 -- It may also be the root node of a unit.
534 -- The return value x_matches_flag will be FND_API.G_TRUE or FND_API.G_FALSE.
535 PROCEDURE Item_Matches_Instance_Pos(p_inventory_item_id IN NUMBER,
536 p_item_revision IN VARCHAR2 default NULL,
537 p_instance_id IN NUMBER,
538 x_matches_flag OUT NOCOPY VARCHAR2);
539
540 END AHL_UTIL_UC_PKG;
[Home] [Help]