USE [SCMUS]
GO
/****** Object:  StoredProcedure [dbo].[USSOPS00031]    Script Date: 10/09/2014 10:15:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[USSOPS00031]
    @inCOMCD VARCHAR(10),
    @inFACCD VARCHAR(2),
    @inERRLOG NVARCHAR(255),
    @inBUSDATE VARCHAR(8),
    @inFILE_PATH NVARCHAR(255),
    @inXMLPATH NVARCHAR(255),
    @outRESULT INT OUTPUT,
    @outMESSAGE NVARCHAR(500) OUTPUT
AS
BEGIN
    BEGIN TRY
        DECLARE @SYSDATE DATETIME,
                @USER NVARCHAR(MAX),
                @sqlstmt NVARCHAR(MAX),
                @param NVARCHAR(MAX),
                @stockClass CHAR(2),
                @sysOpDate CHAR(8),
                @fromDate CHAR(8),
                @toDate CHAR(8)

        SELECT @SYSDATE = CURRENT_TIMESTAMP;
        SELECT @USER = CURRENT_USER;
        -- 1.
        SET @outRESULT = 0;
        SET @outMESSAGE = '';

        --2.    OUTファイル
        -- ①    【パラメータ】.BUSDATEの前月、翌月を取得する
        SELECT @toDate = SUBSTRING(replace(CONVERT(varchar,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,CONVERT(DATETIME, @inBUSDATE))+1,0)),102),'.',''),0,6);
        SELECT @fromDate = SUBSTRING(replace(CONVERT(varchar,DATEADD(month, DATEDIFF(month, 0, CONVERT(DATETIME, @inBUSDATE)), 0),102),'.',''),0,6)

        -- ② 工場別需要先別在庫区分変換マスタから該当工場の在庫区分を取得する
        SET @stockClass = '  '
        SELECT
            @stockClass = IsNull(T1.STOCK_CLASS,'  ')
        FROM
            "01CFPSM001" T1
        WHERE
            T1.PLANT_CD = '' + @inFACCD + ''
            AND T1.DEMAND_CLASS ='1';

        -- ③    テーブル更新情報からREP販売計画の最新のシステム運用日付を取得する
        SET @sysOpDate = '  '
        SELECT
            @sysOpDate = T1.SYSTEM_OP_DATE
        FROM
            "01CUSVO002" T1
        WHERE
            T1.TBL_NAME = '01CRDBT006'
            AND T1.COMPANY_CD = '' + @inCOMCD + ''
            AND T1.PLANT_CD = '**'

        --④ 出力データ取得
        SET @sqlstmt =  '"'
        SET @sqlstmt += 'SELECT '
        SET @sqlstmt += '     ''I'' AS SINPAI'
        SET @sqlstmt += '   , T3.PRODUCT_CAT AS HIN'
        SET @sqlstmt += '   , ''3 '' As UCHI'
        SET @sqlstmt += '   , T3.BPC AS SCODE '
        SET @sqlstmt += '   , T3.BPC AS SCODE_KO '
        SET @sqlstmt += '   , ''' + @stockClass + ''' AS ZKKBN'
        SET @sqlstmt += '   , ''1'' AS JYS '
        SET @sqlstmt += '   , ''        '' AS SKCODE '
        SET @sqlstmt += '   , ''' + @inFACCD + ''' AS FACT '
        SET @sqlstmt += '   , T3.PLAN_YM AS YYYYMMDD '
        SET @sqlstmt += '   , Sum(T3.PLAN_QTY) AS HON'
        SET @sqlstmt += '   , ''P'' AS KBN'
        SET @sqlstmt += ' FROM  '
        SET @sqlstmt += '   ( SELECT '
        SET @sqlstmt += '             T1.PRODUCT_CAT '
        SET @sqlstmt += '           , T1.BPC'
        SET @sqlstmt += '           , T1.PLAN_YM '
        SET @sqlstmt += '           , T1.PLAN_QTY '
        SET @sqlstmt += '      FROM '
        SET @sqlstmt += '           [SCMUS].[dbo].[01CRDBT006] T1 '
        SET @sqlstmt += '           INNER JOIN [SCMUS].[dbo].[01CFPSM005] T2 '
        SET @sqlstmt += '               ON T1.PLAN_YM = T2.TARGET_YM '
        SET @sqlstmt += '               AND T1.PRODUCT_CAT = T2.PRODUCT_CAT '
        SET @sqlstmt += '               AND T2.SET_Cd = ''3 '' '
        SET @sqlstmt += '               AND T1.BPC = T2.BPC '
        SET @sqlstmt += '               AND T2.PRODUCTION_PLANT_CD = ''' + @inFACCD + ''''
        SET @sqlstmt += '               AND T2.PROCESS_STATS <> ''0'''
        SET @sqlstmt += '       WHERE '
        SET @sqlstmt += '               T1.SYSTEM_OP_DATE = ''' + @sysOpDate + ''''
        SET @sqlstmt += '           AND T1.ACQUISITION_CO_CD = ''' + @inCOMCD + ''''
        SET @sqlstmt += '           AND T1.RDB_F_CLASS1 = ''04'''
        SET @sqlstmt += '           AND T1.RDB_F_CLASS2 = ''01'''
        SET @sqlstmt += '           AND T1.ACQUISITION_SYS_NAME = ''MGM'''
        SET @sqlstmt += '           AND T1.COMPANY_CD = ''' +@inCOMCD + ''''
        SET @sqlstmt += '           AND T1.PLAN_YM >= ''' + @fromDate + ''''
        SET @sqlstmt += '           AND T1.PLAN_YM <= ''' + @toDate + ''''
        SET @sqlstmt += '           AND T1.DEMAND_NAME = ''REP'''
        SET @sqlstmt += '           AND T1.PRODUCT_CAT = ''AT'''
        SET @sqlstmt += '           AND T1.SET_CD NOT IN (''6 '',''9 '')'
        SET @sqlstmt += '   UNION ALL '
        SET @sqlstmt += '    SELECT '
        SET @sqlstmt += '             T1.PRODUCT_CAT '
        SET @sqlstmt += '           , T1.BPC '
        SET @sqlstmt += '           , T1.PLAN_YM '
        SET @sqlstmt += '           , T1.PLAN_QTY '
        SET @sqlstmt += '      FROM '
        SET @sqlstmt += '           [SCMUS].[dbo].[01CRDBT006] T1 '
        SET @sqlstmt += '           INNER JOIN [SCMUS].[dbo].[01CFPSM005] T2 '
        SET @sqlstmt += '               ON T1.PLAN_YM = ''' + @toDate + ''''
        SET @sqlstmt += '               AND T1.PRODUCT_CAT = T2.PRODUCT_CAT '
        SET @sqlstmt += '               AND T2.SET_Cd = ''3 '' '
        SET @sqlstmt += '               AND T1.BPC = T2.BPC '
        SET @sqlstmt += '               AND T2.PRODUCTION_PLANT_CD = ''' + @inFACCD + ''''
        SET @sqlstmt += '               AND T2.PROCESS_STATS <> ''0'''
        SET @sqlstmt += '       WHERE '
        SET @sqlstmt += '               T1.SYSTEM_OP_DATE = ''' + @sysOpDate + ''''
        SET @sqlstmt += '           AND T1.ACQUISITION_CO_CD = ''' + @inCOMCD + ''''
        SET @sqlstmt += '           AND T1.RDB_F_CLASS1 = ''04'''
        SET @sqlstmt += '           AND T1.RDB_F_CLASS2 = ''01'''
        SET @sqlstmt += '           AND T1.ACQUISITION_SYS_NAME = ''MGM'''
        SET @sqlstmt += '           AND T1.COMPANY_CD = ''' + @inCOMCD + ''''
        SET @sqlstmt += '           AND T1.PLAN_DATE > ''' + @toDate+ ''''
        SET @sqlstmt += '           AND T1.DEMAND_NAME = ''REP'''
        SET @sqlstmt += '           AND T1.PRODUCT_CAT = ''AT'''
        SET @sqlstmt += '           AND T1.SET_CD NOT IN (''6 '',''9 '')'
        SET @sqlstmt += '   ) AS T3 '
        SET @sqlstmt += ' GROUP BY '
        SET @sqlstmt += '     T3.PRODUCT_CAT '
        SET @sqlstmt += '   , T3.BPC '
        SET @sqlstmt += '   , T3.PLAN_YM '
        SET @sqlstmt += ' HAVING '
        SET @sqlstmt += '     Sum(T3.PLAN_QTY) <> 0 '
        SET @sqlstmt += ' ORDER BY '
        SET @sqlstmt += '     T3.PRODUCT_CAT '
        SET @sqlstmt += '   , T3.BPC '
        SET @sqlstmt += '   , T3.PLAN_YM '
        SET @sqlstmt += '"'

        -- ⑤    BCP機能を使用して、【パラメータ】.OUTFILEファイルを出力する。

        Declare @shell VARCHAR(8000)
        set @shell = 'bcp '
            + @sqlstmt
            + ' queryout '  + @inFILE_PATH
            + ' -f ' + @inXMLPATH
            + ' -T -S ' + @@SERVERNAME
            + ' -e ' + @inERRLOG
        exec xp_cmdShell @shell

        -- 3.   返却値を設定する。
        -- ・それ以外の場合
        SET @param = @inFILE_PATH
        EXEC [dbo].[USSTUS00004_uspGetMsgContentOut] @outRESULT, @param, @outMESSAGE OUTPUT

    END TRY
    BEGIN CATCH
        -- ・2.で例外が発生する場合
        -- RESULT = 8 (異常終了)
        SET @outRESULT = 8
        -- MESSAGE = エラーメッセージ
        SELECT @param = ERROR_MESSAGE();
        EXEC [dbo].[USSTUS00004_uspGetMsgContentOut] @outRESULT, @param, @outMESSAGE OUTPUT
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
    END CATCH
END

Dim cmdStoredFunct
    Dim objRecordSet
    Set cmdStoredFunct = CreateObject("ADODB.Command")
    With cmdStoredFunct
         .CommandText = "dbo.USSIRD00031"
         .CommandType = AD_CMD_STORED_PROC
         .Parameters.Append .CreateParameter("inPGID", AD_VARCHAR, AD_PARAM_INPUT, 40, strPGID)
         .Parameters.Append .CreateParameter("inCOMCD", AD_VARCHAR, AD_PARAM_INPUT, 4, strComCd)
         .Parameters.Append .CreateParameter("inBUSDATE", AD_VARCHAR, AD_PARAM_INPUT, 8, strOpeDate)
         .Parameters.Append .CreateParameter("inFILE_PATH", AD_VARCHAR, AD_PARAM_INPUT, 255, strInfile)
         .Parameters.Append .CreateParameter("inXMLPATH", AD_VARCHAR, AD_PARAM_INPUT, 255, strXmlPath)
         .Parameters.Append .CreateParameter("inINERROR", AD_VARCHAR, AD_PARAM_INPUT, 255, strErrLog)
         .Parameters.Append .CreateParameter("outRESULT", AD_INTEGER, AD_PARAM_OUTPUT)
         .Parameters.Append .CreateParameter("outMESSAGE", AD_VARCHAR, AD_PARAM_OUTPUT, 500)
    End With
    Call StoredExcute(objRecordSet, cmdStoredFunct)
    Dim intResult
    Set intResult = cmdStoredFunct(6)
    Dim strMessage
    Set strMessage = cmdStoredFunct(7)