create or replace view vw_yf_mzcfmx_byj as
select
A.CFLSH0 PRESC_NO,
1 ITEM_NO,
A.ZB0000 ADVICE_CODE,
A.YPNM00 DRUG_CODE,
A.YPGG00 DRUG_SPEC,
A.YPMC00 DRUG_NAME,
' ' FIRM_ID,
' ' FIRM_NAME,
' ' PACKAGE_SPEC,
' ' PACKAGE_UNITS,
--(A.YPZSL0*A.ZHL000)/nvl(B.YYZHL0,B.M2J000) QUANTITY,
--(A.YPZSL0*A.ZHL000)/b.Z2J000 QUANTITY,
A.YPZSL0 QUANTITY,
--B.YYDW00 UNIT,
--a.JLDW00 UNIT,
a.CFDW00 UNIT,
--A.ZFJE00 COSTS,
ROUND(A.LSDJ00*A.YPZSL0,2) COSTS,
--A.ZFJE00 PAYMENTS,
ROUND(A.LSDJ00*A.YPZSL0,2) PAYMENTS,
A.CSL000 DOSAGE,
A.JLDW00 DOSAGE_UNITS,
(select SM0000 from BM_PC0000 where trim(PCMC00)=trim(a.PCMC00) and rownum=1)||
' ÿ´Î'||TRIM(nvl(a.YFSMMC,' '))||TRIM(nvl(a.YPYFMC,' '))||
--Trim(DECODE(a.CSL000,NULL,'',0,'',DECODE(SUBSTR(ABS(a.CSL000),1,1),'.','0','')||ABS(a.CSL000)||trim(a.JLDW00)))
 to_char(round(((a.CSL000/b.YYZHL0)),2),'fm9999990.99999')||trim(b.YYDW00) ADMINISTRATION,
A.PCMC00 FREQUENCY,
A.XMYFMC ADDITIONUSAGE,
a.YYSJ00||a.YYSJDW||' '
--||A.BZ0000||' '
||(select c.ZDMC00 from YS_BRZDXX c,YF_MZCF00 d where c.GHH000=d.GHH000 and d.CFLSH0=a.CFLSH0 and c.ZZDBZ0='3' and rownum=1 ) RCPT_REMARK,
A.CFLSH0
from YF_MZCFMX A,BM_YD0000 B
where
A.CXBZ00='Z' and A.SFZBY0='N' and A.YPNM00=B.YPNM00
;