從集團角度考慮，以法人公司每年生產領料單和委外發料單為資料基礎，以生產領料單的成本物件或委外發料單對應的委外加工件為產品，單據分錄的物料為原材料，兩者之間形成對應關系，最終找出物料編碼為“1”或“5”開頭的物料對應的成本物件/產品為“8”或字母開頭的成品關系；一共需出具2014-2017年4年對應清單，后續將會長期使用
SELECT COST1.FNumber as "成本物件",
MATERIAL.FNumber as "物料"
FROM NEW_EAS75.T_IM_MaterialReqBill ReqBill ---領料出庫單
INNER JOIN NEW_EAS75.T_MM_ManufactureOrder Order1 ON Order1.Fid=ReqBill.FSourceBillID ---生產訂單
LEFT JOIN NEW_EAS75.T_IM_MaterialReqBillentry REQENTRY ON REQENTRY.FParentID=REQBILL.FID -----領料出庫單單體
LEFT JOIN NEW_EAS75.T_BD_CostObject Cost1 ON COST1.FID=ORDER1.FCOSTOBJECTID ----成本中心
LEFT JOIN NEW_EAS75.T_BD_Material Material ON MATERIAL.FID=REQENTRY.fmaterialid ---物料
這是我寫的查詢成本物件跟物料查詢 求存盤程序
uj5u.com熱心網友回復：
加上這個where條件 就可以了呀SELECT COST1.FNumber as "成本物件",
MATERIAL.FNumber as "物料"
FROM NEW_EAS75.T_IM_MaterialReqBill ReqBill ---領料出庫單
INNER JOIN NEW_EAS75.T_MM_ManufactureOrder Order1 ON Order1.Fid=ReqBill.FSourceBillID ---生產訂單
LEFT JOIN NEW_EAS75.T_IM_MaterialReqBillentry REQENTRY ON REQENTRY.FParentID=REQBILL.FID -----領料出庫單單體
LEFT JOIN NEW_EAS75.T_BD_CostObject Cost1 ON COST1.FID=ORDER1.FCOSTOBJECTID ----成本中心
LEFT JOIN NEW_EAS75.T_BD_Material Material ON MATERIAL.FID=REQENTRY.fmaterialid ---物料
where (regexp_like(COST1.FNumber,'^[15]') or regexp_like(MATERIAL.FNumber,'^[8A-Za-z]') ) and
時間 between to_date('2014-01-01','yyyy-mm-dd') and to_date('2017-12-31','yyyy-mm-dd') ;
uj5u.com熱心網友回復：
要求是找到1和5開頭的物料 8開頭的成品uj5u.com熱心網友回復：
regexp_like(物料,'^[15]') or regexp_like(成品,'^[8A-Za-z]')這個就可以篩選到啊
uj5u.com熱心網友回復：
如果要寫成存盤程序怎么寫 求教大神啊uj5u.com熱心網友回復：
create or replace procedure p_1 (p_ref out sys_refcursor)
is
begin
open p_ref for
SELECT COST1.FNumber as "成本物件",
MATERIAL.FNumber as "物料"
FROM NEW_EAS75.T_IM_MaterialReqBill ReqBill ---領料出庫單
INNER JOIN NEW_EAS75.T_MM_ManufactureOrder Order1 ON Order1.Fid=ReqBill.FSourceBillID ---生產訂單
LEFT JOIN NEW_EAS75.T_IM_MaterialReqBillentry REQENTRY ON REQENTRY.FParentID=REQBILL.FID -----領料出庫單單體
LEFT JOIN NEW_EAS75.T_BD_CostObject Cost1 ON COST1.FID=ORDER1.FCOSTOBJECTID ----成本中心
LEFT JOIN NEW_EAS75.T_BD_Material Material ON MATERIAL.FID=REQENTRY.fmaterialid ---物料
where (regexp_like(COST1.FNumber,'^[15]') or regexp_like(MATERIAL.FNumber,'^[8A-Za-z]') ) and
時間 between to_date('2014-01-01','yyyy-mm-dd') and to_date('2017-12-31','yyyy-mm-dd') ;
end p_1;
uj5u.com熱心網友回復：
selectM1.FNUMBER,
Material.FNumber as "物料"
from NEW_EAS75.T_IM_MaterialReqBill ReqBill---領料出庫單
LEFT JOIN NEW_EAS75.T_IM_MaterialReqBillentry REQENTRY ON REQENTRY.FParentID=REQBILL.FID -----領料出庫單單體
INNER JOIN NEW_EAS75.T_SM_SubContractOrder Order1 ON REQENTRY.FSourceBillID=Order1.Fid--委外訂單
left JOIN NEW_EAS75.T_SM_SubContractOrderEntry Entry1 ON Entry1.FParentID=Order1.fid--委外加工件
LEFT JOIN NEW_EAS75.T_BD_Material Material ON MATERIAL.FID=REQENTRY.fmaterialid ---物料
LEFT JOIN NEW_EAS75.T_BD_Material M1 ON M1.FID=Entry1.fmaterialid ---物料
在加上一個表呢 一起怎么寫存盤程序
uj5u.com熱心網友回復：
樓主把里面的陳述句更新一下就可以
create or replace procedure p_1 (p_ref out sys_refcursor)
is
begin
open p_ref for
select
M1.FNUMBER,
Material.FNumber as "物料"
from NEW_EAS75.T_IM_MaterialReqBill ReqBill---領料出庫單
LEFT JOIN NEW_EAS75.T_IM_MaterialReqBillentry REQENTRY ON REQENTRY.FParentID=REQBILL.FID -----領料出庫單單體
INNER JOIN NEW_EAS75.T_SM_SubContractOrder Order1 ON REQENTRY.FSourceBillID=Order1.Fid--委外訂單
left JOIN NEW_EAS75.T_SM_SubContractOrderEntry Entry1 ON Entry1.FParentID=Order1.fid--委外加工件
LEFT JOIN NEW_EAS75.T_BD_Material Material ON MATERIAL.FID=REQENTRY.fmaterialid ---物料
LEFT JOIN NEW_EAS75.T_BD_Material M1 ON M1.FID=Entry1.fmaterialid ---物料
where (regexp_like(M1.FNUMBER,'^[15]') or regexp_like(MATERIAL.FNumber,'^[8A-Za-z]') ) and
時間 between to_date('2014-01-01','yyyy-mm-dd') and to_date('2017-12-31','yyyy-mm-dd') ;
end p_1;
uj5u.com熱心網友回復：
求存盤程序
.從集團角度考慮，以法人公司每年生產領料單和委外發料單為資料基礎，以生產領料單的成本物件或委外發料單對應的委外加工件為產品，單據分錄的物料為原材料，兩者之間形成對應關系，最終找出物料編碼為“1”或“5”開頭的物料對應的成本物件/產品為“8”或字母開頭的成品關系；一共需出具2014-2017年4年對應清單，后續將會長期使用
問題來了 就是按這個寫個存盤程序怎么寫？？
uj5u.com熱心網友回復：
2個表都在上面轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/93573.html
標籤：開發
上一篇：利用powerdesigner建模時生成的觸發器怎么修改默認命名規則
下一篇：powerdesigner生成的sql檔案匯入oracle產生Warning: Trigger created with compilation errors