pears.AccountOverrideInvoiceAddress [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.AccountOverrideInvoiceAddress ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **AccountOverrideInvoiceAddressID** | char(20) | NOT NULL |  |  |
| InvoiceCompanyID | char(20) | NULL |  |  |
| SalesBrandID | char(20) | NULL |  |  |
| AlternativeInvoiceAddressEmailID | char(20) | NULL |  | may not be used |
| InvoiceEmail | char(250) | NULL |  | may not be used |
| InvoiceAddress | long varchar | NULL |  | may not be used |
| ProduceInvoice | tinyint | NULL | 1 |  |
| ABIFramework | char(50) | NULL |  | Pulse only |
| ABIAmount | double | NULL |  | Pulse only |

===== Primary Key =====
  * AccountOverrideInvoiceAddressID

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| Company | InvoiceCompanyID | [[database:tables:pears_company|pears.Company (companyid)]] | ON DELETE SET NULL |
| SalesBrand | SalesBrandID | [[database:tables:pears_salesbrand|pears.SalesBrand (SalesBrandID)]] | ON DELETE SET NULL |
| AlternativeInvoiceAddressEmail | AlternativeInvoiceAddressEmailID | [[database:tables:pears_alternativeinvoiceaddressemail|pears.AlternativeInvoiceAddressEmail (AlternativeInvoiceAddressEmailID)]] | ON DELETE CASCADE |

===== Referenced By =====
  * No incoming foreign keys found.

===== Indexes =====
  * No indexes found.

===== Triggers =====
^ Name ^ Timing ^ Event ^
| AccountOverrideInvoiceAddressAudit | after | insert,delete,update order 1 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."AccountOverrideInvoiceAddress"
-- Table comment: 
-- Statement count: 11

CREATE TABLE "pears"."AccountOverrideInvoiceAddress" (
    "AccountOverrideInvoiceAddressID" char(20) NOT NULL
   ,"InvoiceCompanyID"               char(20) NULL
   ,"SalesBrandID"                   char(20) NULL
   ,"AlternativeInvoiceAddressEmailID" char(20) NULL
   ,"InvoiceEmail"                   char(250) NULL
   ,"InvoiceAddress"                 long varchar NULL
   ,"ProduceInvoice"                 tinyint NULL DEFAULT 1
   ,"ABIFramework"                   char(50) NULL
   ,"ABIAmount"                      double NULL
   ,PRIMARY KEY ("AccountOverrideInvoiceAddressID" ASC) 
)
go


COMMENT ON COLUMN "pears"."AccountOverrideInvoiceAddress"."AlternativeInvoiceAddressEmailID" IS 
	'may not be used'
go


COMMENT ON COLUMN "pears"."AccountOverrideInvoiceAddress"."InvoiceEmail" IS 
	'may not be used'
go


COMMENT ON COLUMN "pears"."AccountOverrideInvoiceAddress"."InvoiceAddress" IS 
	'may not be used'
go


COMMENT ON COLUMN "pears"."AccountOverrideInvoiceAddress"."ABIFramework" IS 
	'Pulse only'
go


COMMENT ON COLUMN "pears"."AccountOverrideInvoiceAddress"."ABIAmount" IS 
	'Pulse only'
go


ALTER TABLE "pears"."AccountOverrideInvoiceAddress"
    ADD FOREIGN KEY "Company" ("InvoiceCompanyID" ASC)
    REFERENCES "pears"."Company" ("companyid")
    ON DELETE SET NULL
go


ALTER TABLE "pears"."AccountOverrideInvoiceAddress"
    ADD FOREIGN KEY "SalesBrand" ("SalesBrandID" ASC)
    REFERENCES "pears"."SalesBrand" ("SalesBrandID")
    ON DELETE SET NULL
go


ALTER TABLE "pears"."AccountOverrideInvoiceAddress"
    ADD FOREIGN KEY "AlternativeInvoiceAddressEmail" ("AlternativeInvoiceAddressEmailID" ASC)
    REFERENCES "pears"."AlternativeInvoiceAddressEmail" ("AlternativeInvoiceAddressEmailID")
    ON DELETE CASCADE
go


create trigger "AccountOverrideInvoiceAddressAudit" after insert,delete,update order 1 on
"pears"."AccountOverrideInvoiceAddress"
referencing old as "oldrec" new as "newrec"
for each row
begin
  call "AuditLog"('COMPANY',"oldrec"."invoicecompanyid","string"(if deleting then 'Delete' else if updating then 'Update' else 'Insert' endif endif,' Account Override Invoice Address ',
  "isnull"((select "description" from "salesbrand" as "s" where "s"."salesbrandid" = "oldrec"."salesbrandid"),
  (select "description" from "AlternativeInvoiceAddressEmail" as "s" where "s"."AlternativeInvoiceAddressEmailid" = "oldrec"."AlternativeInvoiceAddressEmailid"),
  (select "description" from "salesbrand" as "s" where "s"."salesbrandid" = "newrec"."salesbrandid"),
  (select "description" from "AlternativeInvoiceAddressEmail" as "s" where "s"."AlternativeInvoiceAddressEmailid" = "newrec"."AlternativeInvoiceAddressEmailid"))),
  "string"("oldrec"."invoiceaddress",' ',"oldrec"."invoiceemail"),"string"("newrec"."invoiceaddress",' ',"newrec"."invoiceemail"))
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."AccountOverrideInvoiceAddress"."AccountOverrideInvoiceAddressAudit" IS 
{create trigger AccountOverrideInvoiceAddressAudit 
 after insert,delete,update 
order 1 on pears.AccountOverrideInvoiceAddress
referencing old as oldrec new as newrec
for each row
begin
    call AuditLog('COMPANY',oldrec.invoicecompanyid,string( if deleting then 'Delete' else if updating then 'Update' else 'Insert' end if endif , ' Account Override Invoice Address ', 
          isnull( (select description from salesbrand s where s.salesbrandid=oldrec.salesbrandid), 
          (select description from AlternativeInvoiceAddressEmail s where s.AlternativeInvoiceAddressEmailid=oldrec.AlternativeInvoiceAddressEmailid) ,
          (select description from salesbrand s where s.salesbrandid=newrec.salesbrandid), 
          (select description from AlternativeInvoiceAddressEmail s where s.AlternativeInvoiceAddressEmailid=newrec.AlternativeInvoiceAddressEmailid)) ),
          string(oldrec.invoiceaddress, ' ', oldrec.invoiceemail ),string(newrec.invoiceaddress, ' ', newrec.invoiceemail ) )
end
}
go
</code>