We are dealing in different type of currency in day to day business transaction. I am facing issue in adempiere to get reporting in source currency. My vendor or customer with whom I deal is purely in foreign currency so but my accounting entries is been done in my local currency but if want to see ledger of vendors in source currency how do I get it? please help me
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
HI,
I have maybe same issue like you do. I have some clients for whom I made invoices in EUR. I wanted to see some reports in both EUR and in our currency. So I made some changes in views that gives me reports considering Invoices so now I have reports about invoices in currency that was made in any currency and another column that gives me amounts in our currency.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
CREATE OR REPLACE VIEW rv_c_invoice AS
SELECT i.c_invoice_id, i.ad_client_id, i.ad_org_id, i.isactive, i.created, i.createdby, i.updated, i.updatedby, i.issotrx, i.documentno, i.docstatus, i.docaction, i.isprinted, i.isdiscountprinted, i.processing, i.processed, i.istransferred, i.ispaid, i.c_doctype_id, i.c_doctypetarget_id, i.c_order_id, i.description, i.isapproved, i.salesrep_id, i.dateinvoiced, i.dateprinted, i.dateacct, i.c_bpartner_id, i.c_bpartner_location_id, i.ad_user_id, b.c_bp_group_id, i.poreference, i.dateordered, i.c_currency_id, i.c_conversiontype_id, i.paymentrule, i.c_paymentterm_id, i.m_pricelist_id, i.c_campaign_id, i.c_project_id, i.c_activity_id, i.ispayschedulevalid, i.invoicecollectiontype, loc.c_country_id, loc.c_region_id, loc.postal, loc.city, i.c_charge_id,
CASE
WHEN charat(d.docbasetype::character varying, 3)::text = 'C'::text THEN i.chargeamt * (-1)::numeric
ELSE i.chargeamt
END AS chargeamt,
CASE
WHEN charat(d.docbasetype::character varying, 3)::text = 'C'::text THEN i.totallines * (-1)::numeric
ELSE i.totallines
END AS totallines,
CASE
WHEN charat(d.docbasetype::character varying, 3)::text = 'C'::text THEN i.grandtotal * (-1)::numeric
ELSE i.grandtotal
END AS grandtotal,
CASE
WHEN charat(d.docbasetype::character varying, 3)::text = 'C'::text THEN (-1)
ELSE 1
END AS multiplier,
CASE
WHEN i.c_currency_id = acc.c_currency_id THEN i.totallines
ELSE i.totallines * cr.multiplyrate
END AS totallines2,
CASE
WHEN i.c_currency_id = acc.c_currency_id THEN i.grandtotal
ELSE i.grandtotal * cr.multiplyrate
END AS grandtotal2, cr.multiplyrate
FROM c_invoice i
JOIN c_doctype d ON i.c_doctype_id = d.c_doctype_id
JOIN c_bpartner b ON i.c_bpartner_id = b.c_bpartner_id
LEFT JOIN c_conversion_rate cr ON i.c_conversiontype_id = cr.c_conversiontype_id AND i.c_currency_id = cr.c_currency_id AND i.dateacct >= cr.validfrom AND i.dateacct <= cr.validto
JOIN c_acctschema acc ON i.ad_client_id = acc.ad_client_id
JOIN c_bpartner_location bpl ON i.c_bpartner_location_id = bpl.c_bpartner_location_id
JOIN c_location loc ON bpl.c_location_id = loc.c_location_id;
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi,
We are dealing in different type of currency in day to day business transaction. I am facing issue in adempiere to get reporting in source currency. My vendor or customer with whom I deal is purely in foreign currency so but my accounting entries is been done in my local currency but if want to see ledger of vendors in source currency how do I get it? please help me
HI,
I have maybe same issue like you do. I have some clients for whom I made invoices in EUR. I wanted to see some reports in both EUR and in our currency. So I made some changes in views that gives me reports considering Invoices so now I have reports about invoices in currency that was made in any currency and another column that gives me amounts in our currency.
This is how look like rv_c_invoice after changes:
CREATE OR REPLACE VIEW rv_c_invoice AS
SELECT i.c_invoice_id, i.ad_client_id, i.ad_org_id, i.isactive, i.created, i.createdby, i.updated, i.updatedby, i.issotrx, i.documentno, i.docstatus, i.docaction, i.isprinted, i.isdiscountprinted, i.processing, i.processed, i.istransferred, i.ispaid, i.c_doctype_id, i.c_doctypetarget_id, i.c_order_id, i.description, i.isapproved, i.salesrep_id, i.dateinvoiced, i.dateprinted, i.dateacct, i.c_bpartner_id, i.c_bpartner_location_id, i.ad_user_id, b.c_bp_group_id, i.poreference, i.dateordered, i.c_currency_id, i.c_conversiontype_id, i.paymentrule, i.c_paymentterm_id, i.m_pricelist_id, i.c_campaign_id, i.c_project_id, i.c_activity_id, i.ispayschedulevalid, i.invoicecollectiontype, loc.c_country_id, loc.c_region_id, loc.postal, loc.city, i.c_charge_id,
CASE
WHEN charat(d.docbasetype::character varying, 3)::text = 'C'::text THEN i.chargeamt * (-1)::numeric
ELSE i.chargeamt
END AS chargeamt,
CASE
WHEN charat(d.docbasetype::character varying, 3)::text = 'C'::text THEN i.totallines * (-1)::numeric
ELSE i.totallines
END AS totallines,
CASE
WHEN charat(d.docbasetype::character varying, 3)::text = 'C'::text THEN i.grandtotal * (-1)::numeric
ELSE i.grandtotal
END AS grandtotal,
CASE
WHEN charat(d.docbasetype::character varying, 3)::text = 'C'::text THEN (-1)
ELSE 1
END AS multiplier,
CASE
WHEN i.c_currency_id = acc.c_currency_id THEN i.totallines
ELSE i.totallines * cr.multiplyrate
END AS totallines2,
CASE
WHEN i.c_currency_id = acc.c_currency_id THEN i.grandtotal
ELSE i.grandtotal * cr.multiplyrate
END AS grandtotal2, cr.multiplyrate
FROM c_invoice i
JOIN c_doctype d ON i.c_doctype_id = d.c_doctype_id
JOIN c_bpartner b ON i.c_bpartner_id = b.c_bpartner_id
LEFT JOIN c_conversion_rate cr ON i.c_conversiontype_id = cr.c_conversiontype_id AND i.c_currency_id = cr.c_currency_id AND i.dateacct >= cr.validfrom AND i.dateacct <= cr.validto
JOIN c_acctschema acc ON i.ad_client_id = acc.ad_client_id
JOIN c_bpartner_location bpl ON i.c_bpartner_location_id = bpl.c_bpartner_location_id
JOIN c_location loc ON bpl.c_location_id = loc.c_location_id;