<FILE=clients TYPE=mast>
The clients table stores records containing information about
clients that Appx has collection contracts with, as well as clients
which buy materials from Appx.
<FIELDS>
Client_Name                char[50]          The name of the company.

<KEY>
Client_Id                  char[8]           An id assigned to the client, unique, user assigned.

</KEY>
Buyer                      log[Y/N]          Indicates whether or not the client buys from
                                             Appx.

Seller                     log[Y/N]          Indicates whether or not the client sells to
                                             Appx.

Type                       char[20]          The type of organization the client is.  Municipal,
                                             Government, Commercial, Institution, Co-Market.
                                             Validated against a selection list.

Street_1                   char[50]          First line of client's street address.

Street_2                   char[50]          Second line of client's street address.

City                       char[20]          The city the client is located in.

State                      char[3]           The state the client is located in.

Zip                        char[10]          The client's zip code, needs to be compatible with
                                             US 5 and 9 number formats, and Canadian zip codes.

Phone                      char[13]          The client's phone number, in (###)###-#### format.

Fax                        char[13]          The client's fax number, same format as phone
                                             number.

Contact_F                  char[20]          The first name of Appx's contact within the
                                             client's business.

Contact_L                  char[30]          The last name of the contact.
</FILE>

<FILE=mats DESC=Materials TYPE=mast>
The materials table contains information about each material that
Appx collects.
<FIELDS>
<KEY>
Material_Id                char[5]           An id assigned to each material, standard material
                                             acronym.  This field is unique.

</KEY>
Name                       char[30]          The name of the material.  *** Field size is 30.

Container_Unit             char[12]          The type of container or weight the material is
                                             collected in.  Examples: Bins, Tons, Pounds.

Weight_Unit                char[10]          The unit of weight that is used for the material,
                                             Examples: Pounds, Tons.

Conv_Factor                num[8,4]          A conversion factor used to convert the number of
                                             collection units from its Container_Unit into its
                                             Weight_Unit.  For instance, if the material is
                                             collected in "Bins", and a bin typically contains
                                             300 pounds of material, then:

                                             Container_Unit = "Bins"
                                             Weight_Unit = "Pounds"
                                             Conv_Factor = 300

                                             Most conversion factors will convert the bin into
                                             tons, therefore, in this case the Conv_Factor would
                                             be 300/2000 or .15 tons / bin.

                                             *** Note field length change to num[8,4]

Curr_Price                 num[9,3]          The current market price for the material.  This is
                                             calculated from the total sales for a month for all
                                             materials and is measured in dollars / ton.

                                             = (Total Sales / Total Weight Sold)
</FILE>

<FILE=contrs DESC=Contracts TYPE=tran>
The contracts table stores information about each contract.
Appx can have multiple contracts with each of its clients.
<FIELDS>
<KEY>
Contract_Id                char[8]           An id assigned to the contract, unique, user
                                             assigned.

</KEY>
Client_Id                  char[8]           The id of the client who has the contract with
                                             Appx.

Total_Cost                 num[9]            The total maximum cost of the contract, max is
                                             999,999,999.

Annual_Inc                 num[6,2]          The annual increase of the cost of the contract, max
                                             is 999%.

Billing_Freq               char[20]          How frequently the client is billed.  Monthly,
                                             Bi-monthly, Quarterly, Semi-annually, Annually.
                                             Validated against a selection list.

Billing_Code               num[2]            The month in the billing period when the client
                                             should be billed. For instance, if a client is
                                             billed quarterly with a billing code of 2, the
                                             client is billed on the second month of each
                                             quarter.

Set_Out_Ratio              num[4,2]          This is a ratio used to approximate monthly
                                             participation based on the average weekly
                                             participation.

Start_Date                 date[]            The date the contract starts.

End_Date                   date[]            The date the contract ends.

Last_Revised               date[]            The date the contract was last revised.

Term                       num[2]            The number of years the contract lasts.  *** Should
                                             be a calculated field.

Current_Year               num[2]            The year that the contract is currently in, ie. the
                                             contract is in year number 2.
</FILE>

<FILE=contrsdt DESC="Contract Detail" TYPE=tran NODATA>
Lists the specific materials that are collected under this
contract.  This information must also control the fields that are presented
in the collections data entry mode.  Only the materials that are collected
for this contract should show up on the data entry screen when a collection
is entered for this contract.
<FIELDS>
<KEY>
Contract_ID                char[8]           Linking field to Contract table.

Material_Id                char[5]           The Id of the material.  Validated against the
                                             material table.  Some materials are collected co-
                                             mingled.  For instance, steel and aluminum cans may
                                             not be separated during curbside collection.  You
                                             will need to allow the system to allow the entry of
                                             co-mingled materials during the collection phase and
                                             then calculate an estimated distribution amount of
                                             each material.

</KEY>
Serv_Type_Id               char[8]           Service Type code, validated against the service
                                             types file.  Indicates whether this collection is
                                             set up as Curbside, Process Only, Commercial, etc...

Base_Proc_Cost             num[7,2]          The base processing cost Appx gets from
                                             material sales.

Revenue_Share              num[3]            The percentage of revenue that the client gets after
                                             the base processing cost has been subtracted from
                                             the market price.  Appx has different revenue
                                             sharing agreements for each contract.

 Example: Cary collects 100 tons of newspaper and Appx sells the
 newspaper for $100 per ton.  Cary's contract states that Appx base
 processing cost for newspaper is $20 per ton, and Cary's revenue sharing
 percentage is 95%.  $80 per ton is shared revenue of which Cary gets $76 and
 Appx gets $4.  Cary ends up making $7,600 and Appx makes $2,400
 ($400 from revenue sharing plus $20 per ton, base processing cost).

Coll_Scr                   log[Y/N]          Indicates whether or not the material should be part
                                             of the collection data entry form.

Co_Mingle                  log[Y/N]          Default is Yes, unless this material is accurately
                                             weighed, the collection weight is the same as the
                                             sales weight, and the processed inventory is
                                             maintained separate from the co-mingled inventory.
                                             If Distribute=N, this type of collection is not
                                             distributed but sold directly from inventory.

Use_Conv_Fact              log[Y/N]          Whether or not the collection data entry form should
                                             use the material's conversion factor to calculate
                                             approximate weights.

Sequence                   num[2]            The collection data entry form should order the
                                             entry of material amounts based on the value in this
                                             field.
</FILE>

<FILE=svctype DESC="Service Types" TYPE=mast>
The service types table contains information about the
different types of services that Appx can provide.   
<FIELDS>
<KEY>
Serv_Type_Id               char[8]           An id assigned to the service type, unique, user
                                             assigned.

</KEY>
Description                char[50]          A description of the service. Curbside, Drop off,
                                             Process Only, Education, Multi-family, Events,
                                             etc...
</FILE>

<FILE=service DESC=Services TYPE=mast NODATA>
The services table contains information about each service
Appx provides.  The table contains the detail of the services that
should be invoiced the client based on the billing frequency.  (RAD
Requirement ONLY)  *** No data is provided, any data may be entered as test
data and for the Rad Report #8 - Invoicing.
<FIELDS>
<KEY>
Serv_Type_Id               char[8]           The type of service being provided.  Validated
                                             against the Service Types table.

Contract_Id                char[8]           The contract the service is being provided for.
                                             Validated against the contract table.

</KEY>
Est_Quantity               num[5]            The estimated quantity of units per billing cycle.

Act_Quantity               num[5]            The actual quantity of units, if price_type = Fixed
                                             then actual_quant=est_quant

Price_Type                 char[12]          The type of price. Unit, Fixed, Flexible
                                             (unit+fixed).

Base_Cost                  num[6]            The base cost for the service.

Price_Per_Q                num[6,2]          The price per unit.

Frequency_Ser              char[15]          The frequency the service is provided, ex. Weekly,
                                             Bi-weekly, Monthly, etc...

</FILE>

<FILE=routes TYPE=mast>
The routes table contains information about each collection route.
A route can also be defined to be a commercial site or a series of collection
boxes.  
<FIELDS>
Client_Id                  char[8]           The client id relating to the route.  Validated
                                             against the client table.  (This is redundant, as
                                             the client id must equal the client id in the
                                             contract table, but is provided here to match the
                                             file layout of the data.)

<KEY>
Contract_Id                char[8]           The contract id relating to the route.  Validated
                                             against the contract table.

Route_Id                   char[8]           An id assigned to the route, unique for this
                                             contract, user assigned.

</KEY>
Name                       char[40]          The name of the route.  For a commercial route, the
                                             name is the name of the restaurant or commerical
                                             location.

Description                narrative         A description of the route.  Several paragraphs of
                                             information in narrative format must be able to be
                                             entered.  The description might contain the
                                             directions to proceed on the route and other special
                                             information about the route.

Num_Houses                 num[5]            The number of households in the route.

Type                       char[10]          Route type, "Route", "General", "Commercial"

Street_1                   char[50]          First part of commercial site's street address.
                                             This field and the remaining fields must only be
                                             available for data entry if the Route Type is
                                             "Commercial".

Street_2                   char[50]          Second part of commercial site's street address

City                       char[20]          The city the commercial site is located in

State                      char[3]           The state the commercial site is located in

Zip                        char[10]          The commercial site's zip code, needs to be
                                             compatible with US 5 and 9 number formats
</FILE>

<FILE=sites TYPE=mast>
The sites table contains information about each of Appx's
processing sites.
<FIELDS>
<KEY>
Site_Id                    char[8]           An id assigned to the site, unique, user assigned.

</KEY>
Site_Name                  char[30]          The name of the site.
</FILE>

<FILE=collect DESC=Collections TYPE=tran NODATA>
The collections table keeps track of every collection made
by Appx.  The collections are part of Appx'ss contractual agreements
with its clients.  At the end of a monthly period, the collection data will
be distributed among the various "inventories" that are maintained by
collection site, contract, and material.
<FIELDS>
<KEY>
Contract_Id                char[8]           Contract Id.  During data entry, this should be
                                             filled in only once and then the collections for
                                             many routes and runs should be entered for that
                                             contract.

Route_Id                   char[8]           The id of the collection route.  *** Note that in
                                             the data, reference is made to Rte1a, Rte1b, Rte1c,
                                             etc...  This is just a way of referring

Date                       date[]            The date the collection took place.

</KEY>
Num_Set_Outs               num[4]            The number of bins that were set-out for the
                                             collection.

Site_Id                    char[8]           The processing site to which the materials were
                                             taken.  This value should be entered once and then
                                             stay the same until changed.

Material_Id                char[5]           For each material collected (and for ONLY the
                                             materials that are set-up in the Contract Detail
                                             Table) enter the amount that was collected in the
                                             units of collection.  If the units of collection
                                             need to be converted to weights, the conversion
                                             should be done and the weights stored in this table.

Amount                     num[7,2]          If the collection units must be converted to
                                             estimated weights this field should be filled out
                                             and the weight calculated.  If the units are entered
                                             directly in weights, this field must be skipped.

Weight                     num[8,3]          This value is either the calculated weight based on
                                             the conversion factor, or the actual weight of the
                                             materials collected.
</FILE>

<FILE=invent DESC=Inventory TYPE=hist NODATA>
See the discussions on inventory in the Overview.  Inventory must
be maintained for each contract, for each site, and for each material.
Current inventory values are not required.  Beginning inventory values are
required for reports and to determine the new ending inventory at the end of
each month.  Monthly inventory values must be saved.  Reference the reports
section for a discussion of how the ending inventory balances are calculated.
The ending inventory balances must become the next month's beginning
balances.
<FIELDS>
<KEY>
Date                       date[]            Date indicates monthly period.

Site_Id                    char[8]           The id of the inventory site.

Contract_Id                char[8]           Contract Id.

Material_Id                char[5]           The id of the material in inventory.

</KEY>
Amnt_Material              num[7,3]          The amount of material stored in the material's unit
                                             of weight.

</FILE>

<FILE=sales TYPE=tran NODATA>
The sales table keeps track of each Sale Appx makes. See the
inventory table description for information concerning the required processes
for the sales table when a new record is entered.
<FIELDS>
<KEY>
Date                       date[]            The date the marketing sale took place.

Site_Id                    char[8]           The id of the site at which inventory was sold from.

Client_Id                  char[8]           The id of the client who purchased materials from
                                             Appx.

Material_Id                char[5]           The id of the material being sold.

</KEY>
Amt_Material               num[10,3]         The amount of material sold, units are in pounds.

Amount_Paid                num[8,3]          The total amount of money Appx was actually
                                             paid.

Market_Price               num[8,3]          The price per pound of the material at the time of
                                             sale.  Calculated field.  Amount_Paid /
                                             Amt_Material.

Co_Mingled                 log[Y/N]          Default = Yes.  If not co-mingled revenue is not
                                             distributed.

Contract_Id                char[8]           If and only if Co_Mingled = No, then you must enter
                                             a valid contract id to credit the sale toward.
</FILE>

