Yield Report SQL Learning

/*dialect*/
With Tab1 as (
SELECT DISTINCT
Entry. Fmanubillid Manuorderid,
SUM (entry.fqty) qty--quantity of goods in storage
From T_im_manufacturerecbill Bill
INNER JOIN T_im_manufacturerecbillentry entry on entry.fparentid = Bill.fid
INNER JOIN T_scm_transactiontype transtype on transtype.fid = Bill.ftransactiontypeid
INNER JOIN T_org_storage tos on tos.fid = Bill. Fstorageorgunitid
INNER JOIN T_mm_manufactureorder TMM on TMM. Fmaterialid = entry. Fmaterialid
INNER JOIN t_bd_material TBM on tbm.fid = entry. Fmaterialid
INNER JOIN T_db_warehouse warehouse on warehouse.fid = Entry.fwarehouseid
where entry. Fmanubillid = Tmm.fid
and bill.fbasestatus = 4
and warehouse.fname_l2 not like '% defective% '
and warehouse.fname_l2 not like '% spent% '
and warehouse.fname_l2 not like '%b product% '
and (Tos.fid in (' gjyaaaaabn/m567u ') or 0 is null)
and (Tbm.fnumber >= "or null is NULL)
and (Tbm.fnumber <= "or null is NULL)
and (Tmm.fnumber in "or null is NULL)
and (TMM. Fadminorgunitid in "or null is NULL)
and To_char (TMM. Fbizdate, ' yyyy-mm-dd ') <= ' 2018-06-08 '
and To_char (TMM. Fbizdate, ' yyyy-mm-dd ') >= ' 2017-10-01 '
and (tmm.fbasestatus = "or null is NULL)
and Transtype.fnumber in (' 024A ', ' 026C ', ' 902A ', ' 902D ', ' 901 ', ' 901C ')--production of good goods warehousing, production warehousing (rework), AG-Outsourcing processing-Good goods warehousing, AG-Outsourcing processing-sample warehousing, commissioned processing products, samples
Group by entry. Fmanubillid
)
,
TaB2 as (
--b Products
SELECT DISTINCT
Entry. Fmanubillid Manuorderid,
SUM (entry.fqty) Qty
From T_im_manufacturerecbill Bill
INNER JOIN T_im_manufacturerecbillentry entry on entry.fparentid = Bill.fid
INNER JOIN T_scm_transactiontype transtype on transtype.fid = Bill.ftransactiontypeid
INNER JOIN T_org_storage tos on tos.fid = Bill. Fstorageorgunitid
INNER JOIN T_mm_manufactureorder TMM on tmm.fid = Entry.fmanubillid
INNER JOIN T_db_warehouse warehouse on warehouse.fid = Entry.fwarehouseid
INNER JOIN t_bd_material TBM on tbm.fid = entry. Fmaterialid
Where (Tos.fid in (' gjyaaaaabn/m567u ') or 0 is null)
and To_char (TMM. Fbizdate, ' yyyy-mm-dd ') <= ' 2018-06-08 '
and To_char (TMM. Fbizdate, ' yyyy-mm-dd ') >= ' 2017-10-01 '
and (Tbm.fnumber >= "or null is NULL)
and (Tbm.fnumber <= "or null is NULL)
and (Tmm.fnumber in "or null is NULL)
and (TMM. Fadminorgunitid in "or null is NULL)
and warehouse.fname_l2 like '%b product% '
--and Tmm.fnumber = ' mls-1-20180306-0001 '
and (tmm.fbasestatus = "or null is NULL)
and bill.fbasestatus = 4
and Transtype.fnumber not in (' 024A ', ' 902A ', ' 902D ', ' 901 ', ' 901C ')--production of good goods warehousing, stock companies-outsourcing processing-Good goods warehousing, ag-Outsourcing processing-sample warehousing, commissioned processing products, samples
Group by entry. Fmanubillid
)
,
Tab3 as (
---defective
SELECT DISTINCT
Entry. Fmanubillid Manuorderid
, Sum (entry.fqty) Qty
From T_im_manufacturerecbill Bill
INNER JOIN T_im_manufacturerecbillentry entry on entry.fparentid = Bill.fid
INNER JOIN T_org_storage tos on tos.fid = Bill. Fstorageorgunitid
INNER JOIN T_scm_transactiontype transtype on transtype.fid = Bill.ftransactiontypeid
INNER JOIN T_db_warehouse warehouse on warehouse.fid = Entry.fwarehouseid
INNER JOIN t_bd_material TBM on tbm.fid = entry. Fmaterialid
INNER JOIN T_mm_manufactureorder TMM on tmm.fid = Entry.fmanubillid
Where (Tos.fid in (' gjyaaaaabn/m567u ') or 0 is null)
and To_char (TMM. Fbizdate, ' yyyy-mm-dd ') <= ' 2018-06-08 '
and To_char (TMM. Fbizdate, ' yyyy-mm-dd ') >= ' 2017-10-01 '
and Transtype.fnumber not in (' 024A ', ' 902A ', ' 902D ', ' 901 ', ' 901C ')--production of good goods warehousing, stock companies-outsourcing processing-Good goods warehousing, ag-Outsourcing processing-sample warehousing, commissioned processing products, samples
and (Tbm.fnumber >= "or null is NULL)
and (Tbm.fnumber <= "or null is NULL)
and (Tmm.fnumber in "or null is NULL)
and (TMM. Fadminorgunitid in "or null is NULL)
And warehouse.fname_l2 like '% defective% '
and (tmm.fbasestatus = "or null is NULL)
and bill.fbasestatus = 4
Group by entry. Fmanubillid
),
Tab4 as (
--Scrap
SELECT DISTINCT
Entry. Fmanubillid Manuorderid
, Sum (entry.fqty) Qty
From T_im_manufacturerecbill Bill
INNER JOIN T_im_manufacturerecbillentry entry on entry.fparentid = Bill.fid
INNER JOIN T_org_storage tos on tos.fid = Bill. Fstorageorgunitid
INNER JOIN T_scm_transactiontype transtype on transtype.fid = Bill.ftransactiontypeid
INNER JOIN T_db_warehouse warehouse on warehouse.fid = Entry.fwarehouseid
INNER JOIN t_bd_material TBM on tbm.fid = entry. Fmaterialid
INNER JOIN T_mm_manufactureorder TMM on tmm.fid = Entry.fmanubillid
where Tos.fid in (' gjyaaaaabn/m567u ')
and To_char (TMM. Fbizdate, ' yyyy-mm-dd ') <= ' 2018-06-08 '
and To_char (TMM. Fbizdate, ' yyyy-mm-dd ') >= ' 2017-10-01 '
and Transtype.fnumber not in (' 024A ', ' 902A ', ' 902D ', ' 901 ', ' 901C ')--production of good goods warehousing, stock companies-outsourcing processing-Good goods warehousing, ag-Outsourcing processing-sample warehousing, commissioned processing products, samples
and (Tbm.fnumber >= "or null is NULL)
and (Tbm.fnumber <= "or null is NULL)
and (Tmm.fnumber = "or null is NULL)
and (TMM. Fadminorgunitid = "or null is NULL)
And warehouse.fname_l2 like '% spent% '
and (tmm.fbasestatus = "or null is NULL)
and bill.fbasestatus = 4
Group by entry. Fmanubillid
)
, Tab5 as (
SELECT DISTINCT
Tmm.fnumber SCDDH
, SUM (time1.fqty) SL
From
T_im_materialreqbill Tim
INNER join T_im_materialreqbillentry time1 on time1.fparentid = Tim.fid
INNER join T_mm_manufactureorder TMM on tmm.fnumber = time1. Fordernumber
INNER join T_bd_material TBM on tbm.fid = time1. Fmaterialid
where To_char (TMM. Fbizdate, ' yyyy-mm-dd ') <= ' 2018-06-08 '
and To_char (TMM. Fbizdate, ' yyyy-mm-dd ') >= ' 2017-10-01 '
and (Tbm.fnumber like ' 01.01.02% ' or tbm.fnumber like ' 02.03.20% ' or tbm.fnumber like ' 05.10.03.003% ')
and tim.fbasestatus = 4
GROUP BY Tmm.fnumber
ORDER by Tmm.fnumber DESC
)
, Tab6 as (

SELECT DISTINCT
Tmm.fnumber SCDDH
, SUM (time1.fqty) SL
From
T_im_materialreqbill Tim
INNER join T_im_materialreqbillentry time1 on time1.fparentid = Tim.fid
INNER join T_mm_manufactureorder TMM on tmm.fnumber = time1. Fordernumber
Left join T_bd_material TBM on tbm.fid = time1. Fmaterialid
where To_char (TMM. Fbizdate, ' yyyy-mm-dd ') <= ' 2018-06-08 '
and To_char (TMM. Fbizdate, ' yyyy-mm-dd ') >= ' 2017-10-01 '
and (Tbm.fnumber like ' 01.01.01.010% ' or tbm.fnumber like ' 02.10.03.001% ' or tbm.fnumber like ' 05.10.22.001% ')
and tim.fbasestatus = 4
GROUP BY Tmm.fnumber

ORDER by Tmm.fnumber DESC
)
Select
Tmm.fnumber production order number,--Production order number
To_char (tmm.fbizdate, ' yyyy-mm-dd ') business date,
Toa. FNAME_L2 Cost center,
Tbm.fnumber material code,--Material code
TBM.FNAME_L2 Item Name,--Item name
Tbm.fmodel specification model,--specification model
NVL (tbh.fgoodrates,0) standard yield,--standard yield
To_char (tbh.feffectdate, ' yyyy-mm-dd ') effective date,--Effective date
To_char (tbh.fexpirydate, ' yyyy-mm-dd ') expiration date,--Expiration date
TBM1.FNAME_L2 Unit,--unit
Tmm. Fqty production order quantity,--Production order quantity
NVL (tab1.qty,0) Good goods storage quantity,--good goods storage quantity
NVL (bp.qty,0) b goods Storage quantity,--Defective storage quantity
NVL (cp.qty,0) Defective storage quantity,--waste storage quantity
NVL (fp.qty,0) Waste storage quantity,--b goods storage quantity
Tmm. FQTY-NVL (tab1.qty,0)-NVL (bp.qty,0)-NVL (cp.qty,0)-NVL (fp.qty,0) quantity not in storage,
Decode (TMM. FQTY-NVL (tab1.qty,0)-NVL (bp.qty,0)-NVL (cp.qty,0)-NVL (fp.qty,0), ' 0 ', ' OK ') is the list,--increased here
Cast (Decode (TAB1.QTY*100,0,0,NVL (tab1.qty,0)/tmm. FQTY*100) as number (18,2)) The ratio of physical storage of good goods,--increased here
(CAST (Decode (TAB1.QTY*100,0,0,NVL (tab1.qty,0)/tmm. FQTY*100) as number (18,2)) + (CAST (decode (TMM. FQTY*100,0,0,NVL (bp.qty,0)/tmm. FQTY*100) as number (18,2)) The percentage of physical storage of good product B,--increased here
Cast (Decode (TMM). FQTY*100,0,0,NVL (bp.qty,0)/tmm. FQTY*100) as number (18,2)) b product physical storage ratio,
Cast (Decode (TMM). FQTY*100,0,0,NVL (cp.qty,0)/tmm. FQTY*100) as number (18,2)) defective actual storage ratio,--defective actual storage ratio% (defective storage quantity/production order quantity *100)
Cast (Decode (TMM). fqty*100,0,0, NVL (fp.qty,0)/tmm. FQTY*100) as number (18,2)) The actual proportion of waste,--the percentage of actual waste to be put into storage (scrap quantity/production order quantity *100)
Cast (Decode (TMM). fqty*100,0,0,100-tbh.fgoodrates-(NVL (bp.qty,0)/tmm. fqty*100+ NVL (cp.qty,0)/tmm. FQTY*100+NVL (fp.qty,0)/tmm. FQTY*100) as number (18,2)) The difference in defective rate of B-times,--increased here
To_char (TMM. Fcreatetime, ' yyyy-mm-dd ') Order Date,--increased here
To_char (TMM. fcreatetime+30, ' yyyy-mm-dd ') expiry date,--increased here
Case when To_char (Add_months (TMM. fcreatetime,1), ' Yyyy-mm-dd ') <= to_char (sysdate, ' yyyy-mm-dd ') Then ' expiring ' else ' end expiration prompt
, To_char (sysdate, ' yyyy-mm-dd ') Day date,--Added here
Cbc.cfparticlecount number of core grains 3--increased here.
, T5.SL collar Bracket
, T6.SL collar Chip
, case if T6.sl/cbc.cfparticlecount-t5.sl=0 then ' is ' else ' no ' end match hint
, Decode (Tmm.fbasestatus, ' 1 ', ' Save ', ' 2 ', ' Commit ', ' 3 ', ' void ', ' 4 ', ' Audit ', ' 5 ', ' Release ', ' 6 ', ' Freeze ', ' 7 ', ' Off ', ' 8 ', ' Finish ', ' other ') document status
, TMM.FREMARK_L2 notes
From T_mm_manufactureorder TMM
Left joins T_bas_yieldbasedata tbh on tbh.fproductnumberid = Tmm.fmaterialid and TMM. Fstorageorgunitid = Tbh.forgunitid and tbh.fstatus = ' 1 '
Left joins T_bd_measureunit tbm1 on tbm1.fid = TMM. Funitid
Left join T_org_storage tos on tos.fid = TMM. Fstorageorgunitid
Left join tab1 on tab1.manuorderid = Tmm.fid
Left join tab2 bp on bp.manuorderid = Tmm.fid
Left JOIN TAB3 cp on cp.manuorderid = Tmm.fid
Left join TAB4 fp on fp.manuorderid = Tmm.fid
Left join TAB5 T5 on T5.SCDDH = Tmm.fnumber
Left join Tab6 T6 on T6.SCDDH = Tmm.fnumber--Production order Number Association
Left join T_org_admin toa on toa.fid = TMM. Fadminorgunitid
Left join T_bd_material TBM on tbm.fid = TMM. Fmaterialid
Left join T_im_materialreqbillentry entry1 on tmm.fnumber= entry1.fordernumber--Added here
INNER join T_im_materialreqbill tim on tim.fid = Entry1.fparentid
Left JOIN Ct_bas_coreprtnumber CBC on cbc.cfmaterailnumberid = Tbm.fid--Added here
where 1=1
and (Tos.fid in (' gjyaaaaabn/m567u ') or 0 is null)
and To_char (TMM. Fbizdate, ' yyyy-mm-dd ') <= ' 2018-06-08 '
and To_char (TMM. Fbizdate, ' yyyy-mm-dd ') >= ' 2017-10-01 '
and (Tbm.fnumber >= "or null is NULL)
and (Tbm.fnumber <= "or null is NULL)
and (Tmm.fnumber in "or null is NULL)
and (TMM. Fadminorgunitid in (") or null is NULL)
and (tmm.fbasestatus = "or null is NULL)
and tmm.fbasestatus = ' 5 '
and tim.fbasestatus = ' 4 '
GROUP BY Tmm.fnumber
, To_char (tmm.fbizdate, ' yyyy-mm-dd ')
, Toa. Fname_l2,substr (tbm.fnumber,0,8)
, substr (tbm.fnumber,0,12)
, Tbm.fname_l2,tbm.fmodel
, NVL (tbh.fgoodrates,0)
, To_char (tbh.feffectdate, ' yyyy-mm-dd ')
, To_char (tbh.fexpirydate, ' yyyy-mm-dd ')
, TBM1.FNAME_L2,TMM. Fqty
, NVL (tab1.qty,0)
, NVL (bp.qty,0)
, NVL (cp.qty,0)
, NVL (fp.qty,0)
, Cast (Decode (TAB1.QTY*100,0,0,NVL (tab1.qty,0)/tmm. FQTY*100) as number (18,2))
, Cast (Decode (TMM). fqty*100,0,0,100-tbh.fgoodrates-(NVL (bp.qty,0)/tmm. fqty*100+ NVL (cp.qty,0)/tmm. FQTY*100+NVL (fp.qty,0)/tmm. FQTY*100)) as number (18,2))
, To_char (TMM. Fcreatetime, ' Yyyy-mm-dd '), To_char (Add_months (TMM. fcreatetime,1), ' Yyyy-mm-dd ')
, Cbc.cfparticlecount
, T5.SL
, T6.SL
, Case when To_char (Add_months (TMM. fcreatetime,1), ' Yyyy-mm-dd ') <= to_char (sysdate, ' yyyy-mm-dd ') Then ' expires ' else ' end
, Decode (Tmm.fbasestatus, ' 1 ', ' Save ', ' 2 ', ' Commit ', ' 3 ', ' void ', ' 4 ', ' Audit ', ' 5 ', ' Release ', ' 6 ', ' Freeze ', ' 7 ', ' Off ', ' 8 ', ' Finish ', ' other ')
, Tmm.fremark_l2,tbm.fnumber, To_char (TMM. fcreatetime+30, ' Yyyy-mm-dd ')
Order by Toa. FNAME_L2 ASC
(The stent is cross-stocking when picking out the library, so it is not visible in the interface to select multiple inventory organizations)

(If you are intercepting string groupings, write subqueries!) Note that you must match the query criteria)

Yield Report SQL Learning