Fix ordering & application of functions/views/etc. Also some cosmetic changes.

[capital-apms.git] / dba / maintain_rules.sql

----------- Tenant.
-- tenantcode             | integer | not null default nextval('tenant_tenantcode_seq'::regclass)
-- billingcontact         | integer | default 0
-- name                   | text    | default ''::text
-- propertycontact        | integer | default 0
-- active                 | boolean | default false
-- debtclassification     | text    | default ''::text
-- varianceclassification | text    | default ''::text
-- entitycode             | integer | default 0
-- notecode               | integer |
-- quality                | integer | default 0
-- ah1contact             | integer | default 0
-- entitytype             | text    | default 'L'::text
-- legalname              | text    | default ''::text
-- paymentstyle           | text    | default ''::text
-- lastmodifieddate       | date    | default '0001-01-01'::date
-- lastmodifiedtime       | integer | default 0
-- lastmodifieduser       | text    | default ''::text
-- auditrecordid          | integer | default 0
-- ah2contact             | integer | default 0
-- businesstype           | text    | default ''::text
-- lastrenttotal          | numeric | default (0)::numeric
-- cfbalance              | numeric | default (0)::numeric
-- batchlaststatement     | integer | default 0
--
----------- Table "public.postaldetail"
--    Column   |  Type   |         Modifiers
-- ------------+---------+---------------------------
--  personcode | integer | not null default 0
--  postaltype | text    | not null default ''::text
--  address    | text    | default ''::text
--  city       | text    | default ''::text
--  state      | text    | default ''::text
--  country    | text    | default ''::text
--  zip        | text    | default ''::text
--
----------- Table "public.phonedetail"
--     Column    |  Type   |         Modifiers
-- --------------+---------+---------------------------
--  personcode   | integer | not null default 0
--  phonetype    | text    | not null default ''::text
--  number       | text    | default ''::text
--  countrycode  | integer | default 0
--  stdcode      | integer | default 0
--  ccountrycode | text    | default ''::text
--  cstdcode     | text    | default ''::text
--
----------- Table "public.person"
--      Column     |  Type   |    Modifiers
-- ----------------+---------+------------------
--  personcode     | integer | not null
--  persontitle    | text    | default ''::text
--  dateofbirth    | date    |
--  firstname      | text    | default ''::text
--  company        | text    | default ''::text
--  golfhandicap   | integer |
--  lastname       | text    | default ''::text
--  preferred      | text    | default ''::text
--  sex            | boolean |
--  jobtitle       | text    | default ''::text
--  office         | text    | default ''::text
--  creatorid      | text    | default ''::text
--  lastmodified   | date    |
--  department     | text    | default ''::text
--  spouse         | text    | default ''::text
--  lastvalidated  | date    |
--  notes          | integer |
--  systemcontact  | boolean | default false
--  scheduleplusid | text    | default ''::text
--  sorton         | text    | default ''::text
--  initials       | text    | default ''::text
--  middlenames    | text    | default ''::text
--  namesuffix     | text    | default ''::text
--  mailout        | boolean | default true
--  tempgroup      | boolean | default false


-- SELECT setval('batch_seq',(select max(b.batchcode) from (select batchcode from newaccttrans union select batchcode from accttran) b));


CREATE OR REPLACE VIEW debtor_maintenance AS
  SELECT tenant.tenantcode AS debtorcode, tenant.active AS debtor_active, tenant.name AS debtor_name,
         debtclassification, varianceclassification, tenant.entitytype AS debtor_et, tenant.entitycode AS debtor_ec,
         billingcontact,
         billperson.persontitle AS billperson_title, billperson.firstname AS billperson_first, billperson.lastname AS billperson_last,
         billperson.company AS bill_company_name, billperson.jobtitle AS billperson_job,
         bill.address AS bill_address, bill.city AS bill_city, bill.state AS bill_state, bill.country AS bill_country, bill.zip AS bill_zip,
         busphone.number AS busphone_no, busphone.ccountrycode AS busphone_isd, busphone.cstdcode AS busphone_std,
         get_entity_name( tenant.entitytype, tenant.entitycode ) AS debtor_entity_name
    FROM tenant
      LEFT JOIN person billperson ON tenant.billingcontact = billperson.personcode
      LEFT JOIN postaldetail bill ON tenant.billingcontact = bill.personcode AND bill.postaltype = 'BILL'
      LEFT JOIN phonedetail busphone ON tenant.billingcontact = busphone.personcode AND busphone.phonetype = 'BUS' ;

