select a.c_port_code,
a.c_port_name,
to_char(A1.D_ASTSTAT,'yyyy-mm-dd') D_ASTSTAT,
CASE WHEN A.C_DAT_CODE in ('ASS_FOF','ASS_QDII') THEN NVL(A11.DWJZ,A111.DWJZ) ELSE A1.DWJZ END DWJZ,
CASE WHEN A.C_DAT_CODE in ('ASS_FOF','ASS_QDII') THEN NVL(A22.LJDWJZ,A222.LJDWJZ) ELSE A2.LJDWJZ END LJDWJZ,
CASE
WHEN A.C_DAT_CLS = 'CLS_HB' THEN
CASE WHEN A.C_DAT_CODE in ('ASS_FOF','ASS_QDII') THEN NVL(A33.ZCJZ2,A333.ZCJZ2) ELSE A3.ZCJZ2 END
ELSE
CASE WHEN A.C_DAT_CODE in ('ASS_FOF','ASS_QDII') THEN NVL(A33.ZCJZ1,A333.ZCJZ1) ELSE A3.ZCJZ1 END
END AS ZCJZ,
case when nvl(a4.dwjz,0)=0 then 0 else nvl(CASE WHEN A.C_DAT_CODE in ('ASS_FOF','ASS_QDII') THEN NVL(A11.DWJZ,A111.DWJZ) ELSE A1.DWJZ END,0)/nvl(a4.dwjz,0) - 1 end DWJZ1,
case when nvl(a5.dwjz,0)=0 then 0 else nvl(CASE WHEN A.C_DAT_CODE in ('ASS_FOF','ASS_QDII') THEN NVL(A11.DWJZ,A111.DWJZ) ELSE A1.DWJZ END,0)/ nvl(a5.dwjz,0) - 1 end DWJZ2,
CASE WHEN A.C_DAT_CODE in ('ASS_FOF','ASS_QDII') THEN NVL(a66.LJJZZZL,a666.LJJZZZL) ELSE a6.LJJZZZL END LJJZZZL,
to_char(b.D_RECORD_REVIEW_DATE,'yyyy-mm-dd')D_RECORD_REVIEW_DATE,
a7.n_sell_money1,
a8.n_sell_money2
from T_P_AB_PORT a
left join T_P_AB_PORT_PD b
on a.c_port_code = b.c_port_code
left join (SELECT a.c_port_code, a.d_aststat, a.C_KM_NAME DWJZ
FROM t_r_fr_aststat a
where ((A.C_PORT_CODE = 'SGU520'))
and a.d_aststat = date
'2023-07-01'
and a.c_key_code = 'DWJZ'
and instr(a.c_km_code, '_') = 0) A1
ON A.C_PORT_CODE = A1.C_PORT_CODE
left join (SELECT a.c_port_code, a.d_aststat, a.C_KM_NAME DWJZ
FROM t_r_fr_aststat a
where ((A.C_PORT_CODE = 'SGU520'))
and a.d_aststat = GETDAY_HDAY('CN',LAST_DAY(date'2023-07-01')+1,-1,'W')
and a.c_key_code = 'DWJZ'
and instr(a.c_km_code, '_') = 0) A11
ON A.C_PORT_CODE = A11.C_PORT_CODE
left join (SELECT a.c_port_code, a.d_aststat, a.C_KM_NAME DWJZ
FROM t_r_fr_aststat a
where ((A.C_PORT_CODE = 'SGU520'))
and a.d_aststat = GETDAY_HDAY('CN',TRUNC(date'2023-07-01','MM'),-1,'W')
and a.c_key_code = 'DWJZ'
and instr(a.c_km_code, '_') = 0) A111
ON A.C_PORT_CODE = A11.C_PORT_CODE
left join (SELECT a.c_port_code, a.d_aststat, a.C_KM_NAME LJDWJZ
FROM t_r_fr_aststat a
where ((A.C_PORT_CODE = 'SGU520'))
and a.d_aststat = date
'2023-07-01'
and a.c_key_code = 'LJDWJZ'
and instr(a.c_km_code, '_') = 0) A2
ON A.C_PORT_CODE = A2.C_PORT_CODE
left join (SELECT a.c_port_code, a.d_aststat, a.C_KM_NAME LJDWJZ
FROM t_r_fr_aststat a
where ((A.C_PORT_CODE = 'SGU520'))
and a.d_aststat = GETDAY_HDAY('CN',LAST_DAY(date'2023-07-01')+1,-1,'W')
and a.c_key_code = 'LJDWJZ'
and instr(a.c_km_code, '_') = 0) A22
ON A.C_PORT_CODE = A22.C_PORT_CODE
left join (SELECT a.c_port_code, a.d_aststat, a.C_KM_NAME LJDWJZ
FROM t_r_fr_aststat a
where ((A.C_PORT_CODE = 'SGU520'))
and a.d_aststat = GETDAY_HDAY('CN',TRUNC(date'2023-07-01','MM'),-1,'W')
and a.c_key_code = 'LJDWJZ'
and instr(a.c_km_code, '_') = 0) A222
ON A.C_PORT_CODE = A222.C_PORT_CODE
left join (SELECT a.c_port_code,
a.d_aststat,
A.N_PORT_MV ZCJZ1,
a.N_PORT_COST ZCJZ2
FROM t_r_fr_aststat a
where ((A.C_PORT_CODE = 'SGU520'))
and a.d_aststat = date
'2023-07-01'
and a.c_key_code = 'ZCJZ'
and instr(a.c_km_code, '_') = 0) A3
ON A.C_PORT_CODE = A3.C_PORT_CODE
left join (SELECT a.c_port_code,
a.d_aststat,
A.N_PORT_MV ZCJZ1,
a.N_PORT_COST ZCJZ2
FROM t_r_fr_aststat a
where ((A.C_PORT_CODE = 'SGU520'))
and a.d_aststat = GETDAY_HDAY('CN',LAST_DAY(date'2023-07-01')+1,-1,'W')
and a.c_key_code = 'ZCJZ'
and instr(a.c_km_code, '_') = 0) A33
ON A.C_PORT_CODE = A33.C_PORT_CODE
left join (SELECT a.c_port_code,
a.d_aststat,
A.N_PORT_MV ZCJZ1,
a.N_PORT_COST ZCJZ2
FROM t_r_fr_aststat a
where ((A.C_PORT_CODE = 'SGU520'))
and a.d_aststat = GETDAY_HDAY('CN',TRUNC(date'2023-07-01','MM'),-1,'W')
and a.c_key_code = 'ZCJZ'
and instr(a.c_km_code, '_') = 0) A333
ON A.C_PORT_CODE = A333.C_PORT_CODE
left join (SELECT a.c_port_code, a.d_aststat, a.C_KM_NAME DWJZ
FROM t_r_fr_aststat a
where ((A.C_PORT_CODE = 'SGU520'))
and a.d_aststat = trunc(date'2023-07-01','mm')-1
and a.c_key_code = 'DWJZ'
and instr(a.c_km_code, '_') = 0) A4
ON A.C_PORT_CODE = A4.C_PORT_CODE
left join (SELECT a.c_port_code, a.d_aststat, a.C_KM_NAME DWJZ
FROM t_r_fr_aststat a
where ((A.C_PORT_CODE = 'SGU520'))
and a.d_aststat = trunc(date'2023-07-01','yyyy')-1
and a.c_key_code = 'DWJZ'
and instr(a.c_km_code, '_') = 0) A5
ON A.C_PORT_CODE = A5.C_PORT_CODE
left join (SELECT a.c_port_code, a.d_aststat, a.C_KM_NAME LJJZZZL
FROM t_r_fr_aststat a
where ((A.C_PORT_CODE = 'SGU520'))
and a.d_aststat = date
'2023-07-01'
and a.c_key_code = 'LJJZZZL'
and instr(a.c_km_code, '_') = 0) A6
ON A.C_PORT_CODE = A6.C_PORT_CODE
left join (SELECT a.c_port_code, a.d_aststat, a.C_KM_NAME LJJZZZL
FROM t_r_fr_aststat a
where ((A.C_PORT_CODE = 'SGU520'))
and a.d_aststat = GETDAY_HDAY('CN',LAST_DAY(date'2023-07-01')+1,-1,'W')
and a.c_key_code = 'LJJZZZL'
and instr(a.c_km_code, '_') = 0) A66
ON A.C_PORT_CODE = A66.C_PORT_CODE
left join (SELECT a.c_port_code, a.d_aststat, a.C_KM_NAME LJJZZZL
FROM t_r_fr_aststat a
where ((A.C_PORT_CODE = 'SGU520'))
and a.d_aststat = GETDAY_HDAY('CN',TRUNC(date'2023-07-01','MM'),-1,'W')
and a.c_key_code = 'LJJZZZL'
and instr(a.c_km_code, '_') = 0) A666
ON A.C_PORT_CODE = A666.C_PORT_CODE
left join (
select a.c_port_code, sum(a.N_SELL_MONEY) N_SELL_MONEY1 from T_D_AC_SELL_TA a
where (A.C_PORT_CODE = 'SGU520')
and a.D_SELL = date
'2023-07-01'
and a.C_DS_CODE = 'TAXS_SG'
and a.N_CHECK_STATE = 1
group by a.c_port_code
) a7
ON A.C_PORT_CODE = A7.C_PORT_CODE
left join (
select a.c_port_code, sum(a.N_SELL_MONEY) N_SELL_MONEY2 from T_D_AC_SELL_TA a
where (A.C_PORT_CODE = 'SGU520')
and a.D_SELL = date
'2023-07-01'
and a.C_DS_CODE = 'TAXS_SH'
and a.N_CHECK_STATE = 1
group by a.c_port_code
) a8
ON A.C_PORT_CODE = A8.C_PORT_CODE
where (A.C_PORT_CODE = 'SGU520')
and a.N_CHECK_STATE = 1