How Jobs, Routings and BOM’s are stored in the database.
Solution ID = KB-67
Goal : Explanation of how jobs, routings and BOMS are stored in the database for current routings and all types of jobs.
Version = All Version
Module : SLMFG
Fact 1 : Data structure for job, routings and BOMs
Fact 2 : Current Routing
Database : Progress, SQL
Cause
All types of jobs, routings and BOMs
are stored in the same set of tables in the database. This includes current
routings and BOMS which are linked to a current job which is created behind the
scenes when you add a current routing and production schedules for which jobs
are created when you add a PS item or release.
Fix
The file structure is the following:
---jobroute, jrt-sch, jrtresourcegroup (SL7 and higher)
---jobmatl
------job-ref
The job table holds all jobs in the system, jobroute stores all operations for all types of routings and jobmatl stores all materials. There is one job-sch record for each job and one jrt-sch for each jobroute record. They hold scheduling-related fields for jobs and operations, respectively. You can have multiple resoruce groups per operation. The resoruce records are stored in the jrtresourcegroup table. The different jobs are identified by the job.type field:
J - Actual Job in SFC
S - Current jobs (suffix 000), standard jobs (suffix 001)
E - Estimate jobs
P - Production schedule item jobs
R - Production schedule release jobs
Current Jobs
When you add the first current operation to an item (standard operation prior
to version 4.0), the system creates a type-S record in the job table with a
suffix of 000. The system then links that current job to the item master record
by putting the job number into the "item.job" field.
If you run the Change Item Revision utility in the ECN module, an
"itemrev" record is created for the item's old current job number, a
new current job is created, the old current job's routing and BOM and copied to
the new current job and the new number is put into the item.job field. So, if
you notice multiple type-S job records with a suffix of 000 for the same item,
this utility has been run. The most recent current job is the number in the
item.job field.
Standard Jobs
Beginning with version 4.0, the standard routing/BOM was renamed current
and system began maintaining a standard (or frozen) routing/BOM for
standard-costed items. When you run the Roll Current to Standard utility, the
system creates another type-S job where the job number is the same as the
current job but with a suffix of 001. The current routing and BOM is then
copied to this job number.
Production Schedules
When you add a production schedule
(PS) item or release, the system create jobs behind-the-scenes. The type is P
for PS item jobs and R for PS release jobs. For both, the job number is made up
of the string "[SYMIX]" followed by a 9 digit number. The jobs
created for releases have the same job number as the PS item job but with a
suffix which begins at 1 and is incremented for each release.
The system links these jobs back to the PS item by placing the job number into
the "psitem.job" field. The PS number is also placed into the
"job.ps-num" field. When you copy the routing/BOM to a production
schedule, the routing is copied to each PS item and PS release job so that
quantities may be tracked for each release but the BOM is ONLY copied to the PS
item job.
Additional Notes
If you have a separate scheduling database, the job-sch and jrt-sch tables are
stored in that database. Therefore, if you are accessing those tables in a
custom report or query, you should prefix the table name with the
"symsch" alias.
The system assigns the job number for each of these job types by first looking
to see if there is a prefix value set up on the parameter screen for the job
type. If so, it finds the last job in the database with that prefix and adds 1.
If you not using a prefix, it finds the last purely numeric job number in the
table regardless of type and adds 1.
Jobs, operations and materials can all have text. The "key" field in
the job, jobroute and jobmatl tables point to the records in the
"notes" database which holds each records text.
Job.rollup_date is only populated for current jobs and is the last time the
Current BOM cost rollup ran for the item. This field is not updated by
the other Cost Rollup utilities.
