GM-X ERP for Blockchain - The WORK-EFFORT subsystem

By Tony Marston

21st March 2008
Amended 13th October 2023

Introduction

1. Work Efforts

2. Timesheets

3. Expenses

4. Events

5. Constructing Journal Entries for Timesheets

JOURNAL_ENTRY_HEADER

JOURNAL_ENTRY_ITEM

Journal Entries constructed for timesheets

Segment 1a - Revenue, Work in Progress and Direct Cost

Segment 1b - Indirect Expense, Inventory and Acquisition of Other Assets

Segment 2 - Employer Pension Expense

Determine the account_id from an account_usage value

Fixed-Fee Services

is_summary_journal Property

Amendment History

Introduction

As well as selling goods an organisation may sell services which may require the completion of some sort of work effort. Businesses may also perform work efforts within their internal organisations to accomplish tasks, such as completing a project, producing inventory for sale, or maintaining some corporate asset.

Some questions that enterprises need to answer in the course of doing business include the following:

• What type of work effort is required?
• What items need to be produced, or what services need to be delivered?
• Who will be involved, and what are their roles?
• Where will the work take place?
• What is the current status of the effort?
• How many hours has been spent on the effort, and by which party?

Work Efforts

Work efforts are used to track the work needed to fix or produce something and involve the allocation of resources. A work effort may be scheduled to take place at a specific location (facility), and may involve the assignment of several parties with particular roles. Each work effort is categorised by type and purpose.

Figure 1 - Work Effort

WORK_EFFORT_TYPE table

This identifies the work effort type.

Here are some examples:

• Activity
• Phase
• Program
• Project
• Task

WORK_EFFORT_PURPOSE table

This identifies the work effort purpose.

Here are some examples:

• Maintenance
• Production Run
• Research
• Work Flow

WORK_EFFORT table

This identifies work that needs to be done. It may be scheduled to take place at a particular location (facility), or may involve parties who are located across several facilities.

It is also possible to create a hierarchy of work efforts, such as a project which has several phases, and each phase has several tasks, et cetera. These are arranged in a senior-to-junior or parent-to-child relationship where a "child" can have only one "parent", but a "parent" can have an number of "children".

The actual_hours value is updated automatically from the input of timesheet entries.

WORK_EFFORT_STATUS_TYPE table

This identifies all the possible states through which a work effort may be progressed.

Here are some examples:

• Pending
• Approved
• In Progress
• Completed

WORK_EFFORT_STATUS_HIST table

This keeps a history of all changes in status for a work effort. The entry with the highest sequence number is the latest.

WORK_EFFORT_ORDER_ITEM table

This links a WORK_EFFORT with an ORDER_ITEM so that invoices can be generated at intervals for the outstanding hours and expenses.

In order to create multiple invoices over a period of time it is first necessary to create a sales order with the 'Repeating' status, with a separate order item for each work effort which identifies the rate to be charged for each hour's work. At the end of week after the timesheets have been filled in and approved an invoice can be created for the outstanding hours, which is calculated as the difference between actual_hours and invoiced_hours. After the invoice is produced the outstanding hours will be added to invoiced_hours. The amounts from any uninvoiced expense items will also be added to the invoice, after which their is_invoiced value will be changed from 'N' to 'Y'.

WORK_EFFORT_COMPONENT table

This contains bills of material (BOM) for Work Efforts. Each Work Effort BOM identifies all the material required to complete a particular Work Effort, and the quantities which have been staged for that purpose.

Here are some Work Effort BOM examples:

• The manufacturing BOM for a standard product that is made to order (or assembled to order) for a customer, when the Work Effort is linked to a Sales Order Item by way of the WORK_EFFORT_ORDER_ITEM table. In this case the Sales Order Item has a product_id which links to an entry on the PRODUCT table that is a Good which is the finished product sold to the customer, and possibly a revision_id if that finished product is revision-controlled. The Work Effort BOM would generally be created automatically from the Current components of an Engineering BOM found on the PRODUCT_COMPONENT table for that finished product, using the Add Work Effort Components from BOM (Create from BOM) task. The Work Effort BOM components can then be manually modified (using the Add Work Effort Component, Update Work Effort Component, and/or Delete Work Effort Component tasks) to deal with any one-off modifications needed to fulfil the Sales Order Item.
• The manufacturing BOM for a custom product that is engineered to order, and then made or assembled as engineered for a customer, when the Work Effort is linked to a Sales Order Item by way of the WORK_EFFORT_ORDER_ITEM table. In this case the Sales Order Item will have a product_id which links to an entry on the PRODUCT table that is a Good for which no no standard Engineering BOM exists, so all Work Effort BOM components must be manually maintained using the Add Work Effort Component, Update Work Effort Component, and/or Delete Work Effort Component tasks.
• The BOM for a standard service that is performed for a customer, when the Work Effort is linked to a Sales Order Item by way of the WORK_EFFORT_ORDER_ITEM table. In this case the Sales Order Item has a product_id which links to an entry on the PRODUCT table that is a Service which is standard service sold to the customer. However, because a standard Engineering BOM cannot exist for a Service, all Work Effort BOM components must be manually maintained using the Add Work Effort Component, Update Work Effort Component, and/or Delete Work Effort Component tasks.
• The manufacturing BOM for a standard product that is made for stock. In this case the Work Effort is not linked to a Sales Order Item by way of the WORK_EFFORT_ORDER_ITEM table. The Work Effort BOM would generally be created automatically from the Current components of an Engineering BOM for a user-supplied product_id that is a Good, and possibly a user-supplied revision_id, which are found on the PRODUCT_COMPONENT table using the Add Work Effort Components from BOM (Create from BOM) task.
• The BOM consisting of spare parts and consumables for a maintenance operation that is performed for an Asset. In this case the Work Effort is not linked to a Sales Order Item by way of the WORK_EFFORT_ORDER_ITEM table. The Work Effort BOM would generally be created manually using the Add Work Effort Component, Update Work Effort Component, and/or Delete Work Effort Component tasks. Alternatively, a standard Engineering BOM can be specified for a product_id (which may be a Phantom or a Good), and possibly a revision_id, that are supplied by the user when activating the Add Work Effort Components from BOM (Create from BOM) task.

Generally, when the Add Work Effort Components from BOM (Create from BOM) task is activated, only the Current entries on the PRODUCT_COMPONENT table which are directly linked to the senior (parent) product_id will be copied to the Work Effort BOM. Additional PRODUCT_COMPONENT entries for the components of the copied components, if any, will not be copied to the Work Effort BOM, unless a copied component is a Phantom. In that case, the component will not be copied to the Work Effort BOM, but its Current components on the PRODUCT_COMPONENT table will be copied instead, and that process will continue recursively if any of the phantom's components is also a Phantom. This technique is known as a "single-level BOM explosion with phantom blow-through."

When copying PRODUCT_COMPONENT entries to a Work Effort BOM, the Add Work Effort Components from BOM (Create from BOM) task will explode (gross up) the quantity_required as follows:

1. Divide the Engineering BOM quantity by the senior (parent) product's yield percentage, if any, found on the PRODUCT_BOM_TYPE table. If no yield percentage exists, Quantity (1) is the same as the Engineering BOM quantity.
2. Add 100 to the component (junior) product's scrap percentage, if any, and then multiply Quantity (1) by the result as a percentage. If no scrap percentage exists, Quantity (2) is the same as Quantity (1).
3. If the product is a component of an Engineering BOM for a Phantom, multiply Quantity (2) by the quantity of the Phantom where that Phantom is used as the junior (component) product on the PRODUCT_COMPONENT table; otherwise, Quantity (3) is the same as Quantity (2). If a Phantom product has a multi-level Engineering BOM consisting of junior (component) entries that are also Phantom products, multiplication will be done recursively at each level of the Engineering BOM.
4. If the Work Effort is linked to a Sales Order Item by way of the WORK_EFFORT_ORDER_ITEM table, multiply Quantity (3) by the quantity of the ORDER_ITEM to which the Work Effort is linked; otherwise Quantity (4) is the same as Quantity (3).
5. If Quantity (4) is a fractional quantity having more decimal digits than allowed by the scale of the UNIT_OF_MEASURE to which the component (junior) product_id is linked on the PRODUCT table, then increase Quantity (4) to the next whole decimal digit (or, when the scale is zero, to the next whole number); otherwise, Quantity (4) is the same as Quantity (3).

This technique will provide sufficient quantities of material to complete the Work Effort, taking into consideration quantities which are expected to be scrapped before consumption, or consumed but then scrapped due to expected yield losses. Upon completion of the Work Effort, any excess material which is still in useable condition can be returned from the staging location before the staged quantities are consumed. On the other hand, if actual scrap exceeds expectation or actual yield falls below expectation, and the Work Effort is fully completed as originally intended, it will be necessary to manually increase the quantity_required for the affected components, and stage additional material, before the Work Effort can be completed.

