IF (@ship_id is not null)
{
    [SELECT Top 1 sl.ordnum,
            sl.ship_id,
            isnull(st.stop_id, '') as stop_id,
            isnull(cm.car_move_id, '') as car_move_id,
            sl.client_id,
            sl.wh_id
       From shipment sh
      Inner
       join shipment_line sl
         on sl.ship_id = sh.ship_id
       left outer
       join stop st
         on st.stop_id = sh.stop_id
       left outer
       join car_move cm
         on cm.car_move_id = st.car_move_id
      Where @+sh.ship_id
        and sh.shpsts != 'B']
}
Else IF (@ordnum is not null AND @client_id is not null)
{
    [SELECT Top 1 sl.ordnum,
            sl.ship_id,
            isnull(st.stop_id, '') as stop_id,
            isnull(cm.car_move_id, '') as car_move_id,
            sl.client_id,
            sl.wh_id
       From shipment sh
      Inner
       join shipment_line sl
         on sl.ship_id = sh.ship_id
       left outer
       join stop st
         on st.stop_id = sh.stop_id
       left outer
       join car_move cm
         on cm.car_move_id = st.car_move_id
      Where @+sl.ordnum
        AND @+sl.wh_id
        AND @+sl.client_id
        and sh.shpsts != 'B']
}
|
IF (@ship_id is null)
{
    [select '' as ordnum,
            '' as client_id,
            '' as wh_id,
            '' as bt_adr_id,
            '' as st_adr_id,
            '' as cponum,
            '' as cpodte,
            '' as vc_lbl_typ,
            '' as vc_lbl_frmt,
            '' as vc_mh10_supvnno,
            '' as custno,
            '' as st_adrnam,
            '' as st_attn_name '' as st_adr_ln1,
            '' as st_adr_ln2,
            '' as st_adr_ln3,
            '' as st_adrcty,
            '' as st_adrstc,
            '' as st_adrpsz,
            '' st_adrctry_name,
            '' as st_adrpsz,
            '' as ship_id,
            '' as client_name,
            '' as client_name,
            '' as carcod,
            '' as srvlvl,
            '' as late_shpdte,
            '' as late_dlvdte,
            '' as early_dlvdte,
            '' as entdte,
            '' orderheadermsg,
            '' as shiptomsg,
            '' as csterms,
            '' as salespersonmsg,
            '' as nottxt,
            '' as carnam,
            '' as bt_adrnam '' as bt_adrln1,
            '' as bt_adrln2,
            '' as bt_adrln3,
            '' as bt_adrcty,
            '' as bt_adrstc,
            '' as bt_adrpsz,
            '' as bt_adrctry_name,
            '' as bt_attn_name,
            '' as stclntcustno,
            '' as totcases,
            '' as pckqty]
}
Else IF (@ship_id is not null)
{
    [select adrmst.adrnam client_name
       from client
      inner
       join adrmst
         on client.adr_id = adrmst.adr_id
      where client.client_id = @client_id]
    |
    [select nvl(stgdte, '01/01/1900') stgdte,
            late_shpdte,
            late_dlvdte,
            early_dlvdte,
            nvl(carcod, ' ') carcod,
            nvl(srvlvl, ' ') srvlvl
       from shipment
      where wh_id = @wh_id
        and ship_id = @ship_id]
    |
    [Select
            -- Ship To Address here --
            st_adrnam = MAX(stadrmst.adrnam),
            st_attn_name = MAX(stadrmst.attn_name),
            st_adrln1 = MAX(ISNULL(stadrmst.adrln1, '')),
            st_adrln2 = MAX(ISNULL(stadrmst.adrln2, '')),
            st_adrln3 = MAX(ISNULL(stadrmst.adrln3, '')),
            st_adrcty = MAX(stadrmst.adrcty),
            st_adrstc = MAX(stadrmst.adrstc),
            st_adrpsz = MAX(stadrmst.adrpsz),
            st_adrctry_name = MAX(stadrmst.ctry_name)
       From ord
       left outer
       Join adrmst stadrmst
         on ord.st_adr_id = stadrmst.adr_id
      where ord.ordnum = @ordnum
        and ord.client_id = @client_id
        and ord.wh_id = @wh_id]
    |
    [Select ad.host_ext_id,
            ad.adrnam bt_adrnam,
            ad.adrln1 bt_adrln1,
            ad.adrln2 bt_adrln2,
            ad.adrln3 bt_adrln3,
            ad.adrcty bt_adrcty,
            ad.adrstc bt_adrstc,
            ad.adrpsz bt_adrpsz,
            ad.ctry_name bt_adrctry_name,
            ad.attn_name bt_attn_name
       from ord
      inner
       join adrmst ad
         on ord.btcust = ad.adr_id
      Where ord.ordnum = @ordnum
        and ord.client_id = @client_id
        and ord.wh_id = @wh_id
        and ad.adr_id = ord.btcust]
    |
    [select nottxt
       from ord_note
      where ordnum = @ordnum
        and client_id = @client_id
        and nottyp = 'REFERENCENO'
        and rownum < 2] catch(-1403)
    |
    [select nottxt stclntcustno
       from ord_note
      where ordnum = @ordnum
        and client_id = @client_id
        and nottyp = 'STCLNTCUSTNO'
        and rownum < 2] catch(-1403)
    |
    [select carnam
       from carhdr
      where carcod = @carcod
        and rownum < 2] catch(-1403)
    |
    [select sum(pckqty) pckqty
       from ord_line
      where ordnum = @ordnum
        and client_id = @client_id
        and wh_id = @wh_id] catch(-1403)
    |
    [select sum(uc_sub.totcases) totcases
       from (SELECT count(distinct iss.subnum) totcases,
                    -- 6 Total Pieces Shipped
                    max(s.carcod) carcod,
                    -- 10 Carrier

                    CASE WHEN UPPER(max(s.carcod)) = 'SKELPARCEL' THEN 0
                         ELSE (SUM(isnull(sl.tot_pln_pal_qty, 0)))
                    end totpal,
                    CASE WHEN UPPER(max(s.carcod)) = 'SKELPARCEL' THEN 0
                         ELSE ISNULL(COUNT(distinct iv.lodnum), 0)
                    end totpallet,
                    -- 7 Lift Count
                    sum(isnull(sl.tot_pln_cube, 0)) / 1728 cube,
                    -- 13 Cube

                    (SUM(ind.untqty / isnull(pdt.untqty, 1) * pdt.grswgt) * 0.02835) wgh
                    --- Linst C

               FROM ORD o
              INNER
               JOIN SHIPMENT_LINE sl
                 on sl.ordnum = o.ordnum
                and sl.wh_id = o.wh_id
                and sl.client_id = o.client_id
              INNER
               JOIN SHIPMENT s
                 on s.ship_id = sl.ship_id
              INNER
               JOIN invdtl ind
                 on ind.ship_line_id = sl.ship_line_id
              INNER
               JOIN invsub iss
                 on iss.subnum = ind.subnum
              INNER
               JOIN invlod iv
                 on iv.lodnum = iss.lodnum
              INNER
               JOIN (SELECT MAX(ordnum) ordnum,
                            prtnum,
                            ftpcod,
                            prt_client_id,
                            MAX(wh_id) wh_id,
                            ordlin,
                            SUM(shpqty) shpqty,
                            MAX(client_id) client_id
                       FROM ORD_LINE
                      WHERE ordnum = @ordnum
                        and wh_id = @wh_id
                        and client_id = @client_id
                      GROUP BY prtnum,
                            ftpcod,
                            prt_client_id,
                            client_id,
                            ordlin) ol
                 ON ol.ordnum = o.ordnum
                and ol.wh_id = o.wh_id
                and ol.client_id = o.client_id
                and ol.ordlin = sl.ordlin
               LEFT
               JOIN ORD_LINE_NOTE oln
                 ON ol.ordnum = oln.ordnum
                AND ol.ordlin = oln.ordlin
                and ol.client_id = oln.client_id
                and ol.wh_id = oln.wh_id
                and oln.nottyp = 'ORDEREDBASE'
               LEFT
               JOIN PRTFTP_DTL pdt
                 ON ol.prtnum = pdt.prtnum
                and ol.ftpcod = pdt.ftpcod
                AND ol.prt_client_id = pdt.prt_client_id
                and ol.wh_id = pdt.wh_id
                and pdt.uomcod = oln.nottxt
               JOIN PRTFTP_DTL pdt1
                 ON ol.prtnum = pdt1.prtnum
                    /* and ol.ftpcod = pdt.ftpcod */
                AND ol.prt_client_id = pdt1.prt_client_id
                and ol.wh_id = pdt1.wh_id
                and pdt1.ftpcod = ind.ftpcod
                and pdt1.cas_flg = 1
              WHERE o.ordnum = @ordnum
                and o.client_id = @client_id
                and o.wh_id = @wh_id
                and s.shpsts in ('C', 'S')
                and upper(iss.subnum) like 'C%'
              GROUP BY o.ordnum,
                    sl.ship_id
             union
             SELECT CASE WHEN UPPER(max(s.carcod)) = 'SKELPARCEL' THEN MAX(IV.PALPOS)
                         else (CASE when (sum(CAST(ind.untqty as float) / isnull(pdt1.untqty, 1)) < 1) THEN count(distinct iss.subnum)
                                    ELSE ceil(sum(CAST(ind.untqty as float) / isnull(pdt1.untqty, 1)))
                               END)
                    end totcases,
                    -- 6 Total Pieces Shipped
                    max(s.carcod) carcod,
                    -- 10 Carrier

                    CASE WHEN UPPER(max(s.carcod)) = 'SKELPARCEL' THEN 0
                         ELSE (SUM(isnull(sl.tot_pln_pal_qty, 0)))
                    end totpal,
                    CASE WHEN UPPER(max(s.carcod)) = 'SKELPARCEL' THEN 0
                         ELSE ISNULL(COUNT(distinct iv.lodnum), 0)
                    end totpallet,
                    -- 7 Lift Count
                    sum(isnull(sl.tot_pln_cube, 0)) / 1728 cube,
                    -- 13 Cube

                    (SUM(ind.untqty / isnull(pdt.untqty, 1) * pdt.grswgt) * 0.02835) wgh
                    --- Linst C

               FROM ORD o
              INNER
               JOIN SHIPMENT_LINE sl
                 on sl.ordnum = o.ordnum
                and sl.wh_id = o.wh_id
                and sl.client_id = o.client_id
              INNER
               JOIN SHIPMENT s
                 on s.ship_id = sl.ship_id
              INNER
               JOIN invdtl ind
                 on ind.ship_line_id = sl.ship_line_id
              INNER
               JOIN invsub iss
                 on iss.subnum = ind.subnum
              INNER
               JOIN invlod iv
                 on iv.lodnum = iss.lodnum
              INNER
               JOIN (SELECT MAX(ordnum) ordnum,
                            prtnum,
                            ftpcod,
                            prt_client_id,
                            MAX(wh_id) wh_id,
                            ordlin,
                            SUM(shpqty) shpqty,
                            MAX(client_id) client_id
                       FROM ORD_LINE
                      WHERE ordnum = @ordnum
                        and wh_id = @wh_id
                        and client_id = @client_id
                      GROUP BY prtnum,
                            ftpcod,
                            prt_client_id,
                            client_id,
                            ordlin) ol
                 ON ol.ordnum = o.ordnum
                and ol.wh_id = o.wh_id
                and ol.client_id = o.client_id
                and ol.ordlin = sl.ordlin
               LEFT
               JOIN ORD_LINE_NOTE oln
                 ON ol.ordnum = oln.ordnum
                AND ol.ordlin = oln.ordlin
                and ol.client_id = oln.client_id
                and ol.wh_id = oln.wh_id
                and oln.nottyp = 'ORDEREDBASE'
               LEFT
               JOIN PRTFTP_DTL pdt
                 ON ol.prtnum = pdt.prtnum
                and ol.ftpcod = pdt.ftpcod
                AND ol.client_id = pdt.prt_client_id
                and ol.wh_id = pdt.wh_id
                and pdt.uomcod = oln.nottxt
               JOIN PRTFTP_DTL pdt1
                 ON ol.prtnum = pdt1.prtnum
                    /* and ol.ftpcod = pdt1.ftpcod */
                AND ol.client_id = pdt1.prt_client_id
                and ol.wh_id = pdt1.wh_id
                and pdt1.ftpcod = ind.ftpcod
                and pdt1.cas_flg = 1
              WHERE o.ordnum = @ordnum
                and o.client_id = @client_id
                and o.wh_id = @wh_id
                and s.shpsts in ('C', 'S')
                and upper(iss.subnum) not like 'C%'
              GROUP BY o.ordnum,
                    sl.ship_id) uc_sub]
    |
    [Select ord.ordnum,
            ord.client_id,
            ord.wh_id,
            ord.bt_adr_id,
            ord.st_adr_id,
            isnull(ord.cponum, '    ') as cponum,
            nvl(Convert(char(10), ord.cpodte, 101), '  /  /    ') as cpodte,
            ord.vc_lbl_typ,
            ord.vc_mh10_typ,
            ord.vc_lbl_frmt,
            ord.vc_mh10_supvnno,
            @host_ext_id as custno,
            @st_adrnam as st_adrnam,
            @st_attn_name as st_attn_name,
            @st_adrln1 as st_adrln1,
            @st_adrln2 as st_adrln2,
            @st_adrln3 as st_adrln3,
            @st_adrcty as st_adrcty,
            @st_adrstc as st_adrstc,
            @st_adrpsz as st_adrpsz,
            @st_adrctry_name as st_adrctry_name,
            @ship_id as ship_id,
            @stop_id as stop_id,
            @client_name as client_name,
            @carcod as carcod,
            @srvlvl as srvlvl,
            to_date(@late_shpdte, 'YYYYMMDD') late_shpdte,
            to_date(@late_dlvdte, 'YYYYMMDD') late_dlvdte,
            to_date(@early_dlvdte, 'YYYYMMDD') early_dlvdte,
            to_date(@stgdte, 'YYYYMMDD') stgdte,
            ord.entdte,
            -- Note Message here --
            ORDERHEADERMSG = dbo.fnGetNoteTxtIn1Line(ord.ordnum, 'ORDERHEADERMSG'),
            SHIPTOMSG = dbo.fnGetNoteTxtIn1Line(ord.ordnum, 'SHIPTOMSG'),
            CSTERMS = dbo.fnGetNoteTxtIn1Line(ord.ordnum, 'CSTTERMS'),
            SALEPERSONMSG = dbo.fnGetNoteTxtIn1Line(ord.ordnum, 'SALEPERSONMSG'),
            @nottxt nottxt,
            @carnam carnam,
            @bt_adrnam bt_adrnam,
            @bt_adrln1 bt_adrln1,
            @bt_adrln2 bt_adrln2,
            @bt_adrln3 bt_adrln3,
            @bt_adrcty bt_adrcty,
            @bt_adrstc bt_adrstc,
            @bt_adrpsz bt_adrpsz,
            @bt_adrctry_name bt_adrctry_name,
            @bt_attn_name bt_attn_name,
            @stclntcustno stclntcustno,
            @totcases totcases,
            @pckqty pckqty
       From ord ord
      where ord.client_id = @client_id
        and ord.wh_id = @wh_id
        and ord.ordnum = @ordnum]
}