Stored procedure code to find the material code for the "1" or "5" the beginning-CodePudding

Think in terms of group, the company with legal person every year production requisition and appoint outbound order material for data base, object to the cost of production material requisition or corresponding outbound order material outsourcing processing parts for product, document entry materials as raw materials, form the corresponding relationship between, finally find out the material code for the "1" or "5" at the beginning of the material cost of the corresponding object/product for "eight" or letter of finished products; Need to issue a total of 2014-2017, four years the corresponding listing, follow-up will be use for a long time

SELECT COST1. FNumber as "cost object,"
MATERIAL. FNumber as "MATERIAL"
The FROM NEW_EAS75. T_IM_MaterialReqBill ReqBill - picking outbound order
INNER JOIN NEW_EAS75. T_MM_ManufactureOrder Order1 ON Order1, Fid=ReqBill. FSourceBillID - production order
LEFT the JOIN NEW_EAS75. T_IM_MaterialReqBillentry REQENTRY ON REQENTRY. FParentID=REQBILL. FID - picking outbound order monomer
LEFT the JOIN NEW_EAS75. T_BD_CostObject Cost1 ON Cost1. FID=ORDER1. FCOSTOBJECTID -- -- cost center
LEFT the JOIN NEW_EAS75. T_BD_Material Material ON the Material. The FID=REQENTRY. Fmaterialid materials -- -- --

This is me to write the query cost object query and stored procedure with material

CodePudding user response:

Plus the where condition is ok!

 SELECT COST1. FNumber as "cost object," 
MATERIAL. FNumber as "MATERIAL" 
The FROM NEW_EAS75. T_IM_MaterialReqBill ReqBill - picking outbound order 
INNER JOIN NEW_EAS75. T_MM_ManufactureOrder Order1 ON Order1, Fid=ReqBill. FSourceBillID - production order 
LEFT the JOIN NEW_EAS75. T_IM_MaterialReqBillentry REQENTRY ON REQENTRY. FParentID=REQBILL. FID - picking outbound order monomer 
LEFT the JOIN NEW_EAS75. T_BD_CostObject Cost1 ON Cost1. FID=ORDER1. FCOSTOBJECTID -- -- cost center 
LEFT the JOIN NEW_EAS75. T_BD_Material Material ON the Material. The FID=REQENTRY. Fmaterialid materials -- -- -- 

The where (regexp_like (COST1 FNumber, '^' [15] ') or regexp_like (MATERIAL) FNumber, '^ 8 a Za - z] [')) and 
Time between to_date (' 2014-01-01 ', '- dd yyyy - mm) and to_date (' 2017-12-31', '- dd yyyy - mm);

CodePudding user response:

Requirement is to find 8 1 and 5 at the beginning of the material at the beginning of the finished product

CodePudding user response:

Regexp_like (material, '^' [15] ') or regexp_like (finished goods, '^ [8 a Za - z]')
This to filter

CodePudding user response:

If you want to write a stored procedure how to write for a great god

CodePudding user response:

 create or replace procedure p_1 (p_ref out sys_refcursor) 
Is 
The begin 
The open p_ref for 
SELECT COST1. FNumber as "cost object," 
MATERIAL. FNumber as "MATERIAL" 
The FROM NEW_EAS75. T_IM_MaterialReqBill ReqBill - picking outbound order 
INNER JOIN NEW_EAS75. T_MM_ManufactureOrder Order1 ON Order1, Fid=ReqBill. FSourceBillID - production order 
LEFT the JOIN NEW_EAS75. T_IM_MaterialReqBillentry REQENTRY ON REQENTRY. FParentID=REQBILL. FID - picking outbound order monomer 
LEFT the JOIN NEW_EAS75. T_BD_CostObject Cost1 ON Cost1. FID=ORDER1. FCOSTOBJECTID -- -- cost center 
LEFT the JOIN NEW_EAS75. T_BD_Material Material ON the Material. The FID=REQENTRY. Fmaterialid materials -- -- -- 

The where (regexp_like (COST1 FNumber, '^' [15] ') or regexp_like (MATERIAL) FNumber, '^ 8 a Za - z] [')) and 
Time between to_date (' 2014-01-01 ', '- dd yyyy - mm) and to_date (' 2017-12-31', '- dd yyyy - mm); 
End p_1;

CodePudding user response:

Select
M1 FNUMBER,
Material. FNumber as "Material"
The from NEW_EAS75. T_IM_MaterialReqBill ReqBill - picking outbound order
LEFT the JOIN NEW_EAS75. T_IM_MaterialReqBillentry REQENTRY ON REQENTRY. FParentID=REQBILL. FID - picking outbound order monomer
INNER JOIN NEW_EAS75. T_SM_SubContractOrder Order1 ON REQENTRY. FSourceBillID=Order1. Fid -- -- outsourcing order
Left the JOIN NEW_EAS75. T_SM_SubContractOrderEntry Entry1 ON Entry1. FParentID=Order1. Fid -- -- outsourcing machined part
LEFT the JOIN NEW_EAS75. T_BD_Material Material ON the Material. The FID=REQENTRY. Fmaterialid materials -- -- --
LEFT the JOIN NEW_EAS75. T_BD_Material M1 ON M1. FID=Entry1. Fmaterialid materials -- -- --
Combined with a table together how to write a stored procedure

CodePudding user response:

It is ok to put the inside of the building Lord statement updated

 
The create or replace procedure p_1 (p_ref out sys_refcursor) 
Is 
The begin 
The open p_ref for 
Select 
M1 FNUMBER, 
Material. FNumber as "Material" 
The from NEW_EAS75. T_IM_MaterialReqBill ReqBill - picking outbound order 
LEFT the JOIN NEW_EAS75. T_IM_MaterialReqBillentry REQENTRY ON REQENTRY. FParentID=REQBILL. FID - picking outbound order monomer 
INNER JOIN NEW_EAS75. T_SM_SubContractOrder Order1 ON REQENTRY. FSourceBillID=Order1. Fid -- -- outsourcing order 
Left the JOIN NEW_EAS75. T_SM_SubContractOrderEntry Entry1 ON Entry1. FParentID=Order1. Fid -- -- outsourcing machined part 
LEFT the JOIN NEW_EAS75. T_BD_Material Material ON the Material. The FID=REQENTRY. Fmaterialid materials -- -- -- 
LEFT the JOIN NEW_EAS75. T_BD_Material M1 ON M1. FID=Entry1. Fmaterialid materials -- -- -- 

The where (regexp_like (M1) FNUMBER, '^' [15] ') or regexp_like (MATERIAL) FNUMBER, '^ 8 a Za - z] [')) and 
Time between to_date (' 2014-01-01 ', '- dd yyyy - mm) and to_date (' 2017-12-31', '- dd yyyy - mm); 
End p_1;

CodePudding user response:

Strives for the stored procedure
. Think in terms of group, the company with legal person every year production requisition and appoint outbound order material for data base, object to the cost of production material requisition or corresponding outbound order material outsourcing processing parts for product, document entry materials as raw materials, form the corresponding relationship between, finally find out the material code for the "1" or "5" at the beginning of the material cost of the corresponding object/product for "eight" or letter of finished products; Need to issue a total of 2014-2017, four years the corresponding listing, follow-up will be use for a long time
Questions to write a stored procedure is according to the how to write??

CodePudding user response:

2 in the above table