SuiteQL – Query Credit Memos

Hi All,

I’m new to using SuiteQL and am trying to query open credit memos. I’ve followed some other resources which say I should be able to query with something like this:

SELECT
Transaction.ID AS TransactionID,
Transaction.TransactionNumber,
Transaction.TranID,
Transaction.Type,
Transaction.TranDisplayName,
Transaction.TranDate,
Transaction.CreatedDate,
Transaction.DueDate,
Transaction.DaysOpen,
Transaction.ForeignTotal,
Transaction.ForeignAmountUnpaid,
Transaction.PaymentHold,
BUILTIN.DF( Transaction.CreatedBy ) AS CreatedBy,
BUILTIN.DF( Transaction.Status ) AS Status,
BUILTIN.DF( Transaction.BillingStatus ) AS BillingStatus,
BUILTIN.DF( Transaction.Terms ) AS Terms,
Transaction.Memo,
Transaction.ApprovalStatus,
Vendor.ID AS VendorID,
Vendor.AltName AS VendorName,
FROM
Transaction
INNER JOIN Entity AS Vendor ON
( Vendor.ID = Transaction.Entity )
LEFT OUTER JOIN EntityAddress AS VendorAddress ON
( VendorAddress.nkey = Transaction.BillingAddress )
WHERE
( Transaction.Type IN ( ‘VendBill’, ‘VendCred’ ) )
AND ( Transaction.Void = ‘F’ )
AND ( Vendor.AltName = ‘VendorName’)
AND ( Transaction.ForeignAmountUnpaid <> 0 )
( Transaction.ID = 212765032001)

 

However, when I do I don’t see any VendCred records even if I strip it down and just have a where clause on the vendor. Any tips on where I’m going wrong?

Thanks

Rookie Asked on February 6, 2024 in SuiteAnalytics.
Add Comment
4 Answer(s)

Hi,

Do you get anything if you just run it with “( Transaction.Type IN ( ‘VendBill’, ‘VendCred’ ) )” in the WHERE clause?

Thanks,

Chris

Intermediate Answered on February 8, 2024.
Add Comment

No, sorry I mistyped. I removed it to only ( Transaction.Type IN ( ‘VendBill’, ‘VendCred’ ) ). Just seems like credits aren’t anywhere in the table.

Rookie Answered on February 8, 2024.
Add Comment

Hi,

ForeignAmountUnpaid field in Vendor Credit is null,

Try with  AND ( Transaction.ForeignAmountUnpaid <> 0 or (Transaction.foreigntotal – Transaction.foreignpaymentamountused) <> 0)

SELECT
Transaction.*
FROM
Transaction
INNER JOIN Entity AS Vendor ON
( Vendor.ID = Transaction.Entity )
LEFT OUTER JOIN EntityAddress AS VendorAddress ON
( VendorAddress.nkey = Transaction.BillingAddress )
WHERE
Transaction.Type IN (‘VendCred’, ‘VendBill’ )
AND ( Transaction.Void = ‘F’ )
AND ( Transaction.ForeignAmountUnpaid <> 0 or (Transaction.foreigntotal – Transaction.foreignpaymentamountused) <> 0)

Matteo

Rookie Answered on February 10, 2024.
Add Comment

Have you checked the permissions on the role you’re using?

Intermediate Answered on February 12, 2024.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.
  • This site made possible by our sponsors:   Tipalti   Celigo   Become a Sponsor   Become a Sponsor