Stephen Smith's Blog

Musings on Machine Learning…

Posts Tagged ‘database structure

Tables and Data Flow of the Sage 300 ERP System Manager Module

with 8 comments

This blog posting will be looking at the structure of some of the parts of the System Manager modules for the Sage 300 ERP product (previously known as Sage ERP Accpac). You can find the structure of the individual tables in the Sage 300 ERP Application Object Model (AOM), which must be viewed in IE. However this doesn’t tell you how the tables are related or how data flows from one table to another as you post various transactions. The System Manager tables consist of those of the mini-application modules that used to be bundled with System Manager (now just bundled with Sage 300 ERP). These include the 22 tables in Common Services, the 30 tables in Bank Services, the 9 tables in Tax Services and the 5 tables in G/L Sub-ledger Services. We’ll just look at a few interesting cases. Understanding these relationships can be especially important to people writing sophisticated custom reports or BI Views.

The main types of database tables in Sage 300 ERP applications can be categorized as:

  • Setup
  • Master Data
  • Data Entry
  • Data Processing
  • Periodic Processing

The System Manager tables contain information that is used in common by many of the main large Sage 300 ERP accounting modules like Account Receivable or Purchase Orders. The main concentration of tables is of the setup variety since these are setting up the company wide options and properties for the rest of the system.

Administrative Services keeps a few tables outside of the main database. Typically these are required to sign-on to the main database. These include the list of companies you can sign on to and the file with all the user ids and passwords. These are stored in a proprietary ISAM format derived from the old DOS based Accpac Plus database system.

A number of Currency and Security related tables are stored in the System Database. A System Database is shared by a number of Company Databases so that you can update this information in one place and have it used by many companies. The data in the System Database is replicated into each Company Database; so that when we are running we don’t need to do cross-database joins or open extra database connections to get this information. This way we get the advantages of sharing without the overhead of using extra database related resources.

Setup

There are many setup tables across the System Manager functions:

Bank Services

BKOPT (BK0010): Options.

BKTT (BK0003): Distribution Codes (formally known as transaction types).

BKTTX (BK0860): Bank Distribution Codes Tax Data.

BKDISTH (BK0445): Bank Distribution Set Headers.

BKDISTD (BK0440): Bank Distribution Set Details.

BKCCTYP (BK0240): Credit Card Types.

BKGLREF (BK0470): Bank G/L Integration.

Tax Services

TXAUTH (TX0002): Tax authorities.

TXCLASS (TX0001): Tax classes.

TXGRP (TX0003): Tax groups.

TXRATE (TX0004): Tax rates.

TXMATX (TX0902): Tax rate matrix. TXMATX controls interaction with TXRATE.

Administrative Services

A4WUSER (AS0003): Users. This is an ISAM file and not in the database.

A4WCUST (AS0004): Customization directories. This is an ISAM file and not in the database.

ASORGS (AS0020): Companies.

CSSEC (AS0001): Security groups. This is in the system database and cloned to all attached company databases.

CSAUTH (AS0002): User authorizations. This is in the system database and cloned to all attached company databases.

CSUICSH (AS0005): UI Cust. Profile Headers. This is in the system database and cloned to all attached company databases.

CSUICST (AS0006): UI Cust. Profile Details. This is in the system database and cloned to all attached company databases.

CSUSCST (AS0007):  User UI Customizations. This is in the system database and cloned to all attached company databases.

Common Services

CSCOM, CSCOM2 (CS0001): Company profile. (One view, two database tables).

CSFSC (CS0002): Fiscal calendars.

CSOPTFH (CS0011): Optional field headers.

CSOPTFD (CS0012): Optional field values.

All the currency tables are in the system database and cloned to all attached company databases.

CSCCD (CS0003): Currency codes.

CSCRT (CS0004): Rate types.

CSCRH (CS0005): Currency tables.

CSCRD (CS0006): Currency rates.

CSEUR (CS0010): Euro fixed conversion rates. This was only used during the transition period to the Euro. If the Euro falls apart, we may need one to transition out of the Euro.

CSSKTB (CS0030): Schedules.

Optional Details:

CSSKAP (CS0032): Schedule Application Links.

CSSKCB (CS0034): Schedules.

CSSKID (CS0036): Access to Scheduling.

Master Data

The only master data in System Manager is the always important Bank Accounts.

BKACCT (BK0001): Bank Accounts.

BKCUR (BK0002): Currencies.

BKFORM (BK0008): Check stocks.

BKACCT has many more detail views to do with checks, deposits, entries and NSFs; but, we’ll cover those in the following sections.

Data Entry

Reconcile Statements

Most documents like checks, deposits, NSFs, etc. actually come from the sub-ledgers. The Bank module is mostly to reconcile these once your bank statements come in. However you do need to enter Bank entries and to start you need to enter some manual checks and deposits to setup the Bank balances correctly. This section covers all the tables involved in the process of entering bank documents and reconciling them. In Bank nearly everything is a detail of BKACCT, so this appears again at the top.

BKACCT (BK0001): Banks.

BKTRANH (BK0845): Transaction Headers (can be withdrawal or deposit).

                BKTRAND (BK0840): Transaction Details.

BKENTH (BK0665): Bank Entry Headers.

                BKENTD (BK0660): Bank entry details.

BKDCI (BK0104): Direct clearing interface. Superview on top of BKTRAN to help the UI reconcile withdrawals and deposits.

Journal Entry

