pgsql/investigations/ar-cash.sql

[Pman.Xtuple] / pgsql / x-dragon-gl-precision.sql

-- not wide enough?


-- this was a mistake!!! - we should never had made gl 3 decimal places - it makes a nightmare everywhere..

alter table glseries alter column glseries_amount  TYPE numeric(12,2);
 --View: api.journalentry

DROP VIEW api.journalentry;
   alter table gltrans alter column gltrans_amount  TYPE numeric(12,2);

CREATE OR REPLACE VIEW api.journalentry AS 
 SELECT curr_symbol.curr_abbr AS currency, c.gltrans_amount AS amount, c.gltrans_date AS dist_date, c.gltrans_docnumber AS doc_number, formatglaccount(da.accnt_id) AS debit, formatglaccount(ca.accnt_id) AS credit, c.gltrans_notes AS notes
   FROM gltrans d, gltrans c, accnt da, accnt ca, curr_symbol
  WHERE d.gltrans_sequence = c.gltrans_sequence AND d.gltrans_accnt_id = da.accnt_id AND c.gltrans_accnt_id = ca.accnt_id AND d.gltrans_amount < 0::numeric AND c.gltrans_amount > 0::numeric AND d.gltrans_doctype = 'JE'::text AND c.gltrans_doctype = 'JE'::text AND curr_symbol.curr_id = basecurrid()
  ORDER BY d.gltrans_date DESC;

ALTER TABLE api.journalentry
  OWNER TO admin;
GRANT ALL ON TABLE api.journalentry TO admin;
GRANT ALL ON TABLE api.journalentry TO xtrole;
COMMENT ON VIEW api.journalentry
  IS 'Journal Entry';


-- Rule: "_DELETE" ON api.journalentry

-- DROP RULE "_DELETE" ON api.journalentry;

CREATE OR REPLACE RULE "_DELETE" AS
    ON DELETE TO api.journalentry DO INSTEAD NOTHING;

-- Rule: "_INSERT" ON api.journalentry

-- DROP RULE "_INSERT" ON api.journalentry;

CREATE OR REPLACE RULE "_INSERT" AS
    ON INSERT TO api.journalentry DO INSTEAD  SELECT insertgltransaction('G/L'::text, 'JE'::text, new.doc_number, new.notes, getglaccntid(new.credit), getglaccntid(new.debit), (-1), currtobase(getcurrid(new.currency::text), new.amount, new.dist_date), new.dist_date) AS insertgltransaction;

-- Rule: "_UPDATE" ON api.journalentry

-- DROP RULE "_UPDATE" ON api.journalentry;

CREATE OR REPLACE RULE "_UPDATE" AS
    ON UPDATE TO api.journalentry DO INSTEAD NOTHING;
--