|
CREATE OR REPLACE VIEW CNT.KVA_PASTMONTH_DETALN_AKT ( SYB_RNK, N_OB, N_FID, TXT, V, N_GR_TY, N_SH, DD_MM_YYYY, N_INTER_RAS, VAL, INTERV,
AK_SUM, ST, POK_START, RASH_POLN) AS
SELECT a.syb_rnk, a.n_ob, a.n_fid, a.txt, a.v, b.n_gr_ty, b.n_sh, b.dd_mm_yyyy, b.n_inter_ras, b.val, b.interv, b.ak_sum,
(b.pok_start - b.ak_sum) "ST", b.pok_start, b.rash_poln
FROM fid a JOIN buf_v_int b ON a.syb_rnk = b.syb_rnk AND a.n_ob = b.n_ob AND a.n_fid = b.n_fid
WHERE a.v = 1
AND a.syb_rnk = 3
AND b.dd_mm_yyyy =(SELECT TO_CHAR(LAST_DAY ( ADD_MONTHS (SYSDATE, -1) - TO_CHAR (LAST_DAY (ADD_MONTHS (SYSDATE, -1)),'dd'))+ 1,'dd.mm.yyyy' ) FROM DUAL)
AND b.n_inter_ras > 2
AND b.n_gr_ty = 1 -- and a.n_ob=1000 AND a.n_fid=1301
UNION
SELECT a.syb_rnk, a.n_ob, a.n_fid, a.txt, a.v, b.n_gr_ty, b.n_sh, b.dd_mm_yyyy, b.n_inter_ras, b.val, b.interv, b.ak_sum,
(b.pok_start - b.ak_sum) "ST", b.pok_start, b.rash_poln
FROM fid a JOIN buf_v_int b ON a.syb_rnk = b.syb_rnk AND a.n_ob = b.n_ob AND a.n_fid = b.n_fid
WHERE a.v = 1 AND a.syb_rnk = 3
AND b.dd_mm_yyyy BETWEEN TO_DATE ((SELECT TO_CHAR ( LAST_DAY ( ADD_MONTHS (SYSDATE, -1) - TO_CHAR(LAST_DAY (ADD_MONTHS (SYSDATE, -1 )), 'dd'))+ 2, 'dd.mm.yyyy' ) FROM DUAL), 'DD.MM.YYYY' )
AND TO_DATE ((SELECT TO_CHAR (LAST_DAY (ADD_MONTHS (SYSDATE,-1)), 'dd.mm.yyyy') FROM DUAL), 'DD.MM.YYYY') AND b.n_gr_ty = 1 --and a.n_ob=1000
UNION
SELECT a.syb_rnk, a.n_ob, a.n_fid, a.txt, a.v, b.n_gr_ty, b.n_sh, b.dd_mm_yyyy, b.n_inter_ras, b.val, b.interv, b.ak_sum,
(b.pok_start - b.ak_sum) "ST", b.pok_start, b.rash_poln
FROM fid a JOIN buf_v_int b ON a.syb_rnk = b.syb_rnk AND a.n_ob = b.n_ob AND a.n_fid = b.n_fid
WHERE a.v = 1 AND a.syb_rnk = 3 AND b.dd_mm_yyyy = (SELECT TO_CHAR (LAST_DAY (ADD_MONTHS (SYSDATE, -1)) + 1, 'dd.mm.yyyy') FROM DUAL)
AND b.n_inter_ras <= 2 AND b.n_gr_ty = 1 --and a.n_ob=1000
UNION
SELECT a.syb_rnk, a.n_ob, a.n_fid, a.txt, a.v, b.n_gr_ty, b.n_sh, b.dd_mm_yyyy, b.n_inter_ras, b.val, b.interv, b.ak_sum,
(b.pok_start - b.ak_sum) "ST", b.pok_start, b.rash_poln
FROM fid a JOIN buf_v_int b ON a.syb_rnk = b.syb_rnk AND a.n_ob = b.n_ob AND a.n_fid = b.n_fid
WHERE (a.v IS NULL OR a.v <> 1) AND a.syb_rnk = 3 AND b.dd_mm_yyyy
BETWEEN TO_DATE ((SELECT TO_CHAR ( LAST_DAY ( ADD_MONTHS (SYSDATE, -1) - TO_CHAR(LAST_DAY (ADD_MONTHS (SYSDATE,-1)),'dd')) + 1, 'dd.mm.yyyy') FROM DUAL),'DD.MM.YYYY')
AND TO_DATE ((SELECT TO_CHAR (LAST_DAY (ADD_MONTHS (SYSDATE,-1)),'dd.mm.yyyy') FROM DUAL),'DD.MM.YYYY')
AND b.n_gr_ty = 1 --and a.n_ob=1000;
|
