1 PACKAGE AHL_MC_PATH_POSITION_PVT AS
2 /* $Header: AHLVPOSS.pls 120.3 2008/02/29 07:53:40 sathapli ship $ */
3
4
5 ---------------------------------
6 -- Define Record Type for Node --
7 ---------------------------------
8 TYPE Path_Position_Rec_Type IS RECORD (
9 PATH_POSITION_ID NUMBER,
10 OBJECT_VERSION_NUMBER NUMBER,
11 LAST_UPDATE_DATE DATE,
12 LAST_UPDATED_BY NUMBER(15) ,
13 CREATION_DATE DATE ,
14 CREATED_BY NUMBER(15) ,
15 LAST_UPDATE_LOGIN NUMBER(15),
16 SEQUENCE NUMBER ,
17 MC_ID NUMBER ,
18 -- MC_NAME and MC_REVISION added by SATHAPLI on Feb 28, 2008 for bug 6845738
19 MC_NAME VARCHAR2(80),
20 MC_REVISION VARCHAR2(30),
21 VERSION_NUMBER NUMBER ,
22 POSITION_KEY NUMBER ,
23 ATTRIBUTE_CATEGORY VARCHAR2(30) ,
24 ATTRIBUTE1 VARCHAR2(150) ,
25 ATTRIBUTE2 VARCHAR2(150) ,
26 ATTRIBUTE3 VARCHAR2(150) ,
27 ATTRIBUTE4 VARCHAR2(150) ,
28 ATTRIBUTE5 VARCHAR2(150) ,
29 ATTRIBUTE6 VARCHAR2(150) ,
30 ATTRIBUTE7 VARCHAR2(150) ,
31 ATTRIBUTE8 VARCHAR2(150) ,
32 ATTRIBUTE9 VARCHAR2(150) ,
33 ATTRIBUTE10 VARCHAR2(150) ,
34 ATTRIBUTE11 VARCHAR2(150) ,
35 ATTRIBUTE12 VARCHAR2(150) ,
36 ATTRIBUTE13 VARCHAR2(150) ,
37 ATTRIBUTE14 VARCHAR2(150) ,
38 ATTRIBUTE15 VARCHAR2(150)
39 );
40
41
42 ---------------------------------
43 -- Define Table Type for Node --
44 ---------------------------------
45
46 TYPE Path_Position_Tbl_Type IS TABLE OF Path_Position_Rec_Type INDEX BY BINARY_INTEGER;
47
48
49 ------------------------
50 -- Declare Procedures --
51 ------------------------
52 --------------------------------
53 -- Start of Comments --
54 -- Procedure name : Create_Position_ID
55 -- Type : Private
56 -- Function : API to create the new path position or if matches
57 -- existing one, return the existing path_position_id
58 -- Pre-reqs :
59 -- Parameters :
60 --
61 -- Create_Position_ID Parameters:
62 -- p_path_position_tbl IN AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type Required
63 -- p_position_ref_meaning IN VARCHAR2 Optional. Position ref for the path
64 -- p_position_ref_code IN VARCHAR2 Optional, create based on pos
65 -- ref code. Used for copying positions.
66 -- x_position_id OUT NUMBER. The created position or the
67 -- existing position id if there is a match.
68 --
69 -- End of Comments.
70
71 PROCEDURE Create_Position_ID (
72 p_api_version IN NUMBER,
73 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
74 p_commit IN VARCHAR2 := FND_API.G_FALSE,
75 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
76 x_return_status OUT NOCOPY VARCHAR2,
77 x_msg_count OUT NOCOPY NUMBER,
78 x_msg_data OUT NOCOPY VARCHAR2,
79 p_path_position_tbl IN AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type,
80 p_position_ref_meaning IN VARCHAR2,
81 p_position_ref_code IN VARCHAR2,
82 x_position_id OUT NOCOPY NUMBER);
83
84 -----------------------------
85 -- Start of Comments --
86 -- Procedure name : Map_Instance_To_Positions
87 -- Type : Private
88 -- Function : Writes a list of positions that maps to instance
89 -- into AHL_APPLICABLE_INSTANCES
90 -- Pre-reqs :
91 -- Parameters :
92 --
93 -- Map_Instance_To_Positions Parameters:
94 -- p_csi_item_instance_id IN NUMBER Required. instance for the path
95 --
96 -- End of Comments.
97
98 PROCEDURE Map_Instance_To_Positions (
99 p_api_version IN NUMBER,
100 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
101 p_commit IN VARCHAR2 := FND_API.G_FALSE,
102 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
103 x_return_status OUT NOCOPY VARCHAR2,
104 x_msg_count OUT NOCOPY NUMBER,
105 x_msg_data OUT NOCOPY VARCHAR2,
106 p_csi_item_instance_id IN NUMBER);
107
108 -----------------------------
109 -- Start of Comments --
110 -- Procedure name : Map_Position_To_Instance
111 -- Type : Private
112 -- Function : Writes a list of instances that maps to position path
113 --into AHL_APPLICABLE_INSTANCES
114 -- Pre-reqs :
115 -- Parameters :
116 --
117 -- Map_Position_To_Instances Parameters:
118 -- p_position_id IN NUMBER Required
119 --
120 -- End of Comments.
121
122 PROCEDURE Map_Position_To_Instances (
123 p_api_version IN NUMBER,
124 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
125 p_commit IN VARCHAR2 := FND_API.G_FALSE,
126 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
127 x_return_status OUT NOCOPY VARCHAR2,
128 x_msg_count OUT NOCOPY NUMBER,
129 x_msg_data OUT NOCOPY VARCHAR2,
130 p_position_id IN NUMBER);
131
132 -----------------------------
133 -- Start of Comments --
134 -- Procedure name : Map_Instance_To_Pos_id
135 -- Type : Private
136 -- Function : For an instance map the position path and return
137 -- version specific path_pos_id. Reverse of the Get_Pos_Instance function
138 -- Pre-reqs :
139 -- Parameters :
140 --
141 -- Map_Instance_To_Pos_id Parameters:
142 -- p_csi_item_instance_id IN NUMBER Required. instance for the pos
143 -- p_relationship_id IN NUMBER Optional. Used for empty position
144 -- x_path_position_id OUT NUMBER the existing or new path pos id
145 --
146 -- End of Comments.
147
148 PROCEDURE Map_Instance_To_Pos_ID (
149 p_api_version IN NUMBER,
150 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
151 p_commit IN VARCHAR2 := FND_API.G_FALSE,
152 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
153 x_return_status OUT NOCOPY VARCHAR2,
154 x_msg_count OUT NOCOPY NUMBER,
155 x_msg_data OUT NOCOPY VARCHAR2,
156 p_csi_item_instance_id IN NUMBER,
157 p_relationship_id IN NUMBER := FND_API.G_MISS_NUM,
158 x_path_position_id OUT NOCOPY NUMBER);
159
160 ----------------------------
161 -- Start of Comments --
162 -- Procedure name : Get_Pos_Instance
163 -- Type : Private
164 -- Function : Returns the instance that maps to position path
165 -- Pre-reqs :
166 -- Parameters :
167 --
168 -- Get_Pos_Instance Parameters:
169 -- p_position_id IN NUMBER Required
170 -- p_csi_item_instance_id IN NUMBER Required starting instance
171 --
172 -- x_item_instance_id the instance that the position_id + instance maps to
173 -- Returns the parent instance_id if the position is empty
174 -- x_relationship_id returns the position relationship id for empty positions
175 -- x_lowest_uc_csi_id returns the leaf level UC id
176 -- x_mapping_status OUT VARCHAR2 Returns either NA (Not applicable),
177 -- EMPTY (Empty position) or MATCH (if matching instance found)
178 -- End of Comments.
179 PROCEDURE Get_Pos_Instance (
180 p_api_version IN NUMBER,
181 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
182 p_commit IN VARCHAR2 := FND_API.G_FALSE,
183 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
184 x_return_status OUT NOCOPY VARCHAR2,
185 x_msg_count OUT NOCOPY NUMBER,
186 x_msg_data OUT NOCOPY VARCHAR2,
187 p_position_id IN NUMBER,
188 p_csi_item_instance_id IN NUMBER,
189 x_parent_instance_id OUT NOCOPY NUMBER,
190 x_item_instance_id OUT NOCOPY NUMBER,
191 x_relationship_id OUT NOCOPY NUMBER,
192 x_lowest_uc_csi_id OUT NOCOPY NUMBER,
193 x_mapping_status OUT NOCOPY VARCHAR2);
194
195 -----------------------------
196 -- Start of Comments --
197 -- Procedure name : Get_Pos_Instance
198 -- Type : Private
199 -- Function : Returns the instance that maps to position path
200 -- Pre-reqs :
201 -- Parameters :
202 --
203 -- Map_Position_To_Instances Parameters:
204 -- p_position_id IN NUMBER Required
205 -- p_csi_item_instance_id IN NUMBER Required starting instance
206 --
207 -- x_item_instance_id the instance that the position_id + instance maps to
208 -- x_lowest_uc_csi_id returns the leaf level UC id
209 -- x_mapping_status OUT VARCHAR2 Returns either NA (Not applicable),
210 -- EMPTY (Empty position) or MATCH (if matching instance found)
211 -- End of Comments.
212
213 PROCEDURE Get_Pos_Instance (
214 p_api_version 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 x_return_status OUT NOCOPY VARCHAR2,
219 x_msg_count OUT NOCOPY NUMBER,
220 x_msg_data OUT NOCOPY VARCHAR2,
221 p_position_id IN NUMBER,
222 p_csi_item_instance_id IN NUMBER,
223 x_item_instance_id OUT NOCOPY NUMBER,
224 x_lowest_uc_csi_id OUT NOCOPY NUMBER,
225 x_mapping_status OUT NOCOPY VARCHAR2);
226
227
228 -----------------------------
229 -- Start of Comments --
230 -- Procedure name : Copy_Positions_For_MC
231 -- Type : Private
232 -- Function : Copies all path positions for 1 MC to another MC
233 -- Pre-reqs :
234 -- Parameters :
235 --
236 -- Copy_Positions_For_MC Parameters:
237 -- p_from_mc_header_id IN NUMBER Required
238 -- p_to_mc_header_id IN NUMBER Required
239 --
240 -- End of Comments.
241
242 PROCEDURE Copy_Positions_For_MC (
243 p_api_version IN NUMBER,
244 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
245 p_commit IN VARCHAR2 := FND_API.G_FALSE,
246 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
247 x_return_status OUT NOCOPY VARCHAR2,
248 x_msg_count OUT NOCOPY NUMBER,
249 x_msg_data OUT NOCOPY VARCHAR2,
250 p_from_mc_header_id IN NUMBER,
251 p_to_mc_header_id IN NUMBER);
252
253
254 -----------------------------
255 -- Start of Comments --
256 -- Procedure name : Copy_Position
257 -- Type : Private
258 -- Function : Copies 1 path positions to 1 MC
259 -- Pre-reqs :
260 -- Parameters :
261 --
262 -- Copy_Position
263 -- p_position_id IN NUMBER Required
264 -- p_to_mc_header_id IN NUMBER Required
265 -- x_positioN_id OUT NUMBER
266 --
267 -- End of Comments.
268
269 PROCEDURE Copy_Position (
270 p_api_version IN NUMBER,
271 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
272 p_commit IN VARCHAR2 := FND_API.G_FALSE,
273 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
274 x_return_status OUT NOCOPY VARCHAR2,
275 x_msg_count OUT NOCOPY NUMBER,
276 x_msg_data OUT NOCOPY VARCHAR2,
277 p_position_id IN NUMBER,
278 p_to_mc_header_id IN NUMBER,
279 x_position_id OUT NOCOPY NUMBER);
280
281 -----------------------------
282 -- Start of Comments --
283 -- Procedure name : Delete_Positions_For_MC
284 -- Type : Private
285 -- Function : Deletes the Positions corresponding to 1 MC
286 -- Pre-reqs :
287 -- Parameters :
288 --
289 -- Delete_Positions_For_MC Parameters:
290 -- p_mc_header_id IN NUMBER Required
291 --
292 -- End of Comments.
293
294 PROCEDURE Delete_Positions_For_MC (
295 p_api_version IN NUMBER,
296 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
297 p_commit IN VARCHAR2 := FND_API.G_FALSE,
298 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
299 x_return_status OUT NOCOPY VARCHAR2,
300 x_msg_count OUT NOCOPY NUMBER,
301 x_msg_data OUT NOCOPY VARCHAR2,
302 p_mc_header_id IN NUMBER);
303
304
305 ---------------------------------------------------------------------
306 -- Start of Comments --
307 -- Function name: get_posref_by_id
308 -- Type : Private
309 -- Function : Fetches the position path position ref code
310 -- Pre-reqs :
311 -- Parameters :
312 --
313 -- get_position_ref_code Parameters:
314 -- p_position_id IN NUMBER the path position id
315 -- p_code_flag IN VARHCAR2 If Equal to FND_API.G_TRUE, then return
316 -- pos ref code, else return pos ref meaning. Default to False.
317 --
318 FUNCTION get_posref_by_id(
319 p_path_position_ID IN NUMBER,
320 p_code_flag IN VARCHAR2 := FND_API.G_FALSE)
321 RETURN VARCHAR2; -- Position Ref Code
322
323 ---------------------------------------------------------------------
324 -- Start of Comments --
325 -- Function name: get_posref_by_path
326 -- Type : Private
327 -- Function : Fetches the position path position ref code
328 -- Pre-reqs :
329 -- Parameters :
330 --
331 -- get_position_ref_code Parameters:
332 -- p_position_path_tbl IN AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type Required
333 -- p_code_flag IN VARHCAR2 If Equal to FND_API.G_TRUE, then return
334 -- pos ref code, else return pos ref meaning. Default to False.
335 --
336 FUNCTION get_posref_by_path(
337 p_path_position_tbl IN AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type,
338 p_code_flag IN VARCHAR2 := FND_API.G_FALSE
339 )
340 RETURN VARCHAR2; -- Position Ref Code
341
342 ---------------------------------------------------------------------
343 -- Start of Comments --
344 -- Function name: get_posref_for_uc
345 -- Type : Private
346 -- Function : Fetches the position path position ref code
347 -- Pre-reqs :
348 -- Parameters :
349 --
350 -- get_position_ref_code Parameters:
351 -- p_uc_header_id IN NUMBER UNIT CONFIG header id
352 -- p_relationship_id IN NUMBER position of subunit
353 --
354 FUNCTION get_posref_for_uc(
355 p_uc_header_id IN NUMBER,
356 p_relationship_id IN NUMBER
357 )
358 RETURN VARCHAR2; -- Position Ref Meaning
359
360 ---------------------------------------------------------------------
361 -- Start of Comments --
362 -- Function name: get_encoded_path
363 -- Type : Private
364 -- Function : Fetches the position path encoding based on input
365 -- Pre-reqs :
366 -- Parameters :
367 --
368 -- get_encoded_path Parameters:
369 -- p_parent_path IN VARCHAR2. encoded parent position path
370 -- p_mc_id IN NUMBER.
371 -- p_ver_num IN NUMBER.
372 -- p_position_key IN NUMBER.
373 -- p_subconfig_flag IN BOOLEAN indicates whether this is new subconfig
374 --
378 p_ver_num IN NUMBER,
375 FUNCTION get_encoded_path(
376 p_parent_path IN VARCHAR2,
377 p_mc_id IN NUMBER,
379 p_position_key IN NUMBER,
380 p_subconfig_flag IN VARCHAR2
381 )
382 RETURN VARCHAR2; -- New encoded path
383
384 ---------------------------------------------------------------------
385 -- Start of Comments --
386 -- Function name: check_pos_ref_path
387 -- Type : Private
388 -- Function :
389 -- Check that the path from instance to to instance has position ref each step
390 -- and that position ref is not null for all relnships.
391 -- Pre-reqs :
392 -- Parameters : p_from_csi_id NUMBER the from instance id
393 -- p_to_csi_id NUMBER the instance id that it reaches
394 --
395 --
396 FUNCTION check_pos_ref_path(
397 p_from_csi_id IN NUMBER,
398 p_to_csi_id IN NUMBER)
399 RETURN BOOLEAN;
400
401 ---------------------------------------------------------------------
402 -- Start of Comments --
403 -- Function name: check_pos_ref_path_char
404 -- Type : Private
405 -- Function : Calls private function Check_pos_ref_path and returns
406 -- value as 'T' for Boolean TRUE and
407 -- 'F' for Boolean False.
408 -- Pre-reqs :
409 -- Parameters : p_from_csi_id NUMBER the from instance id
413 FUNCTION check_pos_ref_path_char(
410 -- p_to_csi_id NUMBER the instance id that it reaches
411 --
412 --
414 p_from_csi_id IN NUMBER,
415 p_to_csi_id IN NUMBER)
416 RETURN VARCHAR2;
417
418
419 ---------------------------------------------------------------------
420 -- Start of Comments --
421 -- Function name: encode
422 -- Type : Private
423 -- Function :
424 -- Encodes the path position for the path position id
425 -- Pre-reqs :
426 -- Parameters : p_path_position_tbl path position information
427 --
428
429 FUNCTION Encode(
430 p_path_position_tbl IN AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type
431 ) RETURN VARCHAR2;
432
433
434 ---------------------------------------------------------------------
435 -- Start of Comments --
436 -- Function name: Is_Position_Serial_Controlled
437 -- Type : Private
438 -- Function : Cretaed for FP OGMA Issue# 105 - Non-Serialized Item Maintenance.
439 -- Checks whether a position accepts a serialized item instance or not.
440 -- Returns 'Y' if item group attached to the position has first associated item as serialized.
441 -- Returns 'N' otherwise.
442 -- Pre-reqs :
443 -- Parameters : p_relationship_id NUMBER relationship id
444 -- p_path_position_id NUMBER path posiiton id
445 --
446 -- If relationship id is passed, it will be taken to determine the result.
447 -- Position id will be used only when relationship id is NULL.
448 --
449
450 FUNCTION Is_Position_Serial_Controlled(
451 p_relationship_id IN NUMBER,
452 p_path_position_id IN NUMBER
453 ) RETURN VARCHAR2;
454
455 End AHL_MC_PATH_POSITION_PVT;
[Home] [Help]