in function getWholeStatement, when encounter below view, program will can't separate it as a view.
function isQuoted always return true.
issue relate $act"......bs$act"
CREATE VIEW rv_fact_adaxa AS
SELECT f.ad_client_id,
f.ad_org_id,
( SELECT cli.name
FROM ad_client cli
WHERE (cli.ad_client_id = f.ad_client_id)) AS clientname,
f.dateacct,
CASE
WHEN (f.dateacct < ( SELECT clinfo.fy_startdate
FROM ad_clientinfo clinfo
WHERE (clinfo.ad_client_id = f.ad_client_id))) THEN (( SELECT ((clinfo.fy_startdate)::timestamp with time zone - (1)::numeric)
FROM ad_clientinfo clinfo
WHERE (clinfo.ad_client_id = f.ad_client_id)))::timestamp without time zone
ELSE f.dateacct
END AS bsdate,
CASE
WHEN (f.dateacct < ( SELECT clinfo.fy_startdate
FROM ad_clientinfo clinfo
WHERE (clinfo.ad_client_id = f.ad_client_id))) THEN ( SELECT to_char((((clinfo.fy_startdate)::timestamp with time zone - (1)::numeric))::timestamp with time zone, 'YYYY-MM'::text) AS to_char
FROM ad_clientinfo clinfo
WHERE (clinfo.ad_client_id = f.ad_client_id))
ELSE to_char(f.dateacct, 'YYYY-MM'::text)
END AS bsmth,
to_char((((f.dateacct)::timestamp with time zone + (184)::numeric))::timestamp with time zone, '"FY"YYYY'::text) AS fyear,
to_char(f.dateacct, 'YYYY-MM'::text) AS fymth,
( SELECT t.name
FROM ad_table t
WHERE (t.ad_table_id = f.ad_table_id)) AS table_name,
f.record_id,
( SELECT gl.name
FROM gl_budget gl
WHERE (gl.gl_budget_id = f.gl_budget_id)) AS budname,
( SELECT tax.name
FROM c_tax tax
WHERE (tax.c_tax_id = f.c_tax_id)) AS taxname,
CASE f.postingtype
WHEN 'A'::bpchar THEN 'Actual'::text
WHEN 'B'::bpchar THEN 'Budget'::text
WHEN 'E'::bpchar THEN 'Encumbrance'::text
WHEN 'S'::bpchar THEN 'Statistic'::text
ELSE 'Other'::text
END AS act_bud,
( SELECT c.iso_code
FROM c_currency c
WHERE (c.c_currency_id = f.c_currency_id)) AS currency,
(f.amtsourcedr - f.amtsourcecr) AS amtsce,
(f.amtacctdr - f.amtacctcr) AS amtacct,
CASE
WHEN (f.postingtype = 'B'::bpchar) THEN (0)::numeric
ELSE (f.amtacctcr - f.amtacctdr)
END AS "pl$act",
CASE
WHEN (f.postingtype = 'B'::bpchar) THEN (0)::numeric
WHEN (ev.accounttype = 'A'::bpchar) THEN (f.amtacctdr - f.amtacctcr)
WHEN (ev.accounttype = 'L'::bpchar) THEN (f.amtacctdr - f.amtacctcr)
WHEN (ev.accounttype = 'O'::bpchar) THEN (f.amtacctcr - f.amtacctdr)
WHEN (ev.accounttype = 'E'::bpchar) THEN (f.amtacctcr - f.amtacctdr)
WHEN (ev.accounttype = 'R'::bpchar) THEN (f.amtacctcr - f.amtacctdr)
ELSE (0)::numeric
END AS "bs$act",
CASE
WHEN (f.postingtype = 'A'::bpchar) THEN (0)::numeric
WHEN (ev.accounttype = 'E'::bpchar) THEN (f.amtacctcr - f.amtacctdr)
WHEN (ev.accounttype = 'R'::bpchar) THEN (f.amtacctcr - f.amtacctdr)
ELSE (0)::numeric
END AS "pl$bud",
( SELECT um.uomsymbol
FROM c_uom um
WHERE (um.c_uom_id = f.c_uom_id)) AS uomname,
f.qty,
( SELECT prj.name
FROM c_project prj
WHERE (prj.c_project_id = f.c_project_id)) AS projectname,
f.description,
o.name AS orgname,
ev.value AS ac_code,
ev.name AS ac_name,
(((ev.value)::text || '-'::text) || (ev.name)::text) AS account_name,
CASE ev.accounttype
WHEN 'A'::bpchar THEN '1 Asset'::text
WHEN 'E'::bpchar THEN '3 Equity'::text
WHEN 'L'::bpchar THEN '2 Liability'::text
WHEN 'M'::bpchar THEN '9 Memo'::text
WHEN 'O'::bpchar THEN '3 Equity'::text
WHEN 'R'::bpchar THEN '3 Equity'::text
ELSE '9. Unknown'::text
END AS bstypename,
CASE ev.accounttype
WHEN 'A'::bpchar THEN '9 BSheet'::text
WHEN 'E'::bpchar THEN '6 Expense'::text
WHEN 'L'::bpchar THEN '9 BSheet'::text
WHEN 'M'::bpchar THEN '9 Memo'::text
WHEN 'O'::bpchar THEN '9 BSheet'::text
WHEN 'R'::bpchar THEN '4 Revenue'::text
ELSE '9. Unknown'::text
END AS pltypename,
( SELECT (((ss.value)::text || '-'::text) || (ss.name)::text)
FROM c_elementvalue ss
WHERE ((((ss.value)::text = substr((ev.value)::text, 1, 1)) AND (ss.issummary = 'Y'::bpchar)) AND (ss.ad_client_id = f.ad_client_id))) AS ac_group,
CASE ev.accounttype
WHEN 'A'::bpchar THEN ( SELECT (((ss.value)::text || '-'::text) || (ss.name)::text)
FROM c_elementvalue ss
WHERE ((((ss.value)::text = substr((ev.value)::text, 1, 3)) AND (ss.issummary = 'Y'::bpchar)) AND (ss.ad_client_id = f.ad_client_id)))
WHEN 'E'::bpchar THEN '330-Current P and L Accounts Balance'::text
WHEN 'L'::bpchar THEN ( SELECT (((ss.value)::text || '-'::text) || (ss.name)::text)
FROM c_elementvalue ss
WHERE ((((ss.value)::text = substr((ev.value)::text, 1, 3)) AND (ss.issummary = 'Y'::bpchar)) AND (ss.ad_client_id = f.ad_client_id)))
WHEN 'O'::bpchar THEN ( SELECT (((ss.value)::text || '-'::text) || (ss.name)::text)
FROM c_elementvalue ss
WHERE ((((ss.value)::text = substr((ev.value)::text, 1, 3)) AND (ss.issummary = 'Y'::bpchar)) AND (ss.ad_client_id = f.ad_client_id)))
WHEN 'R'::bpchar THEN '330-Current P and L Accounts Balance'::text
ELSE 'Unknown Account Type'::text
END AS bsgroup,
CASE ev.accounttype
WHEN 'A'::bpchar THEN '999-Balance Sheet Item'::text
WHEN 'E'::bpchar THEN ( SELECT (((ss.value)::text || '-'::text) || (ss.name)::text)
FROM c_elementvalue ss
WHERE ((((ss.value)::text = substr((ev.value)::text, 1, 3)) AND (ss.issummary = 'Y'::bpchar)) AND (ss.ad_client_id = f.ad_client_id)))
WHEN 'L'::bpchar THEN '999-Balance Sheet Item'::text
WHEN 'O'::bpchar THEN '999-Balance Sheet Item'::text
WHEN 'R'::bpchar THEN ( SELECT (((ss.value)::text || '-'::text) || (ss.name)::text)
FROM c_elementvalue ss
WHERE ((((ss.value)::text = substr((ev.value)::text, 1, 3)) AND (ss.issummary = 'Y'::bpchar)) AND (ss.ad_client_id = f.ad_client_id)))
ELSE 'Unknown Account Type'::text
END AS plgroup,
CASE ev.accounttype
WHEN 'A'::bpchar THEN 'BS'::text
WHEN 'E'::bpchar THEN 'PL'::text
WHEN 'L'::bpchar THEN 'BS'::text
WHEN 'M'::bpchar THEN 'Unknown Account Type'::text
WHEN 'O'::bpchar THEN 'BS'::text
WHEN 'R'::bpchar THEN 'PL'::text
ELSE 'Unknown Account Type'::text
END AS plbs,
CASE ev.accounttype
WHEN 'A'::bpchar THEN '1 NetAssets'::text
WHEN 'E'::bpchar THEN '2 Equity'::text
WHEN 'L'::bpchar THEN '1 NetAssets'::text
WHEN 'M'::bpchar THEN '9 Memo'::text
WHEN 'O'::bpchar THEN '2 Equity'::text
WHEN 'R'::bpchar THEN '2 Equity'::text
ELSE 'Unknown Account Type'::text
END AS na_eq,
bp.value AS bpcode,
bp.name AS bpname,
CASE
WHEN (bp.name IS NULL) THEN NULL::text
ELSE (((bp.value)::text || '-'::text) || (bp.name)::text)
END AS bpartnercodeandname,
( SELECT bpg.name
FROM c_bp_group bpg
WHERE (bpg.c_bp_group_id = bp.c_bp_group_id)) AS bpgroupname,
( SELECT ad_user.name
FROM ad_user
WHERE (ad_user.ad_user_id = bp.salesrep_id)) AS repname,
p.value AS prodcode,
p.name AS prodname,
CASE
WHEN (p.name IS NULL) THEN NULL::text
ELSE (((p.value)::text || '-'::text) || (p.name)::text)
END AS product,
( SELECT pc.name
FROM m_product_category pc
WHERE (pc.m_product_category_id = p.m_product_category_id)) AS prodcategory
FROM ((((fact_acct f
JOIN ad_org o ON ((f.ad_org_id = o.ad_org_id)))
JOIN c_elementvalue ev ON ((f.account_id = ev.c_elementvalue_id)))
LEFT JOIN c_bpartner bp ON ((f.c_bpartner_id = bp.c_bpartner_id)))
LEFT JOIN m_product p ON ((f.m_product_id = p.m_product_id)));
ALTER TABLE adempiere.rv_fact_adaxa OWNER TO adempiere;
avbasov
commented
8 years ago
in function getWholeStatement, when encounter below view, program will can't separate it as a view. function isQuoted always return true. issue relate $act"......bs$act"
CREATE VIEW rv_fact_adaxa AS SELECT f.ad_client_id, f.ad_org_id, ( SELECT cli.name FROM ad_client cli WHERE (cli.ad_client_id = f.ad_client_id)) AS clientname, f.dateacct, CASE WHEN (f.dateacct < ( SELECT clinfo.fy_startdate FROM ad_clientinfo clinfo WHERE (clinfo.ad_client_id = f.ad_client_id))) THEN (( SELECT ((clinfo.fy_startdate)::timestamp with time zone - (1)::numeric) FROM ad_clientinfo clinfo WHERE (clinfo.ad_client_id = f.ad_client_id)))::timestamp without time zone ELSE f.dateacct END AS bsdate, CASE WHEN (f.dateacct < ( SELECT clinfo.fy_startdate FROM ad_clientinfo clinfo WHERE (clinfo.ad_client_id = f.ad_client_id))) THEN ( SELECT to_char((((clinfo.fy_startdate)::timestamp with time zone - (1)::numeric))::timestamp with time zone, 'YYYY-MM'::text) AS to_char FROM ad_clientinfo clinfo WHERE (clinfo.ad_client_id = f.ad_client_id)) ELSE to_char(f.dateacct, 'YYYY-MM'::text) END AS bsmth, to_char((((f.dateacct)::timestamp with time zone + (184)::numeric))::timestamp with time zone, '"FY"YYYY'::text) AS fyear, to_char(f.dateacct, 'YYYY-MM'::text) AS fymth, ( SELECT t.name FROM ad_table t WHERE (t.ad_table_id = f.ad_table_id)) AS table_name, f.record_id, ( SELECT gl.name FROM gl_budget gl WHERE (gl.gl_budget_id = f.gl_budget_id)) AS budname, ( SELECT tax.name FROM c_tax tax WHERE (tax.c_tax_id = f.c_tax_id)) AS taxname, CASE f.postingtype WHEN 'A'::bpchar THEN 'Actual'::text WHEN 'B'::bpchar THEN 'Budget'::text WHEN 'E'::bpchar THEN 'Encumbrance'::text WHEN 'S'::bpchar THEN 'Statistic'::text ELSE 'Other'::text END AS act_bud, ( SELECT c.iso_code FROM c_currency c WHERE (c.c_currency_id = f.c_currency_id)) AS currency, (f.amtsourcedr - f.amtsourcecr) AS amtsce, (f.amtacctdr - f.amtacctcr) AS amtacct, CASE WHEN (f.postingtype = 'B'::bpchar) THEN (0)::numeric ELSE (f.amtacctcr - f.amtacctdr) END AS "pl$act", CASE WHEN (f.postingtype = 'B'::bpchar) THEN (0)::numeric WHEN (ev.accounttype = 'A'::bpchar) THEN (f.amtacctdr - f.amtacctcr) WHEN (ev.accounttype = 'L'::bpchar) THEN (f.amtacctdr - f.amtacctcr) WHEN (ev.accounttype = 'O'::bpchar) THEN (f.amtacctcr - f.amtacctdr) WHEN (ev.accounttype = 'E'::bpchar) THEN (f.amtacctcr - f.amtacctdr) WHEN (ev.accounttype = 'R'::bpchar) THEN (f.amtacctcr - f.amtacctdr) ELSE (0)::numeric END AS "bs$act", CASE WHEN (f.postingtype = 'A'::bpchar) THEN (0)::numeric WHEN (ev.accounttype = 'E'::bpchar) THEN (f.amtacctcr - f.amtacctdr) WHEN (ev.accounttype = 'R'::bpchar) THEN (f.amtacctcr - f.amtacctdr) ELSE (0)::numeric END AS "pl$bud", ( SELECT um.uomsymbol FROM c_uom um WHERE (um.c_uom_id = f.c_uom_id)) AS uomname, f.qty, ( SELECT prj.name FROM c_project prj WHERE (prj.c_project_id = f.c_project_id)) AS projectname, f.description, o.name AS orgname, ev.value AS ac_code, ev.name AS ac_name, (((ev.value)::text || '-'::text) || (ev.name)::text) AS account_name, CASE ev.accounttype WHEN 'A'::bpchar THEN '1 Asset'::text WHEN 'E'::bpchar THEN '3 Equity'::text WHEN 'L'::bpchar THEN '2 Liability'::text WHEN 'M'::bpchar THEN '9 Memo'::text WHEN 'O'::bpchar THEN '3 Equity'::text WHEN 'R'::bpchar THEN '3 Equity'::text ELSE '9. Unknown'::text END AS bstypename, CASE ev.accounttype WHEN 'A'::bpchar THEN '9 BSheet'::text WHEN 'E'::bpchar THEN '6 Expense'::text WHEN 'L'::bpchar THEN '9 BSheet'::text WHEN 'M'::bpchar THEN '9 Memo'::text WHEN 'O'::bpchar THEN '9 BSheet'::text WHEN 'R'::bpchar THEN '4 Revenue'::text ELSE '9. Unknown'::text END AS pltypename, ( SELECT (((ss.value)::text || '-'::text) || (ss.name)::text) FROM c_elementvalue ss WHERE ((((ss.value)::text = substr((ev.value)::text, 1, 1)) AND (ss.issummary = 'Y'::bpchar)) AND (ss.ad_client_id = f.ad_client_id))) AS ac_group, CASE ev.accounttype WHEN 'A'::bpchar THEN ( SELECT (((ss.value)::text || '-'::text) || (ss.name)::text) FROM c_elementvalue ss WHERE ((((ss.value)::text = substr((ev.value)::text, 1, 3)) AND (ss.issummary = 'Y'::bpchar)) AND (ss.ad_client_id = f.ad_client_id))) WHEN 'E'::bpchar THEN '330-Current P and L Accounts Balance'::text WHEN 'L'::bpchar THEN ( SELECT (((ss.value)::text || '-'::text) || (ss.name)::text) FROM c_elementvalue ss WHERE ((((ss.value)::text = substr((ev.value)::text, 1, 3)) AND (ss.issummary = 'Y'::bpchar)) AND (ss.ad_client_id = f.ad_client_id))) WHEN 'O'::bpchar THEN ( SELECT (((ss.value)::text || '-'::text) || (ss.name)::text) FROM c_elementvalue ss WHERE ((((ss.value)::text = substr((ev.value)::text, 1, 3)) AND (ss.issummary = 'Y'::bpchar)) AND (ss.ad_client_id = f.ad_client_id))) WHEN 'R'::bpchar THEN '330-Current P and L Accounts Balance'::text ELSE 'Unknown Account Type'::text END AS bsgroup, CASE ev.accounttype WHEN 'A'::bpchar THEN '999-Balance Sheet Item'::text WHEN 'E'::bpchar THEN ( SELECT (((ss.value)::text || '-'::text) || (ss.name)::text) FROM c_elementvalue ss WHERE ((((ss.value)::text = substr((ev.value)::text, 1, 3)) AND (ss.issummary = 'Y'::bpchar)) AND (ss.ad_client_id = f.ad_client_id))) WHEN 'L'::bpchar THEN '999-Balance Sheet Item'::text WHEN 'O'::bpchar THEN '999-Balance Sheet Item'::text WHEN 'R'::bpchar THEN ( SELECT (((ss.value)::text || '-'::text) || (ss.name)::text) FROM c_elementvalue ss WHERE ((((ss.value)::text = substr((ev.value)::text, 1, 3)) AND (ss.issummary = 'Y'::bpchar)) AND (ss.ad_client_id = f.ad_client_id))) ELSE 'Unknown Account Type'::text END AS plgroup, CASE ev.accounttype WHEN 'A'::bpchar THEN 'BS'::text WHEN 'E'::bpchar THEN 'PL'::text WHEN 'L'::bpchar THEN 'BS'::text WHEN 'M'::bpchar THEN 'Unknown Account Type'::text WHEN 'O'::bpchar THEN 'BS'::text WHEN 'R'::bpchar THEN 'PL'::text ELSE 'Unknown Account Type'::text END AS plbs, CASE ev.accounttype WHEN 'A'::bpchar THEN '1 NetAssets'::text WHEN 'E'::bpchar THEN '2 Equity'::text WHEN 'L'::bpchar THEN '1 NetAssets'::text WHEN 'M'::bpchar THEN '9 Memo'::text WHEN 'O'::bpchar THEN '2 Equity'::text WHEN 'R'::bpchar THEN '2 Equity'::text ELSE 'Unknown Account Type'::text END AS na_eq, bp.value AS bpcode, bp.name AS bpname, CASE WHEN (bp.name IS NULL) THEN NULL::text ELSE (((bp.value)::text || '-'::text) || (bp.name)::text) END AS bpartnercodeandname, ( SELECT bpg.name FROM c_bp_group bpg WHERE (bpg.c_bp_group_id = bp.c_bp_group_id)) AS bpgroupname, ( SELECT ad_user.name FROM ad_user WHERE (ad_user.ad_user_id = bp.salesrep_id)) AS repname, p.value AS prodcode, p.name AS prodname, CASE WHEN (p.name IS NULL) THEN NULL::text ELSE (((p.value)::text || '-'::text) || (p.name)::text) END AS product, ( SELECT pc.name FROM m_product_category pc WHERE (pc.m_product_category_id = p.m_product_category_id)) AS prodcategory FROM ((((fact_acct f JOIN ad_org o ON ((f.ad_org_id = o.ad_org_id))) JOIN c_elementvalue ev ON ((f.account_id = ev.c_elementvalue_id))) LEFT JOIN c_bpartner bp ON ((f.c_bpartner_id = bp.c_bpartner_id))) LEFT JOIN m_product p ON ((f.m_product_id = p.m_product_id)));
ALTER TABLE adempiere.rv_fact_adaxa OWNER TO adempiere;