The entry function in G/L Sub-ledger Services handles the case of whether G/L is activated or not. Rather than sub-ledgers writing to G/L directly, they write to these views. Then sub-ledger services either writes these through to G/L, or if G/L isn’t present then it writes them to CSV files that can be imported into another G/L.

GPGLPO (GP0950): Transaction transfer. The superview stores data in the following views:

GPGLBB (GP0100): Subledger transactions.

GPGLBH (GP0120): Subledger entries.

GPGLBD (GP0110): Subledger details.

Data Processing

Bank has two big processing functions, namely printing checks and then posting reconciliations. Tax Services then has calculating sales tax and keeping sales tax records.

BKREG (BK0009): Check register. Sub-ledgers put the checks they want printed here, then printing of checks is done through an icon in bank BK5000 – Check print User Interface.

BKPOST (BK0101): Reconciliation posting.

BKJCTL (BK0020): Posting journal control.

BKJRNL (BK0011): Posting journal.

BKJTRANH (BK0655): Bank journal transaction header.

BKJTRAND (BK0650): Bank journal transaction details.

BKJENTH (BK0665): Entry posting journal header.

                BKJENTD (BK0660): Entry posting journal details.

BKJERR (BK0012): Posting journal errors.

TXCALC (TX0901): Tax calculation (low level engine).

TXDCALC (TX0910): Tax document calculation (higher level engine).

TXALLOC (TX0904): Tax allocation (superview).

TXAUDH (TX0011): Tax tracking.

TXAUDD (TX0012): Tax tracking details.

Periodic Processing

There are a set of superviews that perform various periodic processing functions. These Views typically don’t have tables behind them and perform maintenance type operations. Some of these include:

BKCLEAR (BK0103): Clear reconciliation posting journals.

BKDCHK (BK0100): Bank Integrity checker.

TXCLEAR (TX0903): Tax audit clearing.

TXDCHK (TX0990): Tax integrity checker.

ASINTCHK (AS0010): Admin Services Integrity Checker.

ASINTPR (AS0023): Data Integrity checker superview.

ASRST (AS0021): Restart Records. (Actually an ISAM file and not in the database).

CSDCHK (CS0009): Common Services Integrity checker.

GPGLFMT (GP0800): Segment substitution.

GPGLPE (GP0930): Transaction transfer.

GPDCHK (GP0099): G/L Sub-ledger Services Integrity checker.

Summary

Hopefully this blog posting provides a bit more insight into how the various System Manager modules operates and hopefully helps when you use them, interface to them or are creating custom reports.

 

Advertisements

Written by smist08

December 17, 2011 at 5:40 pm

Tables and Data Flow of the Sage 300 ERP Payroll Module

with 2 comments

This blog posting will be looking at the structure of some of the parts of the Canadian and U.S. Payroll modules for the Sage 300 ERP product (previously known as Sage ERP Accpac). You can find the structure of the individual tables in the Sage 300 ERP Application Object Model (AOM), which must be viewed in IE. However this doesn’t tell you how the tables are related or how data flows from one table to another as you post various transactions. There about 99 database tables in either the U.S. or Canadian Payroll module, so obviously we can’t describe each in a single blog post. We’ll just look at a few interesting cases. Understanding these relationships can be especially important to people writing sophisticated custom reports or BI Views.

The main types of database tables in Payroll can be categorized as:

  • Setup
  • Master Data
  • Data Entry
  • Data Processing
  • Periodic Processing

The Payroll modules feed data into both General Ledger and Project and Job Costing. Payroll also interacts very closely with the Bank module for the processing of paychecks. Each Payroll module contains a main common portion which is mostly shared; these contain all the files that start with cp or up. Then for each Payroll there is a Payroll calculation module which has the country specific tables, reports and calculations; these include the tables that start with: CT or UT. Often as the government changes the Payroll rules and rates, we just need to updated and distribute updated CT and UT applications rather than the larger core CP or UP application.

In the descriptions below we will list the U.S. Payroll table name and View IDs. However to get the Canadian Payroll versions, just replace the UP with CP and you have it. Most of these are compiled from the same source code (which uses PR) and then UP or CP are substituted in as part of the product build process.

Incidentally this is the same Payroll module that is used in the Sage HRMS SQL Payroll product, so everything here applies there as well.

Setup

Setting up Payroll correctly is the most crucial task to have things run smoothly. If all the employees and their earnings and deductions are configured correctly then processing Payroll will go much smoother. The Setup Tables are:

UPOPTS (UP0023): Options.

UPCLAS (UP0006): Class codes.

UPDTLM (UP0007): Earnings/deductions. (UPDTLX, UP0099 is UP0007, without some of its functionality for faster read access.)

UPINCL (UP0015): Include list. Used by UPTXMS, too. Holds the applicable taxes for each E/D.

UPDIST (UP0009): Distribution codes.

UPWCCH (UP0036): Worker’s compensation master.

UPWCCD (UP0037): Worker’s compensation codes.

UPOTSC (UP0022): Overtime schedules.

UPSHFT (UP0025): Shift differential schedules.

UPSHFD (UP0038): Shift Differential Details.

UPSHFB (UP0039): Shift Differential Billing Details.

UPTXMS (UP0029): Company payroll taxes.

UPINCL (UP0015): Include list.

UPDIST (UP0009): Distribution codes.

UPTXMO (UP0124): Taxes Optional Field Values.

UPWRKC (UP0027): Work Classification Codes.