Timesheets

Timesheets are used to record the number of hours worked by a particular party on a particular work effort. Each timesheet covers a whole week for a single party, with separate entries for each day and each work effort. Timesheet details can be entered at the end of each day, but should be completed by the end of the working week which is a Friday.

Figure 2 - Timesheets

ASSIGNMENT_ROLE table

This identifies a party's role when assigned to a work effort.

Here are some examples:

• Creator
• Data Entry
• Outsourcing Responsibility
• Project Administrator
• Performer
• Project Manager
• Team Member

WORK_EFFORT_PARTY table

This identifies those parties who can be assigned to take part in work efforts. This is a subset of all those entries which exist on the PARTY table, and may be a person as well as an organisation.

WORK_EFFORT_ASSIGNMENT table

This identifies those parties who are assigned to work on particular work efforts, and in what roles. A party may be assigned to the same work effort more than once, either to cover different time periods or different roles. Each assignment may be carried out at a particular location or facility.

A party may only create timesheet entries for those work efforts for which there is a current assignment record.

TIMESHEET table

This identifies a grouping of timesheet entries for a particular party for a particular week.

Amendments can only take place while the status is 'pending'. When the details are complete for the week the status should be updated from 'pending' to 'submitted', after which it can be reviewed by a manager/supervisor before being changed to 'approved'.

TIMESHEET_ENTRY table

This identifies the number of hours worked by a particular party for a particular work effort on a particular date. Although there is a separate entry for each day the online screens will show all entries for the same week in a single group.

When a timesheet is approved all the hours spent are accumulated in the actual_hours value for the relevant WORK_EFFORT.

NOTE: The link to the WORK_EFFORT_ASSIGNMENT table requires the party_id from the parent TIMESHEET table.

TIMESHEET_ROLE_TYPE table

This identifies the various roles which parties may have in relation to timesheets.

Here are some examples:

• Approver
• Data Entry
• Manager

TIMESHEET_ROLE table

This identifies the various parties that may be associated with a particular timesheet, and their roles.

Expenses

As well as recording the number of hours worked against a work effort it may also be necessary to record any expenses which were incurred.

Figure 3 - Expenses

EXPENSE_TYPE table

This identifies the various types of expense item which may be recorded. Some types may also require the input of a quantity. For example, to claim travel expenses at so much per mile it is necessary to input the number of miles.

EXPENSE_ITEM table

This identifies a particular item of expenditure that was incurred against a particular work item by a particular party on a particular date.

An item may need to be reimbursed to the originating party, and may also need to be recharged to the client with or without a percentage markup.

Amendments can only take place while the status is 'pending'. When the details have been entered and checked the status should be updated from 'pending' to 'submitted', after which it can be reviewed by a manager/supervisor before being changed to 'approved'.

Expense amounts always have values which are expressed in the party's functional currency but may be entered in a different currency known as the transaction currency, in which case an exchange rate must also be provided. For a description of the multi-currency features please refer to FAQ04.

Events

This identifies when a particular event occurred, who completed it, and optionally how long it took.It differs from a TIMESHEET_ENTRY in that it can show how many times an event occurred in a day rather than just the total time taken for that event.

There is an optional link to the ITEM_RECEIPT table whichs allows an event, such as a process or operation which adds to inventory, to be linked with that inventory event.

Figure 4 - Events

EVENT table

This shows when an event (operation or process) took place, who performed it, and optionally how long it took.

Constructing Journal Entries for Timesheets

The wrk_timesheet(upd4)post and wrk_timesheet(batch)post tasks will create JOURNAL_ENTRY_HEADER from the TIMESHEET, and JOURNAL_ENTRY_ITEM entries from the TIMESHEET_ENTRY entries related to each selected or qualified TIMESHEET entry

A timesheet cannot be posted to the General Ledger unless the TIMESHEET entry has the following values:

• timesheet_status = 'A' (Approved)
• is_postable = 'Y'
• is_posted = 'N'

After the timesheet has been posted the is_posted column is set to 'Y' so that it cannot be posted again.

JOURNAL_ENTRY_HEADER (timesheets)

The following table shows the field mappings for constructing JOURNAL_ENTRY_HEADER entries.

