<jsp:directive.page pageEncoding="UTF-8" />
<!-- GFM0013 DB定義(備考) ⇒ ｺｰﾄﾞﾘｿｰｽ差分追加更新 -->

<!-- KBCLM 6:除外 7:共通 8:有閑 9:削除 は対象外 -->
<og:query command="{@command}" debug="{@debug}" maxRowCount="0">
	select '1' as WRITABLE,UPPER(TABLE_NAME) AS TABLE_NAME,UPPER(CLM) AS CLM,CLM_NAME as NAME_JA,
			RENDERER,EDITOR,DBTYPE,0 SEQ,'' CODE,'' CODENAME,
			trim(REGEXP_SUBSTR(BIKO,'([^ ]+:[^ ]+[ ]*)+' )) as BIKO,UNIQ,
			case when SUBSYS='**' then '**' else '{@SYSTEM_ID_R}' end as SYSTEM_ID,
			KBSAKU,SUBSYS
	from GE82
	where SYSTEM_ID = '{@SYSTEM_ID_D}'
	and   TBLSYU    = '{@TBLSYU}'
	<og:where startKey="and">
		<og:and value="TABLE_NAME like '{@TABLE_NAME}'"	/>
		<og:and value="CLM        like '{@CLM}'"		/>
	</og:where>
	and		KBCLM	< '6'
	and		FGJ		= '1'
	and   ( EDITOR in ('BITBOX','CHBOX2','DATALIST','MENU','RADIO')
		OR  RENDERER in ('BITBOX','CHBOX2','MENU','RADIO') )
	order by CLM,KBSAKU DESC,TABLE_NAME
</og:query>

<og:query command="{@command}" debug="{@debug}" dbid="{@DBID_R}" maxRowCount="0" scope="request">
	select '0' as WRITABLE,'' as TABLE_NAME,CLM,NAME_JA,
			RENDERER,EDITOR,DBTYPE,0 SEQ,'' as CODE,'' as CODENAME,'' as BIKO,UNIQ,SYSTEM_ID,
			KBSAKU,SUBSYS
	from GEA03
	where SYSTEM_ID in ( '{@SYSTEM_ID_R}','**' )
	<og:where startKey="and">
		<og:and value="CLM like '{@CLM}'" />
	</og:where>
	and   FGJ       = '1'
	and   ( EDITOR in ('BITBOX','CHBOX2','DATALIST','MENU','RADIO')
		OR RENDERER in ('BITBOX','CHBOX2','MENU','RADIO') )
	ORDER BY SYSTEM_ID DESC, KBSAKU DESC, CLM
</og:query>

<og:tableFilter classId="DBSELECT" dbid="{@DBID_R}" scope="request" keys="APPEND" vals="true" selectedAll="true" >
	select CASE
			WHEN B.LNAME LIKE '%:%' THEN B.LNAME
			ELSE A.CODE||':'||B.LNAME END BIKO
	from GEA04 A inner join GEA08 B
	on A.CLM||'.'||A.CODE = B.CLM
	and A.FGJ = B.FGJ
	and A.SYSTEM_ID   = B.SYSTEM_ID
	where A.SYSTEM_ID = [SYSTEM_ID]
	and A.CLM = [CLM]
	and A.FGJ = '1'
	and B.LANG = 'ja'
	order by A.CLM,A.SEQNO
</og:tableFilter>

<!-- CLMでグループ処理されます。先行優先 -->
<og:tableMerge
	action        = "DIFFERENCE"
	masterScope   = "session"
	masterKeys    = "CLM"
	diffKeys      = "BIKO"
	modifyClms    = "UNIQ"
	slaveScope    = "request"
	display       = "true"
/>

<og:tableFilter classId="BIKO2CODE" />

<og:value key="lblKey" value="C" />

<og:hidden name="SQL">
	INSERT INTO GEA04
		(SYSTEM_ID,CLM,CODE,SEQNO,CODELVL,KBSAKU,SUBSYS,
		 FGJ,DYSET,DYUPD,USRSET,USRUPD,PGUPD)
	VALUES
		([SYSTEM_ID],[CLM],[CODE],[SEQ],'1',[KBSAKU],[SUBSYS],
		 '1','{@USER.YMDH}','{@USER.YMDH}','{@USER.ID}','{@USER.ID}','{@GUI.KEY}')
</og:hidden>

<og:hidden name="SQL2">
	INSERT INTO GEA08
		(SYSTEM_ID,CLM,
		 NAME_JA,LNAME,
		 KBSAKU,SUBSYS,KBLBL,LANG,FGJ,DYSET,DYUPD,USRSET,USRUPD,PGUPD)
	VALUES
		([SYSTEM_ID],[CLM]||'.'||[CODE],
		 [CODENAME],[CODENAME],
		 [KBSAKU],[SUBSYS],'6','ja','1','{@USER.YMDH}','{@USER.YMDH}','{@USER.ID}','{@USER.ID}','{@GUI.KEY}')
</og:hidden>

<!-- コードリソース(GEA04) をINSERT するので、重複するリソースを、削除(FGJ='9')する。 -->
<og:hidden name="SQL3">
	UPDATE GEA04 A SET
			A.FGJ		= '9',
			A.DYUPD		= '{@USER.YMDH}',
			A.USRUPD	= '{@USER.ID}',
			A.PGUPD		= '{@GUI.KEY}'
	WHERE	A.SYSTEM_ID	= [SYSTEM_ID]
	AND		A.CLM		= [CLM]
	AND		A.CODE		= [CODE]
	AND		A.KBSAKU	= [KBSAKU]
	AND		exists (select 1 from GEA04 B
					where A.SYSTEM_ID = B.SYSTEM_ID
					and   A.CLM       = B.CLM
					and   A.KBSAKU    = B.KBSAKU
					and   A.CODE      = B.CODE
					and   A.UNIQ      < B.UNIQ
				)
</og:hidden>

<!-- ラベルリソース(GEA08) をINSERT するので、重複するリソースを、削除(FGJ='9')する。 -->
<og:hidden name="SQL4">
	UPDATE GEA08 A SET
			A.FGJ		= '9',
			A.DYUPD		= '{@USER.YMDH}',
			A.USRUPD	= '{@USER.ID}',
			A.PGUPD		= '{@GUI.KEY}'
	WHERE	A.SYSTEM_ID	= [SYSTEM_ID]
	AND		A.CLM	like [CLM]||'.%'
	AND		A.KBSAKU	= [KBSAKU]
	AND		A.LANG	= 'ja'
	AND		exists (select 1 from GEA08 B
					where A.SYSTEM_ID = B.SYSTEM_ID
					and   A.CLM       = B.CLM
					and   A.KBSAKU    = B.KBSAKU
					and   A.LANG      = B.LANG
					and   A.UNIQ      < B.UNIQ
				)
</og:hidden>

<og:hidden name="DBID2" value="{@DBID_R}" />
