omfgppc - 2009-03-10

TE OR REPLACE FUNCTION taxAssignments(INTEGER, INTEGER) RETURNS SETOF=
taxassign AS '
+ DECLARE
+   pTaxZoneId ALIAS FOR $1;
+   pTaxTypeId ALIAS FOR $2;
+   _row taxassign%ROWTYPE;
+   _qry text;
+   _x RECORD;
+   _y RECORD;
+ BEGIN
+   _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 ';
+     IF (pTaxZone IS NOT NULL) THEN
+       _qry :=3D '(taxass_taxzone_id=3DpTaxZoneId)'
+       IF (pTaxType IS NOT NULL) THEN
+         _qry :=3D ' AND ';
+       END IF;
+     END IF;
+     IF (pTaxType IS NOT NULL) THEN
+       _qry :=3D '(taxass_taxtype_id=3DpTaxTypeId)'
+     END IF;
+   END IF;
+ =

+   FOR _x IN EXECUTE _qry =

+   LOOP
+     Map values to _row here and RETURN NEXT so we get a level tax zone/ty=
pe 0 record.
+     FOR _y IN SELECT all the records with matching _x.taxzone_id and _x.t=
axtype_id from taxass =

+     LOOP
+       Map results to _row
+       RETURN NEXT to get code detail record;
+       =

+       SELECT results FROM taxcodesub(_y.taxassign_taxcode_id, 1) a new re=
cursive function described above
+       LOOP
+         Map results for sub codes =

+         RETURN NEXT return to _row
+       END LOOP;
+     END LOOP;
+   END LOOP;
+ }}}
+ =

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

  {{{
@@ -463, +508 @@

  ALTER TABLE tax ADD COLUMN tax_basis_tax_id integer REFERENCES tax (tax_i=
d) ON DELETE CASCADE;
  }}}
  =3D=3D=3D New Type =3D=3D=3D
+  . '''taxassign''' - A composite type used to return an indented list of =
tax assignments
+ =

+ {{{
+ CREATE TYPE taxassign AS
+ (
+   taxassign_taxzone_id integer,
+   taxassign_taxtype_id integer,
+   taxassign_level integer,
+   taxassign_zone_code text,
+   taxassign_type_descrip text,
+   taxassign_taxclass_code text,
+   taxassign_taxclass_sequence integer
+ );
+ }}}
-  . '''taxdetail''' - A composite type to return a calculated tax detail r=
ecord set used by the taxdetail function which is in turn used by the Tax D=
etail window.
+  . '''taxdetail''' - A composite type used to return a calculated tax det=
ail record set used by the taxdetail function which is in turn used by the =
Tax Detail window.
  =

  {{{
  CREATE TYPE taxdetail AS