Wiki Change:

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

    input of tax zone and tax type and returns a list of tax as=
    signments the correspond to the inputs, including sub taxes.  When null is =
    passed to either input, the result will be assumed to include all results f=
    or that input.  A column will be passed to specify indentation role that ma=
    y be used by the xtreewidget to define the indentation role.  IndentedBOM(i=
    nt,int,int,int) is an example of a similar function.
      =

      Our new function will require a new '''taxassign''' composite type used t=
    o return the indented list of tax assignments
      =

    @@ -350, +352 @@

        taxassign_taxclass_sequence integer
      );
      }}}
    - Then we can build our function
    + Then we can build our function '''taxAssignments''':
      =

      {{{
      CREATE OR REPLACE FUNCTION taxAssignments(INTEGER, INTEGER) RETURNS SETOF=
    taxassign AS $$
    @@ -362, +364 @@

        _x RECORD;
        _y RECORD;
      BEGIN
    +   -- Need to build a query statement based on input parameters
        _qry =3D 'SELECT DISTINCT taxass_taxzone_id,taxass_taxtype_id FROM taxa=
    ss';
        IF (pTaxZoneId IS NOT NULL OR pTaxTypeId IS NOT NULL) THEN
          _qry :=3D _qry || 'WHERE ';
    @@ -376, +379 @@

          END IF;
        END IF;
      =

    +   --This first query gets all the distinct tax zone and type groupings as=
    if it were its own table.
    +   --This allows us to have a level 0 record as pictured in Tax Assignment=
    s window that code assignements will
    +   --Subordinate to.
        FOR _x IN EXECUTE _qry
        LOOP
    +     Map values to _row here
    -     Map values to _row here and RETURN NEXT so we get a level tax zone/ty=
    pe 0 record.
    +     RETURN NEXT so we get a level tax zone/type 0 record.
    + =

    +     -- Now get all the tax code assignments that belong to this Zone and =
    Type pair
    -     FOR _y IN SELECT all the records with matching _x.taxzone_id and _x.t=
    axtype_id from taxass
    +     FOR _y IN SELECT all the records with matching _x.taxzone_id and _x.t=
    axtype_id
    +                      make sure to coalesce group sequnce to zero if no ta=
    x class
    +               FROM taxass
    +               JOIN taxcode ON (taxass_taxcode_id=3Dtaxcode_id)
    +               LEFT OUTER JOIN taxclass ON (taxass_taxcode_id=3Dtax)
          LOOP
            Map results to _row
            RETURN NEXT to get code detail record;
      =

    +       -- Get the sub taxes for this particular code
            SELECT results FROM taxcodesub(_y.taxassign_taxcode_id, 1) a new re=
    cursive function described above
            LOOP
    -         Map results for sub codes
    +         Map results for sub taxes to _row
              RETURN NEXT return to _row
      =

      ... end loops and function
      }}}
    + A query should look something like this:
    + =

    + select * from taxAssignments(4,NULL) -- Where 4 is the system assigned ta=
    x code id for "Gujarat" and NULL indicates all tax types are to be returned
    + =

    + The results as presented for Food Staple and Service in Tax Assignment sc=
    reen shot above:
    + =

    + ||<tablewidth=3D"200px" tablealign=3D"">taxassign_taxzone_id||taxassign_t=
    axtype_id||taxassign_level||taxassign_zone_code||taxassign_type_descrip||ta=
    xassign_classcode_code||taxassign_sequenc||
    + ||4||12||0||Gujarat||Food Staple|| || ||
    + ||4||12||1||ED-10||Excise Duty (10%)||Excise||1||
    + ||4||12||2||EC||Eductation Cess||Excise||1||
    + ||4||12||3||HEC||Higher Education Cess||Excise||1||
    + ||4||12||1||VAT-10||Value Added Tax (10%)||VAT||2||
    + ||4||12||1||Octroi||Octroi||Local||3||
    + ||4||23||0||Gujarat||Service|| || ||
    + ||4||23||1||ST-10||Service Tax (10%)||Excise||1||
    + ||4||23||2||ES||Education Service||Excise||1||
    + ||4||23||3||HES||Higher Education Service||Excise||1||
    + ||4||23||1||CST-10||Central Sales Tax (10%)||Sales||2||
    + ||4||23||1||Octroi||Octroi||Local||3||
    + =

    + =

    + =

    + =

    + =

    + =

      A new table function will be created that takes the an input of tax zone,=
    tax type,date,currency id, and amount to produce a set of output records de=
    scribing tax detail.  This function will be multilevel similar to the inden=
    tedBOM(int,int,int,int) function. It will return records using the taxdetai=
    l composite type listed below.
      =

      {{{