ECC Inventory Management, Inbound Details (1)

select
x.*
from
(
 
SELECT * FROM (
SELECT
  reiv.ecc_spec_id AS receiving_id,
  reiv.ecc_spec_id,
  reiv.trx_type as trx_type_code,
  reiv.priority_flag as priority_flag_code,
  reiv.shipment_line_id,
  reiv.organization_code,
  reiv.expected_trx_date,
  (SELECT meaning FROM fnd_lookup_values WHERE lookup_code = reiv.source_doc_type AND lookup_type = 'DOC_TYPE' AND view_application_id=3 AND language = msit.language) source_doc_type,
  (SELECT meaning FROM fnd_lookup_values WHERE lookup_code = reiv.trx_type AND lookup_type = 'INV_RCV_ECC_TRX_TYPES' AND view_application_id=3 AND language = msit.language) trx_type,
  (SELECT meaning FROM fnd_lookup_values WHERE lookup_code = reiv.priority_flag AND lookup_type = 'YES_NO' AND view_application_id=3 AND language = msit.language) priority_flag,
  reiv.parent_trx_type,
  decode(reiv.source_doc_type,'INTSHIP',(SELECT haou.name FROM MTL_PARAMETERS MP, HR_ALL_ORGANIZATION_UNITS_TL HAOU WHERE mp.organization_code = reiv.source_name AND HAOU.organization_id = mp.organization_id AND language = msit.language),
         'REQ',(SELECT haou.name FROM MTL_PARAMETERS MP, HR_ALL_ORGANIZATION_UNITS_TL HAOU WHERE mp.organization_code = reiv.source_name AND HAOU.organization_id = mp.organization_id AND language = msit.language),reiv.source_name) source_name,
  reiv.document_num,
  reiv.document_line_num,
  reiv.item_number,
  msit.description item_description,
  reiv.ordered_qty,
  reiv.primary_qty,
  reiv.trx_qty,
  reiv.amount,
  reiv.routing_id,
  reiv.to_organization_id,
  reiv.item_id,
  reiv.lpn_id,
  reiv.po_header_id,
  reiv.oe_order_header_id,
  reiv.license_plate_number,
  reiv.gross_weight,
  reiv.container_volume,
  reiv.content_volume,
  msit.LANGUAGE LANGUAGE,
  reiv.dock_to_stock,
  nvl2(reiv.lpn_id, 'N', 'Y')as enable_action,
  reiv.deliver_date  FROM rcv_ecc_inbound_v reiv,
     mtl_system_items_tl msit WHERE reiv.item_id = msit.inventory_item_id
AND   reiv.to_organization_id = msit.organization_id AND msit.LANGUAGE in ( 'US')) PIVOT ( Max(source_doc_type) AS source_doc_type,
          Max(source_name) AS source_name,
          Max(trx_type) AS trx_type,
          Max(priority_flag) AS priority_flag,
          Max(item_description) AS item_description for LANGUAGE in ( 'US' "US"))
) x
where
2=2