CREATE or REPLACE RULE debtor_maintenance_insert AS ON INSERT TO debtor_maintenance
DO INSTEAD
(
  INSERT INTO person ( personcode, persontitle, firstname, lastname, company, jobtitle )
    VALUES(
      COALESCE( NEW.billingcontact, nextval('person_personcode_seq')),
      COALESCE( NEW.billperson_title, '' ),
      COALESCE( NEW.billperson_first, ''),
      COALESCE( NEW.billperson_last, '' ),
      COALESCE( NEW.bill_company_name, NEW.debtor_name ),
      COALESCE( NEW.billperson_job, '')
    );
  INSERT INTO postaldetail ( personcode, postaltype, address, city, state, country, zip )
    VALUES(
      COALESCE( NEW.billingcontact, currval('person_personcode_seq')), 'BILL',
      COALESCE( NEW.bill_address, ''),
      COALESCE( NEW.bill_city, ''),
      COALESCE( NEW.bill_state, ''),
      COALESCE( NEW.bill_country, ''),
      COALESCE( NEW.bill_zip, '')
    );
  INSERT INTO phonedetail ( personcode, phonetype, number, ccountrycode, cstdcode )
    VALUES(
      COALESCE( NEW.billingcontact, currval('person_personcode_seq')), 'BUS',
      COALESCE( NEW.busphone_no, ''),
      COALESCE( NEW.busphone_isd, ''),
      COALESCE( NEW.busphone_std, '' )
    );
  INSERT INTO contact ( personcode, contacttype, systemcode )
    VALUES(
      COALESCE( NEW.billingcontact, currval('person_personcode_seq')), 'TNNT', TRUE
    );
  INSERT INTO tenant ( tenantcode, active, name, debtclassification, varianceclassification, entitytype, entitycode, billingcontact )
    VALUES(
      COALESCE( NEW.debtorcode, nextval('tenant_tenantcode_seq') ),
      COALESCE( NEW.debtor_active, TRUE ),
      COALESCE( NEW.debtor_name, 'An unnamed debtor' ),
      NEW.debtclassification, NEW.varianceclassification,
      COALESCE( NEW.debtor_et, 'L'),
      COALESCE( NEW.debtor_ec, 1 ),
      COALESCE( NEW.billingcontact, currval('person_personcode_seq'))
    );
);

CREATE or REPLACE RULE debtor_maintenance_update AS ON UPDATE TO debtor_maintenance
DO INSTEAD
(
  UPDATE person
     SET persontitle = NEW.billperson_title,
         firstname = NEW.billperson_first,
         lastname = NEW.billperson_last,
         company = NEW.bill_company_name,
         jobtitle = NEW.billperson_job
     WHERE personcode = NEW.billingcontact;

  UPDATE postaldetail
     SET address = NEW.bill_address,
         city    = NEW.bill_city,
         state   = NEW.bill_state,
         country = NEW.bill_country,
         zip     = NEW.bill_zip
    WHERE personcode = NEW.billingcontact AND postaltype = 'BILL';

  UPDATE phonedetail
     SET number       = NEW.busphone_no,
         ccountrycode = NEW.busphone_isd,
         cstdcode     = NEW.busphone_std
    WHERE personcode = NEW.billingcontact AND phonetype = 'BUS';

  UPDATE tenant
     SET active  = NEW.debtor_active,
         name    = NEW.debtor_name,
         debtclassification     = NEW.debtclassification,
         varianceclassification = NEW.varianceclassification,
         entitytype             = NEW.debtor_et,
         entitycode             = NEW.debtor_ec,
         billingcontact         = NEW.billingcontact
   WHERE tenantcode = OLD.debtorcode;
);




