Production Batch dalam 1 Tahun (Bu Eka) - Non Report

WITH temp AS (
SELECT msib.inventory_item_id, msib.organization_id, msib.SEGMENT1, SUBSTR(msib.description,1, INSTR(msib.description,'~')-1) description,
       COUNT(GBH.ATTRIBUTE1) jumlah_batch, MIN(GBH.ATTRIBUTE1) keep(DENSE_RANK FIRST ORDER BY GBH.ACTUAL_START_DATE) BATCH_AWAL,
       TO_CHAR(MIN(GBH.ACTUAL_START_DATE), 'DD-MM-RRRR') "mulai produksi batch awal", TO_CHAR(MIN(GBH.BATCH_CLOSE_DATE), 'DD-MM-RRRR') "selesai produksi batch awal",
       MAX(GBH.ATTRIBUTE1) keep(DENSE_RANK LAST ORDER BY GBH.ACTUAL_START_DATE) BATCH_AKHIR,
       TO_CHAR(MAX(GBH.ACTUAL_START_DATE), 'DD-MM-RRRR') "mulai produksi batch akhir", TO_CHAR(MAX(GBH.BATCH_CLOSE_DATE), 'DD-MM-RRRR') "selesai produksi batch akhir"
--       to_char(MAX(GBH.BATCH_CLOSE_DATE), 'DD-MM-RRRR')

FROM GME_BATCH_HEADER GBH, gme_material_details GMD, MTL_SYSTEM_ITEMS_B MSIB
WHERE 1=1
  AND GBH.BATCH_ID = GMD.BATCH_ID
  AND GMD.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
  AND MSIB.ORGANIZATION_ID = GBH.ORGANIZATION_ID
  AND GBH.ORGANIZATION_ID = 82
  AND SUBSTR(MSIB.SEGMENT1,1,2) IN ('FG','BP')
  AND GBH.BATCH_STATUS NOT IN (-1,1)
  AND TO_CHAR(GBH.ACTUAL_START_DATE,'RRRR') = '2022'
--  AND GBH.ATTRIBUTE1 = '20126'
--  AND MSIB.SEGMENT1 = 'XGTADIS225'
GROUP BY msib.inventory_item_id,
         msib.SEGMENT1,
         SUBSTR(msib.description,1, INSTR(msib.description,'~')-1),
         msib.organization_id
ORDER BY MSIB.SEGMENT1
)

SELECT t.*,
       (SELECT DECODE(mln.STATUS_ID,23,'Quarantine', 24, 'Released','Tidak Terdefinisi')
          FROM mtl_lot_numbers mln
         WHERE mln.INVENTORY_ITEM_ID = t.INVENTORY_ITEM_ID
           AND mln.ORGANIZATION_ID = t.ORGANIZATION_ID
           AND mln.LOT_NUMBER = t.BATCH_AKHIR
       ) "STATUS BATCH AKHIR"
  FROM temp t

;