[Home] [Help]
518: /* extract_expired_inventory
519:
520: Extract the onhand and expired lot controlled inventory quantity/value
521: as of the run time from the inventory current snapshot tables,
522: MTL_ONHAND_QUANTITIES and MTL_LOT_NUMBERS. All inventory that is not
523: lot controlled will be ignored in this extraction.
524:
525: Inventory value is computed using the current item costs stored in the
526: system.
560: -- Extract data. Query logic is as follows:
561: --
562: -- Extract the onhand and expired lot controlled inventory quantity and
563: -- value as of the run time from the inventory current snapshot tables,
564: -- MTL_ONHAND_QUANTITIES and MTL_LOT_NUMBERS. All inventory that is not
565: -- lot controlled will be ignored in this extraction. Since the MTL
566: -- tables store quantity in the item's primary uom, there is no need
567: -- for unit of measure conversions.
568: --
755: 0
756: END)
757: expired_qty
758: FROM mtl_onhand_quantities moq,
759: mtl_lot_numbers mln
760: WHERE moq.inventory_item_id = mln.inventory_item_id
761: AND moq.organization_id = mln.organization_id
762: AND moq.lot_number = mln.lot_number
763: GROUP BY
810: curr_codes.currency_code,
811: p_run_date))
812: sec_conversion_rate
813: FROM
814: (SELECT /*+ parallel (mtl_lot_numbers) */
815: DISTINCT organization_id
816: FROM mtl_lot_numbers) to_conv,
817: (SELECT /*+ leading (hoi) full (hoi) use_hash (gsob)
818: parallel (hoi) parallel (gsob)*/
812: sec_conversion_rate
813: FROM
814: (SELECT /*+ parallel (mtl_lot_numbers) */
815: DISTINCT organization_id
816: FROM mtl_lot_numbers) to_conv,
817: (SELECT /*+ leading (hoi) full (hoi) use_hash (gsob)
818: parallel (hoi) parallel (gsob)*/
819: DISTINCT hoi.organization_id, gsob.currency_code
820: FROM hr_organization_information hoi,