SELECT ood.organization_code, ood.organization_id, ood.organization_name,
ood.operating_unit, gbh.batch_no,
/* DECODE (gmg.group_id,1002, 'PLANT_1'
,1003, 'PLANT_2'
,1004, 'PLANT_3'
,1001 ,'WIP_CUBE'
,1000, 'OSP' ) Plant, */
TRUNC (gbh.actual_cmplt_date) date_of_production,
TRUNC (gbh.actual_start_date) actual_start_date,
msik.concatenated_segments product,
msik.description product_description, gmd.actual_qty qty_produced,
gmd.plan_qty, gmd.dtl_um uom,
(Select gms.SOURCE_COMMENT
from GMD_SAMPLES gms
where gms.LOT_NUMBER = mtln.lot_number
and gms.organization_id = msik.organization_id
and gms.INVENTORY_ITEM_ID = msik.INVENTORY_ITEM_ID
and rownum = 1) QC_Comments,
ROUND ( gmd.actual_qty
* 100
/ (SELECT DECODE (SUM (gmd1.actual_qty),
NULL, 1,
0, 1,
SUM (gmd1.actual_qty)
)
FROM gme_material_details gmd1
WHERE gmd1.line_type = '-1' AND gmd1.batch_id = gbh.batch_id),
2
) yield_percent,
flv.meaning batch_status,
--mmt.transaction_id,
--mmt.source_code,
mtln.lot_number,
av.STATUS_CODE quality_status,
/* (SELECT flv_spec_disp.meaning spec_disposition
FROM gmd_samples gs,
gmd_sampling_events gse,
gmd_event_spec_disp gesd,
fnd_lookup_values flv_spec_disp
WHERE gs.sampling_event_id = gse.sampling_event_id
AND gse.sampling_event_id = gesd.sampling_event_id
AND gesd.disposition = flv_spec_disp.lookup_code
AND flv_spec_disp.lookup_type = 'GMD_QC_SAMPLE_DISP'
AND gs.lot_number = mtln.lot_number
AND gs.inventory_item_id = gmd.inventory_item_id
AND gs.organization_id = gmd.organization_id
AND ROWNUM = 1) quality_status,*/
mmt.transaction_quantity, mmt.transaction_uom, mmt.transaction_date,
gbh.attribute1 dcs_batch_number, gbh.attribute2 dcs_batch_date
FROM gme_batch_header gbh,
gme_material_details gmd,
mtl_system_items_kfv msik,
MTL_LOT_NUMBERS_ALL_V av,
org_organization_definitions ood,
-- gme_batch_groups_association gmg ,
fnd_lookup_values flv,
(SELECT *
FROM mtl_material_transactions
WHERE transaction_type_id IN (44, 17)) mmt,
mtl_transaction_lot_numbers mtln
WHERE gbh.batch_id = gmd.batch_id
--AND gmg.batch_id=gbh.batch_id
AND gmd.line_type = 1
AND gmd.cost_alloc =
(SELECT MAX (gmd1.cost_alloc)
FROM gme_material_details gmd1
WHERE gmd1.batch_id = gmd.batch_id AND gmd1.line_type = 1)
AND gmd.inventory_item_id = msik.inventory_item_id
AND gmd.organization_id = msik.organization_id
AND gmd.organization_id = ood.organization_id
AND gbh.batch_status = flv.lookup_code
and av.inventory_item_id=mtln.inventory_item_id
and av.organization_id = mtln.organization_id
and av.lot_number = mtln.lot_number
AND flv.lookup_type = 'GME_BATCH_STATUS'
AND flv.LANGUAGE (+) = 'PTB'
AND gmd.batch_id = mmt.transaction_source_id(+)
AND gmd.inventory_item_id = mmt.inventory_item_id(+)
--AND mmt.source_code(+) = 'OPM'
AND mmt.transaction_id = mtln.transaction_id(+)
-- AND ood.organization_id in (select ood.organization_id from
--AND gbh.batch_no = '3040004832';
AND ood.operating_unit = :p_org_id
/* and DECODE (gmg.group_id,1002, 'PLANT_1'
,1003, 'PLANT_2'
,1004, 'PLANT_3'
,1001 ,'WIP_CUBE'
,1000, 'OSP' ) = nvl(:p_plant, DECODE (gmg.group_id,1002, 'PLANT_1'
,1003, 'PLANT_2'
,1004, 'PLANT_3'
,1001 ,'WIP_CUBE'
,1000, 'OSP' )) */
AND gbh.batch_no BETWEEN NVL (:p_from_batch, gbh.batch_no)
AND NVL (:p_to_batch, gbh.batch_no)
AND flv.meaning = NVL (:p_status, flv.meaning)
AND msik.concatenated_segments between NVL(:P_FROM_ITEM,msik.concatenated_segments)
AND NVL(:P_TO_ITEM,msik.concatenated_segments)
AND TRUNC (mmt.transaction_date) BETWEEN NVL
(TRUNC (TO_DATE (:p_from_date,
'DD-MON-RRRR'
)
),
TRUNC (mmt.transaction_date)
)
AND NVL
(TRUNC (TO_DATE (:p_to_date,
'DD-MON-RRRR'
)
),
TRUNC (mmt.transaction_date)
)
ORDER BY gbh.batch_no
No comments:
Post a Comment