select '2017上',
'*',
F_GET_COM_DICT_NAME(m.source_code, 'sourceCode'),
m.office_id,
(select org.org_name
from tb_usc_org org
where org.org_id = m.office_id),
(select org.org_name
from tb_usc_org org
where org.org_id = (select decode(org1.parent_org_id,
'00001000',
org1.org_id,
org1.parent_org_id)
from tb_usc_org org1
where org1.org_id = m.office_id)),
'',
m.wh_to_id XH,
(select wh_name from tb_usc_warehouse where wh_id = m.wh_bill_id),
m.import_master_id,
m.material_id,
m.material_name,
m.material_spec,
F_GET_COM_DICT_NAME((select material.charge_type
from tb_usc_material material
where material.material_id = m.material_id),
'chargeType'),
m.unite_price,
m.import_quantity,
m.total,
m.MC
from (
select apply.source_code,
apply.project_number,
dtl.import_master_id,
(select wu.office_id
from tb_usc_warehouse wu
where wu.wh_id = import.wh_to_id) office_id,
import.wh_to_id,
import.wh_bill_id,
dtl.material_id,
dtl.material_name,
dtl.material_spec,
sum(dtl.import_quantity) import_quantity,
dtl.unite_price,
sum(dtl.import_quantity * dtl.unite_price) total,
'专购出库' MC
from tb_usc_po_master po,
tb_usc_po_plan_master plan,
tb_usc_apply_master apply,
tb_usc_import_master import,
tb_usc_import_detail dtl
where po.source_doc_no = plan.po_plan_master_id
and plan.source_doc_no = apply.apply_master_id
and import.po_master_id = po.po_master_id
and dtl.import_master_id = import.import_master_id
and import.status = '05'
and dtl.update_date between to_date('2017-05-31', 'yyyy-mm-dd') and
to_date('2017-06-01', 'yyyy-mm-dd')
group by apply.source_code,
apply.project_number,
dtl.import_master_id,
import.wh_to_id,
import.wh_bill_id,
dtl.material_id,
dtl.material_name,
dtl.material_spec,
dtl.unite_price
union all
select apply.source_code,
apply.project_number,
dtl.stock_transfer_id,
(select wu.office_id
from tb_usc_warehouse wu
where wu.wh_id = dtl.wh_to_id) office_id,
dtl.wh_to_id,
dtl.wh_from_id,
dtl.material_id,
dtl.material_name,
dtl.material_spec,
sum(dtl.process_quantity),
dtl.unite_price,
sum(dtl.process_quantity * dtl.unite_price),
'库发出库' MC
from tb_usc_stock_transfer_master transfer,
tb_usc_apply_master apply,
tb_usc_stock_transfer_detail dtl
where transfer.apply_master_id = apply.apply_master_id
and dtl.stock_transfer_id = transfer.stock_transfer_id
and (transfer.status = '04' or
(transfer.status = '05' and dtl.status = 'Y'))
and dtl.update_date between to_date('2017-05-31', 'yyyy-mm-dd') and
to_date('2017-06-01', 'yyyy-mm-dd')
group by apply.source_code,
apply.project_number,
dtl.stock_transfer_id,
dtl.wh_to_id,
dtl.wh_from_id,
dtl.material_id,
dtl.material_name,
dtl.material_spec,
dtl.unite_price) m
order by 6