Last item used - SLOB

--Last item used in branch
select LIMCU "Branch Plant", TRIM(IBLITM) "Item Number", (select IMDSC1 from USPDDTA.F4101 where LIITM = IMITM) "Description",
IBSRP3 "prod line", IBGLPT "GL Class", TRIM(LILOCN) "Location", TRIM(LILOTN) "Lot Number",LIPQOH/100000 "Quantity on Hand",
TO_DATE(TO_CHAR(IODLEJ+1900000),'YYYYDDD') "Lot Effectivity Date",  IOLOTS "Lot Status Code",
(select TO_DATE(TO_CHAR(max(C2.ILTRDJ)+1900000),'YYYYDDD') from USPDDTA.F4111 C2
   where C2.ILITM = IBITM and C2.ILMCU = IBMCU and C2.ILDCT in ('IM', 'RI','OV')
   /*and C2.ilmcu in ('         FSI', '         FSO', '         FST', '         FSX')*/)  "Last Item b/p used Tran Date"
from USPDDTA.F41021, USPDDTA.F4108, USPDDTA.F4102
where LIITM = IOITM(+) and LIMCU = IOMCU(+) and LILOTN = IOLOTN(+) and LIITM = IBITM and LIMCU = IBMCU
and iodlej <=115215 and iodlej <> 0
and LIPQOH > 0
and LIMCU in ('         FSI', '         FSO', '         FST', '         FSX')
and not exists (select * from USPDDTA.F4111 C1 where C1.ILITM = LIITM and C1.ILMCU = LIMCU and C1.ILDCT in ('IM', 'RI','OV') and C1.ILTRDJ > 115215)
order by IBMCU, IBLITM, LILOCN, IOLOTN;


--Last item used
select LIMCU "Branch Plant", TRIM(IBLITM) "Item Number", (select IMDSC1 from USPDDTA.F4101 where LIITM = IMITM) "Description",
IBSRP3 "prod line", IBGLPT "GL Class", TRIM(LILOCN) "Location", TRIM(LILOTN) "Lot Number",LIPQOH/100000 "Quantity on Hand",
TO_DATE(TO_CHAR(IODLEJ+1900000),'YYYYDDD') "Lot Effectivity Date",  IOLOTS "Lot Status Code",
(select TO_DATE(TO_CHAR(max(C2.ILTRDJ)+1900000),'YYYYDDD') from USPDDTA.F4111 C2
   where C2.ILITM = IBITM and C2.ILDCT in ('IM', 'RI','OV')
   and C2.ilmcu in ('         FSI', '         FSO', '         FST', '         FSX'))  "Last Item used Tran Date"
from USPDDTA.F41021, USPDDTA.F4108, USPDDTA.F4102
where LIITM = IOITM(+) and LIMCU = IOMCU(+) and LILOTN = IOLOTN(+) and LIITM = IBITM and LIMCU = IBMCU
and iodlej <=115215
and LIPQOH > 0
and LIMCU in ('         FSI', '         FSO', '         FST', '         FSX')
and not exists (select * from USPDDTA.F4111 C1 where C1.ILITM = LIITM and C1.ILMCU in ('         FSI', '         FSO', '         FST', '         FSX') and C1.ILDCT in ('IM', 'RI','OV') and C1.ILTRDJ > 115215)
order by IBMCU, IBLITM, LILOCN, IOLOTN;