1. From the value of party_id_functional on the TIMESHEET entry.
2. Automatically generated by the system when inserting a new journal entry into the database.
3. Will be set to posting_date on the TIMESHEET entry if non-blank, otherwise today's date.
4. From the je_template_id_invoice field on either of the following:
   • The AP_PARTY_RELATIONSHIP entry linked to the party_id_internal and party_id on the ORDER_HEADER entry linked to order_type and order_id on the WORK_EFFORT_ASSIGNMENT entry.
   • The AR_PARTY_RELATIONSHIP entry linked to the party_id_internal and party_id on the ORDER_HEADER entry linked to order_type and order_id on the WORK_EFFORT_ORDER_ITEM entry.

JOURNAL_ENTRY_ITEM (timesheets)

The following table shows the field mappings for constructing JOURNAL_ENTRY_ITEM entries.

5. From the value of party_id on the TIMESHEET entry.
6. When constructing a Segment 1a for the 'sales_service' account usage, this value comes from the value of po_number on the ORDER_HEADER entry linked to the WORK_EFFORT_ORDER_ITEM entry; otherwise this entry is blank.
7. These values accumulate for summary journal entries (refer to is_summary_journal Property, below).
8. These come from one of the following:
   • For Segment 1a journal entry items, these values come from the values of order_type, order_id, and order_item_seq_no on the WORK_EFFORT_ORDER_ITEM entry linked to work_effort_id on TIMESHEET_ENTRY.
   • For Segment 1b journal entry items, these values come from the values of order_type, order_id, and order_item_seq_no (if any) on the WORK_EFFORT_ASSIGNMENT entry linked to party_id on the TIMESHEET.
9. These values come from the values of schedule_id and schedule_seq_no (if any) on the WORK_EFFORT entry linked to work_effort_id on TIMESHEET_ENTRY.
10. These values come from the value of event_id on the EVENT entry linked to the TIMESHEET_ENTRY by way of the timesheet_id, work_effort_id and day_no values.
11. This value comes from the value of asset_id (if any) on the WORK_EFFORT entry linked to work_effort_id on TIMESHEET_ENTRY.
12. This value comes from the value of asset_seq_no on the ASSET_BOOK_EVENT entry that is created when the TIMESHEET_ENTRY concerns the construction or servicing of an asset.

Journal Entries constructed for timesheets

Journal entries constructed from timesheet entries can have as many as 2 segments. Segment 1 is required, and Segment 2 is optional depending upon the circumstances described below.

The amounts will be recorded in the JOURNAL_ENTRY_ITEM table after converting the account usage into a chart_id and account_id using the procedure described in Determine the account_id from an account_usage value. Note that the detailed entries may need to be consolidated into summary entries as described in is_summary_journal Property.

Segment 1a - Revenue, Work in Progress and Direct Cost

A separate Segment 1a is constructed for each qualifying timesheet item. A timesheet item is a row on the TIMESHEET_ENTRY table which is linked to the current TIMESHEET. An entry qualifies if it meets the following conditions:

• The work_effort_id on the TIMESHEET_ENTRY table links to a record on the WORK_EFFORT_ORDER_ITEM table which in turn links to an entry on the ORDER_ITEM table with an order_type of 'S' (sales order).
• The product_id on ORDER_ITEM must be non-blank, and the product_subtype on the PRODUCT table must be 'S' (Service).
• The price_component_id on ORDER_ITEM links to an entry on the PRICE_COMPONENT table which must have a price_type of 'B' (Base price).

Note that all entries on the TIMESHEET_ENTRY table will have a non-zero value for hours. Entries with a zero value will not be written to the database.

If an entry on the ORDER_ITEM table exists then a lookup must be performed on the AR_PARTY_RELATIONSHIP table using the following values:

• party_id_1 = party_id_functional on the TIMESHEET table.
• party_id_2 = party_id on the ORDER_HEADER table.
• rel_status_id = 'A' (Active).
• start_date = Less than or equal to entry_date on the TIMESHEET_ENTRY table.
• end_date = Greater than or equal to entry_date on the TIMESHEET_ENTRY table.

If the AR_PARTY_RELATIONSHIP record contains a value for je_template_id_invoice then this must be used to retrieve data from the SUBSYSTEM_JE_TEMPLATE_HEADER and SUBSYSTEM_JE_TEMPLATE_ITEM tables.