UPOFH (UP0120): Optional Field Locations.

UPOFD (UP0121): Optional Field Values.

UPGLREF (UP0057): G/L Reference Intergration.

Master Data

The main master data file for Payroll is the Employees. Then secondarily we have Employee selection lists.

UPEMPL (UP0014): Employees

UPEMPD (UP0008): Employee earnings/deductions.

UPEMPT (UP0010): Employee taxes.

UPEMPC (UP0053): Employee notes.

UPEMBK (UP0201): Employee EFT Banks.

UPEMPO (UP0122): Employee Optional Field Values.

UPEMTF (UP0062): Employee Tax Fields (Implicitly handled by UP0014).

UPESLH                (UP0045): Employee selection lists.

UPESLD (UP0046): Employee selection list members.

There is no master of monetary balances as GL has.  The detail for all checks issued is kept in the following, which serves as a master and detail archive all in one.

UPCHKH (UP0048): Check Header. (Holds completed and un-posted checks.)

UPCHKD (UP0049): Check Details.

                UPCHDO (UP0134): Check detail optional field values.

                UPCHJB (UP0056): Check job details.

                                UPCHJO (UP0144): Check job details optional field values.

UPCHKC (UP0052): Check comment details.

UPCHKE (UP0202): Check EFT details.

UPCHHO (UP0133): Check header optional field values.

Data Entry

The main data entry task for Payroll is the entry of Timecards. A common integration is for ISVs to feed Timecard data into Payroll from an external system (like a time clock check-in system). You may also need to enter some manual checks.

UPTCHD (UP0031): Timecard headers.

UPTCDT (UP0032): Timecard details.

                UPTCDO (UP0128): Timecard detail optional field values.

                UPTCJB (UP0042): Timecard job details.

                                UPTCJO (UP0141): Timecard jobs optional field values.

UPTCHO (UP0127): Timecard optional field values.

UPMCHD (UP0019): Manual check headers

UPMCDT (UP0020): Manual check details.

                UPMCDO (UP0130): Manual check detail optional field values.

                UPMCJB (UP0043): Manual check job details.

                                UPMCJO (UP0142): Manual check job detail optional field values.

UPMCHO (UP0129): Manual check optional field values.

Data Processing

The Calculate Payroll process is the central part of Payroll. This takes all the setup, employee and timecard data and calculates the Payroll. This includes all taxes and deductions, sets up everything to print checks and advice and record all the values that need to be accrued.

UPCALC (UP0083): Superview to calculate payroll.

UPCLCO (UP0137): Calculate payroll optional field values. Since UPCALC has no data, these optional fields have no header.

UPCALE  (UP0084): E/D/T List, helper to UP0083

UPTXMC (UP0100): Include List, table helper, used internally in conjunction with Tax Master by UP0084.

UPCALT (UP0085): Timecard List, helper to UP0083.

UPCALA (UP0086): Cost Center Allocations (there is data here).

UPCHKR (UP0088): Check Run Header, temporary table helper to UP0083.

UPCTRL (UP0026): Concurrency Control.

UPXCPT (UP0064): Calculate Payroll Exceptions, produced by UP0083 if errors detected.

Once the calculate Payroll is performed, the next big process is printing all the checks.

UPPMAN (UP0065): Process Manual Checks (called by UP0078 , UI ‘Process’ button, and Process Icon).

UPCTRL (UP0026): Concurrency Control.

UPXCPT (UP0064): Calc Payroll Exceptions, produced by UP0065 if errors detected.

UPHHAU (UP0021): History Audit Headers.

UPHHOA (UP0135): History audit header optional field values.

UPHDAU (UP0024): History Audit Details.

                UPHDOA (UP0136): History Audit detail optional field values.

UPPCKS (UP0078): Print Checks, controls the Print process and calls Post (UP0050).

UPPCKF                (UP0089): Print Checks Trans, temporary helper to UP0078.

Updates the following staging tables for printing. When you print checks, part of the header is transfer to the BKCHK table which drives check printing. Then the Payroll Crystal check forms will join back to the following two tables to get all the Payroll specific data they need for the check and advice.

UPPCKH (UP0079): Print Checks Header.

UPPCKD (UP0080): Print Checks Details.

Post All Checks

Called internally by UP0078, UP0083 and UP0012.

UPPOST (UP0050): Post checks.

Updates the following tables as needed:

UPMCHD (UP0019): Manual Checks Header

UPMCDT (UP0020): Manual Checks Detail

UPCHKH (UP0048): Check Header

UPCHKD (UP0049): Check Detail

UPYTDS                (UP0047): Employee YTD summaries

CPEUIC (UP0030): Employee EI history (Canadian payroll only).

Periodic Processing

There are a set of superviews that perform various periodic processing functions. These Views typically don’t have tables behind them and perform maintenance type operations. Some of these include:

UPMAIN (UP0018):  Delete inactive records.

UPDCHK: (UP0016): Integrity checker.

Update All employees:

UPGMET (UP0058): Globally modify process.

Uses the following table:

UPGLBM (UP0051): Globally modify list.

Summary

Hopefully this blog posting provides a bit more insight into how Payroll operates and hopefully helps when you use Payroll, interface to Payroll or are creating custom Payroll reports.

 

Written by smist08

December 10, 2011 at 7:45 pm

Tables and Data Flow of the Accpac Accounts Payable Module

with 10 comments

