with shippedqty as (
select sum(shipped_qty) as shpqty, source_salesitem_id
from FACT_SHIPMENT_LINE
where dim_org_id in (select dim_org_id from dim_org WHERE org_name like 'TSI%' AND org_name <> 'TSI FMS')
group by source_salesitem_id
)
select product.nsn_code,
(CASE
WHEN WHSE_CODE='EXE795' THEN 'TX'
WHEN WHSE_CODE='EXE796' THEN 'KUW'
WHEN WHSE_CODE<>'EXE795' AND WHSE_CODE<>'EXE796' THEN DLSF.whse_name
END) AS GAP,
ven.vendor_name as Source,
count(fsi.source_soline_id) as BO_num,
sum(fsi.quantity - coalesce(sq.shpqty, 0)) as tirews_onBO, --qtyordered - qtydelivered
min(od.THISDATE) as oldest_backorder
from fact_salesitem fsi
left join shippedqty sq on fsi.source_soline_id = sq.source_salesitem_id
inner join dim_product product on fsi.dim_product_id = product.dim_product_id
inner join fact_pa_item pai on fsi.dim_product_id = pai.dim_product_id and pai.pa_isactive_ind = 'Y'
inner join DIM_VENDOR ven on pai.DIM_VENDOR_ID = ven.DIM_VENDOR_ID
left JOIN DIM_WHSE_BIN DLSF on FSI.DIM_WHSE_BIN_ID = DLSF.DIM_WHSE_BIN_ID
INNER JOIN DIM_DATE od on FSI.CREATE_DATE_ID = od.DIM_DATE_ID
INNER JOIN DIM_NAME_VALUES SOHS on FSI.DIM_NV_HDR_STATUS_ID = SOHS.DIM_NAME_VALUES_ID
INNER JOIN DIM_NAME_VALUES SOLS on FSI.DIM_NV_LINE_STATUS_ID = SOLS.DIM_NAME_VALUES_ID
WHERE FSI.DIM_ORG_ID IN (SELECT DIM_ORG_ID FROM DIM_ORG WHERE org_name like 'TSI%' AND org_name <> 'TSI FMS')
and SOLS.value_code in ('OH', 'BO') and SOHS.value_code not in ('SC_AF')
and product.nsn_code='2610-00-051-1270'
group by product.nsn_code, DLSF.WHSE_CODE, DLSF.whse_name,ven.vendor_name;
When I run this Iam getting an error Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.