[Lynn Kinsman]

In AX 2012 R3, when I use Accounts receivable/Common/Customer invoices/Open customer invoices, project invoices are missing from the list, even though the query contains Transactions type = Project in the list, along with a few others.   I also see the query is using Amount in Transaction currency 0.. which would exclude all credits. i also see that it is using Status = None, which must be AX’s definition of “Open” even though I would define it as not yet settled on the customer’s account.  This status field seems to have values in the dropdown list that pertain to other kinds of actions “None, Drawn, Redrawn, Protested, Honored, Remitted, Invoiced, Invoice remitted, Endorsed, and Endorsement settled. ”  I see that there is a table that drives this list screen called, Open Customer Transactions.  

This leads me to several questions I can use help with from the community:
1) I thought that elements within a query in AX are usually put together with an implied “AND” condition.   Since this standard query is constructed with several lines of Transaction Type = something, wouldn’t the query always fail because each transaction only has one value for ‘type’?  Does AX switch to “OR” operators when the field you are comparing is repeated?  Why wouldn’t we use a comma separated list?  I tried this and it didn’t seem to change how it worked.

2) Why are project invoices being excluded from this list?  Has anyone else experienced this problem?  The Technet article has no mention of project invoices but does name sales invoices, free text and general journals.  https://technet.microsoft.com/en-us/library/hh454994.aspx
I have looked at the values of the three fields in the query on all my project invoices and they meet the criteria (transaction type, amount in transaction currency, status), so why are they missing?  I have concluded that project invoices must be excluded from the parent table “Open customer transactions”, but I don’t know why or how or what this table is.

3) Why would an ‘open invoices’ screen use this field “Status” to determine openness and not the closure date or the last settlement date?  And why would it naturally exclude credits?  Is there some other definition of “open” that I am unaware of?  Technet defines this field as “The status of the bill of exchange”.   https://technet.microsoft.com/en-us/library/aa634902.aspx.  Perhaps the parent table “open customer transactions” is already checking for whether or not the invoice has been settled?  What if it is partially settled – is that still considered “open”?

——————————
Lynn Kinsman
Senior Systems Analyst, Pricing, Rebates, Projects
Kodak Alaris Inc.
Rochester NY
——————————

[Henrik Juel Hansen]

1) Using the same field on a query will use an OR operator. So your query is supposed to include records with the transaction type equal ‘Project’.

Customer transactions based on project invoices are supposed to be included in the CustomerTransaction table.

If you have access to the development environment you can try browse the table “CustTrans” which will provide a total list of all customertransactions without any filter. In the tablebrowser you can try filter out on the field “Transaction Type” equal “Project”.

——————————
Henrik Juel Hansen
AXINTO aps
Denmark
——————————
——————————————-

[Lynn Kinsman]

Thank you to Ludwig and Henrik for your responses.  We are running CU12 as of September 2017, so we should have any hotfixes.  Thank you for the clarification on the “OR” vs “AND”.

Yes, we have many, many posted project invoices since we’ve been live since May 2016.  They show up on the customer’s transaction screen, but not this “open customer invoices” grid.  How do I “check for additional filters not shown in the screenprint”?   Clicking the Advanced Filter button is the only way I know of to see what is being used in the query.  It is my suspicion that something else is filtering that is hidden to me.  How do I see that?

In non-production environment, where I can access the AOT, I have filtered the table CustTrans using TransType = Project, AmountCur 0.. and BillOfExchangeStatus = None, and I get thousands of invoices.  So there is definitely something else being filtered that is hidden to me.

——————————
Lynn Kinsman
Senior Systems Analyst, Pricing, Rebates, Projects
Kodak Alaris Inc.
Rochester NY
——————————
——————————————-

[George Zhao]

?Hi Lynn,

Have you tried to remove all criteria from the query and see what you get? Also in your non-production environment, what do you get from the CustTransOpen table?

——————————
George Zhao
PAR Technology
New Hartford NY
——————————
——————————————-

[Greg Griffith]

Have you tried “clear your usage data”?

——————————
Greg Griffith
Hitachi Solutions
Irvine CA USA
——————————
——————————————-

[Lynn Kinsman]

Thank you Rick, Greg and George for your replies!

Yes, Usage Data has been cleared many times.  This has been a problem since go live.  We cleared everyone’s cache and usage data systematcally when we did our Cu12 upgrade in September and nothing has changed with this issue.

CustTransOpen is the key here I believe.  Looks like it is connected to CustTrans via AccountNum and RefRecId = Recid.  I don’t see any field on CustTransOpen called RefRecId, so I’m not sure how to test to see if the project invoices that have SettleAmountCur = 0 (therefore open balance) are in CustTransOpen.  Which field do I paste the Recid from CustTrans into when filtering CustTransOpen?

Instead, I looked at some project invoices with SettleAmountCur = 0 and I can match them up by their amounts and dates.  Open project transactions seem to be in the CustTransOpen table. 

The root of the problem seems to be this: why aren’t project invoices making the join between CustTransOpen (parent table for the Open Customer Invoices grid) and CustTrans (child table but the one used for the selection criteria for the Open Customer invoices query), yet sales order invoices join perfectly well?    The records are there, just the join is broken, I think.

——————————
Lynn Kinsman
Senior Systems Analyst, Pricing, Rebates, Projects
Kodak Alaris Inc.
Rochester NY
——————————
——————————————-

[Rick Pierson]

?Concerning the CustTransOpen table, it has records only for open CustTrans records.  When the balance of a CustTrans record is brought to 0.00, the corresponding record(s) in the CustTransOpen table are physically deleted (automatically, by AX).

——————————
Rick Pierson
Braun Intertec
Bloomington MN
——————————
——————————————-

[Mark Prouty]

?In AX2009, and I suspect AX2012, project invoices are in separate tables than sales related invoices. You can see them both in Cust transactions, but you have to go to 2 separate places to see project and sales invoices themselves.
In other words, you can go from customer transactions to either project or sales invoices, but from customer invoices, you can only go to sales related customer transactions, and from project invoices to project transactions.
——————————
Mark Prouty
Programmer / Analyst
ANGI Energy Systems
Janesville WI
——————————

[Lynn Kinsman]

Yes, Mark.  I have observed the same thing in AX 2012.  Sales invoices and Project invoices are viewed from different places and are in different tables.  But everything should come together in AR.  All customer transactions should be visible no matter the Transaction Type from each of the various menu options for AR transactions.

I just spoke with one of our developers.  RefRecid is a hidden field, so there is no way for me to try the rules of the join by filtering in the AOT table browser using CustTransOpen.
My theory is that something we customized with Project Invoices must have broken the way these tables are joined together.  

Thanks everyone, for your valuable inputs!  I’ll need to have one of our developers dive in deeper with some SQL tests to see what is going on.

——————————
Lynn Kinsman
Senior Systems Analyst, Pricing, Rebates, Projects
Kodak Alaris Inc.
Rochester NY
——————————
——————————————-

[Rick Pierson]

First, just a small confirmation: in our system, Project Invoices do appear in the Open Customer Invoices grid. 

Second, the results of the following query should help you spot any issues.

SELECT
CTrans_RecId = CTrans.RecId
, CTrans_AccountNum = CTrans.AccountNum
, CTrans_DataAreaId = CTrans.DataAreaId
, CTrans_Closed = CAST(CTrans.Closed AS DATE)
, CTrans_LastSettleDate = CAST(CTrans.LastSettleDate AS DATE)
, CTrans_AmountCUR = CAST(CTrans.AmountCUR AS NUMERIC(32, 2))
, CTrans_SettleAmountCUR = CAST(CTrans.SettleAmountCUR AS NUMERIC(32, 2))
, CTrans_BalanceCUR = CAST(CTrans.AmountMST – CTrans.SettleAmountCUR AS NUMERIC(32, 2))
, CTrans_AmountMST = CAST(CTrans.AmountMST AS NUMERIC(32, 2))
, CTrans_SettleAmountMST = CAST(CTrans.SettleAmountMST AS NUMERIC(32, 2))
, CTrans_BalanceMST = CAST(CTrans.AmountMST – CTrans.SettleAmountMST AS NUMERIC(32, 2))
, SPACER1 = ‘———-‘
, CS_TransRecId = CS.TransRecId
, CS_AccountNum = CS.AccountNum
, CS_TransCompany = CS.TransCompany
, CS_LastSettleDate = CS.LastSettleDate
, CS_TotalSettleAmountCUR = CS.TotalSettleAmountCUR
, CS_TotalSettleAmountMST = CS.TotalSettleAmountMST
, SPACER2 = ‘———-‘
, CTO_RefRecId = CTO.RefRecId
, CTO_TotalAmountCUR = CTO.TotalAmountCUR
, CTO_TotalAmountMST = CTO.TotalAmountMST
, SPACER3 = ‘———-‘
, PIJ_DataAreaId = PIJ.[DataAreaId]
, PIJ_ProjInvoiceId = PIJ.[ProjInvoiceId]
, PIJ_ProposalId = PIJ.[ProposalId]
, PIJ_InvoiceAccount = PIJ.[InvoiceAccount]
, PIJ_OrderAccount = PIJ.[OrderAccount]
, PIJ_LedgerVoucher = PIJ.[LedgerVoucher]
, PIJ_InvoiceDate = CAST(PIJ.[InvoiceDate] AS DATE)
, PIJ_CostValue = CAST(PIJ.[CostValue] AS NUMERIC(32, 4))
, PIJ_InvoiceAmount = CAST(PIJ.[InvoiceAmount] AS NUMERIC(32, 4))
, PIJ_SalesOrderbalance = CAST(PIJ.[SalesOrderbalance] AS NUMERIC(32, 4))
, PIJ_ParmId = PIJ.[ParmId]
, PIJ_ProjInvoiceProjId = PIJ.[ProjInvoiceProjId]

FROM CustTrans CTrans
LEFT OUTER JOIN
(SELECT
CTO.RefRecId
, CTO.AccountNum
, TotalAmountCUR = CAST(SUM(CTO.AmountCUR) AS NUMERIC(32, 2))
, TotalAmountMST = CAST(SUM(CTO.AmountMST) AS NUMERIC(32, 2))
FROM CustTransOpen CTO
GROUP BY CTO.RefRecId, CTO.AccountNum)
AS CTO ON CTO.AccountNum = CTrans.AccountNum AND CTO.RefRecId = CTrans.RecId

LEFT OUTER JOIN
(SELECT
CS.TransRecId
, CS.AccountNum
, CS.TransCompany
, LastSettleDate = CAST(MAX(CS.TransDate) AS DATE)
, TotalSettleAmountCUR = CAST(SUM(CS.SettleAmountCUR) AS NUMERIC(32, 2))
, TotalSettleAmountMST = CAST(SUM(CS.SettleAmountMST) AS NUMERIC(32, 2))
FROM CustSettlement CS
GROUP BY CS.TransRecId, CS.AccountNum, CS.TransCompany)
AS CS ON CS.TransRecId = CTrans.RecId AND CS.AccountNum = CTrans.AccountNum AND CS.TransCompany = CTrans.DataAreaId

LEFT OUTER JOIN ProjInvoiceJour PIJ
ON PIJ.InvoiceAccount = CTrans.AccountNum AND PIJ.InvoiceDate = CTrans.TransDate AND PIJ.LedgerVoucher = CTrans.Voucher AND PIJ.ProjInvoiceID <> ” AND CTrans.Invoice <> ”

[Lynn Kinsman]

Thank you to everyone who replied!    I have to apologize for not realizing this sooner, but the problem is only occurring in our China legal entity.   There appears to be a China localization that is causing the issue.  We are still investigating.  

The examples I had been exploring were for United States, and indeed the grid is working as expected there.  It definitely helped me learn about the table structure, so again THANK YOU to all the contributors to this thread!  I have learned a lot!

——————————
Lynn Kinsman
Senior Systems Analyst, Pricing, Rebates, Projects
Kodak Alaris Inc.
Rochester NY
——————————
——————————————-

[Lynn Kinsman]

In case anyone is wondering how we solved this, we did a customization so now we can see project invoices in our China legal entities on the Open Customer Invoice grid.  Here is the explanation from our in-house developer:

“Since project invoices do not have a record in the CustInvoiceJour table, the inner join was causing those records to be excluded. This was due to a custom field in the CustInvoiceJour table that only shows up for China. The query is smart enough to only retrieve data from a table if there is a field on the report that uses it. When you query with a legal entity other than China (like US11), that field is not required, so the CustInvoiceJour data source is not added to the query. For China, it is required, so only sales invoices (or other types that actually have a record in CustInvoiceJour) are returned. By changing it to an outer join, the project invoices can be returned for China too. This would be the same for any other legal entity that has a CustInvoiceJour field on the report that is specific to that legal entity. I’m not sure if there are others like that or not.”

——————————
Lynn Kinsman
Senior Systems Analyst, Pricing, Rebates, Projects
Kodak Alaris Inc.
Rochester NY
——————————
——————————————-

[George Zhao]

?For the missing transactions, what do you see in the [Status] field?

——————————
George Zhao
PAR Technology
New Hartford NY
——————————
——————————————-

[Ludwig Reinhard]

Hi,
I had a look at a R3 CU12 demo machine and my project invoices were included in the open customer invoice form in AR.
What AX2012 R3 version do you operate? (Cu8/9/…)
Have you searched on LCS for available hotfixes?
Have you checked for additional filters not shown in the screenprint above?
What about the status of your project invoice? Is it already posted or just in draft mode?
Best regards,
Ludwig

——————————
Ludwig Reinhard
SYCOR GmbH
Goettingen
——————————
——————————————-