Finance | Project Operations, Human Resources, ...

Sales Order Return (Credit sale) RDP Class Report

(0) Share

Report

Posted on by AhsanZia

Please help me

I want to develop a RDP Class report in Ax 2012 where I need to show Only Returned Sales Orders with fields Invoice account, customer name, qty, Line amount.

I'm not sure on how to get the relation from SO Id and Return SO ID. Do I need to use join? Which tables are to be used in order to get all the fields related to a return sales order?

Thanks

All responses (9)

Answers (0)

Sergei Minozhenko 23,091 on at

Like (0)

Report

RE: Sales Order Return (Credit sale) RDP Class Report

Hi AhsanZia,

Could you try to debug it and check if salesLine.SalesQty is not empty when you prepare data? Do you see these quantities from UI, maybe source is different?
AhsanZia 30 on at

Like (0)

Report

RE: Sales Order Return (Credit sale) RDP Class Report

Hi Sergie

Thank you very much for the help.

I got it right by adding Insert in the if statement

But 1 problem now

The quantity for last Order DE-001 is not showing up. Last 2 rows are return lines of Return Order DE-001 and they have quantity -14 and -2593 respectively.

Any mistake on my end? what should I do?
Suggested answer

Sergei Minozhenko 23,091 on at

Like (0)

Report

RE: Sales Order Return (Credit sale) RDP Class Report

Hi AhsanZia,

Insert statement for tmp cursor is outside if statement and it means that you insert a record for the report for every order (should be inside). Also, I suggest adding a range by SalesType to query instead of doing a loop through all records.

ItemName and ReturnItemNum probably also can be fetched from salesLine cursor directly without additional find method to improve performance and get rid of unnecessary calls to the database.
AhsanZia 30 on at

Like (0)

Report

RE: Sales Order Return (Credit sale) RDP Class Report

HI

Here is the code. I've 3 return orders and it is returning 3 but it is showing 250 pages and repeating the 3 records in the preview of the report. Also showing so many blank rows

Images:

Code:

QueryRun queryRun;

QueryBuildDataSource queryBuildDataSource;

QueryBuildRange queryBuildRange;

Query query;

SalesTable salesTable;

SalesLine salesLine;

CustInvoiceJour custInvJour;

CustInvoiceTrans custInvTrans;

CustTable custTable;

date fromDate;

date toDate;

MZNAZ_CreditSaleContract creditSaleContract;

query = this.parmQuery();

creditSaleContract = this.parmDataContract();

fromDate = creditSaleContract.parmFromDate();

toDate = creditSaleContract.parmToDate();

queryBuildDataSource = query.dataSourceTable(tableNum(SalesTable));

queryBuildRange = queryBuildDataSource.addRange(fieldNum(SalesTable, DeliveryDate ));

queryBuildRange.value(SysQuery::range(fromDate,toDate));

queryRun = new QueryRun(query);

while(queryRun.next())

{

salesLine = queryRun.get(tableNum(SalesLine));

salesTable = queryRun.get(tableNum(SalesTable));

if(salesTable.SalesType==4) {

creditSaleTmp.SalesId = salesTable.SalesId;

creditSaleTmp.LineAmount = salesLine.LineAmount;

creditSaleTmp.SalesQty = salesLine.SalesQty;

creditSaleTmp.ItemName = salesLine::find(salesTable.SalesId).itemName();

creditSaleTmp.ReturnItemNum = salesLine::find(salesTable.SalesId).invoiceAccount();

creditSaleTmp.ItemId = salesLine.ItemId;

creditSaleTmp.CustName = salesTable.SalesName;

}

creditSaleTmp.insert();

}
Sergei Minozhenko 23,091 on at

Like (1)

Report

RE: Sales Order Return (Credit sale) RDP Class Report

Hi AhsanZia,

Could you, please, share the code you are using to get this data?
AhsanZia 30 on at

Like (0)

Report

RE: Sales Order Return (Credit sale) RDP Class Report

Hi

I'm getting the return orders from SalesTable and SalesLine table but there is a problem that I have 3 return orders in total and I my report its showing 4 orders but they are getting duplicated in 250+ pages of the report.

Instead there should be only 3 rows of record.

Can u help me solving this issue? And am I using right tables to get the return orders?
Suggested answer

Luke Sha on at

Like (1)

Report

RE: Sales Order Return (Credit sale) RDP Class Report

If you want to know the relation between tables in AX, you can refer ERD

docs.microsoft.com/.../database-erds-on-the-axerd-website

or

alexdmeyer.com/.../Erd-One-GenLed-L1-GJAE.htm
AhsanZia 30 on at

Like (0)

Report

RE: Sales Order Return (Credit sale) RDP Class Report

Hi Sergie

I really appreciate your help

The issue is I'm confused about the tables. For example, I have read many blogs and people are writing about GeneralJournalAccountEntry Table, InventTrans Table , CustInvoiceJour and CustInvoiceTrans Tables.

That's why I need help on the join and tables in my query
Suggested answer

Sergei Minozhenko 23,091 on at

Like (1)

Report

RE: Sales Order Return (Credit sale) RDP Class Report

Hi AhsanZia,

The easiest way would be to check the query for standard form "Sales and marketing - Return orders". Basically it's SalesTable with filter by SalesType = Returned order + related (join) SalesLines.