「stored procedure（ストアドプロシージャ）」SP-Sample2

USE [APPLICATION_DEV]
GO
/****** Object:  StoredProcedure [dbo].[SPAA_APPLICATION_LIST_SEL]    Script Date: 12/08/2014 15:13:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==========================================================================================
-- $Id: dbo.SPAA_APPLICATION_LIST_SEL.StoredProcedure.sql 776 2011-11-29 02:03:50Z ubukata $
-- Create date : 2011/11/07
-- Description : 申請検索結果取得
--             : 検索条件に従い申請を検索する
--             : セットの無い検索条件は、空文字列をセットすること
--             : ===== 2011/11/29 #10 =====
--             : 申請製品テーブルへの対応
-- ==========================================================================================
ALTER PROCEDURE [dbo].[SPAA_APPLICATION_LIST_SEL](
     @appli_no                    VARCHAR(11)        --申請No
    ,@materials_code            VARCHAR(1)        --資材媒体コード
    ,@materials_class_code        VARCHAR(2)        --資材分類コード
    ,@uniform_code                VARCHAR(10)        --統一コード
    ,@order_part_code            VARCHAR(5)        --発注品番
    ,@materials_no                VARCHAR(16)        --資材管理番号
    ,@application_date_start    VARCHAR(7)        --申請年月開始(yyyy/MM)
    ,@application_date_end        VARCHAR(7)        --申請年月終了(yyyy/MM)
    ,@permission_date_start        VARCHAR(7)        --許可年月開始(yyyy/MM)
    ,@permission_date_end        VARCHAR(7)        --許可年月終了(yyyy/MM)
    ,@materials_nm_knj            VARCHAR(255)    --資材名称
    ,@applicant_division        VARCHAR(10)        --申請部署コード
    ,@applicant_stf_code        VARCHAR(5)        --申請者コード
    ,@drug                        VARCHAR(2)        --薬剤コード
    ,@product                    VARCHAR(255)    --製品コード(カンマ区切り文字列)
    ,@status_no                    VARCHAR(255)    --申請ステータス(カンマ区切り文字列)
    ,@committee_no                VARCHAR(3)        --委員会
    ,@stfcode                    VARCHAR(5)        --申請者の担当者コード（事務局の場合、セットしない）
    --,@disp_order                VARCHAR(1)        --表示順(申請番号："1"、薬剤："2"、申請部署："3")

) AS
BEGIN

    --以下の値のいずれかがセットされている場合、そのキー値のみで検索を行う
    --申請番号
    --統一コード
    --発注品番
    --資材管理番号
    --->他の条件は全て渡されないとしてクリアする
    IF
        @appli_no <> '' OR @uniform_code <> '' OR @order_part_code <> '' OR @materials_no <> ''
    BEGIN
        IF
            @appli_no <> ''
        BEGIN
            SET @uniform_code = '';
            SET @order_part_code = '';
            SET @materials_no = '';
        END

        IF
            @uniform_code <> ''
        BEGIN
            SET @appli_no = '';
            SET @order_part_code = '';
            SET @materials_no = '';
        END

        IF
            @order_part_code <> ''
        BEGIN
            SET @appli_no = '';
            SET @uniform_code = '';
            SET @materials_no = '';
        END

        IF
            @materials_no <> ''
        BEGIN
            SET @appli_no = '';
            SET @uniform_code = '';
            SET @order_part_code = '';
        END

        --それ以外の検索条件をクリア
        SET @materials_code = '';
        SET @materials_class_code = '';
        SET @application_date_start = '';
        SET @application_date_end = '';
        SET @permission_date_start = '';
        SET @permission_date_end = '';
        SET @materials_nm_knj = '';
        SET @applicant_division = '';
        SET @applicant_stf_code = '';
        SET @drug = '';
        SET @product = '';
        SET @status_no = '';
        SET @committee_no = '';

    END

    --検索
    SELECT DISTINCT
         APPL.APPLI_NO        --申請No
        ,APPL.REVISION_NO        --修正No
        ,APPL.MATERIALS_NM_KNJ        --資材名称
        ,DRUG_NM_KNJ            --薬剤名称
        ,APPL.STATUS_NO AS APPLI_STATUS_NO        --申請ステータスNo
        ,APPL_STAT.STATUS_NAME_1 AS APPLI_STATUS_NM    --申請ステータス名
        ,INSPECTION_RESULT.DISCUSSION_RESULT_STATUS_NO        --審議結果ステータスNo
        ,DISS_STAT.STATUS_NAME_1 AS DISCUSSION_RESULT_STATUS_NM        --審議結果ステータス名
        ,APPL.UNIFORM_CODE        --統一コード
        ,APPL.ORDER_PART_CODE        --発注品番
        ,MAT.MATERIALS_NM_KNJ AS MATERIALS_CODE_NM_KNJ        --資材媒体（名称）
        ,MAT_CLS.MATERIALS_CLASS_NM_KNJ        --資材分類（名称）
        ,COMMITEE_APPL.COMMITTEE_NO        --委員会No
        ,DBSC.SCN_NM_KNJ            --申請部署名称
        ,STF.STF_NM_KNJ            --申請者名称
        ,CASE WHEN APPL.APPLICATION_DATE = '' 
            THEN '' 
            ELSE CONVERT(CHAR(10),CONVERT(DATETIME,APPL.APPLICATION_DATE,112),111) 
        END AS APPLICATION_DATE        --申請日
        ,APPL.MATERIALS_NO            --資材管理番号
        ,APPL.OLD_UNIFORM_CODE        --元統一コード
        ,DGIF.DRUG_SORT_1            --表示順１
        ,DBSC.ORG_DSP_SORT        --組織表示順
        ,APPL.REVISE_FLG    --新規／改定
        ,APPL.ORDER_FLG        --発注品番の要否
        ,CASE WHEN (ISNULL(APPL.STATUS_NO,'') = '21' OR ISNULL(APPL.STATUS_NO,'') = '22')
            THEN CONVERT(CHAR(10),CONVERT(DATETIME,APPL.UPD_DATE,112),111) 
            ELSE CONVERT(CHAR(10),CONVERT(DATETIME,APPL_HISTORY_21_22.SUPERIOR_APPROVE_YMD,112),111) 
        END AS SUPERIOR_APPROVE_DATE    --上長判定日
        ,CASE WHEN ISNULL(APPL_HISTORY_31.SECRETARIAT_YMD,'') = '' 
            THEN CASE WHEN ISNULL(APPL.STATUS_NO,'') = '31'
                THEN CONVERT(CHAR(10),CONVERT(DATETIME,APPL.UPD_DATE,112),111) 
                ELSE '' 
                END 
            ELSE CONVERT(CHAR(10),CONVERT(DATETIME,APPL_HISTORY_31.SECRETARIAT_YMD,112),111) 
        END AS SECRETARIAT_ACCEPT_DATE  --初回事務局受理
        ,CASE WHEN (ISNULL(APPL.STATUS_NO,'') = '54' OR ISNULL(APPL.STATUS_NO,'') = '55')
            THEN CONVERT(CHAR(10),CONVERT(DATETIME,APPL.UPD_DATE,112),111) 
            ELSE CONVERT(CHAR(10),CONVERT(DATETIME,APPL_HISTORY_54_55.CHAIRPERSON_YMD,112),111) 
        END AS PERMISSION_DATE  --委員長判定日
        ,CASE WHEN ISNULL(APPL_HISTORY_71.PUBLISH_YMD,'') = '' 
            THEN CASE WHEN ISNULL(APPL.STATUS_NO,'') = '71'
                THEN CONVERT(CHAR(10),CONVERT(DATETIME,APPL.UPD_DATE,112),111) 
                ELSE '' 
                END 
            ELSE CONVERT(CHAR(10),CONVERT(DATETIME,APPL_HISTORY_71.PUBLISH_YMD,112),111) 
        END AS PUBLISH_YMD  --初回公開日
APPL_HISTORY_54_55
    FROM
        --申請
        TBAATR_APPLICATION AS APPL
        --審査結果
        LEFT OUTER JOIN (SELECT * FROM TBAATR_INSPECTION_RESULT WHERE DEL_FLG = '0') AS INSPECTION_RESULT
        ON( APPL.APPLI_NO = INSPECTION_RESULT.APPLI_NO
        AND APPL.REVISION_NO = INSPECTION_RESULT.REVISION_NO)
        --委員会審議申請
        LEFT OUTER JOIN (SELECT * FROM TBAARS_COMMITTEE_APPLICATION WHERE DEL_FLG = '0') AS COMMITEE_APPL
        ON( APPL.APPLI_NO = COMMITEE_APPL.APPLI_NO
        AND APPL.REVISION_NO = COMMITEE_APPL.REVISION_NO)
        --申請製品
        LEFT OUTER JOIN (SELECT * FROM TBAATR_PRODUCT WHERE DEL_FLG = '0') AS APPL_PROD
        ON( APPL.APPLI_NO = APPL_PROD.APPLI_NO)
        --資材媒体
        LEFT OUTER JOIN MATERIALS_INFO AS MAT
        ON( APPL.MATERIALS_CODE = MAT.MATERIALS_CODE)
        --資材分類
        LEFT OUTER JOIN MATERIALS_CLASS_INFO AS MAT_CLS
        ON( APPL.MATERIALS_CLASS_CODE = MAT_CLS.MATERIALS_CLASS_CODE)
        --薬剤マスタ
        LEFT OUTER JOIN TBCMRS_DRUG_INFO AS DGIF
        ON( APPL.DRUG = DGIF.DRUG_CODE)
        --組織ビュー
        --//20130911:ISHIKAWA:MOD:START:眼皮組織再編対応
        --LEFT OUTER JOIN VWAP_ORG_DBSC_LIST AS DBSC
        --ON( APPL.APPLICANT_DIVISION = DBSC.ORG_CODE
        --AND APPL.APPLICANT_DIV_REG_DATE BETWEEN DBSC.STA_YMD AND DBSC.END_YMD)
        LEFT OUTER JOIN VWAP_ORG_DBSC_LIST_INCDEL AS DBSC
        ON( APPL.APPLICANT_DIVISION = DBSC.ORG_CODE
        AND APPL.APPLICANT_DIV_REG_DATE BETWEEN DBSC.STA_YMD AND DBSC.END_YMD)
        --//20130911:ISHIKAWA:MOD:END:眼皮組織再編対応
        
        --担当者マスタ
        LEFT OUTER JOIN TBCMRS_STF_INFO AS STF
        ON( APPL.APPLICANT_STF_CODE = STF.STF_CODE)
        --申請ステータス
        INNER JOIN TBAAMS_STATUS AS APPL_STAT
        ON( APPL.STATUS_NO = APPL_STAT.STATUS_NO)
        --申請ステータス（審議結果）
        LEFT OUTER JOIN TBAAMS_STATUS AS DISS_STAT
        ON( INSPECTION_RESULT.DISCUSSION_RESULT_STATUS_NO = DISS_STAT.STATUS_NO)
        --申請履歴
        LEFT OUTER JOIN 
        (
            SELECT APPL_HISTORY_UPD_DATE.APPLI_NO,APPL_HISTORY_UPD_DATE.UPD_DATE AS SUPERIOR_APPROVE_YMD FROM TBAATR_APPLICATION_HISTORY APPL_HISTORY_UPD_DATE
            INNER JOIN 
            ( 
                SELECT APPLI_NO,MAX(HISTORY_NO) AS HISTORY_NO FROM TBAATR_APPLICATION_HISTORY WHERE (STATUS_NO='21' OR STATUS_NO='22') GROUP BY APPLI_NO 
            ) APPL_HISTORY_CPD_MAX
            ON 
                APPL_HISTORY_UPD_DATE.APPLI_NO=APPL_HISTORY_CPD_MAX.APPLI_NO
            AND APPL_HISTORY_UPD_DATE.HISTORY_NO=APPL_HISTORY_CPD_MAX.HISTORY_NO  
         )
        AS APPL_HISTORY_21_22
        ON( APPL.APPLI_NO = APPL_HISTORY_21_22.APPLI_NO )
        LEFT OUTER JOIN 
        (
            SELECT APPL_HISTORY_UPD_DATE.APPLI_NO,APPL_HISTORY_UPD_DATE.UPD_DATE AS SECRETARIAT_YMD FROM TBAATR_APPLICATION_HISTORY APPL_HISTORY_UPD_DATE
            INNER JOIN 
            ( 
                SELECT APPLI_NO,MIN(HISTORY_NO) AS HISTORY_NO FROM TBAATR_APPLICATION_HISTORY WHERE (STATUS_NO='31') GROUP BY APPLI_NO 
            ) APPL_HISTORY_SAD_MIN
            ON 
                APPL_HISTORY_UPD_DATE.APPLI_NO=APPL_HISTORY_SAD_MIN.APPLI_NO
            AND APPL_HISTORY_UPD_DATE.HISTORY_NO=APPL_HISTORY_SAD_MIN.HISTORY_NO  
         )
        AS APPL_HISTORY_31
        ON( APPL.APPLI_NO = APPL_HISTORY_31.APPLI_NO ) 
        LEFT OUTER JOIN 
        (
            SELECT APPL_HISTORY_UPD_DATE.APPLI_NO,APPL_HISTORY_UPD_DATE.UPD_DATE AS CHAIRPERSON_YMD FROM TBAATR_APPLICATION_HISTORY APPL_HISTORY_UPD_DATE
            INNER JOIN 
            ( 
                SELECT APPLI_NO,MAX(HISTORY_NO) AS HISTORY_NO FROM TBAATR_APPLICATION_HISTORY WHERE (STATUS_NO='54' OR STATUS_NO='55') GROUP BY APPLI_NO 
            ) APPL_HISTORY_CPD_MAX
            ON 
                APPL_HISTORY_UPD_DATE.APPLI_NO=APPL_HISTORY_CPD_MAX.APPLI_NO
            AND APPL_HISTORY_UPD_DATE.HISTORY_NO=APPL_HISTORY_CPD_MAX.HISTORY_NO  
         )
        AS APPL_HISTORY_54_55
        ON( APPL.APPLI_NO = APPL_HISTORY_54_55.APPLI_NO ) 
        LEFT OUTER JOIN 
        (
            SELECT APPL_HISTORY_UPD_DATE.APPLI_NO,APPL_HISTORY_UPD_DATE.UPD_DATE AS PUBLISH_YMD FROM TBAATR_APPLICATION_HISTORY APPL_HISTORY_UPD_DATE
            INNER JOIN 
            ( 
                SELECT APPLI_NO,MIN(HISTORY_NO) AS HISTORY_NO FROM TBAATR_APPLICATION_HISTORY WHERE (STATUS_NO='71') GROUP BY APPLI_NO 
            ) APPL_HISTORY_PUB_MIN
            ON 
                APPL_HISTORY_UPD_DATE.APPLI_NO=APPL_HISTORY_PUB_MIN.APPLI_NO
            AND APPL_HISTORY_UPD_DATE.HISTORY_NO=APPL_HISTORY_PUB_MIN.HISTORY_NO  
         )
        AS APPL_HISTORY_71
        ON( APPL.APPLI_NO = APPL_HISTORY_71.APPLI_NO )  

    WHERE
        APPL.DEL_FLG = '0'
    --主キー系の条件
    AND    APPL.APPLI_NO LIKE (CASE WHEN @appli_no = '' THEN APPL.APPLI_NO ELSE @appli_no + '%' END)
    AND    1 = (CASE WHEN @uniform_code = ''
                THEN 1 
                ELSE CASE WHEN APPL.UNIFORM_CODE LIKE @uniform_code + '%' THEN 1 ELSE 0 END
            END)
    AND    1 = (CASE WHEN @order_part_code = ''
                THEN 1 
                ELSE CASE WHEN APPL.ORDER_PART_CODE LIKE @order_part_code + '%' THEN 1 ELSE 0 END
            END)
    AND    1 = (CASE WHEN @materials_no = ''
                THEN 1 
                ELSE CASE WHEN APPL.MATERIALS_NO LIKE @materials_no + '%' THEN 1 ELSE 0 END
            END)
    --日付条件
    AND    1 = (CASE WHEN @application_date_start = ''
                THEN 1 
                ELSE CASE WHEN APPL.APPLICATION_DATE >= CONVERT(CHAR(8),CONVERT(DATETIME,@application_date_start + '/01' ,111),112) THEN 1 ELSE 0 END
            END)
    AND    1 = (CASE WHEN @application_date_end = ''
                THEN 1 
                ELSE CASE WHEN APPL.APPLICATION_DATE <= CONVERT(CHAR(8),DATEADD(DAY,-1,DATEADD(MONTH,+1,CONVERT(DATETIME,@application_date_end + '/01' ,111))),112) THEN 1 ELSE 0 END
            END)
    AND    1 = (CASE WHEN @permission_date_start = ''
                THEN 1 
                ELSE CASE WHEN APPL.PERMISSION_DATE >= CONVERT(CHAR(8),CONVERT(DATETIME,@permission_date_start + '/01' ,111),112) THEN 1 ELSE 0 END
            END)
    AND    1 = (CASE WHEN @permission_date_end = ''
                THEN 1 
                ELSE CASE WHEN APPL.PERMISSION_DATE <= CONVERT(CHAR(8),DATEADD(DAY,-1,DATEADD(MONTH,+1,CONVERT(DATETIME,@permission_date_end + '/01' ,111))),112) THEN 1 ELSE 0 END
            END)

    --それ以外の条件
    AND    APPL.MATERIALS_NM_KNJ LIKE (CASE WHEN @materials_nm_knj = '' THEN APPL.MATERIALS_NM_KNJ ELSE '%' + @materials_nm_knj + '%' END)
    AND    1 = (CASE WHEN @status_no = ''
                THEN 1 
                ELSE CASE WHEN APPL.STATUS_NO IN (SELECT [Text] FROM dbo.FNCO_SPLIT_TEXT(@status_no,default,default)) THEN 1 ELSE 0 END
            END)
    AND    APPL.MATERIALS_CODE = (CASE WHEN @materials_code = '' THEN APPL.MATERIALS_CODE ELSE @materials_code END)
    AND    1 = (CASE WHEN @materials_class_code = ''
                THEN 1 
                ELSE CASE WHEN APPL.MATERIALS_CLASS_CODE = @materials_class_code THEN 1 ELSE 0 END
            END)
    AND    APPL.DRUG = (CASE WHEN @drug = '' THEN APPL.DRUG ELSE @drug END)
    AND    1 = (CASE WHEN @product = ''
                THEN 1 
                ELSE CASE WHEN APPL_PROD.PROD_GRP_CODE IN (SELECT [Text] FROM dbo.FNCO_SPLIT_TEXT(@product,default,default)) THEN 1 ELSE 0 END
            END)
    AND    APPL.APPLICANT_DIVISION = (CASE WHEN @applicant_division = '' THEN APPL.APPLICANT_DIVISION ELSE @applicant_division END)
    AND    APPL.APPLICANT_STF_CODE = (CASE WHEN @applicant_stf_code = '' THEN APPL.APPLICANT_STF_CODE ELSE @applicant_stf_code END)
    AND    1 = (CASE WHEN @committee_no = ''
                THEN 1 
                ELSE CASE WHEN COMMITEE_APPL.COMMITTEE_NO = @committee_no THEN 1 ELSE 0 END
            END)
    ;
    --DEL:20131030:ISHIKAWA:START:ロールでのフィルターは解除
    --申請者の担当者コードがセットされている場合は、自分が申請者か申請者上長になっているものを検索
    --AND    (
    --        (APPL.APPLICANT_STF_CODE = CASE WHEN @stfcode = '' THEN APPL.APPLICANT_STF_CODE ELSE @stfcode END)
    --    OR    (APPL.SUPERIOR_STF_CODE = CASE WHEN @stfcode = '' THEN APPL.SUPERIOR_STF_CODE ELSE @stfcode END)
    --);
    --DEL:20131030:ISHIKAWA:END
    
END