create or replace view SO_MONEY_DETAIL as
select substr(snrh.dbilldate || snih.dbilldate, 0, 10) dbilldate,
substr(snrh.vorderbillcode || snih.vorderbillcode, 0, 40) ddh,
'' bah, snih.ntotalmny fpzje, snrh.total skje,
snih.retainage wk,0 rnreceivedmny,0 inreceivedmny,snih.vbillcode fph,
substr(snrh.cdept || snih.cdept, 0, 20) cdept,
snrh.billmaker ||','|| snih.billmaker billmaker,substr(snrh.sohbillmaker || snih.sohbillmaker, 0, 20) sohbillmaker
from (select srh.dbilldate, srh.effectbillcode, sum(srh.total) total,
srh.vorderbillcode, srh.cdept, srh.billmaker,soh.nreceivedmny,soh.billmaker sohbillmaker
from so_receipt_h srh left join so_order_h soh
on srh.vsrcid = soh.id
where srh.dr = 0
group by srh.dbilldate,srh.effectbillcode, vorderbillcode, srh.cdept,
srh.billmaker,soh.nreceivedmny,soh.billmaker) snrh
full outer join (select sih.dbilldate,sih.effectbillcode, sum(sih.ntotalmny) ntotalmny,
sih.vorderbillcode, sum(sih.retainage) retainage,
wm_concat(sih.vbillcode) vbillcode, sih.cdept,
sih.billmaker,soh.nreceivedmny,soh.billmaker sohbillmaker
from so_invoice_h sih left join so_order_h soh
on sih.vsrcid = soh.id
where sih.dr = 0
group by sih.vorderbillcode,sih.effectbillcode,sih.dbilldate, sih.cdept,
sih.billmaker,soh.nreceivedmny,soh.billmaker) snih
on snrh.vorderbillcode = snih.vorderbillcode
and snrh.dbilldate = snih.dbilldate
order by fph
/
