To find the Closing_Stock @ month end

[harleen_singh]

Hi,

I am working on inventory module, inwhich three table are there

1. Inventory

2. TRN_Receipt

3. TRN_Issue

In Inventory table we have Item wise Opening_Amt, Opening_Qty and only one date as '01-April-11' ( Basically its 1stApril Opening )

In TRN_Receipt we have Item wise Receive_Qty, Receive_Amt  and Doc_Date(Receive Date)

In TRN_Issue we have Item wise Issue_Qty , Issue_Amt and Doc_Date(Issue Date)

and make  Doc_Date asCommon_date  (picking date from Inventory,TRN_Receipt, TRN_Issue)

and Item from Invetory, TRN_Receipt,TRN_Issue as Common_Item

i want to calculate inventory @month_end

Formule for Closing_Qty =Opening_Qty + Receive_Qty - Issue_Qty

For month = 'April'

ClosingQty =  Opening_Qty of April +  Receive_Qty of April - Issue_Qty ofApril

For month ='May'         Opening Qty = 'Closing Qty of April' 

So For Closing_Qty for May = 'Closing Qty of April ' + Receive_Qty of May - Issue_Qty of May

and so on

Exp

Inventory Table :-     Date                      Item      Opening_Qty

                           1/Apr/2011                A           100

                           1/Apr/2011                B            20

                           1/Apr/2011                C            10

Receive Table

                            Doc_Date             Item          Receive_Qty

                           2/Apr/2011             A             10

                           3/Apr/2011             A             25

                          21/May/2011           A             100

IssueTable            Doc_Date             Item        Issue_Qty

                            1/Apr/2011            A             5

                           21/Apr/2011           A             8

                           28/May/2011          A             80

Output will be

                             Month      Item    Opening_Qty    Closing_Qty

                              Apr11          A          100                     122

                              Apr11          B            20                      20

                              Apr11          C            10                      10

                              May11         A          122                     142

                              May11         B            20                      20

                              May11         C           10                       10                                  

Please help me to find the correctoutput

Regards

[tanelry]

In my opinion the best way is to build the data model where opening inventory and all transactions (receipts and issuing) form the fact table (concatenated),

then connect calendar table to transaction dates indirectly, through a link table so that every single calendar date corresponds to all preceding transaction dates.

As a result you can build charts to calculate closing Qty at any calendar date/week/month simply with sum(Qty).

This is how the tables could look like:

Transactions:
StockEvent TransDateID Item  Qty
Start      20110401     A    100
Receive    20110402     A     10
Issue      20110401     A     -5 (note: negative qty for issue)
DateLink:
TransDateID       DateID 
20110401          20110401 
20110401          20110402 
20110402          20110402 
20110401          20110403 
20110402          20110403 
20110403          20110403 
Calendar:
DateID, Date, Year, Month, Week ...

[SunilChauhan]

see the attached file

please apply on your actual data

hope this helps