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.
419 lines
17 KiB
SQL
419 lines
17 KiB
SQL
DROP VIEW NAMSAN3.FARE_ADJUST;
|
|
|
|
/* Formatted on 2019/01/28 11:23 (Formatter Plus v4.8.8) */
|
|
CREATE OR REPLACE FORCE VIEW namsan3.fare_adjust (t_id,
|
|
t_worker_name,
|
|
t_office,
|
|
t_year,
|
|
t_month,
|
|
t_day,
|
|
t_booth,
|
|
t_st_time,
|
|
t_ed_time,
|
|
cash_q,
|
|
k_cash_q,
|
|
sun_q,
|
|
k_sun_q,
|
|
who_q,
|
|
k_who_q,
|
|
coupon_q,
|
|
k_coupon_q,
|
|
minap_q,
|
|
free_q,
|
|
cash_m,
|
|
k_cash_m,
|
|
sun_m,
|
|
k_sun_m,
|
|
who_m,
|
|
k_who_m,
|
|
sale_q,
|
|
k_sale_q,
|
|
sale_m,
|
|
k_sale_m,
|
|
n_repay_q,
|
|
n_repay_m
|
|
)
|
|
AS
|
|
SELECT worker_id, fun_get_worker (worker_id) AS t_worker,
|
|
fare_office_id AS t_office, YEAR AS t_year, MONTH AS t_month,
|
|
DAY AS t_day, booth_id AS t_booth, work_stt_time AS t_st_time,
|
|
work_end_time AS t_ed_time, NVL (cash_quantity, 0) AS cash_q,
|
|
NVL (k_cash_q, 0) AS k_cash_q,
|
|
NVL (card_before_quantity, 0) AS sun_q,
|
|
NVL (k_card_sun_q, 0) AS k_sun_q,
|
|
NVL (card_after_quantity, 0) AS who_q,
|
|
NVL (k_card_who_q, 0) AS k_who_q,
|
|
NVL (coupon_quantity, 0) AS coupon_q,
|
|
NVL (k_coupon_q, 0) AS k_coupon_q,
|
|
NVL (nonpayment_quantity, 0) AS minap_q,
|
|
NVL (exemption_quantity, 0) AS free_q,
|
|
( fun_money_from_fare_info1 (YEAR || MONTH || DAY, '10')
|
|
* NVL (cash_quantity, 0)
|
|
) AS cash_m,
|
|
( fun_money_from_fare_info1 (YEAR || MONTH || DAY, '11')
|
|
* NVL (k_cash_q, 0)
|
|
) AS k_cash_m,
|
|
( fun_money_from_fare_info1 (YEAR || MONTH || DAY, '21')
|
|
* NVL (card_before_quantity, 0)
|
|
) AS sun_m,
|
|
( fun_money_from_fare_info1 (YEAR || MONTH || DAY, '23')
|
|
* NVL (k_card_sun_q, 0)
|
|
) AS k_sun_m,
|
|
( fun_money_from_fare_info1 (YEAR || MONTH || DAY, '22')
|
|
* NVL (card_after_quantity, 0)
|
|
) AS who_m,
|
|
( fun_money_from_fare_info1 (YEAR || MONTH || DAY, '24')
|
|
* NVL (k_card_who_q, 0)
|
|
) AS k_who_m,
|
|
NVL (coupon_sale_quantity, 0) AS sale_q,
|
|
NVL (k_coupon_sale_q, 0) AS k_sale_q,
|
|
( fun_money_from_fare_info1 (YEAR || MONTH || DAY, '52')
|
|
* NVL (coupon_sale_quantity, 0)
|
|
) AS sale_m,
|
|
( fun_money_from_fare_info1 (YEAR || MONTH || DAY, '54')
|
|
* NVL (k_coupon_sale_q, 0)
|
|
) AS k_sale_m,
|
|
repay_cnt AS n_repay_q, repay_amount AS n_repay_m
|
|
FROM fare_adjust_info
|
|
WHERE fare_adjust_yn = '1';
|
|
COMMENT ON TABLE NAMSAN3.FARE_ADJUST IS '징수원정산정보';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.T_ID IS '징수원ID';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.T_OFFICE IS '요금소코드';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.T_YEAR IS '년도';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.T_MONTH IS '월';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.T_DAY IS '일';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.T_BOOTH IS '차로';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.T_ST_TIME IS '시작시간';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.T_ED_TIME IS '종료시간';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.CASH_Q IS '일반현금통행량';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.K_CASH_Q IS '경차현금통행량';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.SUN_Q IS '일반선불통행량';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.K_SUN_Q IS '경차선불통행량';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.WHO_Q IS '일반후불통행량';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.K_WHO_Q IS '경차후불통행량';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.COUPON_Q IS '일반쿠폰회수통행량';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.K_COUPON_Q IS '경차쿠폰회수통행량';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.MINAP_Q IS '미납통행량';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.FREE_Q IS '면제통행량';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.CASH_M IS '일반현금수입금';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.K_CASH_M IS '경차현금수입금';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.SUN_M IS '일반선불수입금';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.K_SUN_M IS '경차선불수입금';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.WHO_M IS '일반후불수입금';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.K_WHO_M IS '경차후불수입금';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.SALE_Q IS '일반정액권판매권수';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.K_SALE_Q IS '경차정액권판매권수';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.SALE_M IS '일반정액권판매수입금';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.K_SALE_M IS '경차정액권판매수입금';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.N_REPAY_Q IS '환불건수';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_ADJUST.N_REPAY_M IS '환불금액';
|
|
|
|
|
|
DROP VIEW NAMSAN3.FARE_BOOTH;
|
|
|
|
/* Formatted on 2019/01/28 11:23 (Formatter Plus v4.8.8) */
|
|
CREATE OR REPLACE FORCE VIEW namsan3.fare_booth (t_worker,
|
|
t_office,
|
|
t_year,
|
|
t_month,
|
|
t_day,
|
|
t_booth,
|
|
t_time,
|
|
cash_q,
|
|
k_cash_q,
|
|
sun_q,
|
|
k_sun_q,
|
|
who_q,
|
|
k_who_q,
|
|
coupon_q,
|
|
k_coupon_q,
|
|
minap_q,
|
|
free_q,
|
|
cash_m,
|
|
k_cash_m,
|
|
sun_m,
|
|
k_sun_m,
|
|
who_m,
|
|
k_who_m,
|
|
sale_q,
|
|
k_sale_q,
|
|
sale_m,
|
|
k_sale_m,
|
|
n_repay_q,
|
|
n_repay_m
|
|
)
|
|
AS
|
|
SELECT fun_get_worker (worker_id) AS t_worker, fare_office_id AS t_office,
|
|
YEAR AS t_year, MONTH AS t_month, DAY AS t_day, booth_id AS t_booth,
|
|
SUBSTR (work_stt_time, 1, 2)
|
|
|| ':'
|
|
|| SUBSTR (work_stt_time, 3, 2)
|
|
|| '-'
|
|
|| SUBSTR (work_end_time, 1, 2)
|
|
|| ':'
|
|
|| SUBSTR (work_end_time, 3, 2) AS t_time,
|
|
NVL (cash_quantity, 0) AS cash_q, NVL (k_cash_q, 0) AS k_cash_q,
|
|
NVL (card_before_quantity, 0) AS sun_q,
|
|
NVL (k_card_sun_q, 0) AS k_sun_q,
|
|
NVL (card_after_quantity, 0) AS who_q,
|
|
NVL (k_card_who_q, 0) AS k_who_q,
|
|
NVL (coupon_quantity, 0) AS coupon_q,
|
|
NVL (k_coupon_q, 0) AS k_coupon_q,
|
|
NVL (nonpayment_quantity, 0) AS minap_q,
|
|
NVL (exemption_quantity, 0) AS free_q,
|
|
( fun_money_from_fare_info1 (YEAR || MONTH || DAY, '10')
|
|
* NVL (cash_quantity, 0)
|
|
) AS cash_m,
|
|
( fun_money_from_fare_info1 (YEAR || MONTH || DAY, '11')
|
|
* NVL (k_cash_q, 0)
|
|
) AS k_cash_m,
|
|
( fun_money_from_fare_info1 (YEAR || MONTH || DAY, '21')
|
|
* NVL (card_before_quantity, 0)
|
|
) AS sun_m,
|
|
( fun_money_from_fare_info1 (YEAR || MONTH || DAY, '23')
|
|
* NVL (k_card_sun_q, 0)
|
|
) AS k_sun_m,
|
|
( fun_money_from_fare_info1 (YEAR || MONTH || DAY, '22')
|
|
* NVL (card_after_quantity, 0)
|
|
) AS who_m,
|
|
( fun_money_from_fare_info1 (YEAR || MONTH || DAY, '24')
|
|
* NVL (k_card_who_q, 0)
|
|
) AS k_who_m,
|
|
NVL (coupon_sale_quantity, 0) AS sale_q,
|
|
NVL (k_coupon_sale_q, 0) AS k_sale_q,
|
|
( fun_money_from_fare_info1 (YEAR || MONTH || DAY, '52')
|
|
* NVL (coupon_sale_quantity, 0)
|
|
) AS sale_m,
|
|
( fun_money_from_fare_info1 (YEAR || MONTH || DAY, '54')
|
|
* NVL (k_coupon_sale_q, 0)
|
|
) AS k_sale_m,
|
|
repay_cnt AS n_repay_q, repay_amount AS n_repay_m
|
|
FROM fare_adjust_info
|
|
WHERE fare_adjust_yn = '1';
|
|
COMMENT ON TABLE NAMSAN3.FARE_BOOTH IS '징수원 정산현황';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.T_WORKER IS '징수원명';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.T_OFFICE IS '요금소코드';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.T_YEAR IS '년도';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.T_MONTH IS '월';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.T_DAY IS '일';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.T_BOOTH IS '차로';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.T_TIME IS '근무시간';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.CASH_Q IS '일반현금통행량';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.K_CASH_Q IS '경차현금통행량';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.SUN_Q IS '일반선불통행량';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.K_SUN_Q IS '경차선불통행량';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.WHO_Q IS '일반후불통행량';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.K_WHO_Q IS '경차후불통행량';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.COUPON_Q IS '일반쿠폰회수통행량';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.K_COUPON_Q IS '경차쿠폰회수통행량';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.MINAP_Q IS '미납통행량';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.FREE_Q IS '면제통행량';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.CASH_M IS '일반현금수입금';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.K_CASH_M IS '경차현금수입금';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.SUN_M IS '일반선불수입금';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.K_SUN_M IS '경차선불수입금';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.WHO_M IS '일반후불수입금';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.K_WHO_M IS '경차후불수입금';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.SALE_Q IS '일반정액권판매권수';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.K_SALE_Q IS '경차정액권판매권수';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.SALE_M IS '일반정액권판매수입금';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.K_SALE_M IS '경차정액권판매수입금';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.N_REPAY_Q IS '환불건수';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_BOOTH.N_REPAY_M IS '환불금액';
|
|
|
|
|
|
DROP VIEW NAMSAN3.FARE_WORKER;
|
|
|
|
/* Formatted on 2019/01/28 11:23 (Formatter Plus v4.8.8) */
|
|
CREATE OR REPLACE FORCE VIEW namsan3.fare_worker (t_worker,
|
|
t_office,
|
|
t_year,
|
|
t_month,
|
|
t_day,
|
|
t_time,
|
|
t_booth,
|
|
n_cash_q,
|
|
n_sun_q,
|
|
n_who_q,
|
|
n_coupon_q,
|
|
n_minap_q,
|
|
n_free_q,
|
|
n_cash_m,
|
|
n_sun_m,
|
|
n_who_m,
|
|
n_sale_q,
|
|
n_sale_m,
|
|
n_repay_q,
|
|
n_repay_m
|
|
)
|
|
AS
|
|
SELECT fun_get_worker (worker_id) AS t_worker, fare_office_id AS t_office,
|
|
YEAR AS t_year, MONTH AS t_month, DAY AS t_day,
|
|
SUBSTR (work_stt_time, 1, 2)
|
|
|| ':'
|
|
|| SUBSTR (work_stt_time, 3, 2)
|
|
|| '-'
|
|
|| SUBSTR (work_end_time, 1, 2)
|
|
|| ':'
|
|
|| SUBSTR (work_end_time, 3, 2) AS t_time,
|
|
booth_id AS t_booth,
|
|
NVL (cash_quantity, 0) + NVL (k_cash_q, 0) AS n_cash_q,
|
|
NVL (card_before_quantity, 0) + NVL (k_card_sun_q, 0) AS n_sun_q,
|
|
NVL (card_after_quantity, 0) + NVL (k_card_who_q, 0) AS n_who_q,
|
|
NVL (coupon_quantity, 0) + NVL (k_coupon_q, 0) AS n_coupon_q,
|
|
NVL (nonpayment_quantity, 0) AS n_minap_q,
|
|
NVL (exemption_quantity, 0) AS n_free_q,
|
|
( fun_money_from_fare_info1 (YEAR || MONTH || DAY, '10')
|
|
* NVL (cash_quantity, 0)
|
|
+ fun_money_from_fare_info1 (YEAR || MONTH || DAY, '11')
|
|
* NVL (k_cash_q, 0)
|
|
) AS n_cash_m,
|
|
( fun_money_from_fare_info1 (YEAR || MONTH || DAY, '21')
|
|
* NVL (card_before_quantity, 0)
|
|
+ fun_money_from_fare_info1 (YEAR || MONTH || DAY, '23')
|
|
* NVL (k_card_sun_q, 0)
|
|
) AS n_sun_m,
|
|
( fun_money_from_fare_info1 (YEAR || MONTH || DAY, '22')
|
|
* NVL (card_after_quantity, 0)
|
|
+ fun_money_from_fare_info1 (YEAR || MONTH || DAY, '24')
|
|
* NVL (k_card_who_q, 0)
|
|
) AS n_who_m,
|
|
NVL (coupon_sale_quantity, 0)
|
|
+ NVL (k_coupon_sale_q, 0) AS n_sale_q,
|
|
( fun_money_from_fare_info1 (YEAR || MONTH || DAY, '52')
|
|
* NVL (coupon_sale_quantity, 0)
|
|
+ fun_money_from_fare_info1 (YEAR || MONTH || DAY, '54')
|
|
* NVL (k_coupon_sale_q, 0)
|
|
) AS n_sale_m,
|
|
repay_cnt AS n_repay_q, repay_amount AS n_repay_m
|
|
FROM fare_adjust_info
|
|
WHERE fare_adjust_yn = '1';
|
|
COMMENT ON TABLE NAMSAN3.FARE_WORKER IS '징수원별 정산현황';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_WORKER.T_WORKER IS '징수원명';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_WORKER.T_OFFICE IS '요금소';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_WORKER.T_YEAR IS '년도';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_WORKER.T_MONTH IS '월';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_WORKER.T_DAY IS '일';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_WORKER.T_TIME IS '근무시간';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_WORKER.T_BOOTH IS '차로';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_WORKER.N_CASH_Q IS '현금통행량';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_WORKER.N_SUN_Q IS '선불카드통행량';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_WORKER.N_WHO_Q IS '후불카드통행량';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_WORKER.N_COUPON_Q IS '쿠폰통행량';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_WORKER.N_MINAP_Q IS '미납통행량';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_WORKER.N_FREE_Q IS '면제통행량';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_WORKER.N_CASH_M IS '현금수입금';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_WORKER.N_SUN_M IS '선불카드수입금';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_WORKER.N_WHO_M IS '후불카드수입금';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_WORKER.N_SALE_Q IS '정액권권수';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_WORKER.N_SALE_M IS '정액권수입금';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_WORKER.N_REPAY_Q IS '환불건수';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.FARE_WORKER.N_REPAY_M IS '환불금액';
|
|
|
|
|
|
DROP VIEW NAMSAN3.WORKER_ALL;
|
|
|
|
/* Formatted on 2019/01/28 11:23 (Formatter Plus v4.8.8) */
|
|
CREATE OR REPLACE FORCE VIEW namsan3.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 NAMSAN3.WORKER_ALL IS '징수원정보';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.WORKER_ALL.T_ID IS '징수원ID';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.WORKER_ALL.T_NAME IS '징수원명';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.WORKER_ALL.D_ST IS '시작일자';
|
|
|
|
COMMENT ON COLUMN NAMSAN3.WORKER_ALL.D_ED IS '종료일자';
|
|
|
|
|