WIP  Work-order query

SELECT TO_CHAR (mmt.transaction_date, 'dd-MON-YYYY') transaction_date,
  '' customer_name,
  msib.segment1 item_no,
  mic.segment1 item_category,
  msib.inventory_item_id,
  msib.organization_id,
  mic.segment4 item_sub_category,
  msib.description,
  msib.attribute1
  || 'X'
  || msib.attribute11 item_size,
  msib.attribute1 core,
  msib.attribute11 sqmm,
  msib.attribute19 color,
  wdj.attribute3 so_number,
  wdj.attribute5 fg_code,
  mmt.transaction_quantity quantity,
  mmt.OPERATION_SEQ_NUM,
  mic.segment5,
  we.wip_entity_name,
  mp.organization_code,
  NVL (mlt.lot_number, '-') lot_number,
  NVL (mlt.transaction_quantity, 0) lot_qty,
  mmt.transaction_uom,
  ROUND (cst.unit_cost, 4) mat_unit_cost,
  ROUND (cst_rs.unit_cost, 4) unit_cost_resource,
  ROUND (cst_oh.unit_cost, 4) unit_cost_oh,
  wdj.status_type_disp,
  /*(SELECT bd.description
  FROM bom_operational_routings bor,
  bom_operation_sequences bos,
  bom_departments bd
  WHERE bor.routing_sequence_id = bos.routing_sequence_id
  AND bd.department_id = bos.department_id
  AND wdj.primary_item_id = bor.assembly_item_id
  AND wdj.organization_id = bor.organization_id
  AND ROWNUM = 1) department,*/
  (
  SELECT bd.description
  FROM wip_operations_v wo,
    bom_departments bd
  WHERE wo.wip_entity_id = wdj.wip_entity_id --808107
  AND wo.department_id   = bd.department_id
  AND wo.organization_id = wdj.organization_id
  AND ROWNUM             = 1
  ) department,
  mln.attribute4 nsl,
  wdj.attribute9 planning_month
FROM mtl_material_transactions mmt,
  mtl_system_items_b msib,
  mtl_item_categories_v mic,
  wip_entities we,
  wip_discrete_jobs_v wdj,
  mtl_parameters mp,
  mtl_transaction_lot_numbers mlt,
  mtl_lot_numbers mln, -- added
  cst_inv_distribution_v cst,
  cst_inv_distribution_v cst_rs,
  cst_inv_distribution_v cst_oh
WHERE mmt.transaction_type_id = 44
AND mmt.transaction_id        = mlt.transaction_id(+)
AND msib.inventory_item_id    = mmt.inventory_item_id
AND msib.organization_id      = mmt.organization_id
AND mic.inventory_item_id     = mmt.inventory_item_id
AND mic.organization_id       = mmt.organization_id
AND we.wip_entity_id          = mmt.transaction_source_id
AND wdj.wip_entity_id         = we.wip_entity_id
--and we.WIP_ENTITY_NAME='647581'
AND mp.organization_id        = mmt.organization_id
AND cst.transaction_id        = mmt.transaction_id
AND cst.cost_element_id       = 1
AND cst.line_type_name        = 'Inv valuation'
AND cst_oh.transaction_id     = mmt.transaction_id
AND cst_oh.cost_element_id    = 5
AND cst_oh.line_type_name     = 'Inv valuation'
AND cst_rs.transaction_id     = mmt.transaction_id
AND cst_rs.cost_element_id    = 3
AND cst_rs.line_type_name     = 'Inv valuation'
  --added for nsl
AND mlt.organization_id   = mln.organization_id(+)
AND mlt.inventory_item_id = mln.inventory_item_id(+)
AND mlt.lot_number        = mln.lot_number(+)
  --------------------------------
