从集团角度考虑,以法人公司每年生产领料单和委外发料单为数据基础,以生产领料单的成本对象或委外发料单对应的委外加工件为产品,单据分录的物料为原材料,两者之间形成对应关系,最终找出物料编码为“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 ---物料
这是我写的查询成本对象跟物料查询 求存储过程
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 ---物料
这是我写的查询成本对象跟物料查询 求存储过程
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') ;
这个就可以筛选到啊
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;
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 ---物料
在加上一个表呢 一起怎么写存储过程
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;