Solved

GI on Invoice and SalesOrder

L
Rank

Hello,

Im trying to build GI with ARInvoice being linked to SalesOrder. I tried to use InvoiceNbr in both but no luck. How can I do it?

icon

Best answer by jinin 20 July 2022, 07:51

View original

10 replies

Naveen Boga
Rank
Userlevel 7
Badge +17

Hi @Leo05  Assuming that you are using SO behavior Sales Order but not the Invoice Type.

If yes, below is the query to link the Sales Order to Invoice. You can use the same joins in the GI to fetch the details. Let me know if you need any help on the GI.

 

select * from SOOrder S inner join SOOrderShipment SS on S.CompanyID=SS.CompanyID and S.OrderType=SS.OrderType and S.OrderNbr=SS.OrderNbr
Inner Join ARTran AR on AR.CompanyID=S.CompanyID and AR.SOShipmentType = SS.ShipmentType and AR.SOShipmentNbr = SS.ShipmentNbr
Where S.CompanyID=2 and S.OrderNbr='SO006683'

 

Naveen B
L
Rank

Thanks for reply but I need a bit different stuff. Im tracking Invoice updates and need to filter invoices by sales order properties. So I need to build relations between AR.ARInvoice and SO.SOOrder tables.

J
Rank
Userlevel 7
Badge +11

Hi @Leo05 ,

Please find the below GI sample. You can add conditions and result grids based on your requirement.

 

 

1 Attachment
Jini N
L
Rank

Hi @Leo05 ,

Please find the below GI sample. You can add conditions and result grids based on your requirement.

 

 

Thanks @jinin,

The problem I do not have permissions to import GI if possible could you please create screenshot with relations tab?

J
Rank
Userlevel 7
Badge +11

Hi @Leo05 

Please find the screenshots
 

 

Jini N
J
Rank
Userlevel 7
Badge +11

Hi @Leo05 

Please find the screenshots
 

 

 

Jini N
M
Rank

@jinin This is helpful - thank you! When I run this, I get a lot of duplicate lines. I’m a newbie so there might be something basic I’m missing. Or is it the way the tables are joined/ related? Thank you.

M
Rank

I used group by and I think it is working now. Thank you anyway.

B
Rank
Userlevel 1
Badge

Has anyone managed to join the ARTran, SOOrder and SOLine tables without causing duplicates?

 

C
Rank
Userlevel 7
Badge +9

Hi @Bree612 Please find the attached Generic Inquiry as a solution.
We could join the ARTran, SOOrder and SOLine without causing duplicates with the below limitations.

Limitation: The Sales Order has one Shipment and One Invoice.

Screenshots for reference:

 

1 Attachment
Chandra M

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved