Hi All,
I have the below SQL statement which when I run in the SQL query tool, it works.
But when I save in Quick reports, it returns an error. What is the correct format in quick reports?
SELECT
p.project_id AS "Project ID",
p.name as "Project Name",
TO_CHAR((select SUM(a.Amount_Balance) * -1
from ACCOUNTING_BALANCE_AUTH a
where p.Project_Id = a.Code_F
and a.Account IN ('3011', '3014')
and a.year_period_key <= NVL('&Year_Period', '204012')), '999,999,999,999') AS "Revenue to Date (RTD)",
TO_CHAR(SUM(b.Net_Amount), '999,999,999,999') AS "Invoiced to Date (ITD)",
TO_CHAR((select SUM(a.Amount_Balance) * -1
from ACCOUNTING_BALANCE_AUTH a
where p.Project_Id = a.Code_F
and a.Account IN ('3011', '3014')
and a.year_period_key <= NVL('&Year_Period', '204012')) - SUM(b.Net_Amount), '999,999,999,999') as "(Under)/Over Claimed",
TO_CHAR(SUM(b.invoice_amount), '999,999,999,999') as "Cash Received (CR)",
TO_CHAR((select SUM(a.Amount_Balance)
from ACCOUNTING_BALANCE_AUTH a
where p.Project_Id = a.Code_F
and a.Account IN ('4000', '4001')
and a.year_period_key <= NVL('&Year_Period', '204012')), '999,999,999,999') as "Cost To Date (CTD)",
TO_CHAR(SUM(b.invoice_amount) - (select SUM(a.Amount_Balance)
from ACCOUNTING_BALANCE_AUTH a
where p.Project_Id = a.Code_F
and a.Account IN ('4000', '4001')
and a.year_period_key <= NVL('&Year_Period', '204012')), '999,999,999,999') as "Under/Over Claim (CR less CTD)"
FROM PROJECT p
LEFT JOIN INVOICE_LEDGER_ITEM_CU_QRY_cfv b
ON p.project_id = b.CF$_PROJECT_ID_NM
WHERE
p.OBJSTATE <> (SELECT PROJECT_API.FINITE_STATE_ENCODE__('Closed') FROM dual)
AND p.OBJSTATE <> (SELECT PROJECT_API.FINITE_STATE_ENCODE__('Cancelled') FROM dual)
AND p.OBJSTATE <> (SELECT PROJECT_API.FINITE_STATE_ENCODE__('Completed') FROM dual)
AND upper(b.INV_STATE) <> upper('Cancelled')
AND p.company <> '30'
AND upper(b.INV_STATE) = upper('PaidPosted')
AND b.invoice_date <= NVL(TO_DATE('&Invoice_Date', 'YYYYMMDD'), TO_DATE('20401231', 'YYYYMMDD'))
GROUP BY p.project_id, p.name
ORDER BY p.project_id;