Skip to main content

The Super Catalogue

The Super Catalogue

KDB 5 Oct 2025

Context​

The AAF-Hub core is a trinity of tables plus helper tables.

The trinity are:
• aafVendable
• aafRecipeActivity
• aafJob
• with helpers aafAbreviation and aafSales

This document concerns aafVendables AKA the “Super Catalogue”: Its role and how it is supported.

Super Catalogue​

The AAF-Hub maintains a super catalogue of all stock (both actual and potential) that might be used in DIYF business activity. For want of a name these items are referred to in relation to the AAF-Hub as vendables. The information that describes them along with their base pricing, status and availability is maintained in a table called aafVendable. Thus each row of the table aafVendable represents a particular vendable as a conjunction of supplier data and DIYF observation. No two rows concern the same vendable and so this table is is used as the principal resource for information on every vendable known to the AAF-Hub.

Bulk updating​

DIYF's vendables are primarily obtained from a set of specified main suppliers (currently three: LarsonJuhl, Centrado and Lion Pic) along with a range of minor suppliers.

Accumulating the main supplier's catalogues into the Super Catalogue​

The main suppliers each provide csv files listing their offered stock. Each of these files contain raw data to be used to inform and update the contents of our vendables a super catalogue.

Each supplier's csv is idiosyncratic to that supplier and as a result there is no standardisation of presentation. This means that the process of taking data from each supplier and using it to update the super catalogue must be unique for each supplier; hence three distinct processes (one each for LJ,Cd, LP). Nevertheless there are significant resemblances between them and so a series of stored routines is provided for automatically acquiring new, up to date, information as and when the DIYF need it.

The process for each of the said main suppliers is as follows (where the token “SupplierName” stands for LarsonJuhl or Centrado or LionPick):

1. Trigger: A new file SupplierName.csv is available for import AND there is a command (can be either on demand or scheduled) to upload it.

2. The file Supplier.csv is imported as table rawSupplierNameImported.
3. The schema (encoding, column headings etc) is confirmed against expectations of usage in the AAF-Hub.

4. The contents of rawSupplierNameImported are compared with previously acquired data and the aafVendable table is updated accordingly.
• Regarding the manner of comparison:
▪︎New vendables can be detected against current content of aafVendables.
▪︎No longer supported vendables can be detected against the version of

rawSupplierNameImported used previously (at this juncture that table would have been renamed rawSupplierName see steps below).
▪︎Other data changes (price, supply availability, detail etc) can also be detected by comparison with the table (if exists) rawSupplierName.

▪︎As part of data content changes in the aafVendables table there are set of Alerts (as flags and notes) added.

5. The current rawSupplierNameImported becomes (replacing if exists) the new rawSupplierName table.
6. The content of the aafVendables table is adjusted for items that are neither regular nor special order stock.
✧ The data content is minimised requiring a user to access certain information in the rawSupplierName table.
✧ Note: Every vendable listed in the aafVendable table is essentially curated. There are notes and other entries included that are not provided by the supplier. Among these are a StockingStatus which indicates if the vendable is regular or special order stock or something else (eg discontinued, blacklisted etc).
7. The acquisition process is concluded with an automatic wrap-up routine.

Accumulating the minor supplier's catalogues into the Super Catalogue​

The minor suppliers do not provide csv files listing their offered stock. Updating the aafVendable table for their supplied vendables is manual.

Support for the necessary processes is presently incomplete and requirements are sought.

Ad hoc Adjustments​

There are adjustments that are expected to be required for time to time. In some cases they will be to individual vendables and in others they will be for groups of vendables.

Adjusting individual vendables​

Support for the necessary processes is presently incomplete and requirements are sought. Possibles include:

• New vendable quick entry

• Vendable general adjustment.(for super admin roles)

• Vendable controlled adjustment (for specified roles cases TBD, such as type, subtype, sku, price, supply, stocking policy, whereat).

Adjusting groups of vendables
The minor suppliers do not provide csv files listing their offered stock. Updating the aafVendable table for their supplied vendables is manual.

Support for the necessary processes is presently incomplete and requirements are sought. Possibles include:

• Bulk retyping and re-subtyping for defined sets of vendables.

• Bulk Alert Flag and Notes management

• Bulk purging

• Bulk restoring

• Bulk archiving

Views​

There are key SQL views defined so that users may be furnished with selection and viewing GUI screens.

aafVendLongView​

This gathers all fields needed by a hub human user to view a selectable list of vendables. It selects the appropriate columns of the aafVendable table

aafVendMasterView​

This gathers all fields needed by a hub human user to view the data on a specific vendable. It selects the appropriate columns of the aafVendable table and supplements them with derivations for:

raw data (taken from the rawSupplierName table)
Structured AlertNotes (using a stored routine to impose a Jason structure on the aafVendables.AlertNotes field).