Joining CustPackingSlipJour and CustInvoiceJour

(0) Share

Report

Posted on by Community Member

Hi Everybody!

I'm trying to create a report to show that all Invoices SHOULD have a Packing Slip ID but I've hit a bit of an issue trying to join these tables together.

Out of my population of Invoices in CustInvoiceJour I seem to have SalesID that are NULL which is very strange.

How can there be SalesID of NULL?

Are there any other fields I can use to join these table togther?

Thanks in advance guys!

Cheers,

Dan

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (0)

Sort by

Suggested answer

Chaitanya Golla 17,225 on at

Like (0)

Report
Copy link

Link copied!

Hi,

Relationship between custInvoiceJour and custInvoiceTrans is

CustInvoiceTrans.SalesId == CustInvoiceJour.SalesId

CustInvoiceTrans.InvoiceId == CustInvoiceJour.InvoiceId

CustInvoiceTrans.InvoiceDate == CustInvoiceJour.InvoiceDate

CustInvoiceTrans.numberSequenceGroup == CustInvoiceJour.numberSequenceGroup

(or)

CustInvoiceTrans.ParentRecId == CustInvoiceJour.RecId

Was this reply helpful? Yes No
Suggested answer

Brandon Wiese 17,790 on at

Like (2)

Report
Copy link

Link copied!

Customer invoices can be posted from 3 source documents, i.e. Sales orders, Free text invoices, and General journals (where the Invoice field is specified). Free text invoices and General journals will never have a SalesId, and so of course they will also not have a Packing slip.

The only reliable way to associate a customer packing slip with a customer invoice is through their inventory transactions (InventTrans). Note that because of the many to many relationship, it can be quite complex and reports attempting to show them both can look a little odd. By many to many I mean that a packing slip can be associated with many invoices, and an invoice can be associated with many packing slips. It's convenient, and in many companies a procedural requirement, when they're one to one, but it is not a requirement of the system.

InvoiceID and PackingSlipID are fields on InventTrans. If the relationships from CustPackingSlipJour and CustInvoiceJour to InventTrans are not self evident, let us know.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi Brandon,

Thanks for the detailed reply. This makes sense now I think about it. I’ll give it a bash on Monday and let you know how I get on.

Much appreciated!

Dan

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (1)

Report
Copy link

Link copied!

Sorry, but both answers in my opinion are wrong. There is no direct relation between the packing slip and invoices.

The only relation you have is through the Sales ID. AX has for the packing slip and invoice seperate table to link this. See CUSTINVOICESALESLINK and CUSTPACKINGSALESLINK. This is called a many to many relation.

Example: You can have multiple packing slip on 1 sales order. But you can also have multiple invoices for a sales order. There is no direct relation between the invoice and packing slip.

If you need to analyse whether a sales order is shipped and invoiced, you need a whole different query. You will need to join some Inventory table and the transorigin and look if there is still a physical value on a sales order. If a physical value remains, that means that a part of the sales order is shipped, but not invoiced and results in open standing balance position in your GL.

For exact details, I can expand on this, but you need a good functional understanding of AX and requires more then just a forum post.

Was this reply helpful? Yes No
Brandon Wiese 17,790 on at

Like (0)

Report
Copy link

Link copied!

For inventory items, InventTrans is the direct link between the two. The packing slip will update the status of inventory to Deducted (accruing cost and posting to ledger precisely according to that cost), and the invoice will update it to Sold (applying cost and posting to ledger precisely according to that cost). The cost of goods for an invoice line derives directly from these InventTrans records as found on sales reports and margin reports. They are critical to packing slip and invoice processing, and any InventTrans record that is updated by one packing slip and one invoice necessarily binds those two together through inventory. You may not like it, but that is the core of how it works in AX.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Reread my post I said the same as you did. Technically yes, you are correct, functionally you are not.

"You will need to join some Inventory table and the transorigin and look if there is still a physical value on a sales order. If a physical value remains, that means that a part of the sales order is shipped, but not invoiced and results in open standing balance position in your GL."

If you join the inventtransorigin with the inventtrans and aggregate on sales order, you will also see packingslipid that dont have an invoice yet. This is a more functionally sound approach.

Was this reply helpful? Yes No