csq39 发表于 2017-6-9 16:39:45

不可收费耗材绩效考核数据-科室反馈表格

<p>执行的sql如下：</p><hr/><blockquote><p>select &#39;2017上&#39;,</p><p>&nbsp; &nbsp; &nbsp; &nbsp;&#39;*&#39;,</p><p>&nbsp; &nbsp; &nbsp; &nbsp;F_GET_COM_DICT_NAME(m.source_code, &#39;sourceCode&#39;),</p><p>&nbsp; &nbsp; &nbsp; &nbsp;m.office_id,</p><p>&nbsp; &nbsp; &nbsp; &nbsp;(select org.org_name</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; from tb_usc_org org</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;where org.org_id = m.office_id),</p><p>&nbsp; &nbsp; &nbsp; &nbsp;(select org.org_name</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; from tb_usc_org org</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;where org.org_id = (select decode(org1.parent_org_id,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&#39;00001000&#39;,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;org1.org_id,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;org1.parent_org_id)</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;from tb_usc_org org1</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; where org1.org_id = m.office_id)),</p><p>&nbsp; &nbsp; &nbsp; &nbsp;&#39;&#39;,</p><p>&nbsp; &nbsp; &nbsp; &nbsp;m.wh_to_id XH,</p><p>&nbsp; &nbsp; &nbsp; &nbsp;(select wh_name from tb_usc_warehouse where wh_id = m.wh_bill_id),</p><p>&nbsp; &nbsp; &nbsp; &nbsp;m.import_master_id,</p><p>&nbsp; &nbsp; &nbsp; &nbsp;m.material_id,</p><p>&nbsp; &nbsp; &nbsp; &nbsp;m.material_name,</p><p>&nbsp; &nbsp; &nbsp; &nbsp;m.material_spec,</p><p>&nbsp; &nbsp; &nbsp; &nbsp;F_GET_COM_DICT_NAME((select material.charge_type</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;from tb_usc_material material</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; where material.material_id = m.material_id),</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&#39;chargeType&#39;),</p><p>&nbsp; &nbsp; &nbsp; &nbsp;m.unite_price,</p><p>&nbsp; &nbsp; &nbsp; &nbsp;m.import_quantity,</p><p>&nbsp; &nbsp; &nbsp; &nbsp;m.total,</p><p>&nbsp; &nbsp; &nbsp; &nbsp;m.MC</p><p>&nbsp; from (</p><p>&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;</p><p>&nbsp; &nbsp; &nbsp; &nbsp; select apply.source_code,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; apply.project_number,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; dtl.import_master_id,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (select wu.office_id</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;from tb_usc_warehouse wu</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; where wu.wh_id = import.wh_to_id) office_id,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; import.wh_to_id,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; import.wh_bill_id,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; dtl.material_id,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; dtl.material_name,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; dtl.material_spec,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sum(dtl.import_quantity) import_quantity,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; dtl.unite_price,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sum(dtl.import_quantity * dtl.unite_price) total,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &#39;专购出库&#39; MC</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; from tb_usc_po_master &nbsp; &nbsp; &nbsp;po,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; tb_usc_po_plan_master plan,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; tb_usc_apply_master &nbsp; apply,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; tb_usc_import_master &nbsp;import,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; tb_usc_import_detail &nbsp;dtl</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;where po.source_doc_no = plan.po_plan_master_id</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;and plan.source_doc_no = apply.apply_master_id</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;and import.po_master_id = po.po_master_id</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;and dtl.import_master_id = import.import_master_id</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;and import.status = &#39;05&#39;</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;and dtl.update_date between to_date(&#39;2017-05-31&#39;, &#39;yyyy-mm-dd&#39;) and</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;to_date(&#39;2017-06-01&#39;, &#39;yyyy-mm-dd&#39;)</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;group by apply.source_code,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;apply.project_number,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;dtl.import_master_id,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;import.wh_to_id,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;import.wh_bill_id,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;dtl.material_id,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;dtl.material_name,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;dtl.material_spec,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;dtl.unite_price</p><p>&nbsp; &nbsp; &nbsp; &nbsp; union all</p><p>&nbsp; &nbsp; &nbsp; &nbsp; select apply.source_code,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; apply.project_number,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; dtl.stock_transfer_id,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (select wu.office_id</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;from tb_usc_warehouse wu</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; where wu.wh_id = dtl.wh_to_id) office_id,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; dtl.wh_to_id,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; dtl.wh_from_id,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; dtl.material_id,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; dtl.material_name,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; dtl.material_spec,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sum(dtl.process_quantity),</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; dtl.unite_price,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sum(dtl.process_quantity * dtl.unite_price),</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &#39;库发出库&#39; MC</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; from tb_usc_stock_transfer_master transfer,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; tb_usc_apply_master &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;apply,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; tb_usc_stock_transfer_detail dtl</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;where transfer.apply_master_id = apply.apply_master_id</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;and dtl.stock_transfer_id = transfer.stock_transfer_id</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;and (transfer.status = &#39;04&#39; or</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;(transfer.status = &#39;05&#39; and dtl.status = &#39;Y&#39;))</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;and dtl.update_date between to_date(&#39;2017-05-31&#39;, &#39;yyyy-mm-dd&#39;) and</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;to_date(&#39;2017-06-01&#39;, &#39;yyyy-mm-dd&#39;)</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;group by apply.source_code,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;apply.project_number,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;dtl.stock_transfer_id,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;dtl.wh_to_id,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;dtl.wh_from_id,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;dtl.material_id,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;dtl.material_name,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;dtl.material_spec,</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;dtl.unite_price) m</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;order by &nbsp;6</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;</p><p><br/></p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;</p><p><br/></p></blockquote><p><br/></p>
页: [1]
查看完整版本: 不可收费耗材绩效考核数据-科室反馈表格