csq39 发表于 2017-6-9 16:49:42

申领单处理时，可用库存量不正确排查方法

<p>1：首先根据申领单号，查看申领单内物资的可用库存</p><p style="text-indent: 2em;">select&nbsp;</p><p style="text-indent: 2em;">F_GET_STOCK_USEABLE_QTY(dtl.material_id,&#39;100560&#39;,null,null,dtl.unpack_yn),</p><p style="text-indent: 2em;">dtl.* from tb_usc_apply_detail dtl where dtl.apply_master_id=&#39;SL2017060812994&#39;</p><p>2：如果显示的可用量确实有问题，则将排查可用量计算的sql，查看数据可能出现在那部分：1：实际库存，2：占用库存量</p><p style="text-indent: 2em;">SELECT M2.USEABLE_QTY - NVL(M1.USEABLE_QTY, 0) USEABLE_QTY</p><p style="text-align: left; text-indent: 0em;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; FROM (</p><p style="text-align: left; text-indent: 0em;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECT &nbsp;SUM(P.PICKING_QUANTITY) USEABLE_QTY</p><p style="text-align: left; text-indent: 0em;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM TB_USC_PICKING P</p><p style="text-align: left; text-indent: 0em;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHERE P.STOCK_TRANSFER_ID IN(</p><p style="text-align: left; text-indent: 0em;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECT DISTINCT STM.STOCK_TRANSFER_ID</p><p style="text-align: left; text-indent: 0em;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM TB_USC_STOCK_TRANSFER_MASTER STM</p><p style="text-align: left; text-indent: 0em;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHERE STM.UNPACK_YN = &#39;N&#39;</p><p style="text-align: left; text-indent: 0em;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )</p><p style="text-align: left; text-indent: 0em;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND P.MATERIAL_ID = &#39;<span style="text-decoration: underline; border: 1px solid rgb(0, 0, 0); background-color: rgb(141, 179, 226);">685600000001749</span>&#39;</p><p style="text-align: left; text-indent: 0em;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND P.STATUS = &#39;N&#39;) M1,</p><p style="text-align: left; text-indent: 0em;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;(</p><p style="text-align: left; text-indent: 0em;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECT SUM(JS.JIT_STOCK_AVAILABLE_QTY) USEABLE_QTY &nbsp;</p><p style="text-align: left; text-indent: 0em;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM TB_USC_JIT_STOCK JS</p><p style="text-align: left; text-indent: 0em;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHERE JS.WH_ID = &#39;100109&#39;</p><p style="text-align: left; text-indent: 0em;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND JS.UNPACK_YN = &nbsp;&#39;N&#39;</p><p style="text-align: left; text-indent: 0em;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND JS.MATERIAL_ID = &nbsp;&#39;<span style="border: 1px solid rgb(0, 0, 0); background-color: rgb(141, 179, 226);">685600000001749</span>&#39;) M2;</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;</p><p>&nbsp;&nbsp;可能需要查询对应的仓库ID使用：select * from tb_usc_warehouse where wh_name like &#39;%卫生材料库(低值)%&#39;</p><p>&nbsp;3：如果确认是占用量存在问题，查看实际占用物资的单据： &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;</p><p>&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT *</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM TB_USC_PICKING P</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHERE P.STOCK_TRANSFER_ID IN(</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECT DISTINCT STM.STOCK_TRANSFER_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; FROM TB_USC_STOCK_TRANSFER_MASTER STM</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHERE STM.UNPACK_YN = &#39;N&#39;</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND P.MATERIAL_ID = &#39;685600000001749&#39;</p><p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND P.STATUS = &#39;N&#39;</p><p><br/></p><p><br/></p><p>附：</p><p>今天遇到的问题均是因为中途变更定数包单位，造成修改定数包之前的单据不能正常处理业务。</p><p><br/></p>
页: [1]
查看完整版本: 申领单处理时，可用库存量不正确排查方法