1. Monetary amounts for the 'direct_labour' and 'accrued_expense' accounts are based upon standard cost per hour and are calculated as follows:
   1. Obtain a value for cost_per_unit using the procedure identified in Lookup Product Cost.
   2. If the uom_id for the product_id on the ORDER_ITEM is *NOT* 'HOUR' or 'EACH' then convert the hours value on the TIMESHEET_ENTRY record using an entry on the UOM_CONVERSION table, where uom_id_to is the uom_id for the product_id on the ORDER_ITEM, and uom_id_from is 'HOUR'. If no conversion_factor exists then display the error message "Cannot find factor to convert from 'xxx' to 'yyy'".
   3. Calculate the monetary amount for 'direct_labour' and 'accrued_expense' as: hours x cost_per_unit (after converting hours, if necessary, at Step 1b above) and then round the result to the scale of the functional currency from the CURRENCY_CODE table.
   4. If this amount calculated at Step 1c is greater than zero then do the following:
      • Create a debit (DR) entry for the 'direct_labour' account.
      • Create a credit (CR) entry for the 'accrued_expense' account.
      • Create a statistical quantity entry for the 'direct_labour' account using hours on the TIMESHEET_ENTRY (before converting hours at Step 1b above).
2. Monetary amounts for the 'sales_service' and 'wip' accounts are based upon the adjusted item price per hour and are calculated as follows:
   1. If the quantity on the ORDER_ITEM is 1 and the uom_id for the product_id on the ORDER_ITEM is 'EACH' then:
      1. Calculate the amount to be posted using the procedure described in Fixed-Fee Services.
      2. Skip to Step 2c below.
   2. ELSE
      1. If the uom_id for the product_id on the ORDER_ITEM is *NOT* 'HOUR' then perform the conversion as per Step 1b above.
      2. Calculate the monetary amount for 'sales_service' and 'wip' as: hours x adjusted_price (after converting hours, if necessary), round the result to the scale of the functional currency from the CURRENCY_CODE table.
      3. Skip to Step 2c below.
   3. If this amount calculated at Step 2a or 2b is greater than zero then do the following:
      • Create a debit (DR) entry for the 'wip' account.
      • Create a credit (CR) entry for the 'sales_service' account.
      • Create a statistical quantity entry for the 'sales_service' account using hours on the TIMESHEET_ENTRY (before converting hours at Step 1b above).

Segment 1b - Indirect Expense, Inventory and Acquisition of Other Assets

A separate Segment 1b is constructed for each qualifying timesheet item. A timesheet item is a row on the TIMESHEET_ENTRY table which is linked to the current TIMESHEET. An entry qualifies if it meets the following conditions:

• The work_effort_id on the TIMESHEET_ENTRY table does *NOT* link to an entry on the WORK_EFFORT_ORDER_ITEM table.
• The work_effort_id on the TIMESHEET_ENTRY table links to an entry on the WORK_EFFORT_ASSIGNMENT table which may contain a link to an entry on the ORDER_ITEM table.
• The work_effort_purpose_id on the WORK_EFFORT table links to an entry on the FUNC_UNIT_WRK_EFF_PURPOSE table.

If an entry on the ORDER_ITEM table exists then a lookup must be performed on the AP_PARTY_RELATIONSHIP table using the following values:

• party_id_1 = party_id_functional on the TIMESHEET table.
• party_id_2 = party_id on the ORDER_HEADER table.
• rel_status_id = 'A' (Active).
• start_date = Less than or equal to entry_date on the TIMESHEET_ENTRY table.
• end_date = Greater than or equal to entry_date on the TIMESHEET_ENTRY table.

If the AP_PARTY_RELATIONSHIP record contains a value for je_template_id_invoice then this must be used to retrieve data from the SUBSYSTEM_JE_TEMPLATE_HEADER and SUBSYSTEM_JE_TEMPLATE_ITEM tables.

Monetary amounts for the 'auc', 'inventory' or 'staff_indirect_other' and 'accrued_expense' accounts are based upon estimated cost per hour and are calculated as follows:

