Question

using a SQL script in a GI

  • 28 April 2022
  • 2 replies
  • 258 views
A
Rank
Userlevel 3
Badge +1

We have a SQL script given to us by acumatica  to detect projects that have a problem preventing us to change the AP pay by single project setting.  We then have to make the projects the script provides to activate the projects from a completed state.  Change the AP setting and then mark those same projects complete.  I was hoping to use mass update in a GI to do this so I don’t have to give the AP clerk access to the SQL database to run the script.  The fix for this bug isn’t scheduled for 2023 R1 and we would have to do this process on a weekly basis.  I am open to other ideas also.

 

2 replies

Neil Cantral
Rank
Userlevel 5
Badge +3

Since you have access to SQL you can link a SQL View to a DAC and use that to drive a GI.

It’s an older article, but you can reference this: Building Reports From an SQL View in Acumatica Cloud ERP

A
Rank
Userlevel 3
Badge +1

the script that Acumatica gave us is:

select
        CC.CompanyID,
        CC.ContractCD
from
(
        select
                 A.CompanyID,
                 A.OrderNbr
        from
        (
                 select
                         L.CompanyID,
                         L.OrderNbr,
                         L.ProjectID,
                         C.ContractCD,
                         count(*) as 'c'
                 from POOrder O
                         inner join POline L on O.CompanyID = L.CompanyID and O.OrderNbr = L.OrderNbr and L.OrderType = 'RS'
                                  inner join Contract C on L.CompanyID = C.CompanyID and L.ProjectID = C.ContractID
                 where O.OrderType = 'RS'
                         and C.Status = 'F'
                 group by
                         L.CompanyID,
                         L.OrderNbr,
                         L.ProjectID,
                         C.ContractCD
        ) A
        group by
                 A.CompanyID,
                 A.OrderNbr
        having count(*) = 1
) B
        inner join POLine LL on B.CompanyID = LL.CompanyID and B.OrderNbr = LL.OrderNbr and LL.OrderType = 'RS'
                 inner join Contract CC on LL.CompanyID = CC.CompanyID and LL.ProjectID = CC.ContractID
group by
        CC.CompanyID,
        CC.ContractCD
order by CC.ContractCD

 

This is only a detection script, the table exist in the DACs but he joins don’t match.  also the ordertype ‘RS’ doesn’t exist in the poline table as data, everything else makes sense.  I don’t think I need to create any DACs, just figure a way to convert the script to a GI.

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