Historical stock status detail report by site ( Invenotry)

[BHAT100]

I am trying to make historical stock status report in crystal.
I am using following tables
-IV00101
-iv30200
-iv30300

I want to pull records bY GL POSTING DATE, what other table I need to use to pull transactions relating other than inventory.

any idea on this would be great help to me

Thanks

-bhat

 

[bradth]

If your looking for historical data on sales orders by GL Posting date, SOP30200 and SOP30300 are the sales orders history tables.

Brad

 

[BHAT100]

Thanks brad, how about other transactions like BOM and purchase reciepts. and are all these tables to be linked to iv30200
(header inventory transactions)


please reply

Thanks

-bhat

 

[bradth]

The purchasing history tables are PM30200 and PM30300 and these could possibly be linked to IV30200 by the Batch Source and Batch Number. The BOM table is table BM010415, and that has to be linked by item number. In GP if you go to the toolbar and go "Tools > Resource Descriptions >Tables" and then click on the button with the "...", you can select your product and series, and it will give you an explanation of the names of all of the tables and what information they hold, whether its live or history, and so on. Hope this helps.

Brad

 

[BHAT100]

when I am linking all these tables.GLPOST DATE is in all four table. how can restrict with formula upto may or some other period ending dates.

I am following formula to pull date:but it does not work

if {BM30200.PSTGDATE} <= cDateTime(2005,05,31,00,00,00)
then {IV30300.TRXQTY}
else if {IV30200.GLPOSTDT} <= cDateTime(2005,05,31,00,00,00)
then {IV30300.TRXQTY}

and so on sop30200 and pm30200 tables

-Bhat

 

[bradth]

What you may want to do is write a query in SQL, that way you can create your formula and view what records are being returned. Then just link the report to the database and place the query in a stored procedure.

Brad

 

[Dukester0122]

I suggest use a Parameter for Ending date then go to Select Expert and create formula:
{IV30200.GLPOSTDT}<= {?End Date}

This is simpler which I always try to do. You can add a Start Date if you want. Also try to see if IV40700- Site Id would help in your report, I had this table on mine.

 

[BHAT100]

I am using following table and all these tables are linked = inner joins to each other

-SOP30200
-PM30200
-BM30200
-IV00101
-IV30200
-IV30300
-IV40700

If I am selecting record using above in seelct expert {IV30200.GLPOSTDT}<= {?End Date}. it just give record orignated in inventory and does not pull any record orignating from say BOM,Sales order and Purchase order.

Thanks

-bhat

 

[Dukester0122]

I don't have access to CR right now to test but I didn't use the GL Posting Date, I used IV30300.docdate which I think is what the Historical Stock Status report is also using, correct me if I'm wrong.

 

[BHAT100]

This report have option to generate report in either by Doc Date or posting date. I am trying to make to pull data by GL posting date to reconcile General Ledger.

if we use doc date then reports runs fine with iv30300 table because all sale, purchase and inventory records are there for docdate but GL posting date is in respective modules tables

could you please let me know how you are building your report on GL posting date.

Thanks

-bhat

 

[Dukester0122]

Like I said, I didn't use GL Posting date because if you do this then you need more tables like SOP30200 for sales transaction, etc.

 

[BHAT100]

how did you handled inter site transfer records in each location

Thanks
-bhat

 

[Dukester0122]

I created a parameter called Location and used IV00102.LOCNCODE then create a formula IV00102.LOCNCODE={?Location}.

Create another formula {?Location} in [{IV30300.TRXLOCTN},{IV30300.TRNSTLOC}]

 

[BHAT100]

Thanks for your reply

where are you placing {?Location} in [{IV30300.TRXLOCTN},{IV30300.TRNSTLOC}] formula. is it in record selection or group selection?

Sorry, I am still not clear how it would pick up data for transfer to location and transfer from location i.e. QTY and Amount from iv30300 table

-bhat

 

[BHAT100]

Please reply,I would be very grateful of you

when I am placing formula {?Location} in [{IV30300.TRXLOCTN},{IV30300.TRNSTLOC}] in record selection or group. it gives error

Thanks for your help

-bhat

 

[Dukester0122]

i don't have access to CR but it should be a formula in Select expert.

 

[BHAT100]

Thanks for your help.

-b

 

[BHAT100]

do you have idea how damaged inventory was excluded.
either using iv30301 table ( qtytype =5)
or some other table if how it is to be linked to iv30300.

Any idea on this would be really great help to me

Thanks again for your help

-b

 

[Dukester0122]

We include damaged goods to inventory as we try to fix them then resell to at discounts.

yes that's your best bet, link iv30301 to iv30300 then exclude qtytype=5

 

[BHAT100]

when I am linking iv30301 to iv30300 by doctype and dconumbr and LNSEQNMBR as joinleftout. it is giving multiple records fro single transaction. I tried other linking option also but it does not work. Any clue to fix this

Thanks again

-b