Top Level Project Management Cost Query for SAP Business One
Here is a project cost and revenue report for all top level projects in SAP Business One. This reports basically presents the project summary tab in a report. This is an exceptional report to use with HANA Pervasive Analytics. The types of KPI's and Dashboards you can draw from this report is exceptional.
Simply copy this query and paste into the Query Generator!
SELECT
T0."AbsEntry" "Project",
T0."FIPROJECT" "FIN Project",
T0."NAME" "Project Name",
T0."START" "Scheduled Start",
T0."DUEDATE" "Scheduled End",
CASE
WHEN T0."STATUS" = 'S' THEN 'Project Started'
WHEN T0."STATUS" = 'T' THEN 'Project Stopped'
WHEN T0."STATUS" = 'P' THEN 'Project Paused'
WHEN T0."STATUS" = 'N' THEN 'Project Canceled'
WHEN T0."STATUS" = 'F' THEN 'Project Finished'
ELSE 'Status Unknown'
END AS "Project Status",
T0."CARDCODE" "Customer ID",
T0."CARDNAME" "Customer Name",
'|' " ",
T1."PhBudget" "Expense Estimate",
T1."OpenAmtAP" "Open AP",
T1."InvoicedAP" "Invoiced AP",
T1."TotalAP" "Total AP",
T1."TotVarAP" "AP Variance",
T1."VarPercAP" "AP Var. Pct",
'|' " ",
T1."PoPhAmt" "Sales Estimate",
T1."OpenAmtAR" "Open AR",
T1."InvoicedAR" "Invoiced AR",
T1."TotalAR" "Total AR",
T1."TotVarAR" "AR Variance",
T1."VarPercAR" "AR Var. Pct"
FROM OPMG T0 INNER JOIN PMG8 T1 ON T0."AbsEntry" = T1."AbsEntry"
ORDER BY T0."START"