Set-based imports
Lattice adds set-based import entities for the journals and master data that customers load in volume. Data management writes them with Set-based processing: a file of a hundred thousand lines is validated and written with a handful of statements per rule instead of one round trip through the entity per line.
Microsoft ships set-based processing for only a few entities - the general journal, fixed asset journal, budget register entries and a short list of reference data. None of the inventory, project, production, payment, customer invoice, vendor invoice or trade agreement journals, and none of the high-volume masters below, can be imported set-based in the standard application. These entities fill that gap.
Audience. The person who builds data management projects and integrations for high-volume loads: data migration, nightly interfaces, store or warehouse feeds. It assumes you know the Data management workspace (data projects, packages, the Target entities page).
The entities
| Entity (data management name) | AOT name | Target | Journal types / scope |
|---|---|---|---|
| Inventory journal lines (set-based) | MGCLatticeSetBasedInventoryJournalLineEntity | InventJournalTrans | Movement, profit/loss (adjustment), transfer, counting, project item, ownership change |
| Customer payment journal lines (set-based) | MGCLatticeSetBasedCustomerPaymentJournalLineEntity | LedgerJournalTrans | Customer payment journals |
| Customer invoice journal lines (set-based) | MGCLatticeSetBasedCustomerInvoiceJournalLineEntity | LedgerJournalTrans | Customer invoices in general journals |
| Vendor payment journal lines (set-based) | MGCLatticeSetBasedVendorPaymentJournalLineEntity | LedgerJournalTrans | Vendor payment journals |
| Vendor invoice journal lines (set-based) | MGCLatticeSetBasedVendorInvoiceJournalLineEntity | LedgerJournalTrans | Vendor invoice journal and invoice register |
| Project expense journal lines (set-based) | MGCLatticeSetBasedProjectExpenseJournalLineEntity | LedgerJournalTrans + LedgerJournalTrans_Project | Project expense (ledger Cost) journals |
| Project journal lines (set-based) | MGCLatticeSetBasedProjectJournalLineEntity | ProjJournalTrans | Hour and fee journals |
| Trade agreement journal lines (set-based) | MGCLatticeSetBasedTradeAgreementJournalLineEntity | PriceDiscAdmTrans | Price, line, multiline and total discount agreements |
| Production picking list lines (set-based) | MGCLatticeSetBasedProductionPickingListLineEntity | ProdJournalBOM | Production picking list journals |
| Production report as finished lines (set-based) | MGCLatticeSetBasedProductionReportAsFinishedLineEntity | ProdJournalProd | Report as finished journals |
| Production route card lines (set-based) | MGCLatticeSetBasedProductionRouteCardLineEntity | ProdJournalRoute | Route card and job card journals |
| Demand forecast lines (set-based) | MGCLatticeSetBasedDemandForecastLineEntity | ForecastSales | Demand forecast lines (insert or update by entry number) |
| BOM lines (set-based) | MGCLatticeSetBasedBomLineEntity | BOM | Lines of existing BOMs (insert only) |
| Bank statement lines (set-based) | MGCLatticeSetBasedBankStatementLineEntity | BankStmtISOReportEntry | Lines of an existing open bank statement |
| Fixed asset books (set-based) | MGCLatticeSetBasedAssetBookEntity | AssetBook | Asset books (insert, or update while not acquired) |
All of them are import-only and not published to OData: they exist for data management packages.
Each has a ...Maintain and a ...View privilege (granted through the Lattice data entities duties) like
every other Lattice entity.
The import process
A set-based import goes through data management the standard way as far as staging. At the copy from staging
to target, data management hands the whole execution to the entity's copyCustomStagingToTarget, which runs
the entity's Lattice import class once for all the staging rows.
What one run does
Every import class follows the same shape (MGCLatticeSetBasedImport.run). Validation and resolution happen
outside any transaction and refuse rows one rule at a time; the writes happen in a single transaction at the end.
A staging row's status
How they behave
Set-based processing is on by default. When the database synchronization registers one of these entities in data management, Lattice answers data management's "start set-based?" question with yes, and an AOS-startup check registers any that are still missing. You can see it on Data management > Data entities: the Set-based processing column is selected.
An existing registration is not otherwise changed, so an administrator who turns Set-based processing off keeps
that setting - and the import then refuses the rows (below). The one exception is the staging table: when an entity's
staging table is replaced (a V2 table, because a staging column changed type), the startup check points the
registration at the new table. The database synchronization does not do that for an entity a data project uses.
Row by row is refused. If someone turns Set-based processing off, data management falls back to writing the entity one row at a time; these entities refuse that with "<entity> imports only with set-based processing. In Data management, open Data entities, select the entity and turn on Set-based processing." They exist to import in bulk - their row-by-row write would have to repeat every rule of the journal forms.
Validation is per rule, not per row. Each rule refuses all the rows that break it in one statement.
A refused row is marked Error in staging and its message appears in the execution's validation log
(View staging data > Validation errors, or GetExecutionErrors through the package API). The other rows
still import. A row that breaks several rules is reported for the first one.
One transaction for the transfer. After validation, the journals and lines are written in one transaction. If anything fails there, nothing is written, the staging rows stay Not started, and the import can simply be run again after the cause is fixed. Rows already Completed are never written twice.
Journals: JournalNumber and LineNumber
For the journal entities, JournalNumber means one of two things:
- an existing open journal - the lines are added to it (its journal name is used; a posted or system-blocked journal is refused, and so is a ledger journal that is in approval workflow);
- any other value - a grouping key. Every distinct key (per journal name) becomes one new journal
numbered from the journal name's number sequence, and its staging rows are updated to the new number, so
the staging data shows where each line went. Use one key per journal you want:
BATCH-2026-10-16-A, a store number, a date.
LineNumber is required (greater than zero) and must not already exist in the journal: line numbers
order the lines and keep re-runs safe.
What is defaulted
The import defaults what the journal forms would: the transaction date (today), the cost or price from the item or price setup, the offset account and worker from the journal, the inventory and financial dimensions from the item. Anything the file gives wins, except where an entity's section says otherwise. What each entity defaults is listed in its section below.
What is not done
A set-based import does not run the target tables' own insert() logic. Each entity sets every column that
logic would have set, or refuses the row, so the journals it creates post with the standard posting. Where the standard insert() creates
records the rest of the application depends on, the entity calls that standard step for each line instead
of re-creating it; the sections below say where.
Using them
In the Data management workspace
- Create an import project, add the entity (for example Inventory journal lines (set-based)) with your source format (CSV, Excel, XML...), and upload the file. Column names are the entity field names below.
- Run the import, in batch for anything large (Import in batch), as for any entity.
- Check Execution summary: refused rows are listed with their messages under the staging data.
Through the package API
Integrations use the standard data management package REST API (GetAzureWriteUrl, ImportFromPackage,
GetExecutionSummaryStatus, GetExecutionErrors) with a package whose manifest names the entity. The
package is a zip of Manifest.xml, PackageHeader.xml and the data file; for a CSV, use the CSV-Unicode source
format (UTF-16 with a byte order mark).
Name the entity by its data management name (the label, for example Inventory journal lines (set-based)).
On an environment where the entity was registered before its labels were deployed - typically only a
development box - the registered name is the AOT name instead; Data management > Data entities shows
which.
Performance notes
- Run large files in batch, and keep the usual data management hygiene: clean staging history, update statistics before a big migration (see Microsoft's Optimize data migration guidance).
- Validation and resolution cost depends on the number of distinct values, not rows: a file with 100,000 lines and 40 warehouse/location combinations resolves 40 inventory dimensions.
- Splitting one huge file into several files run one after another is fine; within one execution everything is one transfer transaction.
Inventory journal lines (set-based)
MGCLatticeSetBasedInventoryJournalLineEntity - inventory journal lines of the movement, profit/loss
(adjustment), transfer, counting, project and ownership change journal types. The journal
type comes from the journal name.
| Column | Required | Notes |
|---|---|---|
JournalNumber | yes | Existing open journal, or a grouping key (see above). |
JournalNameId | for new journals | An inventory journal name (USMF: IMov, IAdj, ITrf, ICnt, ProjItem, IOwner). |
LineNumber | yes | > 0, new in the journal. |
TransactionDate | Defaults to today. | |
ItemNumber | yes | |
InventoryQuantity | yes (not counting) | Positive = receipt, negative = issue; for a transfer, the quantity issued from the source. |
CountedQuantity | counting | The counted quantity; the line's quantity becomes counted minus on-hand. |
UnitCost, UnitCostQuantity, CostAmount, FixedCostCharges | Unit cost defaults to the item's inventory price, cost amount to quantity x unit cost / price quantity. | |
InventorySiteId, InventoryWarehouseId, WarehouseLocationId, ItemBatchNumber, ItemSerialNumber, InventoryStatusId, LicensePlateNumber, ProductConfigurationId, ProductSizeId, ProductColorId, ProductStyleId, ProductVersionId, InventoryOwnerId | Inventory dimensions; each value must exist. | |
ToInventorySiteId ... ToInventoryOwnerId | transfer / ownership change | Destination dimensions. Empty ones are the source's; a transfer's destination site follows its destination warehouse. |
ProjectId, ProjectCategoryId, ProjectLinePropertyId, ProjectSalesPrice, ProjectSalesCurrencyCode | project journals | |
OffsetMainAccountId | Main account of the offset (counter) account; defaults to the journal's. | |
DefaultDimensionDisplayValue | Financial dimensions in the data entity display format; defaults to the item's. |
Validation: line number, journal name, unsupported journal type (BOM, tag counting and fixed asset inventory journals are refused - BOM journals explode the BOM when lines are entered, tag counting has its own table), journal posted or blocked, journal in approval workflow (submitted, pending approval or approved), line number already used, item, zero quantity, negative counted quantity, every dimension value, transfer without a destination warehouse, project journal without a project, project, offset main account, financial dimension values.
Items on hold are not checked at import. An item on hold for inventory transactions (released product > Default order settings > Inventory > Stopped) is imported. The standard entity refuses it at import instead. The line cannot go further: the journal's Validate and Post both refuse it with "Product <item> is on hold for inventory transactions." and nothing posts. Remove the line, or take the item off hold, and post again.
The one per-line step. The standard InventJournalTrans.insert() creates each line's estimated
inventory transactions (ordered / on-order, with the on-hand and reservation updates they carry). A journal
without them cannot be posted ("inventory transactions with status Ordered... are insufficient"). Those are
the inventory engine's own records, so each line is written with the standard InventUpd_Estimated call
rather than rebuilt; everything else (validation, dimensions, accounts, journals, defaults) is set-based.
Measured on 300 lines, this takes under half the time of the standard journal framework alone, before the
overhead row-by-row entity processing adds on top.
Customer payment journal lines (set-based)
MGCLatticeSetBasedCustomerPaymentJournalLineEntity - lines of customer payment journals (ledger journal type
CustPayment), written to LedgerJournalTrans. Each row is one complete voucher: the customer on one side, the
offset account (normally a bank account) on the other. Rows whose journal name has another type are refused.
| Column | Required | Notes |
|---|---|---|
JournalNumber | yes | Existing open journal, or a grouping key (see Journals above). |
JournalNameId | for new journals | A customer payment journal name (USMF: CustPay). For an existing journal, the journal's name is used. |
LineNumber | yes | > 0, new in the journal. |
TransactionDate | Defaults to today. | |
Voucher | Empty = one voucher per line from the journal name's voucher series, in line number order (the ledger's temporary voucher series when the journal name allocates vouchers at posting). A value in the file is used as given. | |
CustomerAccount | yes | The line's account. Must exist in the company. |
Description | The line text. | |
CurrencyCode | Defaults to the customer's currency, else the accounting currency. | |
ExchangeRate | Empty = the rate for the currency on the transaction date. A line in the accounting currency gets its rate too. | |
DebitAmount, CreditAmount | one of them | Exactly one, not zero. A receipt is a credit. |
MethodOfPayment | Defaults to the customer's (USMF: CHECK). | |
BankTransactionType | Defaults to the method of payment's. | |
PaymentReference | ||
InvoiceId | The invoice the receipt pays, written to the line. It does not mark the invoice for settlement (see settlement below). | |
PostingProfile | Defaults to the accounts receivable parameters' posting profile. | |
OffsetAccountType | Ledger, Bank, Cust or Vend. | |
OffsetAccountDisplayValue | The offset account (USMF: Bank USMF OPER). A bank, customer or vendor account number is given as is, delimiter included (US-101). A ledger offset is a main account alone (110180) or a full display value with its dimensions. Empty = the journal's offset account and type: the existing journal's header, else the journal name's. | |
DefaultDimensionDisplayValue | Financial dimensions of the line, in the data entity display format; defaults to the customer's. | |
OffsetDefaultDimensionDisplayValue | Financial dimensions of the offset; for a bank offset, defaults to the bank account's. Must be empty for a ledger offset - its dimensions are part of OffsetAccountDisplayValue. |
Validation: line number, journal name, journal type (not a customer payment journal), journal posted, blocked by the system or in approval workflow (submitted, pending approval or approved), line number already used, customer, debit and credit both given or both zero, currency, offset account type (only ledger, bank, customer, vendor), no offset account (none in the file and none on the journal), offset financial dimensions on a ledger offset, no voucher while the journal name has no voucher series and does not allocate vouchers at posting, method of payment, bank transaction type, posting profile. Then, while resolving: no exchange rate for the currency on the transaction date, customer account not resolved, offset account not resolved, financial dimension values not resolved, a ledger offset whose main account does not allow manual entry (blocked on the main account or by the legal entity override).
Defaults, as the payment journal form sets them when the customer is entered: transaction date today; currency and method of payment from the customer; bank transaction type from the method of payment (posting stops with "Bank transaction type not specified" without one); posting profile from the accounts receivable parameters; due date = transaction date; financial dimensions from the customer, and a bank offset's from the bank account. Every line gets transaction type Payment, is approved with the current user's worker as approver, and is settled against open transactions when the accounts receivable parameters settle automatically (no settlement otherwise). After the insert, the lines get what the standard line insert adds - acknowledgement date = transaction date, the journal's document number, the company's payment instructions, tax direction control when the tax parameters require it, the account name - and the journal header's debit, credit and balance totals are recalculated.
Set-based throughout. The lines are written with one insert_recordset; only vouchers are drawn per line, and
exchange rates and account names are looked up once per distinct value.
After the import the journal is an open customer payment journal; nothing is posted. The lines read back through
the standard CustomerPaymentJournalLines entity. The created journals post with the standard ledger journal
posting, which writes the customer payment transactions.
Customer invoice journal lines (set-based)
MGCLatticeSetBasedCustomerInvoiceJournalLineEntity - customer invoices without a sales order, written as customer
lines of general journals (ledger journal type Daily, the journal type of the standard customer invoice journal),
in LedgerJournalTrans. One row is one whole invoice: the customer, the invoice number, the amount and the revenue
account. Posting the journal posts the invoices to the customers, with sales tax, and leaves them open for settlement.
Rows whose journal name has another type are refused.
| Column | Required | Notes |
|---|---|---|
JournalNumber | yes | Existing open journal, or a grouping key (see Journals above). |
JournalNameId | for new journals | A general journal name (USMF: GenJrn). For an existing journal, the journal's name is used. |
LineNumber | yes | > 0, new in the journal. |
TransactionDate | Defaults to today. | |
Voucher | Empty = one voucher per line from the journal name's voucher series, in line number order (the ledger's temporary voucher series when the journal name allocates vouchers at posting). A value in the file is used as given. | |
CustomerAccount | yes | The line's account. Must exist in the company. |
InvoiceNumber | yes | Must not be used by a posted transaction of the customer, and must appear only once per customer in the import. |
DocumentNumber | Defaults to the journal's document number, if it has one. | |
DocumentDate | The invoice date. When given, the due date is calculated from it. | |
DueDate | Defaults from the terms of payment and the customer's payment day, from the document date when there is one, else from the transaction date; without terms of payment, that date itself. | |
TermsOfPayment | Defaults to the customer's. | |
Description | The line text. | |
CurrencyCode | Defaults to the customer's currency, else the accounting currency. | |
ExchangeRate | Empty = the rate for the currency on the transaction date. A line in the accounting currency gets its rate too. | |
DebitAmount, CreditAmount | one of them | Exactly one, not zero. An invoice is a debit; a credit is a credit note. |
MethodOfPayment | Defaults to the customer's. | |
BankTransactionType | Defaults to the method of payment's. | |
PaymentReference | ||
SalesTaxGroup | Defaults to the customer's. | |
ItemSalesTaxGroup | Defaults to the revenue main account's item sales tax group, else the tax parameters' default unless the main account is tax free. | |
PostingProfile | Defaults to the accounts receivable parameters' posting profile. | |
OffsetAccountType | Ledger (the default when empty). Other offset types are refused. | |
OffsetAccountDisplayValue | The revenue account (USMF: 401100): a main account alone or a full display value with its dimensions. A main account closed to manual entry, such as a control account (USMF: 130100), is refused. Empty = the journal's offset account (the existing journal's header, else the journal name's). | |
DefaultDimensionDisplayValue | Financial dimensions of the customer line, in the data entity display format; defaults to the customer's. The revenue account's dimensions are part of OffsetAccountDisplayValue. |
Validation: line number, journal name, journal type (not a general journal), journal posted, blocked by the system or in approval workflow (submitted, pending approval or approved), line number already used, customer, debit and credit both given or both zero, currency, offset account type (only ledger), no offset account (none in the file and none on the journal), no voucher while the journal name has no voucher series and does not allocate vouchers at posting, invoice number missing, invoice number already used by a posted transaction of the customer, invoice number repeated for the customer in the import (every row with it is refused), customer on hold for all transactions, customer on hold for invoicing on a debit line (a credit note is accepted), terms of payment, method of payment, bank transaction type, posting profile, sales tax group, item sales tax group. Then, while resolving: no exchange rate for the currency on the transaction date, customer account not resolved, revenue account not resolved, financial dimension values not resolved, a revenue main account that does not allow manual entry (blocked on the main account or by the legal entity override). The customer's credit limit is checked by posting, as for any journal.
Defaults, as the journal sets them when the customer is entered: transaction date today; currency, terms of payment, method of payment, payment specification, cash discount and bank account from the customer; bank transaction type from the method of payment; due date from the terms of payment; posting profile from the accounts receivable parameters; sales tax group from the customer and item sales tax group from the revenue account (or the tax parameters); financial dimensions from the customer. Every line gets transaction type General journal, is approved with the current user's worker as approver, and has no settlement; sales tax is calculated when the journal is posted. After the insert, the lines get the acknowledgement date, the journal's document number, the company's payment instructions, tax direction control and the account name, and the header totals are recalculated, as for customer payments.
The per-line standard step. A line whose customer has a cash discount gets it calculated by the standard
LedgerJournalEngine::determineCashDiscountForEntities, one line at a time, as the standard customer invoice journal
entity does. Vouchers are drawn per line; due dates are worked out once per distinct terms of payment, date and
payment day. Everything else, including the line insert, is set-based.
After the import the journal is an open general journal; nothing is posted. The lines read back through the
standard GeneralLedgerCustInvoiceJournalLines entity, the journal through GeneralLedgerCustInvoiceJournalHeaders.
The created journals post with the standard ledger journal posting, which writes one customer
transaction per line (the amount plus the sales tax), open for settlement. Post them from the journal page, or from
your system with postLedgerJournal (Post an existing journal).
Vendor payment journal lines (set-based)
MGCLatticeSetBasedVendorPaymentJournalLineEntity - lines of vendor payment journals (ledger journal type
Payment), written to LedgerJournalTrans. Each row is one complete voucher: the vendor on one side, the offset
account (normally a bank account) on the other. Rows whose journal name has another type are refused.
| Column | Required | Notes |
|---|---|---|
JournalNumber | yes | Existing open journal, or a grouping key (see Journals above). |
JournalNameId | for new journals | A vendor payment journal name (USMF: VendPay). For an existing journal, the journal's name is used. |
LineNumber | yes | > 0, new in the journal. |
TransactionDate | Defaults to today. | |
Voucher | Empty = one voucher per line from the journal name's voucher series, in line number order (the ledger's temporary voucher series when the journal name allocates vouchers at posting). A value in the file is used as given. | |
VendorAccount | yes | The line's account. Must exist in the company. |
Description | The line text. | |
CurrencyCode | Defaults to the vendor's currency, else the accounting currency. | |
ExchangeRate | Empty = the rate for the currency on the transaction date. A line in the accounting currency gets its rate too. | |
DebitAmount, CreditAmount | one of them | Exactly one, not zero. A payment is a debit. |
MethodOfPayment | Defaults to the vendor's. USMF: ELECTRONIC posts directly; CHECK needs a generated check number before posting. | |
BankTransactionType | Defaults to the method of payment's. | |
PaymentReference | ||
PostingProfile | Defaults to the accounts payable parameters' posting profile. | |
OffsetAccountType | Ledger, Bank, Cust or Vend. | |
OffsetAccountDisplayValue | The offset account (USMF: Bank USMF OPER). A bank, customer or vendor account number is given as is, delimiter included. A ledger offset is a main account alone or a full display value with its dimensions. Empty = the journal's offset account and type: the existing journal's header, else the journal name's. | |
DefaultDimensionDisplayValue | Financial dimensions of the line, in the data entity display format; defaults to the vendor's. | |
OffsetDefaultDimensionDisplayValue | Financial dimensions of the offset; for a bank offset, defaults to the bank account's. Must be empty for a ledger offset. |
There is no invoice column: on a vendor line the journal's invoice field is the line's own invoice number, not the invoice it pays.
Validation: line number, journal name, journal type (not a vendor payment journal), journal posted, blocked by the system or in approval workflow (submitted, pending approval or approved), line number already used, vendor, debit and credit both given or both zero, currency, offset account type (only ledger, bank, customer, vendor), no offset account (none in the file and none on the journal), offset financial dimensions on a ledger offset, no voucher while the journal name has no voucher series and does not allocate vouchers at posting, method of payment, bank transaction type, posting profile. Then, while resolving: no exchange rate for the currency on the transaction date, vendor account not resolved, offset account not resolved, financial dimension values not resolved, a ledger offset whose main account does not allow manual entry.
Defaults, as the payment journal form sets them when the vendor is entered: transaction date today; currency and method of payment from the vendor; bank transaction type from the method of payment; posting profile from the accounts payable parameters; due date = transaction date; financial dimensions from the vendor, and a bank offset's from the bank account. Every line gets transaction type Payment, is approved with the current user's worker as approver, and is settled against open transactions when the accounts payable parameters settle automatically (no settlement otherwise). After the insert, the lines get the acknowledgement date, the journal's document number, the company's payment instructions, tax direction control and the account name, and the header totals are recalculated, as for customer payments.
Set-based throughout. One insert_recordset for the lines; only vouchers are drawn per line.
After the import the journal is an open vendor payment journal; nothing is posted. The lines read back through the
standard VendorPaymentJournalLines entity. Posting the created journals writes the vendor payment transactions.
Vendor invoice journal lines (set-based)
MGCLatticeSetBasedVendorInvoiceJournalLineEntity - vendor invoices without a purchase order, written to
LedgerJournalTrans. One row is one whole invoice: the vendor, the invoice number, the amount and the offset account.
The journal name decides the journal type, and with it what posting does:
- a journal name of type
VendInvoiceRegister(USMF: APInvoice) makes a vendor invoice journal. Posting it posts the invoice to the vendor and the expense account outright. Despite the enum name, this is the invoice journal, not the register; - a journal name of type
PurchaseLedger(USMF: APInvReg) makes an invoice register. Posting it records the invoice against the posting profile's pending vendor invoice account; the invoice then waits in the vendor invoice pool to be approved with an approval journal.
Rows whose journal name has any other type are refused. One import can carry both kinds of rows.
| Column | Required | Notes |
|---|---|---|
JournalNumber | yes | Existing open journal, or a grouping key (see Journals above). |
JournalNameId | for new journals | A vendor invoice journal or invoice register journal name (USMF: APInvoice, APInvReg). For an existing journal, the journal's name is used. |
LineNumber | yes | > 0, new in the journal. |
TransactionDate | Defaults to today. | |
Voucher | Empty = one voucher per line from the journal name's voucher series, in line number order (the ledger's temporary voucher series when the journal name allocates vouchers at posting). A value in the file is used as given. | |
VendorAccount | yes | The line's account. Must exist in the company. |
InvoiceNumber | yes | Must not be a posted invoice of the vendor, and must appear only once per vendor in the import. |
DocumentDate | The invoice date. When given, the due date is calculated from it. | |
DueDate | Defaults from the terms of payment, from the document date when there is one, else from the transaction date; without terms of payment, that date itself. | |
TermsOfPayment | Defaults to the vendor's. | |
Description | The line text. | |
CurrencyCode | Defaults to the vendor's currency, else the accounting currency. | |
ExchangeRate | Empty = the rate for the currency on the transaction date. | |
DebitAmount, CreditAmount | one of them | Exactly one, not zero. An invoice is a credit. |
MethodOfPayment | Defaults to the vendor's. | |
BankTransactionType | Defaults to the method of payment's. | |
SalesTaxGroup | Defaults to the vendor's. | |
ItemSalesTaxGroup | Defaults to the ledger offset main account's item sales tax group, else the tax parameters' default unless the main account is tax free. | |
PostingProfile | Defaults to the accounts payable parameters' posting profile. | |
ApproverPersonnelNumber | Personnel number of the worker who approves the invoice. Defaults to the current user's worker. An invoice register line cannot be imported without an approver. | |
OffsetAccountType | Ledger or Bank. | |
OffsetAccountDisplayValue | The expense account (USMF: 601500): a main account alone or a full display value with its dimensions. A main account closed to manual entry, such as a control account (USMF: 200110), is refused. Empty = the journal's offset account and type (the existing journal's header, else the journal name's); an invoice register line with no offset there either gets the posting profile's pending vendor invoice account. | |
DefaultDimensionDisplayValue | Financial dimensions of the line, in the data entity display format; defaults to the vendor's. | |
OffsetDefaultDimensionDisplayValue | Financial dimensions of the offset; for a bank offset, defaults to the bank account's. Must be empty for a ledger offset. |
Validation: line number, journal name, journal type (not a vendor invoice journal or invoice register), journal posted, blocked by the system or in approval workflow (submitted, pending approval or approved), line number already used, vendor, debit and credit both given or both zero, currency, offset account type (only ledger, bank), no offset account, offset financial dimensions on a ledger offset, no voucher while the journal name has no voucher series and does not allocate vouchers at posting, invoice number missing, invoice number already posted for the vendor, invoice number repeated for the vendor in the import (every row with it is refused), approver personnel number not found, an invoice register line without an approver (no personnel number given and the user is not linked to a worker), terms of payment, method of payment, bank transaction type, posting profile, sales tax group, item sales tax group. Then, while resolving: no exchange rate for the currency on the transaction date, vendor account not resolved, offset account not resolved, financial dimension values not resolved, a ledger offset whose main account does not allow manual entry (the pending vendor invoice account an invoice register line was given by default is exempt, as in the journal form).
Defaults, as the journal forms set them when the vendor is entered: transaction date today; currency, terms of payment, method of payment, payment specification, payment ID, cash discount and bank account from the vendor; bank transaction type from the method of payment; due date from the terms of payment; posting profile from the accounts payable parameters; approver as above; financial dimensions from the vendor, and a bank offset's from the bank account. The sales tax groups are defaulted on every vendor invoice journal line, but on an invoice register line only when the accounts payable parameters calculate sales tax at registration. Every line gets transaction type Vend and no settlement. A vendor invoice journal line is approved. An invoice register line is not approved (the approval journal does that) and skips the manual-entry check of its pending vendor invoice account. After the insert, the lines get the acknowledgement date, the journal's document number, the company's payment instructions, tax direction control and the account name, and the header totals are recalculated, as for customer payments.
The per-line standard step. A line whose vendor has a cash discount gets it calculated by the standard
LedgerJournalEngine::determineCashDiscountForEntities, one line at a time, as the standard vendor invoice journal
entity does. Vouchers are drawn per line; due dates and pending vendor invoice accounts are worked out once per
distinct value. Everything else, including the line insert, is set-based.
After the import the journal is open; nothing is posted. Vendor invoice journal lines read back through the
standard VendInvoiceJournalLines entity, invoice register lines through VendInvoiceRegisterLines. Both kinds
post with the standard posting: the vendor invoice journal posts the invoices to the vendor, and the invoice register
posts with the pending vendor invoice account and the line's approver.
Project expense journal lines (set-based)
MGCLatticeSetBasedProjectExpenseJournalLineEntity - lines of project expense journals. These are ledger
journals of type Cost: each row becomes a LedgerJournalTrans line whose account is the project, debited with the
expense, plus its LedgerJournalTrans_Project sub-line (category, quantity, prices). The offset is the ledger or bank
account the expense is paid from. Rows whose journal name has another type are refused.
| Column | Required | Notes |
|---|---|---|
JournalNumber | yes | Existing open journal, or a grouping key (see Journals above). |
JournalNameId | for new journals | A project expense journal name (USMF: PrjJrn). For an existing journal, the journal's name is used. |
LineNumber | yes | > 0, new in the journal. |
TransactionDate | Defaults to today. | |
Voucher | Empty = one voucher per line from the journal name's voucher series, in line number order (the ledger's temporary voucher series when the journal name allocates vouchers at posting). A value in the file is used as given. | |
ProjectId | yes | The line's account. Must exist (USMF: 000184). |
ProjectCategoryId | yes | Must be an expense (cost) category (USMF: Travel). An hour category such as Journeyman is refused. |
ProjectLinePropertyId | Defaults from the project's line property setup for the project and category. | |
ProjectDate | The project transaction date; defaults to the transaction date. | |
Quantity | Defaults to 1. | |
CostPrice | The cost price per unit, written to the sub-line. | |
SalesPrice | ||
SalesCurrencyCode | Defaults to the project's sales currency. | |
Description | The line text. | |
CurrencyCode | Defaults to the accounting currency. | |
ExchangeRate | Empty = the rate for the currency on the transaction date. | |
DebitAmount, CreditAmount | When both are empty, the debit is quantity x cost price. When given, exactly one, not zero; it is used as given, not checked against quantity x cost price. | |
OffsetAccountType | Ledger or Bank. | |
OffsetAccountDisplayValue | The account the expense is paid from (USMF: Ledger 110180, petty cash): a main account alone or a full display value with its dimensions, or a bank account number. Empty = the journal's offset account and type: the existing journal's header, else the journal name's. | |
DefaultDimensionDisplayValue | Financial dimensions of the line, in the data entity display format; defaults to the project's. | |
OffsetDefaultDimensionDisplayValue | Financial dimensions of the offset; for a bank offset, defaults to the bank account's. Must be empty for a ledger offset. |
Validation: line number, journal name, journal type (not a project expense journal), journal posted, blocked by the system or in approval workflow (submitted, pending approval or approved), line number already used, project, debit and credit both given or both zero (with no amount and no cost price there is nothing to default from), currency, offset account type (only ledger, bank), no offset account, offset financial dimensions on a ledger offset, no voucher while the journal name has no voucher series and does not allocate vouchers at posting, project category missing, not found or not an expense category, line property. Then, while resolving: no exchange rate for the currency on the transaction date, project account not resolved, offset account not resolved, financial dimension values not resolved, a ledger offset whose main account does not allow manual entry.
Defaults, as the project expense journal form sets them: transaction date today; project date = transaction date; currency = accounting currency; quantity 1; debit = quantity x cost price; line property from the project's line property setup; sales currency from the project; financial dimensions from the project, and a bank offset's from the bank account. The sub-line gets the project's invoice sales tax group and the category's item sales tax group (the project sales tax), and module Project. Purchase sales tax is not defaulted on the line. Transaction type and approval stay empty, as the form leaves them for a ledger or bank offset (it approves only a customer or vendor offset). Every line gets no settlement. After the insert, the lines get the acknowledgement date, the journal's document number, the company's payment instructions, tax direction control and the account name, and the header totals are recalculated, as for customer payments.
Two inserts, two per-line draws. The lines and their project sub-lines are written with one insert_recordset
each. Each sub-line needs a project transaction id, which the standard sub-line insert draws from the project cost
transaction number sequence; the import draws one per line the same way (ProjCostTrans::newTransId), in journal and
line number order, inside the transfer transaction. Vouchers are drawn per line too; the line property is looked up
once per distinct project and category, the sales currency and invoice sales tax group once per distinct project.
After the import the journal is an open project expense journal; nothing is posted. The lines read back through
the standard ExpenseJournalLines entity. The created journals post with the standard ledger journal
posting, which writes project cost transactions (ProjCostTrans) under the sub-lines' transaction ids.
Project journal lines (set-based)
MGCLatticeSetBasedProjectJournalLineEntity - project journal lines of the hour and fee journal types. The
journal type comes from the journal name: a name of type Hour gives hour lines, a name of type Fee (Revenue) gives fee
lines. For an existing journal, the journal's own name is used. Beginning balance journal names are refused (their
lines are held in other tables).
| Column | Required | Notes |
|---|---|---|
JournalNumber | yes | Existing open journal, or a grouping key (see Journals above). |
JournalNameId | for new journals | A project journal name of type Hour or Fee (USMF: Hour, Fee). For an existing journal, the journal's name is used. |
JournalDescription | Description of a new journal: the first non-empty value among its rows. | |
LineNumber | yes | > 0, new in the journal. |
ProjectDate | The project date. Defaults to the existing journal's project date, then to today. | |
VoucherDate | The voucher (ledger) date. Defaults by the journal's voucher date rule. | |
ProjectId | yes | Defaults to the existing journal's project. The project's stage must allow journals (USMF: 000184). |
CategoryId | yes | An hour category on an hour journal, a revenue category on a fee journal (USMF: Journeyman, Setup). Defaults to the existing journal's category. |
LinePropertyId | Defaults to the existing journal's line property, then to the line property setup. | |
WorkerPersonnelNumber | when the category requires a worker | The worker's personnel number (USMF: 000001). It must be a project resource in the company. Hour lines without one take the existing journal's worker. |
Quantity | hour lines | Hours on an hour line, number of fees on a fee line. A fee line defaults to 1. |
CostPrice, SalesPrice, SalesCurrencyCode | Defaults: the currency of the project's contract, the hour cost price, the hour sales price (hour lines) or the revenue sales price (fee lines). A price of 0 counts as not given. | |
ActivityNumber | when the project requires one on hours | Must exist, be open, belong to the project and be selectable. |
Text | Defaults to the existing journal's text. | |
DefaultDimensionDisplayValue | Financial dimensions in the data entity display format. Defaults to the project's dimensions merged with the resource's. |
Validation: line number, journal name, beginning balance journal type, journal posted or blocked, line number already used, project missing, project, project stage does not allow journals or the project is not active (a fee line also needs the project active for revenue, or a fixed-price billing method), category missing, category, category type does not match the journal, category not allowed (on a fee journal an estimate or subscription category; an indirect cost component category; an inactive category unless the project parameters show inactive categories), category not valid for the project, worker. When the project parameters validate workers: a category that requires a worker gets none from the file or the journal; a category that never has a worker gets one from the file or the journal (customer retention, indirect component and estimate categories are exempt). Then: hour line without hours, line property, currency, activity not valid, hour line without an activity on a project that requires one. While resolving: worker is not a project resource in this company, worker terminated (unless the project parameters show inactive employees), financial dimension values.
Defaults: an existing journal gives its project, category, line property, text, project date and, on hour journals, its worker to the rows that leave them empty. The project date then defaults to today, and a fee line's quantity to 1. The rest is defaulted once per distinct combination of journal, project, category, line property, worker, project date, voucher date, currency, cost price, sales price and financial dimensions - not once per row - by the standard project journal line methods: currency, line property, resource category, voucher date, the contract's price group, the sales tax groups, the financial dimensions, the cost and sales price, the period date and, on hour lines, the indirect component group. Anything the file gives wins.
Vouchers. When the journal name draws vouchers on entry, the lines get one voucher per journal and voucher date (or per voucher date and worker, when the journal changes voucher per worker). An existing line of the journal with the same key keeps its voucher for the new lines, as the journal framework does.
Mostly set-based. The lines are written with one insert_recordset, and the journals' line count, quantity and
sales totals are recalculated. Two steps run per line: the project transaction id (a number sequence) and, when the
project parameter Track committed cost for hour journals is on, the hour line's committed cost, written by the
standard cost control logic that ProjJournalTrans.insert() calls. The hour and fee journals it creates post
with the standard project journal posting, to hour and revenue transactions.
Trade agreement journal lines (set-based)
MGCLatticeSetBasedTradeAgreementJournalLineEntity - trade agreement journal lines for sales and purchase prices,
line discounts, multiline discounts and total discounts. New journals are created from the journal name.
Posting is a separate step, the standard trade agreement journal posting, as for journals entered by hand.
| Column | Required | Notes |
|---|---|---|
JournalNumber | yes | Existing unposted journal, or a grouping key (see Journals above). |
JournalNameId | for new journals | A trade agreement journal name (USMF: S_Price). For an existing journal, the journal's name is used. |
JournalDescription | Replaces the journal name's description on a new journal (first non-empty value among its rows). | |
LineNumber | yes | > 0, new in the journal. |
TradeAgreementType | yes | The relation, by name or label: PricePurch, LineDiscPurch, MultiLineDiscPurch, EndDiscPurch, PriceSales, LineDiscSales, MultiLineDiscSales, EndDiscSales. A text column: an empty or unknown value is refused. Postage discounts (MCRPostageDisc) are refused. |
ItemCode, ItemRelation | Table (an item number), GroupId (an item price/discount group of the relation's group type) or All (relation empty). An empty item code is Table. | |
AccountCode, AccountRelation | Table (a customer on the sales side, a vendor on the purchase side), GroupId (a customer or vendor price/discount group of that side and group type) or All (relation empty). | |
CurrencyCode | Defaults to the customer's or vendor's currency on an account-code Table line, then to the accounting currency. | |
UnitSymbol | Defaults to the item's sales unit (sales side) or purchase unit (purchase side). Lines for an item group or all items carry no unit: a given unit is cleared. | |
FromQuantity, ToQuantity | Not negative. The from quantity must be less than the to quantity, unless the to quantity is 0. | |
FromDate, ToDate | The to date must not be before the from date. An empty to date is open-ended. | |
Amount | The price, or the discount amount. | |
Percent1, Percent2 | Discount lines only. | |
PriceUnit, Markup, LeadTimeDays, LeadTimeCalendarDays | Price lines only. The price unit defaults to 1. | |
DisregardLeadTime, FindNext | Taken as given; No when the column is not in the file. | |
InventorySiteId, InventoryWarehouseId, ProductConfigurationId, ProductSizeId, ProductColorId, ProductStyleId, ProductVersionId | Only on lines for one item (item code Table). The site and warehouse must exist, and the combination must be valid for the item. Other lines get the blank inventory dimension. |
Validation: line number, trade agreement type missing or not a known relation, journal name, journal posted, line
number already used, postage discount, item code not
allowed for the relation, combination of relation, account code and item code not activated in the price/discount
parameters, item (Table), item group (GroupId), item relation given for All, customer (Table, sales side),
vendor (Table, purchase side), account group (GroupId), account relation given for All, currency missing,
currency, unit, to date before from date, quantity range, percentages on a price line, markup / price unit other than
1 / lead time / calendar days on a discount line, inventory dimensions on a line that is not for one item, site,
warehouse, dimensions not valid for the item. These are the rules of PriceDiscAdmTrans.validateWrite, which the
standard journal posting does not run again - the import is where they are checked. Values a relation does not use
are refused, not dropped.
Defaults: the journal name of an existing journal; the module (customer or vendor side) and price group type from the relation; currency, unit and price unit as in the table. Inventory dimensions are resolved once per distinct combination.
Fully set-based. The standard PriceDiscAdmTrans.insert() only defaults the module, the unit and the blank
dimension, which the preparation does, so the lines are written with one insert_recordset. The journals post
with the standard posting into the trade agreements the pricing engine reads, for every relation, including lines
with a site and warehouse.
Production picking list lines (set-based)
MGCLatticeSetBasedProductionPickingListLineEntity - production picking list journal lines: material consumption
against the BOM lines of started production orders. The journal name must be of type Picking list.
Journals. A production journal holds one production order. A new journal is created for every distinct
JournalNumber, JournalNameId and ProductionOrderNumber, so one grouping key with rows for two orders gives two
journals. New journals are set up as the journal forms set them up: from the production parameters, the journal
name's voucher settings and the order's site parameters. For an existing journal, the journal name and (when the row
leaves it empty) the production order are taken from the journal. The same applies to the report as finished and
route card entities below.
| Column | Required | Notes |
|---|---|---|
JournalNumber | yes | Existing open journal, or a grouping key (see Journals above). |
JournalNameId | for new journals | A picking list journal name (USMF: Pick). |
ProductionOrderNumber | yes | A started or reported-as-finished order. For an existing journal it defaults to the journal's order, and another order is refused. |
LineNumber | yes | > 0, new in the journal. |
TransactionDate | Defaults to today. | |
InventoryLotId, BOMLineNumber, ItemNumber | one of them | The BOM line of the order to consume against: by lot id, else by BOM line number, else by item. An item alone matches the order's only BOM line for the item or, when there are several, its only one with a remaining quantity; otherwise the row is refused as ambiguous. An item that is not on the order's BOM is refused (no unplanned consumption: add the BOM line to the order first). A given item must be the BOM line's item. |
ConsumptionBOMQuantity | yes | In the BOM unit; negative = a return. Converted to the inventory unit as the form converts it. |
ProposalBOMQuantity | The proposal in the BOM unit; converted to the inventory unit. | |
BOMUnitSymbol | Defaults to the BOM line's unit; must exist. | |
IsConsumptionEnded | Ends the BOM line's consumption. | |
ErrorCause | ||
InventorySiteId, InventoryWarehouseId, WarehouseLocationId, ItemBatchNumber, ItemSerialNumber, InventoryStatusId, LicensePlateNumber, ProductConfigurationId, ProductSizeId, ProductColorId, ProductStyleId, ProductVersionId | Inventory dimensions. Empty ones are the BOM line's, except that the location and the license plate are taken only when the warehouse is the BOM line's. The site must be the order's. |
Validation: several candidate BOM lines for an item (checked while the BOM line is looked up, before the other rules), line number, journal name, journal type, journal posted or blocked, production order not the journal's order, line number already used, production order missing, production order, order not started or reported as finished, order posting to a project with the consumed method, no lot id, BOM line number or item, item, no BOM line of the order for the lot id, BOM line number or item, item not the BOM line's item, zero quantity, BOM unit, site, warehouse, location (in the warehouse), batch (of the item), serial number (of the item), inventory status, license plate, site not the order's, product dimensions of a product master that are not a released variant.
Defaults: the transaction date (today); the journal name and order from an existing journal; from the BOM line,
what the picking list form takes when a lot id is chosen (ProdJournalBOM.initFromProdBOM: item, lot id, position,
operation, BOM unit, financial dimensions) and the empty inventory dimensions. When the journal name draws vouchers on
entry, the lines get one voucher per journal and date, reusing the voucher of an existing line of the same date.
The one per-line step. The standard ProdJournalBOM.insert() raises the BOM line's remaining quantity when the
consumption exceeds it and creates the line's inventory child references (the split of the BOM line's estimated issue
that the posting picks up, with its marking and reservation). The posting cannot run without them. Those are the
inventory engine's own records, so each line is written with the standard insert(); validation, the BOM line
lookup, dimensions, journals and vouchers are set-based. The journals post with the standard picking list posting,
whether a line names its BOM line by item, by BOM line number or by lot id.
Production report as finished lines (set-based)
MGCLatticeSetBasedProductionReportAsFinishedLineEntity - production report as finished journal lines: good and
error quantities of the item a started production order produces. The journal name must be of type Report as
finished. Journals are created as for picking lists (one journal per key, journal name and order; see Production
picking list lines above).
| Column | Required | Notes |
|---|---|---|
JournalNumber | yes | Existing open journal, or a grouping key (see Journals above). |
JournalNameId | for new journals | A report as finished journal name (USMF: Finish). |
ProductionOrderNumber | yes | A started or reported-as-finished order. For an existing journal it defaults to the journal's order, and another order is refused. Batch orders are refused. |
LineNumber | yes | > 0, new in the journal. |
TransactionDate | Defaults to today. | |
ItemNumber | Defaults to the order's item; when given, it must be the order's item. | |
GoodQuantity, ErrorQuantity | at least one | Negative = a reversal. |
ErrorCause | ||
IsLastReportAsFinished | Ends the job: posting reports the order as finished. | |
AcceptError | Accept error. | |
PostPickingList | Yes or No: posts the order's picking list with the report. Empty = the journal's automatic picking list setting, as the form defaults it. A text column, so No turns it off even when the journal would post it; any other value is refused. | |
InventorySiteId, InventoryWarehouseId, WarehouseLocationId, ItemBatchNumber, ItemSerialNumber, InventoryStatusId, LicensePlateNumber, ProductConfigurationId, ProductSizeId, ProductColorId, ProductStyleId, ProductVersionId | Inventory dimensions. Empty site, warehouse, inventory status and product dimensions are the order's. An empty location in the order's warehouse is the order's default output location. Batch, serial number and license plate are the row's. The site must be the order's. |
Validation: the production journal rules listed under picking list lines (from line number to the consumed-method project rule), then batch order, item not the order's item, neither a good nor an error quantity, a post picking list value other than Yes or No, site, warehouse, location (in the warehouse), batch (of the item), serial number (of the item), inventory status, license plate, site not the order's, product dimensions of a product master that are not a released variant.
Defaults: the transaction date (today); the journal name and order from an existing journal; the order's item and
lot id; the order's inventory dimensions where the row leaves them empty; the default output location
(ProdTable.defaultOutputLocation, once per order and date). Vouchers are drawn as for picking lists.
The one per-line step. The standard ProdJournalProd.insert() raises the order's remaining quantity when the
report exceeds it and creates the line's inventory child reference (the split of the order's estimated receipt that
the posting receives). The posting cannot run without it, so each line is written with the standard insert();
everything else is set-based. The journals post with the standard report as finished posting: the good
quantity is received, and a line with IsLastReportAsFinished reports the order as finished.
Production route card lines (set-based)
MGCLatticeSetBasedProductionRouteCardLineEntity - production route card and job card journal lines: hours
and quantities reported on the operations and jobs of started production orders. The journal name decides the type.
Journals are created as for picking lists (one journal per key, journal name and order; see Production picking list
lines above).
| Column | Required | Notes |
|---|---|---|
JournalNumber | yes | Existing open journal, or a grouping key (see Journals above). |
JournalNameId | for new journals | A route card or job card journal name (USMF: Route, Job). |
ProductionOrderNumber | yes | A started or reported-as-finished order. For an existing journal it defaults to the journal's order, and another order is refused. |
LineNumber | yes | > 0, new in the journal. |
TransactionDate | Defaults to today. | |
OperationNumber, OperationPriority | route card | The operation of the order's route (USMF: D0002, route 000004, operations 10, 20, 30). The priority defaults to primary. On a line with a job, the job decides both. |
JobId | job card | A job of the order. It decides the operation number, priority and job type. |
JobType | Setup or Process. Empty without a job = Process. Other job types are refused. | |
OperationId, ResourceId, WorkerPersonnelNumber | Default to the operation's (the scheduled or required resource, and its worker). A given resource is applied as the form applies it: its worker, cost categories and financial dimensions. | |
Hours, GoodQuantity, ErrorQuantity | at least one | |
ErrorCause | ||
HourCostCategoryId, HourPrice, QuantityCostCategoryId, QuantityPrice | Categories default from the operation or the given resource, prices from the category (a given category is priced from that category). | |
OperationFinished, JobFinished | A finished operation finishes the job. | |
ExecutedPercentage | Route card line on a primary operation: defaults to the share of the operation's setup or process time the hours cover, at most 100. Job card line: defaults to the job's proposal, which also finishes a job that reaches 100 percent. |
Validation: the production journal rules listed under picking list lines (from line number to the consumed-method project rule), then job card line without a job, job not on the order, operation number missing, operation (number and priority) not on the order's route, job type other than setup or process, resource, worker, hour cost category, quantity cost category, nothing reported (no hours, good or error quantity), line on the last operation of a journal that reports as finished automatically (an existing journal with the option, or a new journal when the production parameters have it) - import that report with the report as finished entity.
Defaults: the transaction date (today); the journal name and order from an existing journal; the job's operation
and job type. Then, exactly as the forms default a line when the operation (or job) is entered: the standard
ProdJobType.initProdJournalRoute runs once per distinct order, operation, priority, job, job type, resource, cost
categories and date, and gives the operation id, the resource, its worker, the merged financial dimensions and the hour
and quantity cost categories with their prices. Every column the row fills itself wins. Financial dimensions are not
a file column: they always come from this defaulting. Vouchers are drawn as for picking lists.
Fully set-based. A route card line creates no inventory or other dependent records on insert, so after the
per-combination defaulting the lines are written with one insert_recordset. Two standard side effects are then set
the same way: when the journal posts picking lists automatically, lines on a primary operation that has BOM lines get
the picking list flag; and a setup line at 100 percent marks the operation's constant as released. The route card
and job card journals post with the standard posting, and the hours land on the operations.
Demand forecast lines (set-based)
MGCLatticeSetBasedDemandForecastLineEntity - demand forecast lines (ForecastSales, the item forecasts of the
sales forecast). Each row is one line for one item. This is not a journal: a row is identified by
ForecastEntryNumber, the key of Microsoft's demand forecast entity. A row that names an existing entry updates
that line; any other value inserts a new line.
| Column | Required | Notes |
|---|---|---|
ForecastEntryNumber | yes | An existing entry updates that line. Any other value is a key local to the file: the new line is numbered from the demand forecast number sequence, and the staging row is updated to the new number. |
ForecastModelId | yes | A forecast model (not a submodel) that is not blocked (USMF: CurrentF). |
ItemNumber | yes | USMF: D0001. |
ForecastStartDate | Defaults to today. | |
ForecastEndDate | Kept only for period allocation, where it defaults to the start date. Must not be before the start date. | |
ForecastedQuantity | In the line's unit. Not negative. | |
QuantityUnitSymbol | Defaults to the item's sales unit. Must convert to the item's inventory unit. | |
ForecastedUnitPrice, ForecastedRevenue | When the price is zero, it comes from the standard price search (trade agreements, else the item's sales price). When the revenue is zero, it is quantity x price / price unit. | |
PricingCurrencyCode | Defaults to the customer's currency, else the accounting currency. | |
CustomerAccountNumber, CustomerGroupId | With a customer, the group defaults to the customer's group (USMF: US-001, group 30); another group is refused. | |
ForecastAllocationMethod, AllocationPeriods, AllocationPeriodUnit, PeriodAllocationKeyId | None, Period or Key. Period needs AllocationPeriods > 0. For any method other than Period, the periods, the period unit and the end date are cleared; for Period, the key is cleared. | |
ProductAllocationKeyId | An item allocation key. | |
DeliveringSiteId, DeliveringWarehouseId, InventoryStatusId, ProductConfigurationId, ProductColorId, ProductSizeId, ProductStyleId, ProductVersionId | Inventory dimensions; each value must exist, and the warehouse must belong to the site. The product dimensions of a product master must identify a released variant. | |
Inactive | Yes makes the line inactive. Defaults to No. | |
DefaultDimensionDisplayValue | Financial dimensions in the data entity display format. When given, it replaces the defaulted dimensions. |
Validation: forecast model (it must exist as a main model; a submodel is refused as not found), forecast model blocked, an existing entry that is a project forecast, an existing entry that an allocation created (both: not updatable with this entity), item, catch weight item, negative quantity, customer, customer group, customer group other than the customer's, currency, unit, item allocation key, period allocation key, period allocation without a number of periods, end date before start date, site, warehouse, warehouse not at the site, inventory status. After that, while the dimensions are resolved: product dimensions that are not a released variant, financial dimension values, and a unit that cannot be converted to the item's inventory unit.
What is defaulted, as the standard ForecastSales and BudgetMap methods do. Start date today. With a
customer: the customer's group, sales tax group and currency. Without a currency: the accounting currency. From the
item: the sales unit, the item group and the item sales tax group (a released variant's own sales tax group wins).
The product dimensions the item has a default for, then the site and warehouse from the item's sales order settings
once the product dimensions identify the product. The comment is the customer's name, else the product name. The
financial dimensions are the customer's, customer group's and item's, merged. The cost price is the item's cost
price at the start date. The inventory quantity is the forecast quantity converted to the inventory unit. The cash
flow flag comes from the forecast model. A line is active unless Inactive is Yes. It is reported to master planning
when it is active and has no allocation, or key allocation without a key.
Insert or update. New lines are written with one insert_recordset and updated lines with one
update_recordset. On an update, every column of the line is replaced by the row's values, defaulted as for a new
line: an empty column gets the default, not the line's old value. An updated line's cash flow forecast is marked to
be updated. Data management reports the two counts separately (new and updated). Use file keys that cannot match an
existing entry number, or the row updates that entry.
The one per-line step. The standard ForecastSales.insert() and update() create the line's dependent records
through BudgetTrans: the allocation expansion (one line per period or key share) and the inventory forecast
(ForecastInvent) that master planning reads. Those are the forecast engine's own records, so each line gets the
standard call (BudgetTrans.inserted for a new line, updating and updated for an updated line) instead of a
rebuild. Entry numbers for new lines are also drawn one line at a time. Everything else is set-based.
Not supported: project forecasts (there are no project columns), item group forecasts without an item, catch weight items. With the kitting feature on, kit forecasts are not exploded.
BOM lines (set-based)
MGCLatticeSetBasedBomLineEntity - lines of existing bills of materials (BOM). The BOM header must already
exist; create it with Microsoft's BOM header entity or the form. This is not a journal: a row is identified by
(BOMId, LineNumber). The import only inserts. A row whose line number the BOM already has is refused, not
updated, so a re-run of the same file cannot double or overwrite lines. To change lines, delete them and import
again.
| Column | Required | Notes |
|---|---|---|
BOMId | yes | An existing BOM that is not a formula (USMF: D0001BOM). |
LineNumber | yes | > 0, new in the BOM. |
ItemNumber | yes | A stocked item, not a catch weight item (USMF: M0001-M0006). |
LineType | Item (default), Phantom, Pegged supply or Vendor. Item becomes Phantom for a phantom item. Phantom and pegged supply lines cannot use a service item. | |
Quantity, QuantityDenominator | Quantity and per series; each defaults to 1 when zero. | |
ProductUnitSymbol | Defaults to the item's BOM unit, else its inventory unit. | |
ConsumptionSiteId, ConsumptionWarehouseId | The site defaults to the BOM's site and must be the BOM's site. The warehouse defaults to the item's default inventory warehouse at that site (USMF: site 1, warehouse 11 for M0001) and must belong to the site. A BOM without a site takes no warehouse: none is defaulted, and a given one is refused. | |
ProductConfigurationId, ProductColorId, ProductSizeId, ProductStyleId, ProductVersionId | Default to the item's default product dimensions. The product dimensions of a product master must identify a released variant. | |
PositionNumber | ||
ValidFromDate, ValidToDate | The to date must not be before the from date. Not allowed when the BOM parameters require quantity and dates on the BOM version. | |
VariableScrapPercentage, ConstantScrapQuantity | Default to the item's when zero. | |
RouteOperationNumber, ConsumptionCalculationMethod, ConsumptionCalculationConstant, ConsumptionType, FlushingPrinciple | Stored as given. | |
IsResourceConsumptionUsed | Yes clears the warehouse, a given one included. | |
VendorAccountNumber | Only on Vendor lines; must exist. | |
SubBOMId, SubRouteId | Must exist. |
Validation: line number, BOM, BOM is a formula, BOM approved while the BOM parameters block editing of approved BOMs, line number already in the BOM, item, catch weight item, item not stocked, phantom or pegged supply line with a service item, vendor on a line that is not a Vendor line, vendor, unit, site, warehouse on a BOM without a site, warehouse, warehouse not at the site, site other than the BOM's, to date before from date, validity dates when the BOM parameters require quantity and dates on the BOM version, sub-BOM, sub-route. After that, while the dimensions are resolved: product dimensions that are not a released variant.
What is defaulted, as BOM.initValue, BOM.initFromInventTable and the BOM line form do: quantity and per
series 1, the unit, the scrap, the phantom line type, the site, the product dimensions and the warehouse, as listed in
the table. Columns the file cannot carry are set too: height, width, depth, density and the warehouse release policy
from the item, the material overpick percentage from the item's warehouse settings, and "include in cost calculation"
(always Yes). Each line gets the next line creation sequence number of its BOM, in line number order, after the
highest one the BOM already has.
What BOM.insert() does for the BOM is done once per BOM, set-based: the BOM is marked for a new BOM check, and
BOM level recalculation is flagged when an approved, current BOM version uses the BOM. When the BOM parameters check
circularity per line, each BOM the import adds lines to is checked once. A circular BOM fails the whole transfer:
nothing is written, and the message names the BOM. This is the one rule that stops the transfer instead of refusing
rows.
Everything is set-based. The lines are written with one insert_recordset. The only per-row step is numbering the
line creation sequence on the staging rows.
Bank statement lines (set-based)
MGCLatticeSetBasedBankStatementLineEntity - lines of an advanced bank reconciliation bank statement
(BankStmtISOReportEntry). Lines are added to an existing, open statement, found by BankAccountId +
StatementId (the statement's identification). The statement header (dates, opening and closing balance) comes from
the bank statement form or Microsoft's bank statement import; this entity never creates it. This is not a journal:
the line table has no line number and no natural key, so a row is identified by (BankAccountId, StatementId,
LineNumber), and LineNumber only numbers the rows of the file. The import only inserts: every accepted row
becomes a new line.
| Column | Required | Notes |
|---|---|---|
BankAccountId, StatementId | yes | An open, unposted statement of the bank account (USMF: bank account USMF PAYRL). |
LineNumber | yes | > 0. Numbers the rows of the file; it is not stored. |
BookingDate | yes | Date and time. |
DebitAmount, CreditAmount | one of them | Exactly one, not negative, like the statement form's Debit and Credit columns. A debit increases the statement balance. |
LineStatus | Defaults to Booked. | |
ReversalIndicator | ||
EntryReference | A row whose entry reference the statement already has on a line is refused, which makes a re-import safe. Every row of an entry reference the file has more than once for the statement is refused too. | |
AccountServicerReference, ReferenceNumber, BankDocumentNumber, BankTransactionCode, Description, CreditorReference | Stored as given. BankTransactionCode is the proprietary bank transaction code; Description is the additional entry information. | |
CounterpartyName, CounterpartyId | Become the line's trading party record. | |
CounterpartyBankAccount, CounterpartyBankName | The line's related bank account and bank name. | |
CounterCurrencyCode, CounterCurrencyAmount, CounterExchangeRate | The counter currency must exist; it is matched on the ISO currency code. |
Validation: line number, statement not found, statement identification used by more than one statement of the bank account, statement not open (validated, matched or reconciled), statement posted, no booking date, negative debit amount, negative credit amount, neither amount, both amounts, entry reference already on the statement, entry reference repeated for the statement in the import, counter currency.
What is defaulted: almost nothing. The amount and its debit or credit indicator come from whichever of
DebitAmount and CreditAmount is filled in. The line status is Booked unless the file says otherwise.
Lines are added to an open statement. Lines go only to a statement that is open and not posted - the statements the bank statement form lets you add lines to. Nothing is ever updated or replaced. Rows without an entry reference cannot be recognized on a re-import and are always added.
Statement totals. After the lines are written, each statement's number of entries and its net amount balance are
recalculated from all its lines (calcTotalLines, calcNetAmount). The opening and closing balances are the bank's
figures and are not touched, so the standard statement validation still checks the lines against the bank's closing
balance (opening + net amount = closing).
The one per-row step. A row with a CounterpartyName or CounterpartyId gets its own trading party record
(BankStmtISOPartyIdentification), as the form writes it. Each line needs its own record and must carry its record
id, so these are written one at a time. Validation, statement lookup and the lines themselves (one
insert_recordset) are set-based. BankStmtISOReportEntry.insert() adds nothing in this version.
Not supported: value date / availability rows, sales tax on lines, new bank document information (mark the lines as new bank documents in the form after the import).
Fixed asset books (set-based)
MGCLatticeSetBasedAssetBookEntity - fixed asset books (AssetBook) of existing fixed assets and books. This is not
a journal: a row is identified by (FixedAssetNumber, BookId), the book's unique key. When the asset does not have
the book yet, the row inserts it. When the asset has it, the row updates it, but only while the book is not
acquired (status Not yet acquired). Creating an asset already creates the books of its group's book setup, so most
books to fill in already exist.
| Column | Required | Notes |
|---|---|---|
FixedAssetNumber | yes | An existing fixed asset. |
BookId | yes | An existing book (USMF: SLSL, FED, STATE, T_150_SLLR from group COMP's book setup; 200_SLLR outside it). |
Status | Only Not yet acquired, the default. Books are opened by posting the acquisition. | |
AcquisitionDate, AcquisitionPrice, ExpectedScrapValue | A new book's acquisition date defaults to today. | |
PlacedInService | Defaults to the acquisition date given in the row, when the book has no date yet. | |
DepreciationStartDate | When not given, it is calculated from the placed in service date and the convention (AssetBook.calcDepreciationStartDate). | |
ServiceLife | Not negative. When given, the depreciation periods are recalculated from it (AssetBook.calcLifeTime). | |
DepreciationProfileId, AlternativeDepreciationProfileId, ExtraordinaryDepreciationProfileId | Each must exist. The reducing balance / straight line (RBSL) method is refused. | |
PostingProfileId | Must exist. Required for a book that posts to the general ledger. A new book defaults to the fixed asset parameters' profile (USMF: ALL); a book that does not post has none. | |
DepreciationConvention | Needs a straight line or reducing balance profile and a service life. |
Validation: a legal entity with country-specific fixed asset book rules (Japan, India, France, Hungary, Poland, Czech Republic, Latvia, Russia, China: every row is refused), fixed asset, book, status other than Not yet acquired, book already acquired, depreciation profile, alternative depreciation profile, extraordinary depreciation profile, posting profile, no posting profile on a book that posts to the general ledger, negative service life, RBSL method, convention on a profile that is not straight line or reducing balance. After the dates and periods are calculated: a convention without a service life, a straight line or factor method without depreciation periods, and a reducing balance profile without a percentage.
What is defaulted for a new book, in the standard order. From AssetBook.initValue: acquisition and depreciation
start date today, the posting profile, and the "check maximum percentage" and "disposal in the same year" flags from
the fixed asset parameters. From the asset group's book setup (initFromAssetGroupBookSetup): the depreciation flag,
the three profiles, the service life and depreciation periods, the convention and the depreciation group; without a
book setup for the asset's group, the depreciation flag and profiles of the book itself. The net book value flags
always come from the book. From AssetBook.insert: the asset group and sorting values from the asset, actual
acquisition date = acquisition date, periods remaining = depreciation periods, the periods and service life of a
profile with a depreciation schedule, and the group's bonus depreciation for the book (AssetBookBonus). The values a
row gives override the defaults, then the derived values (placed in service date, depreciation start date,
depreciation periods) are recalculated as AssetBookV2Entity does.
Insert or update. New books are written with one insert_recordset (and one for the bonuses), updated books with
one update_recordset. On an update, a column left empty (zero, no date, no convention) keeps the book's value, so
this entity cannot clear a value. The periods remaining are reset to the depreciation periods, and the actual
acquisition date follows the acquisition date (except on a migrated asset that has one). An acquired book is changed
by transactions, and its row is refused. Data management reports new and updated books separately.
Per distinct value, and two per-row cases. The date and period calculations depend on the book's fiscal calendar
and the convention rules. They are made with the standard methods once per distinct combination of book, profile,
convention, date and service life, then written back set-based. Two steps run per book: copying the asset number into
a new book's default dimension, only in a legal entity with a financial dimension backed by the fixed asset; and
AssetBook.updateMaxPercentRB for updated books that check the maximum reducing balance percentage.