Tracking between Posted Shipments & Invoices

idiot

I need some help here regarding this scenario:
1 Sales Order with Qty = 2 of the same Item.
For reference sake let's call them Item A & Item B.
This Sales Order was posted Ship & Invoiced twice with Qty = 1 each time.

Item A Posted Shipment No.: PS0001, Posted Invoice No.: PI2002
Item B Posted Shipment No.: PS0005, Posted Invoice No.: PI2006

If the user is looking at PS0005, how can he tell that the related posted invoice is PI2006?

Thanks for helping

ara3n

find the related item ledger entry. (for none serialized items, "Item ship entry No." on sales shipment line)

Once the item ledger is found, find the value entry line, The value entry will have the invoice No. as "document no.", then get the sales invoice No.

idiot

Thanks.
I guess there is no direct link between Posted Shipments & Invoices.
Looks like the Roles & Permissions have to be adjusted to allow read for Item Ledger & Value Entries... ](*,)

kriki

idiot wrote:

Thanks.
I guess there is no direct link between Posted Shipments & Invoices.
Looks like the Roles & Permissions have to be adjusted to allow read for Item Ledger & Value Entries... ](*,)

If you already assigned indirect permissions to those tables, the only thing you need to do is to give the object that reads those tables read-permissions (through its property Permissions) and you don't need to change anything for the users.

idiot

From where I see it there's no simple way out...
A new form or exportable to Excel report has to be created...which might mean 2 new Roles to be created & the old Role adjusted... :evil:
Why is there no direct navigate function :evil: ](*,)

idiot

Part 2 of problem.
If there are G/L Accounts involved is there anyway to link the entries?

Luc_VanDyck

idiot wrote:

If the user is looking at PS0005, how can he tell that the related posted invoice is PI2006?

If the invoice is created using the function "Get Shipment Lines" you find the number of the posted shipment as a line of type "blank" in your invoice. If you create a new key on the Sales Invoice Line table, field Description, you can filter on "Shipment No. " + your posted shipment number. Note: you can have multiple invoices for the same posted shipment.

Albertvh

Hi

You can also look at the Order No. field to find the invoice numbers


Albert

themave

If you are talking about looking at the posted shippment using form 130, then go to the bottom button labled "shipment" then select invoices is while show you all invoiced associated with the shipment.

if you are just finding the shippment data looking up an item ledger entry on form 38 - item ledger entries then use the button on the bottom labeled "Entries" and select "value Entries" that will show you the invoice also.

David_Singleton

idiot wrote:

I need some help here regarding this scenario:
1 Sales Order with Qty = 2 of the same Item.
For reference sake let's call them Item A & Item B.
This Sales Order was posted Ship & Invoiced twice with Qty = 1 each time.

Item A Posted Shipment No.: PS0001, Posted Invoice No.: PI2002
Item B Posted Shipment No.: PS0005, Posted Invoice No.: PI2006

If the user is looking at PS0005, how can he tell that the related posted invoice is PI2006?

Thanks for helping

You need to understand how Navision creates shipments and invoices before you will realize why this question makes no sense.

themave

David Singleton wrote:

idiot wrote:

I need some help here regarding this scenario:
1 Sales Order with Qty = 2 of the same Item.
For reference sake let's call them Item A & Item B.
This Sales Order was posted Ship & Invoiced twice with Qty = 1 each time.

Item A Posted Shipment No.: PS0001, Posted Invoice No.: PI2002
Item B Posted Shipment No.: PS0005, Posted Invoice No.: PI2006

If the user is looking at PS0005, how can he tell that the related posted invoice is PI2006?

Thanks for helping

You need to understand how Navision creates shipments and invoices before you will realize why this question makes no sense.

you need to work at an end user company to understand why most likely every user of Navision has asked this same question, it is a common need to see that things that where shipped where also invoiced.

idiot

Thanks for all your input.
I think I need to give more details here from what I've gathered.

1. Sales Line Type of Item
Shipment & Invoice can be linked via Item Shpt. Entry No. in Item Ledger Entry & Value Entry...

2. Sales Line Type of G/L Account
This is where the problem comes in. Sometimes customers of my client may ask why they are billed certain services or what Shipment is it related to...all sorts of funny questions, so if the link between Shipment & Invoice can be established it makes things a while lot easier.


To makes things more complicated, there are 2 kinds of Posting of Invoices, namely direct posting from the Sales Order & using the Get Shipment Method.
While with Shipments & Invoices posted from Sales Orders, the Order No. can be obtained from the Posted Invoice Header Order No., it's not possible to identify those posted with the latter method as the Order No. field is blank. The Shipment No. can be obtained from the Posted Sales Invoice Line Description field in this case but is it practical to search this field? Adding Key?

There are so many scenarios to check... ](*,) ](*,) ](*,) ](*,) ](*,) ](*,)

idiot

David Singleton wrote:

You need to understand how Navision creates shipments and invoices before you will realize why this question makes no sense.

To auditors they make every sense. Otherwise Navision makes no sense.
This is part & parcel of an audit trace :!:

David_Singleton

themave wrote:

you need to work at an end user company to understand why most likely every user of Navision has asked this same question, it is a common need to see that things that where shipped where also invoiced.

Been there done that,

Sorry, I was in a hurry, and really my answer is a bit cryptic. Yes this is a common customer request. (not EVERY customer, maybe 0ne in ten, but if you worked at that ONE, then it may for sure look like every).

What I was trying to point out, is that an NSC (partner what ever) should not be asking the question in these terms.YES I agree, an auditor, or new Navision client might, but not a partner.

As you correctly pointed out, Navision generates a Many to one relation ship from Order to Invoice, and Order to Shipment. (and of course we need to understand combine shipments). So the role of a partner is to sit with the auditor, and discuss what they actually want,then explain how Navision works, and come up with a solution.

But none of that can happen without a clear comprehension of how Orders link to Invoices and Shipments.

BUT sorry for the quick reply, re-reading it, I can see how it could cause confusion.

idiot

Any experienced partner would have expected this coming...& perhaps already have some customizations up their sleeves to give them an advantage over other competitors...

The most common reason for such tracking is for analysis of debtors turnover & cashflow analysis, there are also plenty of other reasons....
What goes through is this:
1. Goods are sent to Cust.

2. Ideal scenario = Invoice sent at the same time.
However ideal scenario doesn't exist, so it could be weeks later the Invoice is sent.

3. Payment terms granted is normally from the date of Invoice. If Invoice is sent 14 days later & terms on Invoice is 30 days, then the term granted to Cust is effectively 14+30 days which results in longer Cust payments & cashflow is affected.

4. Is the poor cashflow caused by late debtors payments? Why are debtors taking so long to pay? Is it because they receive Invoice late? These are questions that can answered with the help of Shipment & Invoice tracking...

idiot

Anyone can help?
Or is this kind of analysis not possible from Navision?

ara3n

i believe i mentioned the link, all you need is a report to display the information.

What you can do is on shipment line create new fields called, invoice no, and invoice line no..

During invoicing, find any sales shipment line that related to that sales line and have blank Invoice No. and populate them with invoice No.

idiot

Thank ara3n for your help.
You have helped with Items.
However some of the Sales are related to G/L Accounts & hence they won't appear in the Item Ledger or Value Entry, so is there any links for these kind of transactions?

ara3n

I've edited my post above you can see a solution for gl as well as items.

idiot

Thanks again.
Your suggestion is great, & most likely implemented for the future.
Does that mean that for history it is not possible to identify for G/L Accounts?

ara3n

one thing about the above solution is that it's one invoice to many shipment relationship. If it's many to many, you'll need a separate table and then fill the table based on quantity shipped not invoiced on shipment line.
For example if you do multiple partial invoice on one shipment.

as for history, you write report to fill the data, and things that you can't populate them them with best guess.

I would suggest invoicing at the same time when you ship. Just include the invoice in the package.

idiot

Thanks again.
Looks like my analysis tallied with your suggestions.
Now how should I explain to the auditors... :-k

ara3n

The Order no, is the link. For a given order you can have many invoices, and many shipments. The system does not allow invoice before shipments.

If they ask for an invoice and the shipment. Find all the invoices and shipments and show them the documents.

Explain that's how they were posted.

idiot

I think everyone know how documents are posted.
The real objective is mentioned in my previous post.
Without the ability to trace Shipments & Invoices there can be no way of determining the time lag between the 2 documents, for reasons of analysis.
Hence there is difficulty in obtaining accurate financial ratios & this leads to other problems...
In depth technically what is needed is just the Shipment Posting Date & the Invoice Posting Date for a related set of transactions of Items & G/L Accounts.

ara3n

idiot wrote:

I think everyone know how documents are posted.
The real objective is mentioned in my previous post.
Without the ability to trace Shipments & Invoices there can be no way of determining the time lag between the 2 documents, for reasons of analysis.
Hence there is difficulty in obtaining accurate financial ratios & this leads to other problems...
In depth technically what is needed is just the Shipment Posting Date & the Invoice Posting Date for a related set of transactions of Items & G/L Accounts.

right and for that you can make the modification to track the relationships between the two.

You are bringing up two problems. One the auditor, second analysis of your shipments and invoicing.

With the modification you'll have both going forward.
Auditors do not need the modification.

idiot

Actually the problem is caused by the auditors... 8) so its just one problem...

Going forward is another problem or rather the solution.
The current problem is explaining why such a report is not possible or rather not 100% accurate.
And if you've dealt with auditors before you'd know they if there's anything they'd not give a clean bill of health...

ara3n

Hello Just wanted to let you know that in 5.0. they have added of the sales shipment form line button a new option. It is called "Item Invoice Lines" and it shows the invoice lines related to sales shipment lines.
The code loops through the item ledger, then finds the associated value entries and then gets the invoice lines related the value entries.

idiot

](*,) ](*,) ](*,) ](*,)
Almost every thing customized in 4.01 now becomes part of 5.0 standard.
The company should have waited....

themave

idiot wrote:

](*,) ](*,) ](*,) ](*,)
Almost every thing customized in 4.01 now becomes part of 5.0 standard.
The company should have waited....

you are not looking at it from a customer point of few. if you needed Navision a year ago, but decided to wait for 5.0 first, then you would also probably then wait for 5.1 since that is the newest technology, but wait you can't go with the first release you will need to wait for 5.1 service pack 1

So now your business has been operating on an old system that didn't meet your needs (which is why you where looking at navision in the first place) for a few extra years. What has that gotten you.

We went through the same decision in back in 2005, we were on 2.0 with advanced distribution. decided to go to 4.0 for many business reasons. went live in Jan. 2006. so should we have stayed with 2.0 that was no longer meeting our needs to wait for the next big release ( at that point 5.0 was already announced ( now known as 5.1). No we moved to the then current version and programmed changes in that it was missing, been on the system for 2 years now. It would not be to our advantage to keep waiting for the next release. our business has to operate today.

cnicola

Perhaps this will be a dumb post but: what if you ship say 2 units, then you ship 3 units then you invoice 1 unit then you invoice 3 and then you invoice 1? How would you provide an answer as to which invoice is for which shipment?
I know that this probably is not likely to happen in real life but that is the source of the problem: you have a lot of flexibility out of the box and that makes it hard to make direct connections like the one that OP is asking. And why it will never be 100% accurate (including 5.0).

So then you make up your own rules:

1. When you invoice you have to invoice everything that has been shipped but not invoiced until then. That way you will have a many to 1 relationship between shipments and invoices.
2. Once you ship once you cannot ship again until you have invoiced the quantites you just shipped. That way you will have a 1 to 1 relationship between shipments and invoices.

Depending on which makes sense for your business you pick one and then you make a very simple mod accordingly. It checks for the above rules and then it stamps the invoice with the shipment number in scenario 2 or the shipments with the invoice number in scenarion 1 and then your report will be 100% accurate.

ara3n

cnicola wrote:

Perhaps this will be a dumb post but: what if you ship say 2 units, then you ship 3 units then you invoice 1 unit then you invoice 3 and then you invoice 1? How would you provide an answer as to which invoice is for which shipment?
I know that this probably is not likely to happen in real life but that is the source of the problem: you have a lot of flexibility out of the box and that makes it hard to make direct connections like the one that OP is asking. And why it will never be 100% accurate (including 5.0).

So then you make up your own rules:

1. When you invoice you have to invoice everything that has been shipped but not invoiced until then. That way you will have a many to 1 relationship between shipments and invoices.
2. Once you ship once you cannot ship again until you have invoiced the quantites you just shipped. That way you will have a 1 to 1 relationship between shipments and invoices.

Depending on which makes sense for your business you pick one and then you make a very simple mod accordingly. It checks for the above rules and then it stamps the invoice with the shipment number in scenario 2 or the shipments with the invoice number in scenarion 1 and then your report will be 100% accurate.

Try it on 5.0 and let us know.