How to Fetch 1st inserted data from purchlineAllversion

(0) Share

Report

Posted on by DarkE

I am going to create AX2012 R3 RDP SSRS Report to show Vendors Total purchamount and invoiceamount and Variance between purchamount and invoiceamount

purchamount should be 1st entered record in purchline

when purchline amount revised it will affected in table purchlineAllversion has more than one version of purchamount how to fetch the 1st entered record

with purchline Itemid I can join purchlineallversion to get top one record sort by desc

but with respect to GL account, there is know itemid then I don't know how to fetch the purchamount.

I have an idea Linenumber is same for versions so with use of line number we can get 1st enetered record

I am in the confusion on how to get those record using linenumber

purchlineallversion

purchid	linenumber	lineamount	VersionDatetime
P001	1	50	6/14/2018 13:00:00
P001	1	45	6/14/2018 14:00:00
P001	2	60	6/14/2018 16:00:00
P001	2	75	6/14/2018 18:00:00

I need to fetch those 1st entered data

purchid : P001 and Lineamount : 50 linenumber :1

purchid : P001 and Lineamount : 60 linenumber :2

Thanks!!!

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (0)

Sort by

Sohaib Cheema 49,677 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

you may trust an algorithm as following

IF(PO never confimed)

{

//take price of line from PurchLine

}

Else

{

//find 1st confirmation of PO in which this PO line exists.

//take price from confirmation journal line

}

This logic can be seen in createData(method) of PurchPurchaseOrderDP(Class)

Was this reply helpful? Yes No
DarkE on at

Like (0)

Report
Copy link

Link copied!

Hi Sohaib,

Thanks for your quick response I have another doubt.

I am can achieve 1st entered record in sql server I need to get this in X++

CREATE TABLE [dbo].[PurchLineAllVersion](

[PurchID] [varchar](50) NOT NULL,

[LineNumber] [int] NOT NULL,

[LineAmount] [int] NOT NULL,

[VersionDateTime] [datetime] NOT NULL

) ON [PRIMARY]

insert into Purchlineallversion

(Purchid,[LineNumber],[LineAmount],[VersionDateTime]) values

('P001',1,50,'6/14/2018 13:00:00'),

('P001',1,45,'6/14/2018 14:00:00'),

('P001',2,60,'6/14/2018 16:00:00'),

('P001',2,75,'6/14/2018 18:00:00')

select * from Purchlineallversion where [VersionDateTime] in ( select Min([VersionDateTime]) from Purchlineallversion group by linenumber)

Thanks!!!

Was this reply helpful? Yes No
vm3ssy 72 on at

Like (0)

Report
Copy link

Link copied!

Hi did you get this working? I have the same requirement.

Thanks

Was this reply helpful? Yes No
DarkE on at

Like (0)

Report
Copy link

Link copied!

Hi Vm3ssy,

I solved with this code!!

purchTable = queryRun.get(tablenum(PurchTable));

while select * from purchLine where purchLine.PurchId==purchTable.PurchId

{

select firstOnly1 LineAmount from purchLineAllVersions order by purchLineAllVersions.VersionDateTime asc

where purchLineAllVersions.LineNumber==purchLine.LineNumber && purchLineAllVersions.PurchId == purchLine.PurchId;

select * from taxTrans where taxTrans.InventTransId==purchLine.InventTransId;

if(taxTrans.TaxValue!=0)

{

TotalTax = purchLineAllVersions.LineAmount * (taxTrans.TaxValue /100);

}

poPriceVarianceTmp.OPOrderValue = poPriceVarianceTmp.OPOrderValue + purchLineAllVersions.LineAmount + TotalTax;

}

Thanks!

Was this reply helpful? Yes No