I have a script that shows delivery qty data for several manufacturing components. Each component has a different new release date so data will run from a different start date (earliest date is 01/07/2007).
Full script is posted below. The script that I would like to add is ...
* SCRIPT REMOVED *
... this shows the sum of the delivered qty from new release date for a 91 day period (3 months). I could repeat this sub select for each 3 month period but, I would have to add more sub selects as time goes on to keep data running to date.
Is there a way to run a loop so to return the delivered qty every 91 days from the new release to date?
* SCRIPT REMOVED *
OK ... thanks for all your advice re: JOIN's and subqueries.
I have revised the JOINS in the script and removed many of the subqueries (full script below).
My question now goes back to the creation of a loop in SQL. Instead of creating dozens of subqueries to show the delivery qty for specified periods of time, could I create a loop that will return the sum delivery qty, per month from the new release date (NR_DATE) to the current date. Subquery would potentially look like the following (and then repeated for each required period)...
(select nvl(sum(dp.del_qty),0) from bds_dhead@sid_to_cdsuk dh, bds_dline@sid_to_cdsuk dp where dp.dhead_no = dh.dhead_no and dh.d_status = '9' and dp.article_no = '9'||nrb.p_catnr and trunc(dh.actshpdate) > trunc(sysdate - 30)) sum_CDS_delqty_30_days
I'm hoping I'm making some sense ... apologies if I'm not clear. I've only been writing SQL for a few months and have taught myself using existing, OLD, reports.
Thanks
FULL SCRIPT
select pd.part_no,
pd.catnr,
pd.prodtyp,
pd.packtyp,
pd.description,
ip.purchase_type,
nvl(pd.fod_idc, 'N'),
(select max(acp.qty_free_idc)
from oes_fod_match acp
where pd.part_no = acp.part_no
and acp.type = 'SUP'
and acp.cre_dat = (select max(acp1.cre_dat)
from oes_fod_match acp1
where acp.part_no = acp1.part_no
and acp1.qty_free > 0
and acp1.type = 'SUP')) qty_free_idc,
(select nvl(sum(pl.qty_onhand), 0)
from part_loc pl
where pl.part_no = pd.part_no
and pl.location_type = 'IN'
and pl.plant in ('A',
'T',
'L')) current_stock_at_comp,
(select nvl(sum(pl.qty_onhand), 0)
from part_loc pl
where pl.part_no = pd.part_no
and pl.location_type = 'IN'
and pl.plant in ('Z',
'V')) current_stock_cz_comp,
(select nvl(sum(pl.qty_onhand), 0)
from part_loc pl
where pl.part_no = pd.part_no
and pl.location_type = 'IN'
and pl.plant in ('W',
'2')) current_stock_uk_comp,
(select nvl(sum(pl.qty_onhand), 0)
from part_loc pl
where pl.part_no = pd.part_no
and pl.location_type = 'IN') total_stock_comp,
(select nvl(max(val_strg1), 0)
from sd_domainval dv
where dv.val_strg = pd.part_no) min_stock_lvl,
(select nvl(max(val_strg2), 0)
from sd_domainval dv
where dv.val_strg = pd.part_no) min_order_qty,
(select nvl(max(val_strg3), 'ND')
from sd_domainval dv
where dv.val_strg = pd.part_no) pref_assembly_plant,
(select nvl(max(pp.qty), 0)
from oes_purpos pp
where pp.av_part_no = pd.part_no
and pp.c_status != 'D'
and pp.datneu = (select max(pp1.datneu)
from oes_purpos pp1
where pp1.av_part_no = pp.av_part_no
and pp1.c_status != 'D')) last_aw_po_qty,
(select nvl(sum(ps.requ_qty - nvl(ps.del_qty, 0)), 0)
from oes_purpos pp,
oes_purseg ps
where ps.headnr = pp.headnr
and ps.posnr = pp.posnr
and pp.av_part_no = pd.part_no
and ps.c_status not in ('9',
'D')
and ps.o_status not in ('D',
'9')) open_aw_po_qty,
(select nvl(sum(al.qty_onhand), 0)
from bds_location@sid_to_cdsuk l,
bds_article_location@sid_to_cdsuk al
where al.article_no = '9' || nrb.p_catnr
and al.qty_onhand <> 0
and l.location_no = al.location_no
and l.location_no like ' 1 DCMS%'
and l.location_class not in ('D',
'C')) onhand_CDS,
(select nvl(sum(st.qty_in_process), 0)
from BDS_ARTICLE_INVLEVEL_ORG@sid_to_cdsuk st
where st.article_no = '9' || nrb.p_catnr) allocated_CDS,
(select nvl(sum(st.qty_in_process), 0)
from BDS_ARTICLE_INVLEVEL_ORG@sid_to_cdsuk st
where st.article_no = '9' || nrb.p_catnr) allocated_CDS,
(select min(selection_relse_date)
from BDS_SELECTION_ORG@sid_to_cdsuk st
where substr(selection_no, 2, 99) = pd.catnr) NR_DATE,
(select sum(ds.planqty - nvl(ds.delqty, 0))
from oes_opos op,
oes_oposdelseg ds,
ncf_comppart nc,
oes_nrbom nb
where ds.ordnr = op.ordnr
and ds.posnr = op.posnr
and nc.item_part_no = pd.part_no
and nb.c_catnr = nc.catnr
and nb.c_prodtyp = nc.prodtyp
and nb.c_packtyp = nc.packtyp
and nb.c_vernr = nc.vernr
and op.catnr = nb.p_catnr
and op.prodtyp = nb.p_prodtyp
and op.packtyp = nb.p_packtyp
and op.vernr = nb.p_vernr
and ds.c_status not in ('9',
'D')
and op.ol_typ = 'XX') sum_open_xx_order,
(select nvl(sum(pl.qty_onhand), 0)
from ncf_comppart nc,
oes_nrbom nb,
part_description pd1,
part_loc pl
where nc.item_part_no = pd.part_no
and nb.c_catnr = nc.catnr
and nb.c_prodtyp = nc.prodtyp
and nb.c_packtyp = nc.packtyp
and nb.c_vernr = nc.vernr
and pd1.catnr = nb.p_catnr
and pd1.prodtyp = nb.p_prodtyp
and pd1.packtyp = nb.p_packtyp
and pd1.vernr = nb.p_vernr
and pl.part_no = pd1.part_no) onhand_avalon_fp,
(select unique sv.gps_planshpdate
from oes_delsegview sv,
oes_opos op,
oes_oposdelseg ds,
oes_nrbom nb,
ncf_comppart cp
where cp.item_part_no = pd.part_no
and nb.c_catnr = cp.catnr
and nb.c_prodtyp = cp.prodtyp
and nb.c_packtyp = cp.packtyp
and nb.c_vernr = cp.vernr
and sv.ordnr = ds.ordnr
and sv.posnr = ds.posnr
and sv.catnr = nb.p_catnr
and sv.prodtyp = nb.p_prodtyp
and sv.packtyp = nb.p_packtyp
and op.ordnr = ds.ordnr
and op.posnr = ds.posnr
and op.catnr = nb.p_catnr
and op.prodtyp = nb.p_prodtyp
and op.packtyp = nb.p_packtyp
and op.vernr = nb.p_vernr
and nb.active = 'Y'
and op.ol_typ in ('XX',
'CO')
and sv.gps_planshpdate = (select max(sv1.gps_planshpdate)
from oes_delsegview sv1,
oes_opos op1,
oes_oposdelseg ds1,
oes_nrbom nb1,
ncf_comppart cp1
where cp1.item_part_no = cp.item_part_no
and nb1.c_catnr = cp1.catnr
and nb1.c_prodtyp = cp1.prodtyp
and nb1.c_packtyp = cp1.packtyp
and nb1.c_vernr = cp1.vernr
and sv1.ordnr = ds1.ordnr
and sv1.posnr = ds1.posnr
and sv1.catnr = nb1.p_catnr
and sv1.prodtyp = nb1.p_prodtyp
and sv1.packtyp = nb1.p_packtyp
and op1.ordnr = ds1.ordnr
and op1.posnr = ds1.posnr
and op1.catnr = nb1.p_catnr
and op1.prodtyp = nb1.p_prodtyp
and op1.packtyp = nb1.p_packtyp
and op1.vernr = nb1.p_vernr
and nb1.active = 'Y'
and sv1.ord_o_status = '9'
and op1.ol_typ in ('XX',
'CO'))) last_ship_date_manufacturing,
(select unique nvl(max(sv.delqty), 0)
from oes_delsegview sv,
oes_opos op,
oes_oposdelseg ds,
oes_nrbom nb,
ncf_comppart cp
where cp.item_part_no = pd.part_no
and nb.c_catnr = cp.catnr
and nb.c_prodtyp = cp.prodtyp
and nb.c_packtyp = cp.packtyp
and nb.c_vernr = cp.vernr
and sv.ordnr = ds.ordnr
and sv.posnr = ds.posnr
and sv.catnr = nb.p_catnr
and sv.prodtyp = nb.p_prodtyp
and sv.packtyp = nb.p_packtyp
and op.ordnr = ds.ordnr
and op.posnr = ds.posnr
and op.catnr = nb.p_catnr
and op.prodtyp = nb.p_prodtyp
and op.packtyp = nb.p_packtyp
and op.vernr = nb.p_vernr
and nb.active = 'Y'
and op.ol_typ in ('XX',
'CO')
and sv.gps_planshpdate = (select max(sv1.gps_planshpdate)
from oes_delsegview sv1,
oes_opos op1,
oes_oposdelseg ds1,
oes_nrbom nb1,
ncf_comppart cp1
where cp1.item_part_no = cp.item_part_no
and nb1.c_catnr = cp1.catnr
and nb1.c_prodtyp = cp1.prodtyp
and nb1.c_packtyp = cp1.packtyp
and nb1.c_vernr = cp1.vernr
and sv1.ordnr = ds1.ordnr
and sv1.posnr = ds1.posnr
and sv1.catnr = nb1.p_catnr
and sv1.prodtyp = nb1.p_prodtyp
and sv1.packtyp = nb1.p_packtyp
and op1.ordnr = ds1.ordnr
and op1.posnr = ds1.posnr
and op1.catnr = nb1.p_catnr
and op1.prodtyp = nb1.p_prodtyp
and op1.packtyp = nb1.p_packtyp
and op1.vernr = nb1.p_vernr
and nb1.active = 'Y'
and sv1.ord_o_status = '9'
and op1.ol_typ in ('XX',
'CO'))) last_ship_qty_manufacturing,
case when case when (select max(psd.conf_date)
from oes_purseg psd,
OES_PURSEGVIEW psv
where psv.headnr = psd.headnr
and psv.posnr = psd.posnr
and psv.segnr = psd.segnr
and psv.av_part_no = pd.part_no
and psv.ps_o_status in ('1', '2', '7')
and psv.ps_c_status not in ('D', '9')) is null then (select max(psd.requ_date) from oes_purseg psd,
OES_PURSEGVIEW psv
where psv.headnr = psd.headnr
and psv.posnr = psd.posnr
and psv.segnr = psd.segnr
and psv.av_part_no = pd.part_no
and psv.ps_o_status in ('0', '1', '2', '7')
and psv.ps_c_status not in ('D', '9'))
else (select max(psd.conf_date)
from oes_purseg psd,
OES_PURSEGVIEW psv
where psv.headnr = psd.headnr
and psv.posnr = psd.posnr
and psv.segnr = psd.segnr
and psv.av_part_no = pd.part_no
and psv.ps_o_status in ('1', '2', '7')
and psv.ps_c_status not in ('D', '9')) end is null then (select max(ps1.del_date)
from oes_purseg ps1
where ps1.headnr || ps1.posnr || ps1.segnr = (select rq.pur_headnr || rq.pur_posnr || rq.pur_segnr
from oes_requisition rq
where rq.seqnr = (select fm.req_seqnr
from oes_fod_match fm
where fm.part_no = pd.part_no
and fm.qty_free_idc = 'Y'
and fm.cre_dat = (select max(fM1.cre_dat)
from oes_fod_match fm1
where fm.part_no = fm1.part_no
and fm1.qty_free_idc = 'Y'))))
else case when (select max(psd.conf_date)
from oes_purseg psd,
OES_PURSEGVIEW psv
where psv.headnr = psd.headnr
and psv.posnr = psd.posnr
and psv.segnr = psd.segnr
and psv.av_part_no = pd.part_no
and psv.ps_o_status in ('1', '2', '7')
and psv.ps_c_status not in ('D', '9')) is null then (select max(psd.requ_date)
from oes_purseg psd,
OES_PURSEGVIEW psv
where psv.headnr = psd.headnr
and psv.posnr = psd.posnr
and psv.segnr = psd.segnr
and psv.av_part_no = pd.part_no
and psv.ps_o_status in ('1', '2', '7')
and psv.ps_c_status not in ('D', '9'))
else (select /*+ ORDERED */ max(psd.conf_date)
from oes_purseg psd,
OES_PURSEGVIEW psv
where psv.headnr = psd.headnr
and psv.posnr = psd.posnr
and psv.segnr = psd.segnr
and psv.av_part_no = pd.part_no
and psv.ps_o_status in ('1', '2', '7')
and psv.ps_c_status not in ('D', '9')) end end ACP_start_date,
(select max(acp2.cre_dat)
from oes_fod_match acp2
where pd.part_no = acp2.part_no
and acp2.type = 'SUP'
and acp2.cre_usr = 'SOX9347') ACP_migration_date,
(select round(nvl(avg(dp.del_qty), 0))
from bds_dhead@sid_to_cdsuk dh,
bds_dline@sid_to_cdsuk dp
where dp.dhead_no = dh.dhead_no
and dh.d_status = '9'
and dp.article_no = '9' || nrb.p_catnr
and dh.actshpdate > sysdate - 365) * 90 avg_CDS_delqty_last_2_mths,
nrb.p_catnr used_in_catnr
FROM part_description pd
INNER JOIN inventory_purchase ip
ON ip.part_no = pd.part_no
INNER JOIN scm_prodtyp pt
ON pt.prodtyp = pd.prodtyp
INNER JOIN oes_fod_match acp
ON acp.part_no = pd.part_no
INNER JOIN part_description pd3
ON pd3.part_no = pd.part_no
INNER JOIN NCF_COMPPART ncf
ON ncf.item_part_no = pd3.part_no
INNER JOIN oes_nrbom nrb
ON ncf.catnr = nrb.c_catnr
AND ncf.prodtyp = nrb.c_prodtyp
AND ncf.packtyp = nrb.c_packtyp
AND ncf.vernr = nrb.c_vernr
WHERE pd.cunr in ('649830', 'W30000')
and pd.catnr = '2EDVD0017'
and pd.fod_idc = 'Y'
and pt.prodgrp = 'AW'
