I made a basic web based reporting system for sales people in our London Office. It also linked to our Hong Kong Office to provide us their sales data. The HK Office recently migrated to Netsuite and without much help and no experience with Oracle/Netsuite I'm trying to get access to their data.
Here is where I'm at for Sales invoiced, I'm not sure where I can find the cost price for a gross margin report? Will need it for Sales Orders too.
SELECT customers.name AS account,
customers.companyname AS custname,
items.name AS itemcode,
items.salesdescription AS description,
brand_code.description AS brand,
character_code.description AS charname,
sales_type_code.description AS typedesc,
countries.name AS country,
Sum(transaction_lines.amount * -1) AS valuehk,
Sum(transaction_lines.amount_foreign * -1) AS value,
Sum(transaction_lines.item_count * -1) AS quantity
FROM transactions
LEFT JOIN transaction_lines
ON transactions.transaction_id = transaction_lines.transaction_id
LEFT JOIN items
ON transaction_lines.item_id = items.item_id
LEFT JOIN brand_code
ON brand_code.brand_code_id = items.brand_code_id
LEFT JOIN customers
ON customers.customer_id = transaction_lines.company_id
LEFT JOIN character_code
ON character_code.character_code_id = items.character_code_id
LEFT JOIN sales_type_code
ON sales_type_code.sales_type_code_id = items.sales_type_code_id
LEFT JOIN countries
ON countries.short_name = customers.country
WHERE transactions.transaction_type = 'Invoice'
AND customers.name NOT IN ( 'ZUK', 'ZT', 'ZWE', 'ZVE' )
AND transaction_lines.kit_part_number IS NULL
AND items.type_name not in ( 'Discount', 'Non-inventory Item', 'Other Charge', 'Service')
AND ( transactions.trandate => '2019-04-01 00:00:00'
AND transactions.trandate <= '2019-04-31 23:59:59' )
GROUP BY items.name,
items.salesdescription,
brand_code.description,
character_code.description,
sales_type_code.description,
customers.name,
customers.companyname,
countries.name
ORDER BY 1