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