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.
  =

  {{{