200201-007.監査候補リスト取得SQL 无 WITH get_system AS(SELECT system_name --システム名称 FROM weeds_master_system --システムマスタWHERE idm_flag = '1' --パスワード発行フラグ（0=発行しない 1=発行する） AND '1' =:referencescopeID --データ参照
200201-007 . 監査候補リスト取得SQL
WITH get_system AS(
SELECT system_name --システム名称
FROM weeds_master_system --システムマスタ
WHERE idm_flag = '1' --パスワード発行フラグ（0=発行しない 1=発行する）
AND '1' =:referencescopeID --データ参照スコープ:1:全システム参照可
UNION
SELECT msys.system_name --システムマスタ
FROM weeds_master_system msys, --システムマスタ
weeds_master_system_person syspers --システム担当者マスタ
WHERE msys.system_name = syspers.system_name --システムマスタ
AND '0' =:referencescopeID ----データ参照スコープ:0:担当システムのみ参照可
AND syspers.term_user =:userID --ユーザーID
AND msys.idm_flag = '1' --パスワード発行フラグ（0=発行しない 1=発行する）
),
get_temp_result AS(
SELECT adfe.report_name, --監査レポート名称
adfe.system_name, --システム名
adfe.ope_hostname, --ホスト名
adfe.user_name, --氏名
adfe.audit_id, --監査pk
adfe.entry_date, --作成日付
adfe.id_applicationstatus --申請ステータス
FROM weeds_adf_entry adfe, --AuditFlowシステムログテーブル
get_system systemname --システム名
WHERE adfe.system_name = systemname.system_name --システム名
AND adfe.entry_date > TRUNC(ADD_MONTHS(SYSDATE,:auditMonth),'MM') --（当月 - nヶ月）
AND (adfe.report_no = :reportNo
OR :reportNo IS NULL) --監査レポート番号
AND (adfe.id_applicationstatus =:applicationstatusID1 --申請ステータス
OR adfe.id_applicationstatus =:applicationstatusID2 --申請ステータス
OR :applicationstatusID1 IS NULL)
AND (adfe.system_name = :systemCode --システムコード
OR :systemCode IS NULL)
ORDER BY adfe.report_name, --監査レポート名称
adfe.system_name, --システム名
adfe.ope_hostname,--ホスト名
adfe.entry_date, --作成日付
adfe.user_name --氏名
),
get_result AS(
SELECT report_name, --監査レポート名称
system_name, --システム名
ope_hostname, --ホスト名
TO_CHAR(TO_DATE(entry_date,'YYYY-MM-DD'),'MM-DD') || '|'
||user_name AS dayusrname,--監査日付||氏名
audit_id, --監査pk
entry_date, --作成日付
id_applicationstatus --申請ステータス
FROM get_temp_result
WHERE ROWNUM < = :rowno +1),
get_sum_report AS(
SELECT report_name, --監査レポート名称
'' system_name, --システム名
'' ope_hostname, --ホスト名
'' dayusrname, --監査日付||氏名
'' audit_id, --監査pk
report_name || '(' || COUNT(*) || '件 )' cnt, --監査レポート名称||合計件数
'' id_applicationstatus --申請ステータス
FROM get_result
WHERE ROWNUM < = :rowno
GROUP BY report_name --監査レポート名称
ORDER BY report_name --監査レポート名称
),
get_sum_report_sys AS(
SELECT report_name, --監査レポート名称
system_name, --システム名
'' ope_hostname, --ホスト名
'' dayusrname, --監査日付||氏名
'' audit_id, --監査pk
system_name || '(' || COUNT(*) || '件 )' cnt, --システム名||合計件数
'' id_applicationstatus --申請ステータス
FROM get_result
WHERE ROWNUM < = :rowno
GROUP BY report_name, --監査レポート名称
system_name --システム名
ORDER BY report_name, --監査レポート名称
system_name --システム名
),
get_sum_report_sys_host AS(
SELECT report_name, --監査レポート名称
system_name, --システム名
ope_hostname, --ホスト名
'' dayusrname, --監査日付||氏名
'' audit_id, --監査pk
ope_hostname || '(' || COUNT(*) || '件 )' cnt, --システム名||合計件数
'' id_applicationstatus --申請ステータス
FROM get_result
WHERE ROWNUM < = :rowno
GROUP BY report_name, --監査レポート名称
system_name, --システム名
ope_hostname --ホスト名
ORDER BY report_name, --監査レポート名称
system_name, --システム名
ope_hostname --ホスト名
)
SELECT report_name, --監査レポート名称
system_name, --システム名
ope_hostname, --ホスト名
dayusrname, --監査日付||氏名
audit_id, --監査pk
cnt, --監査レポート名称||合計件数
id_applicationstatus --申請ステータス
FROM get_sum_report
UNION ALL
SELECT report_name, --監査レポート名称
system_name, --システム名
ope_hostname, --ホスト名
dayusrname, --監査日付||氏名
audit_id, --監査pk
cnt, --システム名||合計件数
id_applicationstatus --申請ステータス
FROM get_sum_report_sys
UNION ALL
SELECT report_name, --監査レポート名称
system_name, --システム名
ope_hostname, --ホスト名
dayusrname, --監査日付||氏名
audit_id, --監査pk
cnt, --システム名||合計件数
id_applicationstatus --申請ステータス
FROM get_sum_report_sys_host
UNION ALL
SELECT report_name, --監査レポート名称
system_name, --システム名
ope_hostname, --ホスト名
dayusrname, --監査日付||氏名
audit_id, --監査pk
'' cnt , --合計件数
id_applicationstatus --申請ステータス
FROM get_result
