Available To Promise - Monthly View

[Former Member]

Hello Experts,

Have anyone tried to generate a query that would display Availability of all items in month view:

Ex: It would be in this format. I created 2 views to accomodate this but still thinking to see if i get this data with a single query or is there any table that i look into and group by months?

*Year ItemCode ItemDesc Instock Jan Committed Jan OnOrder Jan ATP...........................Dec Commited Dec OnOrder Dec ATP*

[former_member218051]

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

[Former Member]

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

[Former Member]

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.

[Former Member]

ok, i understood the requirement now. You mean that, you need a Breakup - Month wise for the Current Order,Commited Stock.

[Former Member]

Hi!

Try this report with Crystal Reporter - Pivot Table, that will be easy in coding aswell give good performance

[Former Member]

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.

[Former Member]

Can you please post your query used in CR. I belive, on fine tunning the query that should work on CR.

[Former Member]

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

)

[Former Member]

Hi,

Have you tried a query to get one month show correctly? If yes, post it and monthly would be easy.

Thanks,

Gordon