select * from
(
SELECT 'IMPORTS','FULL',DECODE (a.packagetypecode, 'CNT12', '20', 'CNT6', '40'), nvl(B.Cnt,0),
DECODE (a.packagetypecode, 'CNT6', nvl(B.Cnt,0) *2, 'CNT12', nvl(B.Cnt,0)) T 
FROM (
    SELECT DISTINCT packagetypecode
    FROM suppackagetypes
    where packagetypecode in ('CNT12','CNT6')
) A
LEFT JOIN (
    SELECT mi.packagetypecode, COUNT(mi.PACKAGETYPECODE) AS Cnt
    FROM manifestitems mi, manifestsequences ms
    WHERE mi.booknumber in (SELECT booknumber
  FROM books b
 WHERE BOOKTYPE = 'M' 
 AND b.arrivalnumber IN (
          SELECT va.arrivalnumber
            FROM supvessels sv, vesselarrivals va
           WHERE sv.vesselnumber = va.vesselnumber
             AND sv.shippingline =
                    DECODE (:p_shippingline,
                            'ALL', sv.shippingline,
                            :p_shippingline
                           )))

and mi.empty = 'N'
      and mi.BOOKNUMBER = ms.booknumber
      and ms.TRANSHIPMENT = 'N' 
    GROUP BY mi.packagetypecode
) B
ON A.packagetypecode = B.packagetypecode
UNION
SELECT 'IMPORTS','EMPTY',DECODE (a.packagetypecode, 'CNT12', '20', 'CNT6', '40'), nvl(B.Cnt,0),
DECODE (a.packagetypecode, 'CNT6', nvl(B.Cnt,0) *2, 'CNT12', nvl(B.Cnt,0)) T 
FROM (
    SELECT DISTINCT packagetypecode
    FROM suppackagetypes
    where packagetypecode in ('CNT12','CNT6')
) A
LEFT JOIN (
    SELECT mi.packagetypecode, COUNT(mi.PACKAGETYPECODE) AS Cnt
    FROM manifestitems mi, manifestsequences ms
    WHERE mi.booknumber in (SELECT booknumber
  FROM books b
 WHERE BOOKTYPE = 'M' 
 AND b.arrivalnumber IN (
          SELECT va.arrivalnumber
            FROM supvessels sv, vesselarrivals va
           WHERE sv.vesselnumber = va.vesselnumber
             AND sv.shippingline =
                    DECODE (:p_shippingline,
                            'ALL', sv.shippingline,
                            :p_shippingline
                           )))

and mi.empty = 'Y'
      and mi.BOOKNUMBER = ms.booknumber
      and ms.TRANSHIPMENT = 'N' 
    GROUP BY mi.packagetypecode
) B
ON A.packagetypecode = B.packagetypecode
)

union

select * from
(
SELECT 'EXPORTS','FULL',DECODE (a.packagetypecode, 'CNT12', '20', 'CNT6', '40'), nvl(B.Cnt,0),
DECODE (a.packagetypecode, 'CNT6', nvl(B.Cnt,0) *2, 'CNT12', nvl(B.Cnt,0)) T 
FROM (
    SELECT DISTINCT packagetypecode
    FROM suppackagetypes
    where packagetypecode in ('CNT12','CNT6')
) A
LEFT JOIN (
    SELECT si.packagetypecode, COUNT(si.PACKAGETYPECODE) AS Cnt
    from shippingitems si,shippingsequences ss 
    WHERE si.booknumber in (SELECT booknumber
  FROM books b
 WHERE BOOKTYPE = 'M' 
 AND b.arrivalnumber IN (
          SELECT va.arrivalnumber
            FROM supvessels sv, vesselarrivals va
           WHERE sv.vesselnumber = va.vesselnumber
             AND sv.shippingline =
                    DECODE (:p_shippingline,
                            'ALL', sv.shippingline,
                            :p_shippingline
                           )))

and si.empty = 'N'
      and si.BOOKNUMBER = ss.booknumber
      and ss.TRANSHIPMENT = 'N' 
    GROUP BY si.packagetypecode
) B
ON A.packagetypecode = B.packagetypecode
UNION
SELECT 'EXPORTS','EMPTY',DECODE (a.packagetypecode, 'CNT12', '20', 'CNT6', '40'), nvl(B.Cnt,0),
DECODE (a.packagetypecode, 'CNT6', nvl(B.Cnt,0) *2, 'CNT12', nvl(B.Cnt,0)) T  
FROM (
    SELECT DISTINCT packagetypecode
    FROM suppackagetypes
    where packagetypecode in ('CNT12','CNT6')
) A
LEFT JOIN (
    SELECT mi.packagetypecode, COUNT(mi.PACKAGETYPECODE) AS Cnt
    FROM manifestitems mi, manifestsequences ms
    WHERE mi.booknumber in (SELECT booknumber
  FROM books b
 WHERE BOOKTYPE = 'M' 
 AND b.arrivalnumber IN (
          SELECT va.arrivalnumber
            FROM supvessels sv, vesselarrivals va
           WHERE sv.vesselnumber = va.vesselnumber
             AND sv.shippingline =
                    DECODE (:p_shippingline,
                            'ALL', sv.shippingline,
                            :p_shippingline
                           )))

and mi.empty = 'Y'
      and mi.BOOKNUMBER = ms.booknumber
      and ms.TRANSHIPMENT = 'N' 
    GROUP BY mi.packagetypecode
) B
ON A.packagetypecode = B.packagetypecode
)

