DPP Item Inventory

SELECT 
msiK.concatenated_segments ITEMNUM,
did.quantity QUANTITY,
ood.organization_name WAREHOUSE,
nvl(serial_number, 'No Serial Number Available') SERIAL_NUMBER
FROM
dpp_transaction_headers_all dh
JOIN
dpp_transaction_lines_all dl
ON dh.transaction_header_id = dl.transaction_header_id
AND dh.transaction_number = :TRANSACTION_NUMBER
INNER JOIN
 org_organization_definitions ood
ON dh.org_id = ood.operating_unit
INNER JOIN
financials_system_params_all fspa
ON dh.org_id  = fspa.org_id 
INNER JOIN
dpp_inventory_details_all did
ON
did.organization_id = ood.organization_id
AND did.transaction_line_id = dl.transaction_line_id
INNER JOIN
mtl_system_items_kfv msiK
ON dl.inventory_item_id = msiK.inventory_item_id
AND msiK.organization_id = ood.organization_id
AND msiK.organization_id = fspa.inventory_organization_id
LEFT OUTER JOIN
mtl_serial_numbers msn
ON msn.current_organization_id = did.organization_id 
AND msn.inventory_item_id = did.inventory_item_id
AND msn.current_status NOT IN( 4,5)
AND(
    (dh.days_covered is null and msn.creation_date < dh.effective_start_date)
     OR
    (dh.days_covered is NOT NULL
    and msn.creation_date >= dh.effective_start_date-dh.days_covered
    and msn.creation_date <  dh.effective_start_date)
  )