----------- Column         |  Type   |                            Modifiers
-- ------------------------+---------+-----------------------------------------------------------------
--  creditorcode           | integer | not null default nextval('creditor_creditorcode_seq'::regclass)
--  paymentcontact         | integer | default 0
--  name                   | text    | default ''::text
--  payeename              | text    | default ''::text
--  othercontact           | integer | default 0
--  active                 | boolean | default false
--  paymentstyle           | text    | default 'CHEQ'::text
--  bankdetails            | text    | default ''::text
--  companycode            | integer | default 0
--  lastmodifieddate       | date    | default '0001-01-01'::date
--  lastmodifiedtime       | integer | default 0
--  lastmodifieduser       | text    | default ''::text
--  auditrecordid          | integer | default 0
--  chequespermonth        | integer | default 31
--  vchrentitytype         | text    | default ''::text
--  vchrentitycode         | integer | default 0
--  vchraccountcode        | numeric | default (0)::numeric
--  vchrapprover           | text    | default ''::text
--  bankdetailschangedby   | text    | default ''::text
--  enabledirectpayment    | boolean | default false
--  directpaymentenabledby | text    | default ''::text
--  nonaccounting          | boolean | default false
--  acctcreditorcode       | integer | default 0
--  dcstatementtext        | text    | default ''::text
--  dcremittanceemail      | text    | default ''::text
--
--
----------- Table "public.postaldetail"
--    Column   |  Type   |         Modifiers
-- ------------+---------+---------------------------
--  personcode | integer | not null default 0
--  postaltype | text    | not null default ''::text
--  address    | text    | default ''::text
--  city       | text    | default ''::text
--  state      | text    | default ''::text
--  country    | text    | default ''::text
--  zip        | text    | default ''::text
--
----------- Table "public.phonedetail"
--     Column    |  Type   |         Modifiers
-- --------------+---------+---------------------------
--  personcode   | integer | not null default 0
--  phonetype    | text    | not null default ''::text
--  number       | text    | default ''::text
--  countrycode  | integer | default 0
--  stdcode      | integer | default 0
--  ccountrycode | text    | default ''::text
--  cstdcode     | text    | default ''::text
--
----------- Table "public.person"
--      Column     |  Type   |    Modifiers
-- ----------------+---------+------------------
--  personcode     | integer | not null
--  persontitle    | text    | default ''::text
--  dateofbirth    | date    |
--  firstname      | text    | default ''::text
--  company        | text    | default ''::text
--  golfhandicap   | integer |
--  lastname       | text    | default ''::text
--  preferred      | text    | default ''::text
--  sex            | boolean |
--  jobtitle       | text    | default ''::text
--  office         | text    | default ''::text
--  creatorid      | text    | default ''::text
--  lastmodified   | date    |
--  department     | text    | default ''::text
--  spouse         | text    | default ''::text
--  lastvalidated  | date    |
--  notes          | integer |
--  systemcontact  | boolean | default false
--  scheduleplusid | text    | default ''::text
--  sorton         | text    | default ''::text
--  initials       | text    | default ''::text
--  middlenames    | text    | default ''::text
--  namesuffix     | text    | default ''::text
--  mailout        | boolean | default true
--  tempgroup      | boolean | default false

CREATE OR REPLACE VIEW creditor_maintenance AS
  SELECT creditor.creditorcode AS creditorcode, creditor.active AS creditor_active, creditor.name AS creditor_name,
         payeename, dcstatementtext, dcremittanceemail, paymentstyle, enabledirectpayment, directpaymentenabledby,
         bankdetails, bankdetailschangedby, chequespermonth, vchrentitytype, vchrentitycode, vchraccountcode,
         vchrapprover, nonaccounting, acctcreditorcode, companycode,
         paymentcontact,
         pymtperson.persontitle AS pymtperson_title, pymtperson.firstname AS pymtperson_first, pymtperson.lastname AS pymtperson_last,
         pymtperson.company AS pymt_company_name, pymtperson.jobtitle AS pymtperson_job,
         pymt.address AS pymt_address, pymt.city AS pymt_city, pymt.state AS pymt_state, pymt.country AS pymt_country, pymt.zip AS pymt_zip,
         busphone.number AS busphone_no, busphone.ccountrycode AS busphone_isd, busphone.cstdcode AS busphone_std,
         get_entity_name( 'L', companycode ) AS creditor_entity_name
    FROM creditor
      LEFT JOIN person pymtperson ON creditor.paymentcontact = pymtperson.personcode
      LEFT JOIN postaldetail pymt ON creditor.paymentcontact = pymt.personcode AND pymt.postaltype = 'PYMT'
      LEFT JOIN phonedetail busphone ON creditor.paymentcontact = busphone.personcode AND busphone.phonetype = 'BUS' ;

CREATE or REPLACE RULE creditor_maintenance_insert AS ON INSERT TO creditor_maintenance
DO INSTEAD
(
  INSERT INTO person ( personcode, persontitle, firstname, lastname, company, jobtitle )
    VALUES(
      COALESCE( NEW.paymentcontact, nextval('person_personcode_seq')),
      COALESCE( NEW.pymtperson_title, '' ),
      COALESCE( NEW.pymtperson_first, ''),
      COALESCE( NEW.pymtperson_last, '' ),
      COALESCE( NEW.pymt_company_name, NEW.creditor_name ),
      COALESCE( NEW.pymtperson_job, '')
    );
  INSERT INTO postaldetail ( personcode, postaltype, address, city, state, country, zip )
    VALUES(
      COALESCE( NEW.paymentcontact, currval('person_personcode_seq')), 'PYMT',
      COALESCE( NEW.pymt_address, ''),
      COALESCE( NEW.pymt_city, ''),
      COALESCE( NEW.pymt_state, ''),
      COALESCE( NEW.pymt_country, ''),
      COALESCE( NEW.pymt_zip, '')
    );
  INSERT INTO phonedetail ( personcode, phonetype, number, ccountrycode, cstdcode )
    VALUES(
      COALESCE( NEW.paymentcontact, currval('person_personcode_seq')), 'BUS',
      COALESCE( NEW.busphone_no, ''),
      COALESCE( NEW.busphone_isd, ''),
      COALESCE( NEW.busphone_std, '' )
    );
  INSERT INTO contact ( personcode, contacttype, systemcode )
    VALUES(
      COALESCE( NEW.paymentcontact, currval('person_personcode_seq')), 'CRED', TRUE
    );
  INSERT INTO creditor ( creditorcode, active, name,
         payeename, dcstatementtext, dcremittanceemail, paymentstyle, enabledirectpayment, directpaymentenabledby,
         bankdetails, bankdetailschangedby, chequespermonth, vchrentitytype, vchrentitycode, vchraccountcode,
         vchrapprover, nonaccounting, acctcreditorcode, companycode, paymentcontact )
    VALUES(
      COALESCE( NEW.creditorcode, nextval('creditor_creditorcode_seq') ),
      COALESCE( NEW.creditor_active, TRUE ),
      COALESCE( NEW.creditor_name, 'An unnamed creditor' ),
      NEW.payeename, NEW.dcstatementtext, NEW.dcremittanceemail, NEW.paymentstyle, NEW.enabledirectpayment, NEW.directpaymentenabledby,
      NEW.bankdetails, NEW.bankdetailschangedby, NEW.chequespermonth, NEW.vchrentitytype, NEW.vchrentitycode, NEW.vchraccountcode,
      NEW.vchrapprover, NEW.nonaccounting, NEW.acctcreditorcode,
      COALESCE( NEW.companycode, 1 ),
      COALESCE( NEW.paymentcontact, currval('person_personcode_seq'))
    );
);

CREATE or REPLACE RULE creditor_maintenance_update AS ON UPDATE TO creditor_maintenance
DO INSTEAD
(
  UPDATE person
     SET persontitle = NEW.pymtperson_title,
         firstname = NEW.pymtperson_first,
         lastname = NEW.pymtperson_last,
         company = NEW.pymt_company_name,
         jobtitle = NEW.pymtperson_job
     WHERE personcode = NEW.paymentcontact;

  UPDATE postaldetail
     SET address = NEW.pymt_address,
         city    = NEW.pymt_city,
         state   = NEW.pymt_state,
         country = NEW.pymt_country,
         zip     = NEW.pymt_zip
    WHERE personcode = NEW.paymentcontact AND postaltype = 'PYMT';

  UPDATE phonedetail
     SET number       = NEW.busphone_no,
         ccountrycode = NEW.busphone_isd,
         cstdcode     = NEW.busphone_std
    WHERE personcode = NEW.paymentcontact AND phonetype = 'BUS';

  UPDATE creditor
     SET active                 = NEW.creditor_active,
         name                   = NEW.creditor_name,
         payeename              = NEW.payeename,
         dcstatementtext        = NEW.dcstatementtext,
         dcremittanceemail      = NEW.dcremittanceemail,
         paymentstyle           = NEW.paymentstyle,
         enabledirectpayment    = NEW.enabledirectpayment,
         directpaymentenabledby = NEW.directpaymentenabledby,
         bankdetails            = NEW.bankdetails,
         bankdetailschangedby   = NEW.bankdetailschangedby,
         chequespermonth        = NEW.chequespermonth,
         vchrentitytype         = NEW.vchrentitytype,
         vchrentitycode         = NEW.vchrentitycode,
         vchraccountcode        = NEW.vchraccountcode,
         vchrapprover           = NEW.vchrapprover,
         nonaccounting          = NEW.nonaccounting,
         acctcreditorcode       = NEW.acctcreditorcode,
         companycode            = NEW.companycode,
         paymentcontact         = NEW.paymentcontact
   WHERE creditorcode = OLD.creditorcode;
);