union

select * from
(
SELECT 'TRANSHIPMENTS','FULL',DECODE (a.packagetypecode, 'CNT12', '20', 'CNT6', '40'), nvl(B.Cnt,0),
DECODE (a.packagetypecode, 'CNT6', nvl(B.Cnt,0) *2, 'CNT12', nvl(B.Cnt,0)) T 
  FROM (SELECT DISTINCT packagetypecode
                   FROM suppackagetypes
                  WHERE packagetypecode IN ('CNT12', 'CNT6')) a
       LEFT JOIN
       (SELECT   mi.packagetypecode, COUNT (mi.packagetypecode) AS cnt
            FROM manifestitems mi, manifestsequences ms
           WHERE mi.booknumber in (SELECT booknumber
  FROM books b
 WHERE BOOKTYPE = 'M' 
 AND b.arrivalnumber IN (
          SELECT va.arrivalnumber
            FROM supvessels sv, vesselarrivals va
           WHERE sv.vesselnumber = va.vesselnumber
             AND sv.shippingline =
                    DECODE (:p_shippingline,
                            'ALL', sv.shippingline,
                            :p_shippingline
                           )))

AND mi.empty = 'N'
             AND mi.booknumber = ms.booknumber
             AND ms.transhipment = 'N'
        GROUP BY mi.packagetypecode) b ON a.packagetypecode =
                                                             b.packagetypecode
UNION
SELECT 'TRANSHIPMENTS','EMPTY',DECODE (a.packagetypecode, 'CNT12', '20', 'CNT6', '40'), nvl(B.Cnt,0),
DECODE (a.packagetypecode, 'CNT6', nvl(B.Cnt,0) *2, 'CNT12', nvl(B.Cnt,0)) T 
  FROM (SELECT DISTINCT packagetypecode
                   FROM suppackagetypes
                  WHERE packagetypecode IN ('CNT12', 'CNT6')) a
       LEFT JOIN
       (SELECT   mi.packagetypecode, COUNT (mi.packagetypecode) AS cnt
            FROM manifestitems mi, manifestsequences ms
           WHERE mi.booknumber in (SELECT booknumber
  FROM books b
 WHERE BOOKTYPE = 'M' 
 AND b.arrivalnumber IN (
          SELECT va.arrivalnumber
            FROM supvessels sv, vesselarrivals va
           WHERE sv.vesselnumber = va.vesselnumber
             AND sv.shippingline =
                    DECODE (:p_shippingline,
                            'ALL', sv.shippingline,
                            :p_shippingline
                           )))

AND mi.empty = 'Y'
             AND mi.booknumber = ms.booknumber
             AND ms.transhipment = 'Y'
        GROUP BY mi.packagetypecode) b ON a.packagetypecode =
                                                             b.packagetypecode
)