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