SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/*******************************************************************************************************
  설정 [ 폰트(굴림체) , 탭을공백으로 저장, 탭크기(4) 로 설정해야 들여쓰기 및 탭공간이 올바르게 보입니다.]
    1. 프로시져명 : [SP_MAT004_SEL1]
    2. 제목(기능) : 원부재료 발주명세서 조회
    3. 작  성  일 : 2008-08-20
    4. 작  성  자 : 정종채
    5. 실      행
        EXEC [SP_MAT004_SEL1] '1999-01-01','2008-10-10',NULL,NULL,NULL,NULL
SELECT * FROM VEND
    6. 수정이력
    ---------------------------------------------------------------------------------------------------
    수정일      수정자      수정내역
    ---------------------------------------------------------------------------------------------------
*******************************************************************************************************/
ALTER  PROCEDURE [dbo].[SP_MAT004_SEL1]
    @PI_DATE_FROM    DATETIME,       -- 발주일자(FROM)
    @PI_DATE_TO      DATETIME,       -- 발주일자(TO)
    @PI_MTITEM       VARCHAR (4),    -- 자재코드(%)
    @PI_VENDCD       VARCHAR (3),    -- 거래처 코드(%)
    @PI_MTGRUP       VARCHAR (1),    -- 자재분류(%)
    @PI_BLJUYN       VARCHAR (1)     -- 발주확정 여부(Y,N,%)
AS
BEGIN
    DECLARE     @SQL  VARCHAR(4000)
    SET @SQL =        'SELECT VENDNM, BLJUYN, BLJUDT,  BLJUNO,'
    SET @SQL = @SQL +        'MTITEM, MTNAME, BJUNIT, BLJUQTY, JANQTY, SPLIT_NABGI '
    SET @SQL = @SQL + 'FROM '
   
    SET @SQL = @SQL + '(SELECT A.VENDCD,                  
                               C.VENDSNM   AS VENDNM,     
                               CASE WHEN A.BLJUDT IS NULL THEN ''N'' ELSE ''Y'' END
                                           AS BLJUYN,     
                               A.BLJUDT,                  
                               A.BLJUNO,                  
                               A.MTITEM,                  
                               B.MTNAME,                  
                               E.CODENM    AS BJUNIT,     
                               A.BLJUKG / ISNULL(D.UNITKG,0)
                                           AS BLJUQTY,    
                               CASE WHEN A.JANKG = 0 THEN 0
                                    ELSE A.JANKG  / ISNULL(D.UNITKG,0)
                                END        AS JANQTY,     
                               A.NABGIDT   AS SPLIT_NABGI 
                          FROM BALJU A LEFT OUTER JOIN MTITEM   B ON A.MTITEM = B.MTITEM
                                       LEFT OUTER JOIN VEND     C ON A.VENDCD = C.VENDCD
                                       LEFT OUTER JOIN MTBJUNIT D ON A.MTITEM = D.MTITEM AND A.UNITNO = D.UNITNO
                                       LEFT OUTER JOIN CODE     E ON ''10''     = E.KIND   AND D.UNITCD = E.CODE
                         WHERE 1=1'
    IF ISNULL(LTRIM(@PI_DATE_FROM),'') != '' AND ISNULL(LTRIM(@PI_DATE_TO),'') = ''
        SET @SQL = @SQL + ' AND A.BLJUDT >= ''' + CONVERT(VARCHAR(20),@PI_DATE_FROM,120) + ''''
    IF ISNULL(LTRIM(@PI_DATE_FROM),'') = '' AND ISNULL(LTRIM(@PI_DATE_TO),'') != ''
        SET @SQL = @SQL + ' AND A.BLJUDT <= ''' + CONVERT(VARCHAR(20),@PI_DATE_TO,120) + ''''
    IF ISNULL(LTRIM(@PI_DATE_FROM),'') != '' AND ISNULL(LTRIM(@PI_DATE_TO),'') != ''
        SET @SQL = @SQL + ' AND A.BLJUDT >= ''' + CONVERT(VARCHAR(20),@PI_DATE_FROM,120) +
                              ''' AND A.BLJUDT <= ''' + CONVERT(VARCHAR(20),@PI_DATE_TO,120) + ''''
    IF ISNULL(LTRIM(@PI_BLJUYN),'') != ''
       IF ISNULL(LTRIM(@PI_BLJUYN),'') != 'Y'
           SET @SQL = @SQL + ' AND A.BLJUDT IS NULL'
       ELSE
           SET @SQL = @SQL + ' AND A.BLJUDT IS NOT NULL'
    IF ISNULL(LTRIM(@PI_VENDCD),'') != ''
        SET @SQL = @SQL + ' AND A.VENDCD LIKE ''%' + @PI_VENDCD + '%'''
    IF ISNULL(LTRIM(@PI_MTGRUP),'') != ''
        SET @SQL = @SQL + ' AND A.MTGRUP LIKE ''%' + @PI_MTGRUP + '%'''
    IF ISNULL(LTRIM(@PI_MTITEM),'') != ''
        SET @SQL = @SQL + ' AND A.MTITEM LIKE ''%' + @PI_MTITEM + '%'''
    SET @SQL = @SQL + ') X'
    EXEC (@SQL)
 --   PRINT (@SQL)
END
 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO