3 use vars qw(@ISA @EXPORT_OK $DEBUG $setup_hack %dbdef_cache);
4 use subs qw(reload_dbdef);
6 use DBIx::DBSchema 0.44; #for foreign keys with MATCH / ON DELETE/UPDATE
7 use DBIx::DBSchema::Table;
8 use DBIx::DBSchema::Column;
9 use DBIx::DBSchema::Index;
10 use DBIx::DBSchema::ForeignKey;
11 #can't use this yet, dependency bs #use FS::Conf;
14 @EXPORT_OK = qw( dbdef dbdef_dist reload_dbdef );
21 FS::Schema - Freeside database schema
25 use FS::Schema qw(dbdef dbdef_dist reload_dbdef);
27 $dbdef = reload_dbdef;
28 $dbdef = reload_dbdef "/non/standard/filename";
30 $dbdef_dist = dbdef_dist;
34 This class represents the database schema.
40 =item reload_dbdef([FILENAME])
42 Load a database definition (see L<DBIx::DBSchema>), optionally from a
43 non-default filename. This command is executed at startup unless
44 I<$FS::Schema::setup_hack> is true. Returns a DBIx::DBSchema object.
51 unless ( exists $dbdef_cache{$file} ) {
52 warn "[debug]$me loading dbdef for $file\n" if $DEBUG;
53 $dbdef_cache{$file} = DBIx::DBSchema->load( $file )
54 or die "can't load database schema from $file: $DBIx::DBSchema::errstr\n";
56 warn "[debug]$me re-using cached dbdef for $file\n" if $DEBUG;
58 $dbdef = $dbdef_cache{$file};
59 FS::Record::fk_methods_init();
64 Returns the current database definition (represents the current database,
65 assuming it is up-to-date). See L<DBIx::DBSchema>.
71 =item dbdef_dist [ DATASRC ]
73 Returns the current canoical database definition as defined in this file.
75 Optionally, pass a DBI data source to enable syntax specific to that database.
76 Currently, this enables "ENGINE=InnoDB" for MySQL databases.
81 my $datasrc = @_ && !ref($_[0]) ? shift : '';
82 my $opt = @_ ? shift : {};
84 my $local_options = '';
85 if ( $datasrc =~ /^dbi:mysql/i ) {
86 $local_options = 'ENGINE=InnoDB';
90 # create a dbdef object from the old data structure
93 my $tables_hashref = tables_hashref();
96 my $dbdef = new DBIx::DBSchema map {
102 while (@{$tables_hashref->{$tablename}{'columns'}}) {
103 #my($name, $type, $null, $length, $default, $local) =
105 splice @{$tables_hashref->{$tablename}{'columns'}}, 0, 6;
106 my %hash = map { $_ => shift @coldef }
107 qw( name type null length default local );
109 #can be removed once we depend on DBIx::DBSchema 0.39;
110 $hash{'type'} = 'LONGTEXT'
111 if $hash{'type'} =~ /^TEXT$/i && $datasrc =~ /^dbi:mysql/i;
113 unless ( defined $hash{'default'} ) {
114 warn "$tablename:\n".
115 join('', map "$_ => $hash{$_}\n", keys %hash) ;# $stop = <STDIN>;
118 push @columns, new DBIx::DBSchema::Column ( \%hash );
121 #false laziness w/sub indices in DBIx::DBSchema::DBD (well, sorta)
122 #and sub sql_create_table in DBIx::DBSchema::Table (slighty more?)
123 my $unique = $tables_hashref->{$tablename}{'unique'};
124 warn "missing index for $tablename\n" unless defined $tables_hashref->{$tablename}{'index'};
125 my @index = @{ $tables_hashref->{$tablename}{'index'} };
127 # kludge to avoid avoid "BLOB/TEXT column 'statustext' used in key
128 # specification without a key length".
129 # better solution: teach DBIx::DBSchema to specify a default length for
130 # MySQL indices on text columns, or just to support an index length at all
131 # so we can pass something in.
132 # best solution: eliminate need for this index in cust_main::retry_realtime
133 @index = grep { @{$_}[0] ne 'statustext' } @index
134 if $datasrc =~ /^dbi:mysql/i;
138 DBIx::DBSchema::Index->new({
139 'name' => $tablename. $indexnum++,
146 DBIx::DBSchema::Index->new({
147 'name' => $tablename. $indexnum++,
155 map DBIx::DBSchema::ForeignKey->new($_),
156 @{ $tables_hashref->{$tablename}{'foreign_keys'} || [] };
158 DBIx::DBSchema::Table->new({
160 primary_key => $tables_hashref->{$tablename}{'primary_key'},
161 columns => \@columns,
162 indices => \@indices,
163 foreign_keys => \@foreign_keys,
164 local_options => $local_options,
167 } keys %$tables_hashref;
170 warn "[debug]$me initial dbdef_dist created ($dbdef) with tables:\n";
171 warn "[debug]$me $_\n" foreach $dbdef->tables;
174 #add radius attributes to svc_acct
176 #my($svc_acct)=$dbdef->table('svc_acct');
179 #foreach $attribute (@attributes) {
180 # $svc_acct->addcolumn ( new DBIx::DBSchema::Column (
181 # 'radius_'. $attribute,
188 #foreach $attribute (@check_attributes) {
189 # $svc_acct->addcolumn( new DBIx::DBSchema::Column (
197 my $tables_hashref_torrus = tables_hashref_torrus();
199 #create history tables
201 grep { ! /^(clientapi|access_user)_session/
203 && ! /^log(_context)?$/
204 && ! /^(legacy_cust_history|cacti_page|template_image|access_user_log)$/
205 && ( ! /^queue(_arg|_depend|_stat)?$/ || ! $opt->{'queue-no_history'} )
207 && ! $tables_hashref_torrus->{$_}
211 my $tableobj = $dbdef->table($table)
212 or die "unknown table $table";
216 unless ( $table eq 'cust_event' || $table eq 'cdr' ) { #others?
218 my %indices = $tableobj->indices;
222 DBIx::DBSchema::Index->new({
223 'name' => 'h_'. $indices{$_}->name,
225 'columns' => [ @{$indices{$_}->columns} ],
231 $h_indices{"h_${table}_srckey"} =
232 DBIx::DBSchema::Index->new({
233 'name' => "h_${table}_srckey",
235 'columns' => [ 'history_action', #right?
236 $tableobj->primary_key,
240 $h_indices{"h_${table}_srckey2"} =
241 DBIx::DBSchema::Index->new({
242 'name' => "h_${table}_srckey2",
244 'columns' => [ 'history_date',
245 $tableobj->primary_key,
249 #necessary for queries that want to look at *who* made changes
250 $h_indices{"h_${table}_usernum"} =
251 DBIx::DBSchema::Index->new({
252 'name' => "h_${table}_usernum",
254 'columns' => [ 'history_usernum'],
257 # necessary because of the evil OR username for older data, be really nice if everything was just migrated to usernum and we could drop username
258 # This will not be helpful to mysql, but postgres smartly does a bitmap across both indexes, mysql will just use one
260 $h_indices{"h_${table}_user"} =
261 DBIx::DBSchema::Index->new({
262 'name' => "h_${table}_user",
264 'columns' => [ 'history_user'],
268 my $primary_key_col = $tableobj->column($tableobj->primary_key)
269 or die "$table: primary key declared as ". $tableobj->primary_key.
270 ", but no column of that name\n";
272 my $historynum_type = ( $tableobj->column($tableobj->primary_key)->type
273 =~ /^(bigserial|bigint|int8)$/i
278 my $h_tableobj = DBIx::DBSchema::Table->new( {
279 'name' => "h_$table",
280 'primary_key' => 'historynum',
281 'indices' => \%h_indices,
282 'local_options' => $local_options,
284 DBIx::DBSchema::Column->new( {
285 'name' => 'historynum',
286 'type' => $historynum_type,
287 'null' => 'NOT NULL',
292 DBIx::DBSchema::Column->new( {
293 'name' => 'history_date',
300 DBIx::DBSchema::Column->new( {
301 'name' => 'history_user',
308 DBIx::DBSchema::Column->new( {
309 'name' => 'history_usernum',
316 DBIx::DBSchema::Column->new( {
317 'name' => 'history_action',
319 'null' => 'NOT NULL',
325 my $column = $tableobj->column($_);
327 #clone so as to not disturb the original
328 $column = DBIx::DBSchema::Column->new( {
329 map { $_ => $column->$_() }
330 qw( name type null length default local )
333 if ( $column->type =~ /^(\w*)SERIAL$/i ) {
334 $column->type(uc($1).'INT');
335 $column->null('NULL');
337 #$column->default('')
338 # if $column->default =~ /^nextval\(/i;
339 #( my $local = $column->local ) =~ s/AUTO_INCREMENT//i;
340 #$column->local($local);
345 $dbdef->addtable($h_tableobj);
348 if ( $datasrc =~ /^dbi:mysql/i ) {
350 my $dup_lock_table = DBIx::DBSchema::Table->new( {
351 'name' => 'duplicate_lock',
352 'primary_key' => 'duplocknum',
353 'local_options' => $local_options,
355 DBIx::DBSchema::Column->new( {
356 'name' => 'duplocknum',
358 'null' => 'NOT NULL',
363 DBIx::DBSchema::Column->new( {
364 'name' => 'lockname',
366 'null' => 'NOT NULL',
372 'indices' => { 'duplicate_lock1' =>
373 DBIx::DBSchema::Index->new({
374 'name' => 'duplicate_lock1',
376 'columns' => [ 'lockname' ],
381 $dbdef->addtable($dup_lock_table);
389 #torrus tables http://torrus.org/reporting_setup.pod.html#create_sql_tables
390 sub tables_hashref_torrus {
394 # Collector export table. It usually grows at several megabytes
395 # per month, and is updated every 5 minutes
398 'id', 'serial', '', '', '', '',
399 'srv_date', 'date', '', '', '', '',#date and time of the data sample
400 'srv_time', 'time', '', '', '', '',
401 'serviceid', 'varchar', '', 64, '', '',#unique service ID per counter
402 'value', 'double precision', '', '', '', '',#collected rate or gauge value
403 'intvl', 'int', '', '', '', '', # collection interval - for counter volume calculation
405 'primary_key' => 'id',
407 'index' => [ ['srv_date'], ['srv_date', 'srv_time'], ['serviceid'], ],
410 #Tables for (currently monthly only) report contents.
411 #These are updated usually once per month, and read at the moment of
412 #rendering the report output (HTML now, PDF or XML or Excel or whatever
415 #DBIx::Sequence backend, theplatform-independent inplementation
417 'dbix_sequence_state' => {
419 'id', 'serial', '', '', '', '',
420 'dataset', 'varchar', '', 50, '', '',
421 'state_id', 'int', '', '', '', '',
423 'primary_key' => 'id',
424 #CONSTRAINT pk_dbix_sequence PRIMARY KEY (dataset, state_id)
425 'unique' => [ [ 'dataset', 'state_id' ], ],
429 'dbix_sequence_release' => {
431 'id', 'serial', '', '', '', '',
432 'dataset', 'varchar', '', 50, '', '',
433 'released_id', 'int', '', '', '', '',
435 'primary_key' => 'id',
436 #CONSTRAINT pk_dbi_release PRIMARY KEY (dataset, released_id)
437 'unique' => [ [ 'dataset', 'released_id', ] ],
441 #Each report is characterized by name, date and time.
442 #Monthly reports are automatically assigned 00:00 of the 1st day
443 #in the month. The report contains fields for every service ID
444 #defined across all datasource trees.
447 'id', 'serial', '', '', '', '',
448 'rep_date', 'date', '', '', '', '',#Start date of the report
449 'rep_time', 'time', '', '', '', '',#Start time of the report
450 'reportname', 'varchar', '', 64, '', '',#Report name, such as
452 'iscomplete', 'int', '', '', '', '',#0 when the report is in
453 # progress, 1 when it is ready
455 'primary_key' => 'id',
456 'unique' => [ [ qw(rep_date rep_time reportname) ] ],
457 'index' => [ [ 'rep_date' ] ],
460 #Each report contains fields. For each service ID,
461 #the report may contain several fields for various statistics.
462 #Each field contains information about the units of the value it
466 'id', 'serial', '', '', '', '',
467 'rep_id', 'int', 'NULL', '', '', '',
468 'name', 'varchar', '', 64, '', '',#name of the field,
470 'serviceid', 'varchar', '', 64, '', '',#service ID
471 'value', 'double precision', '', '', '', '',#Numeric value
472 'units', 'varchar', '', 64, \"''", '',#Units, such as bytes
475 'primary_key', => 'id',
476 'unique' => [ [ qw(rep_id name serviceid) ] ],
486 my $char_d = 80; #default maxlength for text fields
488 #my(@date_type) = ( 'timestamp', '', '' );
489 my @date_type = ( 'int', 'NULL', '' );
490 my @perl_type = ( 'text', 'NULL', '' );
491 my @money_type = ( 'decimal', '', '10,2' );
492 my @money_typen = ( 'decimal', 'NULL', '10,2' );
493 my @taxrate_type = ( 'decimal', '', '14,8' ); # requires pg 8 for
494 my @taxrate_typen = ( 'decimal', 'NULL', '14,8' ); # fs-upgrade to work
496 my $username_len = 64; #usernamemax config file
498 # Return a hashref defining the entire application database schema
499 # Each key of the hashref contains a structure describing a database table
503 # primary_key => 'column',
504 # unique => [column,column,...],
505 # index => [[column],[column,column],...],
506 # foreign_keys => [{...},{...},...],
516 # 'NULL' or '', # 'NULL' : Allow null values
517 # # '' : Disallow null values
519 # 'length', # Column size value. eg:
521 # # '10,2' : FLOAT(10,2)
523 # 'default', # Default column value for a new record
524 # # (Unclear if setting this to '' results in a default
525 # # value of NULL or empty string?)
529 # name, type, nullability, length, default, local,
530 # name, type, nullability, length, default, local,
535 # name type nullability length default local
540 'agentnum', 'serial', '', '', '', '',
541 'agent', 'varchar', '', $char_d, '', '',
542 'typenum', 'int', '', '', '', '',
543 'ticketing_queueid', 'int', 'NULL', '', '', '',
544 'invoice_template', 'varchar', 'NULL', $char_d, '', '',
545 'agent_custnum', 'int', 'NULL', '', '', '',
546 'disabled', 'char', 'NULL', 1, '', '',
547 'username', 'varchar', 'NULL', $char_d, '', '',
548 '_password', 'varchar', 'NULL', $char_d, '', '',
549 'freq', 'int', 'NULL', '', '', '', #deprecated (never used)
550 'prog', @perl_type, '', '', #deprecated (never used)
552 'primary_key' => 'agentnum',
553 #'unique' => [ [ 'agent_custnum' ] ], #one agent per customer?
554 #insert is giving it a value, tho..
555 #'index' => [ ['typenum'], ['disabled'] ],
557 'index' => [ ['typenum'], ['disabled'], ['agent_custnum'] ],
559 { columns => [ 'typenum' ],
560 table => 'agent_type',
562 # 1. RT tables aren't part of our data structure, so
563 # we can't make sure Queue is created already
564 # 2. Future ability to plug in other ticketing systems
565 #{ columns => [ 'ticketing_queueid' ],
567 # references => [ 'id' ],
569 { columns => [ 'agent_custnum' ],
570 table => 'cust_main',
571 references => [ 'custnum' ],
576 'agent_pkg_class' => {
578 'agentpkgclassnum', 'serial', '', '', '', '',
579 'agentnum', 'int', '', '', '', '',
580 'classnum', 'int', 'NULL', '', '', '',
581 'commission_percent', 'decimal', '', '7,4', '', '',
583 'primary_key' => 'agentpkgclassnum',
584 'unique' => [ [ 'agentnum', 'classnum' ], ],
587 { columns => [ 'agentnum' ],
590 { columns => [ 'classnum' ],
591 table => 'pkg_class',
598 'typenum', 'serial', '', '', '', '',
599 'atype', 'varchar', '', $char_d, '', '',
600 'disabled', 'char', 'NULL', 1, '', '',
602 'primary_key' => 'typenum',
604 'index' => [ ['disabled'] ],
609 'typepkgnum', 'serial', '', '', '', '',
610 'typenum', 'int', '', '', '', '',
611 'pkgpart', 'int', '', '', '', '',
613 'primary_key' => 'typepkgnum',
614 'unique' => [ ['typenum', 'pkgpart'] ],
615 'index' => [ ['typenum'] ],
617 { columns => [ 'typenum' ],
618 table => 'agent_type',
620 { columns => [ 'pkgpart' ],
626 'agent_currency' => {
628 'agentcurrencynum', 'serial', '', '', '', '',
629 'agentnum', 'int', '', '', '', '',
630 'currency', 'char', '', 3, '', '',
632 'primary_key' => 'agentcurrencynum',
634 'index' => [ ['agentnum'] ],
636 { columns => [ 'agentnum' ],
644 'salesnum', 'serial', '', '', '', '',
645 'salesperson', 'varchar', '', $char_d, '', '',
646 'agentnum', 'int', 'NULL', '', '', '',
647 'sales_custnum', 'int', 'NULL', '', '', '',
648 'disabled', 'char', 'NULL', 1, '', '',
650 'primary_key' => 'salesnum',
652 'index' => [ ['salesnum'], ['disabled'] ],
654 { columns => [ 'agentnum' ],
657 { columns => [ 'sales_custnum' ],
658 table => 'cust_main',
659 references => [ 'custnum' ],
664 'sales_pkg_class' => {
666 'salespkgclassnum', 'serial', '', '', '', '',
667 'salesnum', 'int', '', '', '', '',
668 'classnum', 'int', 'NULL', '', '', '',
669 'commission_percent', 'decimal', '', '7,4', '', '',
670 'commission_duration', 'int', 'NULL', '', '', '',
672 'primary_key' => 'salespkgclassnum',
673 'unique' => [ [ 'salesnum', 'classnum' ], ],
676 { columns => [ 'salesnum' ],
679 { columns => [ 'classnum' ],
680 table => 'pkg_class',
685 'cust_attachment' => {
687 'attachnum', 'serial', '', '', '', '',
688 'custnum', 'int', '', '', '', '',
689 '_date', @date_type, '', '',
690 'otaker', 'varchar', 'NULL', 32, '', '',
691 'usernum', 'int', 'NULL', '', '', '',
692 'filename', 'varchar', '', 255, '', '',
693 'mime_type', 'varchar', '', $char_d, '', '',
694 'title', 'varchar', 'NULL', $char_d, '', '',
695 'body', 'blob', 'NULL', '', '', '',
696 'disabled', @date_type, '', '',
698 'primary_key' => 'attachnum',
700 'index' => [ ['custnum'], ['usernum'], ],
702 { columns => [ 'custnum' ],
703 table => 'cust_main',
705 { columns => [ 'usernum' ],
706 table => 'access_user',
714 'invnum', 'serial', '', '', '', '',
715 'custnum', 'int', '', '', '', '',
716 '_date', @date_type, '', '',
717 'charged', @money_type, '', '',
718 'currency', 'char', 'NULL', 3, '', '',
719 'invoice_terms', 'varchar', 'NULL', $char_d, '', '',
721 #customer balance info at invoice generation time
723 'previous_balance', @money_typen, '', '', #eventually not nullable
724 'billing_balance', @money_typen, '', '', #eventually not nullable
726 #deprecated (unused by now, right?)
727 'printed', 'int', '', '', '', '',
730 'closed', 'char', 'NULL', 1, '', '', #not yet used much
731 'statementnum', 'int', 'NULL', '', '', '', #invoice aggregate statements
732 'agent_invid', 'int', 'NULL', '', '', '', #(varchar?) importing legacy
733 'promised_date', @date_type, '', '',
734 'taxengine_request', 'text', 'NULL', '', '', '',
736 'pending', 'char', 'NULL', 1, '', '',
738 'primary_key' => 'invnum',
739 'unique' => [ [ 'custnum', 'agent_invid' ] ], #agentnum? huh
740 'index' => [ ['custnum'], ['_date'], ['statementnum'],
744 { columns => [ 'custnum' ],
745 table => 'cust_main',
747 { columns => [ 'statementnum' ],
748 table => 'cust_statement',
753 'cust_bill_void' => {
756 'invnum', 'int', '', '', '', '',
757 'custnum', 'int', '', '', '', '',
758 '_date', @date_type, '', '',
759 'charged', @money_type, '', '',
760 'currency', 'char', 'NULL', 3, '', '',
761 'invoice_terms', 'varchar', 'NULL', $char_d, '', '',
763 #customer balance info at invoice generation time
764 'previous_balance', @money_typen, '', '', #eventually not nullable
765 'billing_balance', @money_typen, '', '', #eventually not nullable
768 'closed', 'char', 'NULL', 1, '', '', #not yet used much
769 'statementnum', 'int', 'NULL', '', '', '', #invoice aggregate statements
770 'agent_invid', 'int', 'NULL', '', '', '', #(varchar?) importing legacy
771 'promised_date', @date_type, '', '',
772 'taxengine_request', 'text', 'NULL', '', '', '',
775 'void_date', @date_type, '', '',
776 'reason', 'varchar', 'NULL', $char_d, '', '',
777 'reasonnum', 'int', 'NULL', '', '', '',
778 'void_usernum', 'int', 'NULL', '', '', '',
780 'primary_key' => 'invnum',
781 'unique' => [ [ 'custnum', 'agent_invid' ] ], #agentnum? huh
782 'index' => [ ['custnum'], ['_date'], ['statementnum'],
783 ['agent_invid'], [ 'void_usernum' ],
786 { columns => [ 'custnum' ],
787 table => 'cust_main',
789 { columns => [ 'statementnum' ],
790 table => 'cust_statement', #_void? both?
792 { columns => [ 'reasonnum' ],
795 { columns => [ 'void_usernum' ],
796 table => 'access_user',
797 references => [ 'usernum' ],
802 #for importing invoices from a legacy system for display purposes only
803 # no effect upon balance
804 'legacy_cust_bill' => {
806 'legacyinvnum', 'serial', '', '', '', '',
807 'legacyid', 'varchar', 'NULL', $char_d, '', '',
808 'custnum', 'int', '', '', '', '',
809 '_date', @date_type, '', '',
810 'charged', @money_type, '', '',
811 'currency', 'char', 'NULL', 3, '', '',
812 'content_pdf', 'blob', 'NULL', '', '', '',
813 'content_html', 'text', 'NULL', '', '', '',
814 'locale', 'varchar', 'NULL', 16, '', '',
816 'primary_key' => 'legacyinvnum',
818 'index' => [ ['legacyid', 'custnum', 'locale' ], ],
820 { columns => [ 'custnum' ],
821 table => 'cust_main',
826 'legacy_cust_history' => {
828 'legacyhistorynum', 'serial', '', '', '', '',
829 'custnum', 'int', '', '', '', '',
830 'history_action', 'varchar', '', $char_d, '', '',
831 'history_date', @date_type, '', '',
832 'history_usernum', 'int', 'NULL', '', '', '',
833 'item', 'varchar', 'NULL', $char_d, '', '',
834 'description', 'varchar', 'NULL', 2*$char_d, '', '',
835 'change_data', 'text', 'NULL', '', '', '',
837 'primary_key' => 'legacyhistorynum',
839 'index' => [ ['custnum'], ['history_date'], ],
841 { columns => [ 'custnum' ],
842 table => 'cust_main',
844 { columns => [ 'history_usernum' ],
845 table => 'access_user',
846 references => [ 'usernum' ],
851 'cust_statement' => {
853 'statementnum', 'serial', '', '', '', '',
854 'custnum', 'int', '', '', '', '',
855 '_date', @date_type, '', '',
857 'primary_key' => 'statementnum',
859 'index' => [ ['custnum'], ['_date'], ],
861 { columns => [ 'custnum' ],
862 table => 'cust_main',
869 'eventpart', 'serial', '', '', '', '',
870 'agentnum', 'int', 'NULL', '', '', '',
871 'event', 'varchar', '', $char_d, '', '',
872 'eventtable', 'varchar', '', $char_d, '', '',
873 'check_freq', 'varchar', 'NULL', $char_d, '', '',
874 'weight', 'int', '', '', '', '',
875 'action', 'varchar', '', $char_d, '', '',
876 'disabled', 'char', 'NULL', 1, '', '',
878 'primary_key' => 'eventpart',
880 'index' => [ ['agentnum'], ['eventtable'], ['check_freq'],
884 { columns => [ 'agentnum' ],
890 'part_event_option' => {
892 'optionnum', 'serial', '', '', '', '',
893 'eventpart', 'int', '', '', '', '',
894 'optionname', 'varchar', '', $char_d, '', '',
895 'optionvalue', 'text', 'NULL', '', '', '',
897 'primary_key' => 'optionnum',
899 'index' => [ [ 'eventpart' ], [ 'optionname' ] ],
901 { columns => [ 'eventpart' ],
902 table => 'part_event',
907 'part_event_condition' => {
909 'eventconditionnum', 'serial', '', '', '', '',
910 'eventpart', 'int', '', '', '', '',
911 'conditionname', 'varchar', '', $char_d, '', '',
913 'primary_key' => 'eventconditionnum',
915 'index' => [ [ 'eventpart' ], [ 'conditionname' ] ],
917 { columns => [ 'eventpart' ],
918 table => 'part_event',
923 'part_event_condition_option' => {
925 'optionnum', 'serial', '', '', '', '',
926 'eventconditionnum', 'int', '', '', '', '',
927 'optionname', 'varchar', '', $char_d, '', '',
928 'optionvalue', 'text', 'NULL', '', '', '',
930 'primary_key' => 'optionnum',
932 'index' => [ [ 'eventconditionnum' ], [ 'optionname' ] ],
934 { columns => [ 'eventconditionnum' ],
935 table => 'part_event_condition',
940 'part_event_condition_option_option' => {
942 'optionoptionnum', 'serial', '', '', '', '',
943 'optionnum', 'int', '', '', '', '',
944 'optionname', 'varchar', '', $char_d, '', '',
945 'optionvalue', 'text', 'NULL', '', '', '',
947 'primary_key' => 'optionoptionnum',
949 'index' => [ [ 'optionnum' ], [ 'optionname' ] ],
951 { columns => [ 'optionnum' ],
952 table => 'part_event_condition_option',
959 'eventnum', 'serial', '', '', '', '',
960 'eventpart', 'int', '', '', '', '',
961 'tablenum', 'int', '', '', '', '',
962 '_date', @date_type, '', '',
963 'status', 'varchar', '', $char_d, '', '',
964 'statustext', 'text', 'NULL', '', '', '',
965 'no_action', 'char', 'NULL', 1, '', '',
967 'primary_key' => 'eventnum',
968 #no... there are retries now #'unique' => [ [ 'eventpart', 'invnum' ] ],
970 'index' => [ ['eventpart'], ['tablenum'], ['status'],
971 ['statustext'], ['_date'], ['no_action'],
974 { columns => [ 'eventpart' ],
975 table => 'part_event',
980 'cust_event_fee' => {
982 'eventfeenum', 'serial', '', '', '', '',
983 'eventnum', 'int', '', '', '', '',
984 'billpkgnum', 'int', 'NULL', '', '', '',
985 'feepart', 'int', '', '', '', '',
986 'nextbill', 'char', 'NULL', 1, '', '',
988 'primary_key' => 'eventfeenum', # I'd rather just use eventnum
989 'unique' => [ [ 'billpkgnum' ], [ 'eventnum' ] ], # one-to-one link
990 'index' => [ [ 'feepart' ] ],
992 { columns => [ 'eventnum' ],
993 table => 'cust_event',
995 #{ columns => [ 'billpkgnum' ],
996 # table => 'cust_bill_pkg',
998 { columns => [ 'feepart' ],
1004 'cust_bill_pkg' => {
1006 'billpkgnum', 'serial', '', '', '', '',
1007 'invnum', 'int', '', '', '', '',
1008 'pkgnum', 'int', '', '', '', '',
1009 'pkgpart_override', 'int', 'NULL', '', '', '',
1010 'setup', @money_type, '', '',
1011 'unitsetup', @money_typen, '', '',
1012 'setup_billed_currency', 'char', 'NULL', 3, '', '',
1013 'setup_billed_amount', @money_typen, '', '',
1014 'recur', @money_type, '', '',
1015 'unitrecur', @money_typen, '', '',
1016 'recur_billed_currency', 'char', 'NULL', 3, '', '',
1017 'recur_billed_amount', @money_typen, '', '',
1018 'sdate', @date_type, '', '',
1019 'edate', @date_type, '', '',
1020 'itemdesc', 'varchar', 'NULL', $char_d, '', '',
1021 'itemcomment', 'varchar', 'NULL', $char_d, '', '',
1022 'section', 'varchar', 'NULL', $char_d, '', '',
1023 'freq', 'varchar', 'NULL', $char_d, '', '',
1024 'quantity', 'int', 'NULL', '', '', '',
1025 'hidden', 'char', 'NULL', 1, '', '',
1026 'feepart', 'int', 'NULL', '', '', '',
1028 'primary_key' => 'billpkgnum',
1030 'index' => [ ['invnum'], [ 'pkgnum' ], [ 'itemdesc' ], ],
1032 { columns => [ 'invnum' ],
1033 table => 'cust_bill',
1035 #pkgnum 0 and -1 are used for special things
1036 #{ columns => [ 'pkgnum' ],
1037 # table => 'cust_pkg',
1039 { columns => [ 'pkgpart_override' ],
1040 table => 'part_pkg',
1041 references => [ 'pkgpart' ],
1043 { columns => [ 'feepart' ],
1044 table => 'part_fee',
1049 'cust_bill_pkg_detail' => {
1051 'detailnum', 'serial', '', '', '', '',
1052 # bigserial? this table will eventually be as big as cdr...
1053 'billpkgnum', 'int', 'NULL', '', '', '', # should not be nullable
1054 'pkgnum', 'int', 'NULL', '', '', '', # deprecated
1055 'invnum', 'int', 'NULL', '', '', '', # deprecated
1056 'amount', 'decimal', 'NULL', '10,4', '', '',
1057 'format', 'char', 'NULL', 1, '', '',
1058 'classnum', 'int', 'NULL', '', '', '',
1059 'duration', 'int', 'NULL', '', 0, '',
1060 'phonenum', 'varchar', 'NULL', 255, '', '', # has to hold a service label
1061 'accountcode', 'varchar', 'NULL', 32, '', '',
1062 'startdate', @date_type, '', '',
1063 'regionname', 'varchar', 'NULL', $char_d, '', '',
1064 'detail', 'varchar', '', 255, '', '',
1066 'primary_key' => 'detailnum',
1068 'index' => [ [ 'billpkgnum' ], [ 'classnum' ],
1069 [ 'pkgnum', 'invnum' ],
1072 { columns => [ 'billpkgnum' ],
1073 table => 'cust_bill_pkg',
1075 #{ columns => [ 'pkgnum' ],
1076 # table => 'cust_pkg',
1078 #{ columns => [ 'invnum' ],
1079 # table => 'cust_bill',
1081 { columns => [ 'classnum' ],
1082 table => 'usage_class',
1087 'cust_bill_pkg_display' => {
1089 'billpkgdisplaynum', 'serial', '', '', '', '',
1090 'billpkgnum', 'int', '', '', '', '',
1091 'section', 'varchar', 'NULL', $char_d, '', '',
1092 #'unitsetup', @money_typen, '', '', #override the linked real one?
1093 #'unitrecur', @money_typen, '', '', #this too?
1094 'post_total', 'char', 'NULL', 1, '', '',
1095 'type', 'char', 'NULL', 1, '', '',
1096 'summary', 'char', 'NULL', 1, '', '',
1098 'primary_key' => 'billpkgdisplaynum',
1100 'index' => [ ['billpkgnum'], ],
1102 { columns => [ 'billpkgnum' ],
1103 table => 'cust_bill_pkg',
1108 'cust_bill_pkg_fee' => {
1110 'billpkgfeenum', 'serial', '', '', '', '',
1111 'billpkgnum', 'int', '', '', '', '',
1112 'base_invnum', 'int', '', '', '', '',
1113 'base_billpkgnum', 'int', 'NULL', '', '', '',
1114 'amount', @money_type, '', '',
1116 'primary_key' => 'billpkgfeenum',
1118 'index' => [ ['billpkgnum'],
1120 ['base_billpkgnum'],
1123 { columns => [ 'billpkgnum' ],
1124 table => 'cust_bill_pkg',
1129 'cust_bill_pkg_fee_void' => {
1131 'billpkgfeenum', 'serial', '', '', '', '',
1132 'billpkgnum', 'int', '', '', '', '',
1133 'base_invnum', 'int', '', '', '', '',
1134 'base_billpkgnum', 'int', 'NULL', '', '', '',
1135 'amount', @money_type, '', '',
1137 'primary_key' => 'billpkgfeenum',
1139 'index' => [ ['billpkgnum'],
1141 ['base_billpkgnum'],
1144 { columns => [ 'billpkgnum' ],
1145 table => 'cust_bill_pkg_void',
1151 'cust_bill_pkg_tax_location' => {
1153 'billpkgtaxlocationnum', 'serial', '', '', '', '',
1154 'billpkgnum', 'int', '', '', '', '',
1155 'taxnum', 'int', '', '', '', '',
1156 'taxtype', 'varchar', '', $char_d, '', '',
1157 'pkgnum', 'int', '', '', '', '', #redundant
1158 'locationnum', 'int', '', '', '', '', #redundant
1159 'amount', @money_type, '', '',
1160 'currency', 'char', 'NULL', 3, '', '',
1161 'taxable_billpkgnum', 'int', 'NULL', '', '', '',
1163 'primary_key' => 'billpkgtaxlocationnum',
1165 'index' => [ [ 'billpkgnum' ],
1169 [ 'taxable_billpkgnum' ],
1172 { columns => [ 'billpkgnum' ],
1173 table => 'cust_bill_pkg',
1175 #{ columns => [ 'pkgnum' ],
1176 # table => 'cust_pkg',
1177 #}, # taxes can apply to fees
1178 { columns => [ 'locationnum' ],
1179 table => 'cust_location',
1181 #{ columns => [ 'taxable_billpkgnum' ],
1182 # table => 'cust_bill_pkg',
1183 # references => [ 'billpkgnum' ],
1188 'cust_bill_pkg_tax_rate_location' => {
1190 'billpkgtaxratelocationnum', 'serial', '', '', '', '',
1191 'billpkgnum', 'int', '', '', '', '',
1192 'taxnum', 'int', '', '', '', '',
1193 'taxtype', 'varchar', '', $char_d, '', '',
1194 'locationtaxid', 'varchar', 'NULL', $char_d, '', '',
1195 'taxratelocationnum', 'int', '', '', '', '',
1196 'amount', @money_type, '', '',
1197 'currency', 'char', 'NULL', 3, '', '',
1198 'taxable_billpkgnum', 'int', 'NULL', '', '', '',
1199 'taxclass', 'varchar', 'NULL', 10, '', '',
1201 'primary_key' => 'billpkgtaxratelocationnum',
1203 'index' => [ ['billpkgnum'], ['taxnum'], ['taxratelocationnum'],
1204 ['taxable_billpkgnum'],
1207 { columns => [ 'billpkgnum' ],
1208 table => 'cust_bill_pkg',
1210 { columns => [ 'taxratelocationnum' ],
1211 table => 'tax_rate_location',
1213 #{ columns => [ 'taxable_billpkgnum' ],
1214 # table => 'cust_bill_pkg',
1215 # references => [ 'billpkgnum' ],
1220 'cust_bill_pkg_void' => {
1222 'billpkgnum', 'int', '', '', '', '',
1223 'invnum', 'int', '', '', '', '',
1224 'pkgnum', 'int', '', '', '', '',
1225 'pkgpart_override', 'int', 'NULL', '', '', '',
1226 'setup', @money_type, '', '',
1227 'recur', @money_type, '', '',
1228 #XXX a currency for a line item? or just one for the entire invoice
1229 #'currency', 'char', 'NULL', 3, '', '',
1230 'sdate', @date_type, '', '',
1231 'edate', @date_type, '', '',
1232 'itemdesc', 'varchar', 'NULL', $char_d, '', '',
1233 'itemcomment', 'varchar', 'NULL', $char_d, '', '',
1234 'section', 'varchar', 'NULL', $char_d, '', '',
1235 'freq', 'varchar', 'NULL', $char_d, '', '',
1236 'quantity', 'int', 'NULL', '', '', '',
1237 'unitsetup', @money_typen, '', '',
1238 'unitrecur', @money_typen, '', '',
1239 'hidden', 'char', 'NULL', 1, '', '',
1240 'feepart', 'int', 'NULL', '', '', '',
1242 'void_date', @date_type, '', '',
1243 'reason', 'varchar', 'NULL', $char_d, '', '',
1244 'reasonnum', 'int', 'NULL', '', '', '',
1245 'void_usernum', 'int', 'NULL', '', '', '',
1247 'primary_key' => 'billpkgnum',
1249 'index' => [ ['invnum'], ['pkgnum'], ['itemdesc'],
1253 { columns => [ 'invnum' ],
1254 table => 'cust_bill_void',
1256 { columns => [ 'reasonnum' ],
1259 #pkgnum 0 and -1 are used for special things
1260 #{ columns => [ 'pkgnum' ],
1261 # table => 'cust_pkg',
1263 { columns => [ 'pkgpart_override' ],
1264 table => 'part_pkg',
1265 references => [ 'pkgpart' ],
1267 { columns => [ 'void_usernum' ],
1268 table => 'access_user',
1269 references => [ 'usernum' ],
1274 'cust_bill_pkg_detail_void' => {
1276 'detailnum', 'int', '', '', '', '',
1277 'billpkgnum', 'int', 'NULL', '', '', '', # should not be nullable
1278 'pkgnum', 'int', 'NULL', '', '', '', # deprecated
1279 'invnum', 'int', 'NULL', '', '', '', # deprecated
1280 'amount', 'decimal', 'NULL', '10,4', '', '',
1281 'format', 'char', 'NULL', 1, '', '',
1282 'classnum', 'int', 'NULL', '', '', '',
1283 'duration', 'int', 'NULL', '', 0, '',
1284 'phonenum', 'varchar', 'NULL', 255, '', '',
1285 'accountcode', 'varchar', 'NULL', 32, '', '',
1286 'startdate', @date_type, '', '',
1287 'regionname', 'varchar', 'NULL', $char_d, '', '',
1288 'detail', 'varchar', '', 255, '', '',
1290 'primary_key' => 'detailnum',
1292 'index' => [ ['billpkgnum'], ['classnum'], ['pkgnum', 'invnum'] ],
1294 { columns => [ 'billpkgnum' ],
1295 table => 'cust_bill_pkg_void',
1297 #{ columns => [ 'pkgnum' ],
1298 # table => 'cust_pkg',
1300 #{ columns => [ 'invnum' ],
1301 # table => 'cust_bill',
1303 { columns => [ 'classnum' ],
1304 table => 'usage_class',
1309 'cust_bill_pkg_display_void' => {
1311 'billpkgdisplaynum', 'int', '', '', '', '',
1312 'billpkgnum', 'int', '', '', '', '',
1313 'section', 'varchar', 'NULL', $char_d, '', '',
1314 #'unitsetup', @money_typen, '', '', #override the linked real one?
1315 #'unitrecur', @money_typen, '', '', #this too?
1316 'post_total', 'char', 'NULL', 1, '', '',
1317 'type', 'char', 'NULL', 1, '', '',
1318 'summary', 'char', 'NULL', 1, '', '',
1320 'primary_key' => 'billpkgdisplaynum',
1322 'index' => [ ['billpkgnum'], ],
1324 { columns => [ 'billpkgnum' ],
1325 table => 'cust_bill_pkg_void',
1330 'cust_bill_pkg_tax_location_void' => {
1332 'billpkgtaxlocationnum', 'int', '', '', '', '',
1333 'billpkgnum', 'int', '', '', '', '',
1334 'taxnum', 'int', '', '', '', '',
1335 'taxtype', 'varchar', '', $char_d, '', '',
1336 'pkgnum', 'int', '', '', '', '',
1337 'locationnum', 'int', '', '', '', '', #redundant?
1338 'amount', @money_type, '', '',
1339 'currency', 'char', 'NULL', 3, '', '',
1340 'taxable_billpkgnum', 'int', 'NULL', '', '', '',
1342 'primary_key' => 'billpkgtaxlocationnum',
1344 'index' => [ ['billpkgnum'], ['taxnum'], ['pkgnum'],
1348 { columns => [ 'billpkgnum' ],
1349 table => 'cust_bill_pkg_void',
1351 { columns => [ 'locationnum' ],
1352 table => 'cust_location',
1354 #{ columns => [ 'taxable_billpkgnum' ],
1355 # table => 'cust_bill_pkg_void',
1356 # references => [ 'billpkgnum' ],
1361 'cust_bill_pkg_tax_rate_location_void' => {
1363 'billpkgtaxratelocationnum', 'int', '', '', '', '',
1364 'billpkgnum', 'int', '', '', '', '',
1365 'taxnum', 'int', '', '', '', '',
1366 'taxtype', 'varchar', '', $char_d, '', '',
1367 'locationtaxid', 'varchar', 'NULL', $char_d, '', '',
1368 'taxratelocationnum', 'int', '', '', '', '',
1369 'amount', @money_type, '', '',
1370 'currency', 'char', 'NULL', 3, '', '',
1372 'primary_key' => 'billpkgtaxratelocationnum',
1374 'index' => [ ['billpkgnum'], ['taxnum'], ['taxratelocationnum'] ],
1376 { columns => [ 'billpkgnum' ],
1377 table => 'cust_bill_pkg_void',
1379 { columns => [ 'taxratelocationnum' ],
1380 table => 'tax_rate_location',
1387 'crednum', 'serial', '', '', '', '',
1388 'custnum', 'int', '', '', '', '',
1389 '_date', @date_type, '', '',
1390 'amount',@money_type, '', '',
1391 'currency', 'char', 'NULL', 3, '', '',
1392 'otaker', 'varchar', 'NULL', 32, '', '',
1393 'usernum', 'int', 'NULL', '', '', '',
1394 'reason', 'text', 'NULL', '', '', '',
1395 'reasonnum', 'int', 'NULL', '', '', '',
1396 'addlinfo', 'text', 'NULL', '', '', '',
1397 'closed', 'char', 'NULL', 1, '', '',
1398 'pkgnum', 'int', 'NULL', '', '','',#desired pkgnum for pkg-balances
1399 'eventnum', 'int', 'NULL', '', '','',#triggering event for commission
1400 'commission_agentnum', 'int', 'NULL', '', '', '', #
1401 'commission_salesnum', 'int', 'NULL', '', '', '', #
1402 'commission_pkgnum', 'int', 'NULL', '', '', '', #
1403 'commission_invnum', 'int', 'NULL', '', '', '',
1404 'credbatch', 'varchar', 'NULL', $char_d, '', '',
1406 'primary_key' => 'crednum',
1408 'index' => [ ['custnum'], ['_date'], ['usernum'], ['eventnum'],
1409 ['commission_salesnum'], ['credbatch'],
1412 { columns => [ 'custnum' ],
1413 table => 'cust_main',
1415 { columns => [ 'usernum' ],
1416 table => 'access_user',
1418 { columns => [ 'reasonnum' ],
1421 { columns => [ 'pkgnum' ],
1422 table => 'cust_pkg',
1424 { columns => [ 'eventnum' ],
1425 table => 'cust_event',
1427 { columns => [ 'commission_agentnum' ],
1429 references => [ 'agentnum' ],
1431 { columns => [ 'commission_salesnum' ],
1433 references => [ 'salesnum' ],
1435 { columns => [ 'commission_pkgnum' ],
1436 table => 'cust_pkg',
1437 references => [ 'pkgnum' ],
1439 { columns => [ 'commission_invnum' ],
1440 table => 'cust_bill',
1441 references => [ 'invnum' ],
1446 'cust_credit_void' => {
1448 'crednum', 'serial', '', '', '', '',
1449 'custnum', 'int', '', '', '', '',
1450 '_date', @date_type, '', '',
1451 'amount',@money_type, '', '',
1452 'currency', 'char', 'NULL', 3, '', '',
1453 'otaker', 'varchar', 'NULL', 32, '', '',
1454 'usernum', 'int', 'NULL', '', '', '',
1455 'reason', 'text', 'NULL', '', '', '',
1456 'reasonnum', 'int', 'NULL', '', '', '',
1457 'addlinfo', 'text', 'NULL', '', '', '',
1458 'closed', 'char', 'NULL', 1, '', '',
1459 'pkgnum', 'int', 'NULL', '', '','',
1460 'eventnum', 'int', 'NULL', '', '','',
1461 'commission_agentnum', 'int', 'NULL', '', '', '',
1462 'commission_salesnum', 'int', 'NULL', '', '', '',
1463 'commission_pkgnum', 'int', 'NULL', '', '', '',
1464 'commission_invnum', 'int', 'NULL', '', '', '',
1466 'void_date', @date_type, '', '',
1467 'void_reason', 'varchar', 'NULL', $char_d, '', '',
1468 'void_reasonnum', 'int', 'NULL', '', '', '',
1469 'void_usernum', 'int', 'NULL', '', '', '',
1471 'primary_key' => 'crednum',
1473 'index' => [ ['custnum'], ['_date'], ['usernum'], ['eventnum'],
1474 ['commission_salesnum'],
1477 { columns => [ 'custnum' ],
1478 table => 'cust_main',
1480 { columns => [ 'usernum' ],
1481 table => 'access_user',
1483 { columns => [ 'reasonnum' ],
1486 { columns => [ 'pkgnum' ],
1487 table => 'cust_pkg',
1489 { columns => [ 'eventnum' ],
1490 table => 'cust_event',
1492 { columns => [ 'commission_agentnum' ],
1494 references => [ 'agentnum' ],
1496 { columns => [ 'commission_salesnum' ],
1498 references => [ 'salesnum' ],
1500 { columns => [ 'commission_pkgnum' ],
1501 table => 'cust_pkg',
1502 references => [ 'pkgnum' ],
1504 { columns => [ 'commission_invnum' ],
1505 table => 'cust_bill',
1506 references => [ 'invnum' ],
1508 { columns => [ 'void_reasonnum' ],
1510 references => [ 'reasonnum' ],
1512 { columns => [ 'void_usernum' ],
1513 table => 'access_user',
1514 references => [ 'usernum' ],
1520 'cust_credit_bill' => {
1522 'creditbillnum', 'serial', '', '', '', '',
1523 'crednum', 'int', '', '', '', '',
1524 'invnum', 'int', '', '', '', '',
1525 '_date', @date_type, '', '',
1526 'amount', @money_type, '', '',
1527 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances
1529 'primary_key' => 'creditbillnum',
1531 'index' => [ ['crednum'], ['invnum'] ],
1533 { columns => [ 'crednum' ],
1534 table => 'cust_credit',
1536 { columns => [ 'invnum' ],
1537 table => 'cust_bill',
1539 { columns => [ 'pkgnum' ],
1540 table => 'cust_pkg',
1545 'cust_credit_bill_pkg' => {
1547 'creditbillpkgnum', 'serial', '', '', '', '',
1548 'creditbillnum', 'int', '', '', '', '',
1549 'billpkgnum', 'int', '', '', '', '',
1550 'billpkgtaxlocationnum', 'int', 'NULL', '', '', '',
1551 'billpkgtaxratelocationnum', 'int', 'NULL', '', '', '',
1552 'amount', @money_type, '', '',
1553 'setuprecur', 'varchar', '', $char_d, '', '',
1554 'sdate', @date_type, '', '',
1555 'edate', @date_type, '', '',
1557 'primary_key' => 'creditbillpkgnum',
1559 'index' => [ [ 'creditbillnum' ],
1561 [ 'billpkgtaxlocationnum' ],
1562 [ 'billpkgtaxratelocationnum' ],
1565 { columns => [ 'creditbillnum' ],
1566 table => 'cust_credit_bill',
1568 { columns => [ 'billpkgnum' ],
1569 table => 'cust_bill_pkg',
1571 { columns => [ 'billpkgtaxlocationnum' ],
1572 table => 'cust_bill_pkg_tax_location',
1574 { columns => [ 'billpkgtaxratelocationnum' ],
1575 table => 'cust_bill_pkg_tax_rate_location',
1580 'cust_credit_source_bill_pkg' => {
1582 'creditsourcebillpkgnum', 'serial', '', '', '', '',
1583 'crednum', 'int', '', '', '', '',
1584 'billpkgnum', 'int', '', '', '', '',
1585 'amount', @money_type, '', '',
1586 'currency', 'char', 'NULL', 3, '', '',
1588 'primary_key' => 'creditsourcebillpkgnum',
1590 'index' => [ ['crednum'], ['billpkgnum'] ],
1592 { columns => ['billpkgnum'],
1593 table => 'cust_bill_pkg',
1595 { columns => ['crednum'],
1596 table => 'cust_credit',
1603 'custnum', 'serial', '', '', '', '',
1604 'agentnum', 'int', '', '', '', '',
1605 'salesnum', 'int', 'NULL', '', '', '',
1606 'agent_custid', 'varchar', 'NULL', $char_d, '', '',
1607 'classnum', 'int', 'NULL', '', '', '',
1608 'custbatch', 'varchar', 'NULL', $char_d, '', '',
1609 # 'titlenum', 'int', 'NULL', '', '', '',
1610 'last', 'varchar', '', 2*$char_d, '', '',
1611 # 'middle', 'varchar', 'NULL', $char_d, '', '',
1612 'first', 'varchar', '', $char_d, '', '',
1613 'ss', 'varchar', 'NULL', 11, '', '',
1614 'stateid', 'varchar', 'NULL', $char_d, '', '',
1615 'stateid_state', 'varchar', 'NULL', $char_d, '', '',
1616 'national_id', 'varchar', 'NULL', $char_d, '', '',
1617 'birthdate' ,@date_type, '', '',
1618 'spouse_last', 'varchar', 'NULL', 2*$char_d, '', '',
1619 'spouse_first', 'varchar', 'NULL', $char_d, '', '',
1620 'spouse_birthdate', @date_type, '', '',
1621 'anniversary_date', @date_type, '', '',
1622 'signupdate',@date_type, '', '',
1623 'dundate', @date_type, '', '',
1624 'company', 'varchar', 'NULL', $char_d, '', '',
1625 'address1', 'varchar', 'NULL', $char_d, '', '',
1626 'address2', 'varchar', 'NULL', $char_d, '', '',
1627 'city', 'varchar', 'NULL', $char_d, '', '',
1628 'county', 'varchar', 'NULL', $char_d, '', '',
1629 'state', 'varchar', 'NULL', $char_d, '', '',
1630 'zip', 'varchar', 'NULL', 10, '', '',
1631 'country', 'char', 'NULL', 2, '', '',
1632 'latitude', 'decimal', 'NULL', '10,7', '', '',
1633 'longitude','decimal', 'NULL', '10,7', '', '',
1634 'coord_auto', 'char', 'NULL', 1, '', '',
1635 'addr_clean', 'char', 'NULL', 1, '', '',
1636 'daytime', 'varchar', 'NULL', 20, '', '',
1637 'night', 'varchar', 'NULL', 20, '', '',
1638 'fax', 'varchar', 'NULL', 12, '', '',
1639 'mobile', 'varchar', 'NULL', 12, '', '',
1640 'ship_last', 'varchar', 'NULL', 2*$char_d, '', '',
1641 # 'ship_middle', 'varchar', 'NULL', $char_d, '', '',
1642 'ship_first', 'varchar', 'NULL', $char_d, '', '',
1643 'ship_company', 'varchar', 'NULL', $char_d, '', '',
1644 'ship_address1', 'varchar', 'NULL', $char_d, '', '',
1645 'ship_address2', 'varchar', 'NULL', $char_d, '', '',
1646 'ship_city', 'varchar', 'NULL', $char_d, '', '',
1647 'ship_county', 'varchar', 'NULL', $char_d, '', '',
1648 'ship_state', 'varchar', 'NULL', $char_d, '', '',
1649 'ship_zip', 'varchar', 'NULL', 10, '', '',
1650 'ship_country', 'char', 'NULL', 2, '', '',
1651 'ship_latitude', 'decimal', 'NULL', '10,7', '', '',
1652 'ship_longitude','decimal', 'NULL', '10,7', '', '',
1653 'ship_coord_auto', 'char', 'NULL', 1, '', '',
1654 'ship_addr_clean', 'char', 'NULL', 1, '', '',
1655 'ship_daytime', 'varchar', 'NULL', 20, '', '',
1656 'ship_night', 'varchar', 'NULL', 20, '', '',
1657 'ship_fax', 'varchar', 'NULL', 12, '', '',
1658 'ship_mobile', 'varchar', 'NULL', 12, '', '',
1659 'currency', 'char', 'NULL', 3, '', '',
1661 'is_tokenized', 'char', 'NULL', '1', '', '',
1663 'geocode', 'varchar', 'NULL', 20, '', '',
1664 'censustract', 'varchar', 'NULL', 20, '', '', # 7 to save space?
1665 'censusyear', 'char', 'NULL', 4, '', '',
1666 'district', 'varchar', 'NULL', 20, '', '',
1667 'tax', 'char', 'NULL', 1, '', '',
1668 'otaker', 'varchar', 'NULL', 32, '', '',
1669 'usernum', 'int', 'NULL', '', '', '',
1670 'refnum', 'int', '', '', '', '',
1671 'referral_custnum', 'int', 'NULL', '', '', '',
1672 'comments', 'text', 'NULL', '', '', '',
1673 'spool_cdr','char', 'NULL', 1, '', '',
1674 'squelch_cdr','char', 'NULL', 1, '', '',
1675 'cdr_termination_percentage', 'decimal', 'NULL', '7,4', '', '',
1676 'invoice_terms', 'varchar', 'NULL', $char_d, '', '',
1677 'credit_limit', @money_typen, '', '',
1678 'credit_limit_currency', 'char', 'NULL', 3, '', '',
1679 'archived', 'char', 'NULL', 1, '', '',
1680 'email_csv_cdr', 'char', 'NULL', 1, '', '',
1681 'accountcode_cdr', 'char', 'NULL', 1, '', '',
1682 'billday', 'int', 'NULL', '', '', '',
1683 'prorate_day', 'int', 'NULL', '', '', '',
1684 'force_prorate_day', 'char', 'NULL', 1, '', '',
1685 'edit_subject', 'char', 'NULL', 1, '', '',
1686 'locale', 'varchar', 'NULL', 16, '', '',
1687 'calling_list_exempt', 'char', 'NULL', 1, '', '',
1688 'invoice_noemail', 'char', 'NULL', 1, '', '',
1689 'message_noemail', 'char', 'NULL', 1, '', '',
1690 'bill_locationnum', 'int', 'NULL', '', '', '',
1691 'ship_locationnum', 'int', 'NULL', '', '', '',
1692 'taxstatusnum', 'int', 'NULL', '', '', '',
1693 'complimentary', 'char', 'NULL', 1, '', '',
1694 'po_number', 'varchar', 'NULL', $char_d, '', '',
1695 'invoice_attn', 'varchar', 'NULL', $char_d, '', '',
1696 'invoice_ship_address', 'char', 'NULL', 1, '', '',
1697 'postal_invoice', 'char', 'NULL', 1, '', '',
1699 'primary_key' => 'custnum',
1700 'unique' => [ [ 'agentnum', 'agent_custid' ] ],
1701 #'index' => [ ['last'], ['company'] ],
1703 ['agentnum'], ['refnum'], ['classnum'], ['usernum'],
1705 [ 'referral_custnum' ],
1707 [ 'ship_locationnum' ],
1708 [ 'bill_locationnum' ],
1712 { columns => [ 'agentnum' ],
1715 { columns => [ 'salesnum' ],
1718 { columns => [ 'refnum' ],
1719 table => 'part_referral',
1721 { columns => [ 'classnum' ],
1722 table => 'cust_class',
1724 { columns => [ 'usernum' ],
1725 table => 'access_user',
1727 { columns => [ 'referral_custnum' ],
1728 table => 'cust_main',
1729 references => [ 'custnum' ],
1731 { columns => [ 'bill_locationnum' ],
1732 table => 'cust_location',
1733 references => [ 'locationnum' ],
1735 { columns => [ 'ship_locationnum' ],
1736 table => 'cust_location',
1737 references => [ 'locationnum' ],
1744 'custpaybynum', 'serial', '', '', '', '',
1745 'custnum', 'int', '', '', '', '',
1746 'weight', 'int', 'NULL', '', '', '',
1747 'payby', 'char', '', 4, '', '',
1748 'payinfo', 'varchar', 'NULL', 512, '', '',
1749 'paycardtype', 'varchar', 'NULL', $char_d, '', '',
1750 'paycvv', 'varchar', 'NULL', 512, '', '',
1751 'paymask', 'varchar', 'NULL', $char_d, '', '',
1752 #'paydate', @date_type, '', '',
1753 'paydate', 'varchar', 'NULL', 10, '', '',
1754 'paystart_month', 'int', 'NULL', '', '', '',
1755 'paystart_year', 'int', 'NULL', '', '', '',
1756 'payissue', 'varchar', 'NULL', 2, '', '',
1757 'payname', 'varchar', 'NULL', 2*$char_d, '', '',
1758 'paystate', 'varchar', 'NULL', $char_d, '', '',
1759 'paytype', 'varchar', 'NULL', $char_d, '', '',
1760 'payip', 'varchar', 'NULL', 15, '', '',
1761 'locationnum', 'int', 'NULL', '', '', '',
1762 'is_tokenized', 'char', 'NULL', 1, '', '',
1764 'primary_key' => 'custpaybynum',
1766 'index' => [ [ 'custnum' ], [ 'is_tokenized' ] ],
1768 { columns => [ 'custnum' ],
1769 table => 'cust_main',
1771 { columns => [ 'locationnum' ],
1772 table => 'cust_location',
1777 'contact_class' => {
1779 'classnum', 'serial', '', '', '', '',
1780 'classname', 'varchar', '', $char_d, '', '',
1781 'disabled', 'char', 'NULL', 1, '', '',
1783 'primary_key' => 'classnum',
1785 'index' => [ ['disabled'] ],
1790 'custcontactnum', 'serial', '', '', '', '',
1791 'custnum', 'int', '', '', '', '',
1792 'contactnum', 'int', '', '', '', '',
1793 'classnum', 'int', 'NULL', '', '', '',
1794 'comment', 'varchar', 'NULL', 255, '', '',
1795 'selfservice_access', 'char', 'NULL', 1, '', '',
1796 'invoice_dest', 'char', 'NULL', 1, '', '', # Y or NULL
1797 'message_dest', 'char', 'NULL', 1, '', '', # Y or NULL
1799 'primary_key' => 'custcontactnum',
1800 'unique' => [ [ 'custnum', 'contactnum' ], ],
1801 'index' => [ [ 'custnum' ], [ 'contactnum' ], ],
1803 { columns => [ 'custnum' ],
1804 table => 'cust_main',
1806 { columns => [ 'contactnum' ],
1809 { columns => [ 'classnum' ],
1810 table => 'contact_class',
1815 'prospect_contact' => {
1817 'prospectcontactnum', 'serial', '', '', '', '',
1818 'prospectnum', 'int', '', '', '', '',
1819 'contactnum', 'int', '', '', '', '',
1820 'classnum', 'int', 'NULL', '', '', '',
1821 'comment', 'varchar', 'NULL', 255, '', '',
1823 'primary_key' => 'prospectcontactnum',
1824 'unique' => [ [ 'prospectnum', 'contactnum' ], ],
1825 'index' => [ [ 'prospectnum' ], [ 'contactnum' ], ],
1827 { columns => [ 'prospectnum' ],
1828 table => 'prospect_main',
1830 { columns => [ 'contactnum' ],
1833 { columns => [ 'classnum' ],
1834 table => 'contact_class',
1841 'contactnum', 'serial', '', '', '', '',
1842 'prospectnum', 'int', 'NULL', '', '', '', #deprecated, now prospect_contact table
1843 'custnum', 'int', 'NULL', '', '', '', #deprecated, now cust_contact table
1844 'locationnum', 'int', 'NULL', '', '', '', #not yet
1845 'classnum', 'int', 'NULL', '', '', '', #deprecated, now prospect_contact or cust_contact
1846 # 'titlenum', 'int', 'NULL', '', '', '', #eg Mr. Mrs. Dr. Rev.
1847 'last', 'varchar', '', $char_d, '', '',
1848 # 'middle', 'varchar', 'NULL', $char_d, '', '',
1849 'first', 'varchar', '', $char_d, '', '',
1850 'title', 'varchar', 'NULL', $char_d, '', '', #eg Head Bottle Washer
1851 'comment', 'varchar', 'NULL', 255, '', '', #depredated, now prospect_contact or cust_contact
1852 'selfservice_access', 'char', 'NULL', 1, '', '', #deprecated, now cust_contact
1853 '_password', 'varchar', 'NULL', $char_d, '', '',
1854 '_password_encoding', 'varchar', 'NULL', $char_d, '', '',
1855 'disabled', 'char', 'NULL', 1, '', '',
1856 'invoice_dest', 'char', 'NULL', 1, '', '',
1858 'primary_key' => 'contactnum',
1860 'index' => [ [ 'prospectnum' ], [ 'custnum' ], [ 'locationnum' ],
1861 [ 'last' ], [ 'first' ],
1864 { columns => [ 'prospectnum' ],
1865 table => 'prospect_main',
1867 { columns => [ 'custnum' ],
1868 table => 'cust_main',
1870 { columns => [ 'locationnum' ],
1871 table => 'cust_location',
1873 { columns => [ 'classnum' ],
1874 table => 'contact_class',
1879 'contact_phone' => {
1881 'contactphonenum', 'serial', '', '', '', '',
1882 'contactnum', 'int', '', '', '', '',
1883 'phonetypenum', 'int', '', '', '', '',
1884 'countrycode', 'varchar', '', 3, '', '',
1885 'phonenum', 'varchar', '', 14, '', '',
1886 'extension', 'varchar', 'NULL', 7, '', '',
1887 #?#'comment', 'varchar', '', $char_d, '', '',
1889 'primary_key' => 'contactphonenum',
1893 { columns => [ 'contactnum' ],
1896 { columns => [ 'phonetypenum' ],
1897 table => 'phone_type',
1904 'phonetypenum', 'serial', '', '', '', '',
1905 'typename', 'varchar', '', $char_d, '', '',
1906 'weight', 'int', '', '', '', '',
1908 'primary_key' => 'phonetypenum',
1909 'unique' => [ [ 'typename' ], ],
1913 'contact_email' => {
1915 'contactemailnum', 'serial', '', '', '', '',
1916 'contactnum', 'int', '', '', '', '',
1917 'emailaddress', 'varchar', '', $char_d, '', '',
1919 'primary_key' => 'contactemailnum',
1920 #'unique' => [ [ 'contactnum', 'emailaddress' ], ],
1921 'unique' => [ [ 'emailaddress' ], ],
1924 { columns => [ 'contactnum' ],
1930 'prospect_main' => {
1932 'prospectnum', 'serial', '', '', '', '',
1933 'agentnum', 'int', '', '', '', '',
1934 'company', 'varchar', 'NULL', $char_d, '', '',
1935 'add_date', @date_type, '', '',
1936 'disabled', 'char', 'NULL', 1, '', '',
1937 'custnum', 'int', 'NULL', '', '', '',
1938 'refnum', 'int', 'NULL', '', '', '',
1939 'taxstatusnum', 'int', 'NULL', '', '', '',
1941 'primary_key' => 'prospectnum',
1943 'index' => [ ['company'], ['agentnum'], ['disabled'], ['refnum'] ],
1945 { columns => [ 'agentnum' ],
1948 { columns => [ 'custnum' ],
1949 table => 'cust_main',
1951 { columns => [ 'refnum' ],
1952 table => 'part_referral',
1960 'quotationnum', 'serial', '', '', '', '',
1961 'prospectnum', 'int', 'NULL', '', '', '',
1962 'custnum', 'int', 'NULL', '', '', '',
1963 '_date', @date_type, '', '',
1964 'disabled', 'char', 'NULL', 1, '', '',
1965 'usernum', 'int', 'NULL', '', '', '',
1966 'close_date', @date_type, '', '',
1967 'confidence', 'int', 'NULL', '', '', '',
1968 'quotation_description', 'varchar', 'NULL', '50', '', '',
1969 #'total', @money_type, '', '',
1970 #'quotation_term', 'varchar', 'NULL', $char_d, '', '',
1972 'primary_key' => 'quotationnum',
1974 'index' => [ [ 'prospectnum' ], ['custnum'], ['disabled'], ],
1976 { columns => [ 'prospectnum' ],
1977 table => 'prospect_main',
1979 { columns => [ 'custnum' ],
1980 table => 'cust_main',
1982 { columns => [ 'usernum' ],
1983 table => 'access_user',
1988 'quotation_pkg' => {
1990 'quotationpkgnum', 'serial', '', '', '', '',
1991 'quotationnum', 'int', 'NULL', '', '', '', #shouldn't be null,
1993 'pkgpart', 'int', '', '', '', '',
1994 'locationnum', 'int', 'NULL', '', '', '',
1995 'start_date', @date_type, '', '',
1996 'contract_end', @date_type, '', '',
1997 'quantity', 'int', 'NULL', '', '', '',
1998 'waive_setup', 'char', 'NULL', 1, '', '',
1999 'unitsetup', @money_typen, '', '',
2000 'unitrecur', @money_typen, '', '',
2002 'primary_key' => 'quotationpkgnum',
2004 'index' => [ ['pkgpart'], ],
2006 { columns => [ 'quotationnum' ],
2007 table => 'quotation',
2009 { columns => [ 'pkgpart' ],
2010 table => 'part_pkg',
2012 { columns => [ 'locationnum' ],
2013 table => 'cust_location',
2018 'quotation_pkg_detail' => {
2020 'detailnum', 'serial', '', '', '', '',
2021 'quotationpkgnum', 'int', '', '', '', '',
2022 'format', 'char', 'NULL', 1, '', '', # not used for anything
2023 'detail', 'varchar', '', 255, '', '',
2024 'copy_on_order', 'char', 'NULL', 1, '', '', # 'Y' to copy when ordering
2026 'primary_key' => 'detailnum',
2028 'index' => [ [ 'quotationpkgnum' ] ],
2030 { columns => [ 'quotationpkgnum' ],
2031 table => 'quotation_pkg',
2032 references => [ 'quotationpkgnum' ],
2037 'quotation_pkg_discount' => {
2039 'quotationpkgdiscountnum', 'serial', '', '', '', '',
2040 'quotationpkgnum', 'int', '', '', '', '',
2041 'discountnum', 'int', '', '', '', '',
2042 'setuprecur', 'varchar', 'NULL', $char_d, '', '',
2043 'amount', @money_typen, '', '',
2044 #'end_date', @date_type, '', '',
2046 'primary_key' => 'quotationpkgdiscountnum',
2048 'index' => [ [ 'quotationpkgnum' ], ], #[ 'discountnum' ] ],
2050 { columns => [ 'quotationpkgnum' ],
2051 table => 'quotation_pkg',
2053 { columns => [ 'discountnum' ],
2054 table => 'discount',
2059 'quotation_pkg_tax' => {
2061 'quotationtaxnum', 'serial', '', '', '', '',
2062 'quotationpkgnum', 'int', '', '', '', '',
2063 'itemdesc', 'varchar', '', $char_d, '', '',
2064 'setup_amount', @money_type, '', '',
2065 'recur_amount', @money_type, '', '',
2067 'primary_key' => 'quotationtaxnum',
2069 'index' => [ [ 'quotationpkgnum' ] ],
2071 { columns => [ 'quotationpkgnum' ],
2072 table => 'quotation_pkg',
2077 'cust_location' => { #'location' now that its prospects too, but...
2079 'locationnum', 'serial', '', '', '', '',
2080 'prospectnum', 'int', 'NULL', '', '', '',
2081 'custnum', 'int', 'NULL', '', '', '',
2082 'locationname', 'varchar', 'NULL', $char_d, '', '',
2083 'address1', 'varchar', '', $char_d, '', '',
2084 'address2', 'varchar', 'NULL', $char_d, '', '',
2085 'city', 'varchar', 'NULL', $char_d, '', '',
2086 'county', 'varchar', 'NULL', $char_d, '', '',
2087 'state', 'varchar', 'NULL', $char_d, '', '',
2088 'zip', 'varchar', 'NULL', 10, '', '',
2089 'latitude', 'decimal', 'NULL', '10,7', '', '',
2090 'longitude', 'decimal', 'NULL', '10,7', '', '',
2091 'coord_auto', 'char', 'NULL', 1, '', '',
2092 'addr_clean', 'char', 'NULL', 1, '', '',
2093 'country', 'char', '', 2, '', '',
2094 'geocode', 'varchar', 'NULL', 20, '', '',
2095 'district', 'varchar', 'NULL', 20, '', '',
2096 'censustract', 'varchar', 'NULL', 20, '', '',
2097 'censusyear', 'char', 'NULL', 4, '', '',
2099 #used only in... DSL qualification? .CA DSL qualification?
2100 'location_type', 'varchar', 'NULL', 20, '', '',
2101 'location_number', 'varchar', 'NULL', 20, '', '',
2102 'location_kind', 'char', 'NULL', 1, '', '',
2104 'incorporated', 'char', 'NULL', 1, '', '',
2105 'disabled', 'char', 'NULL', 1, '', '',
2107 'primary_key' => 'locationnum',
2109 'index' => [ [ 'prospectnum' ], [ 'custnum' ],
2110 [ 'county' ], [ 'state' ], [ 'country' ], [ 'zip' ],
2111 [ 'city' ], [ 'district' ]
2114 { columns => [ 'prospectnum' ],
2115 table => 'prospect_main',
2117 { columns => [ 'custnum' ],
2118 table => 'cust_main',
2123 'cust_main_invoice' => {
2125 'destnum', 'serial', '', '', '', '',
2126 'custnum', 'int', '', '', '', '',
2127 'dest', 'varchar', '', $char_d, '', '',
2129 'primary_key' => 'destnum',
2131 'index' => [ ['custnum'], ],
2133 { columns => [ 'custnum' ],
2134 table => 'cust_main',
2139 'cust_main_credit_limit' => {
2141 'creditlimitnum', 'serial', '', '', '', '',
2142 'custnum', 'int', '', '', '', '',
2143 '_date', @date_type, '', '',
2144 'amount', @money_typen, '', '',
2145 #'amount_currency', 'char', 'NULL', 3, '', '',
2146 'credit_limit', @money_typen, '', '',
2147 #'credit_limit_currency', 'char', 'NULL', 3, '', '',
2149 'primary_key' => 'creditlimitnum',
2151 'index' => [ ['custnum'], ],
2153 { columns => [ 'custnum' ],
2154 table => 'cust_main',
2159 'cust_main_note' => {
2161 'notenum', 'serial', '', '', '', '',
2162 'custnum', 'int', '', '', '', '',
2163 'classnum', 'int', 'NULL', '', '', '',
2164 '_date', @date_type, '', '',
2165 'otaker', 'varchar', 'NULL', 32, '', '',
2166 'usernum', 'int', 'NULL', '', '', '',
2167 'comments', 'text', 'NULL', '', '', '',
2168 'sticky', 'int', '', '', 0, '',
2170 'primary_key' => 'notenum',
2172 'index' => [ [ 'custnum' ], [ '_date' ], [ 'usernum' ], ],
2174 { columns => [ 'custnum' ],
2175 table => 'cust_main',
2177 { columns => [ 'classnum' ],
2178 table => 'cust_note_class',
2180 { columns => [ 'usernum' ],
2181 table => 'access_user',
2186 'cust_note_class' => {
2188 'classnum', 'serial', '', '', '', '',
2189 'classname', 'varchar', '', $char_d, '', '',
2190 'disabled', 'char', 'NULL', 1, '', '',
2192 'primary_key' => 'classnum',
2194 'index' => [ ['disabled'] ],
2197 'cust_category' => {
2199 'categorynum', 'serial', '', '', '', '',
2200 'categoryname', 'varchar', '', $char_d, '', '',
2201 'weight', 'int', 'NULL', '', '', '',
2202 'disabled', 'char', 'NULL', 1, '', '',
2204 'primary_key' => 'categorynum',
2206 'index' => [ ['disabled'] ],
2211 'classnum', 'serial', '', '', '', '',
2212 'classname', 'varchar', '', $char_d, '', '',
2213 'categorynum', 'int', 'NULL', '', '', '',
2214 'tax', 'char', 'NULL', 1, '', '',
2215 'disabled', 'char', 'NULL', 1, '', '',
2217 'primary_key' => 'classnum',
2219 'index' => [ ['disabled'] ],
2221 { columns => [ 'categorynum' ],
2222 table => 'cust_category',
2229 'custtagnum', 'serial', '', '', '', '',
2230 'custnum', 'int', '', '', '', '',
2231 'tagnum', 'int', '', '', '', '',
2233 'primary_key' => 'custtagnum',
2234 'unique' => [ [ 'custnum', 'tagnum' ] ],
2235 'index' => [ [ 'custnum' ] ],
2237 { columns => [ 'custnum' ],
2238 table => 'cust_main',
2240 { columns => [ 'tagnum' ],
2241 table => 'part_tag',
2248 'tagnum', 'serial', '', '', '', '',
2249 'tagname', 'varchar', '', $char_d, '', '',
2250 'tagdesc', 'varchar', 'NULL', $char_d, '', '',
2251 'tagcolor', 'varchar', 'NULL', 6, '', '',
2252 'by_default', 'char', 'NULL', 1, '', '',
2253 'disabled', 'char', 'NULL', 1, '', '',
2255 'primary_key' => 'tagnum',
2256 'unique' => [], #[ [ 'tagname' ] ], #?
2257 'index' => [ [ 'disabled' ] ],
2260 'cust_main_exemption' => {
2262 'exemptionnum', 'serial', '', '', '', '',
2263 'custnum', 'int', '', '', '', '',
2264 'taxname', 'varchar', '', $char_d, '', '',
2265 'exempt_number', 'varchar', 'NULL', $char_d, '', '',
2266 #start/end dates? for reporting?
2268 'primary_key' => 'exemptionnum',
2270 'index' => [ [ 'custnum' ] ],
2272 { columns => [ 'custnum' ],
2273 table => 'cust_main',
2278 'cust_tax_adjustment' => {
2280 'adjustmentnum', 'serial', '', '', '', '',
2281 'custnum', 'int', '', '', '', '',
2282 'taxname', 'varchar', '', $char_d, '', '',
2283 'amount', @money_type, '', '',
2284 'currency', 'char', 'NULL', 3, '', '',
2285 'comment', 'varchar', 'NULL', $char_d, '', '',
2286 'billpkgnum', 'int', 'NULL', '', '', '',
2287 #more? no cust_bill_pkg_tax_location?
2289 'primary_key' => 'adjustmentnum',
2291 'index' => [ [ 'custnum' ], [ 'billpkgnum' ] ],
2293 { columns => [ 'custnum' ],
2294 table => 'cust_main',
2296 { columns => [ 'billpkgnum' ],
2297 table => 'cust_bill_pkg',
2302 'cust_main_county' => { #district+city+county+state+country are checked
2303 #off the cust_main_county for validation and to
2304 #provide a tax rate.
2306 'taxnum', 'serial', '', '', '', '',
2307 'district', 'varchar', 'NULL', 20, '', '',
2308 'city', 'varchar', 'NULL', $char_d, '', '',
2309 'county', 'varchar', 'NULL', $char_d, '', '',
2310 'state', 'varchar', 'NULL', $char_d, '', '',
2311 'country', 'char', '', 2, '', '',
2312 'taxclass', 'varchar', 'NULL', $char_d, '', '',
2313 'exempt_amount', @money_type, '', '',
2314 'exempt_amount_currency', 'char', 'NULL', 3, '', '',
2315 'tax', 'real', '', '', '', '', #tax %
2316 'taxname', 'varchar', 'NULL', $char_d, '', '',
2317 'setuptax', 'char', 'NULL', 1, '', '', # Y = setup tax exempt
2318 'recurtax', 'char', 'NULL', 1, '', '', # Y = recur tax exempt
2319 'source', 'varchar', 'NULL', $char_d, '', '',
2321 'primary_key' => 'taxnum',
2323 # 'unique' => [ ['taxnum'], ['state', 'county'] ],
2324 'index' => [ [ 'district' ], [ 'city' ], [ 'county' ], [ 'state' ],
2332 'taxnum', 'serial', '', '', '', '',
2333 'geocode', 'varchar', 'NULL', $char_d, '', '',#cch provides 10 char
2334 'data_vendor', 'varchar', 'NULL', $char_d, '', '',#auto update source
2335 'location', 'varchar', 'NULL', $char_d, '', '',#provided by tax authority
2336 'taxclassnum', 'int', 'NULL', '', '', '',
2337 'effective_date', @date_type, '', '',
2338 'tax', @taxrate_type, '', '', # tax %
2339 'excessrate', @taxrate_typen, '', '', # second tax %
2340 'taxbase', @money_typen, '', '', # amount at first tax rate
2341 'taxmax', @money_typen, '', '', # maximum about at both rates
2342 'usetax', @taxrate_typen, '', '', # tax % when non-local
2343 'useexcessrate', @taxrate_typen, '', '', # second tax % when non-local
2344 'unittype', 'int', 'NULL', '', '', '', # for fee
2345 'fee', @taxrate_typen, '', '', # amount tax per unit
2346 'excessfee', @taxrate_typen, '', '', # second amount tax per unit
2347 'feebase', @taxrate_typen, '', '', # units taxed at first rate
2348 'feemax', @taxrate_typen, '', '', # maximum number of unit taxed
2349 'maxtype', 'int', 'NULL', '', '', '', # indicator of how thresholds accumulate
2350 'taxname', 'varchar', 'NULL', $char_d, '', '', # may appear on invoice
2351 'taxauth', 'int', 'NULL', '', '', '', # tax authority
2352 'basetype', 'int', 'NULL', '', '', '', # indicator of basis for tax
2353 'passtype', 'int', 'NULL', '', '', '', # indicator declaring how item should be shown
2354 'passflag', 'char', 'NULL', 1, '', '', # Y = required to list as line item, N = Prohibited
2355 'setuptax', 'char', 'NULL', 1, '', '', # Y = setup tax exempt
2356 'recurtax', 'char', 'NULL', 1, '', '', # Y = recur tax exempt
2357 'inoutcity', 'char', 'NULL', 1, '', '', # '', 'I', or 'O'
2358 'inoutlocal', 'char', 'NULL', 1, '', '', # '', 'I', or 'O'
2359 'manual', 'char', 'NULL', 1, '', '', # Y = manually edited
2360 'disabled', 'char', 'NULL', 1, '', '', # Y = tax disabled
2362 'primary_key' => 'taxnum',
2364 'index' => [ ['taxclassnum'], ['data_vendor', 'geocode'] ],
2366 { columns => [ 'taxclassnum' ],
2367 table => 'tax_class',
2372 'tax_rate_location' => {
2374 'taxratelocationnum', 'serial', '', '', '', '',
2375 'data_vendor', 'varchar', 'NULL', $char_d, '', '',
2376 'geocode', 'varchar', '', 20, '', '',
2377 'district', 'varchar', 'NULL', $char_d, '', '',
2378 'city', 'varchar', 'NULL', $char_d, '', '',
2379 'county', 'varchar', 'NULL', $char_d, '', '',
2380 'state', 'char', 'NULL', 2, '', '',
2381 'country', 'char', 'NULL', 2, '', '',
2382 'disabled', 'char', 'NULL', 1, '', '',
2384 'primary_key' => 'taxratelocationnum',
2386 'index' => [ [ 'data_vendor', 'geocode', 'disabled' ] ],
2389 'cust_tax_location' => {
2391 'custlocationnum', 'serial', '', '', '', '',
2392 'data_vendor', 'varchar', 'NULL', $char_d, '', '', # update source
2393 'city', 'varchar', 'NULL', $char_d, '', '',
2394 'postalcity', 'varchar', 'NULL', $char_d, '', '',
2395 'county', 'varchar', 'NULL', $char_d, '', '',
2396 'zip', 'char', 'NULL', 5, '', '',
2397 'state', 'char', '', 2, '', '',
2398 'country', 'char', 'NULL', 2, '', '', # should not actually be NULL, but cch...
2399 'ziphi', 'char', 'NULL', 10, '', '',
2400 'ziplo', 'char', 'NULL', 10, '', '',
2401 'plus4hi', 'char', 'NULL', 4, '', '',
2402 'plus4lo', 'char', 'NULL', 4, '', '',
2403 'default_location','char', 'NULL', 1, '', '', # Y = default for zip
2404 'cityflag', 'char', 'NULL', 1, '', '', # I(n)/O(out)/B(oth)/NULL
2405 'geocode', 'varchar', '', 20, '', '',
2407 'primary_key' => 'custlocationnum',
2409 'index' => [ [ 'zip', 'plus4lo', 'plus4hi', 'ziphi', 'ziplo' ] ],
2414 'taxclassnum', 'serial', '', '', '', '',
2415 'data_vendor', 'varchar', 'NULL', $char_d, '', '',
2416 'taxclass', 'varchar', '', $char_d, '', '',
2417 'description', 'varchar', '', 2*$char_d, '', '',
2419 'primary_key' => 'taxclassnum',
2420 'unique' => [ [ 'data_vendor', 'taxclass' ] ],
2426 'taxstatusnum', 'serial', '', '', '', '',
2427 'data_vendor', 'varchar', 'NULL', $char_d, '', '',
2428 'taxstatus', 'varchar', '', $char_d, '', '',
2429 'description', 'varchar', '', $char_d, '', '',
2431 'primary_key' => 'taxstatusnum',
2432 'unique' => [ [ 'data_vendor', 'taxstatus' ] ],
2436 'cust_pay_pending' => {
2438 'paypendingnum', 'serial', '', '', '', '',
2439 'custnum', 'int', 'NULL', '', '', '',
2440 'paid', @money_type, '', '',
2441 'currency', 'char', 'NULL', 3, '', '',
2442 '_date', @date_type, '', '',
2443 'payby', 'char', '', 4, '', '',
2444 'payinfo', 'varchar', 'NULL', 512, '', '',
2445 'paymask', 'varchar', 'NULL', $char_d, '', '',
2446 'paydate', 'varchar', 'NULL', 10, '', '',
2447 'recurring_billing', 'varchar', 'NULL', $char_d, '', '',
2448 'payunique', 'varchar', 'NULL', $char_d, '', '', #separate paybatch "unique" functions from current usage
2450 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances
2451 'status', 'varchar', '', $char_d, '', '',
2452 'session_id', 'varchar', 'NULL', 1024, '', '', # SHA-512-hex
2453 'statustext', 'text', 'NULL', '', '', '',
2454 'gatewaynum', 'int', 'NULL', '', '', '',
2455 #'cust_balance', @money_type, '', '',
2456 'paynum', 'int', 'NULL', '', '', '',
2457 'void_paynum', 'int', 'NULL', '', '', '',
2458 'jobnum', 'bigint', 'NULL', '', '', '',
2459 'invnum', 'int', 'NULL', '', '', '',
2460 'manual', 'char', 'NULL', 1, '', '',
2461 'discount_term','int', 'NULL', '', '', '',
2462 'failure_status','varchar','NULL', 16, '', '',
2463 'is_tokenized', 'char', 'NULL', 1, '', '',
2465 'primary_key' => 'paypendingnum',
2466 'unique' => [ [ 'payunique' ] ],
2467 'index' => [ [ 'custnum' ], [ 'status' ],
2468 ['paynum'], ['void_paynum'], ['jobnum'], ['invnum'],
2472 { columns => [ 'custnum' ],
2473 table => 'cust_main',
2475 { columns => [ 'pkgnum' ],
2476 table => 'cust_pkg',
2478 { columns => [ 'gatewaynum' ],
2479 table => 'payment_gateway',
2481 { columns => [ 'paynum' ],
2482 table => 'cust_pay',
2484 { columns => [ 'void_paynum' ],
2485 table => 'cust_pay_void',
2486 references => [ 'paynum' ],
2488 { columns => [ 'jobnum' ],
2491 { columns => [ 'invnum' ],
2492 table => 'cust_bill',
2499 'paynum', 'serial', '', '', '', '',
2500 'custnum', 'int', '', '', '', '',
2501 '_date', @date_type, '', '',
2502 'paid', @money_type, '', '',
2503 'currency', 'char', 'NULL', 3, '', '',
2504 'otaker', 'varchar', 'NULL', 32, '', '',
2505 'usernum', 'int', 'NULL', '', '', '',
2506 'payby', 'char', '', 4, '', '',
2507 'payinfo', 'varchar', 'NULL', 512, '', '',
2508 'paycardtype', 'varchar', 'NULL', $char_d, '', '',
2509 'paymask', 'varchar', 'NULL', $char_d, '', '',
2510 'paydate', 'varchar', 'NULL', 10, '', '',
2511 'paybatch', 'varchar', 'NULL', $char_d, '', '',#for auditing purposes
2512 'payunique', 'varchar', 'NULL', $char_d, '', '',#separate paybatch "unique" functions from current usage
2513 'closed', 'char', 'NULL', 1, '', '',
2514 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances
2515 'no_auto_apply', 'char', 'NULL', 1, '', '',
2516 'is_tokenized', 'char', 'NULL', 1, '', '',
2518 # cash/check deposit info fields
2519 'bank', 'varchar', 'NULL', $char_d, '', '',
2520 'depositor', 'varchar', 'NULL', $char_d, '', '',
2521 'account', 'varchar', 'NULL', 20, '', '',
2522 'teller', 'varchar', 'NULL', 20, '', '',
2524 'batchnum', 'int', 'NULL', '', '', '',#pay_batch foreign key
2526 # credit card/EFT fields (formerly in paybatch)
2527 'gatewaynum', 'int', 'NULL', '', '', '', # payment_gateway FK
2528 'processor', 'varchar', 'NULL', $char_d, '', '', # module name
2529 'auth', 'varchar', 'NULL', 16, '', '', # CC auth number
2530 'order_number','varchar', 'NULL', 256, '', '', # transaction number
2532 'primary_key' => 'paynum',
2533 #i guess not now, with cust_pay_pending, if we actually make it here, we _do_ want to record it# 'unique' => [ [ 'payunique' ] ],
2534 'index' => [ ['custnum'], ['paybatch'], ['payby'], ['_date'],
2535 ['usernum'], ['is_tokenized'],
2538 { columns => [ 'custnum' ],
2539 table => 'cust_main',
2541 { columns => [ 'usernum' ],
2542 table => 'access_user',
2544 { columns => [ 'pkgnum' ],
2545 table => 'cust_pkg',
2547 { columns => [ 'batchnum' ],
2548 table => 'pay_batch',
2550 { columns => [ 'gatewaynum' ],
2551 table => 'payment_gateway',
2556 'cust_pay_void' => {
2558 'paynum', 'int', '', '', '', '',
2559 'custnum', 'int', '', '', '', '',
2560 '_date', @date_type, '', '',
2561 'paid', @money_type, '', '',
2562 'currency', 'char', 'NULL', 3, '', '',
2563 'otaker', 'varchar', 'NULL', 32, '', '',
2564 'usernum', 'int', 'NULL', '', '', '',
2565 'payby', 'char', '', 4, '', '',
2566 'payinfo', 'varchar', 'NULL', 512, '', '',
2567 'paycardtype', 'varchar', 'NULL', $char_d, '', '',
2568 'paymask', 'varchar', 'NULL', $char_d, '', '',
2570 'paybatch', 'varchar', 'NULL', $char_d, '', '', #for auditing purposes.
2571 'closed', 'char', 'NULL', 1, '', '',
2572 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances
2573 'is_tokenized', 'char', 'NULL', 1, '', '',
2575 # cash/check deposit info fields
2576 'bank', 'varchar', 'NULL', $char_d, '', '',
2577 'depositor', 'varchar', 'NULL', $char_d, '', '',
2578 'account', 'varchar', 'NULL', 20, '', '',
2579 'teller', 'varchar', 'NULL', 20, '', '',
2580 'batchnum', 'int', 'NULL', '', '', '', #pay_batch foreign key
2582 # credit card/EFT fields (formerly in paybatch)
2583 'gatewaynum', 'int', 'NULL', '', '', '', # payment_gateway FK
2584 'processor', 'varchar', 'NULL', $char_d, '', '', # module name
2585 'auth', 'varchar', 'NULL', 16, '', '', # CC auth number
2586 'order_number','varchar', 'NULL', 256, '', '', # transaction number
2589 'void_date', @date_type, '', '',
2590 'reason', 'varchar', 'NULL', $char_d, '', '',
2591 'reasonnum', 'int', 'NULL', '', '', '',
2592 'void_usernum', 'int', 'NULL', '', '', '',
2594 'primary_key' => 'paynum',
2596 'index' => [ ['custnum'], ['usernum'], ['void_usernum'],
2600 { columns => [ 'custnum' ],
2601 table => 'cust_main',
2603 { columns => [ 'usernum' ],
2604 table => 'access_user',
2606 { columns => [ 'pkgnum' ],
2607 table => 'cust_pkg',
2609 { columns => [ 'batchnum' ],
2610 table => 'pay_batch',
2612 { columns => [ 'gatewaynum' ],
2613 table => 'payment_gateway',
2615 { columns => [ 'reasonnum' ],
2618 { columns => [ 'void_usernum' ],
2619 table => 'access_user',
2620 references => [ 'usernum' ],
2625 'cust_bill_pay' => {
2627 'billpaynum', 'serial', '', '', '', '',
2628 'invnum', 'int', '', '', '', '',
2629 'paynum', 'int', '', '', '', '',
2630 'amount', @money_type, '', '',
2631 '_date', @date_type, '', '',
2632 'pkgnum', 'int', 'NULL', '', '', '', #desired pkgnum for pkg-balances
2634 'primary_key' => 'billpaynum',
2636 'index' => [ [ 'paynum' ], [ 'invnum' ] ],
2638 { columns => [ 'invnum' ],
2639 table => 'cust_bill',
2641 { columns => [ 'paynum' ],
2642 table => 'cust_pay',
2644 { columns => [ 'pkgnum' ],
2645 table => 'cust_pkg',
2650 'cust_bill_pay_batch' => {
2652 'billpaynum', 'serial', '', '', '', '',
2653 'invnum', 'int', '', '', '', '',
2654 'paybatchnum', 'int', '', '', '', '',
2655 'amount', @money_type, '', '',
2656 '_date', @date_type, '', '',
2658 'primary_key' => 'billpaynum',
2660 'index' => [ [ 'paybatchnum' ], [ 'invnum' ] ],
2662 { columns => [ 'invnum' ],
2663 table => 'cust_bill',
2665 { columns => [ 'paybatchnum' ],
2666 table => 'cust_pay_batch',
2671 'cust_bill_pay_pkg' => {
2673 'billpaypkgnum', 'serial', '', '', '', '',
2674 'billpaynum', 'int', '', '', '', '',
2675 'billpkgnum', 'int', '', '', '', '',
2676 'billpkgtaxlocationnum', 'int', 'NULL', '', '', '',
2677 'billpkgtaxratelocationnum', 'int', 'NULL', '', '', '',
2678 'amount', @money_type, '', '',
2679 'setuprecur', 'varchar', '', $char_d, '', '',
2680 'sdate', @date_type, '', '',
2681 'edate', @date_type, '', '',
2683 'primary_key' => 'billpaypkgnum',
2685 'index' => [ [ 'billpaynum' ], [ 'billpkgnum' ], ],
2687 { columns => [ 'billpaynum' ],
2688 table => 'cust_bill_pay',
2690 { columns => [ 'billpkgnum' ],
2691 table => 'cust_bill_pkg',
2693 { columns => [ 'billpkgtaxlocationnum' ],
2694 table => 'cust_bill_pkg_tax_location',
2696 { columns => [ 'billpkgtaxratelocationnum' ],
2697 table => 'cust_bill_pkg_tax_rate_location',
2702 'pay_batch' => { #batches of payments to an external processor
2704 'batchnum', 'serial', '', '', '', '',
2705 'agentnum', 'int', 'NULL', '', '', '',
2706 'payby', 'char', '', 4, '', '', # CARD/CHEK
2707 'status', 'char', 'NULL', 1, '', '',
2708 'download', @date_type, '', '',
2709 'upload', @date_type, '', '',
2710 'title', 'varchar', 'NULL',255, '', '',
2711 'processor_id', 'varchar', 'NULL',255, '', '',
2713 'primary_key' => 'batchnum',
2717 { columns => [ 'agentnum' ],
2723 'cust_pay_batch' => { #list of customers in current CARD/CHEK batch
2725 'paybatchnum', 'serial', '', '', '', '',
2726 'batchnum', 'int', '', '', '', '',
2727 'invnum', 'int', '', '', '', '',
2728 'custnum', 'int', '', '', '', '',
2729 'last', 'varchar', '', $char_d, '', '',
2730 'first', 'varchar', '', $char_d, '', '',
2731 'address1', 'varchar', '', $char_d, '', '',
2732 'address2', 'varchar', 'NULL', $char_d, '', '',
2733 'city', 'varchar', 'NULL', $char_d, '', '',
2734 'state', 'varchar', 'NULL', $char_d, '', '',
2735 'zip', 'varchar', 'NULL', 10, '', '',
2736 'country', 'char', '', 2, '', '',
2737 'payby', 'char', '', 4, '', '',
2738 'payinfo', 'varchar', 'NULL', 512, '', '',
2739 #'paymask', 'varchar', 'NULL', $char_d, '', '',
2740 #'exp', @date_type, '', '',
2741 'exp', 'varchar', 'NULL', 11, '', '',
2742 'payname', 'varchar', 'NULL', $char_d, '', '',
2743 'paytype', 'varchar', 'NULL', $char_d, '', '',
2744 'amount', @money_type, '', '',
2745 'currency', 'char', 'NULL', 3, '', '',
2746 'status', 'varchar', 'NULL', $char_d, '', '',
2747 'failure_status','varchar', 'NULL', 16, '', '',
2748 'error_message', 'varchar', 'NULL', $char_d, '', '',
2749 'paycode', 'varchar', 'NULL', $char_d, '', '',
2751 'primary_key' => 'paybatchnum',
2753 'index' => [ ['batchnum'], ['invnum'], ['custnum'],['status'] ],
2755 { columns => [ 'batchnum' ],
2756 table => 'pay_batch',
2758 #{ columns => [ 'invnum' ],
2759 # table => 'cust_bill',
2761 { columns => [ 'custnum' ],
2762 table => 'cust_main',
2769 'formkey', 'varchar', '', 255, '', '',
2770 'formvalue', 'text', 'NULL', '', '', '',
2772 'primary_key' => 'formkey',
2779 'pkgnum', 'serial', '', '', '', '',
2780 'custnum', 'int', '', '', '', '',
2781 'pkgpart', 'int', '', '', '', '',
2782 'pkgbatch', 'varchar', 'NULL', $char_d, '', '',
2783 'contactnum', 'int', 'NULL', '', '', '',
2784 'locationnum', 'int', 'NULL', '', '', '',
2785 'otaker', 'varchar', 'NULL', 32, '', '',
2786 'usernum', 'int', 'NULL', '', '', '',
2787 'salesnum', 'int', 'NULL', '', '', '',
2788 'order_date', @date_type, '', '',
2789 'start_date', @date_type, '', '',
2790 'setup', @date_type, '', '',
2791 'bill', @date_type, '', '',
2792 'last_bill', @date_type, '', '',
2793 'susp', @date_type, '', '',
2794 'adjourn', @date_type, '', '',
2795 'resume', @date_type, '', '',
2796 'cancel', @date_type, '', '',
2797 'uncancel', @date_type, '', '',
2798 'uncancel_pkgnum', 'int', 'NULL', '', '', '',
2799 'expire', @date_type, '', '',
2800 'contract_end', @date_type, '', '',
2801 'dundate', @date_type, '', '',
2802 'change_date', @date_type, '', '',
2803 'change_pkgnum', 'int', 'NULL', '', '', '',
2804 'change_pkgpart', 'int', 'NULL', '', '', '',
2805 'change_locationnum', 'int', 'NULL', '', '', '',
2806 'change_custnum', 'int', 'NULL', '', '', '',
2807 'main_pkgnum', 'int', 'NULL', '', '', '',
2808 'pkglinknum', 'int', 'NULL', '', '', '',
2809 'no_auto', 'char', 'NULL', 1, '', '',
2810 'quantity', 'int', 'NULL', '', '', '',
2811 'agent_pkgid', 'varchar', 'NULL', $char_d, '', '',
2812 'waive_setup', 'char', 'NULL', 1, '', '',
2813 'recur_show_zero', 'char', 'NULL', 1, '', '',
2814 'setup_show_zero', 'char', 'NULL', 1, '', '',
2815 'change_to_pkgnum', 'int', 'NULL', '', '', '',
2816 'separate_bill', 'char', 'NULL', 1, '', '',
2818 'primary_key' => 'pkgnum',
2820 'index' => [ ['custnum'], ['pkgpart'], ['pkgbatch'],
2821 ['locationnum'], ['usernum'], ['agent_pkgid'],
2822 ['order_date'], [ 'start_date' ], ['setup'], ['bill'],
2823 ['last_bill'], ['susp'], ['adjourn'], ['resume'],
2824 ['cancel'], ['expire'], ['contract_end'],
2829 #['uncancel_pkgnum'],
2830 #['change_pkgnum'], ['change_locationnum'],
2831 #['change_custnum'],
2833 #['pkglinknum'], ['change_to_pkgnum'],
2836 { columns => [ 'custnum' ],
2837 table => 'cust_main',
2839 { columns => [ 'pkgpart' ],
2840 table => 'part_pkg',
2842 { columns => [ 'contactnum' ],
2845 { columns => [ 'locationnum' ],
2846 table => 'cust_location',
2848 { columns => [ 'usernum' ],
2849 table => 'access_user',
2851 { columns => [ 'salesnum' ],
2854 { columns => [ 'uncancel_pkgnum' ],
2855 table => 'cust_pkg',
2856 references => [ 'pkgnum' ],
2858 { columns => [ 'change_pkgnum' ],
2859 table => 'cust_pkg',
2860 references => [ 'pkgnum' ],
2862 { columns => [ 'change_pkgpart' ],
2863 table => 'part_pkg',
2864 references => [ 'pkgpart' ],
2866 { columns => [ 'change_locationnum' ],
2867 table => 'cust_location',
2868 references => [ 'locationnum' ],
2870 { columns => [ 'change_custnum' ],
2871 table => 'cust_main',
2872 references => [ 'custnum' ],
2874 { columns => [ 'main_pkgnum' ],
2875 table => 'cust_pkg',
2876 references => [ 'pkgnum' ],
2878 { columns => [ 'pkglinknum' ],
2879 table => 'part_pkg_link',
2881 { columns => [ 'change_to_pkgnum' ],
2882 table => 'cust_pkg',
2883 references => [ 'pkgnum' ],