Hi everyone! We are trying to build reports from raw netsuite data, extracting it through JBDC. But to model it back to the way we see data in netsuite seems cumbersome and time consuming. Has extracted raw data through JDBC and created netsuite data model for reporting?

Yes, and it isn't that difficult. What issues do you have?

I'm trying to get spend by vendor and the numbers don't match with what i see in Netsuite, some numbers do match

WITH TRANSACTIONS_DETAILS AS (SELECT 
        V.NAME AS VENDOR_NAME,
        T.TRANID AS TRANSACTION_NUMBER,
        T.TRANDATE AS TRANSACTION_DATE,
        T.STATUS AS TRANSACTION_STATUS,
        T.CREATE_DATE AS TRANSACTION_CREATION_DATE,
        TL.NON_POSTING_LINE AS NON_POSTING_LINE,
        CAST(CASE WHEN TL.TRANSACTION_LINE_ID=0 THEN (-1 * TL.AMOUNT) ELSE NULL END AS DOUBLE) AS CREDIT_AMOUNT, 
        CAST(CASE WHEN TL.TRANSACTION_LINE_ID>0 THEN (-1 * TL.AMOUNT) ELSE NULL END AS DOUBLE) AS DEBIT_AMOUNT,
        T.TRANSACTION_TYPE AS TRANSACTION_TYPE, 
        T.CREATED_FROM_ID AS SOURCE_TRANSACTION_INTERNAL_ID,
        T.TRANSACTION_ID AS TRANSACTION_INTERNAL_ID, 
        TL.EXPECTED_RECEIPT_DATE AS EXPECTED_RECEIPT_DATE,
        TL.TRANSACTION_LINE_ID as TRANSACTION_LINE_ID,
        TL.ITEM_COUNT as ITEM_COUNT
    FROM TRANSACTIONS T
    JOIN TRANSACTION_LINES TL
        ON T.TRANSACTION_ID = TL.TRANSACTION_ID
    LEFT JOIN VENDORS V ON V.VENDOR_ID = T.ENTITY_ID)


SELECT ACCOUNTING_PERIOD,
VENDOR_NAME,
sum(CREDIT_AMOUNT)
FROM TRANSACTIONS_DETAILS
group by VENDOR_NAME

Hi Raghu - we have a prebuilt solution for this. Check out www.satorireporting.com

This is great, does this support quicksight?

No - just Power BI

ah ok

TL.AMOUNT

is signed anyway (debits are positive and credits are negative) - so you can just test on that rather than line number. E.g. if you have withholding tax or a credit line on any of your vendor bills your existing statement will be wrong You possibly want an additional criteria

T.IS_NON_POSTING

= 'No' Your query as defined will include journals that have vendors attached to them that don't hit the AP account. Not sure if that is what you want or not

I initially went with +ve --> debit and -ve is credit, but that's not giving the right amount as well, due to refunds and other things Yea, right, the intention is to create a base transactions_denormalized table with all the transaction types and later filter out data as needed during reporting. Below is the full logic that ise to create the transactions_denorm table.

WITH TRANSACTIONS_DETAILS AS (SELECT 
        AP.NAME AS ACCOUNTING_PERIOD,
        V.NAME AS VENDOR_NAME,
        T.TRANID AS TRANSACTION_NUMBER,
        T.TRANDATE AS TRANSACTION_DATE,
        T.STATUS AS TRANSACTION_STATUS,
        T.CREATE_DATE AS TRANSACTION_CREATION_DATE,
        TL.NON_POSTING_LINE AS NON_POSTING_LINE,
        CAST(CASE WHEN TL.TRANSACTION_LINE_ID=0 THEN (-1 * TL.AMOUNT) ELSE NULL END AS DOUBLE) AS CREDIT_AMOUNT, 
        CAST(CASE WHEN TL.TRANSACTION_LINE_ID>0 THEN (-1 * TL.AMOUNT) ELSE NULL END AS DOUBLE) AS DEBIT_AMOUNT,
        T.TRANSACTION_TYPE AS TRANSACTION_TYPE, 
        T.CREATED_FROM_ID AS SOURCE_TRANSACTION_INTERNAL_ID,
        T.TRANSACTION_ID AS TRANSACTION_INTERNAL_ID, 
        D.NAME AS DEPARTMENT_NAME,
        I.NAME AS ITEM_NAME,
        I.TYPE_NAME AS ITEM_TYPE_NAME,
        L.NAME AS LOCATION_NAME,
        L.CITY AS LOCATION_CITY,
        L.COUNTRY AS LOCATION_COUNTRY,
        S.NAME AS SUBSIDIARY_NAME,
        C.FULL_NAME AS CLASS_FULL_NAME,
        E.FULL_NAME AS REQUESTOR_NAME,
        TL.EXPECTED_RECEIPT_DATE AS EXPECTED_RECEIPT_DATE,
        AP.STARTING AS ACCOUNTING_PERIOD_STARTING,
        AP.ENDING AS ACCOUNTING_PERIOD_ENDING,
        TL.TRANSACTION_LINE_ID as TRANSACTION_LINE_ID,
        TL.ITEM_COUNT as ITEM_COUNT
    FROM TRANSACTIONS T
    JOIN TRANSACTION_LINES TL
        ON T.TRANSACTION_ID = TL.TRANSACTION_ID
    LEFT JOIN VENDORS V ON V.VENDOR_ID = T.ENTITY_ID
    LEFT JOIN ACCOUNTING_PERIODS AP ON AP.ACCOUNTING_PERIOD_ID = T.ACCOUNTING_PERIOD_ID
    LEFT JOIN ITEMS I ON I.ITEM_ID = TL.ITEM_ID
    LEFT JOIN ACCOUNTS A ON A.ACCOUNT_ID=TL.ACCOUNT_ID
    LEFT JOIN DEPARTMENTS D ON D.DEPARTMENT_ID=TL.DEPARTMENT_ID
    LEFT JOIN LOCATIONS L ON L.LOCATION_ID=TL.LOCATION_ID
    LEFT JOIN SUBSIDIARIES S ON S.SUBSIDIARY_ID = TL.SUBSIDIARY_ID
    LEFT JOIN CLASSES C ON C.CLASS_ID = TL.CLASS_ID
    LEFT JOIN EMPLOYEES E ON E.EMPLOYEE_ID = T.REQUESTED_BY_ID)

Main line = line 0 I think

thanks for your help @Jon Kears