summaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
authoreinhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>2008-11-12 17:49:51 +0000
committereinhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>2008-11-12 17:49:51 +0000
commitf7ae08a1336c4667e2fb4028d034a52fda7f492b (patch)
tree17377a6d1c01a829a70df293e1cc36bbe3284a2d /sql
parentd7080538940d0f9ca89e1b9635d0f92568771261 (diff)
Updating the billing info function
git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@2391 4979c152-3d1c-0410-bac9-87ea11338e46
Diffstat (limited to 'sql')
-rw-r--r--sql/modules/Company.sql34
1 files changed, 8 insertions, 26 deletions
diff --git a/sql/modules/Company.sql b/sql/modules/Company.sql
index c77d37ca..e630a6a2 100644
--- a/sql/modules/Company.sql
+++ b/sql/modules/Company.sql
@@ -257,6 +257,7 @@ $$ language plpgsql;
CREATE TYPE company_billing_info AS (
legal_name text,
meta_number text,
+control_code text,
tax_id text,
street1 text,
street2 text,
@@ -273,15 +274,16 @@ $$
DECLARE out_var company_billing_info;
t_id INT;
BEGIN
- select c.legal_name, eca.meta_number, c.tax_id, a.line_one, a.line_two, a.line_three,
+ select c.legal_name, eca.meta_number, e.control_code, c.tax_id, a.line_one, a.line_two, a.line_three,
a.city, a.state, a.mail_code, cc.name
into out_var
FROM company c
- JOIN entity_credit_account eca ON (eca.entity_id = c.entity_id)
- JOIN eca_to_location cl ON (eca.id = cl.credit_id)
- JOIN location a ON (a.id = cl.location_id)
- JOIN country cc ON (cc.id = a.country_id)
- WHERE eca.id = in_id AND location_class = 1;
+ JOIN entity e ON (c.entity_id = e.id)
+ JOIN entity_credit_account eca ON (eca.entity_id = e.id)
+ LEFT JOIN eca_to_location cl ON (eca.id = cl.credit_id)
+ LEFT JOIN location a ON (a.id = cl.location_id)
+ LEFT JOIN country cc ON (cc.id = a.country_id)
+ WHERE eca.id = in_id AND (location_class = 1 or location_class is null);
RETURN out_var;
END;
@@ -681,26 +683,6 @@ create or replace function eca__location_save(
$$ language 'plpgsql';
-CREATE OR REPLACE FUNCTION company_get_billing_info (in_id int)
-returns company_billing_info as
-$$
-DECLARE out_var company_billing_info;
- t_id INT;
-BEGIN
- select c.legal_name, c.tax_id, a.line_one, a.line_two, a.line_three,
- a.city, a.state, a.mail_code, cc.name
- into out_var
- FROM company c
- JOIN entity_credit_account eca ON (eca.entity_id = c.entity_id)
- JOIN eca_to_location cl ON (eca.id = cl.credit_id)
- JOIN location a ON (a.id = cl.location_id)
- JOIN country cc ON (cc.id = a.country_id)
- WHERE eca.id = in_id AND location_class = 1;
-
- RETURN out_var;
-END;
-$$ language plpgsql;
-
CREATE OR REPLACE FUNCTION eca__list_locations(in_credit_id int)
RETURNS SETOF location_result AS
$$