[Home] [Help]
181:
182: -- Bug 4440045: added the inv_lot cursor
183: CURSOR inv_lot IS
184: SELECT c.PARENT_LOT_NUMBER, c.LOT_NUMBER
185: FROM MTL_LOT_NUMBERS C,
186: MTL_MATERIAL_TRANSACTIONS M,
187: MTL_TRANSACTION_LOT_NUMBERS L
188: WHERE M.transaction_id = l_trans_id -- replace l_event_key by l_trans_id -- 9756188
189: AND M.inventory_item_id = C.inventory_item_id
211: A.expiration_date,
212: A.Retest_Date,
213: A.last_updated_by --Bug#6276012 added
214: --QZENG Bug 13881118 Changed from MTL_SYSTEM_ITEMS_B_KFV to MTL_SYSTEM_ITEMS_VL to support NLS
215: --FROM mtl_lot_numbers A, mtl_system_items_b_kfv B, mtl_parameters mp --RLNAGARA B4905645
216: FROM mtl_lot_numbers A, mtl_system_items_vl B, mtl_parameters mp --RLNAGARA B4905645
217: WHERE A.inventory_item_id = B.inventory_item_id
218: AND A.organization_id = B.organization_id
219: AND mp.organization_id = A.organization_id
212: A.Retest_Date,
213: A.last_updated_by --Bug#6276012 added
214: --QZENG Bug 13881118 Changed from MTL_SYSTEM_ITEMS_B_KFV to MTL_SYSTEM_ITEMS_VL to support NLS
215: --FROM mtl_lot_numbers A, mtl_system_items_b_kfv B, mtl_parameters mp --RLNAGARA B4905645
216: FROM mtl_lot_numbers A, mtl_system_items_vl B, mtl_parameters mp --RLNAGARA B4905645
217: WHERE A.inventory_item_id = B.inventory_item_id
218: AND A.organization_id = B.organization_id
219: AND mp.organization_id = A.organization_id
220: AND --RLNAGARA B4905645
227:
228: CURSOR supp_lot is
229: SELECT supplier_lot_number, rlt.lot_num
230: from rcv_lot_transactions rlt,
231: mtl_lot_numbers mln
232: where rlt.TRANSACTION_ID = l_transaction_id
233: and rlt.item_id = l_inventory_item_id
234: AND mln.lot_number = rlt.lot_num
235: AND mln.organization_id = l_organization_id
335:
336: --RLNAGARA Bug5334308 Added cursor get_lot and modified cursor recv_inv
337: CURSOR get_lot(p_gen_obj_id NUMBER) IS
338: SELECT LOT_NUMBER
339: FROM mtl_lot_numbers
340: WHERE GEN_OBJECT_ID = p_gen_obj_id;
341:
342: -- 9756188 new cursor for lot issue
343:
342: -- 9756188 new cursor for lot issue
343:
344: CURSOR get_mtln_lot(p_trans_id NUMBER, p_gen_obj_id NUMBER) IS
345: select mtln.lot_number, mln.parent_lot_number,mtln.transaction_quantity, mtln.secondary_transaction_quantity
346: from mtl_transaction_lot_numbers mtln,mtl_lot_numbers mln
347: where mtln.transaction_id = p_trans_id
348: and mln.gen_object_id = p_gen_obj_id
349: and mln.lot_number = mtln.lot_number
350: and mln.inventory_item_id = mtln.inventory_item_id
394: rcv_shipment_lines sl,
395: po_headers_all po,
396: rcv_shipment_headers sh,
397: rcv_lot_transactions tran,
398: mtl_lot_numbers lot,
399: hr_operating_units hou
400: WHERE rt.TRANSACTION_ID = p_trans_id
401: AND rt.shipment_header_id = sl.shipment_header_id
402: AND rt.shipment_line_id = sl.shipment_line_id
2434: -- old cursor was bringing back too many rows and was not using org id and item id in query so the wrong lot information was being returned
2435:
2436: CURSOR Cur_pending_lot(x_material_detail_id number) is
2437: select mln.lot_number, mln.parent_lot_number
2438: from mtl_lot_numbers mln, gme_material_details d, GME_PENDING_PRODUCT_LOTS ppl
2439: where mln.lot_number = ppl.lot_number
2440: and ppl.material_detail_id = x_material_detail_id
2441: and ppl.material_detail_id = d.material_detail_id
2442: and d.organization_id = mln.organization_id
2445:
2446: /* Bug No.8942353 - Start
2447: CURSOR Cur_pending_lot(x_material_detail_id number) is
2448: select mln.lot_number, mln.parent_lot_number
2449: from mtl_lot_numbers mln
2450: where lot_number =
2451: (select lot_number
2452: from GME_PENDING_PRODUCT_LOTS
2453: where material_detail_id = x_material_detail_id