Stock status report 1

[PTina]

Is there a way to change the document date that an inventory transaction got posted with so that the stock status report shows the correct value?

I posted opening quantities with the wrong document date. I did not post to GL since it was beginning quantities so even though I had the correct posting date on the batch, in smartlist GL Posting date comes up blank.

I now need to get the historical stock status report as of the date the beginning quantities were posted but it only uses the document date and not the GL posting date.

I can identify the transactions that were posted as opening quantities but other inventory transactions have been posted since the opening quantities.

Has anyone ever had this problem before? Help...

 

[Luvsql]

You can update the document date with a sql script. I would run a select statement first, to make sure the results are the ones you want to change:

SELECT * FROM IV30200 SET DOCDATE='YYYY-MM-DD'
WHERE DOCNUMBR='ENTERDOC#' AND IVDOCTYP=1
SELECT * FROM IV30300 SET DOCDATE='YYYY-MM-DD'
WHERE DOCNUMBR='ENTERDOC#' AND IVDOCTYP=1

If these are the results, change to an update:

UPDATE IV30200 SET DOCDATE='YYYY-MM-DD'
WHERE DOCNUMBR='ENTERDOC#' AND IVDOCTYP=1
go
UPDATE IV30300 SET DOCDATE='YYYY-MM-DD'
WHERE DOCNUMBR='ENTERDOC#' AND IVDOCTYP=1

 

[PTina]

Thanks Luvsql,

I have one other question though. What about the other tables? Wouldn't I need to update those as well?

The tables that currently are updated with date are:
Table Fields
IV10200 daterecd,flrplndt
IV30102 year1
IV30200 docdate, bachnumb
IV30300 docdate
IV30500 posteddt

 

[Luvsql]

I assume you are using the historical stock status, since it's the only one that is concerned with dates? If so, the purchase receipts table isn't used to determine when to backout the qty, the IV30102 isn't used either, the IV30200 and IV30300 are in the script above adn the posteddt field from the IV30500 is the system date that something was potsed. The Historical Stock status uses the document date of the transaction ie if I physically posted something today, but my document date said March 15th, it would be refected on the Historical Stock Status as March 15th, not today.

 

[PTina]

One more question.

If I only change the dates in the IV30200 and IV30300 for the stock status report. Then when I run check links will this remove 'abandoned records' since the dates would not match in all the tables?

 

[Luvsql]

No, check links will not remove the records. Check links will only remove records if a key field exists in one table, but not the others in their group(ie if a transaction record exists in the IV30301 table, but not the IV30200 or IV30300 table, it will remove the entire record from all 3 tables).

 

[Luvsql]

If you are worried about the outcome, create a test company with the current data. Print the report. Update the dates. Print the report and make sure that's what you wanted it to do. Run check links. Reprint the report. If all tests look good, redo in live company.

 

[PTina]

Thanks Luvsql,

It worked like a charm;

 

[PTina]

One final question Luvsql: If the period that I change the date to is for last year (still open) will this cause issues when closing the year?

Thanks again for your help.

 

[Luvsql]

The GL Year-end process only moves GL data from the open year table, into the history table (GL30000), while updating the retained earnings account with the P&L balances, as well as bringing forward an opening balance for the balance sheet accounts. It does not go into the subledger at all. The inventory year-end subledger routine only adjusts the qty beginning balance plus other options that you select. Changing the document date on a transaction has no affect. As well, you mentioned that these entries were never posted to the GL, so there's nothing to worry about.