1 PACKAGE BODY AHL_LTP_MTL_REQ_PVT AS
2 /* $Header: AHLVLMRB.pls 120.1.12010000.2 2008/11/12 06:28:21 skpathak ship $ */
3
4 -----------------------
5 -- Declare Constants --
6 -----------------------
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_LTP_MTL_REQ_PVT';
8
9 G_LOG_PREFIX CONSTANT VARCHAR2(100) := 'ahl.plsql.AHL_LTP_MTL_REQ_PVT';
10
11 G_NO_FLAG CONSTANT VARCHAR2(1) := 'N';
12 G_YES_FLAG CONSTANT VARCHAR2(1) := 'Y';
13
14 G_APP_MODULE CONSTANT VARCHAR2(30) := 'AHL';
15
16 -- Mtl Req Association Types
17 G_ASSOC_TYPE_DISPOSITION CONSTANT VARCHAR2(30) := 'DISPOSITION';
18 G_ASSOC_TYPE_ROUTE CONSTANT VARCHAR2(30) := 'ROUTE';
19 G_ASSOC_TYPE_OPERATION CONSTANT VARCHAR2(30) := 'OPERATION';
20
21 -- Requirement Types
22 G_REQ_TYPE_FORECAST CONSTANT VARCHAR2(30) := 'FORECAST';
23 G_REQ_TYPE_PLANNED CONSTANT VARCHAR2(30) := 'PLANNED';
24
25 -- Mapping Status for Position
26 G_MAPPING_STATUS_MATCH CONSTANT VARCHAR2(30) := 'MATCH';
27 G_MAPPING_STATUS_EMPTY CONSTANT VARCHAR2(30) := 'EMPTY';
28 G_MAPPING_STATUS_NA CONSTANT VARCHAR2(30) := 'NA';
29
30 -- Added by skpathak on 06-NOV-2008 for bug-7336824
31 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
32 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
33 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
34 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
35 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
36
37 -------------------------------------------------
38 -- Declare Locally used Record and Table Types --
39 -------------------------------------------------
40
41 -------------------------------------------
42 -- Declare Local Procedures and Function --
43 -------------------------------------------
44
45 -- This Procedure validates the input for the Get_Route_Mtl_Req API
46 PROCEDURE Validate_Mtl_Req_Input(
47 p_route_id IN NUMBER,
48 p_mr_route_id IN NUMBER,
49 p_item_instance_id IN NUMBER,
50 p_requirement_date IN DATE,
51 p_request_type IN VARCHAR2,
52 x_route_id OUT NOCOPY NUMBER);
53
54 -- This Procedure gets the requirements from the Disposition List
55 PROCEDURE Get_Disp_List_Requirements(
56 p_route_id IN NUMBER,
57 p_item_instance_id IN NUMBER,
58 p_requirement_date IN DATE,
59 p_request_type IN VARCHAR2,
60 p_unit_instance_id IN NUMBER,
61 p_inst_item_id IN NUMBER,
62 p_mc_header_id IN NUMBER,
63 p_mc_id IN NUMBER,
64 p_mc_version IN NUMBER,
65 x_disp_req_list OUT NOCOPY Route_Mtl_Req_Tbl_Type);
66
67 -- This Procedure gets the Position Path Requirement
68 PROCEDURE Get_Pos_Path_Requirement(
69 p_position_path_id IN NUMBER,
70 p_requirement_date IN DATE,
71 p_unit_instance_id IN NUMBER,
72 p_mc_header_id IN NUMBER,
73 p_mc_id IN NUMBER,
74 p_mc_version IN NUMBER,
75 p_rt_oper_mtl_id IN NUMBER,
76 p_quantity IN NUMBER,
77 p_uom IN VARCHAR2,
78 p_x_disp_req_list IN OUT NOCOPY Route_Mtl_Req_Tbl_Type);
79
80 -- This Procedure gets the requirements from the Route (or Operation)
81 PROCEDURE Get_Route_Requirements(
82 p_route_id IN NUMBER,
83 p_request_type IN VARCHAR2,
84 x_route_req_list OUT NOCOPY Route_Mtl_Req_Tbl_Type);
85
86 -- This Procedure validates a Position Path against a given unit and date.
87 -- It returns a status flag and if valid, it get the details about the position.
88 PROCEDURE Validate_Path_Position(
89 p_path_position_id IN NUMBER,
90 p_unit_instance_id IN NUMBER,
91 p_requirement_date IN DATE,
92 x_valid_flag OUT NOCOPY VARCHAR2,
93 x_relationship_id OUT NOCOPY NUMBER,
94 x_item_group_id OUT NOCOPY NUMBER,
95 x_pos_instance_id OUT NOCOPY NUMBER);
96
97 -- This Function gets the unit's instance id
98 FUNCTION Get_Unit_Instance(
99 p_item_instance_id IN NUMBER) RETURN NUMBER;
100
101
102 -------------------------------------
103 -- End Local Procedures Declaration--
104 -------------------------------------
105
106 -----------------------------------------
107 -- Public Procedure Definitions follow --
108 -----------------------------------------
109 -- Start of Comments --
110 -- Procedure name : Get_Route_Mtl_Req
111 -- Type : Private
112 -- Function : Private API to get the Material requirements for a Route.
113 -- For FORECAST request type, it aggregates requirements at the
114 -- route level (across operations), and gets the highest priority item
115 -- ignoring the inventory org. Also, a disposition list requirement is
116 -- considered for FORECAST only if the REPLACE_PERCENT = 100%.
117 -- For PLANNED, no aggregation is done, NO specific item is obtained
118 -- within an item group and the REPLACE_PERCENT is not considered.
119 -- Pre-reqs :
120 -- Parameters :
121 --
122 -- Standard IN Parameters :
123 -- p_api_version IN NUMBER Required
124 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
125 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
126 --
127 -- Standard OUT Parameters :
128 -- x_return_status OUT VARCHAR2 Required
129 -- x_msg_count OUT NUMBER Required
130 -- x_msg_data OUT VARCHAR2 Required
131 --
132 -- Get_Route_Mtl_Req Parameters:
133 -- p_route_id IN NUMBER Not Required only if p_mr_route_id is not null
134 -- The Id of Route for which to determine the material requirements
135 -- p_mr_route_id IN NUMBER Not Required only if p_route_id is not null
136 -- The Id of MR Route for which to determine the material requirements
137 -- p_item_instance_id IN NUMBER Required
138 -- The Id of Instance for which to plan the material requirements
139 -- p_requirement_date IN DATE Not Required
140 -- The date when the materials are required. If provided, the positions of Master Configs
141 -- (for position path based disposition list requirement) are validated against this date.
142 -- p_request_type IN VARCHAR2 Required
143 -- Should be either 'FORECAST' or 'PLANNED'
144 -- x_route_mtl_req_tbl OUT AHL_LTP_MTL_REQ_PVT.Route_Mtl_Req_Tbl Required
145 -- The Table of records containing the material requirements for the route
146 --
147 -- Version :
148 -- Initial Version 1.0
149 --
150 -- End of Comments.
151
152 PROCEDURE Get_Route_Mtl_Req
153 (
154 p_api_version IN NUMBER,
155 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
156 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
157 x_return_status OUT NOCOPY VARCHAR2,
158 x_msg_count OUT NOCOPY NUMBER,
159 x_msg_data OUT NOCOPY VARCHAR2,
160 p_route_id IN NUMBER,
161 p_mr_route_id IN NUMBER,
162 p_item_instance_id IN NUMBER,
163 p_requirement_date IN DATE := null,
164 p_request_type IN VARCHAR2,
165 x_route_mtl_req_tbl OUT NOCOPY AHL_LTP_MTL_REQ_PVT.Route_Mtl_Req_Tbl_Type) IS
166
167 CURSOR get_mc_dtls_csr(c_instance_id IN NUMBER) IS
168 select UC.MASTER_CONFIG_ID, MC.MC_ID, MC.VERSION_NUMBER
169 from AHL_UNIT_CONFIG_HEADERS UC, AHL_MC_HEADERS_B MC
170 where UC.CSI_ITEM_INSTANCE_ID = c_instance_id AND
171 MC.MC_HEADER_ID = UC.MASTER_CONFIG_ID;
172
173 CURSOR get_item_from_instance_csr(c_instance_id IN NUMBER) IS
174 select INVENTORY_ITEM_ID from CSI_ITEM_INSTANCES
175 where INSTANCE_ID = c_instance_id;
176
177 CURSOR get_item_group_item_csr(c_item_group_id IN NUMBER) IS
178 SELECT inventory_item_id, inventory_org_id
179 FROM ahl_item_associations_b
180 WHERE item_group_id = c_item_group_id
181 AND interchange_type_code in ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
182 ORDER BY priority;
183
184 l_route_id NUMBER;
185 l_unit_instance_id NUMBER;
186 l_inst_item_id NUMBER;
187 l_mc_header_id NUMBER;
188 l_mc_id NUMBER;
189 l_mc_version NUMBER;
190 l_disp_req_list AHL_LTP_MTL_REQ_PVT.Route_Mtl_Req_Tbl_Type;
191 l_route_req_list AHL_LTP_MTL_REQ_PVT.Route_Mtl_Req_Tbl_Type;
192 l_index NUMBER;
193 l_temp_item_id NUMBER;
194 l_temp_ig_id NUMBER;
195 l_found BOOLEAN;
196 l_api_version CONSTANT NUMBER := 1.0;
197 l_api_name CONSTANT VARCHAR2(30) := 'Get_Route_Mtl_Req';
198 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Get_Route_Mtl_Req';
199
200 BEGIN
201
202 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
203 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
204 END IF;
205 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
206 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Input values for the Procedure: ' ||
207 'p_route_id = ' || p_route_id ||
208 ', p_mr_route_id = ' || p_mr_route_id ||
209 ', p_item_instance_id = ' || p_item_instance_id ||
210 ', p_requirement_date = ' || p_requirement_date ||
211 ', p_request_type = ' || p_request_type);
212 END IF;
213
214 -- Standard call to check for call compatibility
215 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
216 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
217 END IF;
218
219 -- Initialize message list if p_init_msg_list is set to TRUE
220 IF FND_API.To_Boolean(p_init_msg_list) THEN
221 FND_MSG_PUB.Initialize;
222 END IF;
223
224 -- Initialize API return status to success
225 x_return_status := FND_API.G_RET_STS_SUCCESS;
226
227 -- Begin Processing
228 -- First Validate the input parameters
229 Validate_Mtl_Req_Input(p_route_id => p_route_id,
230 p_mr_route_id => p_mr_route_id,
231 p_item_instance_id => p_item_instance_id,
232 p_requirement_date => p_requirement_date,
233 p_request_type => p_request_type,
234 x_route_id => l_route_id);
235
236 IF (FND_MSG_PUB.Count_Msg > 0) THEN
237 -- There are validation errors: Raise error
238 RAISE FND_API.G_EXC_ERROR;
239 END IF;
240
241 -- Get the Unit Instance
242 l_unit_instance_id := Get_Unit_Instance(p_item_instance_id);
243 IF (l_unit_instance_id IS NOT NULL) THEN
244 -- Instance is in a UC: Get the MC Details
245 OPEN get_mc_dtls_csr(c_instance_id => l_unit_instance_id);
246 FETCH get_mc_dtls_csr INTO l_mc_header_id, l_mc_id, l_mc_version;
247 CLOSE get_mc_dtls_csr;
248 END IF;
249
250 -- Get item of instance
251 OPEN get_item_from_instance_csr(c_instance_id => p_item_instance_id);
252 FETCH get_item_from_instance_csr INTO l_inst_item_id;
253 CLOSE get_item_from_instance_csr;
254
255 -- Get the Requirements from the Disposition List
256 Get_Disp_List_Requirements(p_route_id => l_route_id,
257 p_item_instance_id => p_item_instance_id,
258 p_requirement_date => p_requirement_date,
259 p_request_type => p_request_type,
260 p_unit_instance_id => l_unit_instance_id,
261 p_inst_item_id => l_inst_item_id,
262 p_mc_header_id => l_mc_header_id,
263 p_mc_id => l_mc_id,
264 p_mc_version => l_mc_version,
265 x_disp_req_list => l_disp_req_list);
266
267 -- Get the Requirements from the Route
268 Get_Route_Requirements(p_route_id => l_route_id,
269 p_request_type => p_request_type,
270 x_route_req_list => l_route_req_list);
271
272 -- Merge the two list of requirements
273 IF (l_disp_req_list.COUNT = 0) THEN
274 x_route_mtl_req_tbl := l_route_req_list;
275 ELSIF (l_route_req_list.COUNT = 0) THEN
276 x_route_mtl_req_tbl := l_disp_req_list;
277 ELSE
278 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
279 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'l_disp_req_list.COUNT = ' || l_disp_req_list.COUNT);
280 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'l_route_req_list.COUNT = ' || l_route_req_list.COUNT);
281 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Both lists are non-empty. Merging...');
282 END IF;
283 x_route_mtl_req_tbl := l_disp_req_list;
284 l_index := x_route_mtl_req_tbl.COUNT;
285 FOR i in l_route_req_list.FIRST .. l_route_req_list.LAST LOOP
286 l_temp_item_id := NVL(l_route_req_list(i).INVENTORY_ITEM_ID, -1);
287 l_temp_ig_id := NVL(l_route_req_list(i).ITEM_GROUP_ID, -1);
288 l_found := FALSE;
289 FOR j in l_disp_req_list.FIRST .. l_disp_req_list.LAST LOOP
290 IF ((NVL(l_disp_req_list(j).INVENTORY_ITEM_ID, -2) = l_temp_item_id) OR
291 (NVL(l_disp_req_list(j).ITEM_GROUP_ID, -2) = l_temp_ig_id)) THEN
292 -- The route requirement exists in the Disposition list also
293 l_found := TRUE;
294 EXIT;
295 END IF;
296 END LOOP;
297 IF(l_found = FALSE) THEN
298 -- Add this Route requirement to the combined list
299 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
300 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Adding Route requirement with index ' || i ||
301 ' to the combined list.');
302 END IF;
303 l_index := l_index + 1;
304 x_route_mtl_req_tbl(l_index) := l_route_req_list(i);
305 ELSE
306 -- Duplicate: Ignore
307 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
308 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Route requirement with index ' || i || ' is a duplicate. Ignoring.');
309 END IF;
310 END IF;
311 END LOOP;
312 END IF;
313
314 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
315 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Size of the merged list: ' || x_route_mtl_req_tbl.COUNT);
316 END IF;
317
318 -- Get the first item for item groups if forecasting
319 IF(x_route_mtl_req_tbl.COUNT > 0 AND p_request_type = G_REQ_TYPE_FORECAST) THEN
320 FOR i in x_route_mtl_req_tbl.FIRST .. x_route_mtl_req_tbl.LAST LOOP
321 IF (x_route_mtl_req_tbl(i).INVENTORY_ITEM_ID IS NULL AND
325 FETCH get_item_group_item_csr INTO x_route_mtl_req_tbl(i).INVENTORY_ITEM_ID,
322 x_route_mtl_req_tbl(i).ITEM_GROUP_ID IS NOT NULL) THEN
323 -- Get the highest prority item from the item group
324 OPEN get_item_group_item_csr(c_item_group_id => x_route_mtl_req_tbl(i).ITEM_GROUP_ID);
326 x_route_mtl_req_tbl(i).INV_MASTER_ORG_ID;
327 CLOSE get_item_group_item_csr;
328 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
329 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'For merged requirement ' || i ||
330 ', Got Item Id as ' || x_route_mtl_req_tbl(i).INVENTORY_ITEM_ID ||
331 ' for item group id ' || x_route_mtl_req_tbl(i).ITEM_GROUP_ID);
332 END IF;
333 END IF;
334 END LOOP;
335 END IF;
336
337 -- Standard call to get message count and if count is 1, get message info
338 FND_MSG_PUB.Count_And_Get
339 ( p_count => x_msg_count,
340 p_data => x_msg_data,
341 p_encoded => fnd_api.g_false
342 );
343
344 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
345 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
346 END IF;
347
348 EXCEPTION
349 WHEN FND_API.G_EXC_ERROR THEN
350 x_return_status := FND_API.G_RET_STS_ERROR;
351 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
352 p_data => x_msg_data,
353 p_encoded => fnd_api.g_false);
354
355 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
356 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
357 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
358 p_data => x_msg_data,
359 p_encoded => fnd_api.g_false);
360
361 WHEN OTHERS THEN
362 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
363 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
364 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
365 p_procedure_name => 'Get_Route_Mtl_Req',
366 p_error_text => SUBSTR(SQLERRM,1,240));
367 END IF;
368 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
369 p_data => x_msg_data,
370 p_encoded => fnd_api.g_false);
371
372 END Get_Route_Mtl_Req;
373
374 --------------------------------------
375 -- End Public Procedure Definitions --
376 --------------------------------------
377
378 -----------------------------------------
379 -- Public Function Definitions follow --
380 -----------------------------------------
381 -- Start of Comments --
382 -- Function name : Get_Primary_UOM_Qty
383 -- Type : Private
384 -- Function : Private helper function to convert a quantity of an item from one
385 -- UOM to the Primary UOM. The inputs are the item id, the quantity
386 -- and the source UOM. The output is the quantity in the primary uom.
387 -- Pre-reqs :
388 -- Parameters :
389 --
390 --
391 -- Get_Primary_UOM_Qty Parameters:
392 -- p_inventory_item_id IN NUMBER Required
393 -- The Id of Inventory item. If this is null, this function returns null.
394 -- p_source_uom_code IN VARCHAR2 Required
395 -- The code of the UOM in which the quantity is currently mentioned.
396 -- If this is null, this function returns null.
397 -- p_quantity IN NUMBER Required
398 -- The quantity of the item in the indicated UOM.
399 -- If this is null, this function returns null.
400 --
401 -- Version :
402 -- Initial Version 1.0
403 --
404 -- End of Comments.
405
406 FUNCTION Get_Primary_UOM_Qty(p_inventory_item_id IN NUMBER,
407 p_source_uom_code IN VARCHAR2,
408 p_quantity IN NUMBER) RETURN NUMBER IS
409
410 CURSOR get_primary_uom_csr IS
411 select primary_uom_code from mtl_system_items
412 where inventory_item_id = p_inventory_item_id;
413
414 l_primary_uom VARCHAR2(10);
415 l_converted_qty NUMBER;
416 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Get_Primary_UOM_Qty';
417
418 BEGIN
419 IF (p_inventory_item_id IS NULL OR p_source_uom_code IS NULL OR p_quantity IS NULL) THEN
420 RETURN NULL;
421 END IF;
422 OPEN get_primary_uom_csr;
423 FETCH get_primary_uom_csr INTO l_primary_uom;
424 IF (get_primary_uom_csr%NOTFOUND) THEN
425 CLOSE get_primary_uom_csr;
426 RETURN null;
427 END IF;
428 CLOSE get_primary_uom_csr;
429 IF(p_source_uom_code = l_primary_uom) THEN
430 RETURN p_quantity;
431 END IF;
432
433 l_converted_qty := inv_convert.inv_um_convert(item_id => p_inventory_item_id,
434 precision => 2,
435 from_quantity => p_quantity,
436 from_unit => p_source_uom_code,
437 to_unit => l_primary_uom,
438 from_name => null,
439 to_name => null);
440 IF (l_converted_qty < 0) THEN
441 l_converted_qty := null;
442 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
446 END IF;
443 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Could not convert from ' || p_source_uom_code ||
444 ' to ' || l_primary_uom || '. Returning null quantity.');
445 END IF;
447 RETURN l_converted_qty;
448 END Get_Primary_UOM_Qty;
449
450 -- Start of Comments --
451 -- Function name : Get_Primary_UOM
452 -- Type : Private
453 -- Function : Private helper function to get the Primary UOM of an item
454 -- The inputs are the item id and the inventory org id.
455 -- Pre-reqs :
456 -- Parameters :
457 --
458 --
459 -- Get_Primary_UOM Parameters:
460 -- p_inventory_item_id IN NUMBER Required
461 -- The Id of Inventory item. If this is null, this function returns null.
462 -- p_inventory_org_id IN NUMBER Required
463 -- The inventory org id of the item. If this is null, this function returns null.
464 --
465 -- Version :
466 -- Initial Version 1.0
467 --
468 -- End of Comments.
469
470 FUNCTION Get_Primary_UOM
471 (
472 p_inventory_item_id IN NUMBER,
473 p_inventory_org_id IN NUMBER
474 ) RETURN VARCHAR2 IS
475 CURSOR get_primary_uom_csr IS
476 select primary_uom_code from mtl_system_items
477 where inventory_item_id = p_inventory_item_id and
478 organization_id = p_inventory_org_id;
479
480 l_primary_uom VARCHAR2(10);
481 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Get_Primary_UOM';
482
483 BEGIN
484 IF (p_inventory_item_id IS NULL OR p_inventory_org_id IS NULL) THEN
485 RETURN NULL;
486 END IF;
487 OPEN get_primary_uom_csr;
488 FETCH get_primary_uom_csr INTO l_primary_uom;
489 IF (get_primary_uom_csr%NOTFOUND) THEN
490 CLOSE get_primary_uom_csr;
491 RETURN null;
492 END IF;
493 CLOSE get_primary_uom_csr;
494 RETURN l_primary_uom;
495 END Get_Primary_UOM;
496
497 -------------------------------------
498 -- End Public Function Definitions --
499 -------------------------------------
500
501 ----------------------------------------
502 -- Local Procedure Definitions follow --
503 ----------------------------------------
504 ----------------------------------------------------------------------
505 -- This Procedure validates the input for the Get_Route_Mtl_Req API --
506 ----------------------------------------------------------------------
507 PROCEDURE Validate_Mtl_Req_Input
508 (
509 p_route_id IN NUMBER,
510 p_mr_route_id IN NUMBER,
511 p_item_instance_id IN NUMBER,
512 p_requirement_date IN DATE,
513 p_request_type IN VARCHAR2,
514 x_route_id OUT NOCOPY NUMBER) IS
515
516 CURSOR get_route_id_csr IS
517 select route_id from ahl_mr_routes
518 where mr_route_id = p_mr_route_id;
519
520 CURSOR validate_route_id_csr(c_route_id IN NUMBER) IS
521 -- Changes by skpathak on 06-NOV-2008 for bug-7336824
522 -- select 'x' from AHL_ROUTES_APP_V
523 select 'x' from AHL_ROUTES_B
524 where route_id = c_route_id;
525
526 CURSOR validate_instance_id_csr IS
527 select 'x' from CSI_ITEM_INSTANCES
528 where instance_id = p_item_instance_id
529 and nvl(active_end_date, sysdate + 1) > sysdate;
530
531 l_dummy VARCHAR2(1);
532 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Validate_Mtl_Req_Input';
533
534 BEGIN
535
536 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
537 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
538 END IF;
539
540 -- Get the Route Id
541 IF (p_route_id IS NULL AND p_mr_route_id IS NULL) THEN
542 FND_MESSAGE.Set_Name('AHL', 'AHL_LTP_APS_ROUTE_ID_NULL');
543 FND_MSG_PUB.ADD;
544 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
545 FND_LOG.MESSAGE(G_LEVEL_ERROR, L_DEBUG_KEY, FALSE);
546 END IF;
547 ELSIF (p_route_id IS NULL) THEN
548 OPEN get_route_id_csr;
549 FETCH get_route_id_csr INTO x_route_id;
550 CLOSE get_route_id_csr;
551 ELSE
552 x_route_id := p_route_id;
553 END IF;
554
555 -- Validate the Route Id
556 OPEN validate_route_id_csr(c_route_id => x_route_id);
557 FETCH validate_route_id_csr INTO l_dummy;
558 IF (validate_route_id_csr%NOTFOUND) THEN
559 FND_MESSAGE.Set_Name('AHL', 'AHL_LTP_APS_ROUTE_ID_INVALID');
560 FND_MESSAGE.Set_Token('ROUTE_ID', x_route_id);
561 FND_MSG_PUB.ADD;
562 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
563 FND_LOG.MESSAGE(G_LEVEL_ERROR, L_DEBUG_KEY, FALSE);
564 END IF;
565 END IF;
566 CLOSE validate_route_id_csr;
567
568 -- Validate the Instance Id
569 IF (p_item_instance_id IS NULL) THEN
570 FND_MESSAGE.Set_Name('AHL', 'AHL_LTP_APS_INST_ID_NULL');
571 FND_MSG_PUB.ADD;
572 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
573 FND_LOG.MESSAGE(G_LEVEL_ERROR, L_DEBUG_KEY, FALSE);
574 END IF;
575 ELSE
576 OPEN validate_instance_id_csr;
577 FETCH validate_instance_id_csr INTO l_dummy;
578 IF (validate_instance_id_csr%NOTFOUND) THEN
579 FND_MESSAGE.Set_Name('AHL', 'AHL_LTP_APS_INST_ID_INVALID');
580 FND_MESSAGE.Set_Token('INST_ID', p_item_instance_id);
581 FND_MSG_PUB.ADD;
585 END IF;
582 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
583 FND_LOG.MESSAGE(G_LEVEL_ERROR, L_DEBUG_KEY, FALSE);
584 END IF;
586 CLOSE validate_instance_id_csr;
587 END IF;
588
589 -- Validate the Request Type
590 IF (p_request_type IS NULL) THEN
591 FND_MESSAGE.Set_Name('AHL', 'AHL_LTP_APS_REQ_TYPE_NULL');
592 FND_MSG_PUB.ADD;
593 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
594 FND_LOG.MESSAGE(G_LEVEL_ERROR, L_DEBUG_KEY, FALSE);
595 END IF;
596 ELSIF (p_request_type NOT IN (G_REQ_TYPE_FORECAST, G_REQ_TYPE_PLANNED)) THEN
597 FND_MESSAGE.Set_Name('AHL', 'AHL_LTP_APS_REQ_TYPE_INVALID');
598 FND_MESSAGE.Set_Token('REQ_TYPE', p_request_type);
599 FND_MSG_PUB.ADD;
600 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
601 FND_LOG.MESSAGE(G_LEVEL_ERROR, L_DEBUG_KEY, FALSE);
602 END IF;
603 END IF;
604
605 -- Validate the Requirement Date
606 IF (p_requirement_date IS NULL) THEN
607 FND_MESSAGE.Set_Name('AHL', 'AHL_LTP_APS_REQ_DATE_NULL');
608 FND_MSG_PUB.ADD;
609 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
610 FND_LOG.MESSAGE(G_LEVEL_ERROR, L_DEBUG_KEY, FALSE);
611 END IF;
612 ELSIF (TRUNC(p_requirement_date) < TRUNC(SYSDATE)) THEN
613 FND_MESSAGE.Set_Name('AHL', 'AHL_LTP_APS_REQ_DATE_PAST');
614 FND_MESSAGE.Set_Token('REQ_DATE', p_requirement_date);
615 FND_MSG_PUB.ADD;
616 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
617 FND_LOG.MESSAGE(G_LEVEL_ERROR, L_DEBUG_KEY, FALSE);
618 END IF;
619 END IF;
620
621 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
622 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
623 END IF;
624
625 END Validate_Mtl_Req_Input;
626
627 --------------------------------------------------------------------
628 -- This Procedure gets the requirements from the Disposition List --
629 --------------------------------------------------------------------
630 PROCEDURE Get_Disp_List_Requirements
631 (
632 p_route_id IN NUMBER,
633 p_item_instance_id IN NUMBER,
634 p_requirement_date IN DATE,
635 p_request_type IN VARCHAR2,
636 p_unit_instance_id IN NUMBER,
637 p_inst_item_id IN NUMBER,
638 p_mc_header_id IN NUMBER,
639 p_mc_id IN NUMBER,
640 p_mc_version IN NUMBER,
641 x_disp_req_list OUT NOCOPY Route_Mtl_Req_Tbl_Type) IS
642
643 CURSOR get_mc_route_eff_id_csr IS
644 SELECT RE.route_effectivity_id
645 FROM AHL_ROUTE_EFFECTIVITIES RE
646 WHERE RE.route_id = p_route_id
647 AND (RE.mc_header_id = NVL(p_mc_header_id, -1) -- Match MC Header Id first
648 OR (RE.mc_id = NVL(p_mc_id, -1) -- Match MC Id next
649 AND RE.mc_header_id IS NULL -- Added on 10/28/03 since Version specific also has stores the MC Id
650 AND NOT EXISTS (SELECT 'x' FROM AHL_ROUTE_EFFECTIVITIES RE1
651 WHERE RE1.route_id = p_route_id
652 AND RE1.mc_header_id = NVL(p_mc_header_id, -1))
653 )
654 );
655
656 CURSOR get_item_route_eff_id_csr IS
657 SELECT RE.route_effectivity_id
658 FROM AHL_ROUTE_EFFECTIVITIES RE
659 WHERE RE.route_id = p_route_id
660 AND RE.inventory_item_id = p_inst_item_id; -- Match the inventory item id
661
662 CURSOR get_disp_req_dtls_csr(c_mc_route_eff_id IN NUMBER,
663 c_item_route_eff_id IN NUMBER) IS
664 SELECT ROM.RT_OPER_MATERIAL_ID,
665 ROM.INVENTORY_ITEM_ID,
666 ROM.INVENTORY_ORG_ID,
667 ROM.UOM_CODE,
668 ROM.QUANTITY,
669 AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY) AS PRIMARY_QUANTITY,
670 AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS PRIMARY_UOM_CODE,
671 ITEM_GROUP_ID,
672 ITEM_COMP_DETAIL_ID,
673 POSITION_PATH_ID,
674 PP.PATH_POS_COMMON_ID,
675 PP.VER_SPEC_SCORE
676 FROM AHL_RT_OPER_MATERIALS ROM, AHL_MC_PATH_POSITIONS PP
677 WHERE OBJECT_ID in (NVL(c_mc_route_eff_id, -1), NVL(c_item_route_eff_id, -1)) AND
678 ROM.POSITION_PATH_ID = PP.PATH_POSITION_ID (+) AND
679 ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_DISPOSITION AND
680 ((REPLACE_PERCENT = 100 AND p_request_type = G_REQ_TYPE_FORECAST) OR
681 (p_request_type = G_REQ_TYPE_PLANNED)
682 )
683 ORDER BY PATH_POS_COMMON_ID, VER_SPEC_SCORE DESC;
684
685 CURSOR get_item_comp_dtls_csr(c_item_comp_detail_id IN NUMBER,
686 c_quantity IN NUMBER,
687 c_uom IN VARCHAR2) IS
688 SELECT ICD.inventory_item_id, ICD.inventory_master_org_id, ICD.item_group_id,
689 DECODE(ICD.inventory_item_id, null, c_quantity, AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(ICD.inventory_item_id, c_uom, c_quantity)) QUANTITY,
690 DECODE(ICD.inventory_item_id, null, c_uom, AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(ICD.inventory_item_id, inventory_master_org_id)) UOM
691 FROM AHL_ITEM_COMP_DETAILS ICD
692 WHERE ITEM_COMP_DETAIL_ID = c_item_comp_detail_id;
693
694 l_mc_route_effectivity_id NUMBER;
695 l_item_route_effectivity_id NUMBER;
696 l_index NUMBER;
697 l_last_common_id NUMBER := -1;
698 l_prior_count NUMBER;
702
699 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Get_Disp_List_Requirements';
700
701 BEGIN
703 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
704 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
705 END IF;
706
707 -- Get the Route Effectivity
708 OPEN get_mc_route_eff_id_csr;
709 FETCH get_mc_route_eff_id_csr INTO l_mc_route_effectivity_id;
710 IF (get_mc_route_eff_id_csr%NOTFOUND) THEN
711 l_mc_route_effectivity_id := -1;
712 END IF;
713 CLOSE get_mc_route_eff_id_csr;
714
715 OPEN get_item_route_eff_id_csr;
716 FETCH get_item_route_eff_id_csr INTO l_item_route_effectivity_id;
717 IF (get_item_route_eff_id_csr%NOTFOUND) THEN
718 l_item_route_effectivity_id := -1;
719 END IF;
720 CLOSE get_item_route_eff_id_csr;
721
722 IF(l_item_route_effectivity_id = -1 AND l_mc_route_effectivity_id = -1) THEN
723 -- No Disposition List available
724 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
725 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'No disposition list found for MC Header Id: ' || p_mc_header_id
726 || ', MC Id: ' || p_mc_id
727 || ', Item Id: ' || p_inst_item_id);
728 END IF;
729 RETURN;
730 END IF;
731
732 l_index := 0;
733 FOR l_disp_req_rec IN get_disp_req_dtls_csr(c_mc_route_eff_id => l_mc_route_effectivity_id,
734 c_item_route_eff_id => l_item_route_effectivity_id) LOOP
735 l_index := l_index + 1;
736 IF ((l_disp_req_rec.INVENTORY_ITEM_ID IS NOT NULL) AND (l_disp_req_rec.ITEM_COMP_DETAIL_ID IS NULL)) THEN
737 -- Simple Item requirement with no reference to any Item composition
738 x_disp_req_list(l_index).RT_OPER_MATERIAL_ID := l_disp_req_rec.RT_OPER_MATERIAL_ID;
739 x_disp_req_list(l_index).INVENTORY_ITEM_ID := l_disp_req_rec.INVENTORY_ITEM_ID;
740 x_disp_req_list(l_index).INV_MASTER_ORG_ID := l_disp_req_rec.INVENTORY_ORG_ID;
741 -- Quantity in Item's Primary UOM
742 x_disp_req_list(l_index).QUANTITY := l_disp_req_rec.PRIMARY_QUANTITY;
743 x_disp_req_list(l_index).UOM_CODE := l_disp_req_rec.PRIMARY_UOM_CODE;
744 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
745 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Requirement ' || l_index || ': RT_OPER_MATERIAL_ID = ' || l_disp_req_rec.RT_OPER_MATERIAL_ID ||
746 ', INVENTORY_ITEM_ID = ' || l_disp_req_rec.INVENTORY_ITEM_ID ||
747 ', INV_MASTER_ORG_ID = ' || l_disp_req_rec.INVENTORY_ORG_ID ||
748 ', QUANTITY = ' || l_disp_req_rec.PRIMARY_QUANTITY ||
749 ', UOM_CODE = ' || l_disp_req_rec.PRIMARY_UOM_CODE);
750 END IF;
751 ELSIF ((l_disp_req_rec.ITEM_GROUP_ID IS NOT NULL) AND (l_disp_req_rec.ITEM_COMP_DETAIL_ID IS NULL)) THEN
752 -- Simple Item Group requirement with no reference to any Item composition
753 x_disp_req_list(l_index).RT_OPER_MATERIAL_ID := l_disp_req_rec.RT_OPER_MATERIAL_ID;
754 x_disp_req_list(l_index).ITEM_GROUP_ID := l_disp_req_rec.ITEM_GROUP_ID;
755 x_disp_req_list(l_index).QUANTITY := l_disp_req_rec.QUANTITY;
756 x_disp_req_list(l_index).UOM_CODE := l_disp_req_rec.UOM_CODE;
757 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
758 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Requirement ' || l_index || ': RT_OPER_MATERIAL_ID = ' || l_disp_req_rec.RT_OPER_MATERIAL_ID ||
759 ', ITEM_GROUP_ID = ' || l_disp_req_rec.ITEM_GROUP_ID ||
760 ', QUANTITY = ' || l_disp_req_rec.QUANTITY ||
761 ', UOM_CODE = ' || l_disp_req_rec.UOM_CODE);
762 END IF;
763 ELSIF (l_disp_req_rec.ITEM_COMP_DETAIL_ID IS NOT NULL) THEN
764 -- Item Composition requirement
765 x_disp_req_list(l_index).RT_OPER_MATERIAL_ID := l_disp_req_rec.RT_OPER_MATERIAL_ID;
766 x_disp_req_list(l_index).ITEM_COMP_DETAIL_ID := l_disp_req_rec.ITEM_COMP_DETAIL_ID;
767 -- Get the details from the Item Composition Details table
768 OPEN get_item_comp_dtls_csr(c_item_comp_detail_id => l_disp_req_rec.ITEM_COMP_DETAIL_ID,
769 c_quantity => l_disp_req_rec.QUANTITY,
770 c_uom => l_disp_req_rec.UOM_CODE);
771 FETCH get_item_comp_dtls_csr INTO x_disp_req_list(l_index).INVENTORY_ITEM_ID,
772 x_disp_req_list(l_index).INV_MASTER_ORG_ID,
773 x_disp_req_list(l_index).ITEM_GROUP_ID,
774 x_disp_req_list(l_index).QUANTITY,
775 x_disp_req_list(l_index).UOM_CODE;
776 CLOSE get_item_comp_dtls_csr;
777 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
778 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Requirement ' || l_index || ': RT_OPER_MATERIAL_ID = ' || l_disp_req_rec.RT_OPER_MATERIAL_ID ||
779 ', ITEM_COMP_DETAIL_ID = ' || l_disp_req_rec.ITEM_COMP_DETAIL_ID ||
780 ', INVENTORY_ITEM_ID = ' || x_disp_req_list(l_index).INVENTORY_ITEM_ID ||
781 ', INV_MASTER_ORG_ID = ' || x_disp_req_list(l_index).INV_MASTER_ORG_ID ||
785 END IF;
782 ', ITEM_GROUP_ID = ' || x_disp_req_list(l_index).ITEM_GROUP_ID ||
783 ', QUANTITY = ' || x_disp_req_list(l_index).QUANTITY ||
784 ', UOM_CODE = ' || x_disp_req_list(l_index).UOM_CODE);
786 ELSE
787 -- Requirement with only Position path reference
788 IF (p_mc_header_id IS NULL) THEN
789 -- Ignore this Position Path specific requirement since the current instance
790 -- is not a UC unit (is not of any MC type)
791 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
792 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Ignoring Disp. List Requirement with Position Path Id ' || l_disp_req_rec.POSITION_PATH_ID);
793 END IF;
794 ELSE
795 -- Get the Position Path requirement if applicable
796 IF (l_disp_req_rec.PATH_POS_COMMON_ID <> l_last_common_id) THEN
797 -- Consider this Position Path since it is has a new common id
798 l_prior_count := x_disp_req_list.COUNT;
799 Get_Pos_Path_Requirement(p_position_path_id => l_disp_req_rec.POSITION_PATH_ID,
800 p_requirement_date => p_requirement_date,
801 p_unit_instance_id => p_unit_instance_id,
802 p_mc_header_id => p_mc_header_id,
803 p_mc_id => p_mc_id,
804 p_mc_version => p_mc_version,
805 p_rt_oper_mtl_id => l_disp_req_rec.RT_OPER_MATERIAL_ID,
806 p_quantity => l_disp_req_rec.QUANTITY,
807 p_uom => l_disp_req_rec.UOM_CODE,
808 p_x_disp_req_list => x_disp_req_list);
809 IF (x_disp_req_list.COUNT > l_prior_count) THEN
810 -- The last Position Path requirement was valid and added.
811 -- Set the l_last_common_id so that subsequent records
812 -- with the same common id (but lower score) can be ignored.
813 l_last_common_id := l_disp_req_rec.PATH_POS_COMMON_ID;
814 ELSE
815 -- Not applicable: Hence not added: Continue to the next lower score.
816 null;
817 END IF; -- if count has increased
818 ELSE
819 -- Common id is same as last added, but lower score: ignore this record
820 null;
821 END IF; -- Common Id is different
822 END IF;
823 l_index := x_disp_req_list.COUNT; -- Set output table index to always point to the last added record.
824 END IF;
825 END LOOP;
826 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
827 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Total number of disposition requirements: ' || x_disp_req_list.COUNT);
828 END IF;
829
830 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
831 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
832 END IF;
833
834 END Get_Disp_List_Requirements;
835
836 -------------------------------------------------------
837 -- This Procedure gets the Position Path Requirement --
838 -------------------------------------------------------
839 PROCEDURE Get_Pos_Path_Requirement
840 (
841 p_position_path_id IN NUMBER,
842 p_requirement_date IN DATE,
843 p_unit_instance_id IN NUMBER,
844 p_mc_header_id IN NUMBER,
845 p_mc_id IN NUMBER,
846 p_mc_version IN NUMBER,
847 p_rt_oper_mtl_id IN NUMBER,
848 p_quantity IN NUMBER,
849 p_uom IN VARCHAR2,
850 p_x_disp_req_list IN OUT NOCOPY Route_Mtl_Req_Tbl_Type) IS
851
852 CURSOR get_sub_unit_mc_csr(c_pos_instance_id IN NUMBER) IS
853 SELECT master_config_id
854 FROM AHL_UNIT_CONFIG_HEADERS
855 WHERE CSI_ITEM_INSTANCE_ID = c_pos_instance_id;
856
857 CURSOR get_item_grp_for_mc_csr(c_mc_header_id IN NUMBER) IS
858 SELECT item_group_id
859 FROM AHL_MC_RELATIONSHIPS
860 WHERE mc_header_id = c_mc_header_id
861 AND parent_relationship_id is null;
862
863 CURSOR get_sub_mc_for_pos_csr(c_position_id IN NUMBER) IS
864 SELECT CR.mc_header_id
865 FROM AHL_MC_CONFIG_RELATIONS CR, AHL_MC_HEADERS_B MC
866 WHERE CR.relationship_id = c_position_id
867 AND MC.mc_header_id = CR.mc_header_id
868 ORDER BY MC.name;
869
870 l_position_id NUMBER;
871 l_pos_item_group_id NUMBER;
872 l_pos_instance_id NUMBER;
873 l_sub_unit_mc NUMBER;
874 l_next_index NUMBER := p_x_disp_req_list.COUNT + 1;
875 l_valid_flag VARCHAR2(1);
876 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Get_Pos_Path_Requirement';
877
878 BEGIN
879
880 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
881 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
882 END IF;
883
884 -- First validate the Position Path and get the Position Details
885 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
886 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Calling Validate_Path_Position with ' ||
887 'p_position_path_id = ' || p_position_path_id ||
891 Validate_Path_Position(p_path_position_id => p_position_path_id,
888 ', p_unit_instance_id = ' || p_unit_instance_id ||
889 ', p_requirement_date = ' || p_requirement_date);
890 END IF;
892 p_unit_instance_id => p_unit_instance_id,
893 p_requirement_date => p_requirement_date,
894 x_valid_flag => l_valid_flag,
895 x_relationship_id => l_position_id,
896 x_item_group_id => l_pos_item_group_id,
897 x_pos_instance_id => l_pos_instance_id);
898
899 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
900 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Validate_Path_Position returned ' ||
901 'x_valid_flag = ' || l_valid_flag ||
902 ', x_relationship_id = ' || l_position_id ||
903 ', x_item_group_id = ' || l_pos_item_group_id ||
904 ', x_pos_instance_id = ' || l_pos_instance_id);
905 END IF;
906
907 IF (l_valid_flag = FND_API.G_FALSE) THEN
908 -- Don't include the requirement if the position path is not valid
909 RETURN;
910 END IF;
911
912 IF (l_pos_instance_id IS NULL) THEN
913 -- The position is empty
914 -- If the position has an item group associated, copy that as the requirement
915 IF (l_pos_item_group_id IS NOT NULL) THEN
916 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
917 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Position ' || l_position_id || ' is empty and has item group ' ||
918 l_pos_item_group_id || ' associated with it.');
919 END IF;
920 p_x_disp_req_list(l_next_index).ITEM_GROUP_ID := l_pos_item_group_id;
921 ELSE
922 -- No Item group for the empty position: Pick one Sub config for the position
923 OPEN get_sub_mc_for_pos_csr(c_position_id => l_position_id);
924 FETCH get_sub_mc_for_pos_csr INTO l_sub_unit_mc;
925 CLOSE get_sub_mc_for_pos_csr;
926 -- Get the Item group for the root node of the chosen Sub MC
927 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
928 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Chosen Sub MC Header Id ' || l_sub_unit_mc ||
929 ' for empty position with id ' || l_position_id);
930 END IF;
931 OPEN get_item_grp_for_mc_csr(c_mc_header_id => l_sub_unit_mc);
932 FETCH get_item_grp_for_mc_csr INTO p_x_disp_req_list(l_next_index).ITEM_GROUP_ID;
933 CLOSE get_item_grp_for_mc_csr;
934 END IF;
935 ELSE
936 -- The position is not empty
937 -- Check if the position corresponds to the root node
938 IF (l_pos_instance_id <> p_unit_instance_id) THEN
939 -- Not root node: Check if a sub-unit is installed in the position
940 OPEN get_sub_unit_mc_csr(c_pos_instance_id => l_pos_instance_id);
941 FETCH get_sub_unit_mc_csr INTO l_sub_unit_mc;
942 IF(get_sub_unit_mc_csr%FOUND) THEN
943 -- A sub unit (UC) is installed in the position
944 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
945 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Sub unit with Sub MC Header Id ' || l_sub_unit_mc ||
946 ' installed at position with id ' || l_position_id);
947 END IF;
948 -- Get the Item group for the root node of the Sub unit's MC
949 OPEN get_item_grp_for_mc_csr(c_mc_header_id => l_sub_unit_mc);
950 FETCH get_item_grp_for_mc_csr INTO p_x_disp_req_list(l_next_index).ITEM_GROUP_ID;
951 CLOSE get_item_grp_for_mc_csr;
952 ELSE
953 -- An instance (not a sub unit) is installed in the position
954 -- Add this position's item group as requirement to the list
955 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
956 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Simple instance with id ' || l_pos_instance_id ||
957 ' installed at position with id ' || l_position_id);
958 END IF;
959 p_x_disp_req_list(l_next_index).ITEM_GROUP_ID := l_pos_item_group_id;
960 END IF; -- Sub unit or instance
961 CLOSE get_sub_unit_mc_csr;
962 ELSE
963 -- Root Node
964 -- Add the item group requirement to the list
965 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
966 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Position Id ' || l_position_id || ' corresponds to the root node.');
967 END IF;
968 p_x_disp_req_list(l_next_index).ITEM_GROUP_ID := l_pos_item_group_id;
969 END IF; -- Root node or not
970 END IF; -- Position is empty or not
971
972 -- Copy the remaining attributes
973 p_x_disp_req_list(l_next_index).RT_OPER_MATERIAL_ID := p_rt_oper_mtl_id;
974 p_x_disp_req_list(l_next_index).POSITION_PATH_ID := p_position_path_id;
975 p_x_disp_req_list(l_next_index).RELATIONSHIP_ID := l_position_id;
976 p_x_disp_req_list(l_next_index).QUANTITY := p_quantity;
977 p_x_disp_req_list(l_next_index).UOM_CODE := p_uom;
978 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
979 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Requirement ' || l_next_index || ': RT_OPER_MATERIAL_ID = ' || p_rt_oper_mtl_id ||
983 ', QUANTITY = ' || p_quantity ||
980 ', ITEM_GROUP_ID = ' || p_x_disp_req_list(l_next_index).ITEM_GROUP_ID ||
981 ', POSITION_PATH_ID = ' || p_position_path_id ||
982 ', RELATIONSHIP_ID = ' || l_position_id ||
984 ', UOM_CODE = ' || p_uom);
985 END IF;
986
987 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
988 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
989 END IF;
990
991 END Get_Pos_Path_Requirement;
992
993 ------------------------------------------------------------------------
994 -- This Procedure gets the requirements from the Route (or Operation) --
995 ------------------------------------------------------------------------
996 PROCEDURE Get_Route_Requirements
997 (
998 p_route_id IN NUMBER,
999 p_request_type IN VARCHAR2,
1000 x_route_req_list OUT NOCOPY Route_Mtl_Req_Tbl_Type) IS
1001
1002 CURSOR get_route_level_reqs_csr IS
1003 SELECT ROM.RT_OPER_MATERIAL_ID,
1004 NULL AS ROUTE_OPERATION_ID,
1005 ROM.INVENTORY_ITEM_ID,
1006 ROM.INVENTORY_ORG_ID,
1007 ROM.QUANTITY,
1008 ROM.UOM_CODE,
1009 AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY) AS PRIMARY_QUANTITY,
1010 AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS PRIMARY_UOM_CODE,
1011 ROM.ITEM_GROUP_ID
1012 FROM AHL_RT_OPER_MATERIALS ROM
1013 WHERE OBJECT_ID = p_route_id
1014 AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_ROUTE;
1015
1016 -- AnRaj : Start for fixing perf bug 4919527
1017 /*
1018 CURSOR get_op_level_reqs_csr IS
1019 --SELECT NULL AS RT_OPER_MATERIAL_ID,
1020 --NULL AS ROUTE_OPERATION_ID,
1021 -- support for Oracle 8
1022 SELECT TO_NUMBER(NULL) AS RT_OPER_MATERIAL_ID,
1023 TO_NUMBER(NULL) AS ROUTE_OPERATION_ID,
1024 INVENTORY_ITEM_ID,
1025 INVENTORY_ORG_ID,
1026 -- Aggregate item quantities across operations when forecasting
1027 SUM(AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY)) AS QUANTITY, --Total Primary Qty
1028 AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS UOM_CODE,
1029 -- support for Oracle 8
1030 --NULL AS ITEM_GROUP_ID
1031 TO_NUMBER(NULL) AS ITEM_GROUP_ID
1032 FROM AHL_RT_OPER_MATERIALS ROM
1033 WHERE OBJECT_ID in (SELECT RO.operation_id
1034 FROM ahl_operations_vl O, ahl_route_operations RO
1035 WHERE O.operation_id = RO.operation_id and
1036 RO.route_id = p_route_id and
1037 O.revision_status_code = 'COMPLETE' and
1038 O.revision_number in (SELECT max(revision_number)
1039 FROM ahl_operations_b_kfv
1040 WHERE concatenated_segments =
1041 O.concatenated_segments and
1042 trunc(sysdate) between
1043 trunc(start_date_active) and
1044 trunc(NVL(end_date_active,SYSDATE+1)))
1045 )
1046 AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_OPERATION
1047 AND INVENTORY_ITEM_ID IS NOT NULL
1048 AND p_request_type = G_REQ_TYPE_FORECAST
1049 GROUP BY INVENTORY_ITEM_ID, INVENTORY_ORG_ID
1050 UNION
1051 -- Don't aggregate for Operation items when Firm Planning
1052 SELECT RT_OPER_MATERIAL_ID,
1053 OBJECT_ID AS ROUTE_OPERATION_ID,
1054 INVENTORY_ITEM_ID,
1055 INVENTORY_ORG_ID,
1056 AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY) AS QUANTITY, -- Primary Qty
1057 AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS UOM_CODE,
1058 --NULL AS ITEM_GROUP_ID
1059 -- support for Oracle 8
1060 TO_NUMBER(NULL) AS ITEM_GROUP_ID
1061 FROM AHL_RT_OPER_MATERIALS ROM
1062 WHERE OBJECT_ID in (SELECT RO.operation_id
1063 FROM ahl_operations_vl O, ahl_route_operations RO
1064 WHERE O.operation_id = RO.operation_id and
1065 RO.route_id = p_route_id and
1066 O.revision_status_code = 'COMPLETE' and
1067 O.revision_number in (SELECT max(revision_number)
1068 FROM ahl_operations_b_kfv
1069 WHERE concatenated_segments =
1070 O.concatenated_segments and
1071 trunc(sysdate) between
1072 trunc(start_date_active) and
1073 trunc(NVL(end_date_active,SYSDATE+1)))
1074 )
1075 AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_OPERATION
1076 AND INVENTORY_ITEM_ID IS NOT NULL
1077 AND p_request_type = G_REQ_TYPE_PLANNED
1078 UNION
1079 -- Item Group: No need to aggregate or convert to Primary UOM
1080 SELECT RT_OPER_MATERIAL_ID,
1081 OBJECT_ID AS ROUTE_OPERATION_ID,
1082 --NULL AS INVENTORY_ITEM_ID,
1086 TO_NUMBER(NULL) AS INVENTORY_ORG_ID,
1083 --NULL AS INVENTORY_ORG_ID,
1084 -- support for Oracle 8
1085 TO_NUMBER(NULL) AS INVENTORY_ITEM_ID,
1087 QUANTITY,
1088 UOM_CODE,
1089 ITEM_GROUP_ID
1090 FROM AHL_RT_OPER_MATERIALS ROM
1091 WHERE OBJECT_ID in (SELECT RO.operation_id
1092 FROM ahl_operations_vl O, ahl_route_operations RO
1093 WHERE O.operation_id = RO.operation_id and
1094 RO.route_id = p_route_id and
1095 O.revision_status_code = 'COMPLETE' and
1096 O.revision_number in (SELECT max(revision_number)
1097 FROM ahl_operations_b_kfv
1098 WHERE concatenated_segments =
1099 O.concatenated_segments and
1100 trunc(sysdate) between
1101 trunc(start_date_active) and
1102 trunc(NVL(end_date_active,SYSDATE+1)))
1103 )
1104 AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_OPERATION
1105 AND INVENTORY_ITEM_ID IS NULL;*/
1106
1107 CURSOR get_op_level_reqs_forecast_csr IS
1108 SELECT TO_NUMBER(NULL) AS RT_OPER_MATERIAL_ID,
1109 TO_NUMBER(NULL) AS ROUTE_OPERATION_ID,
1110 INVENTORY_ITEM_ID,
1111 INVENTORY_ORG_ID,
1112 -- Aggregate item quantities across operations when forecasting
1113 SUM(AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY)) AS QUANTITY, /*Total Primary Qty */
1114 AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS UOM_CODE,
1115 TO_NUMBER(NULL) AS ITEM_GROUP_ID
1116 FROM AHL_RT_OPER_MATERIALS ROM
1117 WHERE OBJECT_ID in ( SELECT RO.operation_id
1118 FROM AHL_OPERATIONS_B_KFV O, ahl_route_operations RO
1119 WHERE O.operation_id = RO.operation_id and
1120 RO.route_id = p_route_id and
1121 O.revision_status_code = 'COMPLETE' and
1122 O.revision_number in ( SELECT max(revision_number)
1123 FROM ahl_operations_b_kfv
1124 WHERE concatenated_segments = O.concatenated_segments
1125 and trunc(sysdate) between
1126 trunc(start_date_active) and
1127 trunc(NVL(end_date_active,SYSDATE+1)))
1128 )
1129 AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_OPERATION
1130 AND INVENTORY_ITEM_ID IS NOT NULL
1131 GROUP BY INVENTORY_ITEM_ID, INVENTORY_ORG_ID
1132 UNION
1133 SELECT RT_OPER_MATERIAL_ID,
1134 OBJECT_ID AS ROUTE_OPERATION_ID,
1135 TO_NUMBER(NULL) AS INVENTORY_ITEM_ID,
1136 TO_NUMBER(NULL) AS INVENTORY_ORG_ID,
1137 QUANTITY,
1138 UOM_CODE,
1139 ITEM_GROUP_ID
1140 FROM AHL_RT_OPER_MATERIALS ROM
1141 WHERE OBJECT_ID in ( SELECT RO.operation_id
1142 FROM AHL_OPERATIONS_B_KFV O, ahl_route_operations RO
1143 WHERE O.operation_id = RO.operation_id and
1144 RO.route_id = p_route_id and
1145 O.revision_status_code = 'COMPLETE' and
1146 O.revision_number in (SELECT max(revision_number)
1147 FROM ahl_operations_b_kfv
1148 WHERE concatenated_segments = O.concatenated_segments
1149 and
1150 trunc(sysdate) between
1151 trunc(start_date_active) and
1152 trunc(NVL(end_date_active,SYSDATE+1)))
1153 )
1154 AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_OPERATION
1155 AND INVENTORY_ITEM_ID IS NULL;
1156
1157 CURSOR get_op_level_reqs_planned_csr IS
1158 SELECT RT_OPER_MATERIAL_ID,
1159 OBJECT_ID AS ROUTE_OPERATION_ID,
1160 INVENTORY_ITEM_ID,
1161 INVENTORY_ORG_ID,
1162 AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY) AS QUANTITY, /* Primary Qty */
1163 AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS UOM_CODE,
1164 TO_NUMBER(NULL) AS ITEM_GROUP_ID
1165 FROM AHL_RT_OPER_MATERIALS ROM
1166 WHERE OBJECT_ID in ( SELECT RO.operation_id
1167 FROM AHL_OPERATIONS_B_KFV O, ahl_route_operations RO
1168 WHERE O.operation_id = RO.operation_id
1169 and RO.route_id = p_route_id
1170 and O.revision_status_code = 'COMPLETE'
1171 and O.revision_number in ( SELECT max(revision_number)
1172 FROM ahl_operations_b_kfv
1173 WHERE concatenated_segments =O.concatenated_segments
1174 and
1178 )
1175 trunc(sysdate) between
1176 trunc(start_date_active) and
1177 trunc(NVL(end_date_active,SYSDATE+1)))
1179 AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_OPERATION
1180 AND INVENTORY_ITEM_ID IS NOT NULL
1181 UNION
1182 -- Item Group: No need to aggregate or convert to Primary UOM
1183 SELECT RT_OPER_MATERIAL_ID,
1184 OBJECT_ID AS ROUTE_OPERATION_ID,
1185 TO_NUMBER(NULL) AS INVENTORY_ITEM_ID,
1186 TO_NUMBER(NULL) AS INVENTORY_ORG_ID,
1187 QUANTITY,
1188 UOM_CODE,
1189 ITEM_GROUP_ID
1190 FROM AHL_RT_OPER_MATERIALS ROM
1191 WHERE OBJECT_ID in (SELECT RO.operation_id
1192 FROM AHL_OPERATIONS_B_KFV O, ahl_route_operations RO
1193 WHERE O.operation_id = RO.operation_id and
1194 RO.route_id = p_route_id and
1195 O.revision_status_code = 'COMPLETE' and
1196 O.revision_number in (SELECT max(revision_number)
1197 FROM ahl_operations_b_kfv
1198 WHERE concatenated_segments = O.concatenated_segments
1199 and
1200 trunc(sysdate) between
1201 trunc(start_date_active) and
1202 trunc(NVL(end_date_active,SYSDATE+1)))
1203 )
1204 AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_OPERATION
1205 AND INVENTORY_ITEM_ID IS NULL;
1206
1207 CURSOR l_op_requirement_neither_csr IS
1208 SELECT RT_OPER_MATERIAL_ID,
1209 OBJECT_ID AS ROUTE_OPERATION_ID,
1210 TO_NUMBER(NULL) AS INVENTORY_ITEM_ID,
1211 TO_NUMBER(NULL) AS INVENTORY_ORG_ID,
1212 QUANTITY,
1213 UOM_CODE,
1214 ITEM_GROUP_ID
1215 FROM AHL_RT_OPER_MATERIALS ROM
1216 WHERE OBJECT_ID in (SELECT RO.operation_id
1217 FROM AHL_OPERATIONS_B_KFV O, ahl_route_operations RO
1218 WHERE O.operation_id = RO.operation_id and
1219 RO.route_id = p_route_id and
1220 O.revision_status_code = 'COMPLETE' and
1221 O.revision_number in (SELECT max(revision_number)
1222 FROM ahl_operations_b_kfv
1223 WHERE concatenated_segments = O.concatenated_segments
1224 and
1225 trunc(sysdate) between
1226 trunc(start_date_active) and
1227 trunc(NVL(end_date_active,SYSDATE+1)))
1228 )
1229 AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_OPERATION
1230 AND INVENTORY_ITEM_ID IS NULL;
1231 -- AnRaj : End for fixing perf bug 4919527
1232 l_rt_requirement_rec get_route_level_reqs_csr%ROWTYPE;
1233 l_index NUMBER := 0;
1234 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Get_Route_Requirements';
1235
1236 BEGIN
1237
1238 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1239 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1240 END IF;
1241
1242 -- Get the requirements defined at the route level (if any)
1243 OPEN get_route_level_reqs_csr;
1244 FETCH get_route_level_reqs_csr INTO l_rt_requirement_rec;
1245 IF(get_route_level_reqs_csr%FOUND) THEN
1246 -- Requirements defined at the Route level
1247 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1248 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Requirements for Route with Id ' || p_route_id || ' are defined at the Route level itself.');
1249 END IF;
1250 LOOP
1251 EXIT WHEN get_route_level_reqs_csr%NOTFOUND;
1252 -- Process this requirement
1253 l_index := l_index + 1;
1254 x_route_req_list(l_index).RT_OPER_MATERIAL_ID := l_rt_requirement_rec.RT_OPER_MATERIAL_ID;
1255 IF(l_rt_requirement_rec.INVENTORY_ITEM_ID IS NULL) THEN
1256 -- Item Group Requirement
1257 x_route_req_list(l_index).ITEM_GROUP_ID := l_rt_requirement_rec.ITEM_GROUP_ID;
1258 x_route_req_list(l_index).QUANTITY := l_rt_requirement_rec.QUANTITY;
1259 x_route_req_list(l_index).UOM_CODE := l_rt_requirement_rec.UOM_CODE;
1260 ELSE
1261 -- Specific Item Requirement
1262 x_route_req_list(l_index).INVENTORY_ITEM_ID := l_rt_requirement_rec.INVENTORY_ITEM_ID;
1263 x_route_req_list(l_index).INV_MASTER_ORG_ID := l_rt_requirement_rec.INVENTORY_ORG_ID;
1264 x_route_req_list(l_index).QUANTITY := l_rt_requirement_rec.PRIMARY_QUANTITY;
1265 x_route_req_list(l_index).UOM_CODE := l_rt_requirement_rec.PRIMARY_UOM_CODE;
1266 END IF;
1267 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1268 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Requirement ' || l_index || ': RT_OPER_MATERIAL_ID = ' || l_rt_requirement_rec.RT_OPER_MATERIAL_ID ||
1272 ', QUANTITY = ' || x_route_req_list(l_index).QUANTITY ||
1269 ', ITEM_GROUP_ID = ' || x_route_req_list(l_index).ITEM_GROUP_ID ||
1270 ', INVENTORY_ITEM_ID = ' || x_route_req_list(l_index).INVENTORY_ITEM_ID ||
1271 ', INV_MASTER_ORG_ID = ' || x_route_req_list(l_index).INV_MASTER_ORG_ID ||
1273 ', UOM_CODE = ' || x_route_req_list(l_index).UOM_CODE);
1274 END IF;
1275 -- Get the next requirement
1276 FETCH get_route_level_reqs_csr INTO l_rt_requirement_rec;
1277 END LOOP;
1278 CLOSE get_route_level_reqs_csr;
1279 ELSE
1280 -- No requirement defined at route level
1281 CLOSE get_route_level_reqs_csr;
1282 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1283 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Requirements for Route with Id ' || p_route_id ||
1284 ' are NOT defined at the Route level. Checking at the operation level.');
1285 END IF;
1286 -- AnRaj : Start for fixing perf bug 4919527
1287 -- AnRaj : Changed the code for fixing the performance bug# 4919527
1288 -- Split the query into 3 to avoid the cursor get_op_level_reqs_csr
1289 IF p_request_type = G_REQ_TYPE_FORECAST THEN
1290 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1291 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'p_request type is G_REQ_TYPE_FORECAST');
1292 END IF;
1293
1294 FOR l_op_requirement_rec IN get_op_level_reqs_forecast_csr LOOP
1295 l_index := l_index + 1;
1296 x_route_req_list(l_index).RT_OPER_MATERIAL_ID := l_op_requirement_rec.RT_OPER_MATERIAL_ID;
1297 x_route_req_list(l_index).ROUTE_OPERATION_ID := l_op_requirement_rec.ROUTE_OPERATION_ID;
1298 x_route_req_list(l_index).ITEM_GROUP_ID := l_op_requirement_rec.ITEM_GROUP_ID;
1299 x_route_req_list(l_index).INVENTORY_ITEM_ID := l_op_requirement_rec.INVENTORY_ITEM_ID;
1300 x_route_req_list(l_index).INV_MASTER_ORG_ID := l_op_requirement_rec.INVENTORY_ORG_ID;
1301 x_route_req_list(l_index).QUANTITY := l_op_requirement_rec.QUANTITY;
1302 x_route_req_list(l_index).UOM_CODE := l_op_requirement_rec.UOM_CODE;
1303 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1304 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Requirement ' || l_index || ': RT_OPER_MATERIAL_ID = ' || x_route_req_list(l_index).RT_OPER_MATERIAL_ID ||
1305 ', ROUTE_OPERATION_ID = ' || x_route_req_list(l_index).ROUTE_OPERATION_ID ||
1306 ', ITEM_GROUP_ID = ' || x_route_req_list(l_index).ITEM_GROUP_ID ||
1307 ', INVENTORY_ITEM_ID = ' || x_route_req_list(l_index).INVENTORY_ITEM_ID ||
1308 ', INV_MASTER_ORG_ID = ' || x_route_req_list(l_index).INV_MASTER_ORG_ID ||
1309 ', QUANTITY = ' || x_route_req_list(l_index).QUANTITY ||
1310 ', UOM_CODE = ' || x_route_req_list(l_index).UOM_CODE);
1311 END IF;
1312 END LOOP;
1313 ELSIF p_request_type = G_REQ_TYPE_PLANNED THEN
1314 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1315 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'p_request type is G_REQ_TYPE_PLANNED');
1316 END IF;
1317
1318 FOR l_op_requirement_rec IN get_op_level_reqs_planned_csr LOOP
1319 l_index := l_index + 1;
1320 x_route_req_list(l_index).RT_OPER_MATERIAL_ID := l_op_requirement_rec.RT_OPER_MATERIAL_ID;
1321 x_route_req_list(l_index).ROUTE_OPERATION_ID := l_op_requirement_rec.ROUTE_OPERATION_ID;
1322 x_route_req_list(l_index).ITEM_GROUP_ID := l_op_requirement_rec.ITEM_GROUP_ID;
1323 x_route_req_list(l_index).INVENTORY_ITEM_ID := l_op_requirement_rec.INVENTORY_ITEM_ID;
1324 x_route_req_list(l_index).INV_MASTER_ORG_ID := l_op_requirement_rec.INVENTORY_ORG_ID;
1325 x_route_req_list(l_index).QUANTITY := l_op_requirement_rec.QUANTITY;
1326 x_route_req_list(l_index).UOM_CODE := l_op_requirement_rec.UOM_CODE;
1327 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1328 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Requirement ' || l_index || ': RT_OPER_MATERIAL_ID = ' || x_route_req_list(l_index).RT_OPER_MATERIAL_ID ||
1329 ', ROUTE_OPERATION_ID = ' || x_route_req_list(l_index).ROUTE_OPERATION_ID ||
1330 ', ITEM_GROUP_ID = ' || x_route_req_list(l_index).ITEM_GROUP_ID ||
1331 ', INVENTORY_ITEM_ID = ' || x_route_req_list(l_index).INVENTORY_ITEM_ID ||
1332 ', INV_MASTER_ORG_ID = ' || x_route_req_list(l_index).INV_MASTER_ORG_ID ||
1333 ', QUANTITY = ' || x_route_req_list(l_index).QUANTITY ||
1334 ', UOM_CODE = ' || x_route_req_list(l_index).UOM_CODE);
1335 END IF;
1336
1337 END LOOP;
1341 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1338 -- Not too sure whether the value of p_request_type can be anything other than G_REQ_TYPE_FORECAST and G_REQ_TYPE_PLANNED
1339 -- Adding this part for safety purpose, so that no records are missed out
1340 ELSE
1342 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'p_request type is neither G_REQ_TYPE_PLANNED nor G_REQ_TYPE_FORECAST');
1343 END IF;
1344
1345 FOR l_op_requirement_rec IN l_op_requirement_neither_csr LOOP
1346 l_index := l_index + 1;
1347 x_route_req_list(l_index).RT_OPER_MATERIAL_ID := l_op_requirement_rec.RT_OPER_MATERIAL_ID;
1348 x_route_req_list(l_index).ROUTE_OPERATION_ID := l_op_requirement_rec.ROUTE_OPERATION_ID;
1349 x_route_req_list(l_index).ITEM_GROUP_ID := l_op_requirement_rec.ITEM_GROUP_ID;
1350 x_route_req_list(l_index).INVENTORY_ITEM_ID := l_op_requirement_rec.INVENTORY_ITEM_ID;
1351 x_route_req_list(l_index).INV_MASTER_ORG_ID := l_op_requirement_rec.INVENTORY_ORG_ID;
1352 x_route_req_list(l_index).QUANTITY := l_op_requirement_rec.QUANTITY;
1353 x_route_req_list(l_index).UOM_CODE := l_op_requirement_rec.UOM_CODE;
1354 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1355 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Requirement ' || l_index || ': RT_OPER_MATERIAL_ID = ' || x_route_req_list(l_index).RT_OPER_MATERIAL_ID ||
1356 ', ROUTE_OPERATION_ID = ' || x_route_req_list(l_index).ROUTE_OPERATION_ID ||
1357 ', ITEM_GROUP_ID = ' || x_route_req_list(l_index).ITEM_GROUP_ID ||
1358 ', INVENTORY_ITEM_ID = ' || x_route_req_list(l_index).INVENTORY_ITEM_ID ||
1359 ', INV_MASTER_ORG_ID = ' || x_route_req_list(l_index).INV_MASTER_ORG_ID ||
1360 ', QUANTITY = ' || x_route_req_list(l_index).QUANTITY ||
1361 ', UOM_CODE = ' || x_route_req_list(l_index).UOM_CODE);
1362 END IF;
1363
1364 END LOOP;
1365 END IF; -- request_type check
1366
1367 -- Check operation level
1368 /* FOR l_op_requirement_rec IN get_op_level_reqs_csr LOOP
1369 l_index := l_index + 1;
1370 x_route_req_list(l_index).RT_OPER_MATERIAL_ID := l_op_requirement_rec.RT_OPER_MATERIAL_ID;
1371 x_route_req_list(l_index).ROUTE_OPERATION_ID := l_op_requirement_rec.ROUTE_OPERATION_ID;
1372 x_route_req_list(l_index).ITEM_GROUP_ID := l_op_requirement_rec.ITEM_GROUP_ID;
1373 x_route_req_list(l_index).INVENTORY_ITEM_ID := l_op_requirement_rec.INVENTORY_ITEM_ID;
1374 x_route_req_list(l_index).INV_MASTER_ORG_ID := l_op_requirement_rec.INVENTORY_ORG_ID;
1375 x_route_req_list(l_index).QUANTITY := l_op_requirement_rec.QUANTITY;
1376 x_route_req_list(l_index).UOM_CODE := l_op_requirement_rec.UOM_CODE;
1377 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1378 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Requirement ' || l_index || ': RT_OPER_MATERIAL_ID = ' || x_route_req_list(l_index).RT_OPER_MATERIAL_ID ||
1379 ', ROUTE_OPERATION_ID = ' || x_route_req_list(l_index).ROUTE_OPERATION_ID ||
1380 ', ITEM_GROUP_ID = ' || x_route_req_list(l_index).ITEM_GROUP_ID ||
1381 ', INVENTORY_ITEM_ID = ' || x_route_req_list(l_index).INVENTORY_ITEM_ID ||
1382 ', INV_MASTER_ORG_ID = ' || x_route_req_list(l_index).INV_MASTER_ORG_ID ||
1383 ', QUANTITY = ' || x_route_req_list(l_index).QUANTITY ||
1384 ', UOM_CODE = ' || x_route_req_list(l_index).UOM_CODE);
1385 END IF;
1386 END LOOP;
1387 */
1388 -- AnRaj : End for fixing perf bug 4919527
1389 END IF;
1390 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1391 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Total number of Route requirements: ' || l_index);
1392 END IF;
1393 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1394 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1395 END IF;
1396
1397 END Get_Route_Requirements;
1398
1399 ------------------------------------------------------------------------
1400 -- This Procedure validates a Position Path against a given unit and date.
1401 -- It returns a status flag and if valid, it get the details about the position.
1402 ------------------------------------------------------------------------
1403 PROCEDURE Validate_Path_Position
1404 (
1405 p_path_position_id IN NUMBER,
1406 p_unit_instance_id IN NUMBER,
1407 p_requirement_date IN DATE,
1408 x_valid_flag OUT NOCOPY VARCHAR2,
1409 x_relationship_id OUT NOCOPY NUMBER,
1410 x_item_group_id OUT NOCOPY NUMBER,
1411 x_pos_instance_id OUT NOCOPY NUMBER) IS
1412
1413 CURSOR get_pos_path_dtls_csr IS
1414 SELECT position_key
1415 FROM AHL_MC_PATH_POSITION_NODES
1416 WHERE PATH_POSITION_ID = p_path_position_id AND
1417 SEQUENCE = (SELECT MAX(SEQUENCE) FROM AHL_MC_PATH_POSITION_NODES
1418 WHERE PATH_POSITION_ID = p_path_position_id);
1419
1420 CURSOR get_position_dtls_csr(c_pos_key IN NUMBER,
1421 c_mc_header_id IN NUMBER) IS
1425 MC_HEADER_ID = c_mc_header_id;
1422 SELECT relationship_id, item_group_id
1423 FROM AHL_MC_RELATIONSHIPS
1424 WHERE POSITION_KEY = c_pos_key AND
1426
1427 CURSOR get_config_dtls_csr(c_unit_instance_id IN NUMBER) IS
1428 SELECT master_config_id
1429 FROM ahl_unit_config_headers
1430 WHERE CSI_ITEM_INSTANCE_ID = c_unit_instance_id AND
1431 NVL(ACTIVE_START_DATE, SYSDATE - 1) <= SYSDATE AND
1432 NVL(ACTIVE_END_DATE, SYSDATE + 1) > SYSDATE;
1433
1434 CURSOR get_all_position_dates_csr(c_start_pos_key IN NUMBER,
1435 c_mc_header_id IN NUMBER) IS
1436 SELECT ACTIVE_START_DATE,
1437 ACTIVE_END_DATE
1438 FROM AHL_MC_RELATIONSHIPS
1439 START WITH POSITION_KEY = c_start_pos_key AND
1440 MC_HEADER_ID = c_mc_header_id
1441 CONNECT BY RELATIONSHIP_ID = PRIOR PARENT_RELATIONSHIP_ID;
1442
1443 CURSOR get_ii_position_csr(c_start_instance_id IN NUMBER) IS
1444 SELECT II.OBJECT_ID,
1445 II.SUBJECT_ID,
1446 REL.RELATIONSHIP_ID,
1447 REL.ACTIVE_START_DATE,
1448 REL.ACTIVE_END_DATE
1449 FROM CSI_II_RELATIONSHIPS II, AHL_MC_RELATIONSHIPS REL
1450 WHERE NVL(II.ACTIVE_START_DATE, SYSDATE - 1) <= SYSDATE AND
1451 NVL(II.ACTIVE_END_DATE, SYSDATE + 1) > SYSDATE AND
1452 II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF' AND
1453 REL.RELATIONSHIP_ID =TO_NUMBER(II.POSITION_REFERENCE)
1454 AND II.RELATIONSHIP_ID IN
1455 (SELECT RELATIONSHIP_ID
1456 FROM CSI_II_RELATIONSHIPS
1457 START WITH SUBJECT_ID = c_start_instance_id AND
1458 NVL(ACTIVE_START_DATE, SYSDATE - 1) <= SYSDATE AND
1459 NVL(ACTIVE_END_DATE, SYSDATE + 1) > SYSDATE AND
1460 RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
1461 CONNECT BY SUBJECT_ID = PRIOR OBJECT_ID AND
1462 NVL(ACTIVE_START_DATE, SYSDATE - 1) <= SYSDATE AND
1463 NVL(ACTIVE_END_DATE, SYSDATE + 1) > SYSDATE AND
1464 RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF');
1465
1466 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Validate_Path_Position';
1467 l_return_status VARCHAR2(1);
1468 l_msg_count NUMBER;
1469 l_msg_data VARCHAR2(1000);
1470 l_installed_inst_id NUMBER;
1471 l_lowest_unit_inst_id NUMBER;
1472 l_lowest_mc_header_id NUMBER;
1473 l_mapping_status VARCHAR2(30);
1474 l_position_key AHL_MC_PATH_POSITION_NODES.POSITION_KEY%TYPE;
1475 l_last_instance_id NUMBER;
1476 l_requirement_date DATE := p_requirement_date;
1477
1478 BEGIN
1479
1480 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1481 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1482 END IF;
1483
1484 IF (G_LEVEL_EVENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1485 FND_LOG.STRING(G_LEVEL_EVENT, L_DEBUG_KEY, 'About to call AHL_MC_PATH_POSITION_PVT.Get_Pos_Instance with ' ||
1486 ' p_position_id = ' || p_path_position_id ||
1487 ' p_csi_item_instance_id = ' || p_unit_instance_id);
1488 END IF;
1489
1490 AHL_MC_PATH_POSITION_PVT.Get_Pos_Instance(p_api_version => 1.0,
1491 p_init_msg_list => FND_API.G_FALSE,
1492 p_commit => FND_API.G_FALSE,
1493 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1494 x_return_status => l_return_status,
1495 x_msg_count => l_msg_count,
1496 x_msg_data => l_msg_data,
1497 p_position_id => p_path_position_id,
1498 p_csi_item_instance_id => p_unit_instance_id,
1499 x_item_instance_id => l_installed_inst_id,
1500 x_lowest_uc_csi_id => l_lowest_unit_inst_id,
1501 x_mapping_status => l_mapping_status);
1502
1503 IF (G_LEVEL_EVENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1504 FND_LOG.STRING(G_LEVEL_EVENT, L_DEBUG_KEY, 'Returned from call to AHL_MC_PATH_POSITION_PVT.Get_Pos_Instance:' ||
1505 ' x_return_status = ' || l_return_status ||
1506 ', x_mapping_status = ' || l_mapping_status ||
1507 ', x_item_instance_id = ' || l_installed_inst_id ||
1508 ', x_lowest_uc_csi_id = ' || l_lowest_unit_inst_id);
1509 END IF;
1510
1511 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1512 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1513 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1514 RAISE FND_API.G_EXC_ERROR;
1515 END IF;
1516
1517 IF(l_mapping_status = G_MAPPING_STATUS_NA) THEN
1518 -- Position does not apply to current unit
1519 x_valid_flag := FND_API.G_FALSE;
1520 RETURN;
1521 END IF;
1522
1523 OPEN get_pos_path_dtls_csr;
1524 FETCH get_pos_path_dtls_csr INTO l_position_key;
1525 CLOSE get_pos_path_dtls_csr;
1526
1527 OPEN get_config_dtls_csr(c_unit_instance_id => l_lowest_unit_inst_id);
1528 FETCH get_config_dtls_csr INTO l_lowest_mc_header_id;
1532 l_requirement_date := SYSDATE;
1529 CLOSE get_config_dtls_csr;
1530
1531 IF l_requirement_date IS NULL THEN
1533 END IF;
1534
1535 -- Check if the Position is valid on the requirement date
1536 -- by traversing the tree from the position up to the unit's root node.
1537 IF (l_mapping_status = G_MAPPING_STATUS_EMPTY) THEN
1538 -- If the position is empty, do a separate traversal from
1539 -- the position up to l_lowest_unit_inst_id first.
1540 FOR position_dates_rec IN get_all_position_dates_csr(l_position_key, l_lowest_mc_header_id) LOOP
1541 IF ((position_dates_rec.ACTIVE_START_DATE IS NOT NULL AND
1542 position_dates_rec.ACTIVE_START_DATE > l_requirement_date) OR
1543 (position_dates_rec.ACTIVE_END_DATE IS NOT NULL AND
1544 position_dates_rec.ACTIVE_END_DATE <= l_requirement_date)) THEN
1545 -- Position is not valid on the requirement date
1546 x_valid_flag := FND_API.G_FALSE;
1547 RETURN;
1548 END IF;
1549 END LOOP;
1550 l_last_instance_id := l_lowest_unit_inst_id;
1551 ELSE
1552 -- Position is not empty
1553 l_last_instance_id := l_installed_inst_id;
1554 END IF;
1555
1556 IF (l_last_instance_id <> p_unit_instance_id) THEN
1557 -- Now traverse up the instance tree to validate positions.
1558 FOR position_rec IN get_ii_position_csr(l_last_instance_id) LOOP
1559 -- Check if the position is valid in the last mc
1560 IF ((position_rec.ACTIVE_START_DATE IS NOT NULL AND
1561 position_rec.ACTIVE_START_DATE > l_requirement_date) OR
1562 (position_rec.ACTIVE_END_DATE IS NOT NULL AND
1563 position_rec.ACTIVE_END_DATE <= l_requirement_date)) THEN
1564 -- Position is not valid on the requirement date
1565 x_valid_flag := FND_API.G_FALSE;
1566 RETURN;
1567 END IF;
1568 END LOOP;
1569 END IF;
1570
1571 -- The Position hierarchy is valid on the requirement Date
1572 x_valid_flag := FND_API.G_TRUE;
1573
1574 -- Information available: l_position_key, l_lowest_mc_header_id, l_mapping_status, l_installed_inst_id
1575 -- Get the relationship id and the item group id for the position
1576 OPEN get_position_dtls_csr(c_pos_key => l_position_key,
1577 c_mc_header_id => l_lowest_mc_header_id);
1578 FETCH get_position_dtls_csr INTO x_relationship_id, x_item_group_id;
1579 CLOSE get_position_dtls_csr;
1580 IF (l_mapping_status = G_MAPPING_STATUS_MATCH) THEN
1581 x_pos_instance_id := l_installed_inst_id;
1582 ELSE
1583 x_pos_instance_id := NULL;
1584 END IF;
1585
1586 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1587 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1588 END IF;
1589
1590 END Validate_Path_Position;
1591
1592
1593 -------------------------------------
1594 -- End Local Procedure Definitions --
1595 -------------------------------------
1596 ---------------------------------------
1597 -- Local Function Definitions follow --
1598 ---------------------------------------
1599 -----------------------------------------------
1600 -- This Function gets the unit's instance id --
1601 -- If p_item_instance_id does not belong to an UC (stand-alone or IB Tree), it
1602 -- returns null. If p_item_instance_id itself is a UC's top node, it returns itself.
1603 -----------------------------------------------
1604
1605 FUNCTION Get_Unit_Instance(
1606 p_item_instance_id IN NUMBER) RETURN NUMBER IS
1607
1608 CURSOR chk_inst_is_unit_csr IS
1609 SELECT 'x'
1610 FROM ahl_unit_config_headers
1611 WHERE csi_item_instance_id = p_item_instance_id
1612 AND nvl(active_end_date, SYSDATE+1) > SYSDATE
1613 AND nvl(active_start_date, SYSDATE) <= SYSDATE;
1614
1615 CURSOR get_parent_instance_csr IS
1616 SELECT object_id
1617 FROM csi_ii_relationships
1618 WHERE object_id IN (SELECT csi_item_instance_id
1619 FROM ahl_unit_config_headers
1620 WHERE nvl(active_end_date, SYSDATE+1) > SYSDATE
1621 AND nvl(active_start_date, SYSDATE) <= SYSDATE)
1622 START WITH subject_id = p_item_instance_id
1623 AND relationship_type_code = 'COMPONENT-OF'
1624 AND nvl(active_start_date, SYSDATE) <= SYSDATE
1625 AND nvl(active_end_date, SYSDATE+1) > SYSDATE
1626 CONNECT BY subject_id = PRIOR object_id
1627 AND relationship_type_code = 'COMPONENT-OF'
1628 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
1629 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
1630 ORDER BY LEVEL;
1631
1632 l_instance_id NUMBER := null;
1633 l_dummy VARCHAR2(1);
1634
1635 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Get_Unit_Instance';
1636
1637 BEGIN
1638 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1639 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Function');
1640 END IF;
1641
1642 IF(p_item_instance_id IS NULL) THEN
1643 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1644 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'p_item_instance_id is null. So, returning null.');
1645 END IF;
1646 l_instance_id := null;
1647 ELSE
1648 OPEN chk_inst_is_unit_csr;
1649 FETCH chk_inst_is_unit_csr INTO l_dummy;
1650 IF (chk_inst_is_unit_csr%FOUND) THEN
1651 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1652 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'p_item_instance_id (' || p_item_instance_id || ') itself is a unit.');
1653 END IF;
1654 l_instance_id := p_item_instance_id;
1655 ELSE
1656 OPEN get_parent_instance_csr;
1657 FETCH get_parent_instance_csr INTO l_instance_id;
1658 CLOSE get_parent_instance_csr;
1659 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1660 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Unit Instance of ' || p_item_instance_id || ' is ' || l_instance_id);
1661 END IF;
1662 END IF;
1663 CLOSE chk_inst_is_unit_csr;
1664 END IF;
1665
1666 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1667 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Function');
1668 END IF;
1669 RETURN l_instance_id;
1670
1671 END Get_Unit_Instance;
1672
1673
1674 ------------------------------------
1675 -- End Local Function Definitions --
1676 ------------------------------------
1677
1678 END AHL_LTP_MTL_REQ_PVT;
[Home] [Help]