1. If the value of party_type is 'Contractor' on the WORK_EFFORT_PARTY entry for the party_id on the TIMESHEET table, then:
   1. If the order_type, order_id and order_item_seq_no on the WORK_EFFORT_ASSIGNMENT entry are blank then skip to Step 2 below.
   2. If the quantity on the ORDER_ITEM is 1 and the uom_id for the product_id on the ORDER_ITEM is 'EACH' then:
      1. Calculate the amount to be posted using the procedure described in Fixed-Fee Services.
      2. Skip to step 4 below.
   3. ELSE:
      1. If the uom_id for the product_id on the ORDER_ITEM is *NOT* 'HOUR' then convert the hours value on the TIMESHEET_ENTRY record using an entry on the UOM_CONVERSION table, where uom_id_to is the uom_id for the product_id on the ORDER_ITEM, and uom_id_from is 'HOUR'. If no conversion_factor exists then display the error message "Cannot find factor to convert from 'xxx' to 'yyy'".
      2. Calculate the monetary amount for 'auc', 'inventory' or 'staff_indirect_other' and 'accrued_expense' as: hours x adjusted_price (after converting hours, if necessary), and then round the result to the scale of the functional currency from the CURRENCY_CODE table.
      3. Skip to Step 4, below.
2. ELSE If cost_per_hour is not blank on the WORK_EFFORT_PARTY entry for the party_id on the TIMESHEET table, then:
   1. Use the cost_per_hour value on the WORK_EFFORT_PARTY entry for the party_id on the TIMESHEET table. This value is denominated in the currency_code on that WORK_EFFORT_PARTY entry.
   2. If the currency_code differs from the functional currency then convert cost_per_hour into functional currency using an EXCHANGE_RATE table lookup FROM the currency_code TO the functional currency as at the entry_date.
   3. Calculate the monetary amount for 'auc', 'inventory' or 'staff_indirect_other' and 'accrued_expense' as: hours x cost_per_hour (after converting cost_per_hour to functional currency, if necessary) and then round the result to the scale of the functional currency from the CURRENCY_CODE table.
   4. Skip to Step 4, below.
3. ELSE If cost_per_hour is blank on the WORK_EFFORT_PARTY entry for the party_id on the TIMESHEET table, then output an error message saying "No cost is available for Timesheet xxx, Work Effort yyy".
4. If this amount calculated at Step 1b, 1c or 2 above is greater than zero then do the following:
   • Create a debit (DR) entry for one of the 'auc', 'inventory' or 'staff_indirect_other' accounts based on the contents of accrual_type on the FUNC_UNIT_WRK_EFF_PURPOSE entry.
   • Create a credit (CR) entry for the 'accrued_expense' account.

Segment 2 - Employer Pension Expense

This segment accounts for any employer-paid pension expenses incurred and accrued when parties perform work efforts.

To convert the 'pension_expense' and 'pension_payable' account usages into their respective account_id values, search records in the following sequence:

• The SUBSYSTEM_JE_TEMPLATE_ITEM table if:
  • A Segment 1a was constructed, and a value for je_template_id_invoice exists on the AR_PARTY_RELATIONSHIP entry.
  • A Segment 1b was constructed, and a value for je_template_id_invoice exists on the AP_PARTY_RELATIONSHIP entry.
• The FUNCTIONAL_UNIT table.

Records are only written to the JOURNAL_ENTRY_ITEM table for each TIMESHEET_ENTRY if the following conditions are met:

• The pension_percent field on the FUNCTIONAL_UNIT entry for the party_id_internal on the WORK_EFFORT_ASSIGNMENT is not blank and greater than zero.
• The party_type field on the WORK_EFFORT_PARTY entry for the party_id on the TIMESHEET is 'Employee'.

The monetary amount posted as a debit (DR) entry to the 'pension_expense' and a credit (CR) entry to the 'pension_payable' accounts are the same values that were posted to one of the following:

• The 'direct_labour' account in Segment 1a.
• The 'auc', 'inventory', or 'staff_indirect_other' account in Segment 1b.

Each of these amounts must be multiplied by the value of the pension_percent field.

Lookup Product Cost

This uses values for VALUATION_TYPE, COST_ELEMENT_ID and PRODUCT_BOM_TYPE which are defined in Accounts Payable Control Data.

Lookup on the PRODUCT_COST table using the following:

• Product_id from the ORDER_ITEM record
• Valuation_type from the Product Control Data
• Cost_element_id from the Product Control Data
• Bom_type_id from the Product Control Data
• Revision_id is either NULL if the product is not revision controlled, OR equal to the entry on the PRODUCT_REVISION table whose effective_date and discontinue_date include the posting_date. If there are multiple entries then take the one with the latest value for effective_date.

If the currency_code_costing differs from the functional currency then convert cost_per_unit into functional currency using an EXCHANGE_RATE table lookup FROM the currency_code_costing TO the functional currency as at the entry_date.

Determine the account_id from an account_usage value

This procedure is used to convert an account_usage into an account_id. This value may be provided on any one of a number of records which must be searched in the following sequence:

1. SUBSYSTEM_JE_TEMPLATE_ITEM using the je_template_id_invoice from one of the following (if it exists):
   • The AP_PARTY_RELATIONSHIP entry linked to the party_id_internal and party_id on the ORDER_HEADER entry linked to order_type and order_id on the WORK_EFFORT_ASSIGNMENT entry.
   • The AR_PARTY_RELATIONSHIP entry linked to the party_id_internal and party_id on the ORDER_HEADER entry linked to order_type and order_id on the WORK_EFFORT_ORDER_ITEM entry.
2. FUNC_UNIT_PRICE_COMPONENT (if a value for price_component_id exists)
3. FUNC_UNIT_PROD_CAT (if a value for prod_cat_id exists - note that an ORDER_ITEM may provide more than one value, in which case only the first account_id will be used)
4. FUNCTIONAL_UNIT table.

Note that some of these tables can only be read if the input record from the ORDER subsystem contains the primary key for that table.

As soon as the first non-blank value for account_id is found then the account determination process will be terminated. If at the end there is no value for account_id then thean error message "Cannot determine account_id for usage 'xxx'" will be produced.

Please note the following exceptions for Segment 1a:

• For the 'wip' and 'direct_labour' account usages search only the FUNC_UNIT_PROD_CAT and FUNCTIONAL_UNIT tables
• For the 'accrued_expense' account usage search only the FUNCTIONAL_UNIT table.

Please note the following exceptions for Segment 1b:

• For the 'auc', 'inventory' and 'staff_indirect_other' account usages search only the FUNC_UNIT_WRK_EFF_PURPOSE and FUNCTIONAL_UNIT tables
• For the 'accrued_expense' account usage search only the FUNCTIONAL_UNIT table.

Please note the following exceptions for Segment 2:

• For the 'pension_expense' and 'pension_payable' account usages search only the SUBSYSTEM_JE_TEMPLATE_ITEM and FUNCTIONAL_UNIT tables

Fixed-Fee Services

This procedure is used for services provided on a fixed-fee basis which is signified when the quantity on the ORDER_ITEM entry is 1 and the uom_id is 'EACH'. It is used to reduce the amount which will be posted to the Ledger so that is does not exceed the adjusted_price on the ORDER_ITEM entry.

1. Calculate the accrued revenue and hours by accumulating the monetary amounts and hours previously posted to the 'sales_service' account for this WORK_EFFORT entry.
2. Calculate the remaining amount by subtracting accrued revenue from the adjusted_price on the ORDER_ITEM entry.
3. Calculate the estimated hourly rate as: adjusted_price / estimated_hours on the WORK_EFFORT entry.
4. Calculate the monetary amount as the estimated hourly rate (from step 3 above) x hours (on the TIMESHEET_ENTRY record), and then round the result to the scale of the functional currency from the CURRENCY_CODE table.
5. The monetary amount is the amount calculated at Step 4, above, or the remaining amount calculated at Step 2, above, whichever is less.
6. Calculate the remaining hours by subtracting accumulated hours from estimated_hours on the WORK_EFFORT entry.
7. The statistical quantity is hours on the TIMESHEET_ENTRY (before converting hours at Step 1, above) or the remaining hours (from step 6, above), whichever is less.

is_summary_journal Property

By default the value of is_summary_journal is 'NO'. When wrk_timesheet(upd4)post is run it will remain at 'NO'. When wrk_timesheet(batch)post is runit may be set to 'YES' if the conditions identified in point #1 of Determine the account_id from an account_usage value identify a record on the SUBSYSTEM_JE_TEMPLATE_HEADER table with a different value for is_summary_journal.

Following the processing described for Segment 1a, Segment 1b and Segment 2 all records destined for the JOURNAL_ENTRY_ITEM table will exist in memory. If the value for is_summary_journal has been changed to 'YES' then these records will need to be consolidated so that only one record is produced for each account_id. This will invovle accumulating the values for debit_functional, credit_functional and statistic. Thus instead of having separate sets of records for each day these will be consolidated into a single set of records for the entire week.

Date created: 3rd September 2007

Amendment history:

Copyright © 1999-2023 by Geoprise Technologies Licensing, All Rights Reserved.