Hi,
Have you tried a query to get one month show correctly? If yes, post it and monthly would be easy.
Thanks,
Gordon
Hi!
Try this report with Crystal Reporter - Pivot Table, that will be easy in coding aswell give good performance
Hello Thanga Raj,
I have the report working in Crystal , how ever the customer has 21000 items and 20 warehouses. Crystal crashes quite often and they need a query.
Can you please post your query used in CR. I belive, on fine tunning the query that should work on CR.
Hello Gordon, Thanga Raj
Here is what i am using in the crsytal source, i used cross-tab and summarized totals at the end of the month to make this work in crystal. Can we get the same in query?
-
Get All Items from Open Sales Orders-----
select T1.ItemCode, T1.ItemName, T3.WhsCode ,T4.OnHand ,
SUM(T3.Quantity) as 'Committed', 0 as 'Ordered', T2.DocDueDate
from ORDR T2
inner join RDR1 T3 on T2.DocEntry = T3.DocEntry
Left join OITM T1 on T3.ItemCode = T1.ItemCode
inner join OITW T4 on T3.WhsCode = T4.WhsCode and T1.ItemCode = T4.ItemCode
where T3.LineStatus = 'O'
and T4.WhsCode='01'
Group By T1.ItemCode,T1.ItemName,T3.WhsCode,T4.OnHand,T3.Quantity,T2.DocDueDate
union all
-
Get All Items from Open Purchase Orders----
select T1.ItemCode, T1.ItemName, T3.WhsCode ,T4.OnHand, 0,
SUM(T3.Quantity), T2.DocDueDate
from OPOR T2
inner join POR1 T3 on T2.DocEntry = T3.DocEntry
Left join OITM T1 on T3.ItemCode = T1.ItemCode
inner join OITW T4 on T3.WhsCode = T4.WhsCode and T1.ItemCode = T4.ItemCode
where T3.LineStatus = 'O'
and T4.WhsCode='01'
Group By T1.ItemCode,T1.ItemName,T3.WhsCode,T4.OnHand,T3.Quantity,T2.DocDueDate
Union All
-
Get All Items not in Sales & Purchase Orders
Select t0.ItemCode,t0.ItemName,t1.WhsCode,t1.OnHand,t1.IsCommited as Committed,t1.OnOrder as 'Ordered',GETDATE() as DocDueDate
from OITM t0 inner join OITW t1 on t0.ItemCode=t1.ItemCode
where t1.WhsCode in('SW','CHINA','CHINA2','CN-TR','CN-VC','UK')
and t0.ItemCode not in
(Select Distinct t.ItemCode
from
(Select T1.ItemCode, T1.ItemName, T3.WhsCode ,T4.OnHand ,
SUM(T3.Quantity) as 'Committed', 0 as 'Ordered', T2.DocDueDate
from ORDR T2
inner join RDR1 T3 on T2.DocEntry = T3.DocEntry
Left join OITM T1 on T3.ItemCode = T1.ItemCode
inner join OITW T4 on T3.WhsCode = T4.WhsCode and T1.ItemCode = T4.ItemCode
where T3.LineStatus = 'O'
and T4.WhsCode='01'
Group By T1.ItemCode,T1.ItemName,T3.WhsCode,T4.OnHand,T3.Quantity,T2.DocDueDate
union all
select T1.ItemCode, T1.ItemName, T3.WhsCode ,T4.OnHand, 0,
SUM(T3.Quantity), T2.DocDueDate
from OPOR T2
inner join POR1 T3 on T2.DocEntry = T3.DocEntry
Left join OITM T1 on T3.ItemCode = T1.ItemCode
inner join OITW T4 on T3.WhsCode = T4.WhsCode and T1.ItemCode = T4.ItemCode
where T3.LineStatus = 'O'
and T4.WhsCode='01'
Group By T1.ItemCode,T1.ItemName,T3.WhsCode,T4.OnHand,T3.Quantity,T2.DocDueDate)t
)
Hi!
Assume we are n month Feb now, what you meant to say Jan Commit, Jan Order. Is that last month data ?
ATP report is something which will show the Current and future Stock detail.
Have you seen Invetory Status Report - Available to Promise
Hello Thang Raj,
Basically we need to ATP report by months. The system report displays detail when a user double clicks on a row and also it is not displayed in months, this will not suffice.
ok, i understood the requirement now. You mean that, you need a Breakup - Month wise for the Current Order,Commited Stock.
Hi Praneeth ,
We have set up SP_TN to check current month's availability of a product and if the booking is more than availibility then the SO is rolled back
You will have an idea from this
following is the logic
extract itemcode
find stock on hand
find pending sales orders for the current month
find pending purchase orders for current month
then( stock + pending purchase orders) - (pending sales orders) = availability
if the availability < booking then roll back
Hope this will help you
Thanks
Malhaar