AND mic.category_set_id       = 1
and trunc(mmt.transaction_date) between trunc(:p_trans) and trunc(:p_trans_to)
and mmt.OPERATION_SEQ_NUM=10
and mmt.organization_id=146
--AND mp.organization_code      = NVL (xxply_daily_manu_pkg.fn_get_organization (), mp.organization_code )
--AND mic.segment1              = NVL (xxply_daily_manu_pkg.fn_get_inventory_type (), mic.segment1)
--AND mic.segment2              = NVL (xxply_daily_manu_pkg.fn_get_business_verical (), mic.segment2 )
--AND wdj.status_type_disp      = NVL (xxply_daily_manu_pkg.fn_get_job_status (), status_type_disp)
--AND NVL (wdj.attribute3, '0') = NVL (xxply_daily_manu_pkg.fn_get_so_no (), NVL (wdj.attribute3, '0') )
--AND TRUNC (mmt.transaction_date) BETWEEN TRUNC (TO_DATE (xxply_daily_manu_pkg.fn_get_fr_date (), 'YYYY/MM/dd HH24:mi:ss' ) ) 
AND TRUNC (TO_DATE (xxply_daily_manu_pkg.fn_get_to_date (), 'YYYY/MM/dd HH24:mi:ss' ) )

---------------------------------------------------------------------------------------------------------------------

SELECT  distinct TO_CHAR (mmt.transaction_date, 'dd-MON-YYYY') transaction_date,
  '' customer_name,
  msib.segment1 item_no,
  mic.segment1 item_category,
  msib.inventory_item_id,
  msib.organization_id,
  mic.segment4 item_sub_category,
  msib.description,
  msib.attribute1
  || 'X'
  || msib.attribute11 item_size,
  msib.attribute1 core,
  msib.attribute11 sqmm,
  msib.attribute19 color,
  wdj.attribute3 so_number,
  wdj.attribute5 fg_code,
  mmt.transaction_quantity quantity,
  mmt.OPERATION_SEQ_NUM,
  mic.segment5,
  we.wip_entity_name,
  mp.organization_code,
  NVL (mlt.lot_number, '-') lot_number,
  NVL (mlt.transaction_quantity, 0) lot_qty,
  mmt.transaction_uom,
  ROUND (cst.unit_cost, 4) mat_unit_cost,
  ROUND (cst_rs.unit_cost, 4) unit_cost_resource,
  ROUND (cst_oh.unit_cost, 4) unit_cost_oh,
  wdj.status_type_disp,
  (
  SELECT bd.description
  FROM wip_operations_v wo,
    bom_departments bd
  WHERE wo.wip_entity_id = wdj.wip_entity_id --808107
  AND wo.department_id   = bd.department_id
  AND wo.organization_id = wdj.organization_id
  AND ROWNUM             = 1
  ) department,
  mln.attribute4 nsl,
  wdj.attribute9 planning_month
FROM mtl_material_transactions mmt,
  mtl_system_items_b msib,
  mtl_item_categories_v mic,
  wip_entities we,
  wip_discrete_jobs_v wdj,
  mtl_parameters mp,
  mtl_transaction_lot_numbers mlt,
  mtl_lot_numbers mln, -- added
  cst_inv_distribution_v cst,
  cst_inv_distribution_v cst_rs,
  cst_inv_distribution_v cst_oh
WHERE mmt.transaction_type_id = 44
AND mmt.transaction_id        = mlt.transaction_id(+)
AND msib.inventory_item_id    = mmt.inventory_item_id
AND msib.organization_id      = mmt.organization_id
AND mic.inventory_item_id     = mmt.inventory_item_id
AND mic.organization_id       = mmt.organization_id
AND we.wip_entity_id          = mmt.transaction_source_id
AND wdj.wip_entity_id         = we.wip_entity_id
AND mp.organization_id        = mmt.organization_id
AND cst.transaction_id        = mmt.transaction_id
AND cst.cost_element_id       = 1
AND cst.line_type_name        = 'Inv valuation'
AND cst_oh.transaction_id     = mmt.transaction_id
AND cst_oh.cost_element_id    = 5
AND cst_oh.line_type_name     = 'Inv valuation'
AND cst_rs.transaction_id     = mmt.transaction_id
AND cst_rs.cost_element_id    = 3
AND cst_rs.line_type_name     = 'Inv valuation'
  --added for nsl
AND mlt.organization_id   = mln.organization_id(+)
AND mlt.inventory_item_id = mln.inventory_item_id(+)
AND mlt.lot_number        = mln.lot_number(+)
  --------------------------------
AND mic.category_set_id       = 1
and trunc(mmt.transaction_date) between trunc(:p_trans) and trunc(:p_trans_to)
and mmt.OPERATION_SEQ_NUM=10

and mmt.organization_id=146