This blog posting will be looking at the structure of some of the parts of the Accounts Payable module of the Sage ERP Accpac product. You can find the structure of the individual tables in the Accpac Application Object Model (AOM), which must be viewed in IE. However this doesn’t tell you how the tables are related or how data flows from one table to another as you post various transactions. There are 78 tables in A/P, so obviously we can’t describe each in a single blog post. So we’ll just look at a few interesting cases. Understanding these relationships can be especially important to people writing sophisticated custom reports or BI Views.

The main types of database tables in A/P can be categorized as:

  • Setup
  • Master Data
  • Data Entry
  • Data Processing
  • Periodic Processing

Accounts Payable controls and tracks paying all a company’s vendors. The Purchase Order module will feed documents into A/P when vendors need to be paid. A/P will then feed all monetary transactions into G/L for recording. A/P also interacts closely with Bank services for printing checks and handling the whole check printing/reconciliation cycle.

Setup

The Setup Tables are:

Options

A/P has 4 options tables. The reason is to separate out the functionality to reduce contention on these files. This way updating the various invoices and other document numbers doesn’t affect the other tables.

APP01 (AP0001): Company Options.

APP02 (AP0002): Invoicing and Numbering.

APP03 (AP0003): Payment and Aging.

APP04 (AP0004): Integration.

Other Setup Tables

APCLX (AP0007): 1099 Class Codes.

APCCS (AP0013): 1099/CPRS Amounts.

APRTA (AP0012): Terms.

APRTB  (AP0011): Terms Payment Schedules.

APSLH (AP0035): Payment Selection Codes. Selection Criteria Header.

APSLD (AP0036): Selection Code Details. Selection Criteria Details.

APSLHO (AP0411): Selection Criteria Header Optional Fields.

APRAS (AP0006): Account Sets.

APRDC (AP0005): Distribution Codes.

APDSH (AP0009): Distribution Sets Headers.

APDSD (AP0008): Distribution Set Details.

APPTP (AP0010): Payment Codes.

APMSG (AP0120): E-mail Messages.

APGLREF (AP0121): G/L Reference Integration.

APOFH (AP0501): Optional Field Locations

APOFD (AP0500): Optional Fields.

Master Data

The main master data file for A/P is the Vendors. Then secondarily we have Vendor Groups. I included the open documents under vendors since they are details, even though they are more a part of posting.

APVGR (AP0016): Vendor Groups.

APVGRO (AP0408): Vendor Group Optional Fields Values.

APVGS (AP0017): Vendor Group Statistics.

APVEN (AP0015):  Vendors.

APVENO (AP0407): Vendor Optional Field Values.

APVCM (AP0014): Vendor Comments.

APVSM (AP0019): Vendor Statistics.

APVNR (AP0018): Remit To Locations.

APVNRO (AP0409): Remit-To Location Optional Fields.

APOBL (AP0025): Open Documents (updated only by postings).

APOBS (AP0026): Open Payment Schedules.

APOBP (AP0027): Document Payments.

Data Entry

Generally data flows into A/P starting as an Invoice. Whether it is entered in A/P or fed in from another application like P/O. Then as the invoice is paid, payment batches record this and mark payments against the Invoices.

Invoice Entry

APIBC (AP0020): Invoice Batch Control.

APIBH (AP0021): Invoices Headers.

APIBD (AP0022): Invoice Details.

APIBT (AP0024): Invoice Detail Comments.

APIBDO (AP0401): Invoice Detail Optional Fields.

APIBS (AP0023): Invoice Payment Schedules.

APIBHO (AP0402): Invoice Optional Fields.

Payment and Adjustment Entry

Both adjustments and payments share the same set of database tables. In the APBTA Batch record the first field is PAYMTYPE which controls whether the batch is payment or adjustment. It is AD for Adjustment and PY for Payment.

APBTA (AP0030):  Payment and Adjustment Batches.

APTCR (AP0031): Payments/Adjustments

APTCP (AP0033): Applied Payments.

APTCU (AP0034): Adjustment G/L Distributions.

APTCN (AP0032): Miscellaneous Payments.

APTCRO (AP0406): Payment/Adjustment Optional Fields.

APPOOP (AP0048): Populates Payment List.

APCTRL (AP0057): Payment Control.

APSYGN (AP0056): Generate Payments Batch.

Data Processing

Posting SuperViews

Batches are posted entry by entry. All the audit files are added to while posting and all statistics are updated along with all the various posted document tables.

APIVPT (AP0039): Superview to Post invoices.

APPYPT (AP0040): Superview to Post payments/adjustments

APTRK (AP0037): Payment GL Transactions.

Posting Journal

APPJS (AP0512): Posting Journals.

APPJH (AP0511): Posting Journal Headers.

APPJHO (AP0514): Posting Journal Header Optional Fields.

APPJD (AP0510): Posting Journal Details.

APPJDO (AP0513): Posting Journal Detail Optional Fields.

APPTER (AP0038): Posting Errors Messages.

Periodic Processing

There are a large set of superviews that perform various periodic processing functions. These Views typically don’t have tables behind them and perform maintenance type operations. Some like year end or currency revaluation are quite major operations in their own right. Some of these include:

Clearing

APPREN (AP0049): Fully Paid Docs and Journals.

APPGCM (AP0050): Vendor comments.

APPGST (AP0051): Stats and 1099/CPRS amts.

APBCTU (AP0059): Deleted and posted batches.

APPGIA (AP0052): Delete inactive records.

Year End and Revaluation

APYREN (AP0044): Year End.

