xit-app
/
greencar-cfs
21
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
You cannot select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
main
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'main'
${ noResults }
greencar-cfs/ddl/center/02_view.sql

6634 lines
255 KiB
SQL
Raw Permalink Blame History

DROP VIEW NAMSAN.WORKER_ALL;
/* Formatted on 2019/01/28 11:16 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FORCE VIEW namsan.worker_all (t_id, t_name, d_st, d_ed)
AS
SELECT worker_id AS t_id, worker_name AS t_name, '00000000' AS d_st,
'00000000' AS d_ed
FROM worker_info
WHERE work_end_yn = '0'
UNION ALL
SELECT b.worker_id AS t_id, b.worker_name AS t_name, stt_date AS d_st,
end_date AS d_ed
FROM worker_his_info b
WHERE b.use_yn = '0'
ORDER BY t_id;
COMMENT ON TABLE NAMSAN.WORKER_ALL IS '징수원정보';
COMMENT ON COLUMN NAMSAN.WORKER_ALL.T_ID IS '징수원ID';
COMMENT ON COLUMN NAMSAN.WORKER_ALL.T_NAME IS '징수원명';
COMMENT ON COLUMN NAMSAN.WORKER_ALL.D_ST IS '시작일자';
COMMENT ON COLUMN NAMSAN.WORKER_ALL.D_ED IS '종료일자';
DROP VIEW NAMSAN.V_RFID_INFO;
/* Formatted on 2019/01/28 11:16 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FORCE VIEW namsan.v_rfid_info (rfid_tag,
car_no,
car_type_kbn,
week_wrong_no
)
AS
SELECT rfid_tag, car_no, car_type_kbn, week_wrong_no
FROM rfid_info;
DROP VIEW NAMSAN.RFID_TIME;
/* Formatted on 2019/01/28 11:16 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FORCE VIEW namsan.rfid_time (t_office,
t_date,
t_time,
week_cash_q,
week_sun_q,
week_who_q,
week_coupon_q,
hybrid_free_q,
hybrid_cash_q,
hybrid_sun_q,
hybrid_who_q,
hybrid_coupon_q,
duo_cash_q,
duo_sun_q,
duo_who_q,
duo_coupon_q,
week_cash_m,
week_sun_m,
week_who_m,
hybrid_cash_m,
hybrid_sun_m,
hybrid_who_m,
duo_cash_m,
duo_sun_m,
duo_who_m
)
AS
SELECT /**ALL_ROWS**/ fare_office_id AS t_office,
YEAR || MONTH || DAY AS t_date,
SUBSTR (work_times, 1, 2) AS t_time,
NVL (SUM (DECODE (rfid_kbn,
'01', DECODE (collect_id, '11', 1, 0),
0
)
),
0
) AS week_cash_q,
NVL (SUM (DECODE (rfid_kbn,
'01', DECODE (collect_id, '23', 1, 0),
0
)
),
0
) AS week_sun_q,
NVL (SUM (DECODE (rfid_kbn,
'01', DECODE (collect_id, '24', 1, 0),
0
)
),
0
) AS week_who_q,
NVL (SUM (DECODE (rfid_kbn,
'01', DECODE (collect_id, '53', 1, 0),
0
)
),
0
) AS week_coupon_q,
NVL (SUM (DECODE (rfid_kbn,
'02', DECODE (collect_id, '30', 1, 0),
0
)
),
0
) AS hybrid_free_q,
NVL (SUM (DECODE (rfid_kbn,
'03', DECODE (collect_id, '11', 1, 0),
0
)
),
0
) AS hybrid_cash_q,
NVL (SUM (DECODE (rfid_kbn,
'03', DECODE (collect_id, '23', 1, 0),
0
)
),
0
) AS hybrid_sun_q,
NVL (SUM (DECODE (rfid_kbn,
'03', DECODE (collect_id, '24', 1, 0),
0
)
),
0
) AS hybrid_who_q,
NVL (SUM (DECODE (rfid_kbn,
'03', DECODE (collect_id, '53', 1, 0),
0
)
),
0
) AS hybrid_coupon_q,
NVL (SUM (DECODE (rfid_kbn,
'04', DECODE (collect_id, '11', 1, 0),
0
)
),
0
) AS duo_cash_q,
NVL (SUM (DECODE (rfid_kbn,
'04', DECODE (collect_id, '23', 1, 0),
0
)
),
0
) AS duo_sun_q,
NVL (SUM (DECODE (rfid_kbn,
'04', DECODE (collect_id, '24', 1, 0),
0
)
),
0
) AS duo_who_q,
NVL (SUM (DECODE (rfid_kbn,
'04', DECODE (collect_id, '53', 1, 0),
0
)
),
0
) AS duo_coupon_q,
NVL (SUM (DECODE (rfid_kbn,
'01', DECODE (collect_id,
'11', levy_amount,
0
),
0
)
),
0
) AS week_cash_m,
NVL (SUM (DECODE (rfid_kbn,
'01', DECODE (collect_id,
'23', levy_amount,
0
),
0
)
),
0
) AS week_sun_m,
NVL (SUM (DECODE (rfid_kbn,
'01', DECODE (collect_id,
'24', levy_amount,
0
),
0
)
),
0
) AS week_who_m,
NVL (SUM (DECODE (rfid_kbn,
'03', DECODE (collect_id,
'11', levy_amount,
0
),
0
)
),
0
) AS hybrid_cash_m,
NVL (SUM (DECODE (rfid_kbn,
'03', DECODE (collect_id,
'23', levy_amount,
0
),
0
)
),
0
) AS hybrid_sun_m,
NVL (SUM (DECODE (rfid_kbn,
'03', DECODE (collect_id,
'24', levy_amount,
0
),
0
)
),
0
) AS hybrid_who_m,
NVL (SUM (DECODE (rfid_kbn,
'04', DECODE (collect_id,
'11', levy_amount,
0
),
0
)
),
0
) AS duo_cash_m,
NVL (SUM (DECODE (rfid_kbn,
'04', DECODE (collect_id,
'23', levy_amount,
0
),
0
)
),
0
) AS duo_sun_m,
NVL (SUM (DECODE (rfid_kbn,
'04', DECODE (collect_id,
'24', levy_amount,
0
),
0
)
),
0
) AS duo_who_m
FROM rfid_namsan_info
GROUP BY fare_office_id, YEAR || MONTH || DAY, SUBSTR (work_times, 1, 2);
COMMENT ON TABLE NAMSAN.RFID_TIME IS 'RFID일별통행량및수입금정보';
COMMENT ON COLUMN NAMSAN.RFID_TIME.T_OFFICE IS '요금소';
COMMENT ON COLUMN NAMSAN.RFID_TIME.T_DATE IS '통행일자';
COMMENT ON COLUMN NAMSAN.RFID_TIME.T_TIME IS '통행시간';
COMMENT ON COLUMN NAMSAN.RFID_TIME.WEEK_CASH_Q IS '요일제현금통행량';
COMMENT ON COLUMN NAMSAN.RFID_TIME.WEEK_SUN_Q IS '요일제선불통행량';
COMMENT ON COLUMN NAMSAN.RFID_TIME.WEEK_WHO_Q IS '요일제후불통행량';
COMMENT ON COLUMN NAMSAN.RFID_TIME.WEEK_COUPON_Q IS '요일제쿠폰통행량';
COMMENT ON COLUMN NAMSAN.RFID_TIME.HYBRID_FREE_Q IS '저공해면제통행량';
COMMENT ON COLUMN NAMSAN.RFID_TIME.HYBRID_CASH_Q IS '저공해화현금통행량';
COMMENT ON COLUMN NAMSAN.RFID_TIME.HYBRID_SUN_Q IS '저공해화선불통행량';
COMMENT ON COLUMN NAMSAN.RFID_TIME.HYBRID_WHO_Q IS '저공해화후불통행량';
COMMENT ON COLUMN NAMSAN.RFID_TIME.HYBRID_COUPON_Q IS '저공해화쿠폰통행량';
COMMENT ON COLUMN NAMSAN.RFID_TIME.DUO_CASH_Q IS '요일/저공해화현금통행량';
COMMENT ON COLUMN NAMSAN.RFID_TIME.DUO_SUN_Q IS '요일/저공해화선불통행량';
COMMENT ON COLUMN NAMSAN.RFID_TIME.DUO_WHO_Q IS '요일/저공해화후불통행량';
COMMENT ON COLUMN NAMSAN.RFID_TIME.DUO_COUPON_Q IS '요일/저공해화쿠폰통행량';
COMMENT ON COLUMN NAMSAN.RFID_TIME.WEEK_CASH_M IS '요일제현금수입금';
COMMENT ON COLUMN NAMSAN.RFID_TIME.WEEK_SUN_M IS '요일제선불수입금';
COMMENT ON COLUMN NAMSAN.RFID_TIME.WEEK_WHO_M IS '요일제후불수입금';
COMMENT ON COLUMN NAMSAN.RFID_TIME.HYBRID_CASH_M IS '저공해화현금수입금';
COMMENT ON COLUMN NAMSAN.RFID_TIME.HYBRID_SUN_M IS '저공해화선불수입금';
COMMENT ON COLUMN NAMSAN.RFID_TIME.HYBRID_WHO_M IS '저공해화후불수입금';
COMMENT ON COLUMN NAMSAN.RFID_TIME.DUO_CASH_M IS '요일/저공해화현금수입금';
COMMENT ON COLUMN NAMSAN.RFID_TIME.DUO_SUN_M IS '요일/저공해화선불수입금';
COMMENT ON COLUMN NAMSAN.RFID_TIME.DUO_WHO_M IS '요일/저공해화후불수입금';
DROP VIEW NAMSAN.RFID_MONTH;
/* Formatted on 2019/01/28 11:16 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FORCE VIEW namsan.rfid_month (t_office,
t_date,
week_cash_q,
week_sun_q,
week_who_q,
week_coupon_q,
hybrid_free_q,
hybrid_cash_q,
hybrid_sun_q,
hybrid_who_q,
hybrid_coupon_q,
duo_cash_q,
duo_sun_q,
duo_who_q,
duo_coupon_q,
week_cash_m,
week_sun_m,
week_who_m,
hybrid_cash_m,
hybrid_sun_m,
hybrid_who_m,
duo_cash_m,
duo_sun_m,
duo_who_m
)
AS
SELECT /**ALL_ROWS**/ fare_office_id AS t_office, YEAR || MONTH AS t_date,
NVL (SUM (DECODE (rfid_kbn,
'01', DECODE (collect_id, '11', 1, 0),
0
)
),
0
) AS week_cash_q,
NVL (SUM (DECODE (rfid_kbn,
'01', DECODE (collect_id, '23', 1, 0),
0
)
),
0
) AS week_sun_q,
NVL (SUM (DECODE (rfid_kbn,
'01', DECODE (collect_id, '24', 1, 0),
0
)
),
0
) AS week_who_q,
NVL (SUM (DECODE (rfid_kbn,
'01', DECODE (collect_id, '53', 1, 0),
0
)
),
0
) AS week_coupon_q,
NVL (SUM (DECODE (rfid_kbn,
'02', DECODE (collect_id, '30', 1, 0),
0
)
),
0
) AS hybrid_free_q,
NVL (SUM (DECODE (rfid_kbn,
'03', DECODE (collect_id, '11', 1, 0),
0
)
),
0
) AS hybrid_cash_q,
NVL (SUM (DECODE (rfid_kbn,
'03', DECODE (collect_id, '23', 1, 0),
0
)
),
0
) AS hybrid_sun_q,
NVL (SUM (DECODE (rfid_kbn,
'03', DECODE (collect_id, '24', 1, 0),
0
)
),
0
) AS hybrid_who_q,
NVL (SUM (DECODE (rfid_kbn,
'03', DECODE (collect_id, '53', 1, 0),
0
)
),
0
) AS hybrid_coupon_q,
NVL (SUM (DECODE (rfid_kbn,
'04', DECODE (collect_id, '11', 1, 0),
0
)
),
0
) AS duo_cash_q,
NVL (SUM (DECODE (rfid_kbn,
'04', DECODE (collect_id, '23', 1, 0),
0
)
),
0
) AS duo_sun_q,
NVL (SUM (DECODE (rfid_kbn,
'04', DECODE (collect_id, '24', 1, 0),
0
)
),
0
) AS duo_who_q,
NVL (SUM (DECODE (rfid_kbn,
'04', DECODE (collect_id, '53', 1, 0),
0
)
),
0
) AS duo_coupon_q,
NVL (SUM (DECODE (rfid_kbn,
'01', DECODE (collect_id,
'11', levy_amount,
0
),
0
)
),
0
) AS week_cash_m,
NVL (SUM (DECODE (rfid_kbn,
'01', DECODE (collect_id,
'23', levy_amount,
0
),
0
)
),
0
) AS week_sun_m,
NVL (SUM (DECODE (rfid_kbn,
'01', DECODE (collect_id,
'24', levy_amount,
0
),
0
)
),
0
) AS week_who_m,
NVL (SUM (DECODE (rfid_kbn,
'03', DECODE (collect_id,
'11', levy_amount,
0
),
0
)
),
0
) AS hybrid_cash_m,
NVL (SUM (DECODE (rfid_kbn,
'03', DECODE (collect_id,
'23', levy_amount,
0
),
0
)
),
0
) AS hybrid_sun_m,
NVL (SUM (DECODE (rfid_kbn,
'03', DECODE (collect_id,
'24', levy_amount,
0
),
0
)
),
0
) AS hybrid_who_m,
NVL (SUM (DECODE (rfid_kbn,
'04', DECODE (collect_id,
'11', levy_amount,
0
),
0
)
),
0
) AS duo_cash_m,
NVL (SUM (DECODE (rfid_kbn,
'04', DECODE (collect_id,
'23', levy_amount,
0
),
0
)
),
0
) AS duo_sun_m,
NVL (SUM (DECODE (rfid_kbn,
'04', DECODE (collect_id,
'24', levy_amount,
0
),
0
)
),
0
) AS duo_who_m
FROM rfid_namsan_info
GROUP BY fare_office_id, YEAR || MONTH;
COMMENT ON TABLE NAMSAN.RFID_MONTH IS 'RFID일별통행량및수입금정보';
COMMENT ON COLUMN NAMSAN.RFID_MONTH.DUO_CASH_M IS '요일/저공해화현금수입금';
COMMENT ON COLUMN NAMSAN.RFID_MONTH.DUO_SUN_M IS '요일/저공해화선불수입금';
COMMENT ON COLUMN NAMSAN.RFID_MONTH.DUO_WHO_M IS '요일/저공해화후불수입금';
COMMENT ON COLUMN NAMSAN.RFID_MONTH.T_OFFICE IS '요금소';
COMMENT ON COLUMN NAMSAN.RFID_MONTH.T_DATE IS '통행일자';
COMMENT ON COLUMN NAMSAN.RFID_MONTH.WEEK_CASH_Q IS '요일제현금통행량';
COMMENT ON COLUMN NAMSAN.RFID_MONTH.WEEK_SUN_Q IS '요일제선불통행량';
COMMENT ON COLUMN NAMSAN.RFID_MONTH.WEEK_WHO_Q IS '요일제후불통행량';
COMMENT ON COLUMN NAMSAN.RFID_MONTH.WEEK_COUPON_Q IS '요일제쿠폰통행량';
COMMENT ON COLUMN NAMSAN.RFID_MONTH.HYBRID_FREE_Q IS '저공해면제통행량';
COMMENT ON COLUMN NAMSAN.RFID_MONTH.HYBRID_CASH_Q IS '저공해화현금통행량';
COMMENT ON COLUMN NAMSAN.RFID_MONTH.HYBRID_SUN_Q IS '저공해화선불통행량';
COMMENT ON COLUMN NAMSAN.RFID_MONTH.HYBRID_WHO_Q IS '저공해화후불통행량';
COMMENT ON COLUMN NAMSAN.RFID_MONTH.HYBRID_COUPON_Q IS '저공해화쿠폰통행량';
COMMENT ON COLUMN NAMSAN.RFID_MONTH.DUO_CASH_Q IS '요일/저공해화현금통행량';
COMMENT ON COLUMN NAMSAN.RFID_MONTH.DUO_SUN_Q IS '요일/저공해화선불통행량';
COMMENT ON COLUMN NAMSAN.RFID_MONTH.DUO_WHO_Q IS '요일/저공해화후불통행량';
COMMENT ON COLUMN NAMSAN.RFID_MONTH.DUO_COUPON_Q IS '요일/저공해화쿠폰통행량';
COMMENT ON COLUMN NAMSAN.RFID_MONTH.WEEK_CASH_M IS '요일제현금수입금';
COMMENT ON COLUMN NAMSAN.RFID_MONTH.WEEK_SUN_M IS '요일제선불수입금';
COMMENT ON COLUMN NAMSAN.RFID_MONTH.WEEK_WHO_M IS '요일제후불수입금';
COMMENT ON COLUMN NAMSAN.RFID_MONTH.HYBRID_CASH_M IS '저공해화현금수입금';
COMMENT ON COLUMN NAMSAN.RFID_MONTH.HYBRID_SUN_M IS '저공해화선불수입금';
COMMENT ON COLUMN NAMSAN.RFID_MONTH.HYBRID_WHO_M IS '저공해화후불수입금';
DROP VIEW NAMSAN.RFID_DAY;
/* Formatted on 2019/01/28 11:16 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FORCE VIEW namsan.rfid_day (t_office,
t_date,
week_cash_q,
week_sun_q,
week_who_q,
week_coupon_q,
hybrid_free_q,
hybrid_cash_q,
hybrid_sun_q,
hybrid_who_q,
hybrid_coupon_q,
duo_cash_q,
duo_sun_q,
duo_who_q,
duo_coupon_q,
week_cash_m,
week_sun_m,
week_who_m,
hybrid_cash_m,
hybrid_sun_m,
hybrid_who_m,
duo_cash_m,
duo_sun_m,
duo_who_m
)
AS
SELECT /**ALL_ROWS**/ fare_office_id AS t_office,
YEAR || MONTH || DAY AS t_date,
NVL (SUM (DECODE (rfid_kbn,
'01', DECODE (collect_id, '11', 1, 0),
0
)
),
0
) AS week_cash_q,
NVL (SUM (DECODE (rfid_kbn,
'01', DECODE (collect_id, '23', 1, 0),
0
)
),
0
) AS week_sun_q,
NVL (SUM (DECODE (rfid_kbn,
'01', DECODE (collect_id, '24', 1, 0),
0
)
),
0
) AS week_who_q,
NVL (SUM (DECODE (rfid_kbn,
'01', DECODE (collect_id, '53', 1, 0),
0
)
),
0
) AS week_coupon_q,
NVL (SUM (DECODE (rfid_kbn,
'02', DECODE (collect_id, '30', 1, 0),
0
)
),
0
) AS hybrid_free_q,
NVL (SUM (DECODE (rfid_kbn,
'03', DECODE (collect_id, '11', 1, 0),
0
)
),
0
) AS hybrid_cash_q,
NVL (SUM (DECODE (rfid_kbn,
'03', DECODE (collect_id, '23', 1, 0),
0
)
),
0
) AS hybrid_sun_q,
NVL (SUM (DECODE (rfid_kbn,
'03', DECODE (collect_id, '24', 1, 0),
0
)
),
0
) AS hybrid_who_q,
NVL (SUM (DECODE (rfid_kbn,
'03', DECODE (collect_id, '53', 1, 0),
0
)
),
0
) AS hybrid_coupon_q,
NVL (SUM (DECODE (rfid_kbn,
'04', DECODE (collect_id, '11', 1, 0),
0
)
),
0
) AS duo_cash_q,
NVL (SUM (DECODE (rfid_kbn,
'04', DECODE (collect_id, '23', 1, 0),
0
)
),
0
) AS duo_sun_q,
NVL (SUM (DECODE (rfid_kbn,
'04', DECODE (collect_id, '24', 1, 0),
0
)
),
0
) AS duo_who_q,
NVL (SUM (DECODE (rfid_kbn,
'04', DECODE (collect_id, '53', 1, 0),
0
)
),
0
) AS duo_coupon_q,
NVL (SUM (DECODE (rfid_kbn,
'01', DECODE (collect_id,
'11', levy_amount,
0
),
0
)
),
0
) AS week_cash_m,
NVL (SUM (DECODE (rfid_kbn,
'01', DECODE (collect_id,
'23', levy_amount,
0
),
0
)
),
0
) AS week_sun_m,
NVL (SUM (DECODE (rfid_kbn,
'01', DECODE (collect_id,
'24', levy_amount,
0
),
0
)
),
0
) AS week_who_m,
NVL (SUM (DECODE (rfid_kbn,
'03', DECODE (collect_id,
'11', levy_amount,
0
),
0
)
),
0
) AS hybrid_cash_m,
NVL (SUM (DECODE (rfid_kbn,
'03', DECODE (collect_id,
'23', levy_amount,
0
),
0
)
),
0
) AS hybrid_sun_m,
NVL (SUM (DECODE (rfid_kbn,
'03', DECODE (collect_id,
'24', levy_amount,
0
),
0
)
),
0
) AS hybrid_who_m,
NVL (SUM (DECODE (rfid_kbn,
'04', DECODE (collect_id,
'11', levy_amount,
0
),
0
)
),
0
) AS duo_cash_m,
NVL (SUM (DECODE (rfid_kbn,
'04', DECODE (collect_id,
'23', levy_amount,
0
),
0
)
),
0
) AS duo_sun_m,
NVL (SUM (DECODE (rfid_kbn,
'04', DECODE (collect_id,
'24', levy_amount,
0
),
0
)
),
0
) AS duo_who_m
FROM rfid_namsan_info
GROUP BY fare_office_id, YEAR || MONTH || DAY;
COMMENT ON TABLE NAMSAN.RFID_DAY IS 'RFID일별통행량및수입금정보';
COMMENT ON COLUMN NAMSAN.RFID_DAY.T_OFFICE IS '요금소';
COMMENT ON COLUMN NAMSAN.RFID_DAY.T_DATE IS '통행일자';
COMMENT ON COLUMN NAMSAN.RFID_DAY.WEEK_CASH_Q IS '요일제현금통행량';
COMMENT ON COLUMN NAMSAN.RFID_DAY.WEEK_SUN_Q IS '요일제선불통행량';
COMMENT ON COLUMN NAMSAN.RFID_DAY.WEEK_WHO_Q IS '요일제후불통행량';
COMMENT ON COLUMN NAMSAN.RFID_DAY.WEEK_COUPON_Q IS '요일제쿠폰통행량';
COMMENT ON COLUMN NAMSAN.RFID_DAY.HYBRID_FREE_Q IS '저공해면제통행량';
COMMENT ON COLUMN NAMSAN.RFID_DAY.HYBRID_CASH_Q IS '저공해화현금통행량';
COMMENT ON COLUMN NAMSAN.RFID_DAY.HYBRID_SUN_Q IS '저공해화선불통행량';
COMMENT ON COLUMN NAMSAN.RFID_DAY.HYBRID_WHO_Q IS '저공해화후불통행량';
COMMENT ON COLUMN NAMSAN.RFID_DAY.HYBRID_COUPON_Q IS '저공해화쿠폰통행량';
COMMENT ON COLUMN NAMSAN.RFID_DAY.DUO_CASH_Q IS '요일/저공해화현금통행량';
COMMENT ON COLUMN NAMSAN.RFID_DAY.DUO_SUN_Q IS '요일/저공해화선불통행량';
COMMENT ON COLUMN NAMSAN.RFID_DAY.DUO_WHO_Q IS '요일/저공해화후불통행량';
COMMENT ON COLUMN NAMSAN.RFID_DAY.DUO_COUPON_Q IS '요일/저공해화쿠폰통행량';
COMMENT ON COLUMN NAMSAN.RFID_DAY.WEEK_CASH_M IS '요일제현금수입금';
COMMENT ON COLUMN NAMSAN.RFID_DAY.WEEK_SUN_M IS '요일제선불수입금';
COMMENT ON COLUMN NAMSAN.RFID_DAY.WEEK_WHO_M IS '요일제후불수입금';
COMMENT ON COLUMN NAMSAN.RFID_DAY.HYBRID_CASH_M IS '저공해화현금수입금';
COMMENT ON COLUMN NAMSAN.RFID_DAY.HYBRID_SUN_M IS '저공해화선불수입금';
COMMENT ON COLUMN NAMSAN.RFID_DAY.HYBRID_WHO_M IS '저공해화후불수입금';
COMMENT ON COLUMN NAMSAN.RFID_DAY.DUO_CASH_M IS '요일/저공해화현금수입금';
COMMENT ON COLUMN NAMSAN.RFID_DAY.DUO_SUN_M IS '요일/저공해화선불수입금';
COMMENT ON COLUMN NAMSAN.RFID_DAY.DUO_WHO_M IS '요일/저공해화후불수입금';
DROP VIEW NAMSAN.PENALTY_PUBLIC_INFO_ROOT;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE FORCE VIEW namsan.penalty_public_info_root
(
penta__row__id,
penalty_manage_year,
penalty_seqno,
owner,
resident_no,
car_no,
worker_id,
fare_office_id,
booth_id,
YEAR,
MONTH,
DAY,
work_times,
seqno,
return_date,
levy_amount,
motion_id,
make_date,
reg_date,
change_date,
reg_status,
change_reason,
gigwan,
sido,
sigungu,
dong,
ri,
san,
bunji,
ho,
tong,
ban,
BLOCK,
special,
sp_dong,
sp_ho,
addr_code,
zip1,
zip2,
return_reason_id,
create_date,
creater,
update_date,
updater
)
AS
SELECT ROWID,
"PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO",
"OWNER",
"RESIDENT_NO",
"CAR_NO",
"WORKER_ID",
"FARE_OFFICE_ID",
"BOOTH_ID",
"YEAR",
"MONTH",
"DAY",
"WORK_TIMES",
"SEQNO",
"RETURN_DATE",
"LEVY_AMOUNT",
"MOTION_ID",
"MAKE_DATE",
"REG_DATE",
"CHANGE_DATE",
"REG_STATUS",
"CHANGE_REASON",
"GIGWAN",
"SIDO",
"SIGUNGU",
"DONG",
"RI",
"SAN",
"BUNJI",
"HO",
"TONG",
"BAN",
"BLOCK",
"SPECIAL",
"SP_DONG",
"SP_HO",
"ADDR_CODE",
"ZIP1",
"ZIP2",
"RETURN_REASON_ID",
"CREATE_DATE",
"CREATER",
"UPDATE_DATE",
"UPDATER"
FROM "NAMSAN"."PENALTY_PUBLIC_INFO_DAMO";
DROP VIEW NAMSAN.PENALTY_PUBLIC_INFO;
CREATE OR REPLACE FORCE VIEW namsan.penalty_public_info (penalty_manage_year,
penalty_seqno,
owner,
resident_no,
car_no,
worker_id,
fare_office_id,
booth_id,
YEAR,
MONTH,
DAY,
work_times,
seqno,
return_date,
levy_amount,
motion_id,
make_date,
reg_date,
change_date,
reg_status,
change_reason,
gigwan,
sido,
sigungu,
dong,
ri,
san,
bunji,
ho,
tong,
ban,
BLOCK,
special,
sp_dong,
sp_ho,
addr_code,
zip1,
zip2,
return_reason_id,
create_date,
creater,
update_date,
updater
)
AS
SELECT "PENALTY_MANAGE_YEAR", "PENALTY_SEQNO", "OWNER", "RESIDENT_NO",
"CAR_NO", "WORKER_ID", "FARE_OFFICE_ID", "BOOTH_ID", "YEAR",
"MONTH", "DAY", "WORK_TIMES", "SEQNO", "RETURN_DATE", "LEVY_AMOUNT",
"MOTION_ID", "MAKE_DATE", "REG_DATE", "CHANGE_DATE", "REG_STATUS",
"CHANGE_REASON", "GIGWAN", "SIDO", "SIGUNGU", "DONG", "RI", "SAN",
"BUNJI", "HO", "TONG", "BAN", "BLOCK", "SPECIAL", "SP_DONG",
"SP_HO", "ADDR_CODE", "ZIP1", "ZIP2", "RETURN_REASON_ID",
"CREATE_DATE", "CREATER", "UPDATE_DATE", "UPDATER"
FROM "NAMSAN"."PENALTY_PUBLIC_INFO_ROOT";
DROP VIEW NAMSAN.PENALTY_HISTORY_INFO_ROOT;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE FORCE VIEW namsan.penalty_history_info_root
(
penta__row__id,
penalty_manage_year,
penalty_seqno,
update_seqno,
worker_id,
fare_office_id,
stt_date,
update_history_date,
levy_manage_year,
levy_seqno,
booth_id,
collect_id,
YEAR,
MONTH,
DAY,
work_times,
car_no,
card_issue_id,
notice_wrong_id,
owner,
notice_id,
resident_no,
fine_amount,
lavy_amount,
motion_id,
notice_date,
notice_wrong_date,
resident_date,
pass_amount,
notice_wrong_reason_id,
seisure_yn,
seizure_date,
seizure_id,
notice_choose_id,
seizure_wrong_id,
seizure_wrong_date,
seizure_termi_date,
seizure_termi,
receipt_yn,
seizure_wrong_reason_id,
double_receipt_yn,
seizure_trmi_yn,
double_receipt_date,
seizure_manage_year,
seizure_manage_no,
bfore_motion_id,
seizure_termi_yn,
seizure_wrong_reason,
notice_wrong_reason,
notice_choose_date,
create_date,
creater,
update_date,
updater,
seqno,
rfid_kbn,
car_change_yn,
car_no_after,
rfid_penalty_kbn
)
AS
SELECT ROWID,
"PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO",
"UPDATE_SEQNO",
"WORKER_ID",
"FARE_OFFICE_ID",
"STT_DATE",
"UPDATE_HISTORY_DATE",
"LEVY_MANAGE_YEAR",
"LEVY_SEQNO",
"BOOTH_ID",
"COLLECT_ID",
"YEAR",
"MONTH",
"DAY",
"WORK_TIMES",
"CAR_NO",
"CARD_ISSUE_ID",
"NOTICE_WRONG_ID",
"OWNER",
"NOTICE_ID",
"RESIDENT_NO",
"FINE_AMOUNT",
"LAVY_AMOUNT",
"MOTION_ID",
"NOTICE_DATE",
"NOTICE_WRONG_DATE",
"RESIDENT_DATE",
"PASS_AMOUNT",
"NOTICE_WRONG_REASON_ID",
"SEISURE_YN",
"SEIZURE_DATE",
"SEIZURE_ID",
"NOTICE_CHOOSE_ID",
"SEIZURE_WRONG_ID",
"SEIZURE_WRONG_DATE",
"SEIZURE_TERMI_DATE",
"SEIZURE_TERMI",
"RECEIPT_YN",
"SEIZURE_WRONG_REASON_ID",
"DOUBLE_RECEIPT_YN",
"SEIZURE_TRMI_YN",
"DOUBLE_RECEIPT_DATE",
"SEIZURE_MANAGE_YEAR",
"SEIZURE_MANAGE_NO",
"BFORE_MOTION_ID",
"SEIZURE_TERMI_YN",
"SEIZURE_WRONG_REASON",
"NOTICE_WRONG_REASON",
"NOTICE_CHOOSE_DATE",
"CREATE_DATE",
"CREATER",
"UPDATE_DATE",
"UPDATER",
"SEQNO",
"RFID_KBN",
"CAR_CHANGE_YN",
"CAR_NO_AFTER",
"RFID_PENALTY_KBN"
FROM "NAMSAN"."PENALTY_HISTORY_INFO_DAMO";
DROP VIEW NAMSAN.PENALTY_HISTORY_INFO;
/* Formatted on 2019/01/28 11:16 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FORCE VIEW namsan.penalty_history_info (penalty_manage_year,
penalty_seqno,
update_seqno,
worker_id,
fare_office_id,
stt_date,
update_history_date,
levy_manage_year,
levy_seqno,
booth_id,
collect_id,
YEAR,
MONTH,
DAY,
work_times,
car_no,
card_issue_id,
notice_wrong_id,
owner,
notice_id,
resident_no,
fine_amount,
lavy_amount,
motion_id,
notice_date,
notice_wrong_date,
resident_date,
pass_amount,
notice_wrong_reason_id,
seisure_yn,
seizure_date,
seizure_id,
notice_choose_id,
seizure_wrong_id,
seizure_wrong_date,
seizure_termi_date,
seizure_termi,
receipt_yn,
seizure_wrong_reason_id,
double_receipt_yn,
seizure_trmi_yn,
double_receipt_date,
seizure_manage_year,
seizure_manage_no,
bfore_motion_id,
seizure_termi_yn,
seizure_wrong_reason,
notice_wrong_reason,
notice_choose_date,
create_date,
creater,
update_date,
updater,
seqno,
rfid_kbn,
car_change_yn,
car_no_after,
rfid_penalty_kbn
)
AS
SELECT "PENALTY_MANAGE_YEAR", "PENALTY_SEQNO", "UPDATE_SEQNO", "WORKER_ID",
"FARE_OFFICE_ID", "STT_DATE", "UPDATE_HISTORY_DATE",
"LEVY_MANAGE_YEAR", "LEVY_SEQNO", "BOOTH_ID", "COLLECT_ID", "YEAR",
"MONTH", "DAY", "WORK_TIMES", "CAR_NO", "CARD_ISSUE_ID",
"NOTICE_WRONG_ID", "OWNER", "NOTICE_ID", "RESIDENT_NO",
"FINE_AMOUNT", "LAVY_AMOUNT", "MOTION_ID", "NOTICE_DATE",
"NOTICE_WRONG_DATE", "RESIDENT_DATE", "PASS_AMOUNT",
"NOTICE_WRONG_REASON_ID", "SEISURE_YN", "SEIZURE_DATE",
"SEIZURE_ID", "NOTICE_CHOOSE_ID", "SEIZURE_WRONG_ID",
"SEIZURE_WRONG_DATE", "SEIZURE_TERMI_DATE", "SEIZURE_TERMI",
"RECEIPT_YN", "SEIZURE_WRONG_REASON_ID", "DOUBLE_RECEIPT_YN",
"SEIZURE_TRMI_YN", "DOUBLE_RECEIPT_DATE", "SEIZURE_MANAGE_YEAR",
"SEIZURE_MANAGE_NO", "BFORE_MOTION_ID", "SEIZURE_TERMI_YN",
"SEIZURE_WRONG_REASON", "NOTICE_WRONG_REASON", "NOTICE_CHOOSE_DATE",
"CREATE_DATE", "CREATER", "UPDATE_DATE", "UPDATER", "SEQNO",
"RFID_KBN", "CAR_CHANGE_YN", "CAR_NO_AFTER", "RFID_PENALTY_KBN"
FROM "NAMSAN"."PENALTY_HISTORY_INFO_ROOT";
DROP VIEW NAMSAN.PENALTY_HISTORY_DETAIL_I_ROOT;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE FORCE VIEW namsan.penalty_history_detail_i_root
(
penta__row__id,
stt_work_date,
end_work_date,
penalty_manage_year,
penalty_seqno,
motion_id,
car_no,
resident_date,
owner,
resident_no,
worker_id,
fare_office_id,
booth_id,
YEAR,
MONTH,
DAY,
work_times,
cancel_yn,
cancel_date,
cancel_reason,
create_date,
creater,
update_date,
updater,
seqno,
rent_kbn
)
AS
SELECT ROWID,
"STT_WORK_DATE",
"END_WORK_DATE",
"PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO",
"MOTION_ID",
"CAR_NO",
"RESIDENT_DATE",
"OWNER",
"RESIDENT_NO",
"WORKER_ID",
"FARE_OFFICE_ID",
"BOOTH_ID",
"YEAR",
"MONTH",
"DAY",
"WORK_TIMES",
"CANCEL_YN",
"CANCEL_DATE",
"CANCEL_REASON",
"CREATE_DATE",
"CREATER",
"UPDATE_DATE",
"UPDATER",
"SEQNO",
"RENT_KBN"
FROM "NAMSAN"."PENALTY_HISTORY_DETAIL_I_DAMO";
DROP VIEW NAMSAN.PENALTY_HISTORY_DETAIL_INFO;
/* Formatted on 2019/01/28 11:16 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FORCE VIEW namsan.penalty_history_detail_info (stt_work_date,
end_work_date,
penalty_manage_year,
penalty_seqno,
motion_id,
car_no,
resident_date,
owner,
resident_no,
worker_id,
fare_office_id,
booth_id,
YEAR,
MONTH,
DAY,
work_times,
cancel_yn,
cancel_date,
cancel_reason,
create_date,
creater,
update_date,
updater,
seqno,
rent_kbn
)
AS
SELECT "STT_WORK_DATE", "END_WORK_DATE", "PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO", "MOTION_ID", "CAR_NO", "RESIDENT_DATE", "OWNER",
"RESIDENT_NO", "WORKER_ID", "FARE_OFFICE_ID", "BOOTH_ID", "YEAR",
"MONTH", "DAY", "WORK_TIMES", "CANCEL_YN", "CANCEL_DATE",
"CANCEL_REASON", "CREATE_DATE", "CREATER", "UPDATE_DATE", "UPDATER",
"SEQNO", "RENT_KBN"
FROM "NAMSAN"."PENALTY_HISTORY_DETAIL_I_ROOT";
DROP VIEW NAMSAN.PENALTY_HEAD_INFO_ROOT;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE FORCE VIEW namsan.penalty_head_info_root
(
penta__row__id,
car_no,
resident_no,
owner,
resident_date,
owner_type_id,
disuse_date,
car_type_id,
car_type_name,
max_carry_capacity,
panalty_count,
penalty_amount,
receive_amount,
post_no1,
post_no2,
after_cars_number_1,
after_cars_number_2,
address_name,
address_number,
create_date,
creater,
update_date,
updater,
car_status_id,
car_status_date,
carnum_change_date,
after_cars_number_3,
rent_kbn
)
AS
SELECT ROWID,
"CAR_NO",
"RESIDENT_NO",
"OWNER",
"RESIDENT_DATE",
"OWNER_TYPE_ID",
"DISUSE_DATE",
"CAR_TYPE_ID",
"CAR_TYPE_NAME",
"MAX_CARRY_CAPACITY",
"PANALTY_COUNT",
"PENALTY_AMOUNT",
"RECEIVE_AMOUNT",
"POST_NO1",
"POST_NO2",
"AFTER_CARS_NUMBER_1",
"AFTER_CARS_NUMBER_2",
"ADDRESS_NAME",
"ADDRESS_NUMBER",
"CREATE_DATE",
"CREATER",
"UPDATE_DATE",
"UPDATER",
"CAR_STATUS_ID",
"CAR_STATUS_DATE",
"CARNUM_CHANGE_DATE",
"AFTER_CARS_NUMBER_3",
"RENT_KBN"
FROM "NAMSAN"."PENALTY_HEAD_INFO_DAMO";
DROP VIEW NAMSAN.PENALTY_HEAD_INFO;
/* Formatted on 2019/01/28 11:16 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FORCE VIEW namsan.penalty_head_info (car_no,
resident_no,
owner,
resident_date,
owner_type_id,
disuse_date,
car_type_id,
car_type_name,
max_carry_capacity,
panalty_count,
penalty_amount,
receive_amount,
post_no1,
post_no2,
after_cars_number_1,
after_cars_number_2,
address_name,
address_number,
create_date,
creater,
update_date,
updater,
car_status_id,
car_status_date,
carnum_change_date,
after_cars_number_3,
rent_kbn
)
AS
SELECT "CAR_NO", "RESIDENT_NO", "OWNER", "RESIDENT_DATE", "OWNER_TYPE_ID",
"DISUSE_DATE", "CAR_TYPE_ID", "CAR_TYPE_NAME", "MAX_CARRY_CAPACITY",
"PANALTY_COUNT", "PENALTY_AMOUNT", "RECEIVE_AMOUNT", "POST_NO1",
"POST_NO2", "AFTER_CARS_NUMBER_1", "AFTER_CARS_NUMBER_2",
"ADDRESS_NAME", "ADDRESS_NUMBER", "CREATE_DATE", "CREATER",
"UPDATE_DATE", "UPDATER", "CAR_STATUS_ID", "CAR_STATUS_DATE",
"CARNUM_CHANGE_DATE", "AFTER_CARS_NUMBER_3", "RENT_KBN"
FROM "NAMSAN"."PENALTY_HEAD_INFO_ROOT";
DROP VIEW NAMSAN.PENALTY_DETAIL_INFO_ROOT;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE FORCE VIEW namsan.penalty_detail_info_root
(
penta__row__id,
penalty_manage_year,
penalty_seqno,
worker_id,
fare_office_id,
levy_manage_year,
stt_date,
levy_seqno,
booth_id,
collect_id,
YEAR,
MONTH,
DAY,
work_times,
seqno,
car_no,
resident_date,
card_issue_id,
notice_wrong_id,
owner,
notice_id,
resident_no,
fine_amount,
lavy_amount,
motion_id,
notice_date,
notice_wrong_date,
before_motion_id,
pass_amount,
seisure_yn,
seizure_date,
seizure_id,
notice_choose_id,
seizure_wrong_id,
seizure_wrong_date,
seizure_termi_date,
receipt_yn,
double_receipt_yn,
seizure_trmi_yn,
double_receipt_date,
seizure_termi,
seizure_manage_year,
seizure_manage_no,
notice_wrong_reason,
notice_choose_date,
seizure_wrong_reason,
cars_trans_flag,
t_real_time,
mini_car_kbn,
create_date,
creater,
update_date,
updater,
c_public,
c_loss,
rfid_kbn,
car_change_yn,
car_no_after,
rfid_penalty_kbn,
rfid_penalty_reason,
car_no_after_2,
car_no_after_3,
money_change_reason,
money_change_person,
rent_kbn
)
AS
SELECT ROWID,
"PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO",
"WORKER_ID",
"FARE_OFFICE_ID",
"LEVY_MANAGE_YEAR",
"STT_DATE",
"LEVY_SEQNO",
"BOOTH_ID",
"COLLECT_ID",
"YEAR",
"MONTH",
"DAY",
"WORK_TIMES",
"SEQNO",
"CAR_NO",
"RESIDENT_DATE",
"CARD_ISSUE_ID",
"NOTICE_WRONG_ID",
"OWNER",
"NOTICE_ID",
"RESIDENT_NO",
"FINE_AMOUNT",
"LAVY_AMOUNT",
"MOTION_ID",
"NOTICE_DATE",
"NOTICE_WRONG_DATE",
"BEFORE_MOTION_ID",
"PASS_AMOUNT",
"SEISURE_YN",
"SEIZURE_DATE",
"SEIZURE_ID",
"NOTICE_CHOOSE_ID",
"SEIZURE_WRONG_ID",
"SEIZURE_WRONG_DATE",
"SEIZURE_TERMI_DATE",
"RECEIPT_YN",
"DOUBLE_RECEIPT_YN",
"SEIZURE_TRMI_YN",
"DOUBLE_RECEIPT_DATE",
"SEIZURE_TERMI",
"SEIZURE_MANAGE_YEAR",
"SEIZURE_MANAGE_NO",
"NOTICE_WRONG_REASON",
"NOTICE_CHOOSE_DATE",
"SEIZURE_WRONG_REASON",
"CARS_TRANS_FLAG",
"T_REAL_TIME",
"MINI_CAR_KBN",
"CREATE_DATE",
"CREATER",
"UPDATE_DATE",
"UPDATER",
"C_PUBLIC",
"C_LOSS",
"RFID_KBN",
"CAR_CHANGE_YN",
"CAR_NO_AFTER",
"RFID_PENALTY_KBN",
"RFID_PENALTY_REASON",
"CAR_NO_AFTER_2",
"CAR_NO_AFTER_3",
"MONEY_CHANGE_REASON",
"MONEY_CHANGE_PERSON",
"RENT_KBN"
FROM "NAMSAN"."PENALTY_DETAIL_INFO_DAMO";
DROP VIEW NAMSAN.PENALTY_DETAIL_INFO;
/* Formatted on 2019/01/28 11:16 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FORCE VIEW namsan.penalty_detail_info (penalty_manage_year,
penalty_seqno,
worker_id,
fare_office_id,
levy_manage_year,
stt_date,
levy_seqno,
booth_id,
collect_id,
YEAR,
MONTH,
DAY,
work_times,
seqno,
car_no,
resident_date,
card_issue_id,
notice_wrong_id,
owner,
notice_id,
resident_no,
fine_amount,
lavy_amount,
motion_id,
notice_date,
notice_wrong_date,
before_motion_id,
pass_amount,
seisure_yn,
seizure_date,
seizure_id,
notice_choose_id,
seizure_wrong_id,
seizure_wrong_date,
seizure_termi_date,
receipt_yn,
double_receipt_yn,
seizure_trmi_yn,
double_receipt_date,
seizure_termi,
seizure_manage_year,
seizure_manage_no,
notice_wrong_reason,
notice_choose_date,
seizure_wrong_reason,
cars_trans_flag,
t_real_time,
mini_car_kbn,
create_date,
creater,
update_date,
updater,
c_public,
c_loss,
rfid_kbn,
car_change_yn,
car_no_after,
rfid_penalty_kbn,
rfid_penalty_reason,
car_no_after_2,
car_no_after_3,
money_change_reason,
money_change_person,
rent_kbn
)
AS
SELECT "PENALTY_MANAGE_YEAR", "PENALTY_SEQNO", "WORKER_ID",
"FARE_OFFICE_ID", "LEVY_MANAGE_YEAR", "STT_DATE", "LEVY_SEQNO",
"BOOTH_ID", "COLLECT_ID", "YEAR", "MONTH", "DAY", "WORK_TIMES",
"SEQNO", "CAR_NO", "RESIDENT_DATE", "CARD_ISSUE_ID",
"NOTICE_WRONG_ID", "OWNER", "NOTICE_ID", "RESIDENT_NO",
"FINE_AMOUNT", "LAVY_AMOUNT", "MOTION_ID", "NOTICE_DATE",
"NOTICE_WRONG_DATE", "BEFORE_MOTION_ID", "PASS_AMOUNT",
"SEISURE_YN", "SEIZURE_DATE", "SEIZURE_ID", "NOTICE_CHOOSE_ID",
"SEIZURE_WRONG_ID", "SEIZURE_WRONG_DATE", "SEIZURE_TERMI_DATE",
"RECEIPT_YN", "DOUBLE_RECEIPT_YN", "SEIZURE_TRMI_YN",
"DOUBLE_RECEIPT_DATE", "SEIZURE_TERMI", "SEIZURE_MANAGE_YEAR",
"SEIZURE_MANAGE_NO", "NOTICE_WRONG_REASON", "NOTICE_CHOOSE_DATE",
"SEIZURE_WRONG_REASON", "CARS_TRANS_FLAG", "T_REAL_TIME",
"MINI_CAR_KBN", "CREATE_DATE", "CREATER", "UPDATE_DATE", "UPDATER",
"C_PUBLIC", "C_LOSS", "RFID_KBN", "CAR_CHANGE_YN", "CAR_NO_AFTER",
"RFID_PENALTY_KBN", "RFID_PENALTY_REASON", "CAR_NO_AFTER_2",
"CAR_NO_AFTER_3", "MONEY_CHANGE_REASON", "MONEY_CHANGE_PERSON",
"RENT_KBN"
FROM "NAMSAN"."PENALTY_DETAIL_INFO_ROOT";
DROP VIEW NAMSAN.PENALTY_ADDRESS_UPDATE_I_ROOT;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE FORCE VIEW namsan.penalty_address_update_i_root
(
penta__row__id,
penalty_manage_year,
penalty_seqno,
car_no,
owner,
resident_no,
resident_date,
chanage_date,
chanage_seqno,
post_no1,
post_no2,
address_name,
motion_id,
address_number,
create_date,
creater,
update_date,
updater,
car_status_id,
car_status_date,
carnum_change_date
)
AS
SELECT ROWID,
"PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO",
"CAR_NO",
"OWNER",
"RESIDENT_NO",
"RESIDENT_DATE",
"CHANAGE_DATE",
"CHANAGE_SEQNO",
"POST_NO1",
"POST_NO2",
"ADDRESS_NAME",
"MOTION_ID",
"ADDRESS_NUMBER",
"CREATE_DATE",
"CREATER",
"UPDATE_DATE",
"UPDATER",
"CAR_STATUS_ID",
"CAR_STATUS_DATE",
"CARNUM_CHANGE_DATE"
FROM "NAMSAN"."PENALTY_ADDRESS_UPDATE_I_DAMO";
DROP VIEW NAMSAN.PENALTY_ADDRESS_UPDATE_INFO;
/* Formatted on 2019/01/28 11:16 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FORCE VIEW namsan.penalty_address_update_info (penalty_manage_year,
penalty_seqno,
car_no,
owner,
resident_no,
resident_date,
chanage_date,
chanage_seqno,
post_no1,
post_no2,
address_name,
motion_id,
address_number,
create_date,
creater,
update_date,
updater,
car_status_id,
car_status_date,
carnum_change_date
)
AS
SELECT "PENALTY_MANAGE_YEAR", "PENALTY_SEQNO", "CAR_NO", "OWNER",
"RESIDENT_NO", "RESIDENT_DATE", "CHANAGE_DATE", "CHANAGE_SEQNO",
"POST_NO1", "POST_NO2", "ADDRESS_NAME", "MOTION_ID",
"ADDRESS_NUMBER", "CREATE_DATE", "CREATER", "UPDATE_DATE",
"UPDATER", "CAR_STATUS_ID", "CAR_STATUS_DATE", "CARNUM_CHANGE_DATE"
FROM "NAMSAN"."PENALTY_ADDRESS_UPDATE_I_ROOT";
DROP VIEW NAMSAN.OVERFARE_ORDER_INFO_ROOT;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE FORCE VIEW namsan.overfare_order_info_root
(
penta__row__id,
car_no,
resident_no,
owner,
resident_date,
overfare_amount,
refund_amount,
file_kind_id,
data_seqno,
YEAR,
MONTH,
DAY,
post_no1,
post_no2,
address_name,
address_number,
overfare_count,
refund_count,
city_taxes_count,
city_taxes_amont,
trans_file_create_yn,
car_type_name,
create_date,
creater,
update_date,
updater
)
AS
SELECT ROWID,
"CAR_NO",
"RESIDENT_NO",
"OWNER",
"RESIDENT_DATE",
"OVERFARE_AMOUNT",
"REFUND_AMOUNT",
"FILE_KIND_ID",
"DATA_SEQNO",
"YEAR",
"MONTH",
"DAY",
"POST_NO1",
"POST_NO2",
"ADDRESS_NAME",
"ADDRESS_NUMBER",
"OVERFARE_COUNT",
"REFUND_COUNT",
"CITY_TAXES_COUNT",
"CITY_TAXES_AMONT",
"TRANS_FILE_CREATE_YN",
"CAR_TYPE_NAME",
"CREATE_DATE",
"CREATER",
"UPDATE_DATE",
"UPDATER"
FROM "NAMSAN"."OVERFARE_ORDER_INFO_DAMO";
DROP VIEW NAMSAN.OVERFARE_ORDER_INFO;
/* Formatted on 2019/01/28 11:16 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FORCE VIEW namsan.overfare_order_info (car_no,
resident_no,
owner,
resident_date,
overfare_amount,
refund_amount,
file_kind_id,
data_seqno,
YEAR,
MONTH,
DAY,
post_no1,
post_no2,
address_name,
address_number,
overfare_count,
refund_count,
city_taxes_count,
city_taxes_amont,
trans_file_create_yn,
car_type_name,
create_date,
creater,
update_date,
updater
)
AS
SELECT "CAR_NO", "RESIDENT_NO", "OWNER", "RESIDENT_DATE",
"OVERFARE_AMOUNT", "REFUND_AMOUNT", "FILE_KIND_ID", "DATA_SEQNO",
"YEAR", "MONTH", "DAY", "POST_NO1", "POST_NO2", "ADDRESS_NAME",
"ADDRESS_NUMBER", "OVERFARE_COUNT", "REFUND_COUNT",
"CITY_TAXES_COUNT", "CITY_TAXES_AMONT", "TRANS_FILE_CREATE_YN",
"CAR_TYPE_NAME", "CREATE_DATE", "CREATER", "UPDATE_DATE", "UPDATER"
FROM "NAMSAN"."OVERFARE_ORDER_INFO_ROOT";
DROP VIEW NAMSAN.OVERFARE_INFO_ROOT;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE FORCE VIEW namsan.overfare_info_root
(
penta__row__id,
worker_id,
fare_office_id,
booth_id,
YEAR,
MONTH,
DAY,
work_stt_time,
work_end_time,
overfare_seqno,
owner,
file_kind_id,
data_seqno,
trans_year,
trans_month,
trans_day,
overfare_kbn,
resident_date,
car_no,
overfare_amount,
overfare_times,
refund_kbn,
refund_worker_id,
remarks,
refund_fare_office_id,
refund_booth_id,
trans_file_create_yn,
refund_year,
refund_owner,
refund_month,
refund_commumi,
refund_day,
refund_times,
resident_no,
process_kbn,
delete_yn,
create_date,
creater,
update_date,
updater
)
AS
SELECT ROWID,
"WORKER_ID",
"FARE_OFFICE_ID",
"BOOTH_ID",
"YEAR",
"MONTH",
"DAY",
"WORK_STT_TIME",
"WORK_END_TIME",
"OVERFARE_SEQNO",
"OWNER",
"FILE_KIND_ID",
"DATA_SEQNO",
"TRANS_YEAR",
"TRANS_MONTH",
"TRANS_DAY",
"OVERFARE_KBN",
"RESIDENT_DATE",
"CAR_NO",
"OVERFARE_AMOUNT",
"OVERFARE_TIMES",
"REFUND_KBN",
"REFUND_WORKER_ID",
"REMARKS",
"REFUND_FARE_OFFICE_ID",
"REFUND_BOOTH_ID",
"TRANS_FILE_CREATE_YN",
"REFUND_YEAR",
"REFUND_OWNER",
"REFUND_MONTH",
"REFUND_COMMUMI",
"REFUND_DAY",
"REFUND_TIMES",
"RESIDENT_NO",
"PROCESS_KBN",
"DELETE_YN",
"CREATE_DATE",
"CREATER",
"UPDATE_DATE",
"UPDATER"
FROM "NAMSAN"."OVERFARE_INFO_DAMO";
DROP VIEW NAMSAN.OVERFARE_INFO;
/* Formatted on 2019/01/28 11:16 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FORCE VIEW namsan.overfare_info (worker_id,
fare_office_id,
booth_id,
YEAR,
MONTH,
DAY,
work_stt_time,
work_end_time,
overfare_seqno,
owner,
file_kind_id,
data_seqno,
trans_year,
trans_month,
trans_day,
overfare_kbn,
resident_date,
car_no,
overfare_amount,
overfare_times,
refund_kbn,
refund_worker_id,
remarks,
refund_fare_office_id,
refund_booth_id,
trans_file_create_yn,
refund_year,
refund_owner,
refund_month,
refund_commumi,
refund_day,
refund_times,
resident_no,
process_kbn,
delete_yn,
create_date,
creater,
update_date,
updater
)
AS
SELECT "WORKER_ID", "FARE_OFFICE_ID", "BOOTH_ID", "YEAR", "MONTH", "DAY",
"WORK_STT_TIME", "WORK_END_TIME", "OVERFARE_SEQNO", "OWNER",
"FILE_KIND_ID", "DATA_SEQNO", "TRANS_YEAR", "TRANS_MONTH",
"TRANS_DAY", "OVERFARE_KBN", "RESIDENT_DATE", "CAR_NO",
"OVERFARE_AMOUNT", "OVERFARE_TIMES", "REFUND_KBN",
"REFUND_WORKER_ID", "REMARKS", "REFUND_FARE_OFFICE_ID",
"REFUND_BOOTH_ID", "TRANS_FILE_CREATE_YN", "REFUND_YEAR",
"REFUND_OWNER", "REFUND_MONTH", "REFUND_COMMUMI", "REFUND_DAY",
"REFUND_TIMES", "RESIDENT_NO", "PROCESS_KBN", "DELETE_YN",
"CREATE_DATE", "CREATER", "UPDATE_DATE", "UPDATER"
FROM "NAMSAN"."OVERFARE_INFO_ROOT";
DROP VIEW NAMSAN.LEVY_TARGET_INFO_ROOT;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE FORCE VIEW namsan.levy_target_info_root
(
penta__row__id,
levy_manage_year,
levy_seqno,
worker_id,
fare_office_id,
booth_id,
YEAR,
MONTH,
DAY,
work_times,
seqno,
car_no,
owner,
resident_no,
motion_id,
except_date,
notice_date,
notice_kbn,
resident_date,
penalty_process_yn,
create_date,
creater,
update_date,
updater
)
AS
SELECT ROWID,
"LEVY_MANAGE_YEAR",
"LEVY_SEQNO",
"WORKER_ID",
"FARE_OFFICE_ID",
"BOOTH_ID",
"YEAR",
"MONTH",
"DAY",
"WORK_TIMES",
"SEQNO",
"CAR_NO",
"OWNER",
"RESIDENT_NO",
"MOTION_ID",
"EXCEPT_DATE",
"NOTICE_DATE",
"NOTICE_KBN",
"RESIDENT_DATE",
"PENALTY_PROCESS_YN",
"CREATE_DATE",
"CREATER",
"UPDATE_DATE",
"UPDATER"
FROM "NAMSAN"."LEVY_TARGET_INFO_DAMO";
DROP VIEW NAMSAN.LEVY_TARGET_INFO;
/* Formatted on 2019/01/28 11:16 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FORCE VIEW namsan.levy_target_info (levy_manage_year,
levy_seqno,
worker_id,
fare_office_id,
booth_id,
YEAR,
MONTH,
DAY,
work_times,
seqno,
car_no,
owner,
resident_no,
motion_id,
except_date,
notice_date,
notice_kbn,
resident_date,
penalty_process_yn,
create_date,
creater,
update_date,
updater
)
AS
SELECT "LEVY_MANAGE_YEAR", "LEVY_SEQNO", "WORKER_ID", "FARE_OFFICE_ID",
"BOOTH_ID", "YEAR", "MONTH", "DAY", "WORK_TIMES", "SEQNO", "CAR_NO",
"OWNER", "RESIDENT_NO", "MOTION_ID", "EXCEPT_DATE", "NOTICE_DATE",
"NOTICE_KBN", "RESIDENT_DATE", "PENALTY_PROCESS_YN", "CREATE_DATE",
"CREATER", "UPDATE_DATE", "UPDATER"
FROM "NAMSAN"."LEVY_TARGET_INFO_ROOT";
DROP VIEW NAMSAN.HONJOB_PASS_VIEW;
/* Formatted on 2019/04/04 오후 3:24:17 (QP5 v5.215.12089.38647) */
CREATE OR REPLACE FORCE VIEW namsan.honjob_pass_view
(
carnumber,
passdate,
passtime,
deptname,
roadnm,
workerid,
progressid,
progresstype,
rfid,
reason,
TYPE
)
AS
SELECT a.car_no AS carnumber,
a.YEAR || '-' || a.MONTH || '-' || a.DAY AS "passdate",
-- A.YEAR,A.MONTH,A.DAY,
SUBSTR (a.work_times, 1, 2)
|| ':'
|| SUBSTR (a.work_times, 3, 2)
|| ':'
|| SUBSTR (a.work_times, 5, 2)
AS "passtime",
'남산' || a.fare_office_id || '호터널' AS "deptname",
'제' || a.booth_id || '차로' AS "roadnm",
w.worker_name AS "workerid", --A.WORKER_ID,
a.collect_id AS "progressid",
c.collect_full_name AS "progresstype", --A.COLLECT_ID,
-- NVL(B.C_REPAY_KBN,'0') ,
--A.RFID_KBN
CASE
WHEN a.rfid_kbn = '01' THEN '요일제감면'
WHEN a.rfid_kbn = '02' THEN '저공해면제'
WHEN a.rfid_kbn = '03' THEN '저공해화감면'
WHEN a.rfid_kbn = '04' THEN '요일제/저공해화감면'
WHEN a.rfid_kbn = '99' THEN '요일제태그/일반키'
ELSE ' '
END
AS "RFID",
(SELECT (CASE
WHEN exempt_kbn = '0' THEN '일반'
WHEN exempt_kbn = '1' THEN '장애인'
WHEN exempt_kbn = '2' THEN '저공해면제'
ELSE '일반2'
END)
|| '-'
|| position_body
|| '-'
|| document_no
|| '-'
|| owner
FROM exempt_cars_info
WHERE car_no = a.car_no AND ROWNUM = 1)
AS "reason",
CASE
WHEN a.car_no LIKE '%7%바%' THEN '버스'
WHEN a.car_no LIKE '%7%사%' THEN '버스'
WHEN a.car_no LIKE '%7%아%' THEN '버스'
WHEN a.car_no LIKE '%7%자%' THEN '버스'
WHEN a.car_no LIKE '7%' THEN '승합'
WHEN a.car_no LIKE '8%' THEN '화물'
WHEN a.car_no LIKE '98%' THEN '특수'
WHEN a.car_no LIKE '99%' THEN '특수'
WHEN a.car_no LIKE '9%' THEN '화물'
WHEN a.car_no LIKE '서울7%' THEN '승합'
WHEN a.car_no LIKE '서울8%' THEN '화물'
WHEN a.car_no LIKE '서울98%' THEN '특수'
WHEN a.car_no LIKE '서울99%' THEN '특수'
WHEN a.car_no LIKE '서울9%' THEN '화물'
WHEN a.car_no LIKE '경기7%' THEN '승합'
WHEN a.car_no LIKE '경기8%' THEN '화물'
WHEN a.car_no LIKE '경기98%' THEN '특수'
WHEN a.car_no LIKE '경기99%' THEN '특수'
WHEN a.car_no LIKE '경기9%' THEN '화물'
WHEN a.car_no LIKE '강원7%' THEN '승합'
WHEN a.car_no LIKE '강원8%' THEN '화물'
WHEN a.car_no LIKE '강원98%' THEN '특수'
WHEN a.car_no LIKE '강원99%' THEN '특수'
WHEN a.car_no LIKE '강원9%' THEN '화물'
WHEN a.car_no LIKE '전남7%' THEN '승합'
WHEN a.car_no LIKE '전남8%' THEN '화물'
WHEN a.car_no LIKE '전남98%' THEN '특수'
WHEN a.car_no LIKE '전남99%' THEN '특수'
WHEN a.car_no LIKE '전남9%' THEN '화물'
WHEN a.car_no LIKE '충남7%' THEN '승합'
WHEN a.car_no LIKE '충남8%' THEN '화물'
WHEN a.car_no LIKE '충남98%' THEN '특수'
WHEN a.car_no LIKE '충남99%' THEN '특수'
WHEN a.car_no LIKE '충남9%' THEN '화물'
WHEN a.car_no LIKE '경남7%' THEN '승합'
WHEN a.car_no LIKE '경남8%' THEN '화물'
WHEN a.car_no LIKE '경남98%' THEN '특수'
WHEN a.car_no LIKE '경남99%' THEN '특수'
WHEN a.car_no LIKE '경남9%' THEN '화물'
WHEN a.car_no LIKE '경북7%' THEN '승합'
WHEN a.car_no LIKE '경북8%' THEN '화물'
WHEN a.car_no LIKE '경북98%' THEN '특수'
WHEN a.car_no LIKE '경북99%' THEN '특수'
WHEN a.car_no LIKE '경북9%' THEN '화물'
WHEN a.car_no LIKE '전북7%' THEN '승합'
WHEN a.car_no LIKE '전북8%' THEN '화물'
WHEN a.car_no LIKE '전북98%' THEN '특수'
WHEN a.car_no LIKE '전북99%' THEN '특수'
WHEN a.car_no LIKE '전북9%' THEN '화물'
WHEN a.car_no LIKE '충북7%' THEN '승합'
WHEN a.car_no LIKE '충북8%' THEN '화물'
WHEN a.car_no LIKE '충북98%' THEN '특수'
WHEN a.car_no LIKE '충북99%' THEN '특수'
WHEN a.car_no LIKE '충북9%' THEN '화물'
WHEN a.car_no LIKE '%31바%' THEN '개인택시'
WHEN a.car_no LIKE '%31사%' THEN '개인택시'
WHEN a.car_no LIKE '%31아%' THEN '개인택시'
WHEN a.car_no LIKE '%31자%' THEN '개인택시'
WHEN a.car_no LIKE '%31배%' THEN '개인택시'
WHEN a.car_no LIKE '%32바%' THEN '개인택시'
WHEN a.car_no LIKE '%32사%' THEN '개인택시'
WHEN a.car_no LIKE '%32아%' THEN '개인택시'
WHEN a.car_no LIKE '%32자%' THEN '개인택시'
WHEN a.car_no LIKE '%32배%' THEN '개인택시'
WHEN a.car_no LIKE '%35바%' THEN '개인택시'
WHEN a.car_no LIKE '%35사%' THEN '개인택시'
WHEN a.car_no LIKE '%35아%' THEN '개인택시'
WHEN a.car_no LIKE '%35자%' THEN '개인택시'
WHEN a.car_no LIKE '%35배%' THEN '개인택시'
WHEN a.car_no LIKE '%33바%' THEN '법인택시'
WHEN a.car_no LIKE '%33사%' THEN '법인택시'
WHEN a.car_no LIKE '%33아%' THEN '법인택시'
WHEN a.car_no LIKE '%33자%' THEN '법인택시'
WHEN a.car_no LIKE '%33배%' THEN '법인택시'
WHEN a.car_no LIKE '%34바%' THEN '법인택시'
WHEN a.car_no LIKE '%34사%' THEN '법인택시'
WHEN a.car_no LIKE '%34아%' THEN '법인택시'
WHEN a.car_no LIKE '%34자%' THEN '법인택시'
WHEN a.car_no LIKE '%34배%' THEN '법인택시'
WHEN a.car_no LIKE '%37바%' THEN '법인택시'
WHEN a.car_no LIKE '%37사%' THEN '법인택시'
WHEN a.car_no LIKE '%37아%' THEN '법인택시'
WHEN a.car_no LIKE '%37자%' THEN '법인택시'
WHEN a.car_no LIKE '%37배%' THEN '법인택시'
ELSE '일반'
END
AS "type"
FROM fare_terminal_info a,
rf_repay_info b,
worker_info w,
collect_code_info c
WHERE a.worker_id = b.worker_id(+)
AND a.worker_id = w.worker_id
AND a.collect_id = c.collect_id
AND a.fare_office_id = b.fare_office_id(+)
AND a.booth_id = b.booth_id(+)
AND a.YEAR = b.YEAR(+)
AND a.MONTH = b.MONTH(+)
AND a.DAY = b.DAY(+)
AND a.work_times = b.work_times(+)
AND a.seqno = b.seqno(+)
AND a.YEAR = '2014' --:as_year
AND a.MONTH BETWEEN '01' AND '08' --:as_month
--AND A.DAY between '01' and '10'
AND a.delete_yn = '0'
AND a.collect_id IN ('11', '23', '24', '30', '53');
DROP VIEW NAMSAN.DEFAULT_FARE_READ_INFO_ROOT;
/*
damo 사용 하도록 변경
*/
CREATE OR REPLACE FORCE VIEW namsan.default_fare_read_info_root
(
penta__row__id,
worker_id,
fare_office_id,
booth_id,
YEAR,
MONTH,
DAY,
work_times,
seqno,
read_result_id,
resident_date,
collect_id,
owner,
resident_no,
reading_reason,
cars_data_kbn,
car_type_name,
reading_id,
cars_move_picture_file,
read_kbn,
cars_stop_picture_file,
car_no,
reader,
exempt_reason_id,
reading_date,
lavy_notice_id,
notconfirm_id,
remarks,
x_position,
y_position,
garo_len,
sero_len,
cars_no_picture_file,
stop_check,
move_check,
t_real_time,
mini_car_kbn,
create_date,
creater,
update_date,
updater,
re_certification,
c_hard_read,
rfid_yn,
rfid_kbn,
rfid_tag1,
rfid_tag2,
rfid_penalty_kbn,
rfid_penalty_reason,
rfid_car,
rfid_week,
rfid_damage,
rfid_kbn1,
rfid_car1,
rfid_week1,
rfid_damage1,
SEND_FLAG
)
AS
SELECT ROWID,
"WORKER_ID",
"FARE_OFFICE_ID",
"BOOTH_ID",
"YEAR",
"MONTH",
"DAY",
"WORK_TIMES",
"SEQNO",
"READ_RESULT_ID",
"RESIDENT_DATE",
"COLLECT_ID",
"OWNER",
"RESIDENT_NO",
"READING_REASON",
"CARS_DATA_KBN",
"CAR_TYPE_NAME",
"READING_ID",
"CARS_MOVE_PICTURE_FILE",
"READ_KBN",
"CARS_STOP_PICTURE_FILE",
"CAR_NO",
"READER",
"EXEMPT_REASON_ID",
"READING_DATE",
"LAVY_NOTICE_ID",
"NOTCONFIRM_ID",
"REMARKS",
"X_POSITION",
"Y_POSITION",
"GARO_LEN",
"SERO_LEN",
"CARS_NO_PICTURE_FILE",
"STOP_CHECK",
"MOVE_CHECK",
"T_REAL_TIME",
"MINI_CAR_KBN",
"CREATE_DATE",
"CREATER",
"UPDATE_DATE",
"UPDATER",
"RE_CERTIFICATION",
"C_HARD_READ",
"RFID_YN",
"RFID_KBN",
"RFID_TAG1",
"RFID_TAG2",
"RFID_PENALTY_KBN",
"RFID_PENALTY_REASON",
"RFID_CAR",
"RFID_WEEK",
"RFID_DAMAGE",
"RFID_KBN1",
"RFID_CAR1",
"RFID_WEEK1",
"RFID_DAMAGE1",
"SEND_FLAG"
FROM "NAMSAN"."DEFAULT_FARE_READ_INFO_DAMO";
DROP VIEW NAMSAN.DEFAULT_FARE_READ_INFO;
/* Formatted on 2019/01/28 11:16 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FORCE VIEW namsan.default_fare_read_info (worker_id,
fare_office_id,
booth_id,
YEAR,
MONTH,
DAY,
work_times,
seqno,
read_result_id,
resident_date,
collect_id,
owner,
resident_no,
reading_reason,
cars_data_kbn,
car_type_name,
reading_id,
cars_move_picture_file,
read_kbn,
cars_stop_picture_file,
car_no,
reader,
exempt_reason_id,
reading_date,
lavy_notice_id,
notconfirm_id,
remarks,
x_position,
y_position,
garo_len,
sero_len,
cars_no_picture_file,
stop_check,
move_check,
t_real_time,
mini_car_kbn,
create_date,
creater,
update_date,
updater,
re_certification,
c_hard_read,
rfid_yn,
rfid_kbn,
rfid_tag1,
rfid_tag2,
rfid_penalty_kbn,
rfid_penalty_reason,
rfid_car,
rfid_week,
rfid_damage,
rfid_kbn1,
rfid_car1,
rfid_week1,
rfid_damage1,
SEND_FLAG
)
AS
SELECT "WORKER_ID", "FARE_OFFICE_ID", "BOOTH_ID", "YEAR", "MONTH", "DAY",
"WORK_TIMES", "SEQNO", "READ_RESULT_ID", "RESIDENT_DATE",
"COLLECT_ID", "OWNER", "RESIDENT_NO", "READING_REASON",
"CARS_DATA_KBN", "CAR_TYPE_NAME", "READING_ID",
"CARS_MOVE_PICTURE_FILE", "READ_KBN", "CARS_STOP_PICTURE_FILE",
"CAR_NO", "READER", "EXEMPT_REASON_ID", "READING_DATE",
"LAVY_NOTICE_ID", "NOTCONFIRM_ID", "REMARKS", "X_POSITION",
"Y_POSITION", "GARO_LEN", "SERO_LEN", "CARS_NO_PICTURE_FILE",
"STOP_CHECK", "MOVE_CHECK", "T_REAL_TIME", "MINI_CAR_KBN",
"CREATE_DATE", "CREATER", "UPDATE_DATE", "UPDATER",
"RE_CERTIFICATION", "C_HARD_READ", "RFID_YN", "RFID_KBN",
"RFID_TAG1", "RFID_TAG2", "RFID_PENALTY_KBN", "RFID_PENALTY_REASON",
"RFID_CAR", "RFID_WEEK", "RFID_DAMAGE", "RFID_KBN1", "RFID_CAR1",
"RFID_WEEK1", "RFID_DAMAGE1", "SEND_FLAG"
FROM "NAMSAN"."DEFAULT_FARE_READ_INFO_ROOT";
DROP VIEW NAMSAN.DEFAULT_CARS_TRANS_INFO_ROOT;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE FORCE VIEW namsan.default_cars_trans_info_root
(
penta__row__id,
worker_id,
fare_office_id,
booth_id,
YEAR,
MONTH,
DAY,
work_times,
seqno,
notice_kbn,
car_no,
resident_date,
owner,
resident_no,
motion_id,
cars_trans_date,
cars_trans_person,
cars_insert_date,
cars_insert_person,
cars_trans_verify_date,
cars_trans_verify_person,
confirm_yn,
collect_id,
verificate_yn,
car_type_name,
address_name,
address_number,
t_real_time,
mini_car_kbn,
c_error,
create_date,
creater,
update_date,
updater,
car_change_yn,
car_no_after,
rfid_penalty_kbn,
car_no_after_2,
car_no_after_3
)
AS
SELECT ROWID,
"WORKER_ID",
"FARE_OFFICE_ID",
"BOOTH_ID",
"YEAR",
"MONTH",
"DAY",
"WORK_TIMES",
"SEQNO",
"NOTICE_KBN",
"CAR_NO",
"RESIDENT_DATE",
"OWNER",
"RESIDENT_NO",
"MOTION_ID",
"CARS_TRANS_DATE",
"CARS_TRANS_PERSON",
"CARS_INSERT_DATE",
"CARS_INSERT_PERSON",
"CARS_TRANS_VERIFY_DATE",
"CARS_TRANS_VERIFY_PERSON",
"CONFIRM_YN",
"COLLECT_ID",
"VERIFICATE_YN",
"CAR_TYPE_NAME",
"ADDRESS_NAME",
"ADDRESS_NUMBER",
"T_REAL_TIME",
"MINI_CAR_KBN",
"C_ERROR",
"CREATE_DATE",
"CREATER",
"UPDATE_DATE",
"UPDATER",
"CAR_CHANGE_YN",
"CAR_NO_AFTER",
"RFID_PENALTY_KBN",
"CAR_NO_AFTER_2",
"CAR_NO_AFTER_3"
FROM "NAMSAN"."DEFAULT_CARS_TRANS_INFO_DAMO";
DROP VIEW NAMSAN.DEFAULT_CARS_TRANS_INFO;
/* Formatted on 2019/01/28 11:16 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FORCE VIEW namsan.default_cars_trans_info (worker_id,
fare_office_id,
booth_id,
YEAR,
MONTH,
DAY,
work_times,
seqno,
notice_kbn,
car_no,
resident_date,
owner,
resident_no,
motion_id,
cars_trans_date,
cars_trans_person,
cars_insert_date,
cars_insert_person,
cars_trans_verify_date,
cars_trans_verify_person,
confirm_yn,
collect_id,
verificate_yn,
car_type_name,
address_name,
address_number,
t_real_time,
mini_car_kbn,
c_error,
create_date,
creater,
update_date,
updater,
car_change_yn,
car_no_after,
rfid_penalty_kbn,
car_no_after_2,
car_no_after_3
)
AS
SELECT "WORKER_ID", "FARE_OFFICE_ID", "BOOTH_ID", "YEAR", "MONTH", "DAY",
"WORK_TIMES", "SEQNO", "NOTICE_KBN", "CAR_NO", "RESIDENT_DATE",
"OWNER", "RESIDENT_NO", "MOTION_ID", "CARS_TRANS_DATE",
"CARS_TRANS_PERSON", "CARS_INSERT_DATE", "CARS_INSERT_PERSON",
"CARS_TRANS_VERIFY_DATE", "CARS_TRANS_VERIFY_PERSON", "CONFIRM_YN",
"COLLECT_ID", "VERIFICATE_YN", "CAR_TYPE_NAME", "ADDRESS_NAME",
"ADDRESS_NUMBER", "T_REAL_TIME", "MINI_CAR_KBN", "C_ERROR",
"CREATE_DATE", "CREATER", "UPDATE_DATE", "UPDATER", "CAR_CHANGE_YN",
"CAR_NO_AFTER", "RFID_PENALTY_KBN", "CAR_NO_AFTER_2",
"CAR_NO_AFTER_3"
FROM "NAMSAN"."DEFAULT_CARS_TRANS_INFO_ROOT";
DROP VIEW NAMSAN.CIVIL_ACCEPT_INFO_ROOT;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE FORCE VIEW namsan.civil_accept_info_root
(
penta__row__id,
penalty_manage_year,
penalty_seqno,
accept_date,
accept_seqno,
civil_kind,
civil_summary_id,
civil_person,
civil_process_id,
civil_tel_no,
civil_accepter,
civil_via,
civil_summary_contents,
remarks,
process_yn,
process_delivery,
civil_pcs_no,
civil_join_kbn,
civil_fax_no,
process_date,
civil_proces_contents,
civil_processer,
creater,
create_date,
update_date,
updater
)
AS
SELECT ROWID,
"PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO",
"ACCEPT_DATE",
"ACCEPT_SEQNO",
"CIVIL_KIND",
"CIVIL_SUMMARY_ID",
"CIVIL_PERSON",
"CIVIL_PROCESS_ID",
"CIVIL_TEL_NO",
"CIVIL_ACCEPTER",
"CIVIL_VIA",
"CIVIL_SUMMARY_CONTENTS",
"REMARKS",
"PROCESS_YN",
"PROCESS_DELIVERY",
"CIVIL_PCS_NO",
"CIVIL_JOIN_KBN",
"CIVIL_FAX_NO",
"PROCESS_DATE",
"CIVIL_PROCES_CONTENTS",
"CIVIL_PROCESSER",
"CREATER",
"CREATE_DATE",
"UPDATE_DATE",
"UPDATER"
FROM "NAMSAN"."CIVIL_ACCEPT_INFO_DAMO";
DROP VIEW NAMSAN.CIVIL_ACCEPT_INFO;
/* Formatted on 2019/01/28 11:16 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FORCE VIEW namsan.civil_accept_info (penalty_manage_year,
penalty_seqno,
accept_date,
accept_seqno,
civil_kind,
civil_summary_id,
civil_person,
civil_process_id,
civil_tel_no,
civil_accepter,
civil_via,
civil_summary_contents,
remarks,
process_yn,
process_delivery,
civil_pcs_no,
civil_join_kbn,
civil_fax_no,
process_date,
civil_proces_contents,
civil_processer,
creater,
create_date,
update_date,
updater
)
AS
SELECT "PENALTY_MANAGE_YEAR", "PENALTY_SEQNO", "ACCEPT_DATE",
"ACCEPT_SEQNO", "CIVIL_KIND", "CIVIL_SUMMARY_ID", "CIVIL_PERSON",
"CIVIL_PROCESS_ID", "CIVIL_TEL_NO", "CIVIL_ACCEPTER", "CIVIL_VIA",
"CIVIL_SUMMARY_CONTENTS", "REMARKS", "PROCESS_YN",
"PROCESS_DELIVERY", "CIVIL_PCS_NO", "CIVIL_JOIN_KBN",
"CIVIL_FAX_NO", "PROCESS_DATE", "CIVIL_PROCES_CONTENTS",
"CIVIL_PROCESSER", "CREATER", "CREATE_DATE", "UPDATE_DATE",
"UPDATER"
FROM "NAMSAN"."CIVIL_ACCEPT_INFO_ROOT";
DROP TRIGGER NAMSAN.SUT_PENALTY_ADDRESS_UPDATE_IN;
/*
PENALTY_ADDRESS_UPDATE_I_ROOT VIEW 에 값이 UPDATE 될 때
PENALTY_ADDRESS_UPDATE_I_DAMO TABLE 의 값을 UPDATE 한다.
damo 사용 안하도록 변경
*/
CREATE OR REPLACE TRIGGER NAMSAN."SUT_PENALTY_ADDRESS_UPDATE_IN"
INSTEAD OF UPDATE
ON "NAMSAN"."PENALTY_ADDRESS_UPDATE_I_ROOT"
FOR EACH ROW
DECLARE
V_LENGTH1 NUMBER;
V_LENGTH2 NUMBER;
V_LENGTH3 NUMBER;
V_LENGTH4 NUMBER;
V_5 VARCHAR2 (13);
V_RAW_5 RAW (2000);
V_OLD_5 VARCHAR2 (13);
V_UPT_5 VARCHAR2 (1);
V_11 VARCHAR2 (60);
V_RAW_11 RAW (2000);
V_OLD_11 VARCHAR2 (60);
V_UPT_11 VARCHAR2 (1);
V_13 VARCHAR2 (80);
V_RAW_13 RAW (2000);
V_OLD_13 VARCHAR2 (80);
V_UPT_13 VARCHAR2 (1);
BEGIN
V_UPT_5 := 'N';
V_OLD_5 := :OLD."RESIDENT_NO";
IF V_OLD_5 IS NULL AND :NEW."RESIDENT_NO" IS NOT NULL
THEN
V_UPT_5 := 'Y';
ELSIF V_OLD_5 IS NOT NULL
THEN
IF V_OLD_5 != :NEW."RESIDENT_NO" OR :NEW."RESIDENT_NO" IS NULL
THEN
V_UPT_5 := 'Y';
END IF;
END IF;
V_UPT_11 := 'N';
V_OLD_11 := :OLD."ADDRESS_NAME";
IF V_OLD_11 IS NULL AND :NEW."ADDRESS_NAME" IS NOT NULL
THEN
V_UPT_11 := 'Y';
ELSIF V_OLD_11 IS NOT NULL
THEN
IF V_OLD_11 != :NEW."ADDRESS_NAME" OR :NEW."ADDRESS_NAME" IS NULL
THEN
V_UPT_11 := 'Y';
END IF;
END IF;
V_UPT_13 := 'N';
V_OLD_13 := :OLD."ADDRESS_NUMBER";
IF V_OLD_13 IS NULL AND :NEW."ADDRESS_NUMBER" IS NOT NULL
THEN
V_UPT_13 := 'Y';
ELSIF V_OLD_13 IS NOT NULL
THEN
IF V_OLD_13 != :NEW."ADDRESS_NUMBER" OR :NEW."ADDRESS_NUMBER" IS NULL
THEN
V_UPT_13 := 'Y';
END IF;
END IF;
IF V_UPT_5 = 'N' AND V_UPT_11 = 'N' AND V_UPT_13 = 'N'
THEN
UPDATE "NAMSAN"."PENALTY_ADDRESS_UPDATE_I_DAMO"
SET "PENALTY_MANAGE_YEAR" = :NEW."PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO" = :NEW."PENALTY_SEQNO",
"CAR_NO" = :NEW."CAR_NO",
"OWNER" = :NEW."OWNER",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"CHANAGE_DATE" = :NEW."CHANAGE_DATE",
"CHANAGE_SEQNO" = :NEW."CHANAGE_SEQNO",
"POST_NO1" = :NEW."POST_NO1",
"POST_NO2" = :NEW."POST_NO2",
"MOTION_ID" = :NEW."MOTION_ID",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"CAR_STATUS_ID" = :NEW."CAR_STATUS_ID",
"CAR_STATUS_DATE" = :NEW."CAR_STATUS_DATE",
"CARNUM_CHANGE_DATE" = :NEW."CARNUM_CHANGE_DATE"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_5 = 'N' AND V_UPT_11 = 'N' AND V_UPT_13 = 'Y'
THEN
UPDATE "NAMSAN"."PENALTY_ADDRESS_UPDATE_I_DAMO"
SET "PENALTY_MANAGE_YEAR" = :NEW."PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO" = :NEW."PENALTY_SEQNO",
"CAR_NO" = :NEW."CAR_NO",
"OWNER" = :NEW."OWNER",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"CHANAGE_DATE" = :NEW."CHANAGE_DATE",
"CHANAGE_SEQNO" = :NEW."CHANAGE_SEQNO",
"POST_NO1" = :NEW."POST_NO1",
"POST_NO2" = :NEW."POST_NO2",
"MOTION_ID" = :NEW."MOTION_ID",
"ADDRESS_NUMBER" = :NEW."ADDRESS_NUMBER",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"CAR_STATUS_ID" = :NEW."CAR_STATUS_ID",
"CAR_STATUS_DATE" = :NEW."CAR_STATUS_DATE",
"CARNUM_CHANGE_DATE" = :NEW."CARNUM_CHANGE_DATE"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_5 = 'N' AND V_UPT_11 = 'Y' AND V_UPT_13 = 'N'
THEN
UPDATE "NAMSAN"."PENALTY_ADDRESS_UPDATE_I_DAMO"
SET "PENALTY_MANAGE_YEAR" = :NEW."PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO" = :NEW."PENALTY_SEQNO",
"CAR_NO" = :NEW."CAR_NO",
"OWNER" = :NEW."OWNER",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"CHANAGE_DATE" = :NEW."CHANAGE_DATE",
"CHANAGE_SEQNO" = :NEW."CHANAGE_SEQNO",
"POST_NO1" = :NEW."POST_NO1",
"POST_NO2" = :NEW."POST_NO2",
"ADDRESS_NAME" = :NEW."ADDRESS_NAME",
"MOTION_ID" = :NEW."MOTION_ID",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"CAR_STATUS_ID" = :NEW."CAR_STATUS_ID",
"CAR_STATUS_DATE" = :NEW."CAR_STATUS_DATE",
"CARNUM_CHANGE_DATE" = :NEW."CARNUM_CHANGE_DATE"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_5 = 'N' AND V_UPT_11 = 'Y' AND V_UPT_13 = 'Y'
THEN
UPDATE "NAMSAN"."PENALTY_ADDRESS_UPDATE_I_DAMO"
SET "PENALTY_MANAGE_YEAR" = :NEW."PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO" = :NEW."PENALTY_SEQNO",
"CAR_NO" = :NEW."CAR_NO",
"OWNER" = :NEW."OWNER",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"CHANAGE_DATE" = :NEW."CHANAGE_DATE",
"CHANAGE_SEQNO" = :NEW."CHANAGE_SEQNO",
"POST_NO1" = :NEW."POST_NO1",
"POST_NO2" = :NEW."POST_NO2",
"ADDRESS_NAME" = :NEW."ADDRESS_NAME",
"MOTION_ID" = :NEW."MOTION_ID",
"ADDRESS_NUMBER" = :NEW."ADDRESS_NUMBER",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"CAR_STATUS_ID" = :NEW."CAR_STATUS_ID",
"CAR_STATUS_DATE" = :NEW."CAR_STATUS_DATE",
"CARNUM_CHANGE_DATE" = :NEW."CARNUM_CHANGE_DATE"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_5 = 'Y' AND V_UPT_11 = 'N' AND V_UPT_13 = 'N'
THEN
UPDATE "NAMSAN"."PENALTY_ADDRESS_UPDATE_I_DAMO"
SET "PENALTY_MANAGE_YEAR" = :NEW."PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO" = :NEW."PENALTY_SEQNO",
"CAR_NO" = :NEW."CAR_NO",
"OWNER" = :NEW."OWNER",
"RESIDENT_NO" = :NEW."RESIDENT_NO",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"CHANAGE_DATE" = :NEW."CHANAGE_DATE",
"CHANAGE_SEQNO" = :NEW."CHANAGE_SEQNO",
"POST_NO1" = :NEW."POST_NO1",
"POST_NO2" = :NEW."POST_NO2",
"MOTION_ID" = :NEW."MOTION_ID",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"CAR_STATUS_ID" = :NEW."CAR_STATUS_ID",
"CAR_STATUS_DATE" = :NEW."CAR_STATUS_DATE",
"CARNUM_CHANGE_DATE" = :NEW."CARNUM_CHANGE_DATE"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_5 = 'Y' AND V_UPT_11 = 'N' AND V_UPT_13 = 'Y'
THEN
UPDATE "NAMSAN"."PENALTY_ADDRESS_UPDATE_I_DAMO"
SET "PENALTY_MANAGE_YEAR" = :NEW."PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO" = :NEW."PENALTY_SEQNO",
"CAR_NO" = :NEW."CAR_NO",
"OWNER" = :NEW."OWNER",
"RESIDENT_NO" = :NEW."RESIDENT_NO",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"CHANAGE_DATE" = :NEW."CHANAGE_DATE",
"CHANAGE_SEQNO" = :NEW."CHANAGE_SEQNO",
"POST_NO1" = :NEW."POST_NO1",
"POST_NO2" = :NEW."POST_NO2",
"MOTION_ID" = :NEW."MOTION_ID",
"ADDRESS_NUMBER" = :NEW."ADDRESS_NUMBER",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"CAR_STATUS_ID" = :NEW."CAR_STATUS_ID",
"CAR_STATUS_DATE" = :NEW."CAR_STATUS_DATE",
"CARNUM_CHANGE_DATE" = :NEW."CARNUM_CHANGE_DATE"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_5 = 'Y' AND V_UPT_11 = 'Y' AND V_UPT_13 = 'N'
THEN
UPDATE "NAMSAN"."PENALTY_ADDRESS_UPDATE_I_DAMO"
SET "PENALTY_MANAGE_YEAR" = :NEW."PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO" = :NEW."PENALTY_SEQNO",
"CAR_NO" = :NEW."CAR_NO",
"OWNER" = :NEW."OWNER",
"RESIDENT_NO" = :NEW."RESIDENT_NO",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"CHANAGE_DATE" = :NEW."CHANAGE_DATE",
"CHANAGE_SEQNO" = :NEW."CHANAGE_SEQNO",
"POST_NO1" = :NEW."POST_NO1",
"POST_NO2" = :NEW."POST_NO2",
"ADDRESS_NAME" = :NEW."ADDRESS_NAME",
"MOTION_ID" = :NEW."MOTION_ID",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"CAR_STATUS_ID" = :NEW."CAR_STATUS_ID",
"CAR_STATUS_DATE" = :NEW."CAR_STATUS_DATE",
"CARNUM_CHANGE_DATE" = :NEW."CARNUM_CHANGE_DATE"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_5 = 'Y' AND V_UPT_11 = 'Y' AND V_UPT_13 = 'Y'
THEN
UPDATE "NAMSAN"."PENALTY_ADDRESS_UPDATE_I_DAMO"
SET "PENALTY_MANAGE_YEAR" = :NEW."PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO" = :NEW."PENALTY_SEQNO",
"CAR_NO" = :NEW."CAR_NO",
"OWNER" = :NEW."OWNER",
"RESIDENT_NO" = :NEW."RESIDENT_NO",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"CHANAGE_DATE" = :NEW."CHANAGE_DATE",
"CHANAGE_SEQNO" = :NEW."CHANAGE_SEQNO",
"POST_NO1" = :NEW."POST_NO1",
"POST_NO2" = :NEW."POST_NO2",
"ADDRESS_NAME" = :NEW."ADDRESS_NAME",
"MOTION_ID" = :NEW."MOTION_ID",
"ADDRESS_NUMBER" = :NEW."ADDRESS_NUMBER",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"CAR_STATUS_ID" = :NEW."CAR_STATUS_ID",
"CAR_STATUS_DATE" = :NEW."CAR_STATUS_DATE",
"CARNUM_CHANGE_DATE" = :NEW."CARNUM_CHANGE_DATE"
WHERE ROWID = :OLD.PENTA__ROW__ID;
END IF;
END;
/
DROP TRIGGER NAMSAN.SDT_PENALTY_ADDRESS_UPDATE_IN;
CREATE OR REPLACE TRIGGER NAMSAN."SDT_PENALTY_ADDRESS_UPDATE_IN" INSTEAD OF DELETE ON "NAMSAN"."PENALTY_ADDRESS_UPDATE_I_ROOT" FOR EACH ROW
DECLARE BEGIN DELETE "NAMSAN"."PENALTY_ADDRESS_UPDATE_I_DAMO" WHERE ROWID = :OLD.PENTA__ROW__ID ; END;
/
DROP TRIGGER NAMSAN.SIT_PENALTY_ADDRESS_UPDATE_IN;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE TRIGGER NAMSAN."SIT_PENALTY_ADDRESS_UPDATE_IN"
INSTEAD OF INSERT
ON "NAMSAN"."PENALTY_ADDRESS_UPDATE_I_ROOT"
FOR EACH ROW
DECLARE
V_LENGTH1 NUMBER;
V_LENGTH2 NUMBER;
V_LENGTH3 NUMBER;
V_LENGTH4 NUMBER;
V_2 NUMBER (6) := 0;
V_RAW_2 RAW (2000);
V_5 VARCHAR2 (13);
V_RAW_5 RAW (2000);
V_8 NUMBER (2) := 0;
V_RAW_8 RAW (2000);
V_11 VARCHAR2 (60);
V_RAW_11 RAW (2000);
V_13 VARCHAR2 (80);
V_RAW_13 RAW (2000);
V_18 CHAR (2) := '00';
V_RAW_18 RAW (2000);
BEGIN
INSERT
INTO "NAMSAN"."PENALTY_ADDRESS_UPDATE_I_DAMO" ("PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO",
"CAR_NO",
"OWNER",
"RESIDENT_NO",
"RESIDENT_DATE",
"CHANAGE_DATE",
"CHANAGE_SEQNO",
"POST_NO1",
"POST_NO2",
"ADDRESS_NAME",
"MOTION_ID",
"ADDRESS_NUMBER",
"CREATE_DATE",
"CREATER",
"UPDATE_DATE",
"UPDATER",
"CAR_STATUS_ID",
"CAR_STATUS_DATE",
"CARNUM_CHANGE_DATE")
VALUES (:NEW."PENALTY_MANAGE_YEAR",
:NEW."PENALTY_SEQNO",
:NEW."CAR_NO",
:NEW."OWNER",
:NEW."RESIDENT_NO",
:NEW."RESIDENT_DATE",
:NEW."CHANAGE_DATE",
:NEW."CHANAGE_SEQNO",
:NEW."POST_NO1",
:NEW."POST_NO2",
:NEW."ADDRESS_NAME",
:NEW."MOTION_ID",
:NEW."ADDRESS_NUMBER",
:NEW."CREATE_DATE",
:NEW."CREATER",
:NEW."UPDATE_DATE",
:NEW."UPDATER",
:NEW."CAR_STATUS_ID",
:NEW."CAR_STATUS_DATE",
:NEW."CARNUM_CHANGE_DATE");
END;
/
DROP TRIGGER NAMSAN.SUT_OVERFARE_ORDER_INFO;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE TRIGGER NAMSAN."SUT_OVERFARE_ORDER_INFO"
INSTEAD OF UPDATE
ON "NAMSAN"."OVERFARE_ORDER_INFO_ROOT"
FOR EACH ROW
DECLARE
V_LENGTH1 NUMBER;
V_LENGTH2 NUMBER;
V_LENGTH3 NUMBER;
V_LENGTH4 NUMBER;
V_2 VARCHAR2 (13);
V_RAW_2 RAW (2000);
V_OLD_2 VARCHAR2 (13);
V_UPT_2 VARCHAR2 (1);
V_14 VARCHAR2 (60);
V_RAW_14 RAW (2000);
V_OLD_14 VARCHAR2 (60);
V_UPT_14 VARCHAR2 (1);
V_15 VARCHAR2 (80);
V_RAW_15 RAW (2000);
V_OLD_15 VARCHAR2 (80);
V_UPT_15 VARCHAR2 (1);
BEGIN
V_UPT_2 := 'N';
V_OLD_2 := :OLD."RESIDENT_NO";
IF V_OLD_2 IS NULL AND :NEW."RESIDENT_NO" IS NOT NULL
THEN
V_UPT_2 := 'Y';
ELSIF V_OLD_2 IS NOT NULL
THEN
IF V_OLD_2 != :NEW."RESIDENT_NO" OR :NEW."RESIDENT_NO" IS NULL
THEN
V_UPT_2 := 'Y';
END IF;
END IF;
V_UPT_14 := 'N';
V_OLD_14 := :OLD."ADDRESS_NAME";
IF V_OLD_14 IS NULL AND :NEW."ADDRESS_NAME" IS NOT NULL
THEN
V_UPT_14 := 'Y';
ELSIF V_OLD_14 IS NOT NULL
THEN
IF V_OLD_14 != :NEW."ADDRESS_NAME" OR :NEW."ADDRESS_NAME" IS NULL
THEN
V_UPT_14 := 'Y';
END IF;
END IF;
V_UPT_15 := 'N';
V_OLD_15 := :OLD."ADDRESS_NUMBER";
IF V_OLD_15 IS NULL AND :NEW."ADDRESS_NUMBER" IS NOT NULL
THEN
V_UPT_15 := 'Y';
ELSIF V_OLD_15 IS NOT NULL
THEN
IF V_OLD_15 != :NEW."ADDRESS_NUMBER" OR :NEW."ADDRESS_NUMBER" IS NULL
THEN
V_UPT_15 := 'Y';
END IF;
END IF;
IF V_UPT_2 = 'N' AND V_UPT_14 = 'N' AND V_UPT_15 = 'N'
THEN
UPDATE "NAMSAN"."OVERFARE_ORDER_INFO_DAMO"
SET "CAR_NO" = :NEW."CAR_NO",
"OWNER" = :NEW."OWNER",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"OVERFARE_AMOUNT" = :NEW."OVERFARE_AMOUNT",
"REFUND_AMOUNT" = :NEW."REFUND_AMOUNT",
"FILE_KIND_ID" = :NEW."FILE_KIND_ID",
"DATA_SEQNO" = :NEW."DATA_SEQNO",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"POST_NO1" = :NEW."POST_NO1",
"POST_NO2" = :NEW."POST_NO2",
"OVERFARE_COUNT" = :NEW."OVERFARE_COUNT",
"REFUND_COUNT" = :NEW."REFUND_COUNT",
"CITY_TAXES_COUNT" = :NEW."CITY_TAXES_COUNT",
"CITY_TAXES_AMONT" = :NEW."CITY_TAXES_AMONT",
"TRANS_FILE_CREATE_YN" = :NEW."TRANS_FILE_CREATE_YN",
"CAR_TYPE_NAME" = :NEW."CAR_TYPE_NAME",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_2 = 'N' AND V_UPT_14 = 'N' AND V_UPT_15 = 'Y'
THEN
UPDATE "NAMSAN"."OVERFARE_ORDER_INFO_DAMO"
SET "CAR_NO" = :NEW."CAR_NO",
"OWNER" = :NEW."OWNER",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"OVERFARE_AMOUNT" = :NEW."OVERFARE_AMOUNT",
"REFUND_AMOUNT" = :NEW."REFUND_AMOUNT",
"FILE_KIND_ID" = :NEW."FILE_KIND_ID",
"DATA_SEQNO" = :NEW."DATA_SEQNO",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"POST_NO1" = :NEW."POST_NO1",
"POST_NO2" = :NEW."POST_NO2",
"ADDRESS_NUMBER" = :NEW."ADDRESS_NUMBER",
"OVERFARE_COUNT" = :NEW."OVERFARE_COUNT",
"REFUND_COUNT" = :NEW."REFUND_COUNT",
"CITY_TAXES_COUNT" = :NEW."CITY_TAXES_COUNT",
"CITY_TAXES_AMONT" = :NEW."CITY_TAXES_AMONT",
"TRANS_FILE_CREATE_YN" = :NEW."TRANS_FILE_CREATE_YN",
"CAR_TYPE_NAME" = :NEW."CAR_TYPE_NAME",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_2 = 'N' AND V_UPT_14 = 'Y' AND V_UPT_15 = 'N'
THEN
UPDATE "NAMSAN"."OVERFARE_ORDER_INFO_DAMO"
SET "CAR_NO" = :NEW."CAR_NO",
"OWNER" = :NEW."OWNER",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"OVERFARE_AMOUNT" = :NEW."OVERFARE_AMOUNT",
"REFUND_AMOUNT" = :NEW."REFUND_AMOUNT",
"FILE_KIND_ID" = :NEW."FILE_KIND_ID",
"DATA_SEQNO" = :NEW."DATA_SEQNO",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"POST_NO1" = :NEW."POST_NO1",
"POST_NO2" = :NEW."POST_NO2",
"ADDRESS_NAME" = :NEW."ADDRESS_NAME",
"OVERFARE_COUNT" = :NEW."OVERFARE_COUNT",
"REFUND_COUNT" = :NEW."REFUND_COUNT",
"CITY_TAXES_COUNT" = :NEW."CITY_TAXES_COUNT",
"CITY_TAXES_AMONT" = :NEW."CITY_TAXES_AMONT",
"TRANS_FILE_CREATE_YN" = :NEW."TRANS_FILE_CREATE_YN",
"CAR_TYPE_NAME" = :NEW."CAR_TYPE_NAME",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_2 = 'N' AND V_UPT_14 = 'Y' AND V_UPT_15 = 'Y'
THEN
UPDATE "NAMSAN"."OVERFARE_ORDER_INFO_DAMO"
SET "CAR_NO" = :NEW."CAR_NO",
"OWNER" = :NEW."OWNER",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"OVERFARE_AMOUNT" = :NEW."OVERFARE_AMOUNT",
"REFUND_AMOUNT" = :NEW."REFUND_AMOUNT",
"FILE_KIND_ID" = :NEW."FILE_KIND_ID",
"DATA_SEQNO" = :NEW."DATA_SEQNO",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"POST_NO1" = :NEW."POST_NO1",
"POST_NO2" = :NEW."POST_NO2",
"ADDRESS_NAME" = :NEW."ADDRESS_NAME",
"ADDRESS_NUMBER" = :NEW."ADDRESS_NUMBER",
"OVERFARE_COUNT" = :NEW."OVERFARE_COUNT",
"REFUND_COUNT" = :NEW."REFUND_COUNT",
"CITY_TAXES_COUNT" = :NEW."CITY_TAXES_COUNT",
"CITY_TAXES_AMONT" = :NEW."CITY_TAXES_AMONT",
"TRANS_FILE_CREATE_YN" = :NEW."TRANS_FILE_CREATE_YN",
"CAR_TYPE_NAME" = :NEW."CAR_TYPE_NAME",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_2 = 'Y' AND V_UPT_14 = 'N' AND V_UPT_15 = 'N'
THEN
UPDATE "NAMSAN"."OVERFARE_ORDER_INFO_DAMO"
SET "CAR_NO" = :NEW."CAR_NO",
"RESIDENT_NO" = :NEW."RESIDENT_NO",
"OWNER" = :NEW."OWNER",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"OVERFARE_AMOUNT" = :NEW."OVERFARE_AMOUNT",
"REFUND_AMOUNT" = :NEW."REFUND_AMOUNT",
"FILE_KIND_ID" = :NEW."FILE_KIND_ID",
"DATA_SEQNO" = :NEW."DATA_SEQNO",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"POST_NO1" = :NEW."POST_NO1",
"POST_NO2" = :NEW."POST_NO2",
"OVERFARE_COUNT" = :NEW."OVERFARE_COUNT",
"REFUND_COUNT" = :NEW."REFUND_COUNT",
"CITY_TAXES_COUNT" = :NEW."CITY_TAXES_COUNT",
"CITY_TAXES_AMONT" = :NEW."CITY_TAXES_AMONT",
"TRANS_FILE_CREATE_YN" = :NEW."TRANS_FILE_CREATE_YN",
"CAR_TYPE_NAME" = :NEW."CAR_TYPE_NAME",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_2 = 'Y' AND V_UPT_14 = 'N' AND V_UPT_15 = 'Y'
THEN
UPDATE "NAMSAN"."OVERFARE_ORDER_INFO_DAMO"
SET "CAR_NO" = :NEW."CAR_NO",
"RESIDENT_NO" = :NEW."RESIDENT_NO",
"OWNER" = :NEW."OWNER",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"OVERFARE_AMOUNT" = :NEW."OVERFARE_AMOUNT",
"REFUND_AMOUNT" = :NEW."REFUND_AMOUNT",
"FILE_KIND_ID" = :NEW."FILE_KIND_ID",
"DATA_SEQNO" = :NEW."DATA_SEQNO",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"POST_NO1" = :NEW."POST_NO1",
"POST_NO2" = :NEW."POST_NO2",
"ADDRESS_NUMBER" = :NEW."ADDRESS_NUMBER",
"OVERFARE_COUNT" = :NEW."OVERFARE_COUNT",
"REFUND_COUNT" = :NEW."REFUND_COUNT",
"CITY_TAXES_COUNT" = :NEW."CITY_TAXES_COUNT",
"CITY_TAXES_AMONT" = :NEW."CITY_TAXES_AMONT",
"TRANS_FILE_CREATE_YN" = :NEW."TRANS_FILE_CREATE_YN",
"CAR_TYPE_NAME" = :NEW."CAR_TYPE_NAME",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_2 = 'Y' AND V_UPT_14 = 'Y' AND V_UPT_15 = 'N'
THEN
UPDATE "NAMSAN"."OVERFARE_ORDER_INFO_DAMO"
SET "CAR_NO" = :NEW."CAR_NO",
"RESIDENT_NO" = :NEW."RESIDENT_NO",
"OWNER" = :NEW."OWNER",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"OVERFARE_AMOUNT" = :NEW."OVERFARE_AMOUNT",
"REFUND_AMOUNT" = :NEW."REFUND_AMOUNT",
"FILE_KIND_ID" = :NEW."FILE_KIND_ID",
"DATA_SEQNO" = :NEW."DATA_SEQNO",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"POST_NO1" = :NEW."POST_NO1",
"POST_NO2" = :NEW."POST_NO2",
"ADDRESS_NAME" = :NEW."ADDRESS_NAME",
"OVERFARE_COUNT" = :NEW."OVERFARE_COUNT",
"REFUND_COUNT" = :NEW."REFUND_COUNT",
"CITY_TAXES_COUNT" = :NEW."CITY_TAXES_COUNT",
"CITY_TAXES_AMONT" = :NEW."CITY_TAXES_AMONT",
"TRANS_FILE_CREATE_YN" = :NEW."TRANS_FILE_CREATE_YN",
"CAR_TYPE_NAME" = :NEW."CAR_TYPE_NAME",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_2 = 'Y' AND V_UPT_14 = 'Y' AND V_UPT_15 = 'Y'
THEN
UPDATE "NAMSAN"."OVERFARE_ORDER_INFO_DAMO"
SET "CAR_NO" = :NEW."CAR_NO",
"RESIDENT_NO" = :NEW."RESIDENT_NO",
"OWNER" = :NEW."OWNER",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"OVERFARE_AMOUNT" = :NEW."OVERFARE_AMOUNT",
"REFUND_AMOUNT" = :NEW."REFUND_AMOUNT",
"FILE_KIND_ID" = :NEW."FILE_KIND_ID",
"DATA_SEQNO" = :NEW."DATA_SEQNO",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"POST_NO1" = :NEW."POST_NO1",
"POST_NO2" = :NEW."POST_NO2",
"ADDRESS_NAME" = :NEW."ADDRESS_NAME",
"ADDRESS_NUMBER" = :NEW."ADDRESS_NUMBER",
"OVERFARE_COUNT" = :NEW."OVERFARE_COUNT",
"REFUND_COUNT" = :NEW."REFUND_COUNT",
"CITY_TAXES_COUNT" = :NEW."CITY_TAXES_COUNT",
"CITY_TAXES_AMONT" = :NEW."CITY_TAXES_AMONT",
"TRANS_FILE_CREATE_YN" = :NEW."TRANS_FILE_CREATE_YN",
"CAR_TYPE_NAME" = :NEW."CAR_TYPE_NAME",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER"
WHERE ROWID = :OLD.PENTA__ROW__ID;
END IF;
END;
/
DROP TRIGGER NAMSAN.SDT_OVERFARE_ORDER_INFO;
CREATE OR REPLACE TRIGGER NAMSAN."SDT_OVERFARE_ORDER_INFO" INSTEAD OF DELETE ON "NAMSAN"."OVERFARE_ORDER_INFO_ROOT" FOR EACH ROW
DECLARE BEGIN DELETE "NAMSAN"."OVERFARE_ORDER_INFO_DAMO" WHERE ROWID = :OLD.PENTA__ROW__ID ; END;
/
DROP TRIGGER NAMSAN.SIT_OVERFARE_INFO;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE TRIGGER NAMSAN."SIT_OVERFARE_INFO"
INSTEAD OF INSERT
ON "NAMSAN"."OVERFARE_INFO_ROOT"
FOR EACH ROW
DECLARE
V_LENGTH1 NUMBER;
V_LENGTH2 NUMBER;
V_LENGTH3 NUMBER;
V_LENGTH4 NUMBER;
V_9 NUMBER (2) := 0;
V_RAW_9 RAW (2000);
V_16 CHAR (1) := 1;
V_RAW_16 RAW (2000);
V_19 NUMBER (5) := 0;
V_RAW_19 RAW (2000);
V_21 CHAR (1) := 0;
V_RAW_21 RAW (2000);
V_26 CHAR (1) := 1;
V_RAW_26 RAW (2000);
V_33 VARCHAR2 (13);
V_RAW_33 RAW (2000);
V_34 CHAR (1) := 0;
V_RAW_34 RAW (2000);
V_35 CHAR (1) := 0;
BEGIN
V_9 := NVL (:NEW."OVERFARE_SEQNO", V_9);
V_16 := NVL (:NEW."OVERFARE_KBN", V_16);
V_19 := NVL (:NEW."OVERFARE_AMOUNT", V_19);
V_21 := NVL (:NEW."REFUND_KBN", V_21);
V_26 := NVL (:NEW."TRANS_FILE_CREATE_YN", V_26);
V_34 := NVL (:NEW."PROCESS_KBN", V_34);
V_35 := NVL (:NEW."DELETE_YN", V_35);
V_33 := :NEW."RESIDENT_NO";
INSERT INTO "NAMSAN"."OVERFARE_INFO_DAMO" ("WORKER_ID",
"FARE_OFFICE_ID",
"BOOTH_ID",
"YEAR",
"MONTH",
"DAY",
"WORK_STT_TIME",
"WORK_END_TIME",
"OVERFARE_SEQNO",
"OWNER",
"FILE_KIND_ID",
"DATA_SEQNO",
"TRANS_YEAR",
"TRANS_MONTH",
"TRANS_DAY",
"OVERFARE_KBN",
"RESIDENT_DATE",
"CAR_NO",
"OVERFARE_AMOUNT",
"OVERFARE_TIMES",
"REFUND_KBN",
"REFUND_WORKER_ID",
"REMARKS",
"REFUND_FARE_OFFICE_ID",
"REFUND_BOOTH_ID",
"TRANS_FILE_CREATE_YN",
"REFUND_YEAR",
"REFUND_OWNER",
"REFUND_MONTH",
"REFUND_COMMUMI",
"REFUND_DAY",
"REFUND_TIMES",
"RESIDENT_NO",
"PROCESS_KBN",
"DELETE_YN",
"CREATE_DATE",
"CREATER",
"UPDATE_DATE",
"UPDATER")
VALUES (:NEW."WORKER_ID",
:NEW."FARE_OFFICE_ID",
:NEW."BOOTH_ID",
:NEW."YEAR",
:NEW."MONTH",
:NEW."DAY",
:NEW."WORK_STT_TIME",
:NEW."WORK_END_TIME",
V_9,
:NEW."OWNER",
:NEW."FILE_KIND_ID",
:NEW."DATA_SEQNO",
:NEW."TRANS_YEAR",
:NEW."TRANS_MONTH",
:NEW."TRANS_DAY",
V_16,
:NEW."RESIDENT_DATE",
:NEW."CAR_NO",
V_19,
:NEW."OVERFARE_TIMES",
V_21,
:NEW."REFUND_WORKER_ID",
:NEW."REMARKS",
:NEW."REFUND_FARE_OFFICE_ID",
:NEW."REFUND_BOOTH_ID",
V_26,
:NEW."REFUND_YEAR",
:NEW."REFUND_OWNER",
:NEW."REFUND_MONTH",
:NEW."REFUND_COMMUMI",
:NEW."REFUND_DAY",
:NEW."REFUND_TIMES",
:NEW."RESIDENT_NO",
V_34,
V_35,
:NEW."CREATE_DATE",
:NEW."CREATER",
:NEW."UPDATE_DATE",
:NEW."UPDATER");
END;
/
DROP TRIGGER NAMSAN.SUT_OVERFARE_INFO;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE TRIGGER NAMSAN."SUT_OVERFARE_INFO"
INSTEAD OF UPDATE
ON "NAMSAN"."OVERFARE_INFO_ROOT"
FOR EACH ROW
DECLARE
V_LENGTH1 NUMBER;
V_LENGTH2 NUMBER;
V_LENGTH3 NUMBER;
V_LENGTH4 NUMBER;
V_33 VARCHAR2 (13);
V_RAW_33 RAW (2000);
V_OLD_33 VARCHAR2 (13);
V_UPT_33 VARCHAR2 (1);
BEGIN
V_UPT_33 := 'N';
V_OLD_33 := :OLD."RESIDENT_NO";
IF V_OLD_33 IS NULL AND :NEW."RESIDENT_NO" IS NOT NULL
THEN
V_UPT_33 := 'Y';
ELSIF V_OLD_33 IS NOT NULL
THEN
IF V_OLD_33 != :NEW."RESIDENT_NO" OR :NEW."RESIDENT_NO" IS NULL
THEN
V_UPT_33 := 'Y';
END IF;
END IF;
IF V_UPT_33 = 'N'
THEN
UPDATE "NAMSAN"."OVERFARE_INFO_DAMO"
SET "WORKER_ID" = :NEW."WORKER_ID",
"FARE_OFFICE_ID" = :NEW."FARE_OFFICE_ID",
"BOOTH_ID" = :NEW."BOOTH_ID",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"WORK_STT_TIME" = :NEW."WORK_STT_TIME",
"WORK_END_TIME" = :NEW."WORK_END_TIME",
"OVERFARE_SEQNO" = :NEW."OVERFARE_SEQNO",
"OWNER" = :NEW."OWNER",
"FILE_KIND_ID" = :NEW."FILE_KIND_ID",
"DATA_SEQNO" = :NEW."DATA_SEQNO",
"TRANS_YEAR" = :NEW."TRANS_YEAR",
"TRANS_MONTH" = :NEW."TRANS_MONTH",
"TRANS_DAY" = :NEW."TRANS_DAY",
"OVERFARE_KBN" = :NEW."OVERFARE_KBN",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"CAR_NO" = :NEW."CAR_NO",
"OVERFARE_AMOUNT" = :NEW."OVERFARE_AMOUNT",
"OVERFARE_TIMES" = :NEW."OVERFARE_TIMES",
"REFUND_KBN" = :NEW."REFUND_KBN",
"REFUND_WORKER_ID" = :NEW."REFUND_WORKER_ID",
"REMARKS" = :NEW."REMARKS",
"REFUND_FARE_OFFICE_ID" = :NEW."REFUND_FARE_OFFICE_ID",
"REFUND_BOOTH_ID" = :NEW."REFUND_BOOTH_ID",
"TRANS_FILE_CREATE_YN" = :NEW."TRANS_FILE_CREATE_YN",
"REFUND_YEAR" = :NEW."REFUND_YEAR",
"REFUND_OWNER" = :NEW."REFUND_OWNER",
"REFUND_MONTH" = :NEW."REFUND_MONTH",
"REFUND_COMMUMI" = :NEW."REFUND_COMMUMI",
"REFUND_DAY" = :NEW."REFUND_DAY",
"REFUND_TIMES" = :NEW."REFUND_TIMES",
"PROCESS_KBN" = :NEW."PROCESS_KBN",
"DELETE_YN" = :NEW."DELETE_YN",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_33 = 'Y'
THEN
V_33 := :NEW."RESIDENT_NO";
UPDATE "NAMSAN"."OVERFARE_INFO_DAMO"
SET "WORKER_ID" = :NEW."WORKER_ID",
"FARE_OFFICE_ID" = :NEW."FARE_OFFICE_ID",
"BOOTH_ID" = :NEW."BOOTH_ID",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"WORK_STT_TIME" = :NEW."WORK_STT_TIME",
"WORK_END_TIME" = :NEW."WORK_END_TIME",
"OVERFARE_SEQNO" = :NEW."OVERFARE_SEQNO",
"OWNER" = :NEW."OWNER",
"FILE_KIND_ID" = :NEW."FILE_KIND_ID",
"DATA_SEQNO" = :NEW."DATA_SEQNO",
"TRANS_YEAR" = :NEW."TRANS_YEAR",
"TRANS_MONTH" = :NEW."TRANS_MONTH",
"TRANS_DAY" = :NEW."TRANS_DAY",
"OVERFARE_KBN" = :NEW."OVERFARE_KBN",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"CAR_NO" = :NEW."CAR_NO",
"OVERFARE_AMOUNT" = :NEW."OVERFARE_AMOUNT",
"OVERFARE_TIMES" = :NEW."OVERFARE_TIMES",
"REFUND_KBN" = :NEW."REFUND_KBN",
"REFUND_WORKER_ID" = :NEW."REFUND_WORKER_ID",
"REMARKS" = :NEW."REMARKS",
"REFUND_FARE_OFFICE_ID" = :NEW."REFUND_FARE_OFFICE_ID",
"REFUND_BOOTH_ID" = :NEW."REFUND_BOOTH_ID",
"TRANS_FILE_CREATE_YN" = :NEW."TRANS_FILE_CREATE_YN",
"REFUND_YEAR" = :NEW."REFUND_YEAR",
"REFUND_OWNER" = :NEW."REFUND_OWNER",
"REFUND_MONTH" = :NEW."REFUND_MONTH",
"REFUND_COMMUMI" = :NEW."REFUND_COMMUMI",
"REFUND_DAY" = :NEW."REFUND_DAY",
"REFUND_TIMES" = :NEW."REFUND_TIMES",
"RESIDENT_NO" = :NEW."RESIDENT_NO",
"PROCESS_KBN" = :NEW."PROCESS_KBN",
"DELETE_YN" = :NEW."DELETE_YN",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER"
WHERE ROWID = :OLD.PENTA__ROW__ID;
END IF;
END;
/
DROP TRIGGER NAMSAN.SDT_OVERFARE_INFO;
CREATE OR REPLACE TRIGGER NAMSAN."SDT_OVERFARE_INFO" INSTEAD OF DELETE ON "NAMSAN"."OVERFARE_INFO_ROOT" FOR EACH ROW
DECLARE BEGIN DELETE "NAMSAN"."OVERFARE_INFO_DAMO" WHERE ROWID = :OLD.PENTA__ROW__ID ; END;
/
DROP TRIGGER NAMSAN.SIT_PENALTY_DETAIL_INFO;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE TRIGGER NAMSAN.SIT_PENALTY_DETAIL_INFO
INSTEAD OF INSERT
ON NAMSAN.PENALTY_DETAIL_INFO_ROOT
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
V_LENGTH1 NUMBER;
V_LENGTH2 NUMBER;
V_LENGTH3 NUMBER;
V_LENGTH4 NUMBER;
V_4 VARCHAR2 (13);
V_RAW_4 RAW (2000);
V_25 VARCHAR2 (16);
V_2 NUMBER (6) := 0;
V_3 NUMBER (6) := 0;
V_7 CHAR (1) := 0;
V_RAW_25 RAW (2000);
V_28 VARCHAR2 (20);
V_RAW_28 RAW (2000);
V_29 VARCHAR2 (4);
V_RAW_29 RAW (2000);
V_33 VARCHAR2 (60);
V_RAW_33 RAW (2000);
BEGIN
V_4 := :NEW."RESIDENT_NO";
INSERT INTO "NAMSAN"."PENALTY_DETAIL_INFO_DAMO" ("PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO",
"WORKER_ID",
"FARE_OFFICE_ID",
"LEVY_MANAGE_YEAR",
"STT_DATE",
"LEVY_SEQNO",
"BOOTH_ID",
"YEAR",
"MONTH",
"DAY",
"WORK_TIMES",
"SEQNO",
"CAR_NO",
"RESIDENT_DATE",
"CARD_ISSUE_ID",
"NOTICE_WRONG_ID",
"OWNER",
"NOTICE_ID",
"RESIDENT_NO",
"FINE_AMOUNT",
"LAVY_AMOUNT",
"MOTION_ID",
"NOTICE_DATE",
"NOTICE_WRONG_DATE",
"BEFORE_MOTION_ID",
"PASS_AMOUNT",
"SEISURE_YN",
"SEIZURE_DATE",
"SEIZURE_ID",
"NOTICE_CHOOSE_ID",
"SEIZURE_WRONG_ID",
"SEIZURE_WRONG_DATE",
"SEIZURE_TERMI_DATE",
"RECEIPT_YN",
"DOUBLE_RECEIPT_YN",
"SEIZURE_TRMI_YN",
"DOUBLE_RECEIPT_DATE",
"SEIZURE_TERMI",
"SEIZURE_MANAGE_YEAR",
"SEIZURE_MANAGE_NO",
"NOTICE_WRONG_REASON",
"NOTICE_CHOOSE_DATE",
"SEIZURE_WRONG_REASON",
"CARS_TRANS_FLAG",
"T_REAL_TIME",
"MINI_CAR_KBN",
"CREATE_DATE",
"CREATER",
"UPDATE_DATE",
"UPDATER",
"C_PUBLIC",
"C_LOSS",
"RFID_KBN",
"CAR_CHANGE_YN",
"CAR_NO_AFTER",
"RFID_PENALTY_KBN",
"RFID_PENALTY_REASON",
"CAR_NO_AFTER_2",
"CAR_NO_AFTER_3",
"MONEY_CHANGE_REASON",
"MONEY_CHANGE_PERSON",
"RENT_KBN")
VALUES (:NEW."PENALTY_MANAGE_YEAR",
:NEW."PENALTY_SEQNO",
:NEW."WORKER_ID",
:NEW."FARE_OFFICE_ID",
:NEW."LEVY_MANAGE_YEAR",
:NEW."STT_DATE",
V_2,
:NEW."BOOTH_ID",
:NEW."YEAR",
:NEW."MONTH",
:NEW."DAY",
:NEW."WORK_TIMES",
:NEW."SEQNO",
:NEW."CAR_NO",
:NEW."RESIDENT_DATE",
:NEW."CARD_ISSUE_ID",
:NEW."NOTICE_WRONG_ID",
:NEW."OWNER",
:NEW."NOTICE_ID",
:NEW."RESIDENT_NO",
:NEW."FINE_AMOUNT",
:NEW."LAVY_AMOUNT",
:NEW."MOTION_ID",
:NEW."NOTICE_DATE",
:NEW."NOTICE_WRONG_DATE",
:NEW."BEFORE_MOTION_ID",
:NEW."PASS_AMOUNT",
V_7,
:NEW."SEIZURE_DATE",
:NEW."SEIZURE_ID",
:NEW."NOTICE_CHOOSE_ID",
:NEW."SEIZURE_WRONG_ID",
:NEW."SEIZURE_WRONG_DATE",
:NEW."SEIZURE_TERMI_DATE",
V_7,
V_7,
V_7,
:NEW."DOUBLE_RECEIPT_DATE",
:NEW."SEIZURE_TERMI",
:NEW."SEIZURE_MANAGE_YEAR",
V_3,
:NEW."NOTICE_WRONG_REASON",
:NEW."NOTICE_CHOOSE_DATE",
:NEW."SEIZURE_WRONG_REASON",
:NEW."CARS_TRANS_FLAG",
:NEW."T_REAL_TIME",
:NEW."MINI_CAR_KBN",
:NEW."CREATE_DATE",
:NEW."CREATER",
:NEW."UPDATE_DATE",
:NEW."UPDATER",
V_7,
V_7,
:NEW."RFID_KBN",
:NEW."CAR_CHANGE_YN",
:NEW."CAR_NO_AFTER",
:NEW."RFID_PENALTY_KBN",
:NEW."RFID_PENALTY_REASON",
:NEW."CAR_NO_AFTER_2",
:NEW."CAR_NO_AFTER_3",
:NEW."MONEY_CHANGE_REASON",
:NEW."MONEY_CHANGE_PERSON",
V_7);
END;
/
DROP TRIGGER NAMSAN.SIT_LEVY_TARGET_INFO;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE TRIGGER NAMSAN."SIT_LEVY_TARGET_INFO"
INSTEAD OF INSERT
ON "NAMSAN"."LEVY_TARGET_INFO_ROOT"
FOR EACH ROW
DECLARE
V_LENGTH1 NUMBER;
V_LENGTH2 NUMBER;
V_LENGTH3 NUMBER;
V_LENGTH4 NUMBER;
V_2 NUMBER (6) := 0;
V_RAW_2 RAW (2000);
V_13 VARCHAR2 (13);
V_RAW_13 RAW (2000);
V_17 CHAR (1) := 1;
V_RAW_17 RAW (2000);
V_19 VARCHAR2 (20) := 0;
V_RAW_19 RAW (2000);
BEGIN
V_2 := NVL (:NEW."LEVY_SEQNO", V_2);
V_17 := NVL (:NEW."NOTICE_KBN", V_17);
V_19 := NVL (:NEW."PENALTY_PROCESS_YN", V_19);
V_13 := :NEW."RESIDENT_NO";
INSERT INTO "NAMSAN"."LEVY_TARGET_INFO_DAMO" ("LEVY_MANAGE_YEAR",
"LEVY_SEQNO",
"WORKER_ID",
"FARE_OFFICE_ID",
"BOOTH_ID",
"YEAR",
"MONTH",
"DAY",
"WORK_TIMES",
"SEQNO",
"CAR_NO",
"OWNER",
"RESIDENT_NO",
"MOTION_ID",
"EXCEPT_DATE",
"NOTICE_DATE",
"NOTICE_KBN",
"RESIDENT_DATE",
"PENALTY_PROCESS_YN",
"CREATE_DATE",
"CREATER",
"UPDATE_DATE",
"UPDATER")
VALUES (:NEW."LEVY_MANAGE_YEAR",
V_2,
:NEW."WORKER_ID",
:NEW."FARE_OFFICE_ID",
:NEW."BOOTH_ID",
:NEW."YEAR",
:NEW."MONTH",
:NEW."DAY",
:NEW."WORK_TIMES",
:NEW."SEQNO",
:NEW."CAR_NO",
:NEW."OWNER",
:NEW."RESIDENT_NO",
:NEW."MOTION_ID",
:NEW."EXCEPT_DATE",
:NEW."NOTICE_DATE",
V_17,
:NEW."RESIDENT_DATE",
V_19,
:NEW."CREATE_DATE",
:NEW."CREATER",
:NEW."UPDATE_DATE",
:NEW."UPDATER");
END;
/
DROP TRIGGER NAMSAN.SUT_LEVY_TARGET_INFO;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE TRIGGER NAMSAN."SUT_LEVY_TARGET_INFO"
INSTEAD OF UPDATE
ON "NAMSAN"."LEVY_TARGET_INFO_ROOT"
FOR EACH ROW
DECLARE
V_LENGTH1 NUMBER;
V_LENGTH2 NUMBER;
V_LENGTH3 NUMBER;
V_LENGTH4 NUMBER;
V_13 VARCHAR2 (13);
V_RAW_13 RAW (2000);
V_OLD_13 VARCHAR2 (13);
V_UPT_13 VARCHAR2 (1);
BEGIN
V_UPT_13 := 'N';
V_OLD_13 := :OLD."RESIDENT_NO";
IF V_OLD_13 IS NULL AND :NEW."RESIDENT_NO" IS NOT NULL
THEN
V_UPT_13 := 'Y';
ELSIF V_OLD_13 IS NOT NULL
THEN
IF V_OLD_13 != :NEW."RESIDENT_NO" OR :NEW."RESIDENT_NO" IS NULL
THEN
V_UPT_13 := 'Y';
END IF;
END IF;
IF V_UPT_13 = 'N'
THEN
UPDATE "NAMSAN"."LEVY_TARGET_INFO_DAMO"
SET "LEVY_MANAGE_YEAR" = :NEW."LEVY_MANAGE_YEAR",
"LEVY_SEQNO" = :NEW."LEVY_SEQNO",
"WORKER_ID" = :NEW."WORKER_ID",
"FARE_OFFICE_ID" = :NEW."FARE_OFFICE_ID",
"BOOTH_ID" = :NEW."BOOTH_ID",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"WORK_TIMES" = :NEW."WORK_TIMES",
"SEQNO" = :NEW."SEQNO",
"CAR_NO" = :NEW."CAR_NO",
"OWNER" = :NEW."OWNER",
"MOTION_ID" = :NEW."MOTION_ID",
"EXCEPT_DATE" = :NEW."EXCEPT_DATE",
"NOTICE_DATE" = :NEW."NOTICE_DATE",
"NOTICE_KBN" = :NEW."NOTICE_KBN",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"PENALTY_PROCESS_YN" = :NEW."PENALTY_PROCESS_YN",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_13 = 'Y'
THEN
V_13 := :NEW."RESIDENT_NO";
UPDATE "NAMSAN"."LEVY_TARGET_INFO_DAMO"
SET "LEVY_MANAGE_YEAR" = :NEW."LEVY_MANAGE_YEAR",
"LEVY_SEQNO" = :NEW."LEVY_SEQNO",
"WORKER_ID" = :NEW."WORKER_ID",
"FARE_OFFICE_ID" = :NEW."FARE_OFFICE_ID",
"BOOTH_ID" = :NEW."BOOTH_ID",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"WORK_TIMES" = :NEW."WORK_TIMES",
"SEQNO" = :NEW."SEQNO",
"CAR_NO" = :NEW."CAR_NO",
"OWNER" = :NEW."OWNER",
"RESIDENT_NO" = :NEW."RESIDENT_NO",
"MOTION_ID" = :NEW."MOTION_ID",
"EXCEPT_DATE" = :NEW."EXCEPT_DATE",
"NOTICE_DATE" = :NEW."NOTICE_DATE",
"NOTICE_KBN" = :NEW."NOTICE_KBN",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"PENALTY_PROCESS_YN" = :NEW."PENALTY_PROCESS_YN",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER"
WHERE ROWID = :OLD.PENTA__ROW__ID;
END IF;
END;
/
DROP TRIGGER NAMSAN.SDT_LEVY_TARGET_INFO;
CREATE OR REPLACE TRIGGER NAMSAN."SDT_LEVY_TARGET_INFO" INSTEAD OF DELETE ON "NAMSAN"."LEVY_TARGET_INFO_ROOT" FOR EACH ROW
DECLARE BEGIN DELETE "NAMSAN"."LEVY_TARGET_INFO_DAMO" WHERE ROWID = :OLD.PENTA__ROW__ID ; END;
/
DROP TRIGGER NAMSAN.SUT_PENALTY_HEAD_INFO;
/*
damo 사용 안하도록 변경
*/
/* Formatted on 2019/04/04 오후 2:59:08 (QP5 v5.215.12089.38647) */
CREATE OR REPLACE TRIGGER NAMSAN."SUT_PENALTY_HEAD_INFO"
INSTEAD OF UPDATE
ON "NAMSAN"."PENALTY_HEAD_INFO_ROOT"
FOR EACH ROW
DECLARE
V_LENGTH1 NUMBER;
V_LENGTH2 NUMBER;
V_LENGTH3 NUMBER;
V_LENGTH4 NUMBER;
V_5 VARCHAR2 (13);
V_RAW_5 RAW (2000);
V_OLD_5 VARCHAR2 (13);
V_UPT_5 VARCHAR2 (1);
V_11 VARCHAR2 (60);
V_RAW_11 RAW (2000);
V_OLD_11 VARCHAR2 (60);
V_UPT_11 VARCHAR2 (1);
V_13 VARCHAR2 (80);
V_RAW_13 RAW (2000);
V_OLD_13 VARCHAR2 (80);
V_UPT_13 VARCHAR2 (1);
BEGIN
V_UPT_5 := 'N';
V_OLD_5 := :OLD."RESIDENT_NO";
IF V_OLD_5 IS NULL AND :NEW."RESIDENT_NO" IS NOT NULL
THEN
V_UPT_5 := 'Y';
ELSIF V_OLD_5 IS NOT NULL
THEN
IF V_OLD_5 != :NEW."RESIDENT_NO" OR :NEW."RESIDENT_NO" IS NULL
THEN
V_UPT_5 := 'Y';
END IF;
END IF;
V_UPT_11 := 'N';
V_OLD_11 := :OLD."ADDRESS_NAME";
IF V_OLD_11 IS NULL AND :NEW."ADDRESS_NAME" IS NOT NULL
THEN
V_UPT_11 := 'Y';
ELSIF V_OLD_11 IS NOT NULL
THEN
IF V_OLD_11 != :NEW."ADDRESS_NAME" OR :NEW."ADDRESS_NAME" IS NULL
THEN
V_UPT_11 := 'Y';
END IF;
END IF;
V_UPT_13 := 'N';
V_OLD_13 := :OLD."ADDRESS_NUMBER";
IF V_OLD_13 IS NULL AND :NEW."ADDRESS_NUMBER" IS NOT NULL
THEN
V_UPT_13 := 'Y';
ELSIF V_OLD_13 IS NOT NULL
THEN
IF V_OLD_13 != :NEW."ADDRESS_NUMBER" OR :NEW."ADDRESS_NUMBER" IS NULL
THEN
V_UPT_13 := 'Y';
END IF;
END IF;
IF V_UPT_5 = 'N' AND V_UPT_11 = 'N' AND V_UPT_13 = 'N'
THEN
UPDATE "NAMSAN"."PENALTY_HEAD_INFO_DAMO"
SET "CAR_NO" = :NEW."CAR_NO",
"OWNER" = :NEW."OWNER",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"OWNER_TYPE_ID" = :NEW."OWNER_TYPE_ID",
"DISUSE_DATE" = :NEW."DISUSE_DATE",
"CAR_TYPE_ID" = :NEW."CAR_TYPE_ID",
"CAR_TYPE_NAME" = :NEW."CAR_TYPE_NAME",
"MAX_CARRY_CAPACITY" = :NEW."MAX_CARRY_CAPACITY",
"PANALTY_COUNT" = :NEW."PANALTY_COUNT",
"PENALTY_AMOUNT" = :NEW."PENALTY_AMOUNT",
"RECEIVE_AMOUNT" = :NEW."RECEIVE_AMOUNT",
"POST_NO1" = :NEW."POST_NO1",
"POST_NO2" = :NEW."POST_NO2",
"AFTER_CARS_NUMBER_1" = :NEW."AFTER_CARS_NUMBER_1",
"AFTER_CARS_NUMBER_2" = :NEW."AFTER_CARS_NUMBER_2",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"CAR_STATUS_ID" = :NEW."CAR_STATUS_ID",
"CAR_STATUS_DATE" = :NEW."CAR_STATUS_DATE",
"CARNUM_CHANGE_DATE" = :NEW."CARNUM_CHANGE_DATE",
"AFTER_CARS_NUMBER_3" = :NEW."AFTER_CARS_NUMBER_3",
"RENT_KBN" = :NEW."RENT_KBN"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_5 = 'N' AND V_UPT_11 = 'N' AND V_UPT_13 = 'Y'
THEN
UPDATE "NAMSAN"."PENALTY_HEAD_INFO_DAMO"
SET "CAR_NO" = :NEW."CAR_NO",
"OWNER" = :NEW."OWNER",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"OWNER_TYPE_ID" = :NEW."OWNER_TYPE_ID",
"DISUSE_DATE" = :NEW."DISUSE_DATE",
"CAR_TYPE_ID" = :NEW."CAR_TYPE_ID",
"CAR_TYPE_NAME" = :NEW."CAR_TYPE_NAME",
"MAX_CARRY_CAPACITY" = :NEW."MAX_CARRY_CAPACITY",
"PANALTY_COUNT" = :NEW."PANALTY_COUNT",
"PENALTY_AMOUNT" = :NEW."PENALTY_AMOUNT",
"RECEIVE_AMOUNT" = :NEW."RECEIVE_AMOUNT",
"POST_NO1" = :NEW."POST_NO1",
"POST_NO2" = :NEW."POST_NO2",
"AFTER_CARS_NUMBER_1" = :NEW."AFTER_CARS_NUMBER_1",
"AFTER_CARS_NUMBER_2" = :NEW."AFTER_CARS_NUMBER_2",
"ADDRESS_NUMBER" = :NEW."ADDRESS_NUMBER",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"CAR_STATUS_ID" = :NEW."CAR_STATUS_ID",
"CAR_STATUS_DATE" = :NEW."CAR_STATUS_DATE",
"CARNUM_CHANGE_DATE" = :NEW."CARNUM_CHANGE_DATE",
"AFTER_CARS_NUMBER_3" = :NEW."AFTER_CARS_NUMBER_3",
"RENT_KBN" = :NEW."RENT_KBN"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_5 = 'N' AND V_UPT_11 = 'Y' AND V_UPT_13 = 'N'
THEN
UPDATE "NAMSAN"."PENALTY_HEAD_INFO_DAMO"
SET "CAR_NO" = :NEW."CAR_NO",
"OWNER" = :NEW."OWNER",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"OWNER_TYPE_ID" = :NEW."OWNER_TYPE_ID",
"DISUSE_DATE" = :NEW."DISUSE_DATE",
"CAR_TYPE_ID" = :NEW."CAR_TYPE_ID",
"CAR_TYPE_NAME" = :NEW."CAR_TYPE_NAME",
"MAX_CARRY_CAPACITY" = :NEW."MAX_CARRY_CAPACITY",
"PANALTY_COUNT" = :NEW."PANALTY_COUNT",
"PENALTY_AMOUNT" = :NEW."PENALTY_AMOUNT",
"RECEIVE_AMOUNT" = :NEW."RECEIVE_AMOUNT",
"POST_NO1" = :NEW."POST_NO1",
"POST_NO2" = :NEW."POST_NO2",
"AFTER_CARS_NUMBER_1" = :NEW."AFTER_CARS_NUMBER_1",
"AFTER_CARS_NUMBER_2" = :NEW."AFTER_CARS_NUMBER_2",
"ADDRESS_NAME" = :NEW."ADDRESS_NAME",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"CAR_STATUS_ID" = :NEW."CAR_STATUS_ID",
"CAR_STATUS_DATE" = :NEW."CAR_STATUS_DATE",
"CARNUM_CHANGE_DATE" = :NEW."CARNUM_CHANGE_DATE",
"AFTER_CARS_NUMBER_3" = :NEW."AFTER_CARS_NUMBER_3",
"RENT_KBN" = :NEW."RENT_KBN"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_5 = 'N' AND V_UPT_11 = 'Y' AND V_UPT_13 = 'Y'
THEN
UPDATE "NAMSAN"."PENALTY_HEAD_INFO_DAMO"
SET "CAR_NO" = :NEW."CAR_NO",
"OWNER" = :NEW."OWNER",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"OWNER_TYPE_ID" = :NEW."OWNER_TYPE_ID",
"DISUSE_DATE" = :NEW."DISUSE_DATE",
"CAR_TYPE_ID" = :NEW."CAR_TYPE_ID",
"CAR_TYPE_NAME" = :NEW."CAR_TYPE_NAME",
"MAX_CARRY_CAPACITY" = :NEW."MAX_CARRY_CAPACITY",
"PANALTY_COUNT" = :NEW."PANALTY_COUNT",
"PENALTY_AMOUNT" = :NEW."PENALTY_AMOUNT",
"RECEIVE_AMOUNT" = :NEW."RECEIVE_AMOUNT",
"POST_NO1" = :NEW."POST_NO1",
"POST_NO2" = :NEW."POST_NO2",
"AFTER_CARS_NUMBER_1" = :NEW."AFTER_CARS_NUMBER_1",
"AFTER_CARS_NUMBER_2" = :NEW."AFTER_CARS_NUMBER_2",
"ADDRESS_NAME" = :NEW."ADDRESS_NAME",
"ADDRESS_NUMBER" = :NEW."ADDRESS_NUMBER",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"CAR_STATUS_ID" = :NEW."CAR_STATUS_ID",
"CAR_STATUS_DATE" = :NEW."CAR_STATUS_DATE",
"CARNUM_CHANGE_DATE" = :NEW."CARNUM_CHANGE_DATE",
"AFTER_CARS_NUMBER_3" = :NEW."AFTER_CARS_NUMBER_3",
"RENT_KBN" = :NEW."RENT_KBN"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_5 = 'Y' AND V_UPT_11 = 'N' AND V_UPT_13 = 'N'
THEN
UPDATE "NAMSAN"."PENALTY_HEAD_INFO_DAMO"
SET "CAR_NO" = :NEW."CAR_NO",
"RESIDENT_NO" = :NEW."RESIDENT_NO",
"OWNER" = :NEW."OWNER",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"OWNER_TYPE_ID" = :NEW."OWNER_TYPE_ID",
"DISUSE_DATE" = :NEW."DISUSE_DATE",
"CAR_TYPE_ID" = :NEW."CAR_TYPE_ID",
"CAR_TYPE_NAME" = :NEW."CAR_TYPE_NAME",
"MAX_CARRY_CAPACITY" = :NEW."MAX_CARRY_CAPACITY",
"PANALTY_COUNT" = :NEW."PANALTY_COUNT",
"PENALTY_AMOUNT" = :NEW."PENALTY_AMOUNT",
"RECEIVE_AMOUNT" = :NEW."RECEIVE_AMOUNT",
"POST_NO1" = :NEW."POST_NO1",
"POST_NO2" = :NEW."POST_NO2",
"AFTER_CARS_NUMBER_1" = :NEW."AFTER_CARS_NUMBER_1",
"AFTER_CARS_NUMBER_2" = :NEW."AFTER_CARS_NUMBER_2",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"CAR_STATUS_ID" = :NEW."CAR_STATUS_ID",
"CAR_STATUS_DATE" = :NEW."CAR_STATUS_DATE",
"CARNUM_CHANGE_DATE" = :NEW."CARNUM_CHANGE_DATE",
"AFTER_CARS_NUMBER_3" = :NEW."AFTER_CARS_NUMBER_3",
"RENT_KBN" = :NEW."RENT_KBN"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_5 = 'Y' AND V_UPT_11 = 'N' AND V_UPT_13 = 'Y'
THEN
UPDATE "NAMSAN"."PENALTY_HEAD_INFO_DAMO"
SET "CAR_NO" = :NEW."CAR_NO",
"RESIDENT_NO" = :NEW."RESIDENT_NO",
"OWNER" = :NEW."OWNER",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"OWNER_TYPE_ID" = :NEW."OWNER_TYPE_ID",
"DISUSE_DATE" = :NEW."DISUSE_DATE",
"CAR_TYPE_ID" = :NEW."CAR_TYPE_ID",
"CAR_TYPE_NAME" = :NEW."CAR_TYPE_NAME",
"MAX_CARRY_CAPACITY" = :NEW."MAX_CARRY_CAPACITY",
"PANALTY_COUNT" = :NEW."PANALTY_COUNT",
"PENALTY_AMOUNT" = :NEW."PENALTY_AMOUNT",
"RECEIVE_AMOUNT" = :NEW."RECEIVE_AMOUNT",
"POST_NO1" = :NEW."POST_NO1",
"POST_NO2" = :NEW."POST_NO2",
"AFTER_CARS_NUMBER_1" = :NEW."AFTER_CARS_NUMBER_1",
"AFTER_CARS_NUMBER_2" = :NEW."AFTER_CARS_NUMBER_2",
"ADDRESS_NUMBER" = :NEW."ADDRESS_NUMBER",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"CAR_STATUS_ID" = :NEW."CAR_STATUS_ID",
"CAR_STATUS_DATE" = :NEW."CAR_STATUS_DATE",
"CARNUM_CHANGE_DATE" = :NEW."CARNUM_CHANGE_DATE",
"AFTER_CARS_NUMBER_3" = :NEW."AFTER_CARS_NUMBER_3",
"RENT_KBN" = :NEW."RENT_KBN"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_5 = 'Y' AND V_UPT_11 = 'Y' AND V_UPT_13 = 'N'
THEN
UPDATE "NAMSAN"."PENALTY_HEAD_INFO_DAMO"
SET "CAR_NO" = :NEW."CAR_NO",
"RESIDENT_NO" = :NEW."RESIDENT_NO",
"OWNER" = :NEW."OWNER",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"OWNER_TYPE_ID" = :NEW."OWNER_TYPE_ID",
"DISUSE_DATE" = :NEW."DISUSE_DATE",
"CAR_TYPE_ID" = :NEW."CAR_TYPE_ID",
"CAR_TYPE_NAME" = :NEW."CAR_TYPE_NAME",
"MAX_CARRY_CAPACITY" = :NEW."MAX_CARRY_CAPACITY",
"PANALTY_COUNT" = :NEW."PANALTY_COUNT",
"PENALTY_AMOUNT" = :NEW."PENALTY_AMOUNT",
"RECEIVE_AMOUNT" = :NEW."RECEIVE_AMOUNT",
"POST_NO1" = :NEW."POST_NO1",
"POST_NO2" = :NEW."POST_NO2",
"AFTER_CARS_NUMBER_1" = :NEW."AFTER_CARS_NUMBER_1",
"AFTER_CARS_NUMBER_2" = :NEW."AFTER_CARS_NUMBER_2",
"ADDRESS_NAME" = :NEW."ADDRESS_NAME",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"CAR_STATUS_ID" = :NEW."CAR_STATUS_ID",
"CAR_STATUS_DATE" = :NEW."CAR_STATUS_DATE",
"CARNUM_CHANGE_DATE" = :NEW."CARNUM_CHANGE_DATE",
"AFTER_CARS_NUMBER_3" = :NEW."AFTER_CARS_NUMBER_3"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_5 = 'Y' AND V_UPT_11 = 'Y' AND V_UPT_13 = 'Y'
THEN
UPDATE "NAMSAN"."PENALTY_HEAD_INFO_DAMO"
SET "CAR_NO" = :NEW."CAR_NO",
"RESIDENT_NO" = :NEW."RESIDENT_NO",
"OWNER" = :NEW."OWNER",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"OWNER_TYPE_ID" = :NEW."OWNER_TYPE_ID",
"DISUSE_DATE" = :NEW."DISUSE_DATE",
"CAR_TYPE_ID" = :NEW."CAR_TYPE_ID",
"CAR_TYPE_NAME" = :NEW."CAR_TYPE_NAME",
"MAX_CARRY_CAPACITY" = :NEW."MAX_CARRY_CAPACITY",
"PANALTY_COUNT" = :NEW."PANALTY_COUNT",
"PENALTY_AMOUNT" = :NEW."PENALTY_AMOUNT",
"RECEIVE_AMOUNT" = :NEW."RECEIVE_AMOUNT",
"POST_NO1" = :NEW."POST_NO1",
"POST_NO2" = :NEW."POST_NO2",
"AFTER_CARS_NUMBER_1" = :NEW."AFTER_CARS_NUMBER_1",
"AFTER_CARS_NUMBER_2" = :NEW."AFTER_CARS_NUMBER_2",
"ADDRESS_NAME" = :NEW."ADDRESS_NAME",
"ADDRESS_NUMBER" = :NEW."ADDRESS_NUMBER",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"CAR_STATUS_ID" = :NEW."CAR_STATUS_ID",
"CAR_STATUS_DATE" = :NEW."CAR_STATUS_DATE",
"CARNUM_CHANGE_DATE" = :NEW."CARNUM_CHANGE_DATE",
"AFTER_CARS_NUMBER_3" = :NEW."AFTER_CARS_NUMBER_3",
"RENT_KBN" = :NEW."RENT_KBN"
WHERE ROWID = :OLD.PENTA__ROW__ID;
END IF;
END;
/
DROP TRIGGER NAMSAN.SIT_DEFAULT_FARE_READ_INFO;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE TRIGGER NAMSAN."SIT_DEFAULT_FARE_READ_INFO"
INSTEAD OF INSERT
ON "NAMSAN"."DEFAULT_FARE_READ_INFO_ROOT"
FOR EACH ROW
DECLARE
V_LENGTH1 NUMBER;
V_LENGTH2 NUMBER;
V_LENGTH3 NUMBER;
V_LENGTH4 NUMBER;
V_8 NUMBER (9) := 0;
V_RAW_8 RAW (2000);
V_13 VARCHAR2 (13);
V_RAW_13 RAW (2000);
V_15 CHAR (1) := 0;
V_RAW_15 RAW (2000);
V_19 CHAR (1) := 0;
V_RAW_19 RAW (2000);
V_28 NUMBER (3) := 0;
V_RAW_28 RAW (2000);
V_29 NUMBER (3) := 0;
V_RAW_29 RAW (2000);
V_30 NUMBER (3) := 0;
V_RAW_30 RAW (2000);
V_31 NUMBER (3) := 0;
V_RAW_31 RAW (2000);
V_33 CHAR (1) := 1;
V_RAW_33 RAW (2000);
V_34 CHAR (1) := 1;
V_RAW_34 RAW (2000);
V_36 CHAR (1) := 0;
V_RAW_36 RAW (2000);
V_41 CHAR (1) := 0;
V_RAW_41 RAW (2000);
V_42 CHAR (1) := 0;
V_RAW_42 RAW (2000);
V_43 CHAR (1) := 0;
V_RAW_43 RAW (2000);
V_44 CHAR (2) := '00';
V_RAW_44 RAW (2000);
V_47 CHAR (1) := 0;
V_RAW_47 RAW (2000);
V_48 CHAR (1) := '0';
V_RAW_48 RAW (2000);
V_50 NUMBER (5) := 0;
V_RAW_50 RAW (2000);
V_51 NUMBER (5) := 0;
V_RAW_51 RAW (2000);
V_54 NUMBER (5) := 0;
V_RAW_54 RAW (2000);
V_55 NUMBER (5) := 0;
V_RAW_55 RAW (2000);
BEGIN
V_8 := NVL (:NEW."SEQNO", V_8);
V_15 := NVL (:NEW."CARS_DATA_KBN", V_15);
V_19 := NVL (:NEW."READ_KBN", V_19);
V_28 := NVL (:NEW."X_POSITION", V_28);
V_29 := NVL (:NEW."Y_POSITION", V_29);
V_30 := NVL (:NEW."GARO_LEN", V_30);
V_31 := NVL (:NEW."SERO_LEN", V_31);
V_33 := NVL (:NEW."STOP_CHECK", V_33);
V_34 := NVL (:NEW."MOVE_CHECK", V_34);
V_36 := NVL (:NEW."MINI_CAR_KBN", V_36);
V_41 := NVL (:NEW."RE_CERTIFICATION", V_41);
V_42 := NVL (:NEW."C_HARD_READ", V_42);
V_43 := NVL (:NEW."RFID_YN", V_43);
V_44 := NVL (:NEW."RFID_KBN", V_44);
V_47 := NVL (:NEW."RFID_PENALTY_KBN", V_47);
V_48 := NVL (:NEW."RFID_PENALTY_REASON", V_48);
V_50 := NVL (:NEW."RFID_WEEK", V_50);
V_51 := NVL (:NEW."RFID_DAMAGE", V_51);
V_54 := NVL (:NEW."RFID_WEEK1", V_54);
V_55 := NVL (:NEW."RFID_DAMAGE1", V_55);
INSERT
INTO "NAMSAN"."DEFAULT_FARE_READ_INFO_DAMO" ("WORKER_ID",
"FARE_OFFICE_ID",
"BOOTH_ID",
"YEAR",
"MONTH",
"DAY",
"WORK_TIMES",
"SEQNO",
"READ_RESULT_ID",
"RESIDENT_DATE",
"COLLECT_ID",
"OWNER",
"RESIDENT_NO",
"READING_REASON",
"CARS_DATA_KBN",
"CAR_TYPE_NAME",
"READING_ID",
"CARS_MOVE_PICTURE_FILE",
"READ_KBN",
"CARS_STOP_PICTURE_FILE",
"CAR_NO",
"READER",
"EXEMPT_REASON_ID",
"READING_DATE",
"LAVY_NOTICE_ID",
"NOTCONFIRM_ID",
"REMARKS",
"X_POSITION",
"Y_POSITION",
"GARO_LEN",
"SERO_LEN",
"CARS_NO_PICTURE_FILE",
"STOP_CHECK",
"MOVE_CHECK",
"T_REAL_TIME",
"MINI_CAR_KBN",
"CREATE_DATE",
"CREATER",
"UPDATE_DATE",
"UPDATER",
"RE_CERTIFICATION",
"C_HARD_READ",
"RFID_YN",
"RFID_KBN",
"RFID_TAG1",
"RFID_TAG2",
"RFID_PENALTY_KBN",
"RFID_PENALTY_REASON",
"RFID_CAR",
"RFID_WEEK",
"RFID_DAMAGE",
"RFID_KBN1",
"RFID_CAR1",
"RFID_WEEK1",
"RFID_DAMAGE1")
VALUES (:NEW."WORKER_ID",
:NEW."FARE_OFFICE_ID",
:NEW."BOOTH_ID",
:NEW."YEAR",
:NEW."MONTH",
:NEW."DAY",
:NEW."WORK_TIMES",
V_8,
:NEW."READ_RESULT_ID",
:NEW."RESIDENT_DATE",
:NEW."COLLECT_ID",
:NEW."OWNER",
:NEW."RESIDENT_NO",
:NEW."READING_REASON",
V_15,
:NEW."CAR_TYPE_NAME",
:NEW."READING_ID",
:NEW."CARS_MOVE_PICTURE_FILE",
V_19,
:NEW."CARS_STOP_PICTURE_FILE",
:NEW."CAR_NO",
:NEW."READER",
:NEW."EXEMPT_REASON_ID",
:NEW."READING_DATE",
:NEW."LAVY_NOTICE_ID",
:NEW."NOTCONFIRM_ID",
:NEW."REMARKS",
V_28,
V_29,
V_30,
V_31,
:NEW."CARS_NO_PICTURE_FILE",
V_33,
V_34,
:NEW."T_REAL_TIME",
V_36,
:NEW."CREATE_DATE",
:NEW."CREATER",
:NEW."UPDATE_DATE",
:NEW."UPDATER",
V_41,
V_42,
V_43,
V_44,
:NEW."RFID_TAG1",
:NEW."RFID_TAG2",
V_47,
V_48,
:NEW."RFID_CAR",
V_50,
V_51,
:NEW."RFID_KBN1",
:NEW."RFID_CAR1",
V_54,
V_55);
END;
/
DROP TRIGGER NAMSAN.SUT_DEFAULT_FARE_READ_INFO;
/*
damo 사용 안하도록 변경
*/
/* Formatted on 2019/04/04 오후 3:03:40 (QP5 v5.215.12089.38647) */
CREATE OR REPLACE TRIGGER NAMSAN."SUT_DEFAULT_FARE_READ_INFO"
INSTEAD OF UPDATE
ON "NAMSAN"."DEFAULT_FARE_READ_INFO_ROOT"
FOR EACH ROW
DECLARE
V_LENGTH1 NUMBER;
V_LENGTH2 NUMBER;
V_LENGTH3 NUMBER;
V_LENGTH4 NUMBER;
V_13 VARCHAR2 (13);
V_RAW_13 RAW (2000);
V_OLD_13 VARCHAR2 (13);
V_UPT_13 VARCHAR2 (1);
BEGIN
V_UPT_13 := 'N';
V_OLD_13 := :OLD."RESIDENT_NO";
IF V_OLD_13 IS NULL AND :NEW."RESIDENT_NO" IS NOT NULL
THEN
V_UPT_13 := 'Y';
ELSIF V_OLD_13 IS NOT NULL
THEN
IF V_OLD_13 != :NEW."RESIDENT_NO" OR :NEW."RESIDENT_NO" IS NULL
THEN
V_UPT_13 := 'Y';
END IF;
END IF;
IF V_UPT_13 = 'N'
THEN
UPDATE "NAMSAN"."DEFAULT_FARE_READ_INFO_DAMO"
SET "WORKER_ID" = :NEW."WORKER_ID",
"FARE_OFFICE_ID" = :NEW."FARE_OFFICE_ID",
"BOOTH_ID" = :NEW."BOOTH_ID",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"WORK_TIMES" = :NEW."WORK_TIMES",
"SEQNO" = :NEW."SEQNO",
"READ_RESULT_ID" = :NEW."READ_RESULT_ID",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"COLLECT_ID" = :NEW."COLLECT_ID",
"OWNER" = :NEW."OWNER",
"READING_REASON" = :NEW."READING_REASON",
"CARS_DATA_KBN" = :NEW."CARS_DATA_KBN",
"CAR_TYPE_NAME" = :NEW."CAR_TYPE_NAME",
"READING_ID" = :NEW."READING_ID",
"CARS_MOVE_PICTURE_FILE" = :NEW."CARS_MOVE_PICTURE_FILE",
"READ_KBN" = :NEW."READ_KBN",
"CARS_STOP_PICTURE_FILE" = :NEW."CARS_STOP_PICTURE_FILE",
"CAR_NO" = :NEW."CAR_NO",
"READER" = :NEW."READER",
"EXEMPT_REASON_ID" = :NEW."EXEMPT_REASON_ID",
"READING_DATE" = :NEW."READING_DATE",
"LAVY_NOTICE_ID" = :NEW."LAVY_NOTICE_ID",
"NOTCONFIRM_ID" = :NEW."NOTCONFIRM_ID",
"REMARKS" = :NEW."REMARKS",
"X_POSITION" = :NEW."X_POSITION",
"Y_POSITION" = :NEW."Y_POSITION",
"GARO_LEN" = :NEW."GARO_LEN",
"SERO_LEN" = :NEW."SERO_LEN",
"CARS_NO_PICTURE_FILE" = :NEW."CARS_NO_PICTURE_FILE",
"STOP_CHECK" = :NEW."STOP_CHECK",
"MOVE_CHECK" = :NEW."MOVE_CHECK",
"T_REAL_TIME" = :NEW."T_REAL_TIME",
"MINI_CAR_KBN" = :NEW."MINI_CAR_KBN",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"RE_CERTIFICATION" = :NEW."RE_CERTIFICATION",
"C_HARD_READ" = :NEW."C_HARD_READ",
"RFID_YN" = :NEW."RFID_YN",
"RFID_KBN" = :NEW."RFID_KBN",
"RFID_TAG1" = :NEW."RFID_TAG1",
"RFID_TAG2" = :NEW."RFID_TAG2",
"RFID_PENALTY_KBN" = :NEW."RFID_PENALTY_KBN",
"RFID_PENALTY_REASON" = :NEW."RFID_PENALTY_REASON",
"RFID_CAR" = :NEW."RFID_CAR",
"RFID_WEEK" = :NEW."RFID_WEEK",
"RFID_DAMAGE" = :NEW."RFID_DAMAGE",
"RFID_KBN1" = :NEW."RFID_KBN1",
"RFID_CAR1" = :NEW."RFID_CAR1",
"RFID_WEEK1" = :NEW."RFID_WEEK1",
"RFID_DAMAGE1" = :NEW."RFID_DAMAGE1"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_13 = 'Y'
THEN
UPDATE "NAMSAN"."DEFAULT_FARE_READ_INFO_DAMO"
SET "WORKER_ID" = :NEW."WORKER_ID",
"FARE_OFFICE_ID" = :NEW."FARE_OFFICE_ID",
"BOOTH_ID" = :NEW."BOOTH_ID",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"WORK_TIMES" = :NEW."WORK_TIMES",
"SEQNO" = :NEW."SEQNO",
"READ_RESULT_ID" = :NEW."READ_RESULT_ID",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"COLLECT_ID" = :NEW."COLLECT_ID",
"OWNER" = :NEW."OWNER",
"RESIDENT_NO" = :NEW."RESIDENT_NO",
"READING_REASON" = :NEW."READING_REASON",
"CARS_DATA_KBN" = :NEW."CARS_DATA_KBN",
"CAR_TYPE_NAME" = :NEW."CAR_TYPE_NAME",
"READING_ID" = :NEW."READING_ID",
"CARS_MOVE_PICTURE_FILE" = :NEW."CARS_MOVE_PICTURE_FILE",
"READ_KBN" = :NEW."READ_KBN",
"CARS_STOP_PICTURE_FILE" = :NEW."CARS_STOP_PICTURE_FILE",
"CAR_NO" = :NEW."CAR_NO",
"READER" = :NEW."READER",
"EXEMPT_REASON_ID" = :NEW."EXEMPT_REASON_ID",
"READING_DATE" = :NEW."READING_DATE",
"LAVY_NOTICE_ID" = :NEW."LAVY_NOTICE_ID",
"NOTCONFIRM_ID" = :NEW."NOTCONFIRM_ID",
"REMARKS" = :NEW."REMARKS",
"X_POSITION" = :NEW."X_POSITION",
"Y_POSITION" = :NEW."Y_POSITION",
"GARO_LEN" = :NEW."GARO_LEN",
"SERO_LEN" = :NEW."SERO_LEN",
"CARS_NO_PICTURE_FILE" = :NEW."CARS_NO_PICTURE_FILE",
"STOP_CHECK" = :NEW."STOP_CHECK",
"MOVE_CHECK" = :NEW."MOVE_CHECK",
"T_REAL_TIME" = :NEW."T_REAL_TIME",
"MINI_CAR_KBN" = :NEW."MINI_CAR_KBN",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"RE_CERTIFICATION" = :NEW."RE_CERTIFICATION",
"C_HARD_READ" = :NEW."C_HARD_READ",
"RFID_YN" = :NEW."RFID_YN",
"RFID_KBN" = :NEW."RFID_KBN",
"RFID_TAG1" = :NEW."RFID_TAG1",
"RFID_TAG2" = :NEW."RFID_TAG2",
"RFID_PENALTY_KBN" = :NEW."RFID_PENALTY_KBN",
"RFID_PENALTY_REASON" = :NEW."RFID_PENALTY_REASON",
"RFID_CAR" = :NEW."RFID_CAR",
"RFID_WEEK" = :NEW."RFID_WEEK",
"RFID_DAMAGE" = :NEW."RFID_DAMAGE",
"RFID_KBN1" = :NEW."RFID_KBN1",
"RFID_CAR1" = :NEW."RFID_CAR1",
"RFID_WEEK1" = :NEW."RFID_WEEK1",
"RFID_DAMAGE1" = :NEW."RFID_DAMAGE1"
WHERE ROWID = :OLD.PENTA__ROW__ID;
END IF;
END;
/
DROP TRIGGER NAMSAN.SDT_DEFAULT_FARE_READ_INFO;
CREATE OR REPLACE TRIGGER NAMSAN."SDT_DEFAULT_FARE_READ_INFO" INSTEAD OF DELETE ON "NAMSAN"."DEFAULT_FARE_READ_INFO_ROOT" FOR EACH ROW
DECLARE BEGIN DELETE "NAMSAN"."DEFAULT_FARE_READ_INFO_DAMO" WHERE ROWID = :OLD.PENTA__ROW__ID ; END;
/
DROP TRIGGER NAMSAN.SDT_DEFAULT_CARS_TRANS_INFO;
CREATE OR REPLACE TRIGGER NAMSAN."SDT_DEFAULT_CARS_TRANS_INFO" INSTEAD OF DELETE ON "NAMSAN"."DEFAULT_CARS_TRANS_INFO_ROOT" FOR EACH ROW
DECLARE BEGIN DELETE "NAMSAN"."DEFAULT_CARS_TRANS_INFO_DAMO" WHERE ROWID = :OLD.PENTA__ROW__ID ; END;
/
DROP TRIGGER NAMSAN.SIT_OVERFARE_ORDER_INFO;
/*
damo 사용 안하도록 변경
*/
/* Formatted on 2019/04/04 오후 3:04:29 (QP5 v5.215.12089.38647) */
CREATE OR REPLACE TRIGGER NAMSAN."SIT_OVERFARE_ORDER_INFO"
INSTEAD OF INSERT
ON "NAMSAN"."OVERFARE_ORDER_INFO_ROOT"
FOR EACH ROW
DECLARE
V_LENGTH1 NUMBER;
V_LENGTH2 NUMBER;
V_LENGTH3 NUMBER;
V_LENGTH4 NUMBER;
V_2 VARCHAR2 (13);
V_RAW_2 RAW (2000);
V_5 NUMBER (5) := 0;
V_RAW_5 RAW (2000);
V_6 NUMBER (5) := 0;
V_RAW_6 RAW (2000);
V_14 VARCHAR2 (60);
V_RAW_14 RAW (2000);
V_15 VARCHAR2 (80);
V_RAW_15 RAW (2000);
V_16 NUMBER (3) := 0;
V_RAW_16 RAW (2000);
V_17 NUMBER (3) := 0;
V_RAW_17 RAW (2000);
V_18 NUMBER (3) := 0;
V_RAW_18 RAW (2000);
V_19 NUMBER (5) := 0;
V_RAW_19 RAW (2000);
V_20 CHAR (1) := 1;
V_RAW_20 RAW (2000);
BEGIN
V_5 := NVL (:NEW."OVERFARE_AMOUNT", V_5);
V_6 := NVL (:NEW."REFUND_AMOUNT", V_6);
V_16 := NVL (:NEW."OVERFARE_COUNT", V_16);
V_17 := NVL (:NEW."REFUND_COUNT", V_17);
V_18 := NVL (:NEW."CITY_TAXES_COUNT", V_18);
V_19 := NVL (:NEW."CITY_TAXES_AMONT", V_19);
V_20 := NVL (:NEW."TRANS_FILE_CREATE_YN", V_20);
INSERT INTO "NAMSAN"."OVERFARE_ORDER_INFO_DAMO" ("CAR_NO",
"RESIDENT_NO",
"OWNER",
"RESIDENT_DATE",
"OVERFARE_AMOUNT",
"REFUND_AMOUNT",
"FILE_KIND_ID",
"DATA_SEQNO",
"YEAR",
"MONTH",
"DAY",
"POST_NO1",
"POST_NO2",
"ADDRESS_NAME",
"ADDRESS_NUMBER",
"OVERFARE_COUNT",
"REFUND_COUNT",
"CITY_TAXES_COUNT",
"CITY_TAXES_AMONT",
"TRANS_FILE_CREATE_YN",
"CAR_TYPE_NAME",
"CREATE_DATE",
"CREATER",
"UPDATE_DATE",
"UPDATER")
VALUES (:NEW."CAR_NO",
:NEW."RESIDENT_NO",
:NEW."OWNER",
:NEW."RESIDENT_DATE",
V_5,
V_6,
:NEW."FILE_KIND_ID",
:NEW."DATA_SEQNO",
:NEW."YEAR",
:NEW."MONTH",
:NEW."DAY",
:NEW."POST_NO1",
:NEW."POST_NO2",
:NEW."ADDRESS_NAME",
:NEW."ADDRESS_NUMBER",
V_16,
V_17,
V_18,
V_19,
V_20,
:NEW."CAR_TYPE_NAME",
:NEW."CREATE_DATE",
:NEW."CREATER",
:NEW."UPDATE_DATE",
:NEW."UPDATER");
END;
/
DROP TRIGGER NAMSAN.SIT_PENALTY_HEAD_INFO;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE TRIGGER NAMSAN."SIT_PENALTY_HEAD_INFO"
INSTEAD OF INSERT
ON "NAMSAN"."PENALTY_HEAD_INFO_ROOT"
FOR EACH ROW
DECLARE
V_LENGTH1 NUMBER;
V_LENGTH2 NUMBER;
V_LENGTH3 NUMBER;
V_LENGTH4 NUMBER;
V_2 NUMBER (6) := 0;
V_20 NUMBER (3) := 0;
V_21 NUMBER (9) := 0;
V_RAW_2 RAW (2000);
V_5 VARCHAR2 (13);
V_7 CHAR (1) := 0;
V_RAW_5 RAW (2000);
V_8 NUMBER (2) := 0;
V_RAW_8 RAW (2000);
V_11 VARCHAR2 (60);
V_RAW_11 RAW (2000);
V_13 VARCHAR2 (80);
V_RAW_13 RAW (2000);
V_18 CHAR (2) := '00';
V_RAW_18 RAW (2000);
BEGIN
V_18 := NVL (:NEW."CAR_STATUS_ID", V_18);
INSERT INTO "NAMSAN"."PENALTY_HEAD_INFO_DAMO" ("CAR_NO",
"OWNER",
"RESIDENT_NO",
"RESIDENT_DATE",
"OWNER_TYPE_ID",
"DISUSE_DATE",
"CAR_TYPE_ID",
"CAR_TYPE_NAME",
"MAX_CARRY_CAPACITY",
"PANALTY_COUNT",
"PENALTY_AMOUNT",
"RECEIVE_AMOUNT",
"POST_NO1",
"POST_NO2",
"ADDRESS_NAME",
"ADDRESS_NUMBER",
"AFTER_CARS_NUMBER_1",
"AFTER_CARS_NUMBER_2",
"CREATE_DATE",
"CREATER",
"UPDATE_DATE",
"UPDATER",
"CAR_STATUS_ID",
"CAR_STATUS_DATE",
"CARNUM_CHANGE_DATE",
"AFTER_CARS_NUMBER_3",
"RENT_KBN")
VALUES (:NEW."CAR_NO",
:NEW."OWNER",
:NEW."RESIDENT_NO",
:NEW."RESIDENT_DATE",
:NEW."OWNER_TYPE_ID",
:NEW."DISUSE_DATE",
:NEW."CAR_TYPE_ID",
:NEW."CAR_TYPE_NAME",
:NEW."MAX_CARRY_CAPACITY",
V_20,
V_21,
V_21,
:NEW."POST_NO1",
:NEW."POST_NO2",
:NEW."ADDRESS_NAME",
:NEW."ADDRESS_NUMBER",
:NEW."AFTER_CARS_NUMBER_1",
:NEW."AFTER_CARS_NUMBER_2",
:NEW."CREATE_DATE",
:NEW."CREATER",
:NEW."UPDATE_DATE",
:NEW."UPDATER",
V_18,
:NEW."CAR_STATUS_DATE",
:NEW."CARNUM_CHANGE_DATE",
:NEW."AFTER_CARS_NUMBER_3",
V_7);
END;
/
DROP TRIGGER NAMSAN.SUT_PENALTY_HISTORY_DETAIL_IN;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE TRIGGER NAMSAN."SUT_PENALTY_HISTORY_DETAIL_IN"
INSTEAD OF UPDATE
ON "NAMSAN"."PENALTY_HISTORY_DETAIL_I_ROOT"
FOR EACH ROW
DECLARE
V_LENGTH1 NUMBER;
V_LENGTH2 NUMBER;
V_LENGTH3 NUMBER;
V_LENGTH4 NUMBER;
V_9 VARCHAR2 (13);
V_RAW_9 RAW (2000);
V_OLD_9 VARCHAR2 (13);
V_UPT_9 VARCHAR2 (1);
BEGIN
V_UPT_9 := 'N';
V_OLD_9 := :OLD."RESIDENT_NO";
IF V_OLD_9 IS NULL AND :NEW."RESIDENT_NO" IS NOT NULL
THEN
V_UPT_9 := 'Y';
ELSIF V_OLD_9 IS NOT NULL
THEN
IF V_OLD_9 != :NEW."RESIDENT_NO" OR :NEW."RESIDENT_NO" IS NULL
THEN
V_UPT_9 := 'Y';
END IF;
END IF;
IF V_UPT_9 = 'N'
THEN
UPDATE "NAMSAN"."PENALTY_HISTORY_DETAIL_I_DAMO"
SET "STT_WORK_DATE" = :NEW."STT_WORK_DATE",
"END_WORK_DATE" = :NEW."END_WORK_DATE",
"PENALTY_MANAGE_YEAR" = :NEW."PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO" = :NEW."PENALTY_SEQNO",
"MOTION_ID" = :NEW."MOTION_ID",
"CAR_NO" = :NEW."CAR_NO",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"OWNER" = :NEW."OWNER",
"WORKER_ID" = :NEW."WORKER_ID",
"FARE_OFFICE_ID" = :NEW."FARE_OFFICE_ID",
"BOOTH_ID" = :NEW."BOOTH_ID",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"WORK_TIMES" = :NEW."WORK_TIMES",
"CANCEL_YN" = :NEW."CANCEL_YN",
"CANCEL_DATE" = :NEW."CANCEL_DATE",
"CANCEL_REASON" = :NEW."CANCEL_REASON",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"SEQNO" = :NEW."SEQNO",
"RENT_KBN" = :NEW."RENT_KBN"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_9 = 'Y'
THEN
UPDATE "NAMSAN"."PENALTY_HISTORY_DETAIL_I_DAMO"
SET "STT_WORK_DATE" = :NEW."STT_WORK_DATE",
"END_WORK_DATE" = :NEW."END_WORK_DATE",
"PENALTY_MANAGE_YEAR" = :NEW."PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO" = :NEW."PENALTY_SEQNO",
"MOTION_ID" = :NEW."MOTION_ID",
"CAR_NO" = :NEW."CAR_NO",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"OWNER" = :NEW."OWNER",
"RESIDENT_NO" = :NEW."RESIDENT_NO",
"WORKER_ID" = :NEW."WORKER_ID",
"FARE_OFFICE_ID" = :NEW."FARE_OFFICE_ID",
"BOOTH_ID" = :NEW."BOOTH_ID",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"WORK_TIMES" = :NEW."WORK_TIMES",
"CANCEL_YN" = :NEW."CANCEL_YN",
"CANCEL_DATE" = :NEW."CANCEL_DATE",
"CANCEL_REASON" = :NEW."CANCEL_REASON",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"SEQNO" = :NEW."SEQNO",
"RENT_KBN" = :NEW."RENT_KBN"
WHERE ROWID = :OLD.PENTA__ROW__ID;
END IF;
END;
/
DROP TRIGGER NAMSAN.SDT_PENALTY_HISTORY_DETAIL_IN;
CREATE OR REPLACE TRIGGER NAMSAN."SDT_PENALTY_HISTORY_DETAIL_IN" INSTEAD OF DELETE ON "NAMSAN"."PENALTY_HISTORY_DETAIL_I_ROOT" FOR EACH ROW
DECLARE BEGIN DELETE "NAMSAN"."PENALTY_HISTORY_DETAIL_I_DAMO" WHERE ROWID = :OLD.PENTA__ROW__ID ; END;
/
DROP TRIGGER NAMSAN.SIT_PENALTY_HISTORY_DETAIL_IN;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE TRIGGER NAMSAN."SIT_PENALTY_HISTORY_DETAIL_IN"
INSTEAD OF INSERT
ON "NAMSAN"."PENALTY_HISTORY_DETAIL_I_ROOT"
FOR EACH ROW
DECLARE
V_LENGTH1 NUMBER;
V_LENGTH2 NUMBER;
V_LENGTH3 NUMBER;
V_LENGTH4 NUMBER;
V_4 NUMBER (6) := 0;
V_RAW_4 RAW (2000);
V_9 VARCHAR2 (13);
V_RAW_9 RAW (2000);
V_RENT CHAR (1) := 0;
V_17 CHAR (1) := 0;
V_RAW_17 RAW (2000);
BEGIN
V_4 := NVL (:NEW."PENALTY_SEQNO", V_4);
V_17 := NVL (:NEW."CANCEL_YN", V_17);
INSERT
INTO "NAMSAN"."PENALTY_HISTORY_DETAIL_I_DAMO" ("STT_WORK_DATE",
"END_WORK_DATE",
"PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO",
"MOTION_ID",
"CAR_NO",
"RESIDENT_DATE",
"OWNER",
"RESIDENT_NO",
"WORKER_ID",
"FARE_OFFICE_ID",
"BOOTH_ID",
"YEAR",
"MONTH",
"DAY",
"WORK_TIMES",
"CANCEL_YN",
"CANCEL_DATE",
"CANCEL_REASON",
"CREATE_DATE",
"CREATER",
"UPDATE_DATE",
"UPDATER",
"SEQNO",
"RENT_KBN")
VALUES (:NEW."STT_WORK_DATE",
:NEW."END_WORK_DATE",
:NEW."PENALTY_MANAGE_YEAR",
V_4,
:NEW."MOTION_ID",
:NEW."CAR_NO",
:NEW."RESIDENT_DATE",
:NEW."OWNER",
:NEW."RESIDENT_NO",
:NEW."WORKER_ID",
:NEW."FARE_OFFICE_ID",
:NEW."BOOTH_ID",
:NEW."YEAR",
:NEW."MONTH",
:NEW."DAY",
:NEW."WORK_TIMES",
V_17,
:NEW."CANCEL_DATE",
:NEW."CANCEL_REASON",
:NEW."CREATE_DATE",
:NEW."CREATER",
:NEW."UPDATE_DATE",
:NEW."UPDATER",
:NEW."SEQNO",
:NEW."RENT_KBN");
END;
/
DROP TRIGGER NAMSAN.SIT_PENALTY_PUBLIC_INFO;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE TRIGGER NAMSAN."SIT_PENALTY_PUBLIC_INFO"
INSTEAD OF INSERT
ON "NAMSAN"."PENALTY_PUBLIC_INFO_ROOT"
FOR EACH ROW
DECLARE
V_LENGTH1 NUMBER;
V_LENGTH2 NUMBER;
V_LENGTH3 NUMBER;
V_LENGTH4 NUMBER;
V_4 VARCHAR2 (13);
V_RAW_4 RAW (2000);
V_25 VARCHAR2 (16);
V_RAW_25 RAW (2000);
V_28 VARCHAR2 (20);
V_RAW_28 RAW (2000);
V_29 VARCHAR2 (4);
V_RAW_29 RAW (2000);
V_33 VARCHAR2 (60);
V_RAW_33 RAW (2000);
BEGIN
INSERT INTO "NAMSAN"."PENALTY_PUBLIC_INFO_DAMO" ("PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO",
"OWNER",
"RESIDENT_NO",
"CAR_NO",
"WORKER_ID",
"FARE_OFFICE_ID",
"BOOTH_ID",
"YEAR",
"MONTH",
"DAY",
"WORK_TIMES",
"SEQNO",
"RETURN_DATE",
"LEVY_AMOUNT",
"MOTION_ID",
"MAKE_DATE",
"REG_DATE",
"CHANGE_DATE",
"REG_STATUS",
"CHANGE_REASON",
"GIGWAN",
"SIDO",
"SIGUNGU",
"DONG",
"RI",
"SAN",
"BUNJI",
"HO",
"TONG",
"BAN",
"BLOCK",
"SPECIAL",
"SP_DONG",
"SP_HO",
"ADDR_CODE",
"ZIP1",
"ZIP2",
"RETURN_REASON_ID",
"CREATE_DATE",
"CREATER",
"UPDATE_DATE",
"UPDATER")
VALUES (:NEW."PENALTY_MANAGE_YEAR",
:NEW."PENALTY_SEQNO",
:NEW."OWNER",
:NEW."RESIDENT_NO",
:NEW."CAR_NO",
:NEW."WORKER_ID",
:NEW."FARE_OFFICE_ID",
:NEW."BOOTH_ID",
:NEW."YEAR",
:NEW."MONTH",
:NEW."DAY",
:NEW."WORK_TIMES",
:NEW."SEQNO",
:NEW."RETURN_DATE",
:NEW."LEVY_AMOUNT",
:NEW."MOTION_ID",
:NEW."MAKE_DATE",
:NEW."REG_DATE",
:NEW."CHANGE_DATE",
:NEW."REG_STATUS",
:NEW."CHANGE_REASON",
:NEW."GIGWAN",
:NEW."SIDO",
:NEW."SIGUNGU",
:NEW."DONG",
:NEW."RI",
:NEW."SAN",
:NEW."BUNJI",
:NEW."HO",
:NEW."TONG",
:NEW."BAN",
:NEW."BLOCK",
:NEW."SPECIAL",
:NEW."SP_DONG",
:NEW."SP_HO",
:NEW."ADDR_CODE",
:NEW."ZIP1",
:NEW."ZIP2",
:NEW."RETURN_REASON_ID",
:NEW."CREATE_DATE",
:NEW."CREATER",
:NEW."UPDATE_DATE",
:NEW."UPDATER");
END;
/
DROP TRIGGER NAMSAN.SUT_PENALTY_PUBLIC_INFO;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE TRIGGER NAMSAN."SUT_PENALTY_PUBLIC_INFO"
INSTEAD OF UPDATE
ON "NAMSAN"."PENALTY_PUBLIC_INFO_ROOT"
FOR EACH ROW
DECLARE
V_LENGTH1 NUMBER;
V_LENGTH2 NUMBER;
V_LENGTH3 NUMBER;
V_LENGTH4 NUMBER;
V_4 VARCHAR2 (13);
V_RAW_4 RAW (2000);
V_OLD_4 VARCHAR2 (13);
V_UPT_4 VARCHAR2 (1);
V_25 VARCHAR2 (16);
V_RAW_25 RAW (2000);
V_OLD_25 VARCHAR2 (16);
V_UPT_25 VARCHAR2 (1);
V_28 VARCHAR2 (20);
V_RAW_28 RAW (2000);
V_OLD_28 VARCHAR2 (20);
V_UPT_28 VARCHAR2 (1);
V_29 VARCHAR2 (4);
V_RAW_29 RAW (2000);
V_OLD_29 VARCHAR2 (4);
V_UPT_29 VARCHAR2 (1);
V_33 VARCHAR2 (60);
V_RAW_33 RAW (2000);
V_OLD_33 VARCHAR2 (60);
V_UPT_33 VARCHAR2 (1);
BEGIN
V_UPT_4 := 'N';
V_OLD_4 := :OLD."RESIDENT_NO";
IF V_OLD_4 IS NULL AND :NEW."RESIDENT_NO" IS NOT NULL
THEN
V_UPT_4 := 'Y';
ELSIF V_OLD_4 IS NOT NULL
THEN
IF V_OLD_4 != :NEW."RESIDENT_NO" OR :NEW."RESIDENT_NO" IS NULL
THEN
V_UPT_4 := 'Y';
END IF;
END IF;
V_UPT_25 := 'N';
V_OLD_25 := :OLD."DONG";
IF V_OLD_25 IS NULL AND :NEW."DONG" IS NOT NULL
THEN
V_UPT_25 := 'Y';
ELSIF V_OLD_25 IS NOT NULL
THEN
IF V_OLD_25 != :NEW."DONG" OR :NEW."DONG" IS NULL
THEN
V_UPT_25 := 'Y';
END IF;
END IF;
V_UPT_28 := 'N';
V_OLD_28 := :OLD."BUNJI";
IF V_OLD_28 IS NULL AND :NEW."BUNJI" IS NOT NULL
THEN
V_UPT_28 := 'Y';
ELSIF V_OLD_28 IS NOT NULL
THEN
IF V_OLD_28 != :NEW."BUNJI" OR :NEW."BUNJI" IS NULL
THEN
V_UPT_28 := 'Y';
END IF;
END IF;
V_UPT_29 := 'N';
V_OLD_29 := :OLD."HO";
IF V_OLD_29 IS NULL AND :NEW."HO" IS NOT NULL
THEN
V_UPT_29 := 'Y';
ELSIF V_OLD_29 IS NOT NULL
THEN
IF V_OLD_29 != :NEW."HO" OR :NEW."HO" IS NULL
THEN
V_UPT_29 := 'Y';
END IF;
END IF;
V_UPT_33 := 'N';
V_OLD_33 := :OLD."SPECIAL";
IF V_OLD_33 IS NULL AND :NEW."SPECIAL" IS NOT NULL
THEN
V_UPT_33 := 'Y';
ELSIF V_OLD_33 IS NOT NULL
THEN
IF V_OLD_33 != :NEW."SPECIAL" OR :NEW."SPECIAL" IS NULL
THEN
V_UPT_33 := 'Y';
END IF;
END IF;
UPDATE "NAMSAN"."PENALTY_PUBLIC_INFO_DAMO"
SET "PENALTY_MANAGE_YEAR" = :NEW."PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO" = :NEW."PENALTY_SEQNO",
"OWNER" = :NEW."OWNER",
"CAR_NO" = :NEW."CAR_NO",
"WORKER_ID" = :NEW."WORKER_ID",
"FARE_OFFICE_ID" = :NEW."FARE_OFFICE_ID",
"BOOTH_ID" = :NEW."BOOTH_ID",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"WORK_TIMES" = :NEW."WORK_TIMES",
"SEQNO" = :NEW."SEQNO",
"RETURN_DATE" = :NEW."RETURN_DATE",
"LEVY_AMOUNT" = :NEW."LEVY_AMOUNT",
"MOTION_ID" = :NEW."MOTION_ID",
"MAKE_DATE" = :NEW."MAKE_DATE",
"REG_DATE" = :NEW."REG_DATE",
"CHANGE_DATE" = :NEW."CHANGE_DATE",
"REG_STATUS" = :NEW."REG_STATUS",
"CHANGE_REASON" = :NEW."CHANGE_REASON",
"GIGWAN" = :NEW."GIGWAN",
"SIDO" = :NEW."SIDO",
"SIGUNGU" = :NEW."SIGUNGU",
"RI" = :NEW."RI",
"SAN" = :NEW."SAN",
"TONG" = :NEW."TONG",
"BAN" = :NEW."BAN",
"BLOCK" = :NEW."BLOCK",
"SP_DONG" = :NEW."SP_DONG",
"SP_HO" = :NEW."SP_HO",
"ADDR_CODE" = :NEW."ADDR_CODE",
"ZIP1" = :NEW."ZIP1",
"ZIP2" = :NEW."ZIP2",
"RETURN_REASON_ID" = :NEW."RETURN_REASON_ID",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER"
WHERE ROWID = :OLD.PENTA__ROW__ID;
IF V_UPT_4 = 'Y'
THEN
UPDATE "NAMSAN"."PENALTY_PUBLIC_INFO_DAMO"
SET "RESIDENT_NO" = :NEW."RESIDENT_NO"
WHERE ROWID = :OLD.PENTA__ROW__ID;
END IF;
IF V_UPT_25 = 'Y'
THEN
UPDATE "NAMSAN"."PENALTY_PUBLIC_INFO_DAMO"
SET "DONG" = :NEW."DONG"
WHERE ROWID = :OLD.PENTA__ROW__ID;
END IF;
IF V_UPT_28 = 'Y'
THEN
UPDATE "NAMSAN"."PENALTY_PUBLIC_INFO_DAMO"
SET "BUNJI" = :NEW."BUNJI"
WHERE ROWID = :OLD.PENTA__ROW__ID;
END IF;
IF V_UPT_29 = 'Y'
THEN
UPDATE "NAMSAN"."PENALTY_PUBLIC_INFO_DAMO"
SET "HO" = :NEW."HO"
WHERE ROWID = :OLD.PENTA__ROW__ID;
END IF;
IF V_UPT_33 = 'Y'
THEN
UPDATE "NAMSAN"."PENALTY_PUBLIC_INFO_DAMO"
SET "SPECIAL" = :NEW."SPECIAL"
WHERE ROWID = :OLD.PENTA__ROW__ID;
END IF;
END;
/
DROP TRIGGER NAMSAN.SDT_PENALTY_PUBLIC_INFO;
CREATE OR REPLACE TRIGGER NAMSAN."SDT_PENALTY_PUBLIC_INFO" INSTEAD OF DELETE ON "NAMSAN"."PENALTY_PUBLIC_INFO_ROOT" FOR EACH ROW
DECLARE BEGIN DELETE "NAMSAN"."PENALTY_PUBLIC_INFO_DAMO" WHERE ROWID = :OLD.PENTA__ROW__ID ; END;
/
DROP TRIGGER NAMSAN.SIT_PENALTY_HISTORY_INFO;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE TRIGGER NAMSAN."SIT_PENALTY_HISTORY_INFO"
INSTEAD OF INSERT
ON "NAMSAN"."PENALTY_HISTORY_INFO_ROOT"
FOR EACH ROW
DECLARE
V_LENGTH1 NUMBER;
V_LENGTH2 NUMBER;
V_LENGTH3 NUMBER;
V_LENGTH4 NUMBER;
V_2 NUMBER (6) := 0;
V_RAW_2 RAW (2000);
V_3 NUMBER (2) := 0;
V_RAW_3 RAW (2000);
V_9 NUMBER (6) := 0;
V_RAW_9 RAW (2000);
V_20 CHAR (2) := 0;
V_RAW_20 RAW (2000);
V_21 VARCHAR2 (13);
V_RAW_21 RAW (2000);
V_22 NUMBER (5) := 0;
V_RAW_22 RAW (2000);
V_23 NUMBER (5) := 0;
V_RAW_23 RAW (2000);
V_28 NUMBER (5) := 0;
V_RAW_28 RAW (2000);
V_30 CHAR (1) := 0;
V_RAW_30 RAW (2000);
V_38 CHAR (1) := 0;
V_RAW_38 RAW (2000);
V_40 CHAR (1) := 0;
V_RAW_40 RAW (2000);
V_44 NUMBER (6) := 0;
V_RAW_44 RAW (2000);
V_46 CHAR (1) := 0;
V_RAW_46 RAW (2000);
V_55 CHAR (2) := '00';
V_RAW_55 RAW (2000);
V_56 CHAR (1) := 0;
V_RAW_56 RAW (2000);
V_58 CHAR (1) := 0;
V_RAW_58 RAW (2000);
BEGIN
V_2 := NVL (:NEW."PENALTY_SEQNO", V_2);
V_3 := NVL (:NEW."UPDATE_SEQNO", V_3);
V_9 := NVL (:NEW."LEVY_SEQNO", V_9);
V_20 := NVL (:NEW."NOTICE_ID", V_20);
V_22 := NVL (:NEW."FINE_AMOUNT", V_22);
V_23 := NVL (:NEW."LAVY_AMOUNT", V_23);
V_28 := NVL (:NEW."PASS_AMOUNT", V_28);
V_30 := NVL (:NEW."SEISURE_YN", V_30);
V_38 := NVL (:NEW."RECEIPT_YN", V_38);
V_40 := NVL (:NEW."DOUBLE_RECEIPT_YN", V_40);
V_44 := NVL (:NEW."SEIZURE_MANAGE_NO", V_44);
V_46 := NVL (:NEW."SEIZURE_TERMI_YN", V_46);
V_55 := NVL (:NEW."RFID_KBN", V_55);
V_56 := NVL (:NEW."CAR_CHANGE_YN", V_56);
V_58 := NVL (:NEW."RFID_PENALTY_KBN", V_58);
INSERT
INTO "NAMSAN"."PENALTY_HISTORY_INFO_DAMO" ("PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO",
"UPDATE_SEQNO",
"WORKER_ID",
"FARE_OFFICE_ID",
"STT_DATE",
"UPDATE_HISTORY_DATE",
"LEVY_MANAGE_YEAR",
"LEVY_SEQNO",
"BOOTH_ID",
"COLLECT_ID",
"YEAR",
"MONTH",
"DAY",
"WORK_TIMES",
"CAR_NO",
"CARD_ISSUE_ID",
"NOTICE_WRONG_ID",
"OWNER",
"NOTICE_ID",
"RESIDENT_NO",
"FINE_AMOUNT",
"LAVY_AMOUNT",
"MOTION_ID",
"NOTICE_DATE",
"NOTICE_WRONG_DATE",
"RESIDENT_DATE",
"PASS_AMOUNT",
"NOTICE_WRONG_REASON_ID",
"SEISURE_YN",
"SEIZURE_DATE",
"SEIZURE_ID",
"NOTICE_CHOOSE_ID",
"SEIZURE_WRONG_ID",
"SEIZURE_WRONG_DATE",
"SEIZURE_TERMI_DATE",
"SEIZURE_TERMI",
"RECEIPT_YN",
"SEIZURE_WRONG_REASON_ID",
"DOUBLE_RECEIPT_YN",
"SEIZURE_TRMI_YN",
"DOUBLE_RECEIPT_DATE",
"SEIZURE_MANAGE_YEAR",
"SEIZURE_MANAGE_NO",
"BFORE_MOTION_ID",
"SEIZURE_TERMI_YN",
"SEIZURE_WRONG_REASON",
"NOTICE_WRONG_REASON",
"NOTICE_CHOOSE_DATE",
"CREATE_DATE",
"CREATER",
"UPDATE_DATE",
"UPDATER",
"SEQNO",
"RFID_KBN",
"CAR_CHANGE_YN",
"CAR_NO_AFTER",
"RFID_PENALTY_KBN")
VALUES (:NEW."PENALTY_MANAGE_YEAR",
V_2,
V_3,
:NEW."WORKER_ID",
:NEW."FARE_OFFICE_ID",
:NEW."STT_DATE",
:NEW."UPDATE_HISTORY_DATE",
:NEW."LEVY_MANAGE_YEAR",
V_9,
:NEW."BOOTH_ID",
:NEW."COLLECT_ID",
:NEW."YEAR",
:NEW."MONTH",
:NEW."DAY",
:NEW."WORK_TIMES",
:NEW."CAR_NO",
:NEW."CARD_ISSUE_ID",
:NEW."NOTICE_WRONG_ID",
:NEW."OWNER",
V_20,
:NEW."RESIDENT_NO",
V_22,
V_23,
:NEW."MOTION_ID",
:NEW."NOTICE_DATE",
:NEW."NOTICE_WRONG_DATE",
:NEW."RESIDENT_DATE",
V_28,
:NEW."NOTICE_WRONG_REASON_ID",
V_30,
:NEW."SEIZURE_DATE",
:NEW."SEIZURE_ID",
:NEW."NOTICE_CHOOSE_ID",
:NEW."SEIZURE_WRONG_ID",
:NEW."SEIZURE_WRONG_DATE",
:NEW."SEIZURE_TERMI_DATE",
:NEW."SEIZURE_TERMI",
V_38,
:NEW."SEIZURE_WRONG_REASON_ID",
V_40,
:NEW."SEIZURE_TRMI_YN",
:NEW."DOUBLE_RECEIPT_DATE",
:NEW."SEIZURE_MANAGE_YEAR",
V_44,
:NEW."BFORE_MOTION_ID",
V_46,
:NEW."SEIZURE_WRONG_REASON",
:NEW."NOTICE_WRONG_REASON",
:NEW."NOTICE_CHOOSE_DATE",
:NEW."CREATE_DATE",
:NEW."CREATER",
:NEW."UPDATE_DATE",
:NEW."UPDATER",
:NEW."SEQNO",
V_55,
V_56,
:NEW."CAR_NO_AFTER",
V_58);
END;
/
DROP TRIGGER NAMSAN.SUT_PENALTY_HISTORY_INFO;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE TRIGGER NAMSAN."SUT_PENALTY_HISTORY_INFO"
INSTEAD OF UPDATE
ON "NAMSAN"."PENALTY_HISTORY_INFO_ROOT"
FOR EACH ROW
DECLARE
V_LENGTH1 NUMBER;
V_LENGTH2 NUMBER;
V_LENGTH3 NUMBER;
V_LENGTH4 NUMBER;
V_21 VARCHAR2 (13);
V_RAW_21 RAW (2000);
V_OLD_21 VARCHAR2 (13);
V_UPT_21 VARCHAR2 (1);
BEGIN
V_UPT_21 := 'N';
V_OLD_21 := :OLD."RESIDENT_NO";
IF V_OLD_21 IS NULL AND :NEW."RESIDENT_NO" IS NOT NULL
THEN
V_UPT_21 := 'Y';
ELSIF V_OLD_21 IS NOT NULL
THEN
IF V_OLD_21 != :NEW."RESIDENT_NO" OR :NEW."RESIDENT_NO" IS NULL
THEN
V_UPT_21 := 'Y';
END IF;
END IF;
IF V_UPT_21 = 'N'
THEN
UPDATE "NAMSAN"."PENALTY_HISTORY_INFO_DAMO"
SET "PENALTY_MANAGE_YEAR" = :NEW."PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO" = :NEW."PENALTY_SEQNO",
"UPDATE_SEQNO" = :NEW."UPDATE_SEQNO",
"WORKER_ID" = :NEW."WORKER_ID",
"FARE_OFFICE_ID" = :NEW."FARE_OFFICE_ID",
"STT_DATE" = :NEW."STT_DATE",
"UPDATE_HISTORY_DATE" = :NEW."UPDATE_HISTORY_DATE",
"LEVY_MANAGE_YEAR" = :NEW."LEVY_MANAGE_YEAR",
"LEVY_SEQNO" = :NEW."LEVY_SEQNO",
"BOOTH_ID" = :NEW."BOOTH_ID",
"COLLECT_ID" = :NEW."COLLECT_ID",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"WORK_TIMES" = :NEW."WORK_TIMES",
"CAR_NO" = :NEW."CAR_NO",
"CARD_ISSUE_ID" = :NEW."CARD_ISSUE_ID",
"NOTICE_WRONG_ID" = :NEW."NOTICE_WRONG_ID",
"OWNER" = :NEW."OWNER",
"NOTICE_ID" = :NEW."NOTICE_ID",
"FINE_AMOUNT" = :NEW."FINE_AMOUNT",
"LAVY_AMOUNT" = :NEW."LAVY_AMOUNT",
"MOTION_ID" = :NEW."MOTION_ID",
"NOTICE_DATE" = :NEW."NOTICE_DATE",
"NOTICE_WRONG_DATE" = :NEW."NOTICE_WRONG_DATE",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"PASS_AMOUNT" = :NEW."PASS_AMOUNT",
"NOTICE_WRONG_REASON_ID" = :NEW."NOTICE_WRONG_REASON_ID",
"SEISURE_YN" = :NEW."SEISURE_YN",
"SEIZURE_DATE" = :NEW."SEIZURE_DATE",
"SEIZURE_ID" = :NEW."SEIZURE_ID",
"NOTICE_CHOOSE_ID" = :NEW."NOTICE_CHOOSE_ID",
"SEIZURE_WRONG_ID" = :NEW."SEIZURE_WRONG_ID",
"SEIZURE_WRONG_DATE" = :NEW."SEIZURE_WRONG_DATE",
"SEIZURE_TERMI_DATE" = :NEW."SEIZURE_TERMI_DATE",
"SEIZURE_TERMI" = :NEW."SEIZURE_TERMI",
"RECEIPT_YN" = :NEW."RECEIPT_YN",
"SEIZURE_WRONG_REASON_ID" = :NEW."SEIZURE_WRONG_REASON_ID",
"DOUBLE_RECEIPT_YN" = :NEW."DOUBLE_RECEIPT_YN",
"SEIZURE_TRMI_YN" = :NEW."SEIZURE_TRMI_YN",
"DOUBLE_RECEIPT_DATE" = :NEW."DOUBLE_RECEIPT_DATE",
"SEIZURE_MANAGE_YEAR" = :NEW."SEIZURE_MANAGE_YEAR",
"SEIZURE_MANAGE_NO" = :NEW."SEIZURE_MANAGE_NO",
"BFORE_MOTION_ID" = :NEW."BFORE_MOTION_ID",
"SEIZURE_TERMI_YN" = :NEW."SEIZURE_TERMI_YN",
"SEIZURE_WRONG_REASON" = :NEW."SEIZURE_WRONG_REASON",
"NOTICE_WRONG_REASON" = :NEW."NOTICE_WRONG_REASON",
"NOTICE_CHOOSE_DATE" = :NEW."NOTICE_CHOOSE_DATE",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"SEQNO" = :NEW."SEQNO",
"RFID_KBN" = :NEW."RFID_KBN",
"CAR_CHANGE_YN" = :NEW."CAR_CHANGE_YN",
"CAR_NO_AFTER" = :NEW."CAR_NO_AFTER",
"RFID_PENALTY_KBN" = :NEW."RFID_PENALTY_KBN"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_21 = 'Y'
THEN
UPDATE "NAMSAN"."PENALTY_HISTORY_INFO_DAMO"
SET "PENALTY_MANAGE_YEAR" = :NEW."PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO" = :NEW."PENALTY_SEQNO",
"UPDATE_SEQNO" = :NEW."UPDATE_SEQNO",
"WORKER_ID" = :NEW."WORKER_ID",
"FARE_OFFICE_ID" = :NEW."FARE_OFFICE_ID",
"STT_DATE" = :NEW."STT_DATE",
"UPDATE_HISTORY_DATE" = :NEW."UPDATE_HISTORY_DATE",
"LEVY_MANAGE_YEAR" = :NEW."LEVY_MANAGE_YEAR",
"LEVY_SEQNO" = :NEW."LEVY_SEQNO",
"BOOTH_ID" = :NEW."BOOTH_ID",
"COLLECT_ID" = :NEW."COLLECT_ID",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"WORK_TIMES" = :NEW."WORK_TIMES",
"CAR_NO" = :NEW."CAR_NO",
"CARD_ISSUE_ID" = :NEW."CARD_ISSUE_ID",
"NOTICE_WRONG_ID" = :NEW."NOTICE_WRONG_ID",
"OWNER" = :NEW."OWNER",
"NOTICE_ID" = :NEW."NOTICE_ID",
"RESIDENT_NO" = :NEW."RESIDENT_NO",
"FINE_AMOUNT" = :NEW."FINE_AMOUNT",
"LAVY_AMOUNT" = :NEW."LAVY_AMOUNT",
"MOTION_ID" = :NEW."MOTION_ID",
"NOTICE_DATE" = :NEW."NOTICE_DATE",
"NOTICE_WRONG_DATE" = :NEW."NOTICE_WRONG_DATE",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"PASS_AMOUNT" = :NEW."PASS_AMOUNT",
"NOTICE_WRONG_REASON_ID" = :NEW."NOTICE_WRONG_REASON_ID",
"SEISURE_YN" = :NEW."SEISURE_YN",
"SEIZURE_DATE" = :NEW."SEIZURE_DATE",
"SEIZURE_ID" = :NEW."SEIZURE_ID",
"NOTICE_CHOOSE_ID" = :NEW."NOTICE_CHOOSE_ID",
"SEIZURE_WRONG_ID" = :NEW."SEIZURE_WRONG_ID",
"SEIZURE_WRONG_DATE" = :NEW."SEIZURE_WRONG_DATE",
"SEIZURE_TERMI_DATE" = :NEW."SEIZURE_TERMI_DATE",
"SEIZURE_TERMI" = :NEW."SEIZURE_TERMI",
"RECEIPT_YN" = :NEW."RECEIPT_YN",
"SEIZURE_WRONG_REASON_ID" = :NEW."SEIZURE_WRONG_REASON_ID",
"DOUBLE_RECEIPT_YN" = :NEW."DOUBLE_RECEIPT_YN",
"SEIZURE_TRMI_YN" = :NEW."SEIZURE_TRMI_YN",
"DOUBLE_RECEIPT_DATE" = :NEW."DOUBLE_RECEIPT_DATE",
"SEIZURE_MANAGE_YEAR" = :NEW."SEIZURE_MANAGE_YEAR",
"SEIZURE_MANAGE_NO" = :NEW."SEIZURE_MANAGE_NO",
"BFORE_MOTION_ID" = :NEW."BFORE_MOTION_ID",
"SEIZURE_TERMI_YN" = :NEW."SEIZURE_TERMI_YN",
"SEIZURE_WRONG_REASON" = :NEW."SEIZURE_WRONG_REASON",
"NOTICE_WRONG_REASON" = :NEW."NOTICE_WRONG_REASON",
"NOTICE_CHOOSE_DATE" = :NEW."NOTICE_CHOOSE_DATE",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"SEQNO" = :NEW."SEQNO",
"RFID_KBN" = :NEW."RFID_KBN",
"CAR_CHANGE_YN" = :NEW."CAR_CHANGE_YN",
"CAR_NO_AFTER" = :NEW."CAR_NO_AFTER",
"RFID_PENALTY_KBN" = :NEW."RFID_PENALTY_KBN"
WHERE ROWID = :OLD.PENTA__ROW__ID;
END IF;
END;
/
DROP TRIGGER NAMSAN.SDT_PENALTY_HISTORY_INFO;
CREATE OR REPLACE TRIGGER NAMSAN."SDT_PENALTY_HISTORY_INFO" INSTEAD OF DELETE ON "NAMSAN"."PENALTY_HISTORY_INFO_ROOT" FOR EACH ROW
DECLARE BEGIN DELETE "NAMSAN"."PENALTY_HISTORY_INFO_DAMO" WHERE ROWID = :OLD.PENTA__ROW__ID ; END;
/
DROP TRIGGER NAMSAN.SIT_CIVIL_ACCEPT_INFO;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE TRIGGER NAMSAN."SIT_CIVIL_ACCEPT_INFO"
INSTEAD OF INSERT
ON "NAMSAN"."CIVIL_ACCEPT_INFO_ROOT"
FOR EACH ROW
DECLARE
V_LENGTH1 NUMBER;
V_LENGTH2 NUMBER;
V_LENGTH3 NUMBER;
V_LENGTH4 NUMBER;
V_2 NUMBER (6) := 0;
V_RAW_2 RAW (2000);
V_4 NUMBER (2) := 0;
V_RAW_4 RAW (2000);
V_9 VARCHAR2 (15);
V_RAW_9 RAW (2000);
V_14 CHAR (1) := 0;
V_RAW_14 RAW (2000);
V_16 VARCHAR2 (15);
V_RAW_16 RAW (2000);
V_17 CHAR (1) := 1;
V_RAW_17 RAW (2000);
BEGIN
V_2 := NVL (:NEW."PENALTY_SEQNO", V_2);
V_4 := NVL (:NEW."ACCEPT_SEQNO", V_4);
V_14 := NVL (:NEW."PROCESS_YN", V_14);
V_17 := NVL (:NEW."CIVIL_JOIN_KBN", V_17);
INSERT INTO "NAMSAN"."CIVIL_ACCEPT_INFO_DAMO" ("PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO",
"ACCEPT_DATE",
"ACCEPT_SEQNO",
"CIVIL_KIND",
"CIVIL_SUMMARY_ID",
"CIVIL_PERSON",
"CIVIL_PROCESS_ID",
"CIVIL_TEL_NO",
"CIVIL_ACCEPTER",
"CIVIL_VIA",
"CIVIL_SUMMARY_CONTENTS",
"REMARKS",
"PROCESS_YN",
"PROCESS_DELIVERY",
"CIVIL_PCS_NO",
"CIVIL_JOIN_KBN",
"CIVIL_FAX_NO",
"PROCESS_DATE",
"CIVIL_PROCES_CONTENTS",
"CIVIL_PROCESSER",
"CREATER",
"CREATE_DATE",
"UPDATE_DATE",
"UPDATER")
VALUES (:NEW."PENALTY_MANAGE_YEAR",
V_2,
:NEW."ACCEPT_DATE",
V_4,
:NEW."CIVIL_KIND",
:NEW."CIVIL_SUMMARY_ID",
:NEW."CIVIL_PERSON",
:NEW."CIVIL_PROCESS_ID",
:NEW."CIVIL_TEL_NO",
:NEW."CIVIL_ACCEPTER",
:NEW."CIVIL_VIA",
:NEW."CIVIL_SUMMARY_CONTENTS",
:NEW."REMARKS",
V_14,
:NEW."PROCESS_DELIVERY",
:NEW."CIVIL_PCS_NO",
V_17,
:NEW."CIVIL_FAX_NO",
:NEW."PROCESS_DATE",
:NEW."CIVIL_PROCES_CONTENTS",
:NEW."CIVIL_PROCESSER",
:NEW."CREATER",
:NEW."CREATE_DATE",
:NEW."UPDATE_DATE",
:NEW."UPDATER");
END;
/
DROP TRIGGER NAMSAN.SUT_CIVIL_ACCEPT_INFO;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE TRIGGER NAMSAN."SUT_CIVIL_ACCEPT_INFO"
INSTEAD OF UPDATE
ON "NAMSAN"."CIVIL_ACCEPT_INFO_ROOT"
FOR EACH ROW
DECLARE
V_LENGTH1 NUMBER;
V_LENGTH2 NUMBER;
V_LENGTH3 NUMBER;
V_LENGTH4 NUMBER;
V_9 VARCHAR2 (15);
V_RAW_9 RAW (2000);
V_OLD_9 VARCHAR2 (15);
V_UPT_9 VARCHAR2 (1);
V_16 VARCHAR2 (15);
V_RAW_16 RAW (2000);
V_OLD_16 VARCHAR2 (15);
V_UPT_16 VARCHAR2 (1);
BEGIN
V_UPT_9 := 'N';
V_OLD_9 := :OLD."CIVIL_TEL_NO";
IF V_OLD_9 IS NULL AND :NEW."CIVIL_TEL_NO" IS NOT NULL
THEN
V_UPT_9 := 'Y';
ELSIF V_OLD_9 IS NOT NULL
THEN
IF V_OLD_9 != :NEW."CIVIL_TEL_NO" OR :NEW."CIVIL_TEL_NO" IS NULL
THEN
V_UPT_9 := 'Y';
END IF;
END IF;
V_UPT_16 := 'N';
V_OLD_16 := :OLD."CIVIL_PCS_NO";
IF V_OLD_16 IS NULL AND :NEW."CIVIL_PCS_NO" IS NOT NULL
THEN
V_UPT_16 := 'Y';
ELSIF V_OLD_16 IS NOT NULL
THEN
IF V_OLD_16 != :NEW."CIVIL_PCS_NO" OR :NEW."CIVIL_PCS_NO" IS NULL
THEN
V_UPT_16 := 'Y';
END IF;
END IF;
IF V_UPT_9 = 'N' AND V_UPT_16 = 'N'
THEN
UPDATE "NAMSAN"."CIVIL_ACCEPT_INFO_DAMO"
SET "PENALTY_MANAGE_YEAR" = :NEW."PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO" = :NEW."PENALTY_SEQNO",
"ACCEPT_DATE" = :NEW."ACCEPT_DATE",
"ACCEPT_SEQNO" = :NEW."ACCEPT_SEQNO",
"CIVIL_KIND" = :NEW."CIVIL_KIND",
"CIVIL_SUMMARY_ID" = :NEW."CIVIL_SUMMARY_ID",
"CIVIL_PERSON" = :NEW."CIVIL_PERSON",
"CIVIL_PROCESS_ID" = :NEW."CIVIL_PROCESS_ID",
"CIVIL_ACCEPTER" = :NEW."CIVIL_ACCEPTER",
"CIVIL_VIA" = :NEW."CIVIL_VIA",
"CIVIL_SUMMARY_CONTENTS" = :NEW."CIVIL_SUMMARY_CONTENTS",
"REMARKS" = :NEW."REMARKS",
"PROCESS_YN" = :NEW."PROCESS_YN",
"PROCESS_DELIVERY" = :NEW."PROCESS_DELIVERY",
"CIVIL_JOIN_KBN" = :NEW."CIVIL_JOIN_KBN",
"CIVIL_FAX_NO" = :NEW."CIVIL_FAX_NO",
"PROCESS_DATE" = :NEW."PROCESS_DATE",
"CIVIL_PROCES_CONTENTS" = :NEW."CIVIL_PROCES_CONTENTS",
"CIVIL_PROCESSER" = :NEW."CIVIL_PROCESSER",
"CREATER" = :NEW."CREATER",
"CREATE_DATE" = :NEW."CREATE_DATE",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_9 = 'N' AND V_UPT_16 = 'Y'
THEN
UPDATE "NAMSAN"."CIVIL_ACCEPT_INFO_DAMO"
SET "PENALTY_MANAGE_YEAR" = :NEW."PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO" = :NEW."PENALTY_SEQNO",
"ACCEPT_DATE" = :NEW."ACCEPT_DATE",
"ACCEPT_SEQNO" = :NEW."ACCEPT_SEQNO",
"CIVIL_KIND" = :NEW."CIVIL_KIND",
"CIVIL_SUMMARY_ID" = :NEW."CIVIL_SUMMARY_ID",
"CIVIL_PERSON" = :NEW."CIVIL_PERSON",
"CIVIL_PROCESS_ID" = :NEW."CIVIL_PROCESS_ID",
"CIVIL_ACCEPTER" = :NEW."CIVIL_ACCEPTER",
"CIVIL_VIA" = :NEW."CIVIL_VIA",
"CIVIL_SUMMARY_CONTENTS" = :NEW."CIVIL_SUMMARY_CONTENTS",
"REMARKS" = :NEW."REMARKS",
"PROCESS_YN" = :NEW."PROCESS_YN",
"PROCESS_DELIVERY" = :NEW."PROCESS_DELIVERY",
"CIVIL_PCS_NO" = :NEW."CIVIL_PCS_NO",
"CIVIL_JOIN_KBN" = :NEW."CIVIL_JOIN_KBN",
"CIVIL_FAX_NO" = :NEW."CIVIL_FAX_NO",
"PROCESS_DATE" = :NEW."PROCESS_DATE",
"CIVIL_PROCES_CONTENTS" = :NEW."CIVIL_PROCES_CONTENTS",
"CIVIL_PROCESSER" = :NEW."CIVIL_PROCESSER",
"CREATER" = :NEW."CREATER",
"CREATE_DATE" = :NEW."CREATE_DATE",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_9 = 'Y' AND V_UPT_16 = 'N'
THEN
UPDATE "NAMSAN"."CIVIL_ACCEPT_INFO_DAMO"
SET "PENALTY_MANAGE_YEAR" = :NEW."PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO" = :NEW."PENALTY_SEQNO",
"ACCEPT_DATE" = :NEW."ACCEPT_DATE",
"ACCEPT_SEQNO" = :NEW."ACCEPT_SEQNO",
"CIVIL_KIND" = :NEW."CIVIL_KIND",
"CIVIL_SUMMARY_ID" = :NEW."CIVIL_SUMMARY_ID",
"CIVIL_PERSON" = :NEW."CIVIL_PERSON",
"CIVIL_PROCESS_ID" = :NEW."CIVIL_PROCESS_ID",
"CIVIL_TEL_NO" = :NEW."CIVIL_TEL_NO",
"CIVIL_ACCEPTER" = :NEW."CIVIL_ACCEPTER",
"CIVIL_VIA" = :NEW."CIVIL_VIA",
"CIVIL_SUMMARY_CONTENTS" = :NEW."CIVIL_SUMMARY_CONTENTS",
"REMARKS" = :NEW."REMARKS",
"PROCESS_YN" = :NEW."PROCESS_YN",
"PROCESS_DELIVERY" = :NEW."PROCESS_DELIVERY",
"CIVIL_JOIN_KBN" = :NEW."CIVIL_JOIN_KBN",
"CIVIL_FAX_NO" = :NEW."CIVIL_FAX_NO",
"PROCESS_DATE" = :NEW."PROCESS_DATE",
"CIVIL_PROCES_CONTENTS" = :NEW."CIVIL_PROCES_CONTENTS",
"CIVIL_PROCESSER" = :NEW."CIVIL_PROCESSER",
"CREATER" = :NEW."CREATER",
"CREATE_DATE" = :NEW."CREATE_DATE",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_9 = 'Y' AND V_UPT_16 = 'Y'
THEN
UPDATE "NAMSAN"."CIVIL_ACCEPT_INFO_DAMO"
SET "PENALTY_MANAGE_YEAR" = :NEW."PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO" = :NEW."PENALTY_SEQNO",
"ACCEPT_DATE" = :NEW."ACCEPT_DATE",
"ACCEPT_SEQNO" = :NEW."ACCEPT_SEQNO",
"CIVIL_KIND" = :NEW."CIVIL_KIND",
"CIVIL_SUMMARY_ID" = :NEW."CIVIL_SUMMARY_ID",
"CIVIL_PERSON" = :NEW."CIVIL_PERSON",
"CIVIL_PROCESS_ID" = :NEW."CIVIL_PROCESS_ID",
"CIVIL_TEL_NO" = :NEW."CIVIL_TEL_NO",
"CIVIL_ACCEPTER" = :NEW."CIVIL_ACCEPTER",
"CIVIL_VIA" = :NEW."CIVIL_VIA",
"CIVIL_SUMMARY_CONTENTS" = :NEW."CIVIL_SUMMARY_CONTENTS",
"REMARKS" = :NEW."REMARKS",
"PROCESS_YN" = :NEW."PROCESS_YN",
"PROCESS_DELIVERY" = :NEW."PROCESS_DELIVERY",
"CIVIL_PCS_NO" = :NEW."CIVIL_PCS_NO",
"CIVIL_JOIN_KBN" = :NEW."CIVIL_JOIN_KBN",
"CIVIL_FAX_NO" = :NEW."CIVIL_FAX_NO",
"PROCESS_DATE" = :NEW."PROCESS_DATE",
"CIVIL_PROCES_CONTENTS" = :NEW."CIVIL_PROCES_CONTENTS",
"CIVIL_PROCESSER" = :NEW."CIVIL_PROCESSER",
"CREATER" = :NEW."CREATER",
"CREATE_DATE" = :NEW."CREATE_DATE",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER"
WHERE ROWID = :OLD.PENTA__ROW__ID;
END IF;
END;
/
DROP TRIGGER NAMSAN.SDT_CIVIL_ACCEPT_INFO;
CREATE OR REPLACE TRIGGER NAMSAN."SDT_CIVIL_ACCEPT_INFO" INSTEAD OF DELETE ON "NAMSAN"."CIVIL_ACCEPT_INFO_ROOT" FOR EACH ROW
DECLARE BEGIN DELETE "NAMSAN"."CIVIL_ACCEPT_INFO_DAMO" WHERE ROWID = :OLD.PENTA__ROW__ID ; END;
/
DROP TRIGGER NAMSAN.SUT_PENALTY_DETAIL_INFO;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE TRIGGER NAMSAN.SUT_PENALTY_DETAIL_INFO
INSTEAD OF UPDATE
ON NAMSAN.PENALTY_DETAIL_INFO_ROOT
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
V_LENGTH1 NUMBER;
V_LENGTH2 NUMBER;
V_LENGTH3 NUMBER;
V_LENGTH4 NUMBER;
V_9 VARCHAR2 (13);
V_RAW_9 RAW (2000);
V_OLD_9 VARCHAR2 (13);
V_UPT_9 VARCHAR2 (1);
BEGIN
V_UPT_9 := 'N';
V_OLD_9 := :OLD."RESIDENT_NO";
IF V_OLD_9 IS NULL AND :NEW."RESIDENT_NO" IS NOT NULL
THEN
V_UPT_9 := 'Y';
ELSIF V_OLD_9 IS NOT NULL
THEN
IF V_OLD_9 != :NEW."RESIDENT_NO" OR :NEW."RESIDENT_NO" IS NULL
THEN
V_UPT_9 := 'Y';
END IF;
END IF;
IF V_UPT_9 = 'N'
THEN
UPDATE "NAMSAN"."PENALTY_DETAIL_INFO_DAMO"
SET "PENALTY_MANAGE_YEAR" = :NEW."PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO" = :NEW."PENALTY_SEQNO",
"WORKER_ID" = :NEW."WORKER_ID",
"FARE_OFFICE_ID" = :NEW."FARE_OFFICE_ID",
"LEVY_MANAGE_YEAR" = :NEW."LEVY_MANAGE_YEAR",
"STT_DATE" = :NEW."STT_DATE",
"LEVY_SEQNO" = :NEW."LEVY_SEQNO",
"BOOTH_ID" = :NEW."BOOTH_ID",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"WORK_TIMES" = :NEW."WORK_TIMES",
"SEQNO" = :NEW."SEQNO",
"CAR_NO" = :NEW."CAR_NO",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"CARD_ISSUE_ID" = :NEW."CARD_ISSUE_ID",
"NOTICE_WRONG_ID" = :NEW."NOTICE_WRONG_ID",
"OWNER" = :NEW."OWNER",
"NOTICE_ID" = :NEW."NOTICE_ID",
"FINE_AMOUNT" = :NEW."FINE_AMOUNT",
"LAVY_AMOUNT" = :NEW."LAVY_AMOUNT",
"MOTION_ID" = :NEW."MOTION_ID",
"NOTICE_DATE" = :NEW."NOTICE_DATE",
"NOTICE_WRONG_DATE" = :NEW."NOTICE_WRONG_DATE",
"BEFORE_MOTION_ID" = :NEW."BEFORE_MOTION_ID",
"PASS_AMOUNT" = :NEW."PASS_AMOUNT",
"SEISURE_YN" = :NEW."SEISURE_YN",
"SEIZURE_DATE" = :NEW."SEIZURE_DATE",
"SEIZURE_ID" = :NEW."SEIZURE_ID",
"NOTICE_CHOOSE_ID" = :NEW."NOTICE_CHOOSE_ID",
"SEIZURE_WRONG_ID" = :NEW."SEIZURE_WRONG_ID",
"SEIZURE_WRONG_DATE" = :NEW."SEIZURE_WRONG_DATE",
"SEIZURE_TERMI_DATE" = :NEW."SEIZURE_TERMI_DATE",
"RECEIPT_YN" = :NEW."RECEIPT_YN",
"DOUBLE_RECEIPT_YN" = :NEW."DOUBLE_RECEIPT_YN",
"SEIZURE_TRMI_YN" = :NEW."SEIZURE_TRMI_YN",
"DOUBLE_RECEIPT_DATE" = :NEW."DOUBLE_RECEIPT_DATE",
"SEIZURE_TERMI" = :NEW."SEIZURE_TERMI",
"SEIZURE_MANAGE_YEAR" = :NEW."SEIZURE_MANAGE_YEAR",
"SEIZURE_MANAGE_NO" = :NEW."SEIZURE_MANAGE_NO",
"NOTICE_WRONG_REASON" = :NEW."NOTICE_WRONG_REASON",
"NOTICE_CHOOSE_DATE" = :NEW."NOTICE_CHOOSE_DATE",
"SEIZURE_WRONG_REASON" = :NEW."SEIZURE_WRONG_REASON",
"CARS_TRANS_FLAG" = :NEW."CARS_TRANS_FLAG",
"T_REAL_TIME" = :NEW."T_REAL_TIME",
"MINI_CAR_KBN" = :NEW."MINI_CAR_KBN",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"C_PUBLIC" = :NEW."C_PUBLIC",
"C_LOSS" = :NEW."C_LOSS",
"RFID_KBN" = :NEW."RFID_KBN",
"CAR_CHANGE_YN" = :NEW."CAR_CHANGE_YN",
"CAR_NO_AFTER" = :NEW."CAR_NO_AFTER",
"RFID_PENALTY_KBN" = :NEW."RFID_PENALTY_KBN",
"RFID_PENALTY_REASON" = :NEW."RFID_PENALTY_REASON",
"CAR_NO_AFTER_2" = :NEW."CAR_NO_AFTER_2",
"CAR_NO_AFTER_3" = :NEW."CAR_NO_AFTER_3",
"MONEY_CHANGE_REASON" = :NEW."MONEY_CHANGE_REASON",
"MONEY_CHANGE_PERSON" = :NEW."MONEY_CHANGE_PERSON",
"RENT_KBN" = :NEW."RENT_KBN"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_9 = 'Y'
THEN
UPDATE "NAMSAN"."PENALTY_DETAIL_INFO_DAMO"
SET "PENALTY_MANAGE_YEAR" = :NEW."PENALTY_MANAGE_YEAR",
"PENALTY_SEQNO" = :NEW."PENALTY_SEQNO",
"WORKER_ID" = :NEW."WORKER_ID",
"FARE_OFFICE_ID" = :NEW."FARE_OFFICE_ID",
"LEVY_MANAGE_YEAR" = :NEW."LEVY_MANAGE_YEAR",
"STT_DATE" = :NEW."STT_DATE",
"LEVY_SEQNO" = :NEW."LEVY_SEQNO",
"BOOTH_ID" = :NEW."BOOTH_ID",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"WORK_TIMES" = :NEW."WORK_TIMES",
"SEQNO" = :NEW."SEQNO",
"CAR_NO" = :NEW."CAR_NO",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"CARD_ISSUE_ID" = :NEW."CARD_ISSUE_ID",
"NOTICE_WRONG_ID" = :NEW."NOTICE_WRONG_ID",
"OWNER" = :NEW."OWNER",
"NOTICE_ID" = :NEW."NOTICE_ID",
"RESIDENT_NO" = :NEW."RESIDENT_NO",
"FINE_AMOUNT" = :NEW."FINE_AMOUNT",
"LAVY_AMOUNT" = :NEW."LAVY_AMOUNT",
"MOTION_ID" = :NEW."MOTION_ID",
"NOTICE_DATE" = :NEW."NOTICE_DATE",
"NOTICE_WRONG_DATE" = :NEW."NOTICE_WRONG_DATE",
"BEFORE_MOTION_ID" = :NEW."BEFORE_MOTION_ID",
"PASS_AMOUNT" = :NEW."PASS_AMOUNT",
"SEISURE_YN" = :NEW."SEISURE_YN",
"SEIZURE_DATE" = :NEW."SEIZURE_DATE",
"SEIZURE_ID" = :NEW."SEIZURE_ID",
"NOTICE_CHOOSE_ID" = :NEW."NOTICE_CHOOSE_ID",
"SEIZURE_WRONG_ID" = :NEW."SEIZURE_WRONG_ID",
"SEIZURE_WRONG_DATE" = :NEW."SEIZURE_WRONG_DATE",
"SEIZURE_TERMI_DATE" = :NEW."SEIZURE_TERMI_DATE",
"RECEIPT_YN" = :NEW."RECEIPT_YN",
"DOUBLE_RECEIPT_YN" = :NEW."DOUBLE_RECEIPT_YN",
"SEIZURE_TRMI_YN" = :NEW."SEIZURE_TRMI_YN",
"DOUBLE_RECEIPT_DATE" = :NEW."DOUBLE_RECEIPT_DATE",
"SEIZURE_TERMI" = :NEW."SEIZURE_TERMI",
"SEIZURE_MANAGE_YEAR" = :NEW."SEIZURE_MANAGE_YEAR",
"SEIZURE_MANAGE_NO" = :NEW."SEIZURE_MANAGE_NO",
"NOTICE_WRONG_REASON" = :NEW."NOTICE_WRONG_REASON",
"NOTICE_CHOOSE_DATE" = :NEW."NOTICE_CHOOSE_DATE",
"SEIZURE_WRONG_REASON" = :NEW."SEIZURE_WRONG_REASON",
"CARS_TRANS_FLAG" = :NEW."CARS_TRANS_FLAG",
"T_REAL_TIME" = :NEW."T_REAL_TIME",
"MINI_CAR_KBN" = :NEW."MINI_CAR_KBN",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"C_PUBLIC" = :NEW."C_PUBLIC",
"C_LOSS" = :NEW."C_LOSS",
"RFID_KBN" = :NEW."RFID_KBN",
"CAR_CHANGE_YN" = :NEW."CAR_CHANGE_YN",
"CAR_NO_AFTER" = :NEW."CAR_NO_AFTER",
"RFID_PENALTY_KBN" = :NEW."RFID_PENALTY_KBN",
"RFID_PENALTY_REASON" = :NEW."RFID_PENALTY_REASON",
"CAR_NO_AFTER_2" = :NEW."CAR_NO_AFTER_2",
"CAR_NO_AFTER_3" = :NEW."CAR_NO_AFTER_3",
"MONEY_CHANGE_REASON" = :NEW."MONEY_CHANGE_REASON",
"MONEY_CHANGE_PERSON" = :NEW."MONEY_CHANGE_PERSON",
"RENT_KBN" = :NEW."RENT_KBN"
WHERE ROWID = :OLD.PENTA__ROW__ID;
END IF;
END;
/
DROP TRIGGER NAMSAN.SUT_DEFAULT_CARS_TRANS_INFO;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE TRIGGER NAMSAN."SUT_DEFAULT_CARS_TRANS_INFO"
INSTEAD OF UPDATE
ON "NAMSAN"."DEFAULT_CARS_TRANS_INFO_ROOT"
FOR EACH ROW
DECLARE
V_LENGTH1 NUMBER;
V_LENGTH2 NUMBER;
V_LENGTH3 NUMBER;
V_LENGTH4 NUMBER;
V_13 VARCHAR2 (13);
V_RAW_13 RAW (2000);
V_OLD_13 VARCHAR2 (13);
V_UPT_13 VARCHAR2 (1);
V_25 VARCHAR2 (60);
V_RAW_25 RAW (2000);
V_OLD_25 VARCHAR2 (60);
V_UPT_25 VARCHAR2 (1);
V_26 VARCHAR2 (80);
V_RAW_26 RAW (2000);
V_OLD_26 VARCHAR2 (80);
V_UPT_26 VARCHAR2 (1);
BEGIN
V_UPT_13 := 'N';
V_OLD_13 := :OLD."RESIDENT_NO";
IF V_OLD_13 IS NULL AND :NEW."RESIDENT_NO" IS NOT NULL
THEN
V_UPT_13 := 'Y';
ELSIF V_OLD_13 IS NOT NULL
THEN
IF V_OLD_13 != :NEW."RESIDENT_NO" OR :NEW."RESIDENT_NO" IS NULL
THEN
V_UPT_13 := 'Y';
END IF;
END IF;
V_UPT_25 := 'N';
V_OLD_25 := :OLD."ADDRESS_NAME";
IF V_OLD_25 IS NULL AND :NEW."ADDRESS_NAME" IS NOT NULL
THEN
V_UPT_25 := 'Y';
ELSIF V_OLD_25 IS NOT NULL
THEN
IF V_OLD_25 != :NEW."ADDRESS_NAME" OR :NEW."ADDRESS_NAME" IS NULL
THEN
V_UPT_25 := 'Y';
END IF;
END IF;
V_UPT_26 := 'N';
V_OLD_26 := :OLD."ADDRESS_NUMBER";
IF V_OLD_26 IS NULL AND :NEW."ADDRESS_NUMBER" IS NOT NULL
THEN
V_UPT_26 := 'Y';
ELSIF V_OLD_26 IS NOT NULL
THEN
IF V_OLD_26 != :NEW."ADDRESS_NUMBER" OR :NEW."ADDRESS_NUMBER" IS NULL
THEN
V_UPT_26 := 'Y';
END IF;
END IF;
IF V_UPT_13 = 'N' AND V_UPT_25 = 'N' AND V_UPT_26 = 'N'
THEN
UPDATE "NAMSAN"."DEFAULT_CARS_TRANS_INFO_DAMO"
SET "WORKER_ID" = :NEW."WORKER_ID",
"FARE_OFFICE_ID" = :NEW."FARE_OFFICE_ID",
"BOOTH_ID" = :NEW."BOOTH_ID",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"WORK_TIMES" = :NEW."WORK_TIMES",
"SEQNO" = :NEW."SEQNO",
"NOTICE_KBN" = :NEW."NOTICE_KBN",
"CAR_NO" = :NEW."CAR_NO",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"OWNER" = :NEW."OWNER",
"MOTION_ID" = :NEW."MOTION_ID",
"CARS_TRANS_DATE" = :NEW."CARS_TRANS_DATE",
"CARS_TRANS_PERSON" = :NEW."CARS_TRANS_PERSON",
"CARS_INSERT_DATE" = :NEW."CARS_INSERT_DATE",
"CARS_INSERT_PERSON" = :NEW."CARS_INSERT_PERSON",
"CARS_TRANS_VERIFY_DATE" = :NEW."CARS_TRANS_VERIFY_DATE",
"CARS_TRANS_VERIFY_PERSON" = :NEW."CARS_TRANS_VERIFY_PERSON",
"CONFIRM_YN" = :NEW."CONFIRM_YN",
"COLLECT_ID" = :NEW."COLLECT_ID",
"VERIFICATE_YN" = :NEW."VERIFICATE_YN",
"CAR_TYPE_NAME" = :NEW."CAR_TYPE_NAME",
"T_REAL_TIME" = :NEW."T_REAL_TIME",
"MINI_CAR_KBN" = :NEW."MINI_CAR_KBN",
"C_ERROR" = :NEW."C_ERROR",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"CAR_CHANGE_YN" = :NEW."CAR_CHANGE_YN",
"CAR_NO_AFTER" = :NEW."CAR_NO_AFTER",
"RFID_PENALTY_KBN" = :NEW."RFID_PENALTY_KBN",
"CAR_NO_AFTER_2" = :NEW."CAR_NO_AFTER_2",
"CAR_NO_AFTER_3" = :NEW."CAR_NO_AFTER_3"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_13 = 'N' AND V_UPT_25 = 'N' AND V_UPT_26 = 'Y'
THEN
UPDATE "NAMSAN"."DEFAULT_CARS_TRANS_INFO_DAMO"
SET "WORKER_ID" = :NEW."WORKER_ID",
"FARE_OFFICE_ID" = :NEW."FARE_OFFICE_ID",
"BOOTH_ID" = :NEW."BOOTH_ID",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"WORK_TIMES" = :NEW."WORK_TIMES",
"SEQNO" = :NEW."SEQNO",
"NOTICE_KBN" = :NEW."NOTICE_KBN",
"CAR_NO" = :NEW."CAR_NO",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"OWNER" = :NEW."OWNER",
"MOTION_ID" = :NEW."MOTION_ID",
"CARS_TRANS_DATE" = :NEW."CARS_TRANS_DATE",
"CARS_TRANS_PERSON" = :NEW."CARS_TRANS_PERSON",
"CARS_INSERT_DATE" = :NEW."CARS_INSERT_DATE",
"CARS_INSERT_PERSON" = :NEW."CARS_INSERT_PERSON",
"CARS_TRANS_VERIFY_DATE" = :NEW."CARS_TRANS_VERIFY_DATE",
"CARS_TRANS_VERIFY_PERSON" = :NEW."CARS_TRANS_VERIFY_PERSON",
"CONFIRM_YN" = :NEW."CONFIRM_YN",
"COLLECT_ID" = :NEW."COLLECT_ID",
"VERIFICATE_YN" = :NEW."VERIFICATE_YN",
"CAR_TYPE_NAME" = :NEW."CAR_TYPE_NAME",
"ADDRESS_NUMBER" = :NEW."ADDRESS_NUMBER",
"T_REAL_TIME" = :NEW."T_REAL_TIME",
"MINI_CAR_KBN" = :NEW."MINI_CAR_KBN",
"C_ERROR" = :NEW."C_ERROR",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"CAR_CHANGE_YN" = :NEW."CAR_CHANGE_YN",
"CAR_NO_AFTER" = :NEW."CAR_NO_AFTER",
"RFID_PENALTY_KBN" = :NEW."RFID_PENALTY_KBN",
"CAR_NO_AFTER_2" = :NEW."CAR_NO_AFTER_2",
"CAR_NO_AFTER_3" = :NEW."CAR_NO_AFTER_3"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_13 = 'N' AND V_UPT_25 = 'Y' AND V_UPT_26 = 'N'
THEN
UPDATE "NAMSAN"."DEFAULT_CARS_TRANS_INFO_DAMO"
SET "WORKER_ID" = :NEW."WORKER_ID",
"FARE_OFFICE_ID" = :NEW."FARE_OFFICE_ID",
"BOOTH_ID" = :NEW."BOOTH_ID",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"WORK_TIMES" = :NEW."WORK_TIMES",
"SEQNO" = :NEW."SEQNO",
"NOTICE_KBN" = :NEW."NOTICE_KBN",
"CAR_NO" = :NEW."CAR_NO",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"OWNER" = :NEW."OWNER",
"MOTION_ID" = :NEW."MOTION_ID",
"CARS_TRANS_DATE" = :NEW."CARS_TRANS_DATE",
"CARS_TRANS_PERSON" = :NEW."CARS_TRANS_PERSON",
"CARS_INSERT_DATE" = :NEW."CARS_INSERT_DATE",
"CARS_INSERT_PERSON" = :NEW."CARS_INSERT_PERSON",
"CARS_TRANS_VERIFY_DATE" = :NEW."CARS_TRANS_VERIFY_DATE",
"CARS_TRANS_VERIFY_PERSON" = :NEW."CARS_TRANS_VERIFY_PERSON",
"CONFIRM_YN" = :NEW."CONFIRM_YN",
"COLLECT_ID" = :NEW."COLLECT_ID",
"VERIFICATE_YN" = :NEW."VERIFICATE_YN",
"CAR_TYPE_NAME" = :NEW."CAR_TYPE_NAME",
"ADDRESS_NAME" = :NEW."ADDRESS_NAME",
"T_REAL_TIME" = :NEW."T_REAL_TIME",
"MINI_CAR_KBN" = :NEW."MINI_CAR_KBN",
"C_ERROR" = :NEW."C_ERROR",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"CAR_CHANGE_YN" = :NEW."CAR_CHANGE_YN",
"CAR_NO_AFTER" = :NEW."CAR_NO_AFTER",
"RFID_PENALTY_KBN" = :NEW."RFID_PENALTY_KBN",
"CAR_NO_AFTER_2" = :NEW."CAR_NO_AFTER_2",
"CAR_NO_AFTER_3" = :NEW."CAR_NO_AFTER_3"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_13 = 'N' AND V_UPT_25 = 'Y' AND V_UPT_26 = 'Y'
THEN
UPDATE "NAMSAN"."DEFAULT_CARS_TRANS_INFO_DAMO"
SET "WORKER_ID" = :NEW."WORKER_ID",
"FARE_OFFICE_ID" = :NEW."FARE_OFFICE_ID",
"BOOTH_ID" = :NEW."BOOTH_ID",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"WORK_TIMES" = :NEW."WORK_TIMES",
"SEQNO" = :NEW."SEQNO",
"NOTICE_KBN" = :NEW."NOTICE_KBN",
"CAR_NO" = :NEW."CAR_NO",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"OWNER" = :NEW."OWNER",
"MOTION_ID" = :NEW."MOTION_ID",
"CARS_TRANS_DATE" = :NEW."CARS_TRANS_DATE",
"CARS_TRANS_PERSON" = :NEW."CARS_TRANS_PERSON",
"CARS_INSERT_DATE" = :NEW."CARS_INSERT_DATE",
"CARS_INSERT_PERSON" = :NEW."CARS_INSERT_PERSON",
"CARS_TRANS_VERIFY_DATE" = :NEW."CARS_TRANS_VERIFY_DATE",
"CARS_TRANS_VERIFY_PERSON" = :NEW."CARS_TRANS_VERIFY_PERSON",
"CONFIRM_YN" = :NEW."CONFIRM_YN",
"COLLECT_ID" = :NEW."COLLECT_ID",
"VERIFICATE_YN" = :NEW."VERIFICATE_YN",
"CAR_TYPE_NAME" = :NEW."CAR_TYPE_NAME",
"ADDRESS_NAME" = :NEW."ADDRESS_NAME",
"ADDRESS_NUMBER" = :NEW."ADDRESS_NUMBER",
"T_REAL_TIME" = :NEW."T_REAL_TIME",
"MINI_CAR_KBN" = :NEW."MINI_CAR_KBN",
"C_ERROR" = :NEW."C_ERROR",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"CAR_CHANGE_YN" = :NEW."CAR_CHANGE_YN",
"CAR_NO_AFTER" = :NEW."CAR_NO_AFTER",
"RFID_PENALTY_KBN" = :NEW."RFID_PENALTY_KBN",
"CAR_NO_AFTER_2" = :NEW."CAR_NO_AFTER_2",
"CAR_NO_AFTER_3" = :NEW."CAR_NO_AFTER_3"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_13 = 'Y' AND V_UPT_25 = 'N' AND V_UPT_26 = 'N'
THEN
UPDATE "NAMSAN"."DEFAULT_CARS_TRANS_INFO_DAMO"
SET "WORKER_ID" = :NEW."WORKER_ID",
"FARE_OFFICE_ID" = :NEW."FARE_OFFICE_ID",
"BOOTH_ID" = :NEW."BOOTH_ID",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"WORK_TIMES" = :NEW."WORK_TIMES",
"SEQNO" = :NEW."SEQNO",
"NOTICE_KBN" = :NEW."NOTICE_KBN",
"CAR_NO" = :NEW."CAR_NO",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"OWNER" = :NEW."OWNER",
"RESIDENT_NO" = :NEW."RESIDENT_NO",
"MOTION_ID" = :NEW."MOTION_ID",
"CARS_TRANS_DATE" = :NEW."CARS_TRANS_DATE",
"CARS_TRANS_PERSON" = :NEW."CARS_TRANS_PERSON",
"CARS_INSERT_DATE" = :NEW."CARS_INSERT_DATE",
"CARS_INSERT_PERSON" = :NEW."CARS_INSERT_PERSON",
"CARS_TRANS_VERIFY_DATE" = :NEW."CARS_TRANS_VERIFY_DATE",
"CARS_TRANS_VERIFY_PERSON" = :NEW."CARS_TRANS_VERIFY_PERSON",
"CONFIRM_YN" = :NEW."CONFIRM_YN",
"COLLECT_ID" = :NEW."COLLECT_ID",
"VERIFICATE_YN" = :NEW."VERIFICATE_YN",
"CAR_TYPE_NAME" = :NEW."CAR_TYPE_NAME",
"T_REAL_TIME" = :NEW."T_REAL_TIME",
"MINI_CAR_KBN" = :NEW."MINI_CAR_KBN",
"C_ERROR" = :NEW."C_ERROR",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"CAR_CHANGE_YN" = :NEW."CAR_CHANGE_YN",
"CAR_NO_AFTER" = :NEW."CAR_NO_AFTER",
"RFID_PENALTY_KBN" = :NEW."RFID_PENALTY_KBN",
"CAR_NO_AFTER_2" = :NEW."CAR_NO_AFTER_2",
"CAR_NO_AFTER_3" = :NEW."CAR_NO_AFTER_3"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_13 = 'Y' AND V_UPT_25 = 'N' AND V_UPT_26 = 'Y'
THEN
UPDATE "NAMSAN"."DEFAULT_CARS_TRANS_INFO_DAMO"
SET "WORKER_ID" = :NEW."WORKER_ID",
"FARE_OFFICE_ID" = :NEW."FARE_OFFICE_ID",
"BOOTH_ID" = :NEW."BOOTH_ID",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"WORK_TIMES" = :NEW."WORK_TIMES",
"SEQNO" = :NEW."SEQNO",
"NOTICE_KBN" = :NEW."NOTICE_KBN",
"CAR_NO" = :NEW."CAR_NO",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"OWNER" = :NEW."OWNER",
"RESIDENT_NO" = :NEW."RESIDENT_NO",
"MOTION_ID" = :NEW."MOTION_ID",
"CARS_TRANS_DATE" = :NEW."CARS_TRANS_DATE",
"CARS_TRANS_PERSON" = :NEW."CARS_TRANS_PERSON",
"CARS_INSERT_DATE" = :NEW."CARS_INSERT_DATE",
"CARS_INSERT_PERSON" = :NEW."CARS_INSERT_PERSON",
"CARS_TRANS_VERIFY_DATE" = :NEW."CARS_TRANS_VERIFY_DATE",
"CARS_TRANS_VERIFY_PERSON" = :NEW."CARS_TRANS_VERIFY_PERSON",
"CONFIRM_YN" = :NEW."CONFIRM_YN",
"COLLECT_ID" = :NEW."COLLECT_ID",
"VERIFICATE_YN" = :NEW."VERIFICATE_YN",
"CAR_TYPE_NAME" = :NEW."CAR_TYPE_NAME",
"ADDRESS_NUMBER" = :NEW."ADDRESS_NUMBER",
"T_REAL_TIME" = :NEW."T_REAL_TIME",
"MINI_CAR_KBN" = :NEW."MINI_CAR_KBN",
"C_ERROR" = :NEW."C_ERROR",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"CAR_CHANGE_YN" = :NEW."CAR_CHANGE_YN",
"CAR_NO_AFTER" = :NEW."CAR_NO_AFTER",
"RFID_PENALTY_KBN" = :NEW."RFID_PENALTY_KBN",
"CAR_NO_AFTER_2" = :NEW."CAR_NO_AFTER_2",
"CAR_NO_AFTER_3" = :NEW."CAR_NO_AFTER_3"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_13 = 'Y' AND V_UPT_25 = 'Y' AND V_UPT_26 = 'N'
THEN
UPDATE "NAMSAN"."DEFAULT_CARS_TRANS_INFO_DAMO"
SET "WORKER_ID" = :NEW."WORKER_ID",
"FARE_OFFICE_ID" = :NEW."FARE_OFFICE_ID",
"BOOTH_ID" = :NEW."BOOTH_ID",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"WORK_TIMES" = :NEW."WORK_TIMES",
"SEQNO" = :NEW."SEQNO",
"NOTICE_KBN" = :NEW."NOTICE_KBN",
"CAR_NO" = :NEW."CAR_NO",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"OWNER" = :NEW."OWNER",
"RESIDENT_NO" = :NEW."RESIDENT_NO",
"MOTION_ID" = :NEW."MOTION_ID",
"CARS_TRANS_DATE" = :NEW."CARS_TRANS_DATE",
"CARS_TRANS_PERSON" = :NEW."CARS_TRANS_PERSON",
"CARS_INSERT_DATE" = :NEW."CARS_INSERT_DATE",
"CARS_INSERT_PERSON" = :NEW."CARS_INSERT_PERSON",
"CARS_TRANS_VERIFY_DATE" = :NEW."CARS_TRANS_VERIFY_DATE",
"CARS_TRANS_VERIFY_PERSON" = :NEW."CARS_TRANS_VERIFY_PERSON",
"CONFIRM_YN" = :NEW."CONFIRM_YN",
"COLLECT_ID" = :NEW."COLLECT_ID",
"VERIFICATE_YN" = :NEW."VERIFICATE_YN",
"CAR_TYPE_NAME" = :NEW."CAR_TYPE_NAME",
"ADDRESS_NAME" = :NEW."ADDRESS_NAME",
"T_REAL_TIME" = :NEW."T_REAL_TIME",
"MINI_CAR_KBN" = :NEW."MINI_CAR_KBN",
"C_ERROR" = :NEW."C_ERROR",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"CAR_CHANGE_YN" = :NEW."CAR_CHANGE_YN",
"CAR_NO_AFTER" = :NEW."CAR_NO_AFTER",
"RFID_PENALTY_KBN" = :NEW."RFID_PENALTY_KBN",
"CAR_NO_AFTER_2" = :NEW."CAR_NO_AFTER_2",
"CAR_NO_AFTER_3" = :NEW."CAR_NO_AFTER_3"
WHERE ROWID = :OLD.PENTA__ROW__ID;
ELSIF V_UPT_13 = 'Y' AND V_UPT_25 = 'Y' AND V_UPT_26 = 'Y'
THEN
UPDATE "NAMSAN"."DEFAULT_CARS_TRANS_INFO_DAMO"
SET "WORKER_ID" = :NEW."WORKER_ID",
"FARE_OFFICE_ID" = :NEW."FARE_OFFICE_ID",
"BOOTH_ID" = :NEW."BOOTH_ID",
"YEAR" = :NEW."YEAR",
"MONTH" = :NEW."MONTH",
"DAY" = :NEW."DAY",
"WORK_TIMES" = :NEW."WORK_TIMES",
"SEQNO" = :NEW."SEQNO",
"NOTICE_KBN" = :NEW."NOTICE_KBN",
"CAR_NO" = :NEW."CAR_NO",
"RESIDENT_DATE" = :NEW."RESIDENT_DATE",
"OWNER" = :NEW."OWNER",
"RESIDENT_NO" = :NEW."RESIDENT_NO",
"MOTION_ID" = :NEW."MOTION_ID",
"CARS_TRANS_DATE" = :NEW."CARS_TRANS_DATE",
"CARS_TRANS_PERSON" = :NEW."CARS_TRANS_PERSON",
"CARS_INSERT_DATE" = :NEW."CARS_INSERT_DATE",
"CARS_INSERT_PERSON" = :NEW."CARS_INSERT_PERSON",
"CARS_TRANS_VERIFY_DATE" = :NEW."CARS_TRANS_VERIFY_DATE",
"CARS_TRANS_VERIFY_PERSON" = :NEW."CARS_TRANS_VERIFY_PERSON",
"CONFIRM_YN" = :NEW."CONFIRM_YN",
"COLLECT_ID" = :NEW."COLLECT_ID",
"VERIFICATE_YN" = :NEW."VERIFICATE_YN",
"CAR_TYPE_NAME" = :NEW."CAR_TYPE_NAME",
"ADDRESS_NAME" = :NEW."ADDRESS_NAME",
"ADDRESS_NUMBER" = :NEW."ADDRESS_NUMBER",
"T_REAL_TIME" = :NEW."T_REAL_TIME",
"MINI_CAR_KBN" = :NEW."MINI_CAR_KBN",
"C_ERROR" = :NEW."C_ERROR",
"CREATE_DATE" = :NEW."CREATE_DATE",
"CREATER" = :NEW."CREATER",
"UPDATE_DATE" = :NEW."UPDATE_DATE",
"UPDATER" = :NEW."UPDATER",
"CAR_CHANGE_YN" = :NEW."CAR_CHANGE_YN",
"CAR_NO_AFTER" = :NEW."CAR_NO_AFTER",
"RFID_PENALTY_KBN" = :NEW."RFID_PENALTY_KBN",
"CAR_NO_AFTER_2" = :NEW."CAR_NO_AFTER_2",
"CAR_NO_AFTER_3" = :NEW."CAR_NO_AFTER_3"
WHERE ROWID = :OLD.PENTA__ROW__ID;
END IF;
END;
/
DROP TRIGGER NAMSAN.SIT_DEFAULT_CARS_TRANS_INFO;
/*
damo 사용 안하도록 변경
*/
CREATE OR REPLACE TRIGGER NAMSAN."SIT_DEFAULT_CARS_TRANS_INFO"
INSTEAD OF INSERT
ON "NAMSAN"."DEFAULT_CARS_TRANS_INFO_ROOT"
FOR EACH ROW
DECLARE
V_LENGTH1 NUMBER;
V_LENGTH2 NUMBER;
V_LENGTH3 NUMBER;
V_LENGTH4 NUMBER;
V_8 NUMBER (9) := 0;
V_RAW_8 RAW (2000);
V_13 VARCHAR2 (13);
V_RAW_13 RAW (2000);
V_21 CHAR (1) := 0;
V_RAW_21 RAW (2000);
V_23 CHAR (1) := 0;
V_RAW_23 RAW (2000);
V_25 VARCHAR2 (60);
V_RAW_25 RAW (2000);
V_26 VARCHAR2 (80);
V_RAW_26 RAW (2000);
V_28 CHAR (1) := 0;
V_RAW_28 RAW (2000);
V_34 CHAR (1) := 0;
V_RAW_34 RAW (2000);
V_36 CHAR (1) := 0;
V_RAW_36 RAW (2000);
BEGIN
V_8 := NVL (:NEW."SEQNO", V_8);
V_21 := NVL (:NEW."CONFIRM_YN", V_21);
V_23 := NVL (:NEW."VERIFICATE_YN", V_23);
V_28 := NVL (:NEW."MINI_CAR_KBN", V_28);
V_34 := NVL (:NEW."CAR_CHANGE_YN", V_34);
V_36 := NVL (:NEW."RFID_PENALTY_KBN", V_36);
INSERT
INTO "NAMSAN"."DEFAULT_CARS_TRANS_INFO_DAMO" ("WORKER_ID",
"FARE_OFFICE_ID",
"BOOTH_ID",
"YEAR",
"MONTH",
"DAY",
"WORK_TIMES",
"SEQNO",
"NOTICE_KBN",
"CAR_NO",
"RESIDENT_DATE",
"OWNER",
"RESIDENT_NO",
"MOTION_ID",
"CARS_TRANS_DATE",
"CARS_TRANS_PERSON",
"CARS_INSERT_DATE",
"CARS_INSERT_PERSON",
"CARS_TRANS_VERIFY_DATE",
"CARS_TRANS_VERIFY_PERSON",
"CONFIRM_YN",
"COLLECT_ID",
"VERIFICATE_YN",
"CAR_TYPE_NAME",
"ADDRESS_NAME",
"ADDRESS_NUMBER",
"T_REAL_TIME",
"MINI_CAR_KBN",
"C_ERROR",
"CREATE_DATE",
"CREATER",
"UPDATE_DATE",
"UPDATER",
"CAR_CHANGE_YN",
"CAR_NO_AFTER",
"RFID_PENALTY_KBN",
"CAR_NO_AFTER_2",
"CAR_NO_AFTER_3")
VALUES (:NEW."WORKER_ID",
:NEW."FARE_OFFICE_ID",
:NEW."BOOTH_ID",
:NEW."YEAR",
:NEW."MONTH",
:NEW."DAY",
:NEW."WORK_TIMES",
V_8,
:NEW."NOTICE_KBN",
:NEW."CAR_NO",
:NEW."RESIDENT_DATE",
:NEW."OWNER",
:NEW."RESIDENT_NO",
:NEW."MOTION_ID",
:NEW."CARS_TRANS_DATE",
:NEW."CARS_TRANS_PERSON",
:NEW."CARS_INSERT_DATE",
:NEW."CARS_INSERT_PERSON",
:NEW."CARS_TRANS_VERIFY_DATE",
:NEW."CARS_TRANS_VERIFY_PERSON",
V_21,
:NEW."COLLECT_ID",
V_23,
:NEW."CAR_TYPE_NAME",
:NEW."ADDRESS_NAME",
:NEW."ADDRESS_NUMBER",
:NEW."T_REAL_TIME",
V_28,
:NEW."C_ERROR",
:NEW."CREATE_DATE",
:NEW."CREATER",
:NEW."UPDATE_DATE",
:NEW."UPDATER",
V_34,
:NEW."CAR_NO_AFTER",
V_36,
:NEW."CAR_NO_AFTER_2",
:NEW."CAR_NO_AFTER_3");
END;
/
DROP PUBLIC SYNONYM DEFAULT_FARE_READ_INFO;
CREATE PUBLIC SYNONYM DEFAULT_FARE_READ_INFO FOR NAMSAN.DEFAULT_FARE_READ_INFO;
DROP PUBLIC SYNONYM PENALTY_DETAIL_INFO;
CREATE PUBLIC SYNONYM PENALTY_DETAIL_INFO FOR NAMSAN.PENALTY_DETAIL_INFO;
DROP PUBLIC SYNONYM PENALTY_DETAIL_INFO_KTEST;
CREATE PUBLIC SYNONYM PENALTY_DETAIL_INFO_KTEST FOR NAMSAN.PENALTY_DETAIL_INFO;
GRANT SELECT ON NAMSAN.DEFAULT_FARE_READ_INFO TO ETISHON;
GRANT SELECT ON NAMSAN.OVERFARE_INFO TO ETISHON WITH GRANT OPTION;
GRANT SELECT ON NAMSAN.PENALTY_DETAIL_INFO TO INDIGO;
GRANT SELECT ON NAMSAN.PENALTY_DETAIL_INFO_ROOT TO INDIGO;
GRANT SELECT ON NAMSAN.V_RFID_INFO TO PARK;
GRANT SELECT ON NAMSAN.CIVIL_ACCEPT_INFO TO TUNNELMAIN;
GRANT SELECT ON NAMSAN.LEVY_TARGET_INFO TO TUNNELMAIN;
GRANT SELECT ON NAMSAN.OVERFARE_INFO TO TUNNELMAIN;
GRANT SELECT ON NAMSAN.OVERFARE_ORDER_INFO TO TUNNELMAIN;
GRANT SELECT ON NAMSAN.PENALTY_ADDRESS_UPDATE_INFO TO TUNNELMAIN;
GRANT SELECT ON NAMSAN.PENALTY_HEAD_INFO TO TUNNELMAIN;
GRANT SELECT ON NAMSAN.PENALTY_HISTORY_DETAIL_INFO TO TUNNELMAIN;
GRANT SELECT ON NAMSAN.PENALTY_PUBLIC_INFO TO TUNNELMAIN;
Reference in New Issue View Git Blame Copy Permalink