I have created the following quick report, but get this error when I try to run it.
select part_no, ifsapp.Inventory_Part_API.Get_Description(CONTRACT,PART_NO) part_description, Contract
, Case When Contract like Nvl(Upper('&Site'),'%') Then 'Y' Else 'N'End Rqd
from ifsapp.Inventory_Part
where contract Not In ('CPH','CTM','VMS','CGP')
--and part_no='WMPI10067'
and Nvl(Substr(ifsapp.Inventory_Part_API.Get_Accounting_Group(contract,part_no),1,2),'Y') Not In ('IS','PH')
),PartsInStock As (
Select part_no, Sum( Case When contract like Nvl(Upper('&Site'),'%') and Substr(warehouse,1,4) like Nvl(Upper('&Warehouse'),'%') Then ipis.qty_onhand Else 0 End ) End_Qty
, Sum( Case When contract like Nvl(Upper('&Site'),'%') and Substr(warehouse,1,4) like Nvl(Upper('&Warehouse'),'%') Then ipis.UNIT_COST * Nvl(ipis.QTY_ONHAND,0) Else 0 End ) End_Value
From ifsapp.Inventory_part_in_stock_uiv ipis
where contract Not In ('CPH','CTM','VMS','CGP') and Nvl(Substr(ifsapp.Inventory_Part_API.Get_Accounting_Group(contract,part_no),1,2),'Y') Not In ('IS','PH')
--and part_no In ('WMPI10067')
--and Substr(warehouse,1,4) In ('M100','M200','M300','M400','M500')
Group by part_no
),Usages As (
select part_no
, Sum( Case When contract like Nvl(Upper('&Site'),'%') and Substr(location_no,1,4) like Nvl(Upper('&Warehouse'),'%') Then quantity Else 0 End ) Total_Qty
from ifsapp.INVENTORY_TRANSACTION_HIST
where direction='-'
--and Substr(location_no,1,4) In ('M100','M200','M300','M400','M500')
and date_applied between trunc(add_months(sysdate, -12),'mm') and trunc(sysdate)
/* Case When '&No_Of_Weeks' Is Null Then trunc(add_months(sysdate, -12),'mm')
When '&No_Of_Weeks' Is not Null Then To_Date('&Period_ending_date','DD/MM/YYYY') - ('&No_Of_Weeks'*7) End
and
Nvl(To_Date('&Period_ending_date','DD/MM/YYYY'), trunc(sysdate) ) */
and contract Not In ('CPH','CTM','VMS','CGP')
and Nvl(Substr(ifsapp.Inventory_Part_API.Get_Accounting_Group(contract,part_no),1,2),'Y') Not In ('IS','PH')
Group by part_no)
select null Total_Sum_Of_Value, null Total_Sum_Of_Quantity, NULL Total_Sum_of_Quantity_Used_52_Weeks
,NULL used_last_52_weeks, NULL Average_per_week, NULL Suggested_Quantity, NULL as Suggested_Value, NULL Variance
, 'PART_NO', 'PART_DESCRIPTION','PARAMETER_USED', "52","51","50","49","48","47","46","45","44","43","42","41","40","39","38","37","36","35","34","33","32","31","30","29","28","27",
"26","25","24","23","22","21","20","19","18","17","16","15","14","13","12","11","10","9","8","7","6","5","4","3","2","1"
from rowsnum
pivot (max(dt) for rn in ( 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52 ) )Union All
Select Round(Nvl(pis.End_Value,0),2) , Nvl(pis.End_qty,0) , Nvl(usg.Total_Qty,0)
, Case When Nvl(usg.Total_Qty,0)=0 Then 'Not Used' Else NULL End XX
, Case When Nvl(usg.Total_Qty,0)=0 Then 0 Else Round(Nvl(usg.Total_Qty,0) / 52,1) End
, Round(Case When Nvl(pis.End_qty,0)=0 Then 0 Else Nvl('&No_Of_Weeks',7) * Case When Nvl(usg.Total_Qty,0)=0 Then 0 Else Round(Nvl(usg.Total_Qty,0) / 52,1) End End,0)
, Case When Case When Nvl(pis.End_qty,0)=0 Then 0 Else Nvl('&No_Of_Weeks',7) * Case When Nvl(usg.Total_Qty,0)=0 Then 0 Else Round(Nvl(usg.Total_Qty,0) / 52,0) End End = 0
Then 0 Else Round((Nvl(pis.End_Value,0) / Nvl(pis.End_qty,0)) *
Case When Nvl(pis.End_qty,0)=0 Then 0 Else Nvl('&No_Of_Weeks',7) * Case When Nvl(usg.Total_Qty,0)=0 Then 0 Else Round(Nvl(usg.Total_Qty,0) / 52,0) End End,2) End, Case When Case When Nvl(pis.End_qty,0)=0 Then 0 Else Nvl('&No_Of_Weeks',7) * Case When Nvl(usg.Total_Qty,0)=0 Then 0 Else Round(Nvl(usg.Total_Qty,0) / 52,0) End End = 0
Then 0 Else Round((Nvl(pis.End_Value,0) / Nvl(pis.End_qty,0)) *
Case When Nvl(pis.End_qty,0)=0 Then 0 Else Nvl('&No_Of_Weeks',7) * Case When Nvl(usg.Total_Qty,0)=0 Then 0 Else Round(Nvl(usg.Total_Qty,0) / 52,0) End End,2) End
- Round(Nvl(pis.End_Value,0),2)
,p.part_no, part_description, 'Site: '||Nvl(Upper('&Site'),'All')||' Warehouse: '||Nvl(Upper('&Warehouse'),'All')
,"52","51","50","49","48","47","46","45","44","43","42","41","40","39","38","37","36","35","34","33","32","31","30","29","28","27",
"26","25","24","23","22","21","20","19","18","17","16","15","14","13","12","11","10","9","8","7","6","5","4","3","2","1"
From (
Select part_no, part_description, to_char(nvl(quantity,0),'99,999.99') quantity, rnFrom (
select ip.part_no, ip.Part_description
, TRUNC(date_applied, 'DAY')+1 start_of_week
, Sum( Case When ip.contract like Nvl(Upper('&Site'),'%') and Substr(location_no,1,4) like Nvl(Upper('&Warehouse'),'%') and direction='-' Then quantity Else 0 End ) quantity
, rn, ROW_NUMBER() Over (Partition by ip.part_no order by ip.part_no, rn) xxfrom RqdParts ip Left Outer Join ifsapp.INVENTORY_TRANSACTION_HIST iph On ip.part_no=iph.part_no and ip.contract = iph.contract
Left Outer Join Rowsnum On to_char(TRUNC(date_applied, 'DAY')+1,'dd/mm/yyyy') = dtwhere --ip.part_no In ('WMPI10067') and
--Substr(location_no,1,4) In ('M100','M200','M300','M400','M500') and
(date_applied between TRUNC(ADD_MONTHS(Nvl(To_Date('&Period_ending_date','DD/MM/YYYY'), trunc(sysdate)), -11),'mm') and Nvl(To_Date('&Period_ending_date','DD/MM/YYYY'), trunc(sysdate))
OR TRUNC(date_applied, 'DAY')+1 IS NOT NULL )
and ip.Rqd = 'Y'
Group by ip.part_no, ip.part_description, TRUNC(date_applied, 'DAY')+1, rn) data Where ( data.rn is not null or ( data.rn is null and data.xx=1))
) src
Pivot
( Max( Quantity ) for rn In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52 )
) p
Left Outer Join PartsInStock pis On pis.part_no = p.part_no
Left Outer Join Usages usg On usg.part_no = p.part_no
Order by 1 desc
Any ideas please ?
Thanks