APUNGL (AP0041): Revaluation.

APRVL (AP0063): Revaluation Details.

APRVLO (AP0410): Revaluation Optional Fields.

Printing

The whole process of printing checks is fairly complicated. To print checks, A/P writes the check information into the BKCHK table in Bank Services. Then it invokes the BK5000 UI to actually drive the check printing. The process works like this so it can be shared by other check printing applications like U.S. and Canadian Payroll. BKCHK doesn’t contain all the check information, only the main header information then the A/P Check form joins back to the A/P tables to get the full set of information the check and advice forms.

APCHKS (AP0058): Print Checks.

APADV (AP0060): Payment/Check Advices.

APBPPU (AP0055): Update Print Status.

Statistics and Aging

APSTAT (AP0047): Update Statistics.

APAGE (AP0043): Age Documents.

Create G/L Batch

APGLTR (AP0042): Create GL batch.

Data Integrity

APINTCK (AP0045): Integrity Checker.

Activation

APINIT (AP0100): Activation.

Drill Down

APDLDN (AP0062): Drill Down. Called from G/L to allow G/L to drill down into A/P source documents.

Summary

Hopefully this blog posting provides a bit more insight into how A/P operates and hopefully helps when you use A/P, interface to A/P or are creating custom A/P reports.

 

Written by smist08

October 8, 2011 at 5:03 pm

Tables and Data Flow of the Accpac Purchase Order Module

with 16 comments

This blog posting will be looking at the structure of some of the parts of the Purchase Order (P/O) module of the Sage ERP Accpac product. You can find the structure of the individual tables in the Accpac Application Object Model (AOM), which must be viewed in IE. However this doesn’t tell you how the tables are related or how data flows from one table to another as you post various transactions. There are 169 tables in P/O, so obviously we can’t describe each in a single blog post. So we’ll just look at a few interesting cases. Understanding these relationships can be especially important to people writing sophisticated custom reports or BI Views.

The main types of database tables in P/O can be categorized as:

  • Setup
  • Data Entry
  • Processing
  • Periodic Processing

Purchase Order is one of the main entry points for data into the Accpac system. Notice that unlike other modules (except O/E); P/O has no master data files. Purchase Order mirrors Order Entry to some degree to handle the purchasing side of things while O/E handles the sales side. Documents from Purchase Order then flow into A/P, inventory is updated in I/C and all financial numbers end up in G/L. Below is a diagram showing this flow:

Setup

The Setup Tables are:

POOPT (PO00600): Options. Various global options for the P/O module.

POVIA (PO0900): Ship Via Addresses.

POPLAT (PO0605): P/O Templates.

POACST (PO0300): Additional Costs.

POACD (PO0290): Additional Cost Taxes.

POACSTO (PO0299): Additional Cost Optional Fields.

POMSG (PO0540): E-mail Messages.

POOFH (PO0585): P/O Optional Field Locations.

POOFD (PO0580): P/O Optional Fields.

POVUPR (PO0181): Vendor Contract Costs.

POVUMB (PO0191): Vendor Contract Cost Base Units.

POVUMS (PO0192): Vendor Contract Cost Sale Units.

POVUTX (PO0183): Vendor Contract Included Taxes.

Besides the standard tables that each application has, P/O has very few setup tables. However beware that P/O uses the setup tables in A/P and I/C extensively so look to these as well when considering what affects P/O.

Data Entry

Data Entry is the largest part of P/O. There are many tables involved in just entering a Purchase Order. Then you have requisitions, receipts, invoices, returns, and credit/debit notes. Several  of these involve tracking serial numbers and lot numbers. Quite a few tables have associated optional field tables. Many of the tables (like POPORHx) are quite large records with many fields and span two physical tables (one ending in 1 and the other 2).

All the data entry tables rely heavily on header/detail relationships. Each level of indentation indicates that the indented level is a detail of the less indented level. For more on header detail relationships check out this.

The data entry Views perform many calculations as you enter data. This includes sales tax calculations and a very sophisticated proration engine.

A general workflow might be to start with a Requisition then from this generate a Purchase Order then from the Purchase Order generate a Receipt then from the Receipt generate an Invoice. Then optionally a Return might be generated or some Credit/Debit Notes. Generally whenever you create on document from another, you can in fact create a document from multiple of the preceding documents. So for instance you can create 1 Invoice for a collection of Purchase Orders.

Requisitions

Requisitions are usually the documents that start the purchasing process, here you are requesting something, but need approval or more data before generating a Purchase Order.

PORQNH1,2 (PO0760): Requisition Headers. Notice that the View spans two physical tables. For each P/O Requisition, there is 1 record each in PORQNH1 and PORQNH2.

PORQNL (PO0770): Requisition Details.

PORQNLO (PO0773): Requisition Detail Optional Fields.

PORQNC (PO0750): Requisition Comments .

PORQNG (PO0759): Requisition Functions.

PORQNHO (PO0763): Requisition Header Optional Fields.

PORQNLV (PO0777): Requisition Vendors.

POGNRQN (PO0359): SuperView to create POs from Requisitions.

Calls the following

POGNVD (PO0361): Create from Requisitions by Vendor.

Purchase Orders

Purchase Orders can either be created new, or can be generated from one or more Requisitions.

POPORH1,2 (PO0620): PO Headers. There are two physical tables for each P/O.

POPORL (PO0630): PO Details.

POPORLO (PO0633): PO Detail Optional Fields.

