[Home] [Help]
PACKAGE BODY: APPS.AHL_PRD_MRSHL_PVT
Source
1 PACKAGE BODY AHL_PRD_MRSHL_PVT AS
2 /* $Header: AHLVPMLB.pls 120.8.12010000.1 2008/07/28 08:33:35 appldev ship $ */
3
4 G_PKG_NAME VARCHAR2(30) := 'AHL_PRD_MRSHL_PVT';
5
6 PROCEDURE get_uc_mrshl_details(
7 p_unit_header_id IN NUMBER,
8 p_visit_id IN NUMBER,
9 x_Uc_mrshl_details_tbl OUT NOCOPY mrshl_details_tbl_type);
10 PROCEDURE get_inst_mrshl_details(
11 p_item_instance_id IN NUMBER,
12 p_visit_id IN NUMBER,
13 x_inst_mrshl_details_tbl OUT NOCOPY mrshl_details_tbl_type);
14
15 FUNCTION GET_ONHAND_AVAILABLE(
16 P_ORG_ID IN NUMBER,
17 P_ITEM_ID IN NUMBER,
18 p_SUBINVENTORY VARCHAR2,
19 p_locator_id NUMBER) RETURN NUMBER
20 IS
21
22 onhand NUMBER;
23 CURSOR Q1(p_org_id NUMBER, P_ITEM_ID NUMBER,p_SUBINVENTORY VARCHAR2,p_locator_id NUMBER ) IS
24 SELECT SUM(TRANSACTION_QUANTITY)
25 FROM MTL_ONHAND_QUANTITIES
26 WHERE ORGANIZATION_ID = p_org_id
27 AND INVENTORY_ITEM_ID = P_ITEM_ID
28 AND LOCATOR_ID = p_locator_id
29 AND SUBINVENTORY_CODE = p_SUBINVENTORY;
30
31 BEGIN
32 OPEN Q1(P_ORG_ID,P_ITEM_ID,p_SUBINVENTORY,p_locator_id);
33 FETCH Q1 INTO onhand;
34 IF(Q1%NOTFOUND) THEN
35 onhand := 0;
36 END IF;
37 CLOSE Q1;
38 IF(NVL(onhand,-1) < 0) THEN
39 onhand := 0;
40 END IF;
41 return onhand;
42 END GET_ONHAND_AVAILABLE;
43
44 FUNCTION GET_ONHAND_NOTAVAILABLE(
45 P_ORG_ID IN NUMBER,
46 P_ITEM_ID IN NUMBER,
47 p_SUBINVENTORY VARCHAR2,
48 p_locator_id NUMBER) RETURN NUMBER
49 IS
50
51 onhand NUMBER;
52 CURSOR Q1(p_org_id NUMBER, p_item_Id NUMBER,p_locator_id NUMBER) IS
53 SELECT SUM(TRANSACTION_QUANTITY)
54 FROM MTL_ONHAND_QUANTITIES
55 WHERE ORGANIZATION_ID = p_org_id
56 AND INVENTORY_ITEM_ID = p_item_id
57 AND LOCATOR_ID <> p_locator_id;
58 --AND SUBINVENTORY_CODE <> p_SUBINVENTORY;
59
60 BEGIN
61 OPEN Q1(P_ORG_ID,P_ITEM_ID,p_locator_id);
62 FETCH Q1 INTO onhand;
63 IF(Q1%NOTFOUND ) THEN
64 onhand := 0;
65 END IF;
66 CLOSE Q1;
67 IF(NVL(onhand,-1) < 0) THEN
68 onhand := 0;
69 END IF;
70 return onhand;
71 END GET_ONHAND_NOTAVAILABLE;
72
73 PROCEDURE Get_unavailable_items
74 (
75 p_api_version IN NUMBER := 1.0,
76 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
77 p_commit IN VARCHAR2 := FND_API.G_FALSE,
78 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
79 p_default IN VARCHAR2 := FND_API.G_FALSE,
80 p_module_type IN VARCHAR2 := NULL,
81 p_Mrshl_search_rec IN Mrshl_search_rec_type,
82 x_Unavailable_items_tbl OUT NOCOPY Unavailable_items_Tbl_Type,
83 x_return_status OUT NOCOPY VARCHAR2,
84 x_msg_count OUT NOCOPY NUMBER,
85 x_msg_data OUT NOCOPY VARCHAR2
86 ) IS
87
88 l_api_name CONSTANT VARCHAR2(30) := 'Get_unavailable_items';
89 l_api_version CONSTANT NUMBER := 1.0;
90
91 l_unavailable_items_tbl Unavailable_items_Tbl_Type;
92 j NUMBER;
93
94 CURSOR get_root_items_instance_csr(p_visit_id NUMBER,
95 p_item_instance_id NUMBER) IS
96 SELECT VTS.instance_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
97 WO.visit_id = p_visit_id
98 AND WO.visit_id = VTS.visit_id
99 AND WO.visit_task_id = VTS.visit_task_id
100 AND VTS.instance_id = p_item_instance_id
101 AND WO.status_code <> 22
102 AND ROWNUM < 2;
103
104 l_root_instance_id NUMBER;
105
106 CURSOR get_unavailable_items_csr(
107 p_visit_id NUMBER,
108 p_root_instance_id NUMBER,
109 p_workorder_name VARCHAR2,
110 p_part_number VARCHAR2,
111 p_part_desc VARCHAR2,
112 p_locator_id NUMBER,
113 p_subinventory_code VARCHAR2,
114 p_fetch_mode VARCHAR2) IS
115 SELECT * FROM(
116 SELECT ASML.scheduled_material_id,AWOS.workorder_id,AWOS.job_number,AWOS.job_status_code,AWOS.job_status_meaning,ASML.operation_sequence, MSIK.concatenated_segments ,
117 MSIK.description, MSIK.primary_uom_code,MSIK.primary_unit_of_measure,
118 WIRO.REQUIRED_QUANTITY , WIRO.DATE_REQUIRED,
119 asml.scheduled_quantity ,asml.scheduled_date ,
120 nvl(ahl_pp_materials_pvt.get_issued_qty(msik.organization_id, asml.inventory_item_id, asml.workorder_operation_id), 0) issued_quantity,
121 asml.inventory_item_id,
122 ASML.organization_id,
123 AWOS.wip_entity_id,
124 AWOS.visit_id,
125 DECODE(MSIK.SERIAL_NUMBER_CONTROL_CODE,1,'N','Y') Is_Serialized,
126 DECODE(NVL(MSIK.LOT_CONTROL_CODE,-1),1,'N','Y') Is_Lot_Controlled,
127 DECODE(NVL(MSIK.REVISION_QTY_CONTROL_CODE,-1),1,'N','Y') Is_Revision_Controlled,
128 GET_ONHAND_AVAILABLE(ASML.organization_id,ASML.inventory_item_id,
129 p_subinventory_code,p_locator_id) AVAILABLE_QUANTITY,
130 GET_ONHAND_NOTAVAILABLE(ASML.organization_id,ASML.inventory_item_id,
131 p_subinventory_code,p_locator_id) NOT_AVAILABLE_QUANTITY,
132 ASML.requested_quantity QTY_PER_ASSEMBLY,
133 ASML.scheduled_date EXCEPTION_DATE,
134 (SELECT nvl(SUM(mrv.primary_reservation_quantity),0) FROM mtl_reservations MRV
135 WHERE MRV.INVENTORY_ITEM_ID =WIRO.INVENTORY_ITEM_ID
136 AND MRV. EXTERNAL_SOURCE_CODE = 'AHL'
137 AND MRV.DEMAND_SOURCE_HEADER_ID = WIRO.WIP_ENTITY_ID
138 AND MRV.DEMAND_SOURCE_LINE_ID =WIRO.OPERATION_SEQ_NUM) RESERVED_QUANTITY
139 from ahl_search_workorders_v AWOS, ahl_schedule_materials ASML, WIP_REQUIREMENT_OPERATIONS WIRO, mtl_system_items_kfv MSIK
140 WHERE AWOS.visit_task_id = ASML.visit_task_id
141 and ASML.inventory_item_id = MSIK.inventory_item_id
142 and ASML.organization_id = MSIK.organization_id
143 AND AWOS.WIP_ENTITY_ID = WIRO.WIP_ENTITY_ID
144 AND ASML.OPERATION_SEQUENCE = WIRO.OPERATION_SEQ_NUM
145 AND ASML.INVENTORY_ITEM_ID = WIRO.INVENTORY_ITEM_ID
146 AND ASML.ORGANIZATION_ID = WIRO.ORGANIZATION_ID and asml.status = 'ACTIVE'
147 AND AWOS.workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
148 WO.visit_id = p_visit_id
149 AND WO.visit_id = VTS.visit_id
150 AND WO.visit_task_id = VTS.visit_task_id
151 AND VTS.instance_id IN(
152 SELECT instance_id FROM csi_item_instances WHERE INSTANCE_ID = p_root_instance_id
153 UNION
154 SELECT
155 SUBJECT_ID INSTANCE_ID
156 FROM CSI_II_RELATIONSHIPS
157 WHERE 1=1
158 START WITH OBJECT_ID = p_root_instance_id /*root instance id*/
159 AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
160 AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
161 AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
162 --CONNECT BY SUBJECT_ID = PRIOR OBJECT_ID
163 CONNECT BY OBJECT_ID = PRIOR SUBJECT_ID
164 AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
165 AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
166 AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
167 )) )REQ WHERE
168 REQ.concatenated_segments LIKE NVL(p_part_number,'%')
169 AND REQ.description LIKE NVL(p_part_desc,'%')
170 AND REQ.job_number LIKE NVL(p_workorder_name,'%')
171 AND REQ.REQUIRED_QUANTITY > (REQ.issued_quantity + REQ.AVAILABLE_QUANTITY)
172 AND decode(p_fetch_mode,'UM',REQ.NOT_AVAILABLE_QUANTITY,1) > 0
173 AND decode(p_fetch_mode,'UI',REQ.REQUIRED_QUANTITY - (REQ.issued_quantity +
174 REQ.AVAILABLE_QUANTITY + REQ.NOT_AVAILABLE_QUANTITY ),1) > 0;
175
176 CURSOR get_unavailable_items_csr1(
177 p_visit_id NUMBER,
178 p_workorder_name VARCHAR2,
179 p_part_number VARCHAR2,
180 p_part_desc VARCHAR2,
181 p_locator_id NUMBER,
182 p_subinventory_code VARCHAR2,
183 p_fetch_mode VARCHAR2) IS
184 SELECT * FROM(
185 SELECT ASML.scheduled_material_id,AWOS.workorder_id,AWOS.job_number,AWOS.job_status_code,AWOS.job_status_meaning,ASML.operation_sequence, MSIK.concatenated_segments ,
186 MSIK.description, MSIK.primary_uom_code,MSIK.primary_unit_of_measure,
187 WIRO.REQUIRED_QUANTITY , WIRO.DATE_REQUIRED,
188 asml.scheduled_quantity ,asml.scheduled_date ,
189 nvl(ahl_pp_materials_pvt.get_issued_qty(msik.organization_id, asml.inventory_item_id, asml.workorder_operation_id), 0) issued_quantity,
190 asml.inventory_item_id,
191 ASML.organization_id,
192 AWOS.wip_entity_id,
193 AWOS.visit_id,
194 DECODE(MSIK.SERIAL_NUMBER_CONTROL_CODE,1,'N','Y') Is_Serialized,
195 DECODE(NVL(MSIK.LOT_CONTROL_CODE,-1),1,'N','Y') Is_Lot_Controlled,
196 DECODE(NVL(MSIK.REVISION_QTY_CONTROL_CODE,-1),1,'N','Y') Is_Revision_Controlled,
197 GET_ONHAND_AVAILABLE(ASML.organization_id,ASML.inventory_item_id,
198 p_subinventory_code,p_locator_id) AVAILABLE_QUANTITY,
199 GET_ONHAND_NOTAVAILABLE(ASML.organization_id,ASML.inventory_item_id,
200 p_subinventory_code,p_locator_id) NOT_AVAILABLE_QUANTITY,
201 ASML.requested_quantity QTY_PER_ASSEMBLY,
202 ASML.scheduled_date EXCEPTION_DATE,
203 (SELECT nvl(SUM(mrv.primary_reservation_quantity),0) FROM mtl_reservations MRV
204 WHERE MRV.INVENTORY_ITEM_ID =WIRO.INVENTORY_ITEM_ID
205 AND MRV. EXTERNAL_SOURCE_CODE = 'AHL'
206 AND MRV.DEMAND_SOURCE_HEADER_ID = WIRO.WIP_ENTITY_ID
207 AND MRV.DEMAND_SOURCE_LINE_ID =WIRO.OPERATION_SEQ_NUM) RESERVED_QUANTITY
208 from ahl_search_workorders_v AWOS, ahl_schedule_materials ASML, WIP_REQUIREMENT_OPERATIONS WIRO, mtl_system_items_kfv MSIK
209 WHERE AWOS.visit_task_id = ASML.visit_task_id
210 and ASML.inventory_item_id = MSIK.inventory_item_id
211 and ASML.organization_id = MSIK.organization_id
212 AND AWOS.WIP_ENTITY_ID = WIRO.WIP_ENTITY_ID
213 AND ASML.OPERATION_SEQUENCE = WIRO.OPERATION_SEQ_NUM
214 AND ASML.INVENTORY_ITEM_ID = WIRO.INVENTORY_ITEM_ID
215 AND ASML.ORGANIZATION_ID = WIRO.ORGANIZATION_ID and asml.status = 'ACTIVE'
216 AND AWOS.workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
217 WO.visit_id = p_visit_id
218 AND WO.visit_id = VTS.visit_id
219 AND WO.visit_task_id = VTS.visit_task_id
220 ))REQ WHERE
221 REQ.concatenated_segments LIKE NVL(p_part_number,'%')
222 AND REQ.description LIKE NVL(p_part_desc,'%')
223 AND REQ.job_number LIKE NVL(p_workorder_name,'%')
224 AND REQ.REQUIRED_QUANTITY > (REQ.issued_quantity + REQ.AVAILABLE_QUANTITY)
225 AND decode(p_fetch_mode,'UM',REQ.NOT_AVAILABLE_QUANTITY,1) > 0
226 AND decode(p_fetch_mode,'UI',REQ.REQUIRED_QUANTITY - (REQ.issued_quantity +
227 REQ.AVAILABLE_QUANTITY + REQ.NOT_AVAILABLE_QUANTITY ),1) > 0;
228
229 CURSOR visit_info_csr(p_visit_id NUMBER)IS
230 SELECT inv_locator_id, subinventory, locator_segments FROM ahl_prd_visits_v
231 WHERE visit_id = p_visit_id;
232 l_locator_id NUMBER;
233 l_subinventory VARCHAR2(10);
234 l_locator_segments VARCHAR2(240);
235
236 CURSOR serialized_item_info(p_org_id NUMBER,
237 p_inventory_item_id NUMBER,
238 p_subinventory_code VARCHAR2,
239 p_locator_id NUMBER) IS
240 select msn.serial_number , msn.lot_number ,msn.revision,
241 msn.current_subinventory_code,msn.current_locator_id,
242
243 decode(msi.segment19, null, mil.concatenated_segments, INV_PROJECT.GET_LOCSEGS(mil.concatenated_segments) ||
244 fnd_flex_ext.get_delimiter('INV', 'MTLL', 101) || INV_ProjectLocator_PUB.get_project_number(msi.segment19) ||
245 fnd_flex_ext.get_delimiter('INV', 'MTLL', 101) || INV_ProjectLocator_PUB.get_task_number(msi.segment20))
246 locator_segments
247 from mtl_serial_numbers msn,
248 mtl_system_items_kfv mkfv,
249 mtl_item_locations_kfv mil,
250 mtl_item_locations msi
251 WHERE msi.inventory_item_id = mkfv.inventory_item_id
252 and msi.organization_id=mkfv.organization_id
253 and msn.inventory_item_id = mkfv.inventory_item_id
254 and msn.current_organization_id=mkfv.organization_id
255 and msn.current_locator_id = mil.INVENTORY_locatION_ID (+)
256 and msn.current_locator_id = msi.INVENTORY_locatION_ID (+)
257 and mkfv.serial_number_control_code <> 1
258 and msn.current_status=3
259 and msn.inventory_item_id = p_inventory_item_id
260 and msn.current_organization_id = p_org_id
261 and msn.current_locator_id <> p_locator_id;
262 --AND msn.current_subinventory_code <> p_subinventory_code;
263
264
265 BEGIN
266 x_return_status := FND_API.G_RET_STS_SUCCESS;
267
268 --Standard call to check for call compatibility.
269 IF NOT FND_API.compatible_api_call(
270 l_api_version,
271 p_api_version,
272 l_api_name,
273 G_PKG_NAME)
274 THEN
275 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
276 END IF;
277 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
278 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
279 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
280 ' :p_Mrshl_search_rec.Visit_id : ' || p_Mrshl_search_rec.Visit_id
281 );
282 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
283 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
284 ' :p_Mrshl_search_rec.item_instance_id : ' || p_Mrshl_search_rec.item_instance_id
285 );
286 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
287 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
288 ' :p_Mrshl_search_rec.Workorder_name : ' || p_Mrshl_search_rec.Workorder_name
289 );
290 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
291 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
292 ' :p_Mrshl_search_rec.Item_name : ' || p_Mrshl_search_rec.Item_name
293 );
294 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
295 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
296 ' :p_Mrshl_search_rec.Item_Desc : ' || p_Mrshl_search_rec.Item_Desc
297 );
298 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
299 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
300 ' :p_Mrshl_search_rec.Search_mode : ' || p_Mrshl_search_rec.Search_mode
301 );
302 END IF;
303 OPEN visit_info_csr(p_Mrshl_search_rec.Visit_id);
304 FETCH visit_info_csr INTO l_locator_id, l_subinventory,l_locator_segments;
305 CLOSE visit_info_csr;
306
307 IF(p_Mrshl_search_rec.item_instance_id IS NOT NULL)THEN
308 /* OPEN get_root_items_instance_csr(p_Mrshl_search_rec.Visit_id,
309 p_Mrshl_search_rec.item_instance_id);
310 FETCH get_root_items_instance_csr INTO l_root_instance_id;
311 IF(get_root_items_instance_csr%NOTFOUND)THEN
312 RETURN;
313 END IF;
314 CLOSE get_root_items_instance_csr;*/
315 l_root_instance_id := p_Mrshl_search_rec.item_instance_id;
316 END IF;
317 j := 1;
318
319 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
320 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
321 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
322 ' :l_root_instance_id : ' || l_root_instance_id
323 );
324 END IF;
325
326 IF(l_root_instance_id IS NOT NULL)THEN
327 FOR unavail_item_csr IN get_unavailable_items_csr(
328 p_Mrshl_search_rec.Visit_id,
329 l_root_instance_id,
330 p_Mrshl_search_rec.Workorder_name,
331 p_Mrshl_search_rec.Item_name,
332 p_Mrshl_search_rec.Item_Desc,
333 NVL(l_locator_id,-1) ,
334 NVL(l_subinventory,'x'),
335 p_Mrshl_search_rec.Search_mode) LOOP
336 l_unavailable_items_tbl(j).Scheduled_material_id := unavail_item_csr.scheduled_material_id;
337 l_unavailable_items_tbl(j).Inventory_item_id := unavail_item_csr.inventory_item_id;
338 l_unavailable_items_tbl(j).Item_name := unavail_item_csr.concatenated_segments;
339 l_unavailable_items_tbl(j).Item_Desc := unavail_item_csr.description;
340 l_unavailable_items_tbl(j).Workorder_id := unavail_item_csr.Workorder_id;
341 l_unavailable_items_tbl(j).Workorder_Name := unavail_item_csr.job_number;
342 l_unavailable_items_tbl(j).Organization_id := unavail_item_csr.Organization_id;
343 l_unavailable_items_tbl(j).Visit_id := unavail_item_csr.Visit_id;
344 l_unavailable_items_tbl(j).Wip_Entity_Id := unavail_item_csr.Wip_Entity_Id;
345 l_unavailable_items_tbl(j).wo_status := unavail_item_csr.job_status_meaning;
346 l_unavailable_items_tbl(j).wo_status_code := unavail_item_csr.job_status_code;
347 l_unavailable_items_tbl(j).Op_seq := unavail_item_csr.operation_sequence;
348 l_unavailable_items_tbl(j).UOM := unavail_item_csr.primary_uom_code;
352 l_unavailable_items_tbl(j).Issued_Quantity := unavail_item_csr.issued_quantity;
349 l_unavailable_items_tbl(j).UOM_DESC := unavail_item_csr.primary_unit_of_measure;
350 l_unavailable_items_tbl(j).Required_quantity := unavail_item_csr.REQUIRED_QUANTITY;
351 l_unavailable_items_tbl(j).Required_date := unavail_item_csr.DATE_REQUIRED;
353 l_unavailable_items_tbl(j).Qty_per_assembly := unavail_item_csr.Qty_per_assembly;
354 l_unavailable_items_tbl(j).Scheduled_date := unavail_item_csr.scheduled_date;
355 l_unavailable_items_tbl(j).Scheduled_Quantity := unavail_item_csr.scheduled_quantity;
356 l_unavailable_items_tbl(j).Is_serialized := unavail_item_csr.Is_serialized;
357 l_unavailable_items_tbl(j).Is_Lot_Controlled := unavail_item_csr.Is_Lot_Controlled;
358 l_unavailable_items_tbl(j).Is_Revision_Controlled := unavail_item_csr.Is_Revision_Controlled;
359 l_unavailable_items_tbl(j).quantity := unavail_item_csr.not_available_quantity;
360 l_unavailable_items_tbl(j).onhand_quantity := unavail_item_csr.not_available_quantity;
361 l_unavailable_items_tbl(j).exception_date := unavail_item_csr.exception_date;
362 l_unavailable_items_tbl(j).reserved_quantity := unavail_item_csr.reserved_quantity;
363 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
364 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
365 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
366 ' :unavail_item_csr.not_available_quantity : ' || unavail_item_csr.not_available_quantity
367 );
368 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
369 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
370 ' :unavail_item_csr.REQUIRED_QUANTITY : ' || unavail_item_csr.REQUIRED_QUANTITY
371 );
372 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
373 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
374 ' :unavail_item_csr.Issued_Quantity : ' || unavail_item_csr.Issued_Quantity
375 );
376 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
377 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
378 ' :unavail_item_csr.available_quantity : ' || unavail_item_csr.available_quantity
379 );
380 END IF;
381 IF p_Mrshl_search_rec.Search_mode = 'UM' THEN
382 IF(l_unavailable_items_tbl(j).quantity >
383 (l_unavailable_items_tbl(j).Required_quantity - (
384 l_unavailable_items_tbl(j).Issued_Quantity +
385 unavail_item_csr.available_quantity)))THEN
386 l_unavailable_items_tbl(j).quantity := l_unavailable_items_tbl(j).Required_quantity -
387 (l_unavailable_items_tbl(j).Issued_Quantity +
388 unavail_item_csr.available_quantity);
389 END IF;
390 IF(unavail_item_csr.not_available_quantity = 1 AND l_unavailable_items_tbl(j).Is_serialized = 'Y')THEN
391 OPEN serialized_item_info(unavail_item_csr.organization_id,
392 unavail_item_csr.inventory_item_id, l_subinventory, l_locator_id);
393 FETCH serialized_item_info INTO l_unavailable_items_tbl(j).Serial_Number,
394 l_unavailable_items_tbl(j).Lot,
395 l_unavailable_items_tbl(j).Revision,
396 l_unavailable_items_tbl(j).Subinventory,
397 l_unavailable_items_tbl(j).Locator_id,
398 l_unavailable_items_tbl(j).Locator_segments;
399 CLOSE serialized_item_info;
400 END IF;
401 ELSIF p_Mrshl_search_rec.Search_mode = 'UI' THEN
402 l_unavailable_items_tbl(j).quantity := l_unavailable_items_tbl(j).Required_quantity
403 - (l_unavailable_items_tbl(j).Issued_Quantity +
404 unavail_item_csr.available_quantity + l_unavailable_items_tbl(j).quantity);
405
406 END IF;
407 j:= j+1;
408 END LOOP;
409 ELSE
410 FOR unavail_item_csr IN get_unavailable_items_csr1(
411 p_Mrshl_search_rec.Visit_id,
412 p_Mrshl_search_rec.Workorder_name,
413 p_Mrshl_search_rec.Item_name,
414 p_Mrshl_search_rec.Item_Desc,
415 NVL(l_locator_id,-1) ,
416 NVL(l_subinventory,'x'),
417 p_Mrshl_search_rec.Search_mode) LOOP
418 l_unavailable_items_tbl(j).Scheduled_material_id := unavail_item_csr.scheduled_material_id;
419 l_unavailable_items_tbl(j).Inventory_item_id := unavail_item_csr.inventory_item_id;
420 l_unavailable_items_tbl(j).Item_name := unavail_item_csr.concatenated_segments;
421 l_unavailable_items_tbl(j).Item_Desc := unavail_item_csr.description;
422 l_unavailable_items_tbl(j).Workorder_id := unavail_item_csr.Workorder_id;
423 l_unavailable_items_tbl(j).Workorder_Name := unavail_item_csr.job_number;
424 l_unavailable_items_tbl(j).Organization_id := unavail_item_csr.Organization_id;
425 l_unavailable_items_tbl(j).Visit_id := unavail_item_csr.Visit_id;
426 l_unavailable_items_tbl(j).Wip_Entity_Id := unavail_item_csr.Wip_Entity_Id;
427 l_unavailable_items_tbl(j).wo_status := unavail_item_csr.job_status_meaning;
428 l_unavailable_items_tbl(j).wo_status_code := unavail_item_csr.job_status_code;
429 l_unavailable_items_tbl(j).Op_seq := unavail_item_csr.operation_sequence;
430 l_unavailable_items_tbl(j).UOM := unavail_item_csr.primary_uom_code;
434 l_unavailable_items_tbl(j).Issued_Quantity := unavail_item_csr.issued_quantity;
431 l_unavailable_items_tbl(j).UOM_DESC := unavail_item_csr.primary_unit_of_measure;
432 l_unavailable_items_tbl(j).Required_quantity := unavail_item_csr.REQUIRED_QUANTITY;
433 l_unavailable_items_tbl(j).Required_date := unavail_item_csr.DATE_REQUIRED;
435 l_unavailable_items_tbl(j).Qty_per_assembly := unavail_item_csr.Qty_per_assembly;
436 l_unavailable_items_tbl(j).Scheduled_date := unavail_item_csr.scheduled_date;
437 l_unavailable_items_tbl(j).Scheduled_Quantity := unavail_item_csr.scheduled_quantity;
438 l_unavailable_items_tbl(j).Is_serialized := unavail_item_csr.Is_serialized;
439 l_unavailable_items_tbl(j).Is_Lot_Controlled := unavail_item_csr.Is_Lot_Controlled;
440 l_unavailable_items_tbl(j).Is_Revision_Controlled := unavail_item_csr.Is_Revision_Controlled;
441 l_unavailable_items_tbl(j).onhand_quantity := unavail_item_csr.not_available_quantity;
442 l_unavailable_items_tbl(j).exception_date := unavail_item_csr.exception_date;
443 l_unavailable_items_tbl(j).reserved_quantity := unavail_item_csr.reserved_quantity;
444 IF( p_Mrshl_search_rec.Search_mode = 'UM')THEN
445 l_unavailable_items_tbl(j).quantity := unavail_item_csr.not_available_quantity;
446 IF(l_unavailable_items_tbl(j).quantity >
447 (l_unavailable_items_tbl(j).Required_quantity -
448 l_unavailable_items_tbl(j).Issued_Quantity -
449 unavail_item_csr.available_quantity))THEN
450 l_unavailable_items_tbl(j).quantity := (l_unavailable_items_tbl(j).Required_quantity -
451 l_unavailable_items_tbl(j).Issued_Quantity -
452 unavail_item_csr.available_quantity);
453 END IF;
454 IF(unavail_item_csr.not_available_quantity = 1 AND l_unavailable_items_tbl(j).Is_serialized = 'Y')THEN
455 OPEN serialized_item_info(unavail_item_csr.organization_id,
456 unavail_item_csr.inventory_item_id, l_subinventory, l_locator_id);
457 FETCH serialized_item_info INTO l_unavailable_items_tbl(j).Serial_Number,
458 l_unavailable_items_tbl(j).Lot,
459 l_unavailable_items_tbl(j).Revision,
460 l_unavailable_items_tbl(j).Subinventory,
461 l_unavailable_items_tbl(j).Locator_id,
462 l_unavailable_items_tbl(j).Locator_segments;
463 CLOSE serialized_item_info;
464 END IF;
465 ELSIF ( p_Mrshl_search_rec.Search_mode = 'UI')THEN
466 l_unavailable_items_tbl(j).quantity := l_unavailable_items_tbl(j).Required_quantity -
467 (l_unavailable_items_tbl(j).Issued_Quantity +
468 unavail_item_csr.available_quantity +
469 unavail_item_csr.not_available_quantity);
470 END IF;
471
472 j:= j+1;
473 END LOOP;
474 END IF;
475 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
476 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
477 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
478 ' :j : ' || j);
479 END IF;
480 x_unavailable_items_tbl := l_unavailable_items_tbl;
481
482 EXCEPTION
483 WHEN FND_API.G_EXC_ERROR THEN
484 x_return_status := FND_API.G_RET_STS_ERROR ;
485 FND_MSG_PUB.count_and_get(
486 p_encoded => FND_API.G_FALSE,
487 p_count => x_msg_count,
488 p_data => x_msg_data);
489
490 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
491 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
492 FND_MSG_PUB.count_and_get(
493 p_encoded => FND_API.G_FALSE,
494 p_count => x_msg_count,
495 p_data => x_msg_data);
496
497 WHEN OTHERS THEN
498 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
499 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
500 THEN
501 FND_MSG_PUB.add_exc_msg(
502 p_pkg_name => G_PKG_NAME,
503 p_procedure_name => l_api_name,
504 p_error_text => SUBSTRB(SQLERRM,1,240));
505 END IF;
506 FND_MSG_PUB.count_and_get(
507 p_encoded => FND_API.G_FALSE,
508 p_count => x_msg_count,
509 p_data => x_msg_data);
510 END Get_unavailable_items;
511
512 PROCEDURE Get_available_items
513 (
514 p_api_version IN NUMBER := 1.0,
515 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
516 p_commit IN VARCHAR2 := FND_API.G_FALSE,
517 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
518 p_default IN VARCHAR2 := FND_API.G_FALSE,
519 p_module_type IN VARCHAR2 := NULL,
520 p_Mrshl_search_rec IN Mrshl_search_rec_type,
521 x_available_items_tbl OUT NOCOPY Available_items_Tbl_Type,
522 x_return_status OUT NOCOPY VARCHAR2,
523 x_msg_count OUT NOCOPY NUMBER,
524 x_msg_data OUT NOCOPY VARCHAR2
525 )IS
526 l_api_name CONSTANT VARCHAR2(30) := 'Get_available_items';
527 l_api_version CONSTANT NUMBER := 1.0;
528 l_available_items_tbl Available_items_Tbl_Type;
529 j NUMBER;
530
531 CURSOR get_root_items_instance_csr(p_visit_id NUMBER,
535 AND WO.visit_id = VTS.visit_id
532 p_item_instance_id NUMBER) IS
533 SELECT VTS.instance_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
534 WO.visit_id = p_visit_id
536 AND WO.visit_task_id = VTS.visit_task_id
537 AND VTS.instance_id = p_item_instance_id
538 AND WO.status_code <> 22
539 AND ROWNUM < 2;
540
541 l_root_instance_id NUMBER;
542
543 CURSOR get_available_items_csr(
544 p_visit_id NUMBER,
545 p_root_instance_id NUMBER,
546 p_workorder_name VARCHAR2,
547 p_part_number VARCHAR2,
548 p_part_desc VARCHAR2,
549 p_locator_id NUMBER,
550 p_subinventory_code VARCHAR2) IS
551 SELECT * FROM(
552 SELECT AWOS.workorder_id,AWOS.job_number,AWOS.job_status_code,AWOS.job_status_meaning,ASML.operation_sequence, MSIK.concatenated_segments ,
553 MSIK.description, MSIK.primary_uom_code,MSIK.primary_unit_of_measure,
554 WIRO.REQUIRED_QUANTITY , WIRO.DATE_REQUIRED,
555 asml.scheduled_quantity ,asml.scheduled_date ,
556 nvl(ahl_pp_materials_pvt.get_issued_qty(msik.organization_id, asml.inventory_item_id, asml.workorder_operation_id), 0) issued_quantity,
557 asml.inventory_item_id,
558 ASML.organization_id,
559 ASML.scheduled_material_id,
560 AWOS.wip_entity_id,
561 AWOS.visit_id,
562 DECODE(MSIK.SERIAL_NUMBER_CONTROL_CODE,1,'N','Y') Is_Serialized,
563 DECODE(NVL(MSIK.LOT_CONTROL_CODE,-1),1,'N','Y') Is_Lot_Controlled,
564 DECODE(NVL(MSIK.REVISION_QTY_CONTROL_CODE,-1),1,'N','Y') Is_Revision_Controlled,
565 GET_ONHAND_AVAILABLE(ASML.organization_id,ASML.inventory_item_id,
566 p_subinventory_code,p_locator_id) AVAILABLE_QUANTITY,
567 ASML.requested_quantity QTY_PER_ASSEMBLY,
568 ASML.scheduled_date EXCEPTION_DATE,
569 (SELECT nvl(SUM(mrv.primary_reservation_quantity),0) FROM mtl_reservations MRV
570 WHERE MRV.INVENTORY_ITEM_ID =WIRO.INVENTORY_ITEM_ID
571 AND MRV. EXTERNAL_SOURCE_CODE = 'AHL'
572 AND MRV.DEMAND_SOURCE_HEADER_ID = WIRO.WIP_ENTITY_ID
573 AND MRV.DEMAND_SOURCE_LINE_ID =WIRO.OPERATION_SEQ_NUM) RESERVED_QUANTITY
574 from ahl_search_workorders_v AWOS, ahl_schedule_materials ASML,
575 WIP_REQUIREMENT_OPERATIONS WIRO, mtl_system_items_kfv MSIK
576 WHERE AWOS.visit_task_id = ASML.visit_task_id
577 AND ASML.inventory_item_id = MSIK.inventory_item_id
578 AND ASML.organization_id = MSIK.organization_id
579 AND AWOS.WIP_ENTITY_ID = WIRO.WIP_ENTITY_ID
580 AND ASML.OPERATION_SEQUENCE = WIRO.OPERATION_SEQ_NUM
581 AND ASML.INVENTORY_ITEM_ID = WIRO.INVENTORY_ITEM_ID
582 AND ASML.ORGANIZATION_ID = WIRO.ORGANIZATION_ID
583 AND asml.status = 'ACTIVE'
584 AND AWOS.workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
585 WO.visit_id = p_visit_id
586 AND WO.visit_id = VTS.visit_id
587 AND WO.visit_task_id = VTS.visit_task_id
588 AND VTS.instance_id IN(
589 SELECT instance_id FROM csi_item_instances WHERE INSTANCE_ID = p_root_instance_id
590 UNION
591 SELECT
592 SUBJECT_ID INSTANCE_ID
593 FROM CSI_II_RELATIONSHIPS
594 WHERE 1=1
595 START WITH OBJECT_ID = p_root_instance_id /*root instance id*/
596 AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
597 AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
598 AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
599 --CONNECT BY SUBJECT_ID = PRIOR OBJECT_ID
600 CONNECT BY OBJECT_ID = PRIOR SUBJECT_ID
601 AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
602 AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
603 AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
604 )))REQ WHERE
605 REQ.concatenated_segments LIKE NVL(p_part_number,'%')
606 AND REQ.description LIKE NVL(p_part_desc,'%')
607 AND REQ.job_number LIKE NVL(p_workorder_name,'%')
608 AND REQ.REQUIRED_QUANTITY > REQ.issued_quantity
609 AND AVAILABLE_QUANTITY > 0;
610
611 CURSOR get_available_items_csr1(
612 p_visit_id NUMBER,
613 p_workorder_name VARCHAR2,
614 p_part_number VARCHAR2,
615 p_part_desc VARCHAR2,
616 p_locator_id NUMBER,
617 p_subinventory_code VARCHAR2) IS
618 SELECT * FROM(SELECT AWOS.workorder_id,AWOS.job_number,AWOS.job_status_code,AWOS.job_status_meaning,ASML.operation_sequence, MSIK.concatenated_segments ,
619 MSIK.description, MSIK.primary_uom_code,MSIK.primary_unit_of_measure,
620 WIRO.REQUIRED_QUANTITY , WIRO.DATE_REQUIRED,
621 asml.scheduled_quantity ,asml.scheduled_date ,
622 nvl(ahl_pp_materials_pvt.get_issued_qty(msik.organization_id, asml.inventory_item_id, asml.workorder_operation_id), 0) issued_quantity,
623 asml.inventory_item_id,
624 ASML.organization_id,
625 ASML.scheduled_material_id,
626 AWOS.wip_entity_id,
627 AWOS.visit_id,
628 DECODE(MSIK.SERIAL_NUMBER_CONTROL_CODE,1,'N','Y') Is_Serialized,
629 DECODE(NVL(MSIK.LOT_CONTROL_CODE,-1),1,'N','Y') Is_Lot_Controlled,
630 DECODE(NVL(MSIK.REVISION_QTY_CONTROL_CODE,-1),1,'N','Y') Is_Revision_Controlled,
631 GET_ONHAND_AVAILABLE(ASML.organization_id,ASML.inventory_item_id,
632 p_subinventory_code,p_locator_id) AVAILABLE_QUANTITY,
633 ASML.requested_quantity QTY_PER_ASSEMBLY,
634 ASML.scheduled_date EXCEPTION_DATE,
635 (SELECT nvl(SUM(mrv.primary_reservation_quantity),0) FROM mtl_reservations MRV
636 WHERE MRV.INVENTORY_ITEM_ID =WIRO.INVENTORY_ITEM_ID
637 AND MRV. EXTERNAL_SOURCE_CODE = 'AHL'
638 AND MRV.DEMAND_SOURCE_HEADER_ID = WIRO.WIP_ENTITY_ID
639 AND MRV.DEMAND_SOURCE_LINE_ID =WIRO.OPERATION_SEQ_NUM) RESERVED_QUANTITY
643 and ASML.organization_id = MSIK.organization_id
640 from ahl_search_workorders_v AWOS, ahl_schedule_materials ASML, WIP_REQUIREMENT_OPERATIONS WIRO, mtl_system_items_kfv MSIK
641 WHERE AWOS.visit_task_id = ASML.visit_task_id
642 and ASML.inventory_item_id = MSIK.inventory_item_id
644 AND AWOS.WIP_ENTITY_ID = WIRO.WIP_ENTITY_ID
645 AND ASML.OPERATION_SEQUENCE = WIRO.OPERATION_SEQ_NUM
646 AND ASML.INVENTORY_ITEM_ID = WIRO.INVENTORY_ITEM_ID
647 AND ASML.ORGANIZATION_ID = WIRO.ORGANIZATION_ID and asml.status = 'ACTIVE'
648 AND AWOS.workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
649 WO.visit_id = p_visit_id
650 AND WO.visit_id = VTS.visit_id
651 AND WO.visit_task_id = VTS.visit_task_id
652 ))REQ WHERE
653 REQ.concatenated_segments LIKE NVL(p_part_number,'%')
654 AND REQ.description LIKE NVL(p_part_desc,'%')
655 AND REQ.job_number LIKE NVL(p_workorder_name,'%')
656 AND REQ.REQUIRED_QUANTITY > REQ.issued_quantity
657 AND AVAILABLE_QUANTITY > 0;
658
659 CURSOR visit_info_csr(p_visit_id NUMBER)IS
660 SELECT inv_locator_id, subinventory, locator_segments FROM ahl_prd_visits_v
661 WHERE visit_id = p_visit_id;
662 l_locator_id NUMBER;
663 l_subinventory VARCHAR2(10);
664 l_locator_segments VARCHAR2(240);
665
666 CURSOR serialized_item_info(p_org_id NUMBER,
667 p_inventory_item_id NUMBER,
668 p_subinventory_code VARCHAR2,
669 p_locator_id NUMBER) IS
670 select msn.serial_number , msn.lot_number ,msn.revision
671 from mtl_serial_numbers msn,
672 mtl_system_items_kfv mkfv,
673 mtl_item_locations_kfv mil
674 where msn.inventory_item_id = mkfv.inventory_item_id
675 and msn.current_organization_id=mkfv.organization_id
676 and msn.current_locator_id = mil.INVENTORY_locatION_ID (+)
677 and mkfv.serial_number_control_code <> 1
678 and msn.current_status=3
679 and msn.inventory_item_id = p_inventory_item_id
680 and msn.current_organization_id = p_org_id
681 and msn.current_locator_id = p_locator_id
682 and msn.current_subinventory_code = p_subinventory_code;
683
684
685
686 BEGIN
687 x_return_status := FND_API.G_RET_STS_SUCCESS;
688
689 --Standard call to check for call compatibility.
690 IF NOT FND_API.compatible_api_call(
691 l_api_version,
692 p_api_version,
693 l_api_name,
694 G_PKG_NAME)
695 THEN
696 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
697 END IF;
698
699 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
700 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
701 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
702 ' :p_Mrshl_search_rec.Visit_id : ' || p_Mrshl_search_rec.Visit_id
703 );
704 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
705 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
706 ' :p_Mrshl_search_rec.item_instance_id : ' || p_Mrshl_search_rec.item_instance_id
707 );
708 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
709 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
710 ' :p_Mrshl_search_rec.Workorder_name : ' || p_Mrshl_search_rec.Workorder_name
711 );
712 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
713 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
714 ' :p_Mrshl_search_rec.Item_name : ' || p_Mrshl_search_rec.Item_name
715 );
716 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
717 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
718 ' :p_Mrshl_search_rec.Item_Desc : ' || p_Mrshl_search_rec.Item_Desc
719 );
720 END IF;
721
722 OPEN visit_info_csr(p_Mrshl_search_rec.Visit_id);
723 FETCH visit_info_csr INTO l_locator_id, l_subinventory,l_locator_segments;
724 CLOSE visit_info_csr;
725
726 IF(p_Mrshl_search_rec.item_instance_id IS NOT NULL)THEN
727 /*OPEN get_root_items_instance_csr(p_Mrshl_search_rec.Visit_id,
728 p_Mrshl_search_rec.item_instance_id);
729 FETCH get_root_items_instance_csr INTO l_root_instance_id;
730 IF(get_root_items_instance_csr%NOTFOUND)THEN
731 RETURN;
732 END IF;
733 CLOSE get_root_items_instance_csr;*/
734 l_root_instance_id := p_Mrshl_search_rec.item_instance_id;
735 END IF;
736
737 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
738 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
739 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
740 ' :l_root_instance_id : ' || l_root_instance_id
741 );
742 END IF;
743 j := 1;
744
745 IF(l_root_instance_id IS NOT NULL)THEN
746 FOR avail_item_csr IN get_available_items_csr(
747 p_Mrshl_search_rec.Visit_id,
748 l_root_instance_id,
749 p_Mrshl_search_rec.Workorder_name,
750 p_Mrshl_search_rec.Item_name,
751 p_Mrshl_search_rec.Item_Desc,
752 NVL(l_locator_id,-1) ,
753 NVL(l_subinventory,'x')) LOOP
754 l_available_items_tbl(j).Scheduled_material_id := avail_item_csr.scheduled_material_id;
755 l_available_items_tbl(j).Inventory_item_id := avail_item_csr.inventory_item_id;
756 l_available_items_tbl(j).Item_name := avail_item_csr.concatenated_segments;
760 l_available_items_tbl(j).Organization_id := avail_item_csr.Organization_id;
757 l_available_items_tbl(j).Item_Desc := avail_item_csr.description;
758 l_available_items_tbl(j).Workorder_id := avail_item_csr.Workorder_id;
759 l_available_items_tbl(j).Workorder_Name := avail_item_csr.job_number;
761 l_available_items_tbl(j).Visit_id := avail_item_csr.Visit_id;
762 l_available_items_tbl(j).Wip_Entity_Id := avail_item_csr.Wip_Entity_Id;
763 l_available_items_tbl(j).wo_status := avail_item_csr.job_status_meaning;
764 l_available_items_tbl(j).wo_status_code := avail_item_csr.job_status_code;
765 l_available_items_tbl(j).Op_seq := avail_item_csr.operation_sequence;
766 l_available_items_tbl(j).UOM := avail_item_csr.primary_uom_code;
767 l_available_items_tbl(j).UOM_DESC := avail_item_csr.primary_unit_of_measure;
768 l_available_items_tbl(j).Required_quantity := avail_item_csr.REQUIRED_QUANTITY;
769 l_available_items_tbl(j).Required_date := avail_item_csr.DATE_REQUIRED;
770 l_available_items_tbl(j).Issued_Quantity := avail_item_csr.issued_quantity;
771 l_available_items_tbl(j).Scheduled_date := avail_item_csr.scheduled_date;
772 l_available_items_tbl(j).Scheduled_Quantity := avail_item_csr.scheduled_quantity;
773 l_available_items_tbl(j).Subinventory := l_subinventory;
774 l_available_items_tbl(j).Locator_id := l_locator_id;
775 l_available_items_tbl(j).Locator_segments := l_locator_segments;
776 l_available_items_tbl(j).Qty_per_assembly := avail_item_csr.Qty_per_assembly;
777 l_available_items_tbl(j).Is_serialized := avail_item_csr.Is_serialized;
778 l_available_items_tbl(j).Is_Lot_Controlled := avail_item_csr.Is_Lot_Controlled;
779 l_available_items_tbl(j).Is_Revision_Controlled := avail_item_csr.Is_Revision_Controlled;
780 l_available_items_tbl(j).quantity := avail_item_csr.available_quantity;
781 l_available_items_tbl(j).onhand_quantity := avail_item_csr.available_quantity;
782 l_available_items_tbl(j).exception_date := avail_item_csr.exception_date;
783 l_available_items_tbl(j).reserved_quantity := avail_item_csr.reserved_quantity;
784 IF(l_available_items_tbl(j).quantity >
785 (l_available_items_tbl(j).Required_quantity -
786 l_available_items_tbl(j).Issued_Quantity))THEN
787 l_available_items_tbl(j).quantity := (l_available_items_tbl(j).Required_quantity -
788 l_available_items_tbl(j).Issued_Quantity);
789 END IF;
790 IF(avail_item_csr.available_quantity = 1 AND l_available_items_tbl(j).Is_serialized = 'Y')THEN
791 OPEN serialized_item_info(avail_item_csr.organization_id,
792 avail_item_csr.inventory_item_id, l_subinventory, l_locator_id);
793 FETCH serialized_item_info INTO l_available_items_tbl(j).Serial_Number,
794 l_available_items_tbl(j).Lot,
795 l_available_items_tbl(j).Revision;
796 CLOSE serialized_item_info;
797 END IF;
798 j:= j+1;
799 END LOOP;
800 ELSE
801 FOR avail_item_csr IN get_available_items_csr1(
802 p_Mrshl_search_rec.Visit_id,
803 p_Mrshl_search_rec.Workorder_name,
804 p_Mrshl_search_rec.Item_name,
805 p_Mrshl_search_rec.Item_Desc,
806 NVL(l_locator_id,-1) ,
807 NVL(l_subinventory,'x')) LOOP
808 l_available_items_tbl(j).Scheduled_material_id := avail_item_csr.scheduled_material_id;
809 l_available_items_tbl(j).Inventory_item_id := avail_item_csr.inventory_item_id;
810 l_available_items_tbl(j).Item_name := avail_item_csr.concatenated_segments;
811 l_available_items_tbl(j).Item_Desc := avail_item_csr.description;
812 l_available_items_tbl(j).Workorder_id := avail_item_csr.Workorder_id;
813 l_available_items_tbl(j).Workorder_Name := avail_item_csr.job_number;
814 l_available_items_tbl(j).Organization_id := avail_item_csr.Organization_id;
815 l_available_items_tbl(j).Visit_id := avail_item_csr.Visit_id;
816 l_available_items_tbl(j).Wip_Entity_Id := avail_item_csr.Wip_Entity_Id;
817 l_available_items_tbl(j).wo_status := avail_item_csr.job_status_meaning;
818 l_available_items_tbl(j).wo_status_code := avail_item_csr.job_status_code;
819 l_available_items_tbl(j).Op_seq := avail_item_csr.operation_sequence;
820 l_available_items_tbl(j).UOM := avail_item_csr.primary_uom_code;
821 l_available_items_tbl(j).UOM_DESC := avail_item_csr.primary_unit_of_measure;
822 l_available_items_tbl(j).Required_quantity := avail_item_csr.REQUIRED_QUANTITY;
823 l_available_items_tbl(j).Required_date := avail_item_csr.DATE_REQUIRED;
824 l_available_items_tbl(j).Issued_Quantity := avail_item_csr.issued_quantity;
825 l_available_items_tbl(j).Scheduled_date := avail_item_csr.scheduled_date;
826 l_available_items_tbl(j).Scheduled_Quantity := avail_item_csr.scheduled_quantity;
827 l_available_items_tbl(j).Qty_per_assembly := avail_item_csr.Qty_per_assembly;
828 l_available_items_tbl(j).Subinventory := l_subinventory;
829 l_available_items_tbl(j).Locator_id := l_locator_id;
830 l_available_items_tbl(j).Locator_segments := l_locator_segments;
831 l_available_items_tbl(j).Is_serialized := avail_item_csr.Is_serialized;
832 l_available_items_tbl(j).Is_Lot_Controlled := avail_item_csr.Is_Lot_Controlled;
833 l_available_items_tbl(j).Is_Revision_Controlled := avail_item_csr.Is_Revision_Controlled;
837 l_available_items_tbl(j).reserved_quantity := avail_item_csr.reserved_quantity;
834 l_available_items_tbl(j).quantity := avail_item_csr.available_quantity;
835 l_available_items_tbl(j).onhand_quantity := avail_item_csr.available_quantity;
836 l_available_items_tbl(j).exception_date := avail_item_csr.exception_date;
838 IF(l_available_items_tbl(j).quantity >
839 (l_available_items_tbl(j).Required_quantity -
840 l_available_items_tbl(j).Issued_Quantity))THEN
841 l_available_items_tbl(j).quantity := (l_available_items_tbl(j).Required_quantity -
842 l_available_items_tbl(j).Issued_Quantity);
843 END IF;
844 IF(avail_item_csr.available_quantity = 1 AND l_available_items_tbl(j).Is_serialized = 'Y')THEN
845 OPEN serialized_item_info(avail_item_csr.organization_id,
846 avail_item_csr.inventory_item_id, l_subinventory, l_locator_id);
847 FETCH serialized_item_info INTO l_available_items_tbl(j).Serial_Number,
848 l_available_items_tbl(j).Lot,
849 l_available_items_tbl(j).Revision;
850 CLOSE serialized_item_info;
851 END IF;
852 j:= j+1;
853 END LOOP;
854 END IF;
855 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
856 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
857 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
858 ' Count : ' || to_char(j -1));
859 END IF;
860 x_available_items_tbl := l_available_items_tbl;
861 EXCEPTION
862 WHEN FND_API.G_EXC_ERROR THEN
863 x_return_status := FND_API.G_RET_STS_ERROR ;
864 FND_MSG_PUB.count_and_get(
865 p_encoded => FND_API.G_FALSE,
866 p_count => x_msg_count,
867 p_data => x_msg_data);
868
869 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
870 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
871 FND_MSG_PUB.count_and_get(
872 p_encoded => FND_API.G_FALSE,
873 p_count => x_msg_count,
874 p_data => x_msg_data);
875
876 WHEN OTHERS THEN
877 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
878 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
879 THEN
880 FND_MSG_PUB.add_exc_msg(
881 p_pkg_name => G_PKG_NAME,
882 p_procedure_name => l_api_name,
883 p_error_text => SUBSTRB(SQLERRM,1,240));
884 END IF;
885 FND_MSG_PUB.count_and_get(
886 p_encoded => FND_API.G_FALSE,
887 p_count => x_msg_count,
888 p_data => x_msg_data);
889 END Get_available_items;
890 /*
891 p_mode can be as follows "IC", "CO" as follows
892 IC - In complete
893 CO - Completed
894 TO - Total
895 */
896 FUNCTION Get_workorder_count
897 (
898 p_visit_id IN NUMBER,
899 p_item_instance_id IN NUMBER,
900 p_mode IN VARCHAR2
901 ) RETURN NUMBER IS
902
903 CURSOR comp_inst_wo_count_csr(p_visit_id NUMBER,p_item_instance_id NUMBER) IS
904 SELECT COUNT(*)
905 FROM AHL_SEARCH_WORKORDERS_V
906 WHERE visit_id = p_visit_id
907 AND JOB_STATUS_CODE IN('4','5','7','12')
908 AND workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
909 WO.visit_id = p_visit_id
910 AND WO.visit_id = VTS.visit_id
911 AND WO.visit_task_id = VTS.visit_task_id
912 AND VTS.instance_id = p_item_instance_id);
913
914 CURSOR comp_cumm_inst_wo_count_csr(p_visit_id NUMBER,p_item_instance_id NUMBER) IS
915 SELECT COUNT(*)
916 FROM AHL_SEARCH_WORKORDERS_V
917 WHERE visit_id = p_visit_id
918 AND JOB_STATUS_CODE IN('4','5','7','12')
919 AND workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
920 WO.visit_id = p_visit_id
921 AND WO.visit_id = VTS.visit_id
922 AND WO.visit_task_id = VTS.visit_task_id
923 AND VTS.instance_id IN(
924 SELECT instance_id FROM csi_item_instances WHERE INSTANCE_ID = p_item_instance_id
925 UNION
926 SELECT
927 SUBJECT_ID INSTANCE_ID
928 FROM CSI_II_RELATIONSHIPS
929 WHERE 1=1
930 START WITH OBJECT_ID = p_item_instance_id /*root instance id*/
931 AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
932 AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
933 AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
934 --CONNECT BY SUBJECT_ID = PRIOR OBJECT_ID
935 CONNECT BY OBJECT_ID = PRIOR SUBJECT_ID
936 AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
937 AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
938 AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
939 ));
940
941
942 CURSOR total_inst_wo_count_csr(p_visit_id NUMBER,p_item_instance_id NUMBER) IS
943 SELECT COUNT(*)
944 FROM AHL_SEARCH_WORKORDERS_V
945 WHERE visit_id = p_visit_id
946 AND JOB_STATUS_CODE <> '22'
947 AND workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
948 WO.visit_id = p_visit_id
949 AND WO.visit_id = VTS.visit_id
950 AND WO.visit_task_id = VTS.visit_task_id
951 AND VTS.instance_id = p_item_instance_id);
952
953 CURSOR total_cumm_inst_wo_count_csr(p_visit_id NUMBER,p_item_instance_id NUMBER) IS
954 SELECT COUNT(*)
955 FROM AHL_SEARCH_WORKORDERS_V
956 WHERE visit_id = p_visit_id
960 AND WO.visit_id = VTS.visit_id
957 AND JOB_STATUS_CODE <> '22'
958 AND workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
959 WO.visit_id = p_visit_id
961 AND WO.visit_task_id = VTS.visit_task_id
962 AND VTS.instance_id IN(
963 SELECT instance_id FROM csi_item_instances WHERE INSTANCE_ID = p_item_instance_id
964 UNION
965 SELECT
966 SUBJECT_ID INSTANCE_ID
967 FROM CSI_II_RELATIONSHIPS
968 WHERE 1=1
969 START WITH OBJECT_ID = p_item_instance_id /*root instance id*/
970 AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
971 AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
972 AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
973 --CONNECT BY SUBJECT_ID = PRIOR OBJECT_ID
974 CONNECT BY OBJECT_ID = PRIOR SUBJECT_ID
975 AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
976 AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
977 AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
978 ));
979
980 CURSOR comp_visit_wo_count_csr(p_visit_id NUMBER) IS
981 SELECT COUNT(*)
982 FROM AHL_SEARCH_WORKORDERS_V
983 WHERE visit_id = p_visit_id
984 AND JOB_STATUS_CODE IN('4','5','7','12');
985
986
987 CURSOR total_visit_wo_count_csr(p_visit_id NUMBER) IS
988 SELECT COUNT(*)
989 FROM AHL_SEARCH_WORKORDERS_V
990 WHERE visit_id = p_visit_id
991 AND JOB_STATUS_CODE <> '22';
992
993 l_quantity NUMBER;
994
995
996 BEGIN
997 mo_global.init('AHL');
998 l_quantity :=0;
999 IF(p_mode IN ('CO','COC'))THEN
1000 IF(p_item_instance_id IS NULL)THEN
1001 OPEN comp_visit_wo_count_csr(p_visit_id);
1002 FETCH comp_visit_wo_count_csr INTO l_quantity;
1003 CLOSE comp_visit_wo_count_csr;
1004 ELSIF p_mode = 'CO' THEN
1005 OPEN comp_inst_wo_count_csr(p_visit_id,p_item_instance_id);
1006 FETCH comp_inst_wo_count_csr INTO l_quantity;
1007 CLOSE comp_inst_wo_count_csr;
1008 ELSIF p_mode = 'COC' THEN
1009 OPEN comp_cumm_inst_wo_count_csr(p_visit_id,p_item_instance_id);
1010 FETCH comp_cumm_inst_wo_count_csr INTO l_quantity;
1011 CLOSE comp_cumm_inst_wo_count_csr;
1012 END IF;
1013 ELSIF (p_mode IN ('TO','TOC'))THEN
1014 IF(p_item_instance_id IS NULL)THEN
1015 OPEN total_visit_wo_count_csr(p_visit_id);
1016 FETCH total_visit_wo_count_csr INTO l_quantity;
1017 CLOSE total_visit_wo_count_csr;
1018 ELSIF p_mode = 'TO' THEN
1019 OPEN total_inst_wo_count_csr(p_visit_id,p_item_instance_id);
1020 FETCH total_inst_wo_count_csr INTO l_quantity;
1021 CLOSE total_inst_wo_count_csr;
1022 ELSIF p_mode = 'TOC' THEN
1023 OPEN total_cumm_inst_wo_count_csr(p_visit_id,p_item_instance_id);
1024 FETCH total_cumm_inst_wo_count_csr INTO l_quantity;
1025 CLOSE total_cumm_inst_wo_count_csr;
1026 END IF;
1027 END IF;
1028 RETURN NVL(l_quantity,0);
1029 END Get_workorder_count;
1030 /*
1031 p_mode can be as follows
1032 MU - Material unavailable
1033 MU - Material unavailable
1034 MUC - Material unavailable Cummulative
1035 MA - Material Avaialble
1036 MAC - Material Avaialble Cummulative
1037 MR - Material Required
1038 MRC - Material Required Cummulative
1039 MI - Material Issued
1040 MIC - Material Issued Cummulative
1041 */
1042 FUNCTION Get_item_count
1043 (
1044 p_visit_id IN NUMBER,
1045 p_item_instance_id IN NUMBER :=NULL,
1046 p_mode IN VARCHAR2
1047 ) RETURN NUMBER IS
1048
1049 CURSOR get_inst_required_qty(p_visit_id NUMBER,p_item_instance_id NUMBER) IS
1050 SELECT SUM(WIRO.REQUIRED_QUANTITY)
1051 from ahl_workorders AWOS, ahl_schedule_materials ASML, WIP_REQUIREMENT_OPERATIONS WIRO, mtl_system_items_kfv MSIK
1052 WHERE AWOS.visit_task_id = ASML.visit_task_id
1053 and ASML.inventory_item_id = MSIK.inventory_item_id
1054 and ASML.organization_id = MSIK.organization_id
1055 AND AWOS.WIP_ENTITY_ID = WIRO.WIP_ENTITY_ID
1056 AND ASML.OPERATION_SEQUENCE = WIRO.OPERATION_SEQ_NUM
1057 AND ASML.INVENTORY_ITEM_ID = WIRO.INVENTORY_ITEM_ID
1058 AND ASML.ORGANIZATION_ID = WIRO.ORGANIZATION_ID and asml.status = 'ACTIVE'
1059 AND AWOS.workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
1060 WO.visit_id = p_visit_id
1061 AND WO.visit_id = VTS.visit_id
1062 AND WO.visit_task_id = VTS.visit_task_id
1063 AND VTS.instance_id = p_item_instance_id);
1064
1065 CURSOR get_cumm_inst_required_qty(p_visit_id NUMBER,p_item_instance_id NUMBER)IS
1066 SELECT SUM(WIRO.REQUIRED_QUANTITY)
1067 from ahl_workorders AWOS, ahl_schedule_materials ASML, WIP_REQUIREMENT_OPERATIONS WIRO, mtl_system_items_kfv MSIK
1068 WHERE AWOS.visit_task_id = ASML.visit_task_id
1069 and ASML.inventory_item_id = MSIK.inventory_item_id
1070 and ASML.organization_id = MSIK.organization_id
1071 AND AWOS.WIP_ENTITY_ID = WIRO.WIP_ENTITY_ID
1072 AND ASML.OPERATION_SEQUENCE = WIRO.OPERATION_SEQ_NUM
1073 AND ASML.INVENTORY_ITEM_ID = WIRO.INVENTORY_ITEM_ID
1074 AND ASML.ORGANIZATION_ID = WIRO.ORGANIZATION_ID and asml.status = 'ACTIVE'
1075 AND AWOS.workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
1076 WO.visit_id = p_visit_id
1077 AND WO.visit_id = VTS.visit_id
1078 AND WO.visit_task_id = VTS.visit_task_id
1079 AND VTS.instance_id IN(
1083 SUBJECT_ID INSTANCE_ID
1080 SELECT instance_id FROM csi_item_instances WHERE INSTANCE_ID = p_item_instance_id
1081 UNION
1082 SELECT
1084 FROM CSI_II_RELATIONSHIPS
1085 WHERE 1=1
1086 START WITH OBJECT_ID = p_item_instance_id /*root instance id*/
1087 AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
1088 AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
1089 AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
1090 --CONNECT BY SUBJECT_ID = PRIOR OBJECT_ID
1091 CONNECT BY OBJECT_ID = PRIOR SUBJECT_ID
1092 AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
1093 AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
1094 AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
1095 ));
1096
1097 CURSOR get_visit_required_qty(p_visit_id NUMBER) IS
1098 SELECT SUM(WIRO.REQUIRED_QUANTITY)
1099 from ahl_workorders AWOS, ahl_schedule_materials ASML, WIP_REQUIREMENT_OPERATIONS WIRO, mtl_system_items_kfv MSIK
1100 WHERE AWOS.visit_task_id = ASML.visit_task_id
1101 and ASML.inventory_item_id = MSIK.inventory_item_id
1102 and ASML.organization_id = MSIK.organization_id
1103 AND AWOS.WIP_ENTITY_ID = WIRO.WIP_ENTITY_ID
1104 AND ASML.OPERATION_SEQUENCE = WIRO.OPERATION_SEQ_NUM
1105 AND ASML.INVENTORY_ITEM_ID = WIRO.INVENTORY_ITEM_ID
1106 AND ASML.ORGANIZATION_ID = WIRO.ORGANIZATION_ID and asml.status = 'ACTIVE'
1107 AND AWOS.workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
1108 WO.visit_id = p_visit_id
1109 AND WO.visit_id = VTS.visit_id
1110 AND WO.visit_task_id = VTS.visit_task_id);
1111
1112 CURSOR get_inst_issued_qty(p_visit_id NUMBER,p_item_instance_id NUMBER) IS
1113 SELECT SUM( nvl(ahl_pp_materials_pvt.get_issued_qty(msik.organization_id, asml.inventory_item_id, asml.workorder_operation_id), 0)) issued_qty
1114 from ahl_workorders AWOS, ahl_schedule_materials ASML, WIP_REQUIREMENT_OPERATIONS WIRO, mtl_system_items_kfv MSIK
1115 WHERE AWOS.visit_task_id = ASML.visit_task_id
1116 and ASML.inventory_item_id = MSIK.inventory_item_id
1117 and ASML.organization_id = MSIK.organization_id
1118 AND AWOS.WIP_ENTITY_ID = WIRO.WIP_ENTITY_ID
1119 AND ASML.OPERATION_SEQUENCE = WIRO.OPERATION_SEQ_NUM
1120 AND ASML.INVENTORY_ITEM_ID = WIRO.INVENTORY_ITEM_ID
1121 AND ASML.ORGANIZATION_ID = WIRO.ORGANIZATION_ID and asml.status = 'ACTIVE'
1122 AND AWOS.workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
1123 WO.visit_id = p_visit_id
1124 AND WO.visit_id = VTS.visit_id
1125 AND WO.visit_task_id = VTS.visit_task_id
1126 AND VTS.instance_id = p_item_instance_id);
1127
1128 CURSOR get_cumm_inst_issued_qty(p_visit_id NUMBER,p_item_instance_id NUMBER)IS
1129 SELECT SUM( nvl(ahl_pp_materials_pvt.get_issued_qty(msik.organization_id, asml.inventory_item_id, asml.workorder_operation_id), 0)) issued_qty
1130 from ahl_workorders AWOS, ahl_schedule_materials ASML, WIP_REQUIREMENT_OPERATIONS WIRO, mtl_system_items_kfv MSIK
1131 WHERE AWOS.visit_task_id = ASML.visit_task_id
1132 and ASML.inventory_item_id = MSIK.inventory_item_id
1133 and ASML.organization_id = MSIK.organization_id
1134 AND AWOS.WIP_ENTITY_ID = WIRO.WIP_ENTITY_ID
1135 AND ASML.OPERATION_SEQUENCE = WIRO.OPERATION_SEQ_NUM
1136 AND ASML.INVENTORY_ITEM_ID = WIRO.INVENTORY_ITEM_ID
1137 AND ASML.ORGANIZATION_ID = WIRO.ORGANIZATION_ID and asml.status = 'ACTIVE'
1138 AND AWOS.workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
1139 WO.visit_id = p_visit_id
1140 AND WO.visit_id = VTS.visit_id
1141 AND WO.visit_task_id = VTS.visit_task_id
1142 AND VTS.instance_id IN(
1143 SELECT instance_id FROM csi_item_instances WHERE INSTANCE_ID = p_item_instance_id
1144 UNION
1145 SELECT
1146 SUBJECT_ID INSTANCE_ID
1147 FROM CSI_II_RELATIONSHIPS
1148 WHERE 1=1
1149 START WITH OBJECT_ID = p_item_instance_id /*root instance id*/
1150 AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
1151 AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
1152 AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
1153 --CONNECT BY SUBJECT_ID = PRIOR OBJECT_ID
1154 CONNECT BY OBJECT_ID = PRIOR SUBJECT_ID
1155 AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
1156 AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
1157 AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
1158 ));
1159
1160 CURSOR get_visit_issued_qty(p_visit_id NUMBER) IS
1161 SELECT SUM( nvl(ahl_pp_materials_pvt.get_issued_qty(msik.organization_id, asml.inventory_item_id, asml.workorder_operation_id), 0)) issued_qty
1162 from ahl_workorders AWOS, ahl_schedule_materials ASML, WIP_REQUIREMENT_OPERATIONS WIRO, mtl_system_items_kfv MSIK
1163 WHERE AWOS.visit_task_id = ASML.visit_task_id
1164 and ASML.inventory_item_id = MSIK.inventory_item_id
1165 and ASML.organization_id = MSIK.organization_id
1166 AND AWOS.WIP_ENTITY_ID = WIRO.WIP_ENTITY_ID
1167 AND ASML.OPERATION_SEQUENCE = WIRO.OPERATION_SEQ_NUM
1168 AND ASML.INVENTORY_ITEM_ID = WIRO.INVENTORY_ITEM_ID
1169 AND ASML.ORGANIZATION_ID = WIRO.ORGANIZATION_ID and asml.status = 'ACTIVE'
1170 AND AWOS.workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
1171 WO.visit_id = p_visit_id
1172 AND WO.visit_id = VTS.visit_id
1173 AND WO.visit_task_id = VTS.visit_task_id);
1174
1175 CURSOR get_inst_avail_qty(p_visit_id NUMBER,p_item_instance_id NUMBER,
1176 p_subinventory_code VARCHAR2,
1177 p_locator_id NUMBER) IS
1178 SELECT SUM(WIRO.REQUIRED_QUANTITY) REQUIRED_QUANTITY,
1179 SUM( nvl(ahl_pp_materials_pvt.get_issued_qty(msik.organization_id, asml.inventory_item_id, asml.workorder_operation_id), 0)) issued_qty,
1180 SUM(GET_ONHAND_AVAILABLE(
1184 p_locator_id)) available_quantity
1181 ASML.organization_id,
1182 ASML.inventory_item_id,
1183 p_subinventory_code,
1185 from ahl_workorders AWOS, ahl_schedule_materials ASML, WIP_REQUIREMENT_OPERATIONS WIRO, mtl_system_items_kfv MSIK
1186 WHERE AWOS.visit_task_id = ASML.visit_task_id
1187 and ASML.inventory_item_id = MSIK.inventory_item_id
1188 and ASML.organization_id = MSIK.organization_id
1189 AND AWOS.WIP_ENTITY_ID = WIRO.WIP_ENTITY_ID
1190 AND ASML.OPERATION_SEQUENCE = WIRO.OPERATION_SEQ_NUM
1191 AND ASML.INVENTORY_ITEM_ID = WIRO.INVENTORY_ITEM_ID
1192 AND ASML.ORGANIZATION_ID = WIRO.ORGANIZATION_ID and asml.status = 'ACTIVE'
1193 AND AWOS.workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
1194 WO.visit_id = p_visit_id
1195 AND WO.visit_id = VTS.visit_id
1196 AND WO.visit_task_id = VTS.visit_task_id
1197 AND VTS.instance_id = p_item_instance_id)
1198 GROUP BY asml.inventory_item_id,ASML.organization_id,AWOS.WIP_ENTITY_ID,ASML.OPERATION_SEQUENCE;
1199
1200 CURSOR get_cumm_inst_avail_qty(p_visit_id NUMBER,p_item_instance_id NUMBER,
1201 p_subinventory_code VARCHAR2,
1202 p_locator_id NUMBER)IS
1203 SELECT SUM(WIRO.REQUIRED_QUANTITY) REQUIRED_QUANTITY,
1204 SUM( nvl(ahl_pp_materials_pvt.get_issued_qty(msik.organization_id, asml.inventory_item_id, asml.workorder_operation_id), 0)) issued_qty,
1205 SUM(GET_ONHAND_AVAILABLE(
1206 ASML.organization_id,
1207 ASML.inventory_item_id,
1208 p_subinventory_code,
1209 p_locator_id)) available_quantity
1210 from ahl_workorders AWOS, ahl_schedule_materials ASML, WIP_REQUIREMENT_OPERATIONS WIRO, mtl_system_items_kfv MSIK
1211 WHERE AWOS.visit_task_id = ASML.visit_task_id
1212 and ASML.inventory_item_id = MSIK.inventory_item_id
1213 and ASML.organization_id = MSIK.organization_id
1214 AND AWOS.WIP_ENTITY_ID = WIRO.WIP_ENTITY_ID
1215 AND ASML.OPERATION_SEQUENCE = WIRO.OPERATION_SEQ_NUM
1216 AND ASML.INVENTORY_ITEM_ID = WIRO.INVENTORY_ITEM_ID
1217 AND ASML.ORGANIZATION_ID = WIRO.ORGANIZATION_ID and asml.status = 'ACTIVE'
1218 AND AWOS.workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
1219 WO.visit_id = p_visit_id
1220 AND WO.visit_id = VTS.visit_id
1221 AND WO.visit_task_id = VTS.visit_task_id
1222 AND VTS.instance_id IN(
1223 SELECT instance_id FROM csi_item_instances WHERE INSTANCE_ID = p_item_instance_id
1224 UNION
1225 SELECT
1226 SUBJECT_ID INSTANCE_ID
1227 FROM CSI_II_RELATIONSHIPS
1228 WHERE 1=1
1229 START WITH OBJECT_ID = p_item_instance_id /*root instance id*/
1230 AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
1231 AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
1232 AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
1233 --CONNECT BY SUBJECT_ID = PRIOR OBJECT_ID
1234 CONNECT BY OBJECT_ID = PRIOR SUBJECT_ID
1235 AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
1236 AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
1237 AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
1238 ))
1239 GROUP BY asml.inventory_item_id,ASML.organization_id,AWOS.WIP_ENTITY_ID,ASML.OPERATION_SEQUENCE;
1240
1241 CURSOR get_visit_avail_qty(p_visit_id NUMBER,
1242 p_subinventory_code VARCHAR2,
1243 p_locator_id NUMBER) IS
1244 SELECT SUM(WIRO.REQUIRED_QUANTITY) REQUIRED_QUANTITY,
1245 SUM( nvl(ahl_pp_materials_pvt.get_issued_qty(msik.organization_id, asml.inventory_item_id, asml.workorder_operation_id), 0)) issued_qty,
1246 SUM(GET_ONHAND_AVAILABLE(
1247 ASML.organization_id,
1248 ASML.inventory_item_id,
1249 p_subinventory_code,
1250 p_locator_id)) available_quantity
1251 from ahl_workorders AWOS, ahl_schedule_materials ASML, WIP_REQUIREMENT_OPERATIONS WIRO, mtl_system_items_kfv MSIK
1252 WHERE AWOS.visit_task_id = ASML.visit_task_id
1253 and ASML.inventory_item_id = MSIK.inventory_item_id
1254 and ASML.organization_id = MSIK.organization_id
1255 AND AWOS.WIP_ENTITY_ID = WIRO.WIP_ENTITY_ID
1256 AND ASML.OPERATION_SEQUENCE = WIRO.OPERATION_SEQ_NUM
1257 AND ASML.INVENTORY_ITEM_ID = WIRO.INVENTORY_ITEM_ID
1258 AND ASML.ORGANIZATION_ID = WIRO.ORGANIZATION_ID and asml.status = 'ACTIVE'
1259 AND AWOS.workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
1260 WO.visit_id = p_visit_id
1261 AND WO.visit_id = VTS.visit_id
1262 AND WO.visit_task_id = VTS.visit_task_id)
1263 GROUP BY asml.inventory_item_id,ASML.organization_id,AWOS.WIP_ENTITY_ID,ASML.OPERATION_SEQUENCE;
1264
1265 CURSOR visit_info_csr(p_visit_id NUMBER)IS
1266 SELECT inv_locator_id, subinventory, locator_segments FROM ahl_prd_visits_v
1267 WHERE visit_id = p_visit_id;
1268 l_locator_id NUMBER;
1269 l_subinventory VARCHAR2(10);
1270 l_locator_segments VARCHAR2(240);
1271
1272
1273 l_quantity NUMBER;
1274 l_req_quantity NUMBER;
1275 l_issued_qty NUMBER;
1276 l_avail_qty NUMBER;
1277 l_unavail_qty NUMBER;
1278
1279 BEGIN
1280 mo_global.init('AHL');
1281 IF(p_mode IN ('MR','MRC'))THEN
1282 IF(p_item_instance_id IS NULL)THEN
1283 OPEN get_visit_required_qty(p_visit_id);
1284 FETCH get_visit_required_qty INTO l_quantity;
1285 CLOSE get_visit_required_qty;
1286 ELSIF p_mode = 'MR' THEN
1287 OPEN get_inst_required_qty(p_visit_id,p_item_instance_id);
1288 FETCH get_inst_required_qty INTO l_quantity;
1289 CLOSE get_inst_required_qty;
1290 ELSIF p_mode = 'MRC' THEN
1291 OPEN get_cumm_inst_required_qty(p_visit_id,p_item_instance_id);
1292 FETCH get_cumm_inst_required_qty INTO l_quantity;
1296 IF(p_item_instance_id IS NULL)THEN
1293 CLOSE get_cumm_inst_required_qty;
1294 END IF;
1295 ELSIF (p_mode IN ('MI','MIC'))THEN
1297 OPEN get_visit_issued_qty(p_visit_id);
1298 FETCH get_visit_issued_qty INTO l_quantity;
1299 CLOSE get_visit_issued_qty;
1300 ELSIF p_mode = 'MI' THEN
1301 OPEN get_inst_issued_qty(p_visit_id,p_item_instance_id);
1302 FETCH get_inst_issued_qty INTO l_quantity;
1303 CLOSE get_inst_issued_qty;
1304 ELSIF p_mode = 'MIC' THEN
1305 OPEN get_cumm_inst_issued_qty(p_visit_id,p_item_instance_id);
1306 FETCH get_cumm_inst_issued_qty INTO l_quantity;
1307 CLOSE get_cumm_inst_issued_qty;
1308 END IF;
1309 ELSIF (p_mode IN ('MA','MAC'))THEN
1310 OPEN visit_info_csr(p_visit_id);
1311 FETCH visit_info_csr INTO l_locator_id, l_subinventory,l_locator_segments;
1312 CLOSE visit_info_csr;
1313
1314 l_avail_qty := 0;
1315 l_quantity := 0;
1316 IF(p_item_instance_id IS NULL)THEN
1317 FOR avail_rec IN get_visit_avail_qty(p_visit_id,l_subinventory,l_locator_id) LOOP
1318 l_avail_qty := 0;
1319 IF(avail_rec.required_quantity > (avail_rec.issued_qty + avail_rec.available_quantity))THEN
1320 l_avail_qty := avail_rec.available_quantity;
1321 ELSIF(avail_rec.required_quantity > avail_rec.issued_qty)THEN
1322 l_avail_qty := avail_rec.required_quantity - avail_rec.issued_qty;
1323 END IF;
1324 l_quantity := l_quantity + l_avail_qty;
1325 END LOOP;
1326 ELSIF p_mode = 'MA' THEN
1327 FOR avail_rec IN get_inst_avail_qty(p_visit_id,p_item_instance_id,l_subinventory,l_locator_id) LOOP
1328 l_avail_qty := 0;
1329 IF(avail_rec.required_quantity > ( avail_rec.issued_qty + avail_rec.available_quantity))THEN
1330 l_avail_qty := avail_rec.available_quantity;
1331 ELSIF(avail_rec.required_quantity > avail_rec.issued_qty)THEN
1332 l_avail_qty := avail_rec.required_quantity - avail_rec.issued_qty;
1333 END IF;
1334 l_quantity := l_quantity + l_avail_qty;
1335 END LOOP;
1336
1337 ELSIF p_mode = 'MAC' THEN
1338 FOR avail_rec IN get_cumm_inst_avail_qty(p_visit_id,p_item_instance_id,l_subinventory,l_locator_id) LOOP
1339 l_avail_qty := 0;
1340 IF(avail_rec.required_quantity > ( avail_rec.issued_qty + avail_rec.available_quantity))THEN
1341 l_avail_qty := avail_rec.available_quantity;
1342 ELSIF(avail_rec.required_quantity > avail_rec.issued_qty)THEN
1343 l_avail_qty := avail_rec.required_quantity - avail_rec.issued_qty;
1344 END IF;
1345 l_quantity := l_quantity + l_avail_qty;
1346 END LOOP;
1347 END IF;
1348
1349 ELSIF (p_mode IN ('MU','MUC'))THEN
1350 OPEN visit_info_csr(p_visit_id);
1351 FETCH visit_info_csr INTO l_locator_id, l_subinventory,l_locator_segments;
1352 CLOSE visit_info_csr;
1353
1354 l_unavail_qty := 0;
1355 l_quantity := 0;
1356
1357 IF(p_item_instance_id IS NULL)THEN
1358 FOR avail_rec IN get_visit_avail_qty(p_visit_id,l_subinventory,l_locator_id) LOOP
1359 l_unavail_qty := 0;
1360 IF((avail_rec.required_quantity - (avail_rec.issued_qty + avail_rec.available_quantity)) > 0)THEN
1361 l_unavail_qty := avail_rec.required_quantity - (avail_rec.issued_qty + avail_rec.available_quantity);
1362 ELSE
1363 l_unavail_qty := 0;
1364 END IF;
1365 l_quantity := l_quantity + l_unavail_qty;
1366 END LOOP;
1367 ELSIF p_mode = 'MU' THEN
1368 FOR avail_rec IN get_inst_avail_qty(p_visit_id,p_item_instance_id,l_subinventory,l_locator_id) LOOP
1369 l_unavail_qty := 0;
1370 IF((avail_rec.required_quantity - (avail_rec.issued_qty + avail_rec.available_quantity)) > 0)THEN
1371 l_unavail_qty := avail_rec.required_quantity - (avail_rec.issued_qty + avail_rec.available_quantity);
1372 ELSE
1373 l_unavail_qty := 0;
1374 END IF;
1375 l_quantity := l_quantity + l_unavail_qty;
1376 END LOOP;
1377 ELSIF p_mode = 'MUC' THEN
1378 FOR avail_rec IN get_cumm_inst_avail_qty(p_visit_id,p_item_instance_id,l_subinventory,l_locator_id) LOOP
1379 l_unavail_qty := 0;
1380 IF((avail_rec.required_quantity - (avail_rec.issued_qty + avail_rec.available_quantity)) > 0)THEN
1381 l_unavail_qty := avail_rec.required_quantity - (avail_rec.issued_qty + avail_rec.available_quantity);
1382 ELSE
1383 l_unavail_qty := 0;
1384 END IF;
1385 l_quantity := l_quantity + l_unavail_qty;
1386 END LOOP;
1387 END IF;
1388 END IF;
1389 RETURN NVL(l_quantity,0);
1390 END Get_item_count;
1391
1392 FUNCTION Get_visit_completion_perc
1393 (
1394 p_visit_id IN NUMBER
1395 ) RETURN NUMBER IS
1396
1397 CURSOR completed_time_csr(p_visit_id NUMBER) IS
1398 SELECT SUM(SCHEDULED_END_DATE - SCHEDULED_START_DATE)
1399 FROM AHL_SEARCH_WORKORDERS_V
1400 WHERE visit_id = p_visit_id
1401 AND JOB_STATUS_CODE IN('4','5','7','12')
1402 GROUP BY visit_id;
1403
1404 CURSOR total_time_csr(p_visit_id NUMBER) IS
1405 SELECT SUM(SCHEDULED_END_DATE - SCHEDULED_START_DATE)
1406 FROM AHL_SEARCH_WORKORDERS_V
1407 WHERE visit_id = p_visit_id
1408 AND JOB_STATUS_CODE <> '22'
1409 GROUP BY visit_id;
1410
1414 l_comp_percentage NUMBER;
1411 total_time NUMBER;
1412 completed_time NUMBER;
1413
1415
1416 BEGIN
1417 OPEN completed_time_csr(p_visit_id);
1418 FETCH completed_time_csr INTO completed_time;
1419 CLOSE completed_time_csr;
1420
1421 completed_time := NVL(completed_time,0);
1422
1423 OPEN total_time_csr(p_visit_id);
1424 FETCH total_time_csr INTO total_time;
1425 CLOSE total_time_csr;
1426
1427 l_comp_percentage := ROUND((completed_time/total_time)*100);
1428
1429 RETURN l_comp_percentage;
1430
1431 END Get_visit_completion_perc;
1432
1433 PROCEDURE Get_mrshl_details
1434 (
1435 p_api_version IN NUMBER := 1.0,
1436 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1437 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1438 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1439 p_default IN VARCHAR2 := FND_API.G_FALSE,
1440 p_module_type IN VARCHAR2 := NULL,
1441 p_unit_header_id IN NUMBER,
1442 p_item_instance_id IN NUMBER,
1443 p_visit_id IN NUMBER,
1444 x_mrshl_details_tbl OUT NOCOPY mrshl_details_tbl_type,
1445 x_return_status OUT NOCOPY VARCHAR2,
1446 x_msg_count OUT NOCOPY NUMBER,
1447 x_msg_data OUT NOCOPY VARCHAR2
1448 )IS
1449
1450
1451 l_api_name CONSTANT VARCHAR2(30) := 'Get_marsh_details';
1452 l_api_version CONSTANT NUMBER := 1.0;
1453
1454 l_count NUMBER;
1455
1456 BEGIN
1457 --Initialize API return status to success
1458 x_return_status := FND_API.G_RET_STS_SUCCESS;
1459
1460 --Standard call to check for call compatibility.
1461 IF NOT FND_API.compatible_api_call(
1462 l_api_version,
1463 p_api_version,
1464 l_api_name,
1465 G_PKG_NAME)
1466 THEN
1467 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1468 END IF;
1469 --Initialize message list if p_init_msg_list is set to TRUE.
1470 IF FND_API.to_boolean( p_init_msg_list ) THEN
1471 FND_MSG_PUB.initialize;
1472 END IF;
1473
1474 IF(p_unit_header_id IS NOT NULL)THEN
1475 get_uc_mrshl_details(
1476 p_unit_header_id => p_unit_header_id,
1477 p_visit_id => p_visit_id,
1478 x_Uc_mrshl_details_tbl => x_mrshl_details_tbl);
1479 ELSIF(p_item_instance_id IS NOT NULL)THEN
1480 get_inst_mrshl_details(
1481 p_item_instance_id => p_item_instance_id,
1482 p_visit_id => p_visit_id,
1483 x_inst_mrshl_details_tbl => x_mrshl_details_tbl);
1484 END IF;
1485
1486 IF x_mrshl_details_tbl.FIRST IS NOT NULL THEN
1487 FOR i IN x_mrshl_details_tbl.FIRST..x_mrshl_details_tbl.LAST LOOP
1488 INSERT INTO ahl_prd_mb_uc_details
1489 (
1490 Unit_Header_id,
1491 Unit_Name,
1492 --Path_position_id,
1493 relationship_id,
1494 parent_rel_id,
1495 POSITION,
1496 IS_POSITION_SER_CTRLD,
1497 CURR_ITEM_ID,
1498 CURR_INSTANCE_ID,
1499 parent_instance_id,
1500 ALLOWED_QTY,
1501 CURR_ITEM_NUMBER,
1502 CURR_SERIAL_NUMBER,
1503 CURR_INSTLD_QTY,
1504 REQ_QTY,
1505 ISSUED_QTY,
1506 AVAILABLE_QTY,
1507 NOT_AVAILABLE_QTY,
1508 COMPL_WO_COUNT,
1509 TOTAL_WO_COUNT,
1510 CUMM_REQ_QTY,
1511 CUMM_ISSUED_QTY,
1512 CUMM_AVAILABLE_QTY,
1513 CUMM_NOT_AVAILABLE_QTY,
1514 CUMM_COMPL_WO_COUNT,
1515 CUMM_TOTAL_WO_COUNT,
1516 ROOT_INSTANCE_ID
1517 )VALUES
1518 (
1519 x_mrshl_details_tbl(i).Unit_Header_id,
1520 x_mrshl_details_tbl(i).Unit_Name,
1521 x_mrshl_details_tbl(i).relationship_id,
1522 x_mrshl_details_tbl(i).parent_rel_id,
1523 x_mrshl_details_tbl(i).POSITION,
1524 x_mrshl_details_tbl(i).IS_POSITION_SER_CTRLD,
1525 x_mrshl_details_tbl(i).CURR_ITEM_ID,
1526 x_mrshl_details_tbl(i).CURR_INSTANCE_ID,
1527 x_mrshl_details_tbl(i).parent_instance_id,
1528 x_mrshl_details_tbl(i).ALLOWED_QTY,
1529 x_mrshl_details_tbl(i).CURR_ITEM_NUMBER,
1530 x_mrshl_details_tbl(i).CURR_SERIAL_NUMBER,
1531 x_mrshl_details_tbl(i).CURR_INSTLD_QTY,
1532 x_mrshl_details_tbl(i).REQ_QTY,
1533 x_mrshl_details_tbl(i).ISSUED_QTY,
1534 x_mrshl_details_tbl(i).AVAILABLE_QTY,
1535 x_mrshl_details_tbl(i).NOT_AVAILABLE_QTY,
1536 x_mrshl_details_tbl(i).COMPL_WO_COUNT,
1537 x_mrshl_details_tbl(i).TOTAL_WO_COUNT,
1538 x_mrshl_details_tbl(i).CUMM_REQ_QTY,
1539 x_mrshl_details_tbl(i).CUMM_ISSUED_QTY,
1540 x_mrshl_details_tbl(i).CUMM_AVAILABLE_QTY,
1541 x_mrshl_details_tbl(i).CUMM_NOT_AVAILABLE_QTY,
1542 x_mrshl_details_tbl(i).CUMM_COMPL_WO_COUNT,
1543 x_mrshl_details_tbl(i).CUMM_TOTAL_WO_COUNT,
1544 x_mrshl_details_tbl(i).ROOT_INSTANCE_ID
1545 );
1546 END LOOP;
1547 END IF;
1548
1549
1550 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1551 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1555
1552 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After normal execution',
1553 'At the end of the procedure');
1554 END IF;
1556 IF(p_commit = FND_API.G_TRUE) THEN
1557 COMMIT WORK;
1558 END IF;
1559 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
1560 x_msg_count := FND_MSG_PUB.count_msg;
1561 IF x_msg_count > 0 THEN
1562 RAISE FND_API.G_EXC_ERROR;
1563 END IF;
1564 -- Count and Get messages (optional)
1565 FND_MSG_PUB.count_and_get(
1566 p_encoded => FND_API.G_FALSE,
1567 p_count => x_msg_count,
1568 p_data => x_msg_data);
1569
1570 EXCEPTION
1571 WHEN FND_API.G_EXC_ERROR THEN
1572 x_return_status := FND_API.G_RET_STS_ERROR ;
1573 FND_MSG_PUB.count_and_get(
1574 p_encoded => FND_API.G_FALSE,
1575 p_count => x_msg_count,
1576 p_data => x_msg_data);
1577
1578 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1579 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1580 FND_MSG_PUB.count_and_get(
1581 p_encoded => FND_API.G_FALSE,
1582 p_count => x_msg_count,
1583 p_data => x_msg_data);
1584
1585 WHEN OTHERS THEN
1586 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1587 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1588 THEN
1589 FND_MSG_PUB.add_exc_msg(
1590 p_pkg_name => G_PKG_NAME,
1591 p_procedure_name => l_api_name,
1592 p_error_text => SUBSTRB(SQLERRM,1,240));
1593 END IF;
1594 FND_MSG_PUB.count_and_get(
1595 p_encoded => FND_API.G_FALSE,
1596 p_count => x_msg_count,
1597 p_data => x_msg_data);
1598 END Get_mrshl_details;
1599
1600 PROCEDURE get_uc_mrshl_details(
1601 p_unit_header_id IN NUMBER,
1602 p_visit_id IN NUMBER,
1603 x_Uc_mrshl_details_tbl OUT NOCOPY mrshl_details_tbl_type) IS
1604
1605
1606 l_api_name CONSTANT VARCHAR2(30) := 'get_uc_mrshl_details';
1607 l_Uc_mrshl_details_tbl mrshl_details_tbl_type;
1608 l_return_status VARCHAR2(1);
1609 l_msg_count NUMBER;
1610 l_msg_data VARCHAR2(2000);
1611
1612 l_uc_descendant_tbl AHL_UC_TREE_PVT.uc_descendant_tbl_type;
1613 l_unit_name VARCHAR2(80);
1614 l_root_instance_id NUMBER;
1615
1616 CURSOR uc_header_information(c_unit_header_id IN NUMBER) IS
1617 SELECT UC_NAME,CSI_INSTANCE_ID FROM ahl_unit_config_headers_v
1618 WHERE UC_HEADER_ID = c_unit_header_id;
1619
1620 CURSOR get_part_info(c_instance_id NUMBER) IS
1621 SELECT M.inventory_item_id,M.concatenated_segments,C.serial_number,
1622 C.QUANTITY
1623 FROM mtl_system_items_kfv M, csi_item_instances C
1624 WHERE C.instance_id = c_instance_id
1625 AND C.inventory_item_id = M.inventory_item_id
1626 AND C.inv_master_organization_id = M.organization_id;
1627
1628 /*CURSOR get_all_details(c_unit_header_id IN NUMBER) IS
1629 Select * FROM ahl_prd_mb_uc_details
1630 WHERE UNIT_HEADER_ID = c_unit_header_id;*/
1631
1632 CURSOR get_pos_dtls_csr(c_mc_relationship_id IN NUMBER,
1633 c_instance_id IN NUMBER) IS
1634 SELECT iasso.quantity Itm_qty,
1635 iasso.uom_code Itm_uom_code,
1636 iasso.revision Itm_revision,
1637 iasso.item_association_id,
1638 reln.quantity Posn_qty,
1639 reln.uom_code Posn_uom_code,
1640 reln.parent_relationship_id,
1641 reln.position_ref_code,
1642 csi.INVENTORY_ITEM_ID,
1643 csi.QUANTITY Inst_qty,
1644 csi.UNIT_OF_MEASURE Inst_uom_code
1645 FROM ahl_mc_relationships reln, ahl_item_associations_b iasso, csi_item_instances csi
1646 WHERE csi.INSTANCE_ID = c_instance_id
1647 AND reln.relationship_id = c_mc_relationship_id
1648 AND iasso.item_group_id = reln.item_group_id
1649 AND iasso.inventory_item_id = CSI.INVENTORY_ITEM_ID
1650 AND (iasso.revision IS NULL OR iasso.revision = CSI.INVENTORY_REVISION)
1651 AND iasso.interchange_type_code IN ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
1652 AND trunc(nvl(reln.active_start_date, sysdate)) <= trunc(sysdate)
1653 AND trunc(nvl(reln.active_end_date, sysdate+1)) > trunc(sysdate);
1654
1655 l_pos_dtls_rec get_pos_dtls_csr%ROWTYPE;
1656
1657 j NUMBER;
1658 l_allowed_quantity NUMBER;
1659
1660 BEGIN
1661 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1662 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1663 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
1664 'At the start of the procedure');
1665 END IF;
1666
1667 OPEN uc_header_information(p_unit_header_id);
1668 FETCH uc_header_information INTO l_unit_name,l_root_instance_id;
1669 CLOSE uc_header_information;
1670
1671 AHL_UC_TREE_PVT.get_whole_uc_tree(
1672 p_api_version => 1.0,
1673 p_init_msg_list => FND_API.G_TRUE,
1674 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1675 x_return_status => l_return_status,
1676 x_msg_count => l_msg_count,
1677 x_msg_data => l_msg_data,
1678 p_uc_header_id => p_unit_header_id,
1679 x_uc_descendant_tbl => l_uc_descendant_tbl);
1680 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1681 RAISE FND_API.G_EXC_ERROR;
1682 END IF;
1683
1684 j := 1;
1685 IF l_uc_descendant_tbl.FIRST IS NOT NULL THEN
1689 l_Uc_mrshl_details_tbl(j).root_instance_id := l_root_instance_id;
1686 FOR i IN l_uc_descendant_tbl.FIRST..l_uc_descendant_tbl.LAST LOOP
1687 l_Uc_mrshl_details_tbl(j).Unit_Header_id := p_unit_header_id;
1688 l_Uc_mrshl_details_tbl(j).Unit_Name := l_unit_name;
1690
1691 l_Uc_mrshl_details_tbl(j).relationship_id := l_uc_descendant_tbl(i).relationship_id;
1692 l_Uc_mrshl_details_tbl(j).parent_rel_id := l_uc_descendant_tbl(i).parent_rel_id;
1693 l_Uc_mrshl_details_tbl(j).POSITION := l_uc_descendant_tbl(i).position_reference;
1694
1695 l_Uc_mrshl_details_tbl(j).CURR_INSTANCE_ID := l_uc_descendant_tbl(i).INSTANCE_ID;
1696 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1697 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1698 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1699 'l_uc_descendant_tbl(i).INSTANCE_ID :: ' || l_uc_descendant_tbl(i).INSTANCE_ID);
1700 END IF;
1701 l_Uc_mrshl_details_tbl(j).parent_instance_id := l_uc_descendant_tbl(i).parent_instance_id;
1702 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1703 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1704 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1705 'l_uc_descendant_tbl(i).parent_instance_id :: ' || l_uc_descendant_tbl(i).parent_instance_id);
1706 END IF;
1707 IF l_Uc_mrshl_details_tbl(j).CURR_INSTANCE_ID IS NOT NULL THEN
1708 OPEN get_part_info(l_Uc_mrshl_details_tbl(j).CURR_INSTANCE_ID);
1709 FETCH get_part_info INTO l_Uc_mrshl_details_tbl(j).CURR_ITEM_ID,
1710 l_Uc_mrshl_details_tbl(j).CURR_ITEM_NUMBER,
1711 l_Uc_mrshl_details_tbl(j).CURR_SERIAL_NUMBER,
1712 l_Uc_mrshl_details_tbl(j).CURR_INSTLD_QTY;
1713 CLOSE get_part_info;
1714 END IF;
1715 IF(l_Uc_mrshl_details_tbl(j).CURR_INSTLD_QTY IS NULL)THEN
1716 l_Uc_mrshl_details_tbl(j).CURR_INSTLD_QTY := 0;
1717 END IF;
1718
1719
1720 IF(l_Uc_mrshl_details_tbl(j).CURR_ITEM_NUMBER IS NOT NULL)THEN
1721 l_Uc_mrshl_details_tbl(j).POSITION := l_Uc_mrshl_details_tbl(j).POSITION || ' (' ||
1722 l_Uc_mrshl_details_tbl(j).CURR_ITEM_NUMBER;
1723 IF(l_Uc_mrshl_details_tbl(j).CURR_SERIAL_NUMBER IS NOT NULL)THEN
1724 l_Uc_mrshl_details_tbl(j).POSITION := l_Uc_mrshl_details_tbl(j).POSITION || '\' ||
1725 l_Uc_mrshl_details_tbl(j).CURR_SERIAL_NUMBER || ')';
1726 ELSE
1727 l_Uc_mrshl_details_tbl(j).POSITION := l_Uc_mrshl_details_tbl(j).POSITION || ')';
1728 END IF;
1729 END IF;
1730
1731 l_Uc_mrshl_details_tbl(j).IS_POSITION_SER_CTRLD :=
1732 AHL_MC_PATH_POSITION_PVT.Is_Position_Serial_Controlled(
1733 p_relationship_id => l_Uc_mrshl_details_tbl(j).relationship_id,
1734 p_path_position_id => NULL
1735 );
1736 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1737 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1738 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1739 'l_Uc_mrshl_details_tbl(j).IS_POSITION_SER_CTRLD :: ' || l_Uc_mrshl_details_tbl(j).IS_POSITION_SER_CTRLD);
1740 END IF;
1741 IF(l_Uc_mrshl_details_tbl(j).IS_POSITION_SER_CTRLD = 'Y')THEN
1742 l_Uc_mrshl_details_tbl(j).ALLOWED_QTY :=1;
1743 ELSE
1744 IF (l_uc_descendant_tbl(i).relationship_id IS NOT NULL) THEN
1745 OPEN get_pos_dtls_csr(c_mc_relationship_id => l_uc_descendant_tbl(i).relationship_id,
1746 c_instance_id => l_Uc_mrshl_details_tbl(j).CURR_INSTANCE_ID);
1747 FETCH get_pos_dtls_csr INTO l_pos_dtls_rec;
1748 CLOSE get_pos_dtls_csr;
1749
1750 IF (l_pos_dtls_rec.Itm_qty IS NULL OR l_pos_dtls_rec.Itm_qty = 0) THEN
1751 -- Pick the Quantity and UOM from Position level.
1752 l_pos_dtls_rec.Itm_qty := l_pos_dtls_rec.Posn_qty;
1753 l_pos_dtls_rec.Itm_uom_code := l_pos_dtls_rec.Posn_uom_code;
1754 END IF;
1755
1756 IF (l_pos_dtls_rec.Itm_uom_code <> l_pos_dtls_rec.Inst_uom_code) THEN
1757 -- UOMs are different: Convert Item UOM Qty to Inst UOM Qty
1758 l_allowed_quantity := inv_convert.inv_um_convert(item_id => l_pos_dtls_rec.INVENTORY_ITEM_ID,
1759 precision => 6,
1760 from_quantity => l_pos_dtls_rec.Itm_qty,
1761 from_unit => l_pos_dtls_rec.Itm_uom_code,
1762 to_unit => l_pos_dtls_rec.Inst_uom_code,
1763 from_name => NULL,
1764 to_name => NULL);
1765 l_pos_dtls_rec.Itm_qty := l_allowed_quantity;
1766 l_pos_dtls_rec.Itm_uom_code := l_pos_dtls_rec.Inst_uom_code;
1767 END IF;
1768 l_Uc_mrshl_details_tbl(j).ALLOWED_QTY := NVL(l_pos_dtls_rec.Itm_qty,0);
1769 END IF;
1770 END IF;
1771 /*
1772 MU - Material unavailable
1773 MUC - Material unavailable Cummulative
1774 MA - Material Avaialble
1775 MAC - Material Avaialble Cummulative
1776 MR - Material Required
1777 MRC - Material Required Cummulative
1778 MI - Material Issued
1779 MIC - Material Issued Cummulative
1780 */
1781 l_Uc_mrshl_details_tbl(j).REQ_QTY := Get_item_count
1782 (
1783 p_visit_id => p_visit_id,
1784 p_item_instance_id => NVL(l_Uc_mrshl_details_tbl(j).CURR_INSTANCE_ID,-1),
1785 p_mode => 'MR'
1789 p_visit_id => p_visit_id,
1786 );
1787 l_Uc_mrshl_details_tbl(j).CUMM_REQ_QTY := Get_item_count
1788 (
1790 p_item_instance_id => NVL(l_Uc_mrshl_details_tbl(j).CURR_INSTANCE_ID,-1),
1791 p_mode => 'MRC'
1792 );
1793 l_Uc_mrshl_details_tbl(j).ISSUED_QTY := Get_item_count
1794 (
1795 p_visit_id => p_visit_id,
1796 p_item_instance_id => NVL(l_Uc_mrshl_details_tbl(j).CURR_INSTANCE_ID,-1),
1797 p_mode => 'MI'
1798 );
1799
1800 l_Uc_mrshl_details_tbl(j).CUMM_ISSUED_QTY := Get_item_count
1801 (
1802 p_visit_id => p_visit_id,
1803 p_item_instance_id => NVL(l_Uc_mrshl_details_tbl(j).CURR_INSTANCE_ID,-1),
1804 p_mode => 'MIC'
1805 );
1806 l_Uc_mrshl_details_tbl(j).AVAILABLE_QTY := Get_item_count
1807 (
1808 p_visit_id => p_visit_id,
1809 p_item_instance_id => NVL(l_Uc_mrshl_details_tbl(j).CURR_INSTANCE_ID,-1),
1810 p_mode => 'MA'
1811 );
1812 l_Uc_mrshl_details_tbl(j).CUMM_AVAILABLE_QTY := Get_item_count
1813 (
1814 p_visit_id => p_visit_id,
1815 p_item_instance_id => NVL(l_Uc_mrshl_details_tbl(j).CURR_INSTANCE_ID,-1),
1816 p_mode => 'MAC'
1817 );
1818 l_Uc_mrshl_details_tbl(j).NOT_AVAILABLE_QTY := Get_item_count
1819 (
1820 p_visit_id => p_visit_id,
1821 p_item_instance_id => NVL(l_Uc_mrshl_details_tbl(j).CURR_INSTANCE_ID,-1),
1822 p_mode => 'MU'
1823 );
1824 l_Uc_mrshl_details_tbl(j).CUMM_NOT_AVAILABLE_QTY := Get_item_count
1825 (
1826 p_visit_id => p_visit_id,
1827 p_item_instance_id => NVL(l_Uc_mrshl_details_tbl(j).CURR_INSTANCE_ID,-1),
1828 p_mode => 'MUC'
1829 );
1830 l_Uc_mrshl_details_tbl(j).COMPL_WO_COUNT := Get_workorder_count
1831 (
1832 p_visit_id => p_visit_id,
1833 p_item_instance_id => NVL(l_Uc_mrshl_details_tbl(j).CURR_INSTANCE_ID,-1),
1834 p_mode => 'CO'
1835 );
1836 l_Uc_mrshl_details_tbl(j).CUMM_COMPL_WO_COUNT := Get_workorder_count
1837 (
1838 p_visit_id => p_visit_id,
1839 p_item_instance_id => NVL(l_Uc_mrshl_details_tbl(j).CURR_INSTANCE_ID,-1),
1840 p_mode => 'COC'
1841 );
1842 l_Uc_mrshl_details_tbl(j).TOTAL_WO_COUNT := Get_workorder_count
1843 (
1844 p_visit_id => p_visit_id,
1845 p_item_instance_id => NVL(l_Uc_mrshl_details_tbl(j).CURR_INSTANCE_ID,-1),
1846 p_mode => 'TO'
1847 );
1848 l_Uc_mrshl_details_tbl(j).CUMM_TOTAL_WO_COUNT := Get_workorder_count
1849 (
1850 p_visit_id => p_visit_id,
1851 p_item_instance_id => NVL(l_Uc_mrshl_details_tbl(j).CURR_INSTANCE_ID,-1),
1852 p_mode => 'TOC'
1853 );
1854 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1855 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1856 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1857 'l_Uc_mrshl_details_tbl(j).TOTAL_WO_COUNT :: ' || l_Uc_mrshl_details_tbl(j).TOTAL_WO_COUNT);
1858 END IF;
1859
1860 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1861 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1862 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1863 'l_Uc_mrshl_details_tbl(j) :: ' || i);
1864 END IF;
1865 j := j+1;
1866 END LOOP;
1867 END IF;
1868
1869 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1870 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1871 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1872 'Done fetching details');
1873 END IF;
1874
1875 x_Uc_mrshl_details_tbl := l_Uc_mrshl_details_tbl;
1876 /*IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1877 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1878 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1879 'final fetch');
1880 END IF;
1881 FOR get_all_details_rec IN get_all_details(p_unit_header_id) LOOP
1882 x_Uc_mrshl_details_tbl(j).Unit_Header_id := get_all_details_rec.Unit_Header_id ;
1883 x_Uc_mrshl_details_tbl(j).Unit_Name := get_all_details_rec.Unit_Name ;
1884 x_Uc_mrshl_details_tbl(j).Path_position_id := get_all_details_rec.Path_position_id ;
1885 x_Uc_mrshl_details_tbl(j).relationship_id := get_all_details_rec.relationship_id ;
1886 x_Uc_mrshl_details_tbl(j).parent_rel_id := get_all_details_rec.parent_rel_id ;
1887 x_Uc_mrshl_details_tbl(j).POSITION := get_all_details_rec.POSITION ;
1888 x_Uc_mrshl_details_tbl(j).IS_POSITION_SER_CTRLD := get_all_details_rec.IS_POSITION_SER_CTRLD ;
1889 x_Uc_mrshl_details_tbl(j).CURR_ITEM_ID := get_all_details_rec.CURR_ITEM_ID ;
1890 x_Uc_mrshl_details_tbl(j).CURR_INSTANCE_ID := get_all_details_rec.CURR_INSTANCE_ID ;
1891 x_Uc_mrshl_details_tbl(j).parent_instance_id := get_all_details_rec.parent_instance_id ;
1892 x_Uc_mrshl_details_tbl(j).ALLOWED_QTY := get_all_details_rec.ALLOWED_QTY ;
1893 x_Uc_mrshl_details_tbl(j).CURR_ITEM_NUMBER := get_all_details_rec.CURR_ITEM_NUMBER ;
1894 x_Uc_mrshl_details_tbl(j).CURR_SERIAL_NUMBER := get_all_details_rec.CURR_SERIAL_NUMBER ;
1895 x_Uc_mrshl_details_tbl(j).CURR_INSTLD_QTY := get_all_details_rec.CURR_INSTLD_QTY ;
1899 x_Uc_mrshl_details_tbl(j).NOT_AVAILABLE_QTY := get_all_details_rec.NOT_AVAILABLE_QTY ;
1896 x_Uc_mrshl_details_tbl(j).REQ_QTY := get_all_details_rec.REQ_QTY ;
1897 x_Uc_mrshl_details_tbl(j).ISSUED_QTY := get_all_details_rec.ISSUED_QTY ;
1898 x_Uc_mrshl_details_tbl(j).AVAILABLE_QTY := get_all_details_rec.AVAILABLE_QTY ;
1900 x_Uc_mrshl_details_tbl(j).COMPL_WO_COUNT := get_all_details_rec.COMPL_WO_COUNT ;
1901 x_Uc_mrshl_details_tbl(j).TOTAL_WO_COUNT := get_all_details_rec.TOTAL_WO_COUNT ;
1902 x_Uc_mrshl_details_tbl(j).CUMM_REQ_QTY := get_all_details_rec.CUMM_REQ_QTY ;
1903 x_Uc_mrshl_details_tbl(j).CUMM_ISSUED_QTY := get_all_details_rec.CUMM_ISSUED_QTY ;
1904 x_Uc_mrshl_details_tbl(j).CUMM_AVAILABLE_QTY := get_all_details_rec.CUMM_AVAILABLE_QTY ;
1905 x_Uc_mrshl_details_tbl(j).CUMM_NOT_AVAILABLE_QTY := get_all_details_rec.CUMM_NOT_AVAILABLE_QTY ;
1906 x_Uc_mrshl_details_tbl(j).CUMM_COMPL_WO_COUNT:= get_all_details_rec.CUMM_COMPL_WO_COUNT;
1907 x_Uc_mrshl_details_tbl(j).CUMM_TOTAL_WO_COUNT := get_all_details_rec.CUMM_TOTAL_WO_COUNT;
1908 j := j+1;
1909 END LOOP;*/
1910
1911 END get_uc_mrshl_details;
1912
1913 PROCEDURE get_inst_mrshl_details(
1914 p_item_instance_id IN NUMBER,
1915 p_visit_id IN NUMBER,
1916 x_inst_mrshl_details_tbl OUT NOCOPY mrshl_details_tbl_type) IS
1917
1918 l_api_name CONSTANT VARCHAR2(30) := 'get_uc_mrshl_details';
1919 l_inst_mrshl_details_tbl mrshl_details_tbl_type;
1920 j NUMBER;
1921
1922 CURSOR get_part_info(c_instance_id NUMBER) IS
1923 SELECT M.inventory_item_id,M.concatenated_segments,C.serial_number,
1924 DECODE(M.serial_number_control_code,'1','N','Y') is_serial_cntld,C.quantity
1925 FROM mtl_system_items_kfv M, csi_item_instances C
1926 WHERE C.instance_id = c_instance_id
1927 AND C.inventory_item_id = M.inventory_item_id
1928 AND C.inv_master_organization_id = M.organization_id;
1929
1930 CURSOR instance_tree_csr(p_root_instance_id NUMBER) IS
1931 SELECT instance_id,to_number(NULL) parent_instance_id FROM csi_item_instances WHERE INSTANCE_ID = p_root_instance_id
1932 UNION
1933 SELECT
1934 SUBJECT_ID INSTANCE_ID,
1935 OBJECT_ID PARENT_INSTANCE_ID
1936 FROM CSI_II_RELATIONSHIPS
1937 WHERE 1=1
1938 START WITH OBJECT_ID = p_root_instance_id
1939 AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
1940 AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
1941 AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
1942 CONNECT BY OBJECT_ID = PRIOR SUBJECT_ID
1943 AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
1944 AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
1945 AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE);
1946
1947
1948
1949 BEGIN
1950 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1951 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1952 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
1953 'At the start of the procedure');
1954 END IF;
1955
1956 IF p_item_instance_id IS NOT NULL THEN
1957
1958 j:= 1;
1959 FOR instance_rec IN instance_tree_csr(p_item_instance_id) LOOP
1960 l_inst_mrshl_details_tbl(j).root_instance_id := p_item_instance_id;
1961 l_inst_mrshl_details_tbl(j).CURR_INSTANCE_ID := instance_rec.instance_id;
1962 l_inst_mrshl_details_tbl(j).PARENT_INSTANCE_ID := instance_rec.parent_instance_id;
1963
1964
1965 IF l_inst_mrshl_details_tbl(j).CURR_INSTANCE_ID IS NOT NULL THEN
1966 OPEN get_part_info(l_inst_mrshl_details_tbl(j).CURR_INSTANCE_ID);
1967 FETCH get_part_info INTO l_inst_mrshl_details_tbl(j).CURR_ITEM_ID,
1968 l_inst_mrshl_details_tbl(j).CURR_ITEM_NUMBER,
1969 l_inst_mrshl_details_tbl(j).CURR_SERIAL_NUMBER,
1970 l_inst_mrshl_details_tbl(j).IS_POSITION_SER_CTRLD,
1971 l_inst_mrshl_details_tbl(j).CURR_INSTLD_QTY;
1972 CLOSE get_part_info;
1973 END IF;
1974 l_inst_mrshl_details_tbl(j).POSITION := l_inst_mrshl_details_tbl(j).CURR_ITEM_NUMBER;
1975 IF(l_inst_mrshl_details_tbl(j).CURR_SERIAL_NUMBER IS NOT NULL)THEN
1976 l_inst_mrshl_details_tbl(j).POSITION := l_inst_mrshl_details_tbl(j).POSITION || '(' ||
1977 l_inst_mrshl_details_tbl(j).CURR_SERIAL_NUMBER || ')';
1978 END IF;
1979
1980 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1981 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1982 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1983 'l_inst_mrshl_details_tbl(j).IS_POSITION_SER_CTRLD :: ' || l_inst_mrshl_details_tbl(j).IS_POSITION_SER_CTRLD);
1984 END IF;
1985 IF(l_inst_mrshl_details_tbl(j).IS_POSITION_SER_CTRLD = 'Y')THEN
1986 l_inst_mrshl_details_tbl(j).ALLOWED_QTY :=1;
1987 END IF;
1988 /*
1989 MU - Material unavailable
1990 MUC - Material unavailable Cummulative
1991 MA - Material Avaialble
1992 MAC - Material Avaialble Cummulative
1993 MR - Material Required
1994 MRC - Material Required Cummulative
1995 MI - Material Issued
1996 MIC - Material Issued Cummulative
1997 */
1998 l_inst_mrshl_details_tbl(j).REQ_QTY := Get_item_count
1999 (
2000 p_visit_id => p_visit_id,
2001 p_item_instance_id => NVL(l_inst_mrshl_details_tbl(j).CURR_INSTANCE_ID,-1),
2002 p_mode => 'MR'
2003 );
2004 l_inst_mrshl_details_tbl(j).CUMM_REQ_QTY := Get_item_count
2005 (
2006 p_visit_id => p_visit_id,
2007 p_item_instance_id => NVL(l_inst_mrshl_details_tbl(j).CURR_INSTANCE_ID,-1),
2008 p_mode => 'MRC'
2009 );
2010 l_inst_mrshl_details_tbl(j).ISSUED_QTY := Get_item_count
2011 (
2012 p_visit_id => p_visit_id,
2013 p_item_instance_id => NVL(l_inst_mrshl_details_tbl(j).CURR_INSTANCE_ID,-1),
2014 p_mode => 'MI'
2015 );
2016
2017 l_inst_mrshl_details_tbl(j).CUMM_ISSUED_QTY := Get_item_count
2018 (
2019 p_visit_id => p_visit_id,
2020 p_item_instance_id => NVL(l_inst_mrshl_details_tbl(j).CURR_INSTANCE_ID,-1),
2021 p_mode => 'MIC'
2022 );
2023 l_inst_mrshl_details_tbl(j).AVAILABLE_QTY := Get_item_count
2024 (
2025 p_visit_id => p_visit_id,
2026 p_item_instance_id => NVL(l_inst_mrshl_details_tbl(j).CURR_INSTANCE_ID,-1),
2027 p_mode => 'MA'
2028 );
2029 l_inst_mrshl_details_tbl(j).CUMM_AVAILABLE_QTY := Get_item_count
2030 (
2031 p_visit_id => p_visit_id,
2032 p_item_instance_id => NVL(l_inst_mrshl_details_tbl(j).CURR_INSTANCE_ID,-1),
2033 p_mode => 'MAC'
2034 );
2035 l_inst_mrshl_details_tbl(j).NOT_AVAILABLE_QTY := Get_item_count
2036 (
2037 p_visit_id => p_visit_id,
2038 p_item_instance_id => NVL(l_inst_mrshl_details_tbl(j).CURR_INSTANCE_ID,-1),
2039 p_mode => 'MU'
2040 );
2041 l_inst_mrshl_details_tbl(j).CUMM_NOT_AVAILABLE_QTY := Get_item_count
2042 (
2043 p_visit_id => p_visit_id,
2044 p_item_instance_id => NVL(l_inst_mrshl_details_tbl(j).CURR_INSTANCE_ID,-1),
2045 p_mode => 'MUC'
2046 );
2047 l_inst_mrshl_details_tbl(j).COMPL_WO_COUNT := Get_workorder_count
2048 (
2049 p_visit_id => p_visit_id,
2050 p_item_instance_id => NVL(l_inst_mrshl_details_tbl(j).CURR_INSTANCE_ID,-1),
2051 p_mode => 'CO'
2052 );
2053 l_inst_mrshl_details_tbl(j).CUMM_COMPL_WO_COUNT := Get_workorder_count
2054 (
2055 p_visit_id => p_visit_id,
2056 p_item_instance_id => NVL(l_inst_mrshl_details_tbl(j).CURR_INSTANCE_ID,-1),
2057 p_mode => 'COC'
2058 );
2059 l_inst_mrshl_details_tbl(j).TOTAL_WO_COUNT := Get_workorder_count
2060 (
2061 p_visit_id => p_visit_id,
2062 p_item_instance_id => NVL(l_inst_mrshl_details_tbl(j).CURR_INSTANCE_ID,-1),
2063 p_mode => 'TO'
2064 );
2065 l_inst_mrshl_details_tbl(j).CUMM_TOTAL_WO_COUNT := Get_workorder_count
2066 (
2067 p_visit_id => p_visit_id,
2068 p_item_instance_id => NVL(l_inst_mrshl_details_tbl(j).CURR_INSTANCE_ID,-1),
2069 p_mode => 'TOC'
2070 );
2071 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2072 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2073 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2074 'l_inst_mrshl_details_tbl(j).TOTAL_WO_COUNT :: ' || l_inst_mrshl_details_tbl(j).TOTAL_WO_COUNT);
2075 END IF;
2076
2077 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2078 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2079 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2080 'l_inst_mrshl_details_tbl(j) :: ' || j);
2081 END IF;
2082 j := j+1;
2083 END LOOP;
2084 END IF;
2085
2086 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2087 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2088 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2089 'Done fetching details');
2090 END IF;
2091
2092 x_inst_mrshl_details_tbl := l_inst_mrshl_details_tbl;
2093
2094 END get_inst_mrshl_details;
2095
2096
2097 END AHL_PRD_MRSHL_PVT;