1 PACKAGE BODY AHL_LTP_MTL_REQ_PVT AS
2 /* $Header: AHLVLMRB.pls 120.5.12020000.2 2012/12/10 13:48:28 shnatu 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 -- Modified by surrkuma for bug# 13998921 on 26-Apr-12
47 -- Added Visit ID as a parameter for validating visit task instance.
48 PROCEDURE Validate_Mtl_Req_Input(
49 p_route_id IN NUMBER,
50 p_mr_route_id IN NUMBER,
51 p_item_instance_id IN NUMBER,
52 p_requirement_date IN DATE,
53 p_request_type IN VARCHAR2,
54 p_visit_id IN NUMBER := null,
55 x_route_id OUT NOCOPY NUMBER);
56
57 -- This Procedure gets the requirements from the Disposition List
58 PROCEDURE Get_Disp_List_Requirements(
59 p_route_id IN NUMBER,
60 p_item_instance_id IN NUMBER,
61 p_requirement_date IN DATE,
62 p_request_type IN VARCHAR2,
63 p_unit_instance_id IN NUMBER,
64 p_inst_item_id IN NUMBER,
65 p_mc_header_id IN NUMBER,
66 p_mc_id IN NUMBER,
67 p_mc_version IN NUMBER,
68 x_disp_req_list OUT NOCOPY Route_Mtl_Req_Tbl_Type);
69
70 -- This Procedure gets the Position Path Requirement
71 PROCEDURE Get_Pos_Path_Requirement(
72 p_position_path_id IN NUMBER,
73 p_requirement_date IN DATE,
74 p_unit_instance_id IN NUMBER,
75 p_mc_header_id IN NUMBER,
76 p_mc_id IN NUMBER,
77 p_mc_version IN NUMBER,
78 p_rt_oper_mtl_id IN NUMBER,
79 p_quantity IN NUMBER,
80 p_uom IN VARCHAR2,
81 p_x_disp_req_list IN OUT NOCOPY Route_Mtl_Req_Tbl_Type);
82
83 -- Modified by surrkuma for Service Bulletin :: 07-Jun-2011
84 -- Added the parameters MC_ID, MC_HEADER_ID, UC_HEADER_ID and VISIT_ID
85 -- This Procedure gets the requirements from the Route (or Operation)
86 PROCEDURE Get_Route_Requirements(
87 p_route_id IN NUMBER,
88 p_request_type IN VARCHAR2,
89 p_mc_id IN NUMBER,
90 p_mc_header_id IN NUMBER,
91 p_uc_header_id IN NUMBER,
92 p_visit_id IN NUMBER,
93 x_route_req_list OUT NOCOPY Route_Mtl_Req_Tbl_Type);
94
95 -- This Procedure validates a Position Path against a given unit and date.
96 -- It returns a status flag and if valid, it get the details about the position.
97 PROCEDURE Validate_Path_Position(
98 p_path_position_id IN NUMBER,
99 p_unit_instance_id IN NUMBER,
100 p_requirement_date IN DATE,
101 x_valid_flag OUT NOCOPY VARCHAR2,
102 x_relationship_id OUT NOCOPY NUMBER,
103 x_item_group_id OUT NOCOPY NUMBER,
104 x_pos_instance_id OUT NOCOPY NUMBER);
105
106 -- This Function gets the unit's instance id
107 FUNCTION Get_Unit_Instance(
108 p_item_instance_id IN NUMBER) RETURN NUMBER;
109
110 -- SURRKUMA :: Service Bulletin :: 07-Jun-2011
111 -- Gets an highest priority item from item group if SB rule doesn't exists for the position,
112 -- else get the highest priority item which satisfies the SB rule condition.
113 PROCEDURE Get_Item_Group_Item(
114 p_item_group_id IN NUMBER,
115 p_rule_id IN NUMBER,
116 p_visit_org_id IN NUMBER,
117 x_inventory_item_id OUT NOCOPY NUMBER,
118 x_inventory_org_id OUT NOCOPY NUMBER,
119 x_quantity OUT NOCOPY NUMBER,
120 x_uom_code OUT NOCOPY VARCHAR2);
121
122
123 -------------------------------------
124 -- End Local Procedures Declaration--
125 -------------------------------------
126
127 -----------------------------------------
128 -- Public Procedure Definitions follow --
129 -----------------------------------------
130 -- Start of Comments --
131 -- Procedure name : Get_Route_Mtl_Req
132 -- Type : Private
133 -- Function : Private API to get the Material requirements for a Route.
134 -- For FORECAST request type, it aggregates requirements at the
135 -- route level (across operations), and gets the highest priority item
136 -- ignoring the inventory org. Also, a disposition list requirement is
137 -- considered for FORECAST only if the REPLACE_PERCENT = 100%.
138 -- For PLANNED, no aggregation is done, NO specific item is obtained
139 -- within an item group and the REPLACE_PERCENT is not considered.
140 -- Pre-reqs :
141 -- Parameters :
142 --
143 -- Standard IN Parameters :
144 -- p_api_version IN NUMBER Required
145 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
146 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
147 --
148 -- Standard OUT Parameters :
149 -- x_return_status OUT VARCHAR2 Required
150 -- x_msg_count OUT NUMBER Required
151 -- x_msg_data OUT VARCHAR2 Required
152 --
153 -- Get_Route_Mtl_Req Parameters:
154 -- p_route_id IN NUMBER Not Required only if p_mr_route_id is not null
155 -- The Id of Route for which to determine the material requirements
156 -- p_mr_route_id IN NUMBER Not Required only if p_route_id is not null
157 -- The Id of MR Route for which to determine the material requirements
158 -- p_item_instance_id IN NUMBER Required
159 -- The Id of Instance for which to plan the material requirements
160 -- p_visit_id IN NUMBER Not Requried
161 -- The Id of Visit for which to determine the material requirements
162 -- p_requirement_date IN DATE Not Required
163 -- The date when the materials are required. If provided, the positions of Master Configs
164 -- (for position path based disposition list requirement) are validated against this date.
165 -- p_request_type IN VARCHAR2 Required
166 -- Should be either 'FORECAST' or 'PLANNED'
167 -- x_route_mtl_req_tbl OUT AHL_LTP_MTL_REQ_PVT.Route_Mtl_Req_Tbl Required
168 -- The Table of records containing the material requirements for the route
169 --
170 -- Version :
171 -- Initial Version 1.0
172 --
173 -- End of Comments.
174
175 PROCEDURE Get_Route_Mtl_Req
176 (
177 p_api_version IN NUMBER,
178 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
179 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
180 x_return_status OUT NOCOPY VARCHAR2,
181 x_msg_count OUT NOCOPY NUMBER,
182 x_msg_data OUT NOCOPY VARCHAR2,
183 p_route_id IN NUMBER,
184 p_mr_route_id IN NUMBER,
185 p_item_instance_id IN NUMBER,
186 p_visit_id IN NUMBER := null, -- Added by surrkuma for Service Bulletin, 07-Jun-2011
187 p_requirement_date IN DATE := null,
188 p_request_type IN VARCHAR2,
189 x_route_mtl_req_tbl OUT NOCOPY AHL_LTP_MTL_REQ_PVT.Route_Mtl_Req_Tbl_Type) IS
190
191 -- SURRKUMA :: Service Bulletin :: 07-Jun-2011
192 -- Modified the cursor to fetch UC header id too, for getting the affected SB rule later
193 CURSOR get_mc_dtls_csr(c_instance_id IN NUMBER) IS
194 select UC.MASTER_CONFIG_ID, MC.MC_ID, MC.VERSION_NUMBER,UC.UNIT_CONFIG_HEADER_ID
195 from AHL_UNIT_CONFIG_HEADERS UC, AHL_MC_HEADERS_B MC
196 where UC.CSI_ITEM_INSTANCE_ID = c_instance_id AND
197 MC.MC_HEADER_ID = UC.MASTER_CONFIG_ID;
198
199 CURSOR get_item_from_instance_csr(c_instance_id IN NUMBER) IS
200 select INVENTORY_ITEM_ID from CSI_ITEM_INSTANCES
201 where INSTANCE_ID = c_instance_id;
202
203 CURSOR get_item_group_item_csr(c_item_group_id IN NUMBER) IS
204 SELECT inventory_item_id, inventory_org_id
205 FROM ahl_item_associations_b
206 WHERE item_group_id = c_item_group_id
207 AND interchange_type_code in ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
208 ORDER BY priority;
209
210 l_route_id NUMBER;
211 l_unit_instance_id NUMBER;
212 l_inst_item_id NUMBER;
213 l_mc_header_id NUMBER;
214 l_mc_id NUMBER;
215 l_mc_version NUMBER;
216 -- SURRKUMA :: Service Bulletin :: 07-Jun-2011
217 -- Added a local variable for UC header id
218 l_uc_header_id NUMBER;
219 l_disp_req_list AHL_LTP_MTL_REQ_PVT.Route_Mtl_Req_Tbl_Type;
220 l_route_req_list AHL_LTP_MTL_REQ_PVT.Route_Mtl_Req_Tbl_Type;
221 l_index NUMBER;
222 l_temp_item_id NUMBER;
223 l_temp_ig_id NUMBER;
224 l_found BOOLEAN;
225 l_api_version CONSTANT NUMBER := 1.0;
226 l_api_name CONSTANT VARCHAR2(30) := 'Get_Route_Mtl_Req';
227 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Get_Route_Mtl_Req';
228
229 BEGIN
230
231 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
232 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
233 END IF;
234 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
235 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Input values for the Procedure: ' ||
236 'p_route_id = ' || p_route_id ||
237 ', p_mr_route_id = ' || p_mr_route_id ||
238 ', p_item_instance_id = ' || p_item_instance_id ||
239 ', p_requirement_date = ' || p_requirement_date ||
240 ', p_visit_id = ' || p_visit_id ||
241 ', p_request_type = ' || p_request_type);
242 END IF;
243
244 -- Standard call to check for call compatibility
245 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
246 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
247 END IF;
248
249 -- Initialize message list if p_init_msg_list is set to TRUE
250 IF FND_API.To_Boolean(p_init_msg_list) THEN
251 FND_MSG_PUB.Initialize;
252 END IF;
253
254 -- Initialize API return status to success
255 x_return_status := FND_API.G_RET_STS_SUCCESS;
256
257 -- Begin Processing
258 -- First Validate the input parameters
259 Validate_Mtl_Req_Input(p_route_id => p_route_id,
260 p_mr_route_id => p_mr_route_id,
261 p_item_instance_id => p_item_instance_id,
262 p_requirement_date => p_requirement_date,
263 p_request_type => p_request_type,
264 p_visit_id => p_visit_id,
265 x_route_id => l_route_id);
266
267 IF (FND_MSG_PUB.Count_Msg > 0) THEN
268 -- There are validation errors: Raise error
269 RAISE FND_API.G_EXC_ERROR;
270 END IF;
271
272 -- Get the Unit Instance
273 l_unit_instance_id := Get_Unit_Instance(p_item_instance_id);
274 IF (l_unit_instance_id IS NOT NULL) THEN
275 -- Instance is in a UC: Get the MC Details
276 OPEN get_mc_dtls_csr(c_instance_id => l_unit_instance_id);
277 FETCH get_mc_dtls_csr INTO l_mc_header_id, l_mc_id, l_mc_version, l_uc_header_id;
278 CLOSE get_mc_dtls_csr;
279 END IF;
280
281 -- Get item of instance
282 OPEN get_item_from_instance_csr(c_instance_id => p_item_instance_id);
283 FETCH get_item_from_instance_csr INTO l_inst_item_id;
284 CLOSE get_item_from_instance_csr;
285
286 -- Get the Requirements from the Disposition List
287 Get_Disp_List_Requirements(p_route_id => l_route_id,
288 p_item_instance_id => p_item_instance_id,
289 p_requirement_date => p_requirement_date,
290 p_request_type => p_request_type,
291 p_unit_instance_id => l_unit_instance_id,
292 p_inst_item_id => l_inst_item_id,
293 p_mc_header_id => l_mc_header_id,
294 p_mc_id => l_mc_id,
295 p_mc_version => l_mc_version,
296 x_disp_req_list => l_disp_req_list);
297
298 -- Get the Requirements from the Route
299 -- SURRKUMA :: Service Bulletin :: 07-Jun-2011
300 -- Adding parameters mc_id, mc_header_id and uc_header_id to validate position based requirements
301 Get_Route_Requirements(p_route_id => l_route_id,
302 p_request_type => p_request_type,
303 p_mc_id => l_mc_id,
304 p_mc_header_id => l_mc_header_id,
305 p_uc_header_id => l_uc_header_id,
306 p_visit_id => p_visit_id,
307 x_route_req_list => l_route_req_list);
308
309 -- Merge the two list of requirements
310 IF (l_disp_req_list.COUNT = 0) THEN
311 x_route_mtl_req_tbl := l_route_req_list;
312 ELSIF (l_route_req_list.COUNT = 0) THEN
313 x_route_mtl_req_tbl := l_disp_req_list;
314 ELSE
315 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
316 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'l_disp_req_list.COUNT = ' || l_disp_req_list.COUNT);
317 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'l_route_req_list.COUNT = ' || l_route_req_list.COUNT);
318 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Both lists are non-empty. Merging...');
319 END IF;
320 x_route_mtl_req_tbl := l_disp_req_list;
321 l_index := x_route_mtl_req_tbl.COUNT;
322 FOR i in l_route_req_list.FIRST .. l_route_req_list.LAST LOOP
323 l_temp_item_id := NVL(l_route_req_list(i).INVENTORY_ITEM_ID, -1);
324 l_temp_ig_id := NVL(l_route_req_list(i).ITEM_GROUP_ID, -1);
325 l_found := FALSE;
326 FOR j in l_disp_req_list.FIRST .. l_disp_req_list.LAST LOOP
327 IF ((NVL(l_disp_req_list(j).INVENTORY_ITEM_ID, -2) = l_temp_item_id) OR
328 (NVL(l_disp_req_list(j).ITEM_GROUP_ID, -2) = l_temp_ig_id)) THEN
329 -- The route requirement exists in the Disposition list also
330 l_found := TRUE;
331 EXIT;
332 END IF;
333 END LOOP;
334 IF(l_found = FALSE) THEN
335 -- Add this Route requirement to the combined list
336 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
337 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Adding Route requirement with index ' || i ||
338 ' to the combined list.');
339 END IF;
340 l_index := l_index + 1;
341 x_route_mtl_req_tbl(l_index) := l_route_req_list(i);
342 ELSE
343 -- Duplicate: Ignore
344 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
345 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Route requirement with index ' || i || ' is a duplicate. Ignoring.');
346 END IF;
347 END IF;
348 END LOOP;
349 END IF;
350
351 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
352 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Size of the merged list: ' || x_route_mtl_req_tbl.COUNT);
353 END IF;
354
355 -- Get the first item for item groups if forecasting
356 IF(x_route_mtl_req_tbl.COUNT > 0 AND p_request_type = G_REQ_TYPE_FORECAST) THEN
357 FOR i in x_route_mtl_req_tbl.FIRST .. x_route_mtl_req_tbl.LAST LOOP
358 IF (x_route_mtl_req_tbl(i).INVENTORY_ITEM_ID IS NULL AND
359 x_route_mtl_req_tbl(i).ITEM_GROUP_ID IS NOT NULL) THEN
360 -- Get the highest prority item from the item group
361 OPEN get_item_group_item_csr(c_item_group_id => x_route_mtl_req_tbl(i).ITEM_GROUP_ID);
362 FETCH get_item_group_item_csr INTO x_route_mtl_req_tbl(i).INVENTORY_ITEM_ID,
363 x_route_mtl_req_tbl(i).INV_MASTER_ORG_ID;
364 CLOSE get_item_group_item_csr;
365 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
366 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'For merged requirement ' || i ||
367 ', Got Item Id as ' || x_route_mtl_req_tbl(i).INVENTORY_ITEM_ID ||
368 ' for item group id ' || x_route_mtl_req_tbl(i).ITEM_GROUP_ID);
369 END IF;
370 END IF;
371 END LOOP;
372 END IF;
373
374 -- Standard call to get message count and if count is 1, get message info
375 FND_MSG_PUB.Count_And_Get
376 ( p_count => x_msg_count,
377 p_data => x_msg_data,
378 p_encoded => fnd_api.g_false
379 );
380
381 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
382 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
383 END IF;
384
385 EXCEPTION
386 WHEN FND_API.G_EXC_ERROR THEN
387 x_return_status := FND_API.G_RET_STS_ERROR;
388 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
389 p_data => x_msg_data,
390 p_encoded => fnd_api.g_false);
391
392 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
393 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
394 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
395 p_data => x_msg_data,
396 p_encoded => fnd_api.g_false);
397
398 WHEN OTHERS THEN
399 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
400 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
401 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
402 p_procedure_name => 'Get_Route_Mtl_Req',
403 p_error_text => SUBSTR(SQLERRM,1,240));
404 END IF;
405 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
406 p_data => x_msg_data,
407 p_encoded => fnd_api.g_false);
408
409 END Get_Route_Mtl_Req;
410
411 --------------------------------------
412 -- End Public Procedure Definitions --
413 --------------------------------------
414
415 -----------------------------------------
416 -- Public Function Definitions follow --
417 -----------------------------------------
418 -- Start of Comments --
419 -- Function name : Get_Primary_UOM_Qty
420 -- Type : Private
421 -- Function : Private helper function to convert a quantity of an item from one
422 -- UOM to the Primary UOM. The inputs are the item id, the quantity
423 -- and the source UOM. The output is the quantity in the primary uom.
424 -- Pre-reqs :
425 -- Parameters :
426 --
427 --
428 -- Get_Primary_UOM_Qty Parameters:
429 -- p_inventory_item_id IN NUMBER Required
430 -- The Id of Inventory item. If this is null, this function returns null.
431 -- p_source_uom_code IN VARCHAR2 Required
432 -- The code of the UOM in which the quantity is currently mentioned.
433 -- If this is null, this function returns null.
434 -- p_quantity IN NUMBER Required
435 -- The quantity of the item in the indicated UOM.
436 -- If this is null, this function returns null.
437 --
438 -- Version :
439 -- Initial Version 1.0
440 --
441 -- End of Comments.
442
443 FUNCTION Get_Primary_UOM_Qty(p_inventory_item_id IN NUMBER,
444 p_source_uom_code IN VARCHAR2,
445 p_quantity IN NUMBER) RETURN NUMBER IS
446
447 CURSOR get_primary_uom_csr IS
448 select primary_uom_code from mtl_system_items
449 where inventory_item_id = p_inventory_item_id;
450
451 l_primary_uom VARCHAR2(10);
452 l_converted_qty NUMBER;
453 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Get_Primary_UOM_Qty';
454
455 BEGIN
456 IF (p_inventory_item_id IS NULL OR p_source_uom_code IS NULL OR p_quantity IS NULL) THEN
457 RETURN NULL;
458 END IF;
459 OPEN get_primary_uom_csr;
460 FETCH get_primary_uom_csr INTO l_primary_uom;
461 IF (get_primary_uom_csr%NOTFOUND) THEN
462 CLOSE get_primary_uom_csr;
463 RETURN null;
464 END IF;
465 CLOSE get_primary_uom_csr;
466 IF(p_source_uom_code = l_primary_uom) THEN
467 RETURN p_quantity;
468 END IF;
469
470 l_converted_qty := inv_convert.inv_um_convert(item_id => p_inventory_item_id,
471 precision => 2,
472 from_quantity => p_quantity,
473 from_unit => p_source_uom_code,
474 to_unit => l_primary_uom,
475 from_name => null,
476 to_name => null);
477 IF (l_converted_qty < 0) THEN
478 l_converted_qty := null;
479 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
480 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Could not convert from ' || p_source_uom_code ||
481 ' to ' || l_primary_uom || '. Returning null quantity.');
482 END IF;
483 END IF;
484 RETURN l_converted_qty;
485 END Get_Primary_UOM_Qty;
486
487 -- Start of Comments --
488 -- Function name : Get_Primary_UOM
489 -- Type : Private
490 -- Function : Private helper function to get the Primary UOM of an item
491 -- The inputs are the item id and the inventory org id.
492 -- Pre-reqs :
493 -- Parameters :
494 --
495 --
496 -- Get_Primary_UOM Parameters:
497 -- p_inventory_item_id IN NUMBER Required
498 -- The Id of Inventory item. If this is null, this function returns null.
499 -- p_inventory_org_id IN NUMBER Required
500 -- The inventory org id of the item. If this is null, this function returns null.
501 --
502 -- Version :
503 -- Initial Version 1.0
504 --
505 -- End of Comments.
506
507 FUNCTION Get_Primary_UOM
508 (
509 p_inventory_item_id IN NUMBER,
510 p_inventory_org_id IN NUMBER
511 ) RETURN VARCHAR2 IS
512 CURSOR get_primary_uom_csr IS
513 select primary_uom_code from mtl_system_items
514 where inventory_item_id = p_inventory_item_id and
515 organization_id = p_inventory_org_id;
516
517 l_primary_uom VARCHAR2(10);
518 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Get_Primary_UOM';
519
520 BEGIN
521 IF (p_inventory_item_id IS NULL OR p_inventory_org_id IS NULL) THEN
522 RETURN NULL;
523 END IF;
524 OPEN get_primary_uom_csr;
525 FETCH get_primary_uom_csr INTO l_primary_uom;
526 IF (get_primary_uom_csr%NOTFOUND) THEN
527 CLOSE get_primary_uom_csr;
528 RETURN null;
529 END IF;
530 CLOSE get_primary_uom_csr;
531 RETURN l_primary_uom;
532 END Get_Primary_UOM;
533
534 -------------------------------------
535 -- End Public Function Definitions --
536 -------------------------------------
537
538 ----------------------------------------
539 -- Local Procedure Definitions follow --
540 ----------------------------------------
541 ----------------------------------------------------------------------
542 -- This Procedure validates the input for the Get_Route_Mtl_Req API --
543 ----------------------------------------------------------------------
544 -- Modified by surrkuma for bug# 13998921 on 26-Apr-12
545 -- Added Visit ID as a parameter for validating visit task instance.
546 PROCEDURE Validate_Mtl_Req_Input
547 (
548 p_route_id IN NUMBER,
549 p_mr_route_id IN NUMBER,
550 p_item_instance_id IN NUMBER,
551 p_requirement_date IN DATE,
552 p_request_type IN VARCHAR2,
553 p_visit_id IN NUMBER := null,
554 x_route_id OUT NOCOPY NUMBER) IS
555
556 CURSOR get_route_id_csr IS
557 select route_id from ahl_mr_routes
558 where mr_route_id = p_mr_route_id;
559
560 CURSOR validate_route_id_csr(c_route_id IN NUMBER) IS
561 -- Changes by skpathak on 06-NOV-2008 for bug-7336824
562 -- select 'x' from AHL_ROUTES_APP_V
563 select 'x' from AHL_ROUTES_B
564 where route_id = c_route_id;
565
566 CURSOR validate_instance_id_csr IS
567 select 'x' from CSI_ITEM_INSTANCES
568 where instance_id = p_item_instance_id
569 and nvl(active_end_date, sysdate + 1) > sysdate;
570
571 -- Added by surrkuma for bug# 13998921 on 26-Apr-12
572 -- Check the Visit tasks for the given instance in PLANNING status
573 CURSOR check_visit_tasks_status_csr IS
574 SELECT 'x' FROM ahl_visit_tasks_b
575 WHERE visit_id = p_visit_id
576 AND instance_id = p_item_instance_id
577 AND status_code = 'PLANNING';
578
579 l_dummy VARCHAR2(1);
580 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Validate_Mtl_Req_Input';
581
582 BEGIN
583
584 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
585 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
586 END IF;
587
588 -- Get the Route Id
589 IF (p_route_id IS NULL AND p_mr_route_id IS NULL) THEN
590 FND_MESSAGE.Set_Name('AHL', 'AHL_LTP_APS_ROUTE_ID_NULL');
591 FND_MSG_PUB.ADD;
592 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
593 FND_LOG.MESSAGE(G_LEVEL_ERROR, L_DEBUG_KEY, FALSE);
594 END IF;
595 ELSIF (p_route_id IS NULL) THEN
596 OPEN get_route_id_csr;
597 FETCH get_route_id_csr INTO x_route_id;
598 CLOSE get_route_id_csr;
599 ELSE
600 x_route_id := p_route_id;
601 END IF;
602
603 -- Validate the Route Id
604 OPEN validate_route_id_csr(c_route_id => x_route_id);
605 FETCH validate_route_id_csr INTO l_dummy;
606 IF (validate_route_id_csr%NOTFOUND) THEN
607 FND_MESSAGE.Set_Name('AHL', 'AHL_LTP_APS_ROUTE_ID_INVALID');
608 FND_MESSAGE.Set_Token('ROUTE_ID', x_route_id);
609 FND_MSG_PUB.ADD;
610 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
611 FND_LOG.MESSAGE(G_LEVEL_ERROR, L_DEBUG_KEY, FALSE);
612 END IF;
613 END IF;
614 CLOSE validate_route_id_csr;
615
616 -- Validate the Instance Id
617 IF (p_item_instance_id IS NULL) THEN
618 FND_MESSAGE.Set_Name('AHL', 'AHL_LTP_APS_INST_ID_NULL');
619 FND_MSG_PUB.ADD;
620 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
621 FND_LOG.MESSAGE(G_LEVEL_ERROR, L_DEBUG_KEY, FALSE);
622 END IF;
623 ELSE
624 OPEN check_visit_tasks_status_csr;
625 FETCH check_visit_tasks_status_csr INTO l_dummy;
626 IF (check_visit_tasks_status_csr%FOUND) THEN
627 OPEN validate_instance_id_csr;
628 FETCH validate_instance_id_csr INTO l_dummy;
629 IF (validate_instance_id_csr%NOTFOUND) THEN
630 FND_MESSAGE.Set_Name('AHL', 'AHL_LTP_APS_INST_ID_INVALID');
631 FND_MESSAGE.Set_Token('INST_ID', p_item_instance_id);
632 FND_MSG_PUB.ADD;
633 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
634 FND_LOG.MESSAGE(G_LEVEL_ERROR, L_DEBUG_KEY, FALSE);
635 END IF;
636 END IF;
637 CLOSE validate_instance_id_csr;
638 END IF;
639 CLOSE check_visit_tasks_status_csr;
640 END IF;
641
642 -- Validate the Request Type
643 IF (p_request_type IS NULL) THEN
644 FND_MESSAGE.Set_Name('AHL', 'AHL_LTP_APS_REQ_TYPE_NULL');
645 FND_MSG_PUB.ADD;
646 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
647 FND_LOG.MESSAGE(G_LEVEL_ERROR, L_DEBUG_KEY, FALSE);
648 END IF;
649 ELSIF (p_request_type NOT IN (G_REQ_TYPE_FORECAST, G_REQ_TYPE_PLANNED)) THEN
650 FND_MESSAGE.Set_Name('AHL', 'AHL_LTP_APS_REQ_TYPE_INVALID');
651 FND_MESSAGE.Set_Token('REQ_TYPE', p_request_type);
652 FND_MSG_PUB.ADD;
653 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
654 FND_LOG.MESSAGE(G_LEVEL_ERROR, L_DEBUG_KEY, FALSE);
655 END IF;
656 END IF;
657
658 -- Validate the Requirement Date
659 IF (p_requirement_date IS NULL) THEN
660 FND_MESSAGE.Set_Name('AHL', 'AHL_LTP_APS_REQ_DATE_NULL');
661 FND_MSG_PUB.ADD;
662 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
663 FND_LOG.MESSAGE(G_LEVEL_ERROR, L_DEBUG_KEY, FALSE);
664 END IF;
665 -- SKPATHAK :: Bug 8343599 :: 13-APR-2009
666 -- Removing the check to allow creation of Material Requirements in the past.
667 /**
668 ELSIF (TRUNC(p_requirement_date) < TRUNC(SYSDATE)) THEN
669 FND_MESSAGE.Set_Name('AHL', 'AHL_LTP_APS_REQ_DATE_PAST');
670 FND_MESSAGE.Set_Token('REQ_DATE', p_requirement_date);
671 FND_MSG_PUB.ADD;
672 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
673 FND_LOG.MESSAGE(G_LEVEL_ERROR, L_DEBUG_KEY, FALSE);
674 END IF;
675 **/
676 END IF;
677
678 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
679 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
680 END IF;
681
682 END Validate_Mtl_Req_Input;
683
684 --------------------------------------------------------------------
685 -- This Procedure gets the requirements from the Disposition List --
686 --------------------------------------------------------------------
687 PROCEDURE Get_Disp_List_Requirements
688 (
689 p_route_id IN NUMBER,
690 p_item_instance_id IN NUMBER,
691 p_requirement_date IN DATE,
692 p_request_type IN VARCHAR2,
693 p_unit_instance_id IN NUMBER,
694 p_inst_item_id IN NUMBER,
695 p_mc_header_id IN NUMBER,
696 p_mc_id IN NUMBER,
697 p_mc_version IN NUMBER,
698 x_disp_req_list OUT NOCOPY Route_Mtl_Req_Tbl_Type) IS
699
700 CURSOR get_mc_route_eff_id_csr IS
701 SELECT RE.route_effectivity_id
702 FROM AHL_ROUTE_EFFECTIVITIES RE
703 WHERE RE.route_id = p_route_id
704 AND (RE.mc_header_id = NVL(p_mc_header_id, -1) -- Match MC Header Id first
705 OR (RE.mc_id = NVL(p_mc_id, -1) -- Match MC Id next
706 AND RE.mc_header_id IS NULL -- Added on 10/28/03 since Version specific also has stores the MC Id
707 AND NOT EXISTS (SELECT 'x' FROM AHL_ROUTE_EFFECTIVITIES RE1
708 WHERE RE1.route_id = p_route_id
709 AND RE1.mc_header_id = NVL(p_mc_header_id, -1))
710 )
711 );
712
713 CURSOR get_item_route_eff_id_csr IS
714 SELECT RE.route_effectivity_id
715 FROM AHL_ROUTE_EFFECTIVITIES RE
716 WHERE RE.route_id = p_route_id
717 AND RE.inventory_item_id = p_inst_item_id; -- Match the inventory item id
718
719 CURSOR get_disp_req_dtls_csr(c_mc_route_eff_id IN NUMBER,
720 c_item_route_eff_id IN NUMBER) IS
721 SELECT ROM.RT_OPER_MATERIAL_ID,
722 ROM.INVENTORY_ITEM_ID,
723 ROM.INVENTORY_ORG_ID,
724 ROM.UOM_CODE,
725 ROM.QUANTITY,
726 AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY) AS PRIMARY_QUANTITY,
727 AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS PRIMARY_UOM_CODE,
728 ITEM_GROUP_ID,
729 ITEM_COMP_DETAIL_ID,
730 POSITION_PATH_ID,
731 PP.PATH_POS_COMMON_ID,
732 PP.VER_SPEC_SCORE
733 FROM AHL_RT_OPER_MATERIALS ROM, AHL_MC_PATH_POSITIONS PP
734 WHERE OBJECT_ID in (NVL(c_mc_route_eff_id, -1), NVL(c_item_route_eff_id, -1)) AND
735 ROM.POSITION_PATH_ID = PP.PATH_POSITION_ID (+) AND
736 ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_DISPOSITION AND
737 ((REPLACE_PERCENT = 100 AND p_request_type = G_REQ_TYPE_FORECAST) OR
738 (p_request_type = G_REQ_TYPE_PLANNED)
739 )
740 ORDER BY PATH_POS_COMMON_ID, VER_SPEC_SCORE DESC;
741
742 CURSOR get_item_comp_dtls_csr(c_item_comp_detail_id IN NUMBER,
743 c_quantity IN NUMBER,
744 c_uom IN VARCHAR2) IS
745 SELECT ICD.inventory_item_id, ICD.inventory_master_org_id, ICD.item_group_id,
746 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,
747 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
748 FROM AHL_ITEM_COMP_DETAILS ICD
749 WHERE ITEM_COMP_DETAIL_ID = c_item_comp_detail_id;
750
751 l_mc_route_effectivity_id NUMBER;
752 l_item_route_effectivity_id NUMBER;
753 l_index NUMBER;
754 l_last_common_id NUMBER := -1;
755 l_prior_count NUMBER;
756 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Get_Disp_List_Requirements';
757
758 BEGIN
759
760 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
761 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
762 END IF;
763
764 -- Get the Route Effectivity
765 OPEN get_mc_route_eff_id_csr;
766 FETCH get_mc_route_eff_id_csr INTO l_mc_route_effectivity_id;
767 IF (get_mc_route_eff_id_csr%NOTFOUND) THEN
768 l_mc_route_effectivity_id := -1;
769 END IF;
770 CLOSE get_mc_route_eff_id_csr;
771
772 OPEN get_item_route_eff_id_csr;
773 FETCH get_item_route_eff_id_csr INTO l_item_route_effectivity_id;
774 IF (get_item_route_eff_id_csr%NOTFOUND) THEN
775 l_item_route_effectivity_id := -1;
776 END IF;
777 CLOSE get_item_route_eff_id_csr;
778
779 IF(l_item_route_effectivity_id = -1 AND l_mc_route_effectivity_id = -1) THEN
780 -- No Disposition List available
781 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
782 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'No disposition list found for MC Header Id: ' || p_mc_header_id
783 || ', MC Id: ' || p_mc_id
784 || ', Item Id: ' || p_inst_item_id);
785 END IF;
786 RETURN;
787 END IF;
788
789 l_index := 0;
790 FOR l_disp_req_rec IN get_disp_req_dtls_csr(c_mc_route_eff_id => l_mc_route_effectivity_id,
791 c_item_route_eff_id => l_item_route_effectivity_id) LOOP
792 l_index := l_index + 1;
793 IF ((l_disp_req_rec.INVENTORY_ITEM_ID IS NOT NULL) AND (l_disp_req_rec.ITEM_COMP_DETAIL_ID IS NULL)) THEN
794 -- Simple Item requirement with no reference to any Item composition
795 x_disp_req_list(l_index).RT_OPER_MATERIAL_ID := l_disp_req_rec.RT_OPER_MATERIAL_ID;
796 x_disp_req_list(l_index).INVENTORY_ITEM_ID := l_disp_req_rec.INVENTORY_ITEM_ID;
797 x_disp_req_list(l_index).INV_MASTER_ORG_ID := l_disp_req_rec.INVENTORY_ORG_ID;
798 -- Quantity in Item's Primary UOM
799 x_disp_req_list(l_index).QUANTITY := l_disp_req_rec.PRIMARY_QUANTITY;
800 x_disp_req_list(l_index).UOM_CODE := l_disp_req_rec.PRIMARY_UOM_CODE;
801 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
802 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Requirement ' || l_index || ': RT_OPER_MATERIAL_ID = ' || l_disp_req_rec.RT_OPER_MATERIAL_ID ||
803 ', INVENTORY_ITEM_ID = ' || l_disp_req_rec.INVENTORY_ITEM_ID ||
804 ', INV_MASTER_ORG_ID = ' || l_disp_req_rec.INVENTORY_ORG_ID ||
805 ', QUANTITY = ' || l_disp_req_rec.PRIMARY_QUANTITY ||
806 ', UOM_CODE = ' || l_disp_req_rec.PRIMARY_UOM_CODE);
807 END IF;
808 ELSIF ((l_disp_req_rec.ITEM_GROUP_ID IS NOT NULL) AND (l_disp_req_rec.ITEM_COMP_DETAIL_ID IS NULL)) THEN
809 -- Simple Item Group requirement with no reference to any Item composition
810 x_disp_req_list(l_index).RT_OPER_MATERIAL_ID := l_disp_req_rec.RT_OPER_MATERIAL_ID;
811 x_disp_req_list(l_index).ITEM_GROUP_ID := l_disp_req_rec.ITEM_GROUP_ID;
812 x_disp_req_list(l_index).QUANTITY := l_disp_req_rec.QUANTITY;
813 x_disp_req_list(l_index).UOM_CODE := l_disp_req_rec.UOM_CODE;
814 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
815 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Requirement ' || l_index || ': RT_OPER_MATERIAL_ID = ' || l_disp_req_rec.RT_OPER_MATERIAL_ID ||
816 ', ITEM_GROUP_ID = ' || l_disp_req_rec.ITEM_GROUP_ID ||
817 ', QUANTITY = ' || l_disp_req_rec.QUANTITY ||
818 ', UOM_CODE = ' || l_disp_req_rec.UOM_CODE);
819 END IF;
820 ELSIF (l_disp_req_rec.ITEM_COMP_DETAIL_ID IS NOT NULL) THEN
821 -- Item Composition requirement
822 x_disp_req_list(l_index).RT_OPER_MATERIAL_ID := l_disp_req_rec.RT_OPER_MATERIAL_ID;
823 x_disp_req_list(l_index).ITEM_COMP_DETAIL_ID := l_disp_req_rec.ITEM_COMP_DETAIL_ID;
824 -- Get the details from the Item Composition Details table
825 OPEN get_item_comp_dtls_csr(c_item_comp_detail_id => l_disp_req_rec.ITEM_COMP_DETAIL_ID,
826 c_quantity => l_disp_req_rec.QUANTITY,
827 c_uom => l_disp_req_rec.UOM_CODE);
828 FETCH get_item_comp_dtls_csr INTO x_disp_req_list(l_index).INVENTORY_ITEM_ID,
829 x_disp_req_list(l_index).INV_MASTER_ORG_ID,
830 x_disp_req_list(l_index).ITEM_GROUP_ID,
831 x_disp_req_list(l_index).QUANTITY,
832 x_disp_req_list(l_index).UOM_CODE;
833 CLOSE get_item_comp_dtls_csr;
834 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
835 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Requirement ' || l_index || ': RT_OPER_MATERIAL_ID = ' || l_disp_req_rec.RT_OPER_MATERIAL_ID ||
836 ', ITEM_COMP_DETAIL_ID = ' || l_disp_req_rec.ITEM_COMP_DETAIL_ID ||
837 ', INVENTORY_ITEM_ID = ' || x_disp_req_list(l_index).INVENTORY_ITEM_ID ||
838 ', INV_MASTER_ORG_ID = ' || x_disp_req_list(l_index).INV_MASTER_ORG_ID ||
839 ', ITEM_GROUP_ID = ' || x_disp_req_list(l_index).ITEM_GROUP_ID ||
840 ', QUANTITY = ' || x_disp_req_list(l_index).QUANTITY ||
841 ', UOM_CODE = ' || x_disp_req_list(l_index).UOM_CODE);
842 END IF;
843 ELSE
844 -- Requirement with only Position path reference
845 IF (p_mc_header_id IS NULL) THEN
846 -- Ignore this Position Path specific requirement since the current instance
847 -- is not a UC unit (is not of any MC type)
848 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
849 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Ignoring Disp. List Requirement with Position Path Id ' || l_disp_req_rec.POSITION_PATH_ID);
850 END IF;
851 ELSE
852 -- Get the Position Path requirement if applicable
853 IF (l_disp_req_rec.PATH_POS_COMMON_ID <> l_last_common_id) THEN
854 -- Consider this Position Path since it is has a new common id
855 l_prior_count := x_disp_req_list.COUNT;
856 Get_Pos_Path_Requirement(p_position_path_id => l_disp_req_rec.POSITION_PATH_ID,
857 p_requirement_date => p_requirement_date,
858 p_unit_instance_id => p_unit_instance_id,
859 p_mc_header_id => p_mc_header_id,
860 p_mc_id => p_mc_id,
861 p_mc_version => p_mc_version,
862 p_rt_oper_mtl_id => l_disp_req_rec.RT_OPER_MATERIAL_ID,
863 p_quantity => l_disp_req_rec.QUANTITY,
864 p_uom => l_disp_req_rec.UOM_CODE,
865 p_x_disp_req_list => x_disp_req_list);
866 IF (x_disp_req_list.COUNT > l_prior_count) THEN
867 -- The last Position Path requirement was valid and added.
868 -- Set the l_last_common_id so that subsequent records
869 -- with the same common id (but lower score) can be ignored.
870 l_last_common_id := l_disp_req_rec.PATH_POS_COMMON_ID;
871 ELSE
872 -- Not applicable: Hence not added: Continue to the next lower score.
873 null;
874 END IF; -- if count has increased
875 ELSE
876 -- Common id is same as last added, but lower score: ignore this record
877 null;
878 END IF; -- Common Id is different
879 END IF;
880 l_index := x_disp_req_list.COUNT; -- Set output table index to always point to the last added record.
881 END IF;
882 END LOOP;
883 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
884 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Total number of disposition requirements: ' || x_disp_req_list.COUNT);
885 END IF;
886
887 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
888 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
889 END IF;
890
891 END Get_Disp_List_Requirements;
892
893 -------------------------------------------------------
894 -- This Procedure gets the Position Path Requirement --
895 -------------------------------------------------------
896 PROCEDURE Get_Pos_Path_Requirement
897 (
898 p_position_path_id IN NUMBER,
899 p_requirement_date IN DATE,
900 p_unit_instance_id IN NUMBER,
901 p_mc_header_id IN NUMBER,
902 p_mc_id IN NUMBER,
903 p_mc_version IN NUMBER,
904 p_rt_oper_mtl_id IN NUMBER,
905 p_quantity IN NUMBER,
906 p_uom IN VARCHAR2,
907 p_x_disp_req_list IN OUT NOCOPY Route_Mtl_Req_Tbl_Type) IS
908
909 CURSOR get_sub_unit_mc_csr(c_pos_instance_id IN NUMBER) IS
910 SELECT master_config_id
911 FROM AHL_UNIT_CONFIG_HEADERS
912 WHERE CSI_ITEM_INSTANCE_ID = c_pos_instance_id;
913
914 CURSOR get_item_grp_for_mc_csr(c_mc_header_id IN NUMBER) IS
915 SELECT item_group_id
916 FROM AHL_MC_RELATIONSHIPS
917 WHERE mc_header_id = c_mc_header_id
918 AND parent_relationship_id is null;
919
920 CURSOR get_sub_mc_for_pos_csr(c_position_id IN NUMBER) IS
921 SELECT CR.mc_header_id
922 FROM AHL_MC_CONFIG_RELATIONS CR, AHL_MC_HEADERS_B MC
923 WHERE CR.relationship_id = c_position_id
924 AND MC.mc_header_id = CR.mc_header_id
925 ORDER BY MC.name;
926
927 l_position_id NUMBER;
928 l_pos_item_group_id NUMBER;
929 l_pos_instance_id NUMBER;
930 l_sub_unit_mc NUMBER;
931 l_next_index NUMBER := p_x_disp_req_list.COUNT + 1;
932 l_valid_flag VARCHAR2(1);
933 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Get_Pos_Path_Requirement';
934
935 BEGIN
936
937 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
938 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
939 END IF;
940
941 -- First validate the Position Path and get the Position Details
942 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
943 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Calling Validate_Path_Position with ' ||
944 'p_position_path_id = ' || p_position_path_id ||
945 ', p_unit_instance_id = ' || p_unit_instance_id ||
946 ', p_requirement_date = ' || p_requirement_date);
947 END IF;
948 Validate_Path_Position(p_path_position_id => p_position_path_id,
949 p_unit_instance_id => p_unit_instance_id,
950 p_requirement_date => p_requirement_date,
951 x_valid_flag => l_valid_flag,
952 x_relationship_id => l_position_id,
953 x_item_group_id => l_pos_item_group_id,
954 x_pos_instance_id => l_pos_instance_id);
955
956 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
957 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Validate_Path_Position returned ' ||
958 'x_valid_flag = ' || l_valid_flag ||
959 ', x_relationship_id = ' || l_position_id ||
960 ', x_item_group_id = ' || l_pos_item_group_id ||
961 ', x_pos_instance_id = ' || l_pos_instance_id);
962 END IF;
963
964 IF (l_valid_flag = FND_API.G_FALSE) THEN
965 -- Don't include the requirement if the position path is not valid
966 RETURN;
967 END IF;
968
969 IF (l_pos_instance_id IS NULL) THEN
970 -- The position is empty
971 -- If the position has an item group associated, copy that as the requirement
972 IF (l_pos_item_group_id IS NOT NULL) THEN
973 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
974 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Position ' || l_position_id || ' is empty and has item group ' ||
975 l_pos_item_group_id || ' associated with it.');
976 END IF;
977 p_x_disp_req_list(l_next_index).ITEM_GROUP_ID := l_pos_item_group_id;
978 ELSE
979 -- No Item group for the empty position: Pick one Sub config for the position
980 OPEN get_sub_mc_for_pos_csr(c_position_id => l_position_id);
981 FETCH get_sub_mc_for_pos_csr INTO l_sub_unit_mc;
982 CLOSE get_sub_mc_for_pos_csr;
983 -- Get the Item group for the root node of the chosen Sub MC
984 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
985 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Chosen Sub MC Header Id ' || l_sub_unit_mc ||
986 ' for empty position with id ' || l_position_id);
987 END IF;
988 OPEN get_item_grp_for_mc_csr(c_mc_header_id => l_sub_unit_mc);
989 FETCH get_item_grp_for_mc_csr INTO p_x_disp_req_list(l_next_index).ITEM_GROUP_ID;
990 CLOSE get_item_grp_for_mc_csr;
991 END IF;
992 ELSE
993 -- The position is not empty
994 -- Check if the position corresponds to the root node
995 IF (l_pos_instance_id <> p_unit_instance_id) THEN
996 -- Not root node: Check if a sub-unit is installed in the position
997 OPEN get_sub_unit_mc_csr(c_pos_instance_id => l_pos_instance_id);
998 FETCH get_sub_unit_mc_csr INTO l_sub_unit_mc;
999 IF(get_sub_unit_mc_csr%FOUND) THEN
1000 -- A sub unit (UC) is installed in the position
1001 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1002 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Sub unit with Sub MC Header Id ' || l_sub_unit_mc ||
1003 ' installed at position with id ' || l_position_id);
1004 END IF;
1005 -- Get the Item group for the root node of the Sub unit's MC
1006 OPEN get_item_grp_for_mc_csr(c_mc_header_id => l_sub_unit_mc);
1007 FETCH get_item_grp_for_mc_csr INTO p_x_disp_req_list(l_next_index).ITEM_GROUP_ID;
1008 CLOSE get_item_grp_for_mc_csr;
1009 ELSE
1010 -- An instance (not a sub unit) is installed in the position
1011 -- Add this position's item group as requirement to the list
1012 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1013 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Simple instance with id ' || l_pos_instance_id ||
1014 ' installed at position with id ' || l_position_id);
1015 END IF;
1016 p_x_disp_req_list(l_next_index).ITEM_GROUP_ID := l_pos_item_group_id;
1017 END IF; -- Sub unit or instance
1018 CLOSE get_sub_unit_mc_csr;
1019 ELSE
1020 -- Root Node
1021 -- Add the item group requirement to the list
1022 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1023 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Position Id ' || l_position_id || ' corresponds to the root node.');
1024 END IF;
1025 p_x_disp_req_list(l_next_index).ITEM_GROUP_ID := l_pos_item_group_id;
1026 END IF; -- Root node or not
1027 END IF; -- Position is empty or not
1028
1029 -- Copy the remaining attributes
1030 p_x_disp_req_list(l_next_index).RT_OPER_MATERIAL_ID := p_rt_oper_mtl_id;
1031 p_x_disp_req_list(l_next_index).POSITION_PATH_ID := p_position_path_id;
1032 p_x_disp_req_list(l_next_index).RELATIONSHIP_ID := l_position_id;
1033 p_x_disp_req_list(l_next_index).QUANTITY := p_quantity;
1034 p_x_disp_req_list(l_next_index).UOM_CODE := p_uom;
1035 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1036 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Requirement ' || l_next_index || ': RT_OPER_MATERIAL_ID = ' || p_rt_oper_mtl_id ||
1037 ', ITEM_GROUP_ID = ' || p_x_disp_req_list(l_next_index).ITEM_GROUP_ID ||
1038 ', POSITION_PATH_ID = ' || p_position_path_id ||
1039 ', RELATIONSHIP_ID = ' || l_position_id ||
1040 ', QUANTITY = ' || p_quantity ||
1041 ', UOM_CODE = ' || p_uom);
1042 END IF;
1043
1044 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1045 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1046 END IF;
1047
1048 END Get_Pos_Path_Requirement;
1049
1050 -- Modified by surrkuma for Service Bulletin, 07-Jun-2011
1051 -- Added the parameters MC_ID, MC_HEADER_ID, UC_HEADER_ID and VISIT_ID
1052 ------------------------------------------------------------------------
1053 -- This Procedure gets the requirements from the Route (or Operation) --
1054 ------------------------------------------------------------------------
1055 PROCEDURE Get_Route_Requirements
1056 (
1057 p_route_id IN NUMBER,
1058 p_request_type IN VARCHAR2,
1059 p_mc_id IN NUMBER,
1060 p_mc_header_id IN NUMBER,
1061 p_uc_header_id IN NUMBER,
1062 p_visit_id IN NUMBER,
1063 x_route_req_list OUT NOCOPY Route_Mtl_Req_Tbl_Type) IS
1064
1065 -- SURRKUMA :: Service Bulletin :: 07-Jun-2011
1066 -- Modified the cursor to fetch details required for position level requirements
1067 -- like MC_ID, POSITION_KEY, QUANTITY and UOM_CODE from AHL_RT_OPER_MATERIALS table.
1068 -- NOTE: The same modifications are done for the rest of the cursors too, listing the cursor names.
1069 -- get_op_level_reqs_forecast_csr, get_op_level_reqs_planned_csr and l_op_requirement_neither_csr
1070 CURSOR get_route_level_reqs_csr IS
1071 SELECT ROM.RT_OPER_MATERIAL_ID,
1072 NULL AS ROUTE_OPERATION_ID,
1073 ROM.INVENTORY_ITEM_ID,
1074 ROM.INVENTORY_ORG_ID,
1075 ROM.MC_ID,
1076 ROM.POSITION_KEY,
1077 ROM.QUANTITY,
1078 ROM.UOM_CODE,
1079 AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY) AS PRIMARY_QUANTITY,
1080 AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS PRIMARY_UOM_CODE,
1081 ROM.ITEM_GROUP_ID
1082 FROM AHL_RT_OPER_MATERIALS ROM
1083 WHERE OBJECT_ID = p_route_id
1084 AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_ROUTE;
1085
1086 -- AnRaj : Start for fixing perf bug 4919527
1087 /*
1088 CURSOR get_op_level_reqs_csr IS
1089 --SELECT NULL AS RT_OPER_MATERIAL_ID,
1090 --NULL AS ROUTE_OPERATION_ID,
1091 -- support for Oracle 8
1092 SELECT TO_NUMBER(NULL) AS RT_OPER_MATERIAL_ID,
1093 TO_NUMBER(NULL) AS ROUTE_OPERATION_ID,
1094 INVENTORY_ITEM_ID,
1095 INVENTORY_ORG_ID,
1096 -- Aggregate item quantities across operations when forecasting
1097 SUM(AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY)) AS QUANTITY, --Total Primary Qty
1098 AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS UOM_CODE,
1099 -- support for Oracle 8
1100 --NULL AS ITEM_GROUP_ID
1101 TO_NUMBER(NULL) AS ITEM_GROUP_ID
1102 FROM AHL_RT_OPER_MATERIALS ROM
1103 WHERE OBJECT_ID in (SELECT RO.operation_id
1104 FROM ahl_operations_vl O, ahl_route_operations RO
1105 WHERE O.operation_id = RO.operation_id and
1106 RO.route_id = p_route_id and
1107 O.revision_status_code = 'COMPLETE' and
1108 O.revision_number in (SELECT max(revision_number)
1109 FROM ahl_operations_b_kfv
1110 WHERE concatenated_segments =
1111 O.concatenated_segments and
1112 trunc(sysdate) between
1113 trunc(start_date_active) and
1114 trunc(NVL(end_date_active,SYSDATE+1)))
1115 )
1116 AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_OPERATION
1117 AND INVENTORY_ITEM_ID IS NOT NULL
1118 AND p_request_type = G_REQ_TYPE_FORECAST
1119 GROUP BY INVENTORY_ITEM_ID, INVENTORY_ORG_ID
1120 UNION
1121 -- Don't aggregate for Operation items when Firm Planning
1122 SELECT RT_OPER_MATERIAL_ID,
1123 OBJECT_ID AS ROUTE_OPERATION_ID,
1124 INVENTORY_ITEM_ID,
1125 INVENTORY_ORG_ID,
1126 AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY) AS QUANTITY, -- Primary Qty
1127 AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS UOM_CODE,
1128 --NULL AS ITEM_GROUP_ID
1129 -- support for Oracle 8
1130 TO_NUMBER(NULL) AS ITEM_GROUP_ID
1131 FROM AHL_RT_OPER_MATERIALS ROM
1132 WHERE OBJECT_ID in (SELECT RO.operation_id
1133 FROM ahl_operations_vl O, ahl_route_operations RO
1134 WHERE O.operation_id = RO.operation_id and
1135 RO.route_id = p_route_id and
1136 O.revision_status_code = 'COMPLETE' and
1137 O.revision_number in (SELECT max(revision_number)
1138 FROM ahl_operations_b_kfv
1139 WHERE concatenated_segments =
1140 O.concatenated_segments and
1141 trunc(sysdate) between
1142 trunc(start_date_active) and
1143 trunc(NVL(end_date_active,SYSDATE+1)))
1144 )
1145 AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_OPERATION
1146 AND INVENTORY_ITEM_ID IS NOT NULL
1147 AND p_request_type = G_REQ_TYPE_PLANNED
1148 UNION
1149 -- Item Group: No need to aggregate or convert to Primary UOM
1150 SELECT RT_OPER_MATERIAL_ID,
1151 OBJECT_ID AS ROUTE_OPERATION_ID,
1152 --NULL AS INVENTORY_ITEM_ID,
1153 --NULL AS INVENTORY_ORG_ID,
1154 -- support for Oracle 8
1155 TO_NUMBER(NULL) AS INVENTORY_ITEM_ID,
1156 TO_NUMBER(NULL) AS INVENTORY_ORG_ID,
1157 QUANTITY,
1158 UOM_CODE,
1159 ITEM_GROUP_ID
1160 FROM AHL_RT_OPER_MATERIALS ROM
1161 WHERE OBJECT_ID in (SELECT RO.operation_id
1162 FROM ahl_operations_vl O, ahl_route_operations RO
1163 WHERE O.operation_id = RO.operation_id and
1164 RO.route_id = p_route_id and
1165 O.revision_status_code = 'COMPLETE' and
1166 O.revision_number in (SELECT max(revision_number)
1167 FROM ahl_operations_b_kfv
1168 WHERE concatenated_segments =
1169 O.concatenated_segments and
1170 trunc(sysdate) between
1171 trunc(start_date_active) and
1172 trunc(NVL(end_date_active,SYSDATE+1)))
1173 )
1174 AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_OPERATION
1175 AND INVENTORY_ITEM_ID IS NULL;*/
1176
1177 CURSOR get_op_level_reqs_forecast_csr IS
1178 SELECT TO_NUMBER(NULL) AS RT_OPER_MATERIAL_ID,
1179 TO_NUMBER(NULL) AS ROUTE_OPERATION_ID,
1180 INVENTORY_ITEM_ID,
1181 INVENTORY_ORG_ID,
1182 TO_NUMBER(NULL) AS MC_ID,
1183 TO_NUMBER(NULL) AS POSITION_KEY,
1184 TO_NUMBER(NULL) AS QUANTITY,
1185 NULL AS UOM_CODE,
1186 -- Aggregate item quantities across operations when forecasting
1187 SUM(AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY)) AS PRIMARY_QUANTITY, /*Total Primary Qty */
1188 AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS PRIMARY_UOM_CODE,
1189 TO_NUMBER(NULL) AS 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 -- manisaga : Bug# 9726667 : 22-Jun-2010
1200 -- added the below condition to fetch the latest revision of
1201 -- operations with only 'complete' status
1202 and revision_status_code = 'COMPLETE'
1203 and trunc(sysdate) between
1204 trunc(start_date_active) and
1205 trunc(NVL(end_date_active,SYSDATE+1)))
1206 )
1207 AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_OPERATION
1208 AND INVENTORY_ITEM_ID IS NOT NULL
1209 GROUP BY INVENTORY_ITEM_ID, INVENTORY_ORG_ID
1210 UNION
1211 SELECT RT_OPER_MATERIAL_ID,
1212 OBJECT_ID AS ROUTE_OPERATION_ID,
1213 TO_NUMBER(NULL) AS INVENTORY_ITEM_ID,
1214 TO_NUMBER(NULL) AS INVENTORY_ORG_ID,
1215 MC_ID,
1216 POSITION_KEY,
1217 QUANTITY,
1218 UOM_CODE,
1219 QUANTITY AS PRIMARY_QUANTITY,
1220 UOM_CODE AS PRIMARY_UOM_CODE,
1221 ITEM_GROUP_ID
1222 FROM AHL_RT_OPER_MATERIALS ROM
1223 WHERE OBJECT_ID in ( SELECT RO.operation_id
1224 FROM AHL_OPERATIONS_B_KFV O, ahl_route_operations RO
1225 WHERE O.operation_id = RO.operation_id and
1226 RO.route_id = p_route_id and
1227 O.revision_status_code = 'COMPLETE' and
1228 O.revision_number in (SELECT max(revision_number)
1229 FROM ahl_operations_b_kfv
1230 WHERE concatenated_segments = O.concatenated_segments
1231 -- manisaga : Bug# 9726667 : 22-Jun-2010
1232 and revision_status_code = 'COMPLETE'
1233 and
1234 trunc(sysdate) between
1235 trunc(start_date_active) and
1236 trunc(NVL(end_date_active,SYSDATE+1)))
1237 )
1238 AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_OPERATION
1239 AND INVENTORY_ITEM_ID IS NULL;
1240
1241 CURSOR get_op_level_reqs_planned_csr IS
1242 SELECT RT_OPER_MATERIAL_ID,
1243 OBJECT_ID AS ROUTE_OPERATION_ID,
1244 INVENTORY_ITEM_ID,
1245 INVENTORY_ORG_ID,
1246 MC_ID,
1247 POSITION_KEY,
1248 QUANTITY,
1249 UOM_CODE,
1250 AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY) AS PRIMARY_QUANTITY, /* Primary Qty */
1251 AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS PRIMARY_UOM_CODE,
1252 TO_NUMBER(NULL) AS ITEM_GROUP_ID
1253 FROM AHL_RT_OPER_MATERIALS ROM
1254 WHERE OBJECT_ID in ( SELECT RO.operation_id
1255 FROM AHL_OPERATIONS_B_KFV O, ahl_route_operations RO
1256 WHERE O.operation_id = RO.operation_id
1257 and RO.route_id = p_route_id
1258 and O.revision_status_code = 'COMPLETE'
1259 and O.revision_number in ( SELECT max(revision_number)
1260 FROM ahl_operations_b_kfv
1261 WHERE concatenated_segments =O.concatenated_segments
1262 -- manisaga : Bug# 9726667 : 22-Jun-2010
1263 and revision_status_code = 'COMPLETE'
1264 and
1265 trunc(sysdate) between
1266 trunc(start_date_active) and
1267 trunc(NVL(end_date_active,SYSDATE+1)))
1268 )
1269 AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_OPERATION
1270 AND INVENTORY_ITEM_ID IS NOT NULL
1271 UNION
1272 -- Item Group: No need to aggregate or convert to Primary UOM
1273 SELECT RT_OPER_MATERIAL_ID,
1274 OBJECT_ID AS ROUTE_OPERATION_ID,
1275 TO_NUMBER(NULL) AS INVENTORY_ITEM_ID,
1276 TO_NUMBER(NULL) AS INVENTORY_ORG_ID,
1277 MC_ID,
1278 POSITION_KEY,
1279 QUANTITY,
1280 UOM_CODE,
1281 QUANTITY AS PRIMARY_QUANTITY,
1282 UOM_CODE AS PRIMARY_UOM_CODE,
1283 ITEM_GROUP_ID
1284 FROM AHL_RT_OPER_MATERIALS ROM
1285 WHERE OBJECT_ID in (SELECT RO.operation_id
1286 FROM AHL_OPERATIONS_B_KFV O, ahl_route_operations RO
1287 WHERE O.operation_id = RO.operation_id and
1288 RO.route_id = p_route_id and
1289 O.revision_status_code = 'COMPLETE' and
1290 O.revision_number in (SELECT max(revision_number)
1291 FROM ahl_operations_b_kfv
1292 WHERE concatenated_segments = O.concatenated_segments
1293 -- manisaga : Bug# 9726667 : 22-Jun-2010
1294 and revision_status_code = 'COMPLETE'
1295 and
1296 trunc(sysdate) between
1297 trunc(start_date_active) and
1298 trunc(NVL(end_date_active,SYSDATE+1)))
1299 )
1300 AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_OPERATION
1301 AND INVENTORY_ITEM_ID IS NULL;
1302
1303 CURSOR l_op_requirement_neither_csr IS
1304 SELECT RT_OPER_MATERIAL_ID,
1305 OBJECT_ID AS ROUTE_OPERATION_ID,
1306 TO_NUMBER(NULL) AS INVENTORY_ITEM_ID,
1307 TO_NUMBER(NULL) AS INVENTORY_ORG_ID,
1308 MC_ID,
1309 POSITION_KEY,
1310 QUANTITY,
1311 UOM_CODE,
1312 ITEM_GROUP_ID
1313 FROM AHL_RT_OPER_MATERIALS ROM
1314 WHERE OBJECT_ID in (SELECT RO.operation_id
1315 FROM AHL_OPERATIONS_B_KFV O, ahl_route_operations RO
1316 WHERE O.operation_id = RO.operation_id and
1317 RO.route_id = p_route_id and
1318 O.revision_status_code = 'COMPLETE' and
1319 O.revision_number in (SELECT max(revision_number)
1320 FROM ahl_operations_b_kfv
1321 WHERE concatenated_segments = O.concatenated_segments
1322 -- manisaga : Bug# 9726667 : 22-Jun-2010
1323 and revision_status_code = 'COMPLETE'
1324 and
1325 trunc(sysdate) between
1326 trunc(start_date_active) and
1327 trunc(NVL(end_date_active,SYSDATE+1)))
1328 )
1329 AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_OPERATION
1330 AND INVENTORY_ITEM_ID IS NULL;
1331 -- AnRaj : End for fixing perf bug 4919527
1332
1333 -- Added by jaramana on 22-OCT-2009 for bug 9037150
1334 -- Use the following cursor to do a high level check for presence of operation level mtl requirements
1335 CURSOR op_level_planned_reqs_exist IS
1336 SELECT ROM.RT_OPER_MATERIAL_ID
1337 FROM AHL_RT_OPER_MATERIALS ROM, AHL_ROUTE_OPERATIONS RO
1338 WHERE ROM.OBJECT_ID = RO.operation_id
1339 AND RO.route_id = p_route_id
1340 AND ROM.ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_OPERATION;
1341 l_dummy op_level_planned_reqs_exist%ROWTYPE;
1342
1343 -- Added by SURRKUMA for Service Bulletin :: 07-Jun-2011
1344 l_api_version CONSTANT NUMBER := 1.0;
1345 l_msg_data VARCHAR2(2000);
1346 l_return_status VARCHAR2(1);
1347 l_msg_count NUMBER;
1348
1349 l_rt_requirement_rec get_route_level_reqs_csr%ROWTYPE;
1350 l_index NUMBER := 0;
1351 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Get_Route_Requirements';
1352
1353 BEGIN
1354
1355 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1356 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1357 END IF;
1358
1359 -- Get the requirements defined at the route level (if any)
1360 OPEN get_route_level_reqs_csr;
1361 FETCH get_route_level_reqs_csr INTO l_rt_requirement_rec;
1362 IF(get_route_level_reqs_csr%FOUND) THEN
1363 -- Requirements defined at the Route level
1364 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1365 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Requirements for Route with Id ' || p_route_id || ' are defined at the Route level itself.');
1366 END IF;
1367 LOOP
1368 EXIT WHEN get_route_level_reqs_csr%NOTFOUND;
1369 -- Process this requirement
1370 l_index := l_index + 1;
1371 x_route_req_list(l_index).RT_OPER_MATERIAL_ID := l_rt_requirement_rec.RT_OPER_MATERIAL_ID;
1372 -- SURRKUMA :: Service Bulletin :: 07-Jun-2011
1373 -- Added condition for position based requirement
1374 IF (l_rt_requirement_rec. MC_ID IS NOT NULL AND l_rt_requirement_rec.POSITION_KEY IS NOT NULL) THEN
1375 -- Position Based requirement
1376 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1377 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Route MC:' || l_rt_requirement_rec.MC_ID || ', Visit MC: '||p_mc_id);
1378 END IF;
1379 IF (l_rt_requirement_rec.MC_ID = p_mc_id) THEN
1380 -- MC specified in route level and MC part of visit are same.
1381 x_route_req_list(l_index).MC_HEADER_ID := p_mc_header_id;
1382 x_route_req_list(l_index).POSITION_KEY := l_rt_requirement_rec.POSITION_KEY;
1383
1384 Get_Material_Req_For_Pos(
1385 p_api_version => l_api_version,
1386 x_return_status => l_return_status,
1387 x_msg_count => l_msg_count,
1388 x_msg_data => l_msg_data,
1389 p_uc_header_id => p_uc_header_id,
1390 p_visit_id => p_visit_id,
1391 p_quantity => l_rt_requirement_rec.QUANTITY,
1392 p_uom_code => l_rt_requirement_rec.UOM_CODE,
1393 p_x_index => l_index,
1394 p_x_route_mtl_reqs => x_route_req_list
1395 );
1396 END IF;
1397
1398 -- The position based requirement is not valid, so removing the record index by 1.
1399 -- This happen in two cases.
1400 -- 1. The UC's MC and Material Requirement's MC doesn't match.
1401 -- 2. The Position is not part of the MC.
1402 IF ( x_route_req_list(l_index).INVENTORY_ITEM_ID IS NULL) THEN
1403 x_route_req_list(l_index).MC_HEADER_ID := NULL;
1404 x_route_req_list(l_index).POSITION_KEY := NULL;
1405 l_index := l_index - 1;
1406 END IF;
1407
1408 ELSIF (l_rt_requirement_rec.INVENTORY_ITEM_ID IS NULL) THEN
1409 -- Item Group Requirement
1410 x_route_req_list(l_index).ITEM_GROUP_ID := l_rt_requirement_rec.ITEM_GROUP_ID;
1411 x_route_req_list(l_index).QUANTITY := l_rt_requirement_rec.QUANTITY;
1412 x_route_req_list(l_index).UOM_CODE := l_rt_requirement_rec.UOM_CODE;
1413 ELSE
1414 -- Specific Item Requirement
1415 x_route_req_list(l_index).INVENTORY_ITEM_ID := l_rt_requirement_rec.INVENTORY_ITEM_ID;
1416 x_route_req_list(l_index).INV_MASTER_ORG_ID := l_rt_requirement_rec.INVENTORY_ORG_ID;
1417 x_route_req_list(l_index).QUANTITY := l_rt_requirement_rec.PRIMARY_QUANTITY;
1418 x_route_req_list(l_index).UOM_CODE := l_rt_requirement_rec.PRIMARY_UOM_CODE;
1419 END IF;
1420
1421 -- Modified by surrkuma for Service Bulletin on 27-Jun-2011
1422 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL AND l_index > 0) THEN
1423 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Requirement ' || l_index || ': RT_OPER_MATERIAL_ID = ' || l_rt_requirement_rec.RT_OPER_MATERIAL_ID ||
1424 ', ITEM_GROUP_ID = ' || x_route_req_list(l_index).ITEM_GROUP_ID ||
1425 ', INVENTORY_ITEM_ID = ' || x_route_req_list(l_index).INVENTORY_ITEM_ID ||
1426 ', INV_MASTER_ORG_ID = ' || x_route_req_list(l_index).INV_MASTER_ORG_ID ||
1427 ', QUANTITY = ' || x_route_req_list(l_index).QUANTITY ||
1428 ', UOM_CODE = ' || x_route_req_list(l_index).UOM_CODE);
1429 END IF;
1430 -- Get the next requirement
1431 FETCH get_route_level_reqs_csr INTO l_rt_requirement_rec;
1432 END LOOP;
1433 CLOSE get_route_level_reqs_csr;
1434 ELSE
1435 -- No requirement defined at route level
1436 CLOSE get_route_level_reqs_csr;
1437 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1438 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Requirements for Route with Id ' || p_route_id ||
1439 ' are NOT defined at the Route level. Checking at the operation level.');
1440 END IF;
1441 -- AnRaj : Start for fixing perf bug 4919527
1442 -- AnRaj : Changed the code for fixing the performance bug# 4919527
1443 -- Split the query into 3 to avoid the cursor get_op_level_reqs_csr
1444 IF p_request_type = G_REQ_TYPE_FORECAST THEN
1445 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1446 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'p_request type is G_REQ_TYPE_FORECAST');
1447 END IF;
1448
1449 FOR l_op_requirement_rec IN get_op_level_reqs_forecast_csr LOOP
1450 l_index := l_index + 1;
1451 x_route_req_list(l_index).RT_OPER_MATERIAL_ID := l_op_requirement_rec.RT_OPER_MATERIAL_ID;
1452 x_route_req_list(l_index).ROUTE_OPERATION_ID := l_op_requirement_rec.ROUTE_OPERATION_ID;
1453 -- SURRKUMA :: Service Bulletin :: 07-Jun-2011
1454 -- Added condition for position based requirement
1455 IF (l_op_requirement_rec. MC_ID IS NOT NULL AND l_op_requirement_rec.POSITION_KEY IS NOT NULL) THEN
1456 -- Position Based requirement
1457 IF (l_op_requirement_rec.MC_ID = p_mc_id) THEN
1458 -- MC specified in route level and MC part of visit are same.
1459 x_route_req_list(l_index).MC_HEADER_ID := p_mc_header_id;
1460 x_route_req_list(l_index).POSITION_KEY := l_op_requirement_rec.POSITION_KEY;
1461
1462 Get_Material_Req_For_Pos(
1463 p_api_version => l_api_version,
1464 x_return_status => l_return_status,
1465 x_msg_count => l_msg_count,
1466 x_msg_data => l_msg_data,
1467 p_uc_header_id => p_uc_header_id,
1468 p_visit_id => p_visit_id,
1469 p_quantity => l_op_requirement_rec.QUANTITY,
1470 p_uom_code => l_op_requirement_rec.UOM_CODE,
1471 p_x_index => l_index,
1472 p_x_route_mtl_reqs => x_route_req_list
1473 );
1474 END IF;
1475
1476 -- The position based requirement is not valid, so removing the record index by 1.
1477 -- This happen in two cases.
1478 -- 1. The UC's MC and Material Requirement's MC doesn't match.
1479 -- 2. The Position is not part of the MC.
1480 IF ( x_route_req_list(l_index).INVENTORY_ITEM_ID IS NULL) THEN
1481 x_route_req_list(l_index).MC_HEADER_ID := NULL;
1482 x_route_req_list(l_index).POSITION_KEY := NULL;
1483 l_index := l_index - 1;
1484 END IF;
1485 ELSE
1486 x_route_req_list(l_index).ITEM_GROUP_ID := l_op_requirement_rec.ITEM_GROUP_ID;
1487 x_route_req_list(l_index).INVENTORY_ITEM_ID := l_op_requirement_rec.INVENTORY_ITEM_ID;
1488 x_route_req_list(l_index).INV_MASTER_ORG_ID := l_op_requirement_rec.INVENTORY_ORG_ID;
1489 x_route_req_list(l_index).QUANTITY := l_op_requirement_rec.PRIMARY_QUANTITY;
1490 x_route_req_list(l_index).UOM_CODE := l_op_requirement_rec.PRIMARY_UOM_CODE;
1491 END IF;
1492
1493 -- Modified by surrkuma for Service Bulletin on 27-Jun-2011
1494 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL AND l_index > 0) THEN
1495 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 ||
1496 ', ROUTE_OPERATION_ID = ' || x_route_req_list(l_index).ROUTE_OPERATION_ID ||
1497 ', ITEM_GROUP_ID = ' || x_route_req_list(l_index).ITEM_GROUP_ID ||
1498 ', INVENTORY_ITEM_ID = ' || x_route_req_list(l_index).INVENTORY_ITEM_ID ||
1499 ', INV_MASTER_ORG_ID = ' || x_route_req_list(l_index).INV_MASTER_ORG_ID ||
1500 ', QUANTITY = ' || x_route_req_list(l_index).QUANTITY ||
1501 ', UOM_CODE = ' || x_route_req_list(l_index).UOM_CODE);
1502 END IF;
1503 END LOOP;
1504 ELSIF p_request_type = G_REQ_TYPE_PLANNED THEN
1505 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1506 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'p_request type is G_REQ_TYPE_PLANNED');
1507 END IF;
1508
1509 -- Begin changes by jaramana on 22-OCT-2009 for bug 9037150
1510 -- Do a high level check for presence of operation level mtl requirements before
1511 -- attempting to get the details of the requirements to avoid performance hit
1512 OPEN op_level_planned_reqs_exist;
1513 FETCH op_level_planned_reqs_exist INTO l_dummy;
1514 IF (op_level_planned_reqs_exist%FOUND) THEN
1515 -- Operation Level Requirements seem to exist. Get these if they are effective
1516 -- and if they are applicable to the latest revision
1517 FOR l_op_requirement_rec IN get_op_level_reqs_planned_csr LOOP
1518 l_index := l_index + 1;
1519 x_route_req_list(l_index).RT_OPER_MATERIAL_ID := l_op_requirement_rec.RT_OPER_MATERIAL_ID;
1520 x_route_req_list(l_index).ROUTE_OPERATION_ID := l_op_requirement_rec.ROUTE_OPERATION_ID;
1521
1522 -- SURRKUMA :: Service Bulletin :: 07-Jun-2011
1523 -- Added condition for position based requirement
1524 IF (l_op_requirement_rec. MC_ID IS NOT NULL AND l_op_requirement_rec.POSITION_KEY IS NOT NULL) THEN
1525 -- Position Based requirement
1526 IF (l_op_requirement_rec.MC_ID = p_mc_id) THEN
1527 -- MC specified in route level and MC part of visit are same.
1528 x_route_req_list(l_index).MC_HEADER_ID := p_mc_header_id;
1529 x_route_req_list(l_index).POSITION_KEY := l_op_requirement_rec.POSITION_KEY;
1530
1531 Get_Material_Req_For_Pos(
1532 p_api_version => l_api_version,
1533 x_return_status => l_return_status,
1534 x_msg_count => l_msg_count,
1535 x_msg_data => l_msg_data,
1536 p_uc_header_id => p_uc_header_id,
1537 p_visit_id => p_visit_id,
1538 p_quantity => l_op_requirement_rec.QUANTITY,
1539 p_uom_code => l_op_requirement_rec.UOM_CODE,
1540 p_x_index => l_index,
1541 p_x_route_mtl_reqs => x_route_req_list
1542 );
1543 END IF;
1544
1545 -- The position based requirement is not valid, so removing the record index by 1.
1546 -- This happen in two cases.
1547 -- 1. The UC's MC and Material Requirement's MC doesn't match.
1548 -- 2. The Position is not part of the MC.
1549 IF ( x_route_req_list(l_index).INVENTORY_ITEM_ID IS NULL) THEN
1550 x_route_req_list(l_index).MC_HEADER_ID := NULL;
1551 x_route_req_list(l_index).POSITION_KEY := NULL;
1552 l_index := l_index - 1;
1553 END IF;
1554 ELSE
1555 x_route_req_list(l_index).ITEM_GROUP_ID := l_op_requirement_rec.ITEM_GROUP_ID;
1556 x_route_req_list(l_index).INVENTORY_ITEM_ID := l_op_requirement_rec.INVENTORY_ITEM_ID;
1557 x_route_req_list(l_index).INV_MASTER_ORG_ID := l_op_requirement_rec.INVENTORY_ORG_ID;
1558 x_route_req_list(l_index).QUANTITY := l_op_requirement_rec.PRIMARY_QUANTITY;
1559 x_route_req_list(l_index).UOM_CODE := l_op_requirement_rec.PRIMARY_UOM_CODE;
1560 END IF;
1561 -- Modified by surrkuma for Service Bulletin on 27-Jun-2011
1562 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL AND l_index > 0) THEN
1563 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 ||
1564 ', ROUTE_OPERATION_ID = ' || x_route_req_list(l_index).ROUTE_OPERATION_ID ||
1565 ', ITEM_GROUP_ID = ' || x_route_req_list(l_index).ITEM_GROUP_ID ||
1566 ', INVENTORY_ITEM_ID = ' || x_route_req_list(l_index).INVENTORY_ITEM_ID ||
1567 ', INV_MASTER_ORG_ID = ' || x_route_req_list(l_index).INV_MASTER_ORG_ID ||
1568 ', QUANTITY = ' || x_route_req_list(l_index).QUANTITY ||
1569 ', UOM_CODE = ' || x_route_req_list(l_index).UOM_CODE);
1570 END IF;
1571
1572 END LOOP;
1573 END IF;
1574 CLOSE op_level_planned_reqs_exist;
1575 -- End changes by jaramana on 22-OCT-2009 for bug 9037150
1576
1577 -- Not too sure whether the value of p_request_type can be anything other than G_REQ_TYPE_FORECAST and G_REQ_TYPE_PLANNED
1578 -- Adding this part for safety purpose, so that no records are missed out
1579 ELSE
1580 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1581 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'p_request type is neither G_REQ_TYPE_PLANNED nor G_REQ_TYPE_FORECAST');
1582 END IF;
1583
1584 FOR l_op_requirement_rec IN l_op_requirement_neither_csr LOOP
1585 l_index := l_index + 1;
1586 x_route_req_list(l_index).RT_OPER_MATERIAL_ID := l_op_requirement_rec.RT_OPER_MATERIAL_ID;
1587 x_route_req_list(l_index).ROUTE_OPERATION_ID := l_op_requirement_rec.ROUTE_OPERATION_ID;
1588
1589 -- SURRKUMA :: Service Bulletin :: 07-Jun-2011
1590 -- Added condition for position based requirement
1591 IF (l_op_requirement_rec. MC_ID IS NOT NULL AND l_op_requirement_rec.POSITION_KEY IS NOT NULL) THEN
1592 -- Position Based requirement
1593 IF (l_op_requirement_rec.MC_ID = p_mc_id) THEN
1594 -- MC specified in route level and MC part of visit are same.
1595 x_route_req_list(l_index).MC_HEADER_ID := p_mc_header_id;
1596 x_route_req_list(l_index).POSITION_KEY := l_op_requirement_rec.POSITION_KEY;
1597
1598 Get_Material_Req_For_Pos(
1599 p_api_version => l_api_version,
1600 x_return_status => l_return_status,
1601 x_msg_count => l_msg_count,
1602 x_msg_data => l_msg_data,
1603 p_uc_header_id => p_uc_header_id,
1604 p_visit_id => p_visit_id,
1605 p_quantity => l_op_requirement_rec.QUANTITY,
1606 p_uom_code => l_op_requirement_rec.UOM_CODE,
1607 p_x_index => l_index,
1608 p_x_route_mtl_reqs => x_route_req_list
1609 );
1610 END IF;
1611
1612 -- The position based requirement is not valid, so removing the record index by 1.
1613 -- This happen in two cases.
1614 -- 1. The UC's MC and Material Requirement's MC doesn't match.
1615 -- 2. The Position is not part of the MC.
1616 IF ( x_route_req_list(l_index).INVENTORY_ITEM_ID IS NULL) THEN
1617 x_route_req_list(l_index).MC_HEADER_ID := NULL;
1618 x_route_req_list(l_index).POSITION_KEY := NULL;
1619 l_index := l_index - 1;
1620 END IF;
1621 ELSE
1622 x_route_req_list(l_index).ITEM_GROUP_ID := l_op_requirement_rec.ITEM_GROUP_ID;
1623 x_route_req_list(l_index).INVENTORY_ITEM_ID := l_op_requirement_rec.INVENTORY_ITEM_ID;
1624 x_route_req_list(l_index).INV_MASTER_ORG_ID := l_op_requirement_rec.INVENTORY_ORG_ID;
1625 x_route_req_list(l_index).QUANTITY := l_op_requirement_rec.QUANTITY;
1626 x_route_req_list(l_index).UOM_CODE := l_op_requirement_rec.UOM_CODE;
1627 END IF;
1628
1629 -- Modified by surrkuma for Service Bulletin on 27-Jun-2011
1630 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL AND l_index > 0) THEN
1631 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 ||
1632 ', ROUTE_OPERATION_ID = ' || x_route_req_list(l_index).ROUTE_OPERATION_ID ||
1633 ', ITEM_GROUP_ID = ' || x_route_req_list(l_index).ITEM_GROUP_ID ||
1634 ', INVENTORY_ITEM_ID = ' || x_route_req_list(l_index).INVENTORY_ITEM_ID ||
1635 ', INV_MASTER_ORG_ID = ' || x_route_req_list(l_index).INV_MASTER_ORG_ID ||
1636 ', QUANTITY = ' || x_route_req_list(l_index).QUANTITY ||
1637 ', UOM_CODE = ' || x_route_req_list(l_index).UOM_CODE);
1638 END IF;
1639
1640 END LOOP;
1641 END IF; -- request_type check
1642
1643 -- Check operation level
1644 /* FOR l_op_requirement_rec IN get_op_level_reqs_csr LOOP
1645 l_index := l_index + 1;
1646 x_route_req_list(l_index).RT_OPER_MATERIAL_ID := l_op_requirement_rec.RT_OPER_MATERIAL_ID;
1647 x_route_req_list(l_index).ROUTE_OPERATION_ID := l_op_requirement_rec.ROUTE_OPERATION_ID;
1648 x_route_req_list(l_index).ITEM_GROUP_ID := l_op_requirement_rec.ITEM_GROUP_ID;
1649 x_route_req_list(l_index).INVENTORY_ITEM_ID := l_op_requirement_rec.INVENTORY_ITEM_ID;
1650 x_route_req_list(l_index).INV_MASTER_ORG_ID := l_op_requirement_rec.INVENTORY_ORG_ID;
1651 x_route_req_list(l_index).QUANTITY := l_op_requirement_rec.QUANTITY;
1652 x_route_req_list(l_index).UOM_CODE := l_op_requirement_rec.UOM_CODE;
1653 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1654 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 ||
1655 ', ROUTE_OPERATION_ID = ' || x_route_req_list(l_index).ROUTE_OPERATION_ID ||
1656 ', ITEM_GROUP_ID = ' || x_route_req_list(l_index).ITEM_GROUP_ID ||
1657 ', INVENTORY_ITEM_ID = ' || x_route_req_list(l_index).INVENTORY_ITEM_ID ||
1658 ', INV_MASTER_ORG_ID = ' || x_route_req_list(l_index).INV_MASTER_ORG_ID ||
1659 ', QUANTITY = ' || x_route_req_list(l_index).QUANTITY ||
1660 ', UOM_CODE = ' || x_route_req_list(l_index).UOM_CODE);
1661 END IF;
1662 END LOOP;
1663 */
1664 -- AnRaj : End for fixing perf bug 4919527
1665 END IF;
1666 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1667 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Total number of Route requirements: ' || l_index);
1668 END IF;
1669 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1670 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1671 END IF;
1672
1673 END Get_Route_Requirements;
1674
1675 ------------------------------------------------------------------------
1676 -- This Procedure validates a Position Path against a given unit and date.
1677 -- It returns a status flag and if valid, it get the details about the position.
1678 ------------------------------------------------------------------------
1679 PROCEDURE Validate_Path_Position
1680 (
1681 p_path_position_id IN NUMBER,
1682 p_unit_instance_id IN NUMBER,
1683 p_requirement_date IN DATE,
1684 x_valid_flag OUT NOCOPY VARCHAR2,
1685 x_relationship_id OUT NOCOPY NUMBER,
1686 x_item_group_id OUT NOCOPY NUMBER,
1687 x_pos_instance_id OUT NOCOPY NUMBER) IS
1688
1689 CURSOR get_pos_path_dtls_csr IS
1690 SELECT position_key
1691 FROM AHL_MC_PATH_POSITION_NODES
1692 WHERE PATH_POSITION_ID = p_path_position_id AND
1693 SEQUENCE = (SELECT MAX(SEQUENCE) FROM AHL_MC_PATH_POSITION_NODES
1694 WHERE PATH_POSITION_ID = p_path_position_id);
1695
1696 CURSOR get_position_dtls_csr(c_pos_key IN NUMBER,
1697 c_mc_header_id IN NUMBER) IS
1698 SELECT relationship_id, item_group_id
1699 FROM AHL_MC_RELATIONSHIPS
1700 WHERE POSITION_KEY = c_pos_key AND
1701 MC_HEADER_ID = c_mc_header_id;
1702
1703 CURSOR get_config_dtls_csr(c_unit_instance_id IN NUMBER) IS
1704 SELECT master_config_id
1705 FROM ahl_unit_config_headers
1706 WHERE CSI_ITEM_INSTANCE_ID = c_unit_instance_id AND
1707 NVL(ACTIVE_START_DATE, SYSDATE - 1) <= SYSDATE AND
1708 NVL(ACTIVE_END_DATE, SYSDATE + 1) > SYSDATE;
1709
1710 CURSOR get_all_position_dates_csr(c_start_pos_key IN NUMBER,
1711 c_mc_header_id IN NUMBER) IS
1712 SELECT ACTIVE_START_DATE,
1713 ACTIVE_END_DATE
1714 FROM AHL_MC_RELATIONSHIPS
1715 START WITH POSITION_KEY = c_start_pos_key AND
1716 MC_HEADER_ID = c_mc_header_id
1717 CONNECT BY RELATIONSHIP_ID = PRIOR PARENT_RELATIONSHIP_ID;
1718
1719 CURSOR get_ii_position_csr(c_start_instance_id IN NUMBER) IS
1720 SELECT II.OBJECT_ID,
1721 II.SUBJECT_ID,
1722 REL.RELATIONSHIP_ID,
1723 REL.ACTIVE_START_DATE,
1724 REL.ACTIVE_END_DATE
1725 FROM CSI_II_RELATIONSHIPS II, AHL_MC_RELATIONSHIPS REL
1726 WHERE NVL(II.ACTIVE_START_DATE, SYSDATE - 1) <= SYSDATE AND
1727 NVL(II.ACTIVE_END_DATE, SYSDATE + 1) > SYSDATE AND
1728 II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF' AND
1729 REL.RELATIONSHIP_ID =TO_NUMBER(II.POSITION_REFERENCE)
1730 AND II.RELATIONSHIP_ID IN
1731 (SELECT RELATIONSHIP_ID
1732 FROM CSI_II_RELATIONSHIPS
1733 START WITH SUBJECT_ID = c_start_instance_id AND
1734 NVL(ACTIVE_START_DATE, SYSDATE - 1) <= SYSDATE AND
1735 NVL(ACTIVE_END_DATE, SYSDATE + 1) > SYSDATE AND
1736 RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
1737 CONNECT BY SUBJECT_ID = PRIOR OBJECT_ID AND
1738 NVL(ACTIVE_START_DATE, SYSDATE - 1) <= SYSDATE AND
1739 NVL(ACTIVE_END_DATE, SYSDATE + 1) > SYSDATE AND
1740 RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF');
1741
1742 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Validate_Path_Position';
1743 l_return_status VARCHAR2(1);
1744 l_msg_count NUMBER;
1745 l_msg_data VARCHAR2(1000);
1746 l_installed_inst_id NUMBER;
1747 l_lowest_unit_inst_id NUMBER;
1748 l_lowest_mc_header_id NUMBER;
1749 l_mapping_status VARCHAR2(30);
1750 l_position_key AHL_MC_PATH_POSITION_NODES.POSITION_KEY%TYPE;
1751 l_last_instance_id NUMBER;
1752 l_requirement_date DATE := p_requirement_date;
1753
1754 BEGIN
1755
1756 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1757 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1758 END IF;
1759
1760 IF (G_LEVEL_EVENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1761 FND_LOG.STRING(G_LEVEL_EVENT, L_DEBUG_KEY, 'About to call AHL_MC_PATH_POSITION_PVT.Get_Pos_Instance with ' ||
1762 ' p_position_id = ' || p_path_position_id ||
1763 ' p_csi_item_instance_id = ' || p_unit_instance_id);
1764 END IF;
1765
1766 AHL_MC_PATH_POSITION_PVT.Get_Pos_Instance(p_api_version => 1.0,
1767 p_init_msg_list => FND_API.G_FALSE,
1768 p_commit => FND_API.G_FALSE,
1769 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1770 x_return_status => l_return_status,
1771 x_msg_count => l_msg_count,
1772 x_msg_data => l_msg_data,
1773 p_position_id => p_path_position_id,
1774 p_csi_item_instance_id => p_unit_instance_id,
1775 x_item_instance_id => l_installed_inst_id,
1776 x_lowest_uc_csi_id => l_lowest_unit_inst_id,
1777 x_mapping_status => l_mapping_status);
1778
1779 IF (G_LEVEL_EVENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1780 FND_LOG.STRING(G_LEVEL_EVENT, L_DEBUG_KEY, 'Returned from call to AHL_MC_PATH_POSITION_PVT.Get_Pos_Instance:' ||
1781 ' x_return_status = ' || l_return_status ||
1782 ', x_mapping_status = ' || l_mapping_status ||
1783 ', x_item_instance_id = ' || l_installed_inst_id ||
1784 ', x_lowest_uc_csi_id = ' || l_lowest_unit_inst_id);
1785 END IF;
1786
1787 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1788 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1789 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1790 RAISE FND_API.G_EXC_ERROR;
1791 END IF;
1792
1793 IF(l_mapping_status = G_MAPPING_STATUS_NA) THEN
1794 -- Position does not apply to current unit
1795 x_valid_flag := FND_API.G_FALSE;
1796 RETURN;
1797 END IF;
1798
1799 OPEN get_pos_path_dtls_csr;
1800 FETCH get_pos_path_dtls_csr INTO l_position_key;
1801 CLOSE get_pos_path_dtls_csr;
1802
1803 OPEN get_config_dtls_csr(c_unit_instance_id => l_lowest_unit_inst_id);
1804 FETCH get_config_dtls_csr INTO l_lowest_mc_header_id;
1805 CLOSE get_config_dtls_csr;
1806
1807 IF l_requirement_date IS NULL THEN
1808 l_requirement_date := SYSDATE;
1809 END IF;
1810
1811 -- Check if the Position is valid on the requirement date
1812 -- by traversing the tree from the position up to the unit's root node.
1813 IF (l_mapping_status = G_MAPPING_STATUS_EMPTY) THEN
1814 -- If the position is empty, do a separate traversal from
1815 -- the position up to l_lowest_unit_inst_id first.
1816 FOR position_dates_rec IN get_all_position_dates_csr(l_position_key, l_lowest_mc_header_id) LOOP
1817 IF ((position_dates_rec.ACTIVE_START_DATE IS NOT NULL AND
1818 position_dates_rec.ACTIVE_START_DATE > l_requirement_date) OR
1819 (position_dates_rec.ACTIVE_END_DATE IS NOT NULL AND
1820 position_dates_rec.ACTIVE_END_DATE <= l_requirement_date)) THEN
1821 -- Position is not valid on the requirement date
1822 x_valid_flag := FND_API.G_FALSE;
1823 RETURN;
1824 END IF;
1825 END LOOP;
1826 l_last_instance_id := l_lowest_unit_inst_id;
1827 ELSE
1828 -- Position is not empty
1829 l_last_instance_id := l_installed_inst_id;
1830 END IF;
1831
1832 IF (l_last_instance_id <> p_unit_instance_id) THEN
1833 -- Now traverse up the instance tree to validate positions.
1834 FOR position_rec IN get_ii_position_csr(l_last_instance_id) LOOP
1835 -- Check if the position is valid in the last mc
1836 IF ((position_rec.ACTIVE_START_DATE IS NOT NULL AND
1837 position_rec.ACTIVE_START_DATE > l_requirement_date) OR
1838 (position_rec.ACTIVE_END_DATE IS NOT NULL AND
1839 position_rec.ACTIVE_END_DATE <= l_requirement_date)) THEN
1840 -- Position is not valid on the requirement date
1841 x_valid_flag := FND_API.G_FALSE;
1842 RETURN;
1843 END IF;
1844 END LOOP;
1845 END IF;
1846
1847 -- The Position hierarchy is valid on the requirement Date
1848 x_valid_flag := FND_API.G_TRUE;
1849
1850 -- Information available: l_position_key, l_lowest_mc_header_id, l_mapping_status, l_installed_inst_id
1851 -- Get the relationship id and the item group id for the position
1852 OPEN get_position_dtls_csr(c_pos_key => l_position_key,
1853 c_mc_header_id => l_lowest_mc_header_id);
1854 FETCH get_position_dtls_csr INTO x_relationship_id, x_item_group_id;
1855 CLOSE get_position_dtls_csr;
1856 IF (l_mapping_status = G_MAPPING_STATUS_MATCH) THEN
1857 x_pos_instance_id := l_installed_inst_id;
1858 ELSE
1859 x_pos_instance_id := NULL;
1860 END IF;
1861
1862 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1863 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1864 END IF;
1865
1866 END Validate_Path_Position;
1867
1868
1869 -------------------------------------
1870 -- End Local Procedure Definitions --
1871 -------------------------------------
1872 ---------------------------------------
1873 -- Local Function Definitions follow --
1874 ---------------------------------------
1875 -----------------------------------------------
1876 -- This Function gets the unit's instance id --
1877 -- If p_item_instance_id does not belong to an UC (stand-alone or IB Tree), it
1878 -- returns null. If p_item_instance_id itself is a UC's top node, it returns itself.
1879 -----------------------------------------------
1880
1881 FUNCTION Get_Unit_Instance(
1882 p_item_instance_id IN NUMBER) RETURN NUMBER IS
1883
1884 CURSOR chk_inst_is_unit_csr IS
1885 SELECT 'x'
1886 FROM ahl_unit_config_headers
1887 WHERE csi_item_instance_id = p_item_instance_id
1888 AND nvl(active_end_date, SYSDATE+1) > SYSDATE
1889 AND nvl(active_start_date, SYSDATE) <= SYSDATE;
1890
1891 CURSOR get_parent_instance_csr IS
1892 SELECT object_id
1893 FROM csi_ii_relationships
1894 WHERE object_id IN (SELECT csi_item_instance_id
1895 FROM ahl_unit_config_headers
1896 WHERE nvl(active_end_date, SYSDATE+1) > SYSDATE
1897 AND nvl(active_start_date, SYSDATE) <= SYSDATE)
1898 START WITH subject_id = p_item_instance_id
1899 AND relationship_type_code = 'COMPONENT-OF'
1900 AND nvl(active_start_date, SYSDATE) <= SYSDATE
1901 AND nvl(active_end_date, SYSDATE+1) > SYSDATE
1902 CONNECT BY subject_id = PRIOR object_id
1903 AND relationship_type_code = 'COMPONENT-OF'
1904 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
1905 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
1906 ORDER BY LEVEL;
1907
1908 l_instance_id NUMBER := null;
1909 l_dummy VARCHAR2(1);
1910
1911 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Get_Unit_Instance';
1912
1913 BEGIN
1914 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1915 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Function');
1916 END IF;
1917
1918 IF(p_item_instance_id IS NULL) THEN
1919 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1920 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'p_item_instance_id is null. So, returning null.');
1921 END IF;
1922 l_instance_id := null;
1923 ELSE
1924 OPEN chk_inst_is_unit_csr;
1925 FETCH chk_inst_is_unit_csr INTO l_dummy;
1926 IF (chk_inst_is_unit_csr%FOUND) THEN
1927 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1928 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'p_item_instance_id (' || p_item_instance_id || ') itself is a unit.');
1929 END IF;
1930 l_instance_id := p_item_instance_id;
1931 ELSE
1932 OPEN get_parent_instance_csr;
1933 FETCH get_parent_instance_csr INTO l_instance_id;
1934 CLOSE get_parent_instance_csr;
1935 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1936 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Unit Instance of ' || p_item_instance_id || ' is ' || l_instance_id);
1937 END IF;
1938 END IF;
1939 CLOSE chk_inst_is_unit_csr;
1940 END IF;
1941
1942 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1943 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Function');
1944 END IF;
1945 RETURN l_instance_id;
1946
1947 END Get_Unit_Instance;
1948
1949 -- Start of Comments --
1950 -- Procedure name : Get_Material_Req_For_Pos
1951 -- Type : Private
1952 -- Function : Gets the Material requirement for a position specified in
1953 -- Route/Operation level requriements
1954 -- Pre-reqs :
1955 -- Parameters :
1956 --
1957 -- Standard IN Parameters :
1958 -- p_api_version IN NUMBER Required
1959 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1960 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1961 --
1962 -- Standard OUT Parameters :
1963 -- x_return_status OUT VARCHAR2 Required
1964 -- x_msg_count OUT NUMBER Required
1965 -- x_msg_data OUT VARCHAR2 Required
1966 --
1967 -- Get_Material_Req_For_Pos Parameters:
1968 -- p_uc_header_id IN NUMBER Required
1969 -- Unit Configuration Header ID of the Visit's Unit
1970 -- p_visit_id IN NUMBER Not Requried when p_visit_id is NULL
1971 -- The Id of the Visit for which material requirements calculated.
1972 -- p_quantity IN NUMBER Not Required when p_quantity is NULL
1973 -- Requested Quanity.
1974 -- p_uom_code IN VARCHAR2 Not Required when p_uom_code is NULL
1975 -- Unit of Measure of the defined at requirement request level.
1976 -- p_x_index IN OUT NUMBER Not Required
1977 -- p_x_route_mtl_reqs material requirements table index
1978 -- p_x_route_mtl_reqs IN OUT Route_Mtl_Req_Tbl_Type Required
1979 -- The table containing the route based material requirements.
1980 --
1981 -- Version :
1982 -- Initial Version 1.0
1983 -- Modified by surrkuma on 29-Jul-2011
1984 -- Changed the method signature to support control position based
1985 -- Material requirements
1986 --
1987 -- End of Comments.
1988
1989 PROCEDURE Get_Material_Req_For_Pos (
1990 p_api_version IN NUMBER,
1991 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1992 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1993 x_return_status OUT NOCOPY VARCHAR2,
1994 x_msg_count OUT NOCOPY NUMBER,
1995 x_msg_data OUT NOCOPY VARCHAR2,
1996 p_uc_header_id IN NUMBER,
1997 p_visit_id IN NUMBER := NULL,
1998 p_quantity IN NUMBER := NULL,
1999 p_uom_code IN VARCHAR2 := NULL,
2000 p_x_index IN OUT NOCOPY NUMBER,
2001 p_x_route_mtl_reqs IN OUT NOCOPY Route_Mtl_Req_Tbl_Type
2002 ) IS
2003
2004 -- Cursor get the MC relation ship details for given mc and position
2005 CURSOR get_mc_relationship_detail_csr (c_mc_header_id NUMBER,
2006 c_position_key NUMBER) IS
2007 SELECT relationship_id,
2008 item_group_id,
2009 quantity,
2010 uom_code
2011 FROM ahl_mc_relationships
2012 WHERE mc_header_id = c_mc_header_id
2013 AND position_key = c_position_key;
2014
2015 -- cursor to get applicable SB rules for the given unit and position
2016 -- NOTE::Fetch only once from this cursor. UNION ALL is fine as it will be fetched only once.
2017 CURSOR get_appl_sb_rules_csr (c_uc_header_id NUMBER,
2018 c_relationship_id NUMBER,
2019 c_visit_id NUMBER) IS
2020 SELECT DISTINCT rule_id,
2021 rule_sequence
2022 FROM (
2023 SELECT SB.rule_id,
2024 SB.rule_sequence
2025 FROM ahl_sb_position_rules SB,
2026 ahl_unit_config_headers UC
2027 WHERE UC.unit_config_header_id = c_uc_header_id
2028 AND SB.mc_header_id = UC.master_config_id
2029 -- take only the rules which have MRs accomplished on the unit
2030 AND EXISTS (SELECT 'X'
2031 FROM ahl_unit_effectivities_b UE
2032 WHERE UE.mr_header_id = SB.mr_header_id
2033 AND UE.csi_item_instance_id = UC.csi_item_instance_id -- SB MRs are effective on root node
2034 AND UE.accomplished_date IS NOT NULL)
2035 -- take only the MAX sequence rules for a position, among the accomplished MRs
2036 AND SB.rule_sequence = (SELECT MAX(SB2.rule_sequence)
2037 FROM ahl_sb_position_rules SB2
2038 WHERE SB2.relationship_id = SB.relationship_id
2039 AND EXISTS (SELECT 'X'
2040 FROM ahl_unit_effectivities_b UE
2041 WHERE UE.mr_header_id = SB2.mr_header_id
2042 AND UE.csi_item_instance_id = UC.csi_item_instance_id
2043 AND UE.accomplished_date IS NOT NULL))
2044 AND SB.relationship_id = c_relationship_id
2045 AND TRUNC(NVL(SB.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
2046 AND TRUNC(NVL(SB.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE)
2047 UNION ALL
2048 SELECT SB.rule_id,
2049 SB.rule_sequence
2050 FROM AHL_SB_POSITION_RULES SB,
2051 AHL_VISIT_TASKS_B VT,
2052 AHL_UNIT_CONFIG_HEADERS UC
2053 WHERE UC.unit_config_header_id = c_uc_header_id
2054 AND SB.mc_header_id = UC.master_config_id
2055 -- take only the rules which have MRs attached to the visit
2056 AND VT.visit_id = c_visit_id
2057 AND VT.mr_id = SB.mr_header_id
2058 AND VT.status_code NOT IN ('CANCELLED', 'DELETED')
2059 -- take only the MAX sequence rules for a position
2060 AND SB.rule_sequence = (SELECT MAX(SB2.rule_sequence)
2061 FROM AHL_SB_POSITION_RULES SB2
2062 WHERE SB2.relationship_id = SB.relationship_id
2063 AND SB2.mr_header_id = SB.mr_header_id)
2064 AND SB.relationship_id = c_relationship_id
2065 AND TRUNC(NVL(SB.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
2066 AND TRUNC(NVL(SB.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE)
2067 ) ORDER BY rule_sequence DESC;
2068
2069 -- Cursor to get allowable sub configuration item group ids for the given position
2070 CURSOR get_sub_config_item_group(c_relationship_id IN NUMBER) IS
2071 SELECT mcr.item_group_id
2072 FROM ahl_mc_relationships mcr, ahl_mc_config_relations smc
2073 WHERE mcr.mc_header_id = smc.mc_header_id
2074 AND mcr.parent_relationship_id is null
2075 AND smc.relationship_id = c_relationship_id
2076 ORDER BY smc.priority;
2077
2078 -- Cursor to get Visit Org Id from the visit.
2079 CURSOR get_visit_org_id_cur(c_visit_id IN NUMBER)IS
2080 SELECT organization_id
2081 FROM ahl_visits_b
2082 WHERE visit_id = C_VISIT_ID
2083 AND ( organization_id IS NOT NULL
2084 OR start_date_time IS NOT NULL
2085 OR department_id IS NOT NULL);
2086
2087 -- Added by SURRKUMA for Service Bulletin on 26-Jul-2011
2088 -- Use to following cursor to check for controlled position with total quantity mc rule.
2089 CURSOR control_position_check_csr(c_mc_header_id IN NUMBER,
2090 c_position_key IN NUMBER,
2091 c_quantity IN NUMBER) IS
2092 SELECT rel.relationship_id, NVL(c_quantity, mcrs.object_attribute1)
2093 FROM ahl_mc_path_position_nodes mcpp,
2094 ahl_mc_rule_statements mcrs,
2095 ahl_mc_headers_b mch,
2096 ahl_mc_relationships rel,
2097 ahl_mc_rules_b mcr
2098 WHERE mch.mc_header_id = c_mc_header_id
2099 AND mcpp.mc_id = mch.mc_id
2100 AND mch.version_number = NVL(mcpp.version_number, mch.version_number)
2101 AND mcpp.position_key = c_position_key
2102 AND mcrs.subject_id = mcpp.path_position_id
2103 AND mcrs.subject_type = 'POSITION'
2104 AND mcrs.operator IN ('MUST_HAVE', 'HAVE')
2105 AND mcrs.object_type = 'TOT_CHILD_QUANTITY'
2106 AND rel.mc_header_id = c_mc_header_id
2107 AND rel.position_key = mcpp.position_key
2108 AND mch.config_status_code = 'COMPLETE'
2109 AND NVL(rel.active_end_date, SYSDATE + 1) > SYSDATE
2110 AND mcr.rule_id = mcrs.rule_id
2111 AND mcr.rule_type_code = 'MANDATORY'
2112 AND NVL(mcr.active_start_date, SYSDATE - 1) < SYSDATE
2113 AND NVL(mcr.active_end_date, SYSDATE + 1) > SYSDATE;
2114
2115 -- Added by SURRKUMA for Service Bulletin on 26-Jul-2011
2116 -- Use the followig cursor to get the child positions of a parent position.
2117 CURSOR get_child_positions_csr(c_relationship_id IN NUMBER) IS
2118 SELECT mcr.position_key
2119 FROM ahl_mc_relationships mcr
2120 WHERE mcr.parent_relationship_id = c_relationship_id
2121 AND NVL(mcr.active_end_date, SYSDATE + 1) > SYSDATE
2122 ORDER BY display_order;
2123
2124 l_api_name CONSTANT VARCHAR2(30) := 'Get_Material_Req_For_Pos';
2125 l_api_version CONSTANT NUMBER := 1.0;
2126 l_mc_header_id CONSTANT NUMBER := p_x_route_mtl_reqs(p_x_index).mc_header_id;
2127 l_rt_oper_mtl_id CONSTANT NUMBER := p_x_route_mtl_reqs(p_x_index).rt_oper_material_id;
2128 l_return_status VARCHAR2(1);
2129 l_msg_count NUMBER;
2130 l_relationship_id NUMBER;
2131 l_sb_rule_id NUMBER;
2132 l_pos_item_group_id NUMBER;
2133 l_pos_quantity NUMBER;
2134 l_pos_uom_code VARCHAR2(3);
2135 l_ig_quantity NUMBER;
2136 l_ig_uom_code VARCHAR2(3);
2137 l_rt_quantity NUMBER := p_quantity;
2138 l_uom_code VARCHAR2(3);
2139 l_quantity NUMBER;
2140 l_visit_org_id NUMBER;
2141 l_dummy NUMBER;
2142 l_cp_relationship_id NUMBER;
2143 l_remaining_qty NUMBER := 0;
2144 l_cp_uom VARCHAR2(3) := p_uom_code;
2145 l_child_position_key NUMBER;
2146
2147 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Get_Material_Req_For_Pos';
2148 BEGIN
2149 --Initialize API return status to success
2150 x_return_status := FND_API.G_RET_STS_SUCCESS;
2151
2152 --Standard Start of API savepoint
2153 SAVEPOINT get_material_req_for_pos;
2154
2155 --Standard call to check for call compatibility.
2156 IF NOT FND_API.compatible_api_call(
2157 l_api_version,
2158 p_api_version,
2159 l_api_name,
2160 G_PKG_NAME)
2161 THEN
2162 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2163 END IF;
2164
2165 --Initialize message list if p_init_msg_list is set to TRUE.
2166 IF FND_API.to_boolean( p_init_msg_list ) THEN
2167 FND_MSG_PUB.initialize;
2168 END IF;
2169
2170 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2171 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2172 END IF;
2173
2174 -- Getting the Org Id for given visit
2175 OPEN get_visit_org_id_cur(p_visit_id);
2176 FETCH get_visit_org_id_cur INTO l_visit_org_id;
2177 CLOSE get_visit_org_id_cur;
2178
2179 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2180 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'mc_header_id: ' || l_mc_header_id ||
2181 ', mc_pos_key: ' || p_x_route_mtl_reqs(p_x_index).position_key ||
2182 ', uc_header_id: '|| p_uc_header_id ||
2183 ', visit_id: '|| p_visit_id ||
2184 ', visit_org_id: '|| l_visit_org_id ||
2185 ', quantity: '|| l_rt_quantity ||
2186 ', uom_code: '|| p_uom_code);
2187 END IF;
2188
2189 -- Checking whether the position is control position or not.
2190 OPEN control_position_check_csr(l_mc_header_id, p_x_route_mtl_reqs(p_x_index).position_key, l_rt_quantity);
2191 FETCH control_position_check_csr INTO l_cp_relationship_id, l_remaining_qty;
2192 -- If position is control position then use the derived quantity (l_remaining_qty).
2193 -- Else use the quantity defined at route level.
2194 IF (control_position_check_csr%FOUND) THEN
2195 l_rt_quantity := NULL;
2196 ELSE
2197 l_remaining_qty := l_rt_quantity;
2198 END IF;
2199 CLOSE control_position_check_csr;
2200
2201 -- Get the child positions for the control position.
2202 OPEN get_child_positions_csr(l_cp_relationship_id);
2203 FETCH get_child_positions_csr INTO l_child_position_key;
2204 IF (get_child_positions_csr%FOUND) THEN
2205 p_x_route_mtl_reqs(p_x_index).position_key := l_child_position_key;
2206 END IF;
2207 LOOP
2208 OPEN get_mc_relationship_detail_csr(l_mc_header_id, p_x_route_mtl_reqs(p_x_index).position_key);
2209 FETCH get_mc_relationship_detail_csr INTO l_relationship_id, l_pos_item_group_id, l_pos_quantity, l_pos_uom_code;
2210 CLOSE get_mc_relationship_detail_csr;
2211
2212 IF (l_relationship_id IS NOT NULL) THEN
2213 -- Position is part of given MC
2214
2215 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2216 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Position Key: ' || p_x_route_mtl_reqs(p_x_index).position_key ||
2217 ', Remaining Qty: ' || l_remaining_qty ||
2218 ', l_relationship_id: ' || l_relationship_id ||
2219 ', l_pos_item_group_id: ' || l_pos_item_group_id);
2220 END IF;
2221
2222 -- Gets Last accomplised SB rule id.
2223 OPEN get_appl_sb_rules_csr (p_uc_header_id, l_relationship_id, p_visit_id);
2224 FETCH get_appl_sb_rules_csr INTO l_sb_rule_id, l_dummy;
2225 CLOSE get_appl_sb_rules_csr;
2226
2227 p_x_route_mtl_reqs(p_x_index).relationship_id := l_relationship_id;
2228
2229 -- Deriving the item from the highest priority Sub Config's item group
2230 -- associated to the position for which requirement is created.
2231 -- IF SB rule defined for the position then derive the item from the highest
2232 -- priority Sub Config's item group which has item satifies the SB rule.
2233 FOR l_sc_item_group_id IN get_sub_config_item_group(l_relationship_id) LOOP
2234 EXIT WHEN p_x_route_mtl_reqs(p_x_index).inventory_item_id IS NOT NULL;
2235
2236 Get_Item_Group_Item(
2237 p_item_group_id => l_sc_item_group_id.item_group_id,
2238 p_rule_id => l_sb_rule_id,
2239 p_visit_org_id => l_visit_org_id,
2240 x_inventory_item_id => p_x_route_mtl_reqs(p_x_index).inventory_item_id,
2241 x_inventory_org_id => p_x_route_mtl_reqs(p_x_index).inv_master_org_id,
2242 x_quantity => l_ig_quantity,
2243 x_uom_code => l_ig_uom_code);
2244
2245 END LOOP;
2246
2247 -- If no Sub Config defined for the position or the Sub Configs doesn't have an item
2248 -- which satisfies SB rule then derive the item from the position level item group.
2249 IF p_x_route_mtl_reqs(p_x_index).inventory_item_id IS NULL AND l_pos_item_group_id IS NOT NULL THEN
2250
2251 Get_Item_Group_Item(
2252 p_item_group_id => l_pos_item_group_id,
2253 p_rule_id => l_sb_rule_id,
2254 p_visit_org_id => l_visit_org_id,
2255 x_inventory_item_id => p_x_route_mtl_reqs(p_x_index).inventory_item_id,
2256 x_inventory_org_id => p_x_route_mtl_reqs(p_x_index).inv_master_org_id,
2257 x_quantity => l_ig_quantity,
2258 x_uom_code => l_ig_uom_code);
2259
2260 END IF;
2261
2262 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2263 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'inventory_item_id: ' || p_x_route_mtl_reqs(p_x_index).inventory_item_id ||
2264 ', inv_master_org_id: ' || p_x_route_mtl_reqs(p_x_index).inv_master_org_id);
2265 END IF;
2266
2267 IF p_x_route_mtl_reqs(p_x_index).inventory_item_id IS NOT NULL THEN
2268 -- If quantity and uom code defined at requirement level then convert those to
2269 -- primary quantity and primary uom code.
2270 -- Else if quantity and uom code is defined at item group level then convert
2271 -- those to primary quantity and primary uom code.
2272 -- Else get the quantity and uom code defined at position level convert those
2273 -- to primary quantity and primary uom code.
2274 IF (l_rt_quantity IS NOT NULL AND p_uom_code IS NOT NULL) THEN
2275 l_quantity := Get_Primary_UOM_Qty(
2276 p_x_route_mtl_reqs(p_x_index).inventory_item_id,
2277 p_uom_code,
2278 l_rt_quantity);
2279 l_uom_code := p_uom_code;
2280 ELSIF (l_ig_quantity IS NOT NULL AND l_ig_uom_code IS NOT NULL) THEN
2281 l_quantity := Get_Primary_UOM_Qty(
2282 p_x_route_mtl_reqs(p_x_index).inventory_item_id,
2283 l_ig_uom_code,
2284 l_ig_quantity);
2285 l_uom_code := l_ig_uom_code;
2286 ELSE
2287 l_quantity := Get_Primary_UOM_Qty(
2288 p_x_route_mtl_reqs(p_x_index).inventory_item_id,
2289 l_pos_uom_code,
2290 l_pos_quantity);
2291 l_uom_code := l_pos_uom_code;
2292 END IF;
2293
2294 -- Required to check the UOM Conversion exists in mtl_units_of_measure
2295 IF l_quantity IS NULL THEN
2296 FND_MESSAGE.Set_Name( 'AHL','AHL_LTP_UOM_CONV_NOT_EXIST' );
2297 FND_MESSAGE.Set_Token('FUOM', l_uom_code);
2298 FND_MESSAGE.Set_Token('TUOM', p_x_route_mtl_reqs(p_x_index).uom_code);
2299 FND_MSG_PUB.add;
2300 RAISE FND_API.G_EXC_ERROR;
2301 END IF;
2302
2303 -- Fetching the primary UOM defined for the derived item
2304 p_x_route_mtl_reqs(p_x_index).uom_code := Get_Primary_UOM(
2305 p_x_route_mtl_reqs(p_x_index).inventory_item_id,
2306 p_x_route_mtl_reqs(p_x_index).inv_master_org_id);
2307 IF (l_cp_uom IS NULL) THEN
2308 l_cp_uom := p_x_route_mtl_reqs(p_x_index).uom_code;
2309 ELSIF l_cp_uom <> p_x_route_mtl_reqs(p_x_index).uom_code THEN
2310 l_remaining_qty := Get_Primary_UOM_Qty(
2311 p_x_route_mtl_reqs(p_x_index).inventory_item_id,
2312 l_cp_uom,
2313 l_remaining_qty);
2314 -- Required to check the UOM Conversion exists in mtl_units_of_measure
2315 IF l_remaining_qty IS NULL THEN
2316 FND_MESSAGE.Set_Name( 'AHL','AHL_LTP_UOM_CONV_NOT_EXIST' );
2317 FND_MESSAGE.Set_Token('FUOM', l_cp_uom);
2318 FND_MESSAGE.Set_Token('TUOM', p_x_route_mtl_reqs(p_x_index).uom_code);
2319 FND_MSG_PUB.add;
2320 RAISE FND_API.G_EXC_ERROR;
2321 END IF;
2322
2323 -- Fetching the primary UOM defined for the derived item
2324 l_cp_uom := Get_Primary_UOM(
2325 p_x_route_mtl_reqs(p_x_index).inventory_item_id,
2326 p_x_route_mtl_reqs(p_x_index).inv_master_org_id);
2327 END IF;
2328 IF l_remaining_qty IS NULL THEN
2329 l_remaining_qty := l_quantity;
2330 END IF;
2331 -- If the material requirement created for the demanded quantity then
2332 -- make the remaining quantity as zero. Else subtract the scheduled
2333 -- quantity from the remaining quantity.
2334 IF (l_quantity > l_remaining_qty) THEN
2335 p_x_route_mtl_reqs(p_x_index).quantity := l_remaining_qty;
2336 l_remaining_qty := 0;
2337 ELSE
2338 p_x_route_mtl_reqs(p_x_index).quantity := l_quantity;
2339 l_remaining_qty := l_remaining_qty - l_quantity;
2340 END IF;
2341 END IF;
2342 END IF;
2343 -- If material requirement is created for requested quantity then exit.
2344 EXIT WHEN (l_remaining_qty = 0);
2345 FETCH get_child_positions_csr INTO l_child_position_key;
2346 -- If all the child positions were parsed then exit.
2347 EXIT WHEN get_child_positions_csr%NOTFOUND;
2348 -- Setting the route operation id , mc header id and position key for the next child position requirement.
2349 IF p_x_route_mtl_reqs(p_x_index).inventory_item_id IS NOT NULL THEN
2350 p_x_index := p_x_index + 1;
2351 p_x_route_mtl_reqs(p_x_index).rt_oper_material_id := l_rt_oper_mtl_id;
2352 p_x_route_mtl_reqs(p_x_index).MC_HEADER_ID := l_mc_header_id;
2353 p_x_route_mtl_reqs(p_x_index).position_key := l_child_position_key;
2354 END IF;
2355 END LOOP;
2356 CLOSE get_child_positions_csr;
2357 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2358 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Item_Id: ' || p_x_route_mtl_reqs(p_x_index).inventory_item_id ||
2359 ', Org_Id:' || p_x_route_mtl_reqs(p_x_index).inv_master_org_id ||
2360 ', Quantity:' || p_x_route_mtl_reqs(p_x_index).quantity ||
2361 ', UOM:' || p_x_route_mtl_reqs(p_x_index).uom_code);
2362 END IF;
2363
2364 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2365 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2366 END IF;
2367
2368 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
2369 l_msg_count := FND_MSG_PUB.count_msg;
2370 IF l_msg_count > 0 THEN
2371 x_msg_count := l_msg_count;
2372 RAISE FND_API.G_EXC_ERROR;
2373 END IF;
2374
2375 -- Count and Get messages (optional)
2376 FND_MSG_PUB.count_and_get(
2377 p_encoded => FND_API.G_FALSE,
2378 p_count => x_msg_count,
2379 p_data => x_msg_data);
2380
2381 EXCEPTION
2382 WHEN FND_API.G_EXC_ERROR THEN
2383 ROLLBACK TO get_material_req_for_pos;
2384 x_return_status := FND_API.G_RET_STS_ERROR ;
2385 FND_MSG_PUB.count_and_get(
2386 p_encoded => FND_API.G_FALSE,
2387 p_count => x_msg_count,
2388 p_data => x_msg_data);
2389
2390 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2391 ROLLBACK TO get_material_req_for_pos;
2392 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2393 FND_MSG_PUB.count_and_get(
2394 p_encoded => FND_API.G_FALSE,
2395 p_count => x_msg_count,
2396 p_data => x_msg_data);
2397
2398 WHEN OTHERS THEN
2399 ROLLBACK TO get_material_req_for_pos;
2400 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2401 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2402 THEN
2403 FND_MSG_PUB.add_exc_msg(
2404 p_pkg_name => G_PKG_NAME,
2405 p_procedure_name => l_api_name,
2406 p_error_text => SUBSTRB(SQLERRM,1,240));
2407 END IF;
2408 FND_MSG_PUB.count_and_get(
2409 p_encoded => FND_API.G_FALSE,
2410 p_count => x_msg_count,
2411 p_data => x_msg_data);
2412
2413
2414 END Get_Material_Req_For_Pos;
2415 ------------------------------------------------------------------------------------
2416
2417 ------------------------------------------------------------------------------------
2418 -- Start of Comments
2419 -- Procedure name : Get_Item_Group_Item
2420 -- Type : Public
2421 -- Function : Gets an highest priority item from item group if SB rule doesn't
2422 -- exists for the position, else get the highest priority item which
2423 -- satisfies the SB rule condition.
2424 -- Pre-reqs :
2425 -- Parameters :
2426 --
2427 -- Get_Item_Group_Item Parameters:
2428 -- p_item_group_id IN Given MC header id. Required
2429 -- p_rule_id IN Given MC position key. Required
2430 -- p_visit_org_id IN Organisation Id associated to the visit Optional
2431 -- End of Comments
2432
2433 PROCEDURE Get_Item_Group_Item(
2434 p_item_group_id IN NUMBER,
2435 p_rule_id IN NUMBER,
2436 p_visit_org_id IN NUMBER,
2437 x_inventory_item_id OUT NOCOPY NUMBER,
2438 x_inventory_org_id OUT NOCOPY NUMBER,
2439 x_quantity OUT NOCOPY NUMBER,
2440 x_uom_code OUT NOCOPY VARCHAR2
2441 ) IS
2442 -- To Get highest priority item when SB is not present
2443 CURSOR get_item_group_item_csr IS
2444 SELECT ia.inventory_item_id, NVL(p_visit_org_id, ia.inventory_org_id), ia.quantity, ia.uom_code
2445 FROM ahl_item_associations_b ia, mtl_system_items_b msi
2446 WHERE ia.item_group_id = p_item_group_id
2447 AND ia.interchange_type_code = '2-WAY INTERCHANGEABLE'
2448 AND ia.inventory_item_id = msi.inventory_item_id
2449 AND msi.organization_id = NVL(p_visit_org_id, ia.inventory_org_id)
2450 ORDER BY ia.priority;
2451
2452 -- To Get the highest priority item when SB is present
2453 CURSOR get_item_group_sb_item_csr IS
2454 SELECT ia.inventory_item_id, NVL(p_visit_org_id, ia.inventory_org_id), ia.quantity, ia.uom_code
2455 FROM ahl_item_associations_b ia, mtl_system_items_b msi
2456 WHERE ia.item_group_id = p_item_group_id
2457 AND ia.interchange_type_code = '2-WAY INTERCHANGEABLE'
2458 AND ia.inventory_item_id = msi.inventory_item_id
2459 AND msi.organization_id = NVL(p_visit_org_id, ia.inventory_org_id)
2460 AND ia.item_association_id IN (SELECT item_association_id
2461 FROM ahl_sb_rule_items
2462 WHERE rule_id = p_rule_id
2463 AND item_group_id = p_item_group_id)
2464 ORDER BY ia.priority;
2465
2466 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Get_Item_Group_Item';
2467 BEGIN
2468 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2469 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2470 END IF;
2471
2472 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2473 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'p_item_group_id: ' || p_item_group_id ||
2474 ', p_rule_id: ' || p_rule_id||
2475 ', p_visit_org_id' || p_visit_org_id);
2476 END IF;
2477
2478 IF ( p_item_group_id IS NULL ) THEN
2479 RETURN;
2480 END IF;
2481
2482 -- If SB rule is not defined then get the highest priority item from the item group.
2483 -- Else, get the highest priority item which satisfies the SB rule.
2484 IF (p_rule_id IS NULL) THEN
2485 OPEN get_item_group_item_csr;
2486 FETCH get_item_group_item_csr INTO x_inventory_item_id, x_inventory_org_id, x_quantity, x_uom_code;
2487 CLOSE get_item_group_item_csr;
2488 ELSE
2489 OPEN get_item_group_sb_item_csr;
2490 FETCH get_item_group_sb_item_csr INTO x_inventory_item_id, x_inventory_org_id, x_quantity, x_uom_code;
2491 CLOSE get_item_group_sb_item_csr;
2492 END IF;
2493
2494 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2495 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2496 END IF;
2497 END Get_Item_Group_Item;
2498
2499 ------------------------------------------------------------------------------------
2500
2501 ------------------------------------------------------------------------------------
2502 -- Start of Comments
2503 -- Procedure name : Get_Alternate_Items
2504 -- Type : Public
2505 -- Function : Gets all the applicable alternate items for given ahl_schedule_material
2506 -- requirement.
2507 -- Pre-reqs :
2508 -- Parameters :
2509 --
2510 -- Get_Alternate_Items Parameters:
2511 -- p_schedule_material_id IN NUMBER schedule material id Required
2512 -- x_alt_items OUT Alt_Items_Tbl_Type Alternate Items for material requirement
2513 -- End of Comments
2514 PROCEDURE Get_Alternate_Items(
2515 p_api_version IN NUMBER,
2516 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2517 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2518 p_schedule_material_id IN NUMBER,
2519 p_curr_item_flag IN VARCHAR2 := FND_API.G_FALSE,
2520 x_alt_items OUT NOCOPY Alt_Items_Tbl_Type,
2521 x_return_status OUT NOCOPY VARCHAR2,
2522 x_msg_count OUT NOCOPY NUMBER,
2523 x_msg_data OUT NOCOPY VARCHAR2
2524 ) IS
2525
2526 -- Cursor to get the details from ahl_schedule_materials table
2527 CURSOR get_asm_req_details_csr(c_asm_id IN NUMBER) IS
2528 SELECT asm.mc_header_id,
2529 asm.position_key,
2530 asm.inventory_item_id,
2531 asm.visit_id,
2532 asm.visit_task_id,
2533 asm.item_group_id,
2534 asm.organization_id,
2535 asm.relationship_id,
2536 vt.instance_id
2537 FROM ahl_schedule_materials asm,
2538 ahl_visit_tasks_b vt
2539 WHERE asm.scheduled_material_id = c_asm_id
2540 AND vt.visit_task_id = asm.visit_task_id;
2541
2542 -- Cursor to get the UC header Id from the instance Id
2543 CURSOR get_uc_header_csr(c_instance_id IN NUMBER) IS
2544 SELECT unit_config_header_id uc_header_id
2545 FROM ahl_unit_config_headers
2546 WHERE csi_item_instance_id = c_instance_id;
2547
2548 -- cursor to get applicable SB rules for the given unit and position
2549 -- NOTE::Fetch only once from this cursor. UNION ALL is fine as it will be fetched only once.
2550 CURSOR get_appl_sb_rules_csr (c_uc_header_id NUMBER,
2551 c_relationship_id NUMBER,
2552 c_visit_id NUMBER) IS
2553 SELECT DISTINCT rule_id,
2554 rule_sequence
2555 FROM (
2556 SELECT SB.rule_id,
2557 SB.rule_sequence
2558 FROM ahl_sb_position_rules SB,
2559 ahl_unit_config_headers UC
2560 WHERE UC.unit_config_header_id = c_uc_header_id
2561 AND SB.mc_header_id = UC.master_config_id
2562 -- take only the rules which have MRs accomplished on the unit
2563 AND EXISTS (SELECT 'X'
2564 FROM ahl_unit_effectivities_b UE
2565 WHERE UE.mr_header_id = SB.mr_header_id
2566 AND UE.csi_item_instance_id = UC.csi_item_instance_id -- SB MRs are effective on root node
2567 AND UE.accomplished_date IS NOT NULL)
2568 -- take only the MAX sequence rules for a position, among the accomplished MRs
2569 AND SB.rule_sequence = (SELECT MAX(SB2.rule_sequence)
2570 FROM ahl_sb_position_rules SB2
2571 WHERE SB2.relationship_id = SB.relationship_id
2572 AND EXISTS (SELECT 'X'
2573 FROM ahl_unit_effectivities_b UE
2574 WHERE UE.mr_header_id = SB2.mr_header_id
2575 AND UE.csi_item_instance_id = UC.csi_item_instance_id
2576 AND UE.accomplished_date IS NOT NULL))
2577 AND SB.relationship_id = c_relationship_id
2578 AND TRUNC(NVL(SB.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
2579 AND TRUNC(NVL(SB.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE)
2580 UNION ALL
2581 SELECT SB.rule_id,
2582 SB.rule_sequence
2583 FROM AHL_SB_POSITION_RULES SB,
2584 AHL_VISIT_TASKS_B VT,
2585 AHL_UNIT_CONFIG_HEADERS UC
2586 WHERE UC.unit_config_header_id = c_uc_header_id
2587 AND SB.mc_header_id = UC.master_config_id
2588 -- take only the rules which have MRs attached to the visit
2589 AND VT.visit_id = c_visit_id
2590 AND VT.mr_id = SB.mr_header_id
2591 AND VT.status_code NOT IN ('CANCELLED', 'DELETED')
2592 -- take only the MAX sequence rules for a position
2593 AND SB.rule_sequence = (SELECT MAX(SB2.rule_sequence)
2594 FROM AHL_SB_POSITION_RULES SB2
2595 WHERE SB2.relationship_id = SB.relationship_id
2596 AND SB2.mr_header_id = SB.mr_header_id)
2597 AND SB.relationship_id = c_relationship_id
2598 AND TRUNC(NVL(SB.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
2599 AND TRUNC(NVL(SB.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE)
2600 ) ORDER BY rule_sequence DESC;
2601
2602 -- Cursor to get all the applicable alternate items if the requirement is
2603 -- Item Group based.
2604 CURSOR get_alt_items_ig_csr(c_item_group_id IN NUMBER,
2605 c_inventory_item_id IN NUMBER,
2606 c_organization_id IN NUMBER) IS
2607 SELECT ia.inventory_item_id
2608 FROM ahl_item_associations_b ia,
2609 mtl_system_items_b msi
2610 WHERE ia.item_group_id = c_item_group_id
2611 AND ia.interchange_type_code = '2-WAY INTERCHANGEABLE'
2612 AND ia.inventory_item_id = msi.inventory_item_id
2613 AND msi.organization_id = c_organization_id
2614 AND ia.inventory_item_id <> nvl(c_inventory_item_id, -1)
2615 ORDER BY ia.priority;
2616
2617 -- Cursor to get all the applicable alternate items if the requirement is
2618 -- position based and no SB rule accomplished for that position
2619 CURSOR get_alt_items_pos_csr(c_relationship_id IN NUMBER,
2620 c_inventory_item_id IN NUMBER,
2621 c_organization_id IN NUMBER) IS
2622 SELECT ia.inventory_item_id,
2623 smc.priority smc_priority,
2624 ia.priority item_priority
2625 FROM ahl_item_associations_b ia,
2626 mtl_system_items_b msi,
2627 ahl_mc_relationships mcr,
2628 ahl_mc_config_relations smc
2629 WHERE mcr.mc_header_id = smc.mc_header_id
2630 AND mcr.parent_relationship_id is null
2631 AND smc.relationship_id = c_relationship_id
2632 AND ia.item_group_id = mcr.item_group_id
2633 AND ia.interchange_type_code = '2-WAY INTERCHANGEABLE'
2634 AND ia.inventory_item_id = msi.inventory_item_id
2635 AND msi.organization_id = c_organization_id
2636 AND ia.inventory_item_id <> nvl(c_inventory_item_id,-1)
2637 UNION
2638 SELECT ia.inventory_item_id,
2639 TO_NUMBER(NULL) AS smc_priority,
2640 ia.priority item_priority
2641 FROM ahl_item_associations_b ia,
2642 mtl_system_items_b msi,
2643 ahl_mc_relationships mcr
2644 WHERE mcr.relationship_id = c_relationship_id
2645 AND ia.item_group_id = mcr.item_group_id
2646 AND ia.interchange_type_code = '2-WAY INTERCHANGEABLE'
2647 AND ia.inventory_item_id = msi.inventory_item_id
2648 AND msi.organization_id = c_organization_id
2649 AND ia.inventory_item_id <> nvl(c_inventory_item_id,-1)
2650 order by smc_priority, item_priority;
2651
2652 -- Cursor to get all the applicable alternate items if the requirement is
2653 -- position based and a SB rule accomplished for that position
2654 CURSOR get_alt_items_sb_pos_csr(c_relationship_id IN NUMBER,
2655 c_inventory_item_id IN NUMBER,
2656 c_organization_id IN NUMBER,
2657 c_sb_rule_id IN NUMBER) IS
2658 SELECT ia.inventory_item_id,
2659 smc.priority smc_priority,
2660 ia.priority item_priority
2661 FROM ahl_item_associations_b ia,
2662 mtl_system_items_b msi,
2663 ahl_mc_relationships mcr,
2664 ahl_mc_config_relations smc
2665 WHERE mcr.mc_header_id = smc.mc_header_id
2666 AND mcr.parent_relationship_id is null
2667 AND smc.relationship_id = c_relationship_id
2668 AND ia.item_group_id = mcr.item_group_id
2669 AND ia.interchange_type_code = '2-WAY INTERCHANGEABLE'
2670 AND ia.inventory_item_id = msi.inventory_item_id
2671 AND msi.organization_id = c_organization_id
2672 AND ia.inventory_item_id <> nvl(c_inventory_item_id, -1)
2673 AND ia.item_association_id IN (SELECT item_association_id
2674 FROM ahl_sb_rule_items
2675 WHERE rule_id = c_sb_rule_id
2676 AND item_group_id = mcr.item_group_id)
2677 UNION
2678 SELECT ia.inventory_item_id,
2679 TO_NUMBER(NULL) AS smc_priority,
2680 ia.priority item_priority
2681 FROM ahl_item_associations_b ia,
2682 mtl_system_items_b msi,
2683 ahl_mc_relationships mcr
2684 WHERE mcr.relationship_id = c_relationship_id
2685 AND ia.item_group_id = mcr.item_group_id
2686 AND ia.interchange_type_code = '2-WAY INTERCHANGEABLE'
2687 AND ia.inventory_item_id = msi.inventory_item_id
2688 AND msi.organization_id = c_organization_id
2689 AND ia.inventory_item_id <> nvl(c_inventory_item_id, -1)
2690 AND ia.item_association_id IN (SELECT item_association_id
2691 FROM ahl_sb_rule_items
2692 WHERE rule_id = c_sb_rule_id
2693 AND item_group_id = mcr.item_group_id)
2694 order by smc_priority, item_priority;
2695
2696 l_api_name CONSTANT VARCHAR2(30) := 'Get_Alternate_Items';
2697 l_api_version CONSTANT NUMBER := 1.0;
2698 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Get_Alternate_Items';
2699 l_msg_count NUMBER;
2700 l_asm_req_rec get_asm_req_details_csr%ROWTYPE;
2701 l_instance_id NUMBER;
2702 l_uc_header_id NUMBER;
2703 l_index NUMBER := 1;
2704 l_dummy NUMBER;
2705 l_sb_rule_id NUMBER;
2706 l_inventory_item_id NUMBER := NULL;
2707 l_duplicate_item BOOLEAN := FALSE;
2708 BEGIN
2709 --Initialize API return status to success
2710 x_return_status := FND_API.G_RET_STS_SUCCESS;
2711
2712 --Standard call to check for call compatibility.
2713 IF NOT FND_API.compatible_api_call(
2714 l_api_version,
2715 p_api_version,
2716 l_api_name,
2717 G_PKG_NAME)
2718 THEN
2719 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2720 END IF;
2721
2722 --Initialize message list if p_init_msg_list is set to TRUE.
2723 IF FND_API.to_boolean( p_init_msg_list ) THEN
2724 FND_MSG_PUB.initialize;
2725 END IF;
2726
2727 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2728 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2729 END IF;
2730
2731 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2732 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Schedule Material ID: '|| p_schedule_material_id);
2733 END IF;
2734
2735 IF p_schedule_material_id IS NULL THEN
2736 Fnd_Message.SET_NAME('AHL','AHL_LTP_MAT_ID_INVALID');
2737 Fnd_Msg_Pub.ADD;
2738 RAISE Fnd_Api.G_EXC_ERROR;
2739 END IF;
2740
2741 OPEN get_asm_req_details_csr(p_schedule_material_id);
2742 FETCH get_asm_req_details_csr INTO l_asm_req_rec;
2743 IF get_asm_req_details_csr%NOTFOUND THEN
2744 Fnd_Message.SET_NAME('AHL','AHL_LTP_MAT_ID_INVALID');
2745 Fnd_Msg_Pub.ADD;
2746 CLOSE get_asm_req_details_csr;
2747 RAISE Fnd_Api.G_EXC_ERROR;
2748 END IF;
2749 CLOSE get_asm_req_details_csr;
2750
2751 IF p_curr_item_flag IS NOT NULL AND p_curr_item_flag = FND_API.G_TRUE THEN
2752 l_inventory_item_id := NULL;
2753 ELSE
2754 l_inventory_item_id := l_asm_req_rec.inventory_item_id;
2755 END IF;
2756
2757 l_instance_id := Get_Unit_Instance(l_asm_req_rec.instance_id);
2758
2759 OPEN get_uc_header_csr(l_instance_id);
2760 FETCH get_uc_header_csr INTO l_uc_header_id;
2761 CLOSE get_uc_header_csr;
2762
2763 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2764 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'mc_header_id: ' || l_asm_req_rec.mc_header_id ||
2765 ', mc_pos_key: ' || l_asm_req_rec.position_key ||
2766 ', Inventory Item Id: ' || l_asm_req_rec.inventory_item_id ||
2767 ', uc_header_id: '|| l_uc_header_id ||
2768 ', visit_id: '|| l_asm_req_rec.visit_id ||
2769 ', visit_org_id: '|| l_asm_req_rec.organization_id ||
2770 ', visit_task_id: '|| l_asm_req_rec.visit_task_id ||
2771 ', relationship_id: '|| l_asm_req_rec.relationship_id ||
2772 ', item_group_id: '|| l_asm_req_rec.item_group_id);
2773 END IF;
2774
2775 -- Item Group Based Requirement
2776 IF l_asm_req_rec.item_group_id IS NOT NULL THEN
2777 FOR inv_item_id_rec IN get_alt_items_ig_csr(l_asm_req_rec.item_group_id,
2778 l_inventory_item_id,
2779 l_asm_req_rec.organization_id)
2780 LOOP
2781 x_alt_items(l_index) := inv_item_id_rec.inventory_item_id;
2782 l_index := l_index + 1;
2783 END LOOP;
2784 -- Position Based Material Requirement
2785 ELSIF l_asm_req_rec.mc_header_id IS NOT NULL AND l_asm_req_rec.position_key IS NOT NULL THEN
2786 -- Gets Last accomplised SB rule id.
2787 OPEN get_appl_sb_rules_csr (l_uc_header_id, l_asm_req_rec.relationship_id, l_asm_req_rec.visit_id);
2788 FETCH get_appl_sb_rules_csr INTO l_sb_rule_id, l_dummy;
2789 CLOSE get_appl_sb_rules_csr;
2790
2791 -- If no SB rule is accomplished or applicable for current position
2792 IF l_sb_rule_id IS NULL THEN
2793 FOR inv_item_id_rec IN get_alt_items_pos_csr(l_asm_req_rec.relationship_id,
2794 l_inventory_item_id,
2795 l_asm_req_rec.organization_id)
2796 LOOP
2797 l_duplicate_item := FALSE;
2798 -- Don't insert into the alt_items table if the inventory item is already
2799 -- present
2800 FOR i IN 1 .. x_alt_items.COUNT
2801 LOOP
2802 IF x_alt_items(i) = inv_item_id_rec.inventory_item_id THEN
2803 l_duplicate_item := TRUE;
2804 END IF;
2805 EXIT WHEN (l_duplicate_item = TRUE);
2806 END LOOP;
2807 IF l_duplicate_item = FALSE THEN
2808 x_alt_items(l_index) := inv_item_id_rec.inventory_item_id;
2809 l_index := l_index + 1;
2810 END IF;
2811 END LOOP;
2812 -- If SB rule accomplished for that position
2813 ELSE
2814 FOR inv_item_id_rec IN get_alt_items_sb_pos_csr(l_asm_req_rec.relationship_id,
2815 l_inventory_item_id,
2816 l_asm_req_rec.organization_id,
2817 l_sb_rule_id)
2818 LOOP
2819 l_duplicate_item := FALSE;
2820 -- Don't insert into the alt_items table if the inventory item is already
2821 -- present
2822 FOR i IN 1 .. x_alt_items.COUNT
2823 LOOP
2824 IF x_alt_items(i) = inv_item_id_rec.inventory_item_id THEN
2825 l_duplicate_item := TRUE;
2826 END IF;
2827 EXIT WHEN (l_duplicate_item = TRUE);
2828 END LOOP;
2829 IF l_duplicate_item = FALSE THEN
2830 x_alt_items(l_index) := inv_item_id_rec.inventory_item_id;
2831 l_index := l_index + 1;
2832 END IF;
2833 END LOOP;
2834 END IF;
2835 END IF;
2836
2837 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2838 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Alt Items Count: '|| x_alt_items.COUNT );
2839 END IF;
2840
2841 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2842 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2843 END IF;
2844
2845 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
2846 l_msg_count := FND_MSG_PUB.count_msg;
2847 IF l_msg_count > 0 THEN
2848 x_msg_count := l_msg_count;
2849 RAISE FND_API.G_EXC_ERROR;
2850 END IF;
2851
2852 -- Count and Get messages (optional)
2853 FND_MSG_PUB.count_and_get(
2854 p_encoded => FND_API.G_FALSE,
2855 p_count => x_msg_count,
2856 p_data => x_msg_data);
2857
2858 EXCEPTION
2859 WHEN FND_API.G_EXC_ERROR THEN
2860 x_return_status := FND_API.G_RET_STS_ERROR ;
2861 FND_MSG_PUB.count_and_get(
2862 p_encoded => FND_API.G_FALSE,
2863 p_count => x_msg_count,
2864 p_data => x_msg_data);
2865
2866 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2867 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2868 FND_MSG_PUB.count_and_get(
2869 p_encoded => FND_API.G_FALSE,
2870 p_count => x_msg_count,
2871 p_data => x_msg_data);
2872
2873 WHEN OTHERS THEN
2874 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2875 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2876 THEN
2877 FND_MSG_PUB.add_exc_msg(
2878 p_pkg_name => G_PKG_NAME,
2879 p_procedure_name => l_api_name,
2880 p_error_text => SUBSTRB(SQLERRM,1,240));
2881 END IF;
2882 FND_MSG_PUB.count_and_get(
2883 p_encoded => FND_API.G_FALSE,
2884 p_count => x_msg_count,
2885 p_data => x_msg_data);
2886 END Get_Alternate_Items;
2887 ------------------------------------------------------------------------------------
2888
2889 ------------------------------------
2890 -- End Local Function Definitions --
2891 ------------------------------------
2892
2893 END AHL_LTP_MTL_REQ_PVT;
[Home] [Help]