POPORC (PO0610): PO Comments.

POPORR (PO0632): PO (from) Requisitions.

POPORG (PO0619): PO Functions.

POPORHO (PO0623): PO Header Optional Fields.

POFRQNL (PO0345): Requisitions (lookup helper).

POPORI (PO0621): PO Postings. Result of POST button.

POGENIC (PO0350): Create POs from IC                – Auto-generate from IC.

POGENOE (PO0352): Create POs from OE – Auto-generate from OE.

POGNOE (PO0355): Create PO from OE Order by Vendor.

POGNOEB (PO0357): Create PO from OE Order by Order Number.

Receipts

Next come Receipts which are usually generated from one or more Purchase Orders.

PORCPH1,2 (PO0700): Receipt Header. There are two physical tables for this and each record spans both tables.

PORCPL (PO0710): Receipt Lines.

PORCPC (PO0695): Receipt Comments.

PORCPV (PO0718): Receipt Vendors.

PORCPS (PO0714): Receipt Additional Costs.

PORCPR (PO0705): Receipt (from) Purchase Orders.

PORCPG (PO0699): Receipt Functions.

PORCPHO (PO0703): Receipt Optional Fields.

PORCPD (PO0696): Receipt Cost Distributions.

PORCPI (PO0701): Receipt Postings – Result of POST button.

PORCPM (PO0711): Receipt Postings Lines.

PORCPT (PO0715): Receipt Postings Add. Costs.

Invoices

Now it’s time to pay with an Invoice document.

POINVH1,2 (PO0420): Invoice Headers. Each Invoice header spans two physical records.

POINVP (PO0436): Invoice Payment Schedules.

POINVL (PO0430): Invoice Lines.

POINVLO (PO0433): Invoice Detail Optional Fields.

POINVLL (PO0819): Invoice Detail Lot Numbers.

POINVLS (PO0810): Invoice Line Serial Numbers.

POINVS (PO0440): Invoice Additional Costs.

POINVC (PO0416): Invoice Comments.

POINVG (PO0419): Invoice Functions.

POINVHO (PO0423): Invoice Header Optional Fields.

POINVD (PO0415): Invoice Cost Distributions.

POINVB (PO0414): Duplicate Invoices.

POINVI (PO0421): Invoice Postings Header. Result of POST button.

POINVT (PO0441): Invoice Post Cost Lines.

POINVM (PO0431): Invoice Postings Lines.

Returns

If you don’t like what you purchased then return it.

PORETH1,2 (PO0731): Returns Headers.

PORETL (PO0735): Return Lines.

PORETLO (PO0739): Return Detail Optional Fields.

PORETLL (PO0799): Return Line Lot Numbers.

PORETLS (PO0790): Return Line Serial Numbers.

PORETC (PO0729): Return Comments.

PORETG (PO0730): Return Functions.

PORETHO (PO0738): Return Header Optional Fields.

PORETI (PO0732): Return Postings. Result of POST button.

PORETM (PO0736): Return Posting Lines.

Credit/Debit Notes

POCRNH1,2 (PO0311): Credit/Debit Note Headers.

POCRNL (PO0315): Credit/Debit Note Details.

POCRNLO (PO0318): Credit/Debit Note Line Optional Fields.

POCRNLL (PO0829): Credit/Debit Note Line Lot Numbers.

POCRNLS (PO0820): Credit/Debit Note Line Serial Numbers.

POCRNC (PO0309): Credit/Debit Note Comments.

POCRNS (PO0320): Credit/Debit Note Add’l Costs .

POCRNG (PO0310): Credit/Debit Note Functions.

POCRNHO (PO0314): Credit/Debit Note Header Optional Fields.

POCRND (PO0326): Credit/Debit Note Cost Distributions.

POCRNB (PO0308): Cr/Dr Duplicate Notes.

POCRNI (PO0312): Credit/Debit Note Posting. Result of POST button.

POCRNM (PO0316): Credit/Debit Note Posting Line.

POCRNT (PO0312): Posting Additional Costs.

Data Processing

Most data processing in P/O happens at Day End time, whether this is initiated from I/C Day End Processing or you have the perform day end during posting option set. You can also choose to split the operation and do costing during posting and the rest (like sub-ledger batch generation) during I/C Day End. I/C Day End will call the Day End processing View in each application that subscribes to Day End (including O/E and P/O). For more on Day End Processing check out this blog post.

Day End Processing

PODEPR (PO0335): PO Day end Processing.

Purchase Orders

POPORZ (PO0636): PO Posting.

POHSTH (PO0380): Purchase Order History.

POHSTL (PO0384): Purchase Order History Lines.

POPORAH (PO0612): Purchase Order Audit.

POPORAL (PO0614): Purchase Order Audit Lines.

Receipts

PORCPZ (PO0720): Receipt Posting.

PORCPAH (PO0686): Receipt Audit Headers.

PORCPAL (PO0688): Receipt Audit Lines.

PORCPAQ (PO0690): Receipt Audit Prorate Lines.

PORCPAS (PO0692): Receipt Audit Costs.

PORCPJ (PO0702): Receipt Day End History Headers.

PORCPN (PO0712): Receipt Day End Lines.

PORCPU (PO0716): Receipt Day End Costs.

Invoices

POINVZ (PO0445):  PO Invoice Posting.

POINVAH (PO0409): Invoice Audit Headers.

POINVAL (PO0410): Invoice Audit Lines.

