--表示項目選択プルダウン用のVIEW

CREATE VIEW V_KANRIKOUMOKU AS
SELECT
	T0.PATTERNID AS パターンID,
	T0.KOUMOKUNO AS 項目NO,
	T2.KOUMOKUNAME AS 項目名称,
    T2.KOUMOKUNO1 AS 計算項目1,
    T2.表示順 AS 表示順,
	T0.SELECTKBN AS 表示区分
FROM
	EX_MENU4 T0
	INNER JOIN EX_MENU1 T1 ON T0.PATTERNID = T1.PATTERNID
	INNER JOIN EX_DSOURCEI T2 ON T1.DSOURCEID = T2.DSOURCEID AND T2.KOUMOKUKBN = 4 AND T0.KOUMOKUNO = T2.KOUMOKUNO

--得意先別補正データに最新の部課、担当者を付加

CREATE OR REPLACE VIEW VIEW_KANRIDB
    (YYYYMM,KEY1,KEY2,KEY3,KEY4,KEY5,
    D1,D2,D3,D4,D5,D6,D7,D8,D9,D10,
    D11,D12,D13,D14,D15,D16,D17,D18,D19,D20,
    D21,D22,D23,D24,D25,D26,D27,D28,D29,D30,
    D31,D32,D33,D34,D35,D36,D37,D38,D39,D40,
    D41,D42,D43,D44,D45,D46,D47,D48,D49,D50,
    D51,D52,D53,D54,D55,D56,D57,D58,D59,D60)
AS
SELECT
    A.YYYYMM,
    C.EX_VALUE KEY1,
    B.EX_VALUE KEY2,
    A.TOK KEY3,
    ' ' KEY4,
    ' ' KEY5,
    A.D1,A.D2,A.D3,A.D4,A.D5,A.D6,A.D7,A.D8,A.D9,A.D10,
    A.D11,A.D12,A.D13,A.D14,A.D15,A.D16,A.D17,A.D18,A.D19,A.D20,
    A.D21,A.D22,A.D23,A.D24,A.D25,A.D26,A.D27,A.D28,A.D29,A.D30,
    A.D31,A.D32,A.D33,A.D34,A.D35,A.D36,A.D37,A.D38,A.D39,A.D40,
    A.D41,A.D42,A.D43,A.D44,A.D45,A.D46,A.D47,A.D48,A.D49,A.D50,
    A.D51,A.D52,A.D53,A.D54,A.D55,A.D56,A.D57,A.D58,A.D59,A.D60
FROM
    EX_KANRITOK A
    LEFT OUTER JOIN EX_HENKAN B ON B.EX_KUBUN='営業担当' AND B.EX_KEY=A.TOK
    LEFT OUTER JOIN EX_HENKAN C ON C.EX_KUBUN='部課' AND C.EX_KEY=A.TOK

※使用の際は、EX_KUBUNの値を確認すること。