ユーザー情報、ステータス情報の条件によって取得するデータを制限したい

質問

・データベース Oracle10g
・プログラム　java 1.6

お世話になっております。業務でOracleを利用して以下のデータをしたいと考えています。

★やりたいこと

・検索画面から、状態を指定し、次画面の検索結果画面で結果を表示したい。
・検索条件は、状態のみ(本当はいろいろあるけどここでは割愛)
・ログイン時のユーザー情報を保持しており、画面で指定された'状態’と'ユーザー'をキーにデータを取得する
・係長、一般職、派遣のユーザーは "状態"が"本社承認済み"以降のデータしか見られない
・ただし、自分が登録・申請・承認に関わるデータは"本社承認済み"以前の状態のものも見られる
・上記の結果を一回のSQLで取得したい

■テーブル:予定情報
---------------------------------------------
登録内容 | 登録者 | 申請者 | 承認者 | 状態・・・・
---------------------------------------------
AAAAAAAA |派遣　　| 課長　 | 部長　 | 登録済み
BBBBBBBB |一般職　| 係長 | 申請済み
CCCCCCCC |派遣　　| 一般職 | 課長　 | 本社承認済み
DDDDDDDD |課長　　| 課長　 | 部長　 | 連携済み
EEEEEEEE |派遣　　| 派遣　 | 係長　 | 反映済み

※ユーザーが一般職の場合、登録内容AAAAAAAA以外のデータを取得する
派遣の場合は、登録内容BBBBBBBB以外のデータを取得する
係長の場合は、登録内容AAAAAAAA以外のデータを取得する

■ユーザー(登録者、申請者、承認者のデータ)
以下のユーザーが存在しています。
・社長
・部長
・課長
・係長
・一般職
・派遣

■画面から指定可能な状態は以下の通り
・すべて
・登録済み
・申請済み
・本社承認済み
・連携済み
・反映済み

★わからないこと
検索画面で状態'すべて'が選択された場合に、
係長、一般職、派遣のユーザーについて状態"が"本社承認済み"以降のデータはすべて表示し、
自分が登録・申請・承認に関わるデータは"本社承認済み"以前の状態のものを取得するというSQL
の条件指定をおこない際の構文がわからない。

状態が'登録済み'かつ、ユーザーが'派遣'の場合のSQLは以下ですが、
状態で'すべて'が選択された場合は、どのような構文になるのでしょうか？

Selct * from 予定情報 where 状態 ='登録済み'
and 登録者 = '派遣'　and 申請者 = '派遣'　and 承認者　='派遣'

なにとぞよろしくお願いいたします。

回答No.1

3rd_001

こういった場合、以下のようなマスタ類を整備します。

■状態マスタ

状態コード、状態
・10:登録済み
・20:申請済み
・30:本社承認済み
・40:連携済み
・50:反映済み

"本社承認済み"以前は、「where 状態コード > 30」で表現可能です。
※というか、以前以後を表現するためには「本社承認済み」という文字列では表現できません。

また、ユーザに対しては参照可能な「状態コード」を付与します。
こうすることで、ユーザが参照可能な状態を定義します。

■ユーザマスタ
ユーザID、参照可能状態コード、ユーザ名
・01　10　社長
・02　10　部長
・03　10　課長
・04　40　係長
・05　40　一般職
・06　40　派遣

こうしておけば以下条件で各ユーザが参照可能な状態を指定できます。
ユーザ毎に個別指定も可能です。
「where 状態コード >= 参照可能状態コード」
一般職は40からしか見られなくなりますし、課長は10から見られます。

■状態マスタ

・99　すべて
・10　登録済み
・20　申請済み
・30　本社承認済み
・40　連携済み
・50　反映済み

(1) 検索条件取得
「一般職」が状態「登録済み」を指定。

・ユーザID(05)と参照可能状態コード(40)を取得
・指定された状態(10)を取得

(2)SQLの組み立て
　(1)で取得した条件を以下に当てはめていきます。
　カッコで条件の優先順位を定義します。

条件は(a)、(b)、(c)に分けます。
(a) 自己データの検索部分
(b)ユーザ権限による検索部分
(c)指定された状態を権限に付与する。

優先順位は以下にします。
ただし、(c)の条件は99の場合は指定しません。
( (a) or (b) ) and (c)

以下のようなSQLとなります。

--(a) 自己データの検索部分
-- 登録者、申請者 、承認者が自分 の場合は、状態を検索条件にしない。
where
(
(
(登録者コード = 05 or 申請者コード = 05 or 承認者コード = 05 )
)
-- (b)ユーザ権限による検索部分
-- 参照可能な状態を指定する。
or ( where 状態コード >= 40)
)
-- (c)指定された状態を権限に付与する。
-- ※99の場合は条件指定を省略
and 状態コード = 10
--

これくらいのテーブル構成でないと、実現はできないと思います。
じゃないと、java内部で条件指定を直書きすることになります。

あと、これだと（and条件）、登録も申請も承認も「派遣」の人が全部やったデータしかみられませんよ？
>Select * from 予定情報 where 状態 ='登録済み'
>and 登録者 = '派遣'　and 申請者 = '派遣'　and 承認者　='派遣'