POINVAQ (PO0411): Invoice Audit Prorate Lines.

POINVAS (PO0412): Invoice Audit Costs.

POINVJ (PO0422): Invoice Day End History Headers.

POINVN (PO0432): Invoice Day End Lines.

POINVU (PO0442): Invoice Day End Additional Costs.

Returns

PORETZ (PO0744): Return Posting.

PORETAH (PO0724): Return Audit Headers.

PORETAL (PO0726): Return Audit Lines.

PORETAQ (PO0727): Return Audit Prorate Lines.

PORETAS (PO0728): Return Audit Costs.

PORETJ (PO0733): Returns Day End History Headers.

PORETN (PO0734): Returns Day End Lines.

Debit/Credit Notes

POCRNZ (PO0330): Dr/Cr Note Posting.

POCRNAH (PO0304): Dr/Cr Note Audit Headers.

POCRNAL (PO0305): Dr/Cr Note Audit Lines.

POCRNAQ (PO0306): Dr/Cr Note Audit Prorate Lines.

POCRNAS (PO0307): Dr/Cr Note Audit Costs.

POCRNJ (PO0313): Dr/Cr Note Day End History Headers.

POCRNN (PO0317): Dr/Cr Note Day End History Lines.

POCRNU (PO0322): Dr/Cr Note Historical Add’l Costs.

Day End Helpers

Updated during day end

POLINEZ (PO0520): Generic Line Data.

POCOSTZ (PO0295): Generic Cost Data.

POPRXH (PO0656): Cost Prorate Headers.

POPRXL (PO0658): Cost Prorate Lines.

POPRXP (PO0660): Line Cost Prorate Lines.

POPRXC (PO0650): Line Cost Prorate Details.

POSTTL (PO0800): Statistics – from Day End.

Periodic Processing

POCH (PO0302): PO Clear History.

POGLTR (PO0353): Create GL Batch.

Printing Helpers

POLBLS (PO0500): Mailing Labels.

POFLAG (PO0340): Mark Posting Journals Printed.

POPRNT (PO0640): Mark Documents Posted.

Miscellaneous

PODLDN (PO0336): Drill Down.

POINIT (PO0405): PO Activation (new).

POUPGD (PO0850): PO Activation Upgrade.

POINTCK (PO0407): Integrity Checker.

Summary

Hopefully this blog posting provides a bit more insight into how P/O operates and hopefully helps when you use P/O, interface to P/O or are creating custom P/O reports or forms.

 

Written by smist08

September 10, 2011 at 9:01 pm

Accpac A/R Database Structure

with 17 comments

This blog posting will be looking at the structure of some of the parts of the Accounts Receivable module of the Sage ERP Accpac product. You can find the structure of the individual tables in the Accpac Application Object Model (AOM) which you must view in IE. However this doesn’t tell you how the tables are related or how data flows from one table to another as you post various transactions. There are 103 tables in A/R, so obviously we can’t describe each in a single blog post. So we’ll just look at a few interesting cases. Understanding these relationships can be especially important to people writing sophisticated custom reports or BI Views.

The main types of database tables in A/R can be categorized as:

  • Setup
  • Master Data
  • Data Entry
  • Processing
  • Periodic Processing

Setup Tables

Setup tables tend to be fairly simple and their usage can be easily discerned from the AOM. These include things like ARDUN (Dunning Messages), ARRBC (Billing Cycles), ARRDC (Distribution Codes), ARRTA (Terms Codes) and such.

Master Data

The main master data table in A/R is the Customer table ARCUS.  ARITH (Items) is another master data table. The A/R Customers table is sync’ed with the companies table in SageCRM if the integration has been activated. The A/R Customers Optional Fields table (ARCUSO) is a detail under the ARCUS table. A detail means that there are multiple records for each ARCUS record. Some other tables are closely related to the ARCUS table and linked to it, namely:

  • ARCSM                 AR0022                 Customer Statistics
  • ARCSP                   AR0023                 Ship-to Locations
  • ARCMM               AR0021                 Customer Comments

Master data tables are like setup tables, but tend to contain the main data for the application, as opposed to a few records that configure the application.

Data Entry

Invoice Entry is the main set of tables for holding A/R Invoices. There is a table for the batch and then a table for the headers (there are multiple headers per batch) and a table for the invoice detail lines (again multiple per header). There are other detail views shown below where indentation indicates that there are multiple records for the table above.

  • ARIBC                    AR0031                 Invoice batches
    • ARIBH   AR0032                 Invoices
      • ARIBD                   AR0033                 Invoice Details
        • ARIBDO                                AR0401                 Invoice Detail Optional Fields
    • ARIBS                    AR0034                 Invoice Payment Schedules
    • ARIBT                    AR0035                 Invoice Detail Comments
    • ARIBHO                                AR0402                 Invoice Optional Fields

Receipt and Adjustment Entry is the set of tables for holding both receipt and adjustment batches. The first field of each batch record indicates the batch type.

  • ARBTA                  AR0041                 Receipt/Adj. Batches
    • ARTCR   AR0042                 Receipts/Adjustments
      • ARTCP   AR0044                 Applied Receipts/Adj.
      • ARTCU  AR0045                 Adjustment GL Distributions
      • ARTCN  AR0043                 Miscellaneous Receipts
      • ARTCRO               AR0406 Receipt/Adj Optional Fields

