You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
70 lines
2.4 KiB
Plaintext
70 lines
2.4 KiB
Plaintext
with my_cte as (
|
|
Select
|
|
PP.cf$_purchase_cat1_desc Purch_Category1_Desc
|
|
,IP.Part_NO
|
|
,IP.Note_Text Legacy
|
|
,IP.Contract Site
|
|
,IP.DESCRIPTION
|
|
,sum(SMA.QTY_REQUIRED) Qty_required
|
|
,sum(SMA.QTY_ISSUED) Qty_Issued
|
|
,sum(SMA.QTY_REMAIN_TO_ISSUE) Qty_Remaining_to_Issue
|
|
,nvl(TFS.QtyOnHand,0) Floor_Stock
|
|
,nvl(TP.QtyOnHand,0) Picking
|
|
,sum(SMA.QTY_REMAIN_TO_ISSUE)- nvl(TFS.QtyOnHand,0) Qty_To_Move
|
|
,TL.Warehouse
|
|
,TL.Location_No
|
|
,TL.HANDLING_UNIT_ID HU
|
|
,TL.Receipt_Date
|
|
,TL.Location_Qty
|
|
,sum(TL.Location_Qty) over (PARTITION BY IP.Part_No ORDER BY IP.Part_NO,TL.HANDLING_UNIT_ID rows UNBOUNDED PRECEDING) TTL
|
|
|
|
|
|
|
|
From
|
|
SHOP_MATERIAL_ALLOC_uiv SMA inner join INVENTORY_PART IP on SMA.PART_NO=IP.PART_NO and SMA.CONTRACT=IP.CONTRACT left join PURCHASE_PART_cfv PP on IP.Part_No=PP.Part_No and IP.Contract=PP.Contract
|
|
|
|
|
|
left join (select PART_NO, CONTRACT, sum(QTY_ONHAND) QtyOnHand from Inventory_Part_in_Stock_uiv where AVAILABILITY_CONTROL_ID is null and LOCATION_TYPE_db='F' Group By Contract, PART_NO) TFS
|
|
on SMA.Part_No=TFS.PART_NO and SMA.Contract=TFS.Contract
|
|
|
|
left join (select PART_NO, CONTRACT, sum(QTY_ONHAND) QtyOnHand from Inventory_Part_in_Stock_uiv where AVAILABILITY_CONTROL_ID is null and LOCATION_TYPE_db='PICKING' Group By Contract, PART_NO) TP
|
|
on SMA.Part_No=TP.PART_NO and SMA.Contract=TP.Contract
|
|
|
|
left join (select PART_NO, CONTRACT, Location_No, Warehouse, RECEIPT_DATE, HANDLING_UNIT_ID, sum(QTY_ONHAND) Location_Qty from Inventory_Part_in_Stock_uiv where AVAILABILITY_CONTROL_ID is null and LOCATION_TYPE_db='PICKING' and WAREHOUSE like nvl ('&FromWarehouse','%') Group By Contract, PART_NO,Location_No, Warehouse, HANDLING_UNIT_ID, RECEIPT_DATE) TL
|
|
on TL.Part_No=SMA.PART_NO and TL.Contract=SMA.Contract
|
|
|
|
Where
|
|
IP.CONTRACT LIKE '%&Contract%'
|
|
and report_sys.parse_parameter(PP.cf$_purchase_part_cat_1, '&[-C-BL]PurchasedCategory') = 'TRUE'
|
|
and SMA.State not in ('Cancelled','Closed','Planned')
|
|
and trunc(SMA.DATE_REQUIRED) BETWEEN to_date( '&DATE_FROM','YYYY-MM-DD') AND to_date('&DATE_TO','YYYY-MM-DD')
|
|
|
|
|
|
Group by
|
|
IP.Part_NO
|
|
,PP.cf$_purchase_cat1_desc
|
|
,IP.Note_Text
|
|
,IP.Contract
|
|
,IP.DESCRIPTION
|
|
,TFS.QtyOnHand
|
|
,TP.QtyOnHand
|
|
,TL.Location_No
|
|
,TL.HANDLING_UNIT_ID
|
|
,TL.Warehouse
|
|
,TL.Receipt_Date
|
|
,TL.Location_Qty
|
|
|
|
|
|
Having
|
|
(sum(SMA.QTY_REMAIN_TO_ISSUE)- nvl(TFS.QtyOnHand,0)) >0 and TL.Location_Qty>0
|
|
|
|
) -- end my_cte
|
|
|
|
select * from my_cte d
|
|
-- where (d.TTL - d.Location_Qty) < d.Qty_To_Move
|
|
|
|
Order by
|
|
d.Part_NO
|
|
,d.HU
|
|
,d.Receipt_Date
|
|
,d.Location_No |