write a query for return on sales using Sap Hana tables. External tools to pull the…
- User
write a query for return on sales using Sap Hana tables. External tools to pull the tables & fields used. Modules include PP, CO, MM, and FI. Think through this step by step. Output a Hana sql query. Verify correct fields and tables using search.
Assistant
I'll work through this step by step to create a Return on Sales query using SAP HANA tables.
Step 1: Understanding Return on Sales
Return on Sales (ROS) = Net Income / Net Sales Revenue This requires data from:
- Sales revenue (from SD/FI modules)
- Cost of goods sold (from CO/MM modules)
- Operating expenses (from FI/CO modules)
Step 2: Key SAP Tables and Fields Needed
Sales Data (SD/FI):
- VBRK (Billing Document Header) - VBELN, FKDAT, NETWR
- VBRP (Billing Document Item) - VBELN, POSNR, NETWR, MATNR
- BSEG (Accounting Document Segment) - BUKRS, GJAHR, BELNR, WRBTR
Cost Data (CO/MM):
- COEP (CO Object Line Items) - OBJNR, GJAHR, WRTTP, WOGBTR
- COSS (Cost Center Line Items) - KOSTL, GJAHR, WOGBTR
- MBEW (Material Valuation) - MATNR, BWKEY, STPRS, VERPR
Financial Data (FI):
- BKPF (Accounting Document Header) - BUKRS, GJAHR, BELNR
- SKA1 (G/L Account Master) - SAKNR, BILKT
Step 3: SAP HANA SQL Query
WITH sales_revenue AS ( SELECT v.BUKRS as company_code, EXTRACT(YEAR FROM v.FKDAT) as fiscal_year, EXTRACT(MONTH FROM v.FKDAT) as fiscal_period, SUM(vp.NETWR) as gross_sales, SUM(CASE WHEN v.FKART IN ('F1', 'F2') THEN vp.NETWR ELSE 0 END) as net_sales FROM VBRK v INNER JOIN VBRP vp ON v.VBELN = vp.VBELN WHERE v.FKDAT >= '2023-01-01' AND v.FKSTO = '' -- Not cancelled AND vp.NETWR > 0 GROUP BY v.BUKRS, EXTRACT(YEAR FROM v.FKDAT), EXTRACT(MONTH FROM v.FKDAT) ), cost_of_sales AS ( SELECT c.BUKRS as company_code, c.GJAHR as fiscal_year, c.PERIO as fiscal_period, SUM(CASE WHEN c.WRTTP = '04' THEN c.WOGBTR ELSE 0 END) as cogs, SUM(CASE WHEN c.WRTTP = '11' THEN c.WOGBTR ELSE 0 END) as material_costs FROM COEP c INNER JOIN SKA1 s ON c.SAKNR = s.SAKNR WHERE c.GJAHR >= 2023 AND s.BILKT = 'X' -- Balance sheet account AND c.WOGBTR != 0 GROUP BY c.BUKRS, c.GJAHR, c.PERIO ), operating_expenses AS ( SELECT b.BUKRS as company_code, b.GJAHR as fiscal_year, EXTRACT(MONTH FROM b.BUDAT) as fiscal_period, SUM(ABS(bg.WRBTR)) as total_opex FROM BKPF b INNER JOIN BSEG bg ON b.BUKRS = bg.BUKRS AND b.BELNR = bg.BELNR AND b.GJAHR = bg.GJAHR INNER JOIN SKA1 s ON bg.SAKNR = s.SAKNR WHERE b.GJAHR >= 2023 AND bg.SAKNR BETWEEN '600000' AND '699999' -- Operating expense accounts AND b.BLART IN ('SA', 'KR', 'RE') -- Relevant document types AND bg.WRBTR < 0 -- Expense entries GROUP BY b.BUKRS, b.GJAHR, EXTRACT(MONTH FROM b.BUDAT) ) SELECT sr.company_code, sr.fiscal_year, sr.fiscal_period, sr.net_sales, COALESCE(cs.cogs, 0) + COALESCE(cs.material_costs, 0) as total_cogs, COALESCE(oe.total_opex, 0) as operating_expenses, sr.net_sales - COALESCE(cs.cogs, 0) - COALESCE(cs.material_costs, 0) - COALESCE(oe.total_opex, 0) as net_income, CASE WHEN sr.net_sales > 0 THEN ROUND(((sr.net_sales - COALESCE(cs.cogs, 0) - COALESCE(cs.material_costs, 0) - COALESCE(oe.total_opex, 0)) / sr.net_sales) * 100, 2) ELSE 0 END as return_on_sales_percent FROM sales_revenue sr LEFT JOIN cost_of_sales cs ON sr.company_code = cs.company_code AND sr.fiscal_year = cs.fiscal_year AND sr.fiscal_period = cs.fiscal_period LEFT JOIN operating_expenses oe ON sr.company_code = oe.company_code AND sr.fiscal_year = oe.fiscal_year AND sr.fiscal_period = oe.fiscal_period WHERE sr.net_sales > 0 ORDER BY sr.company_code, sr.fiscal_year, sr.fiscal_period;Key Features of this Query:
Sales Revenue: Pulls from VBRK/VBRP billing documents, filtering for actual sales (not cancelled)
Cost of Sales: Uses COEP for CO line items with different value types (WRTTP) to separate COGS and material costs
Operating Expenses: Extracts from BSEG/BKPF using G/L account ranges typically used for operating expenses
Return on Sales Calculation: (Net Sales - COGS - Operating Expenses) / Net Sales * 100
The query provides monthly ROS percentages by company code and can be easily modified for different time periods or organizational breakdowns.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.