Refunds are the set of tables that refund documents are entered into.

  • ARRFB                   AR0140                 Refund Batches
    • ARRFH  AR0141                 Refund Entries
      • ARRFD  AR0142 Refund Details
        • ARRFDJ AR0145                 Refund Detail Jobs
    • ARRFHO               AR0143                 Refund Options Fields

Posting

Posted documents end up in the following tables:

  • AROBL                  AR0036                 Documents
    • AROBS  AR0037                 Document Sched Payments
      • AROBP  AR0038                 Document Payments
      • AROBLJ AR0200                 Open Document Details

When you post documents you get the following general data flows.

Invoices:

  • ARIBH AROBL
  • ARIBS → AROBS
  • ARIBD → AROBLJ

Receipts:

  • ARTCR → AROBL
  • ARTCP → AROBP

Note that for each ARTCP record you get 2 AROBP records. One attached to the Invoice’s AROBL which is positive to reduce the invoice and one attached to the Receipt’s AROBL which is negative to reduce the receipt.

Example

Let’s look at an example where we enter and post an Invoice and then enter and post a Receipt for that Invoice. Sometime it helps to do small transactions in Accpac and then look at the records produced in a database tool like the SQL Server Management Studio.

First let’s enter an Invoice, here are the main Invoice tables showing the values of a few key fields:

ARIBC    Batch     Batch Total               Number of Entries
         36              .44                        1

ARIBH   Batch     Entry     Customer            Document
        36           1      1100                IN0000012

ARIBD Batch       Entry     Line        Item         Quantity         Price
       36           1       20          CA-78           1             2.25

Now we post the Invoice which generates the following AROBL/AROBLJ records:

AROBL  Customer       Document Batch Entry Invoice Amount
        1100          IN0000012 36   1        2.44
AROBLJ Customer Document Line       Item      Quantity
        1100    IN0000012           CA-78       1

Now let’s enter a receipt for this Invoice:

ARBTA  BatchType     Batch    Entries          Total
         CA           40         1             2.44
ARTCR Type Batch     Entry Check#     Customer
      CA     40       1     1111       1100
ARTCP Type Batch       Entry Line     Customer Document    Amt
      CA    40           1   20       1100      IN000012   2.44

Now we post the Receipt applying it to the previous Invoice and get:

AROBL  Customer      Document Batch Entry   Invoice Amount
        1100         PY000077  40   1         -2.44
AROBP  Customer     Document Line Check#           Amt
       1100         IN0000012      1111           -2.44
       1100         PY0000077      1111            2.44

Audit Files

Below are the main audit files that contain the A/R Audit History:

  • Posting Journals/GL Transactions: ARPJS → ARPJH → ARPJD
  • Item Sales History: ARITH → ARITS
  • Customer Statistics: ARCUS → ARCSM

Statements

Statements are generated by the ARSTMT processing View (AR0077), this isn’t a table but it generates the data for a statements run. The data that it generates are in the following tables:

  • ARSTRUN                            AR0110                 Reprint Statement Header
    • ARSTCUS             AR0111                 Reprint Statement Customers
      • ARSTOBL              AR0112 Reprint Statement Customer Invoices
        • ARSTOBP             AR0113 Reprint Statement Customer Receivables
    • ARSTNAT             AR0114 Reprint Statement NAT Customers

This data is only cleared by Clear History. When you generate the report the View returns the STMTSEQ which you have to set as a report parameter to get the correct statement.

Aging

Aging data is generated by the ARAGE (AR0055) Processing View. The data is generated each time aging is run and cleared the next time aging is run. To run Aging: set fields with options and call .Process on ARAGE. When done copy the AGESEQ field from the View to the AGESEQ report parameter. The tables that are populated are:

Aged Trial Balance: ARSLCUS → ARAGED

Statistics

Customer statistics are stored in the ARCSM table which is a detail of the ARCUS table:

  • ARCUS → ARCSM

Once collecting statistics, do not change the accumulate statistics options as this will mess up the calculations since we don’t refactor already collected statistics. But if you do, they will eventually flush through the system.

If creating summary/dashboard type reports then it’s easier to get the data here than to add up totals from the posted transaction tables.

Searching Based on Document Number

A/R is document centered, given a document number; you can retrieve all the relevant information from Sage ERP Accpac.

For example, given an invoice IN0000001:

To find out its current balance (source and functional), original balance (source and functional), document date, discount, due date, etc:

  • Search AROBL table by the index IDINVC.

To find out the Invoice entry data

  • Search AROBL by the index IDINVC. The CNTBTCH/CNTITEM (BATCH and ENTRY) fields of AROBL point to the corresponding record(s) in ARIBC/ARIBH/ARIBD (BATCH/ENTRY are the primary key fields for those tables), which store the invoice entry.

To find out all the payments or adjustments against IN0000001:

  • Search AROBP by the IDINVC index, and you will find all the payments against this document.

To find out the G/L entries for this document:

  • First look up AROBL by IDINVC index to find out the posting sequence#, batch#, entry# for this document, and then search ARPJH table on the primary key  (TYPEBTCH,POSTSEQNCE,CNTBTCH,CNTITEM). TYPEBTCH is ‘IN’ for invoice, ‘AD’ for adjustment, ‘CA’ for payments. And the fields GLBATCH/GLENTRY in ARPJH point to the record in GLJEH.

Summary

The database schema structure of the Accpac Accounting modules can be quite complicated, but hopefully this article gives you an idea of how the main flow of data processing works in A/R.

Written by smist08

April 9, 2011 at 5:13 pm