Wiki Change:

omfgppc
2009-03-10
2013-03-08
  • omfgppc
    omfgppc
    2009-03-10

    You have subscribed to a wiki page or wiki category on "xTuple" for change =
    notification.

    The "EnhancedTaxInternationalization" page has been changed by jrogelstad:

      CREATE TABLE taxhist
      (
        taxhist_id serial PRIMARY KEY,
    -   taxhist_source character(2) NOT NULL,
    -   taxhist_source_id integer NOT NULL,
    +   taxhist_parent_id integer NOT NULL,
        taxhist_tax_id integer NOT NULL REFERENCES tax (tax_id),
        taxhist_basis numeric (16,2) NOT NULL,
        taxhist_basis_tax_id integer,
    @@ -437, +436 @@

      );
      COMMENT ON TABLE taxhist IS 'A table type to record tax transaction histo=
    ry. Inherited by other tables that actually record history. As the parent, =
    queries can be run against it that will join all child tables. ';
      COMMENT ON COLUMN taxhist.taxhist_id IS 'Primary key';
    - COMMENT ON COLUMN taxhist.taxhist_source IS 'Source type. Only one type s=
    hould be allowed per child table.';
    - COMMENT ON COLUMN taxhist.taxhist_source_id IS 'Source parent id.';
    + COMMENT ON COLUMN taxhist.taxhist_parent_id IS 'Source parent id.'; =

    - COMMENT ON COLUMN taxhist.taxhist_tax_id IS 'Tax code id.';
    + COMMENT ON COLUMN taxhist.taxhist_tax_id IS 'Tax code id.'; =

      COMMENT ON COLUMN taxhist.taxhist_basis IS 'Base price amount on which th=
    e tax calculation is based.';
      COMMENT ON COLUMN taxhist.taxhist_basis_tax_id IS 'Tax rate calculation b=
    asis.  If null, then the amount of the parent document, otherwise calculate=
    d on the result amount of the tax code id referenced.';
    - COMMENT ON COLUMN taxhist.taxhist_cum_taxhist_ids IS 'An array that conta=
    ins references to other tax hist records that were cumulatively added to th=
    e basis.';
    + COMMENT ON COLUMN taxhist.taxhist_cum_taxhist_ids IS 'An array that conta=
    ins references to other tax hist records that were cumulatively added to th=
    e basis.'; =

    - COMMENT ON COLUMN taxhist.taxhist_rate IS 'Tax rate percentage.';
    + COMMENT ON COLUMN taxhist.taxhist_rate IS 'Tax rate percentage.'; =

      COMMENT ON COLUMN taxhist.taxhist_amount IS 'Tax amount transacted';
      COMMENT ON COLUMN taxhist.taxhist_docdate IS 'The date of the parent docu=
    ment.';
      COMMENT ON COLUMN taxhist.taxhist_docdate IS 'The G/L distribution date o=
    f the parent document.';
    @@ -455, +453 @@

      {{{
      CREATE TABLE cohisttax
      (
    -     PRIMARY KEY (taxhist_id),
    +     PRIMARY KEY (taxhist_id), =

    -     CHECK (taxhist_source =3D 'CH'),
    -     FOREIGN KEY (taxhist_source_id) REFERENCES cohist (cohist_id) ON DELE=
    TE CASCADE,
    +     FOREIGN KEY (taxhist_source_id) REFERENCES cohist (cohist_id) ON DELE=
    TE CASCADE, =

          FOREIGN KEY (taxhist_tax_id) REFERENCES tax (tax_id),
          FOREIGN KEY (taxhist_basis_tax_id) REFERENCES tax (tax_id)
    - )
    + ) =

      INHERITS (taxhist);
      }}}
    + All that needed to be defined on this table were the constraints, the res=
    t is inherited by taxhist.  The advantage of using table inheritence is tha=
    t it will enforce structural integrity, simplify queries and functions that=
    must sum tax from multiple tables and likely future maintenance as well.  =
    A new table should be made for each table that currently stores historical =
    tax data.  The naming convention will be {tablename} + tax per the example =
    above for each of these 12 tables: '''asohist, cmhead, cmitem, cobill, cobm=
    isc, cohist, invchead, invcitem, tohead, toitem, vohead, voitem.'''  Each t=
    able should have foreign keys referencing the parent and tax table ids as s=
    how above.
    - All that needed to be defined on this table were the constraints, the res=
    t is inherited by taxhist.  Tables like this should be made according to th=
    is matrix:
    - =

    - ||<tablewidth=3D"653px" tableheight=3D"430px" tablealign=3D"">Parent Tabl=
    e||New Child Table||Source Type Code||
    - ||asohist||asohisttax|| ||
    - ||cmhead||cmheadtax|| ||
    - ||cmitem||cmitemtax|| ||
    - ||cobill||cobilltax|| ||
    - ||cobmisc||cobmisctax|| ||
    - ||cohist||cohisttax|| ||
    - ||invchead||invcheadtax|| ||
    - ||invcitem||invcitemtax|| ||
    - ||tohead||toheadtax|| ||
    - ||toitem||toitemtax|| ||
    - ||vohead||voheadtax|| ||
    - ||voitem||voitemtax|| ||
    - =

    - =

      =

      =

      =3D=3D=3D Updated Tables =3D=3D=3D