CREATE OR REPLACE VIEW V_MLS_SCDW_SN AS
------------------------------------------------------------------------
-- PURPOSE: Harvest as many serial numbers in MLS from SCDW as you can.
--
--  Who     When            What
--  ---     ----            ----
--
--  JJM     8-MAR-2005      Started for Diana.
--------------------------------------------------------------------------
(  
select s.bcfname,
       s.usid,
       s.name_alias,
       m.MLS_SERIAL,
       s.SERIAL_NUM SCDW_SERIAL,
       m.MLS_PROD_CD,
       s.PRODUCT_CD SCDW_PROD_CD,
       m.DESCRIPTION MLS_DESC

from  
(
-- SCDW Serial No's from ITEM  
select b1.gid,
       b1.seqnbr,
       m.bcfname,
       m.usid,
       i.name_alias,
       i.product_cd,
       i.serial_num
  from scdw.bcf@FOMSRDB b1,
       scdw.item@FOMSRDB i,
       (SELECT b.NAME "BCFNAME",
               b.GID,
               MAX(CUSTOMER_SITE_ID) "USID",
               MAX(SEQNBR) "MX",
               MAX(REFRESH_DT) "REFRESH_DT"
          FROM SCDW.BCF@FOMSRDB b
         WHERE NOT EXISTS
         (SELECT 1
                  FROM SCDW.BCF@FOMSRDB b2
                 WHERE b2.GID = b.GID AND SUBSTR(b.NAME, 1, 1) = '1') --NO REHOMES!!! 
         GROUP BY b.NAME, b.GID) m
 where b1.gid || b1.seqnbr = m.gid || m.mx and
       b1.gid || b1.seqnbr = i.gid || i.seqnbr
) s,

(
-- MLS Serial No's from DELIVERY_ITEMS
SELECT 
  SHIPMENT_LINES.PROD_RFR_PRODUCT_CD MLS_PROD_CD,
  SHIP_PRODUCTS.DESCRIPTION,
  DELIVERY_ITEMS.SERIAL_NUMBER MLS_SERIAL
FROM
  mls018.SHIPMENT_LINES,
  mls018.PRODUCTS  SHIP_PRODUCTS,
  mls018.DELIVERY_ITEMS
WHERE
       ( SHIPMENT_LINES.PROD_RFR_CODESYS_CD=SHIP_PRODUCTS.PK_CODESYS_CD  )
  AND  ( SHIPMENT_LINES.PROD_RFR_PRODUCT_CD=SHIP_PRODUCTS.PK_PRODUCT_CD  )
  AND  ( SHIPMENT_LINES.PK_SHPLINE_ID=DELIVERY_ITEMS.SHLI_RLTTO_SHPLINE_ID(+)  )
  AND  ( SHIPMENT_LINES.PK_SHP_ID=DELIVERY_ITEMS.SHLI_RLTTO_SHP_ID(+)  )
  AND  ( SHIPMENT_LINES.PK_IIN_ID=DELIVERY_ITEMS.SHLI_RLTTO_IIN_ID(+)  )
  ) m

where  s.serial_num is not null
and    s.serial_num <> '0'
and    s.serial_num <> '00000000000'        
and    (
        upper(substr(m.MLS_SERIAL,-12)) = upper(substr(s.serial_num,-12)) OR
        upper(m.MLS_SERIAL) = upper(s.serial_num)                         OR
        upper(m.MLS_SERIAL) = upper(substr(s.serial_num,2))
       )
)