GO
/****** Object:  StoredProcedure [dbo].[aa_sp_saile]    Script Date: 11/12/2010 23:52:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[aa_sp_saile]
@BegDate as nvarchar(20)='',
@EndDate as nvarchar(20)='',
@fpro_id as nvarchar(20)
as
if len(@BegDate) <> 0 and len(@EndDate) <> 0 begin
SELECT jr.fpro_id, jm.fsono, jm.fjobno, jm.fstatus, jm.frel_dt, jm.fpartno, jm.fac, jm.fquantity, sm.fcustno,
sm.fcompany, sm.forderdate, (sm.fsocoord +'/'+ sm.festimator) as coordEst
FROM jodrtg jr LEFT OUTER JOIN jomast jm ON jr.fjobno=jm.fjobno

      LEFT OUTER JOIN somast sm ON jm.fsono = sm.fsono

      LEFT OUTER JOIN soitem si ON jm.fsono=si.fsono AND si.fpartno=jm.fpartno AND jm.fkey = si.finumber+si.fpartrev

WHERE jr.fpro_id IN (@fpro_id)

      AND jm.fstatus IN ('OPEN', 'RELEASE')

      AND convert(nvarchar, jm.frel_dt, 101) >= @BegDate
      AND convert(nvarchar, jm.frel_dt, 101) <= @EndDate

      and year(jm.frel_dt) not in ('2044', '2020')

      AND si.fordertype <> 'MO'

      AND sm.fstatus NOT IN ('CANCELLED', 'ONHOLD','CLOSED')

ORDER BY jm.frel_dt
end
else begin
SELECT jr.fpro_id, jm.fsono, jm.fjobno, jm.fstatus, jm.frel_dt, jm.fpartno, jm.fac, jm.fquantity, sm.fcustno,
sm.fcompany, sm.forderdate, (sm.fsocoord +'/'+ sm.festimator) as coordEst
FROM jodrtg jr LEFT OUTER JOIN jomast jm ON jr.fjobno=jm.fjobno

      LEFT OUTER JOIN somast sm ON jm.fsono = sm.fsono

      LEFT OUTER JOIN soitem si ON jm.fsono=si.fsono AND si.fpartno=jm.fpartno AND jm.fkey = si.finumber+si.fpartrev

WHERE jr.fpro_id IN (@fpro_id)

      AND jm.fstatus IN ('OPEN', 'RELEASE')


      AND si.fordertype <> 'MO'
      and year(jm.frel_dt) not in ('2044', '2020')

      AND sm.fstatus NOT IN ('CANCELLED', 'ONHOLD','CLOSED')

ORDER BY jm.frel_dt
end

exec aa_sp_saile @fpro_id='YAGLASR', @BegDate='11/1/2010', @EndDate='11/10/2010'