Post date: Nov 16, 2010 3:35:15 AM
/* Formatted on 2010/08/06 09:55 (Formatter Plus v4.8.8) */
SELECT DISTINCT gbh.batch_no Batch_NO
, DECODE (gbh.batch_status,
'-1','Cancelled',
'1','Pending',
'2','WIP',
'3','Completed',
'4','Closed',
'Others'
) Batch_Status
--,mmt.transaction_id, ,
,TO_CHAR (gbh.actual_start_date,
'dd-mm-rrrr hh24:mi:ss'
) batch_start_date,
TO_CHAR (gbh.actual_cmplt_date,
'dd-mm-rrrr hh24:mi:ss'
) batch_end_date,
TO_CHAR ((SELECT MIN (actual_start_date)
FROM gme_batch_steps
WHERE batch_id = gbh.batch_id),
'dd-mm-rrrr hh24:mi:ss'
) step_start_date,
TO_CHAR ((SELECT MAX (actual_cmplt_date)
FROM gme_batch_steps
WHERE batch_id = gbh.batch_id),
'dd-mm-rrrr hh24:mi:ss'
) step_end_date
, gmd.line_no
,DECODE (gmd.line_type,
1, 'Product',
-1, 'Ingredients'
) item_type,
msib.segment1 item_code, msib.description item_description,
msib.primary_uom_code primary_uom
,mp.organization_code
,pn_opm_pkg.GET_LOT_NUMBER(gbh.batch_id,gbh.organization_id) lot_no
FROM gme_batch_header gbh,
gme_material_details gmd,
mtl_system_items_b msib,
-- mtl_material_transactions mmt,
-- mtl_transaction_lot_numbers mtln,
-- mtl_transaction_types mtty,
mtl_parameters mp
WHERE gbh.batch_id = gmd.batch_id
AND gmd.inventory_item_id = msib.inventory_item_id
AND gmd.organization_id = msib.organization_id
AND gmd.LINE_TYPE=1
AND gmd.ACTUAL_QTY = 0
AND gbh.batch_status IN
(3, 4) -- batch status harus completed atau closed
-- AND gmd.material_detail_id = mmt.trx_source_line_id
AND gbh.organization_id = mp.organization_id
AND gmd.COST_ALLOC = 1
ORDER BY 1