Skip to main content
Solved

GI on Invoice and SalesOrder

  • Jul 17, 2022 4:08 PM UTC
  • 10 replies
  • 849 views
  • Translate All

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?

Best answer by jinin

Hi @Leo05 

Please find the screenshots
 

 

 

View original

10 replies

Naveen Boga
Captain II
Forum|alt.badge.img+20
  • Captain II
  • Jul 18, 2022 4:56 AM UTC

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'

 

Translate

  • Author
  • Freshman I
  • Jul 18, 2022 8:39 AM UTC

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.

Translate

jinin
Pro I
Forum|alt.badge.img+12
  • Pro I
  • Jul 18, 2022 11:23 AM UTC

Hi @Leo05 ,

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

 

 

Translate

  • Author
  • Freshman I
  • Jul 20, 2022 3:58 AM UTC

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?

Translate

jinin
Pro I
Forum|alt.badge.img+12
  • Pro I
  • Jul 20, 2022 5:46 AM UTC

Hi @Leo05 

Please find the screenshots
 

 

Translate

jinin
Pro I
Forum|alt.badge.img+12
  • Pro I
  • Answer
  • Jul 20, 2022 5:51 AM UTC

Hi @Leo05 

Please find the screenshots
 

 

 

Translate

  • Freshman I
  • Apr 28, 2023 10:40 PM UTC

@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.

Translate

  • Freshman I
  • Apr 29, 2023 12:45 AM UTC

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

Translate

Forum|alt.badge.img
  • Jr Varsity II
  • Oct 14, 2023 3:25 PM UTC

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

 

Translate

Forum|alt.badge.img+8
  • Semi-Pro I
  • Oct 14, 2023 9:38 PM UTC

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:

 

Translate