Best way to add Shipment No. in Sales Invoice

aceX

Hi there,

I need opinion and help, how to get Sales Shipment No. in lines of the invoice. I need every item in the document to be grouped by Sales Shipment No. For example lines in the sales invoice should look look like in the example.



Best Regards

marcusc

That is standard field. shipment no. field

Then you should not look on the header. Look at the line, as all the information also in the line. Go take a look.

xStepa

Hi, I think there is no direct link between them - there can be a N:N relation between Invoices and Shipments - try to bind them via Order No. & Line No. :

SELECT SIL.[Line No_], SIL.No_, SIL.Description, SIL.Quantity, SSL.[Document No_] FROM [YourCompany$Sales Invoice Line] as SIL
OUTER APPLY (SELECT SIH.[Order No_] FROM [YourCompany$Sales Invoice Header] as SIH WHERE SIH.No_ = SIL.[Document No_]) as rSIH
OUTER APPLY (SELECT SSH.No_ FROM [YourCompany$Sales Shipment Header] as SSH WHERE SSH.[Order No_] = rSIH.[Order No_]) as rSSH
LEFT OUTER JOIN [YourCompany$Sales Shipment Line] as SSL ON SSL.[Document No_] = rSSH.No_ AND SSL.[Line No_] = SIL.[Line No_]
WHERE SIL.[Document No_] = 'YourInvoiceNo'
ORDER BY SSL.[Document No_], SIL.[Line No_]

Try to transform this query into a dataset and add a group in the layout.

(or maybe I missed something from your question )

vaprog

Hi aceX,

disallow posting invoices from sales order if more than one shipment might be involved. Instead create a new sales invoice and use the Get shipment lines... functionality from there. This establishes the link you need.

aceX

Actually there is a link between Posted Sales Invoices and Posted Sales Shipments. I need to get the Document No. and No. from Sales Invoice Line, and search the Value Entry table for this document. For lines from Value Entry I need to use Item Ledger Entry No. field and go to ILE where I will find the Shipment No. in ILE."Document No.". But the problem is, I think I'll need to use two temporary tables and in the end to use Item Ledger Entry as a temp table in the Report - Sales Invoice instead of Sales Invoice Line to show the lines ... :S and that is the solution that I don't want to use. So is any other option (idea) which someone other used ...

Thanks in advice
Best Regards

Jan_Veenendaal

I think that if you create the sales invoices using the report 'Combine Shipments', then the text including the shipment number is automatically inserted as an invoice line, and all lines are created in the correct order. Then only thing you should need to do is to have the invoice lines sorted (as usual) on Line No.

aceX

Hi @Jan_Veenendaal

But I need the Shipments to be printed and on historical posted sales invoices too. All of this must be enabled in the report.

marcusc

there is shipment no. field in posted sales invoice.

aceX

Hi @marcusc,

In Sales Invoice Header only exist Order No. field, the Shipment No. does not exist in original table. I suppose the field Shipment No. is custom in your case.
However, even if that field exist, cannot be inserted into header in my case, because one invoice can be created from maybe 10 sales shipments.

Regards

marcusc

That is standard field. shipment no. field

Then you should not look on the header. Look at the line, as all the information also in the line. Go take a look.

Miklos_Hollender

As other said for new invoices get shipment lines does exactly this, but if you want to change historic invoices, how about rejecting that request? In most legislations changing posted invoices is not allowed. In fact we are getting in trouble that the company moves to a new address, there is a tax audit, we reprint old invoices and it has the new address because it is a real time lookup from the Company Info table so actually I know recommend users to archive invoices on paper or on proper archiving software and never reprint old invoices!

Check with your tax auditor. 99% of the cases old invoices are not to be changed.

aceX

Yes @marcusc, you ware right. Shipment No is existing field. I've tried to find it in Sales Header / Sales Invoice Header. We will try to convince the client to use Get Shipments Lines function. So one good solution may be it's this.

@Miklos_Hollender also you are right. But my intention is not to change the posted sales invoices, but to get the sales shipments only in the report and only in the report Sales Invoice ...