Intercompany orders

(6) Share

Report

Posted on by MacNeg

122

Hello,

I am trying to create a simple list screen that will list intercompany PO-SO orders where their statuses are not in synch. I found tables to use and was able to verify the SQL to use in SQLDB (as inner join). But when I used it in X++, using query or override to executeQuery, I am not getting any information back from the IC sales order and only works as outer join. My main data source is purchTable. I am using fields intercompanycompanyid and intercompanysalesid, to map the record to salesTable. Is there a special setting on the Xpp side that I missed?

Thanks in advance!

Categories:

Dynamics 365 Finance

All responses (23)

Answers (1)

MacNeg 122 on at

Like (0)

Report

Intercompany orders

Yay, it worked! Happy Friyay!
MacNeg 122 on at

Like (0)

Report

Intercompany orders

Thanks Martin. Got it. Sorry, I am a newbie - no wonder it was coded generateOnly - LOL. I will update my SQL and see what happens.

Is there other good site to find information about X++ options, etc for D365 FAO, not AX?

Thanks!
Verified answer

Martin Dráb 232,133 Most Valuable Professional on at

Like (1)

Report

Intercompany orders

It's because didn't select anything from SalesTable. You used the table buffer with generateOnly keyword, just to generate the SQL statement.
MacNeg 122 on at

Like (0)

Report

Intercompany orders

Hello,

I re-written the select statement as shown below. It is now showing the correct IC SO from PO and also in the resulting SQL statement, but when I tried to display the value, I am not getting anything from the salesTable. Not sure why info is not showing the read values. It shows 2 records were read. :(

TIA.

internal final class GetSqlDemo
{
public static void main(Args _args)
{
PurchTable purchTable;
SalesTable salesTable;
int salesCount = 0;
int purchCount = 0;
while select crosscompany purchTable
where purchTable.dataAreaId == "USMF"
&& purchTable.InterCompanyOrder == NoYes::Yes
{
purchCount++;
info(strFmt("Purch Comp: %1, Purch ID: %2, InterCompanySalesId: %3, InterCompanyCompanyId: %4",
purchTable.DataAreaId, purchTable.PurchId, purchTable.InterCompanySalesId, purchTable.InterCompanyCompanyId));
select generateOnly forceLiterals crosscompany salesTable
where salesTable.SalesId == purchTable.InterCompanySalesId
&& salesTable.DataAreaId == purchTable.InterCompanyCompanyId;
info(strFmt("Sales Comp: %1, Sales ID: %2, Purch Comp: %3, Purch ID: %4", salesTable.DataAreaId, salesTable.SalesId, purchTable.DataAreaId, purchTable.PurchId));
salesCount++;
info(salesTable.getSQLStatement());
}
info(strFmt("Total intercompany purchase orders: %1, sales orders: %2", purchCount, salesCount));
}
}

Records 3 and 4 below for USMF PO and USRT SO:
Martin Dráb 232,133 Most Valuable Professional on at

Like (1)

Report

Intercompany orders

Ah, sorry, I see.

At least we know what's wrong in the query, because getSqlStatement() works.

Unfortunately, this seems to be a limitation of F&O cross-company queries. They don't filter by DataAreaId but they do add the relation between joined tables.
MacNeg 122 on at

Like (0)

Report

Intercompany orders

Hi Martin,

Thanks again for responding. It is the same code you forwarded to me for the select statement. Your SQL shows the additional dataareaid and partition codes when there is a crossCompany in the select statement.

For the querybuilddatasource code, I just added the 2 options to ignore dataareadid and partition but it wont compile because the methods are not valid - that is why i think its a legacy method.
Martin Dráb 232,133 Most Valuable Professional on at

Like (1)

Report

Intercompany orders

Please show us your X++ code too.

queryBuildDataSource() is a valid method, but you must also consider which class you're talking about. There are classes that have this method and others than don't. But queryBuildDataSource() isn't related to our discussion if you create the query by a select statement.
MacNeg 122 on at

Like (1)

Report

Intercompany orders

I got the same result when using join (which is what I want), there is a code appended for dataareaid and partition. But If I comment those out, then it matches the desired result I got from SQL query from MSSMS.

I googled for option to ignore adding dataareaid in the where clause and found that crossCompany should take care of it. But not sure why my sql is still adding it. I also found an option of querybuilddatasource to ignore dataareaid but it is not a valid method in FAO (maybe used to be available in AX?). Thanks!
MacNeg 122 on at

Like (1)

Report

Intercompany orders

Good morning,

Thanks a lot Martin. Your code worked and was able to see the SQL statement generated. I updated my code and had to comment out the "when" code because the generateOnly is generating an error. I guess it only works for 1 record return and not on loop? Anyway, I am able to see the SQL, yay!, and in analyzing the SQL code, the reason why it is not reading SO correcrtly, was because it is still mapping the dataareaID from both tables, which is incorrect. How do we ignore or override or stop X++ from adding the data area id mapping in this sql statement?

Thanks again Martin, appreciate all your help!
Martin Dráb 232,133 Most Valuable Professional on at

Like (2)

Report
Intercompany orders

Okay, let's try it differently this time. Don't write your own code, copy this whole class and run it:

internal final class GetSqlDemo { public static void main(Args _args) { PurchTable purchTable; SalesTable salesTable; select generateOnly forceLiterals crossCompany purchTable where purchTable.dataAreaId == "USMF" && purchTable.InterCompanyOrder == NoYes::Yes outer join salesTable where salesTable.SalesId == purchTable.InterCompanySalesId && salesTable.DataAreaId == purchTable.InterCompanyCompanyId; info(purchTable.getSQLStatement()); } }

It works correctly for me, I get a result like this (I just removed the field list):

SELECT * FROM PURCHTABLE T1 LEFT OUTER JOIN SALESTABLE T2 ON ((T2.PARTITION=5637144576) AND ((T1.INTERCOMPANYSALESID=T2.SALESID AND (T1.DATAAREAID = T2.DATAAREAID) AND (T1.PARTITION = T2.PARTITION)) AND (T1.INTERCOMPANYCOMPANYID=T2.DATAAREAID AND (T1.DATAAREAID = T2.DATAAREAID) AND (T1.PARTITION = T2.PARTITION)))) WHERE ((T1.PARTITION=5637144576) AND ((T1.DATAAREAID=N'USMF') AND (T1.INTERCOMPANYORDER=1)))

The AI-generated reply posted by Saif is just something generic about inter-company; it's not really relevant to this discussion.