Return 

How to retrieve Cost Data from Project Database


           Retrieving Project Cost Data using User-Defined Retrieval Codes

This article explains how to create User-Defined Retrieval Codes and how to apply them to get the current project cost information.

           Under the term "using User-Defined Retrieval Codes", we shall understand the ability of the Cost Engineer to assign his own code words to some or all major Project Objects (CONSTRUCTION, MATERIALS, ENGINEERING) and Project Participants  (CONTRACTORS, SUPPLIERS, ENGINEERING FIRMS), and this in order to find - at any time during the project execution - the cost information related to his defined code words. As already mentioned in our Article #17, there are two types of data retrieval in TECA:

  • AUTOMATIC RETRIEVAL
  • USER-DEFINED RETRIEVAL

         Our previous Article #17 covered the subject of AUTOMATIC RETRIEVAL, and this article covers the subject of the USER-DEFINED RETRIEVAL. Implemented in TECA, the SQL-language permits to retrieve the cost data instantly from the relational project database, inclusive of the User-Defined Retrieval Codes, making the User unaware about any need to create statements in this computer language. As you may know, so far TECA is the only dual-entry project accounting computer program that is implementing also the relational database to handle project cost data.  Click here to read how to acquire the TECA computer program.

            As already explained in many of our articles, project cost planning and control requires implementation of the dual-entry accounting, similar but not the same as that used by Business.  Project Cost Engineering is concerned with BUDGET and TiME-COST, but not with PROFIT or LOSS that are the main concerns of Business.  Although many accounting features are similar - such as the format of journals and ledgers, yet the basic equations governing the accounting concept of Business Finance and that of Engineering Cost Control are both completely different; click here. Moreover, D-E Cost Engineering implements COST-NETWORK (click here) that has no counterpart in Business Accounting.           


USER-DEFINED RETRIEVAL

The User-Defined Retrieval Codes are applicable to Project Objects (Cost Accounts: CONSTRUCTION, MATERIALS, ENGINEERING) and to Project Participants (Expense Accounts: CONTRACTORS, SUPPLIERS, ENGINEERING FIRMS). Retrieval code words supply additional attributes that may be utilized during the project execution to identify some accounts, requiring special attention of the Project Management.

 In the TECA computer program, there are four (4) groups of codes, allowing the User to define them any way he may need. Each group may comprise any number of Codes. Each Code comprises four (4) Code Words, where each Code Word consists of two (2) alphanumeric characters. The empty Code Word comprises two commas (,,). Hence, each Code comprises eight (8) alphanumeric characters, and the empty Code comprises eight commas (,,,,,,,,,,,,).

The Standard Retrieval Code, generated by TECA, identifies four (4) code groups, and it implements numeric code only. However, the User may edit - i.e. overtype, delete, or add any code as needed, using any alphanumeric two characters for any Code Word. The four (4) code groups of the Standard Retrieval Code are as follows:

  1. Group "WHAT" - referring to all Cost Accounts (CONSTRUCTION, MATERIALS, ENGINEERING)
  2. Group "WHEN" - referring to all Project Periods
  3. Group "WHO" - referring to all Expense Accounts (CONTRACTORS, SUPPLIERS, ENGINEERING FIRMS)
  4. Group "HOW" - referring to importance of some identified Accounts

To explain this retrieval code, let us assume that the following retrieval code applies to our Project:

  1. Group "WHAT":
    • O1 - Barge Leasing (w/Revolving Crane)
    • 02  - Cleanup Operations
    • 03  - Diving Investigations and Assistance
    • ......
    • 39  - Zinc Bracelet Anodes
  2. Group "WHEN":
    • O1 - 11/01/1991 to 11/30/1991
    • 02 -  12/01/1991 to 12/31/1991
    • 03 -  01/01/1992 to 01/31/1992
    • ......
    • 34  08/01/1994 - 08/31/1994
  3. Group "WHO":
    • O1 - Bergamo Engineers, S.A., Italy
    • 02  - Diving Services, Southampton, UK
    • 03  - Globber Engineering, Saudi Arabia
    • ......
    • 24  Veraldo S.A., Milan, Italy
  4. Group "HOW":
    • 01  - Standard
    • 02  - Special
    • 03  - Low Security
    • 04  - High Security
    • 05  - Low Risk
    • 06  - High Risk
    • 07  - In Kingdom
    • 08  - Out of Kingdom

        For example, the listed below retrieval codes shall be understood as follows:

            02,,0307  -  Cleanup Operations; Globber Engineering, Saudi Arabia; In Kingdom
            ,,0302,,    -  From 01/01/1992 to 01/31/1992; Diving Services, Southampton, UK
            ,,,,0106    -  Bergamo Engineers, S.A., Italy; High Risk

Creating the Retrieval Codes with TECA is explained by the Case #1 below. The Case #2 explains how to retrieve project cost data using the Retrieval Codes. The Case #3 explains how to retrieve project cost data referring to some time period defined by two dates, where each date remains within the Project Time-Span (i.e. planned project duration). Finally, the Case #4 explains how to append any Retrieval Code Word to any Cost Account or Expense Account.


CASE #1 - Creating the generic Retrieval Code

This example shows how to create Standard Retrieval Code for the project SOFP - Safanya Offshore Facilities. There are five (5) screens that illustrate each step of the procedure described below. You may scroll down the first screen to review all related screens.

PROCEDURE:

  • Start on the screen named Retrieval Codes (click here - Screen #1). This screen comprises four sub-screens, each representing one code group. To enter code words into one of the sub-screens, click there the button EDIT to put the sub-screen into the edit mode, and then click the button ADD to create one record line. On this screen, the User had already started to enter some code words this way. By clicking the button DEL, any affected record line can be deleted.

  • To create the Standard Retrieval Code, click the button STANDARD RETRIEVAL CODE  and confirm the prompt; (click here - Screen #2). In response, TECA generates instantly all Codes, utilizing existing cost and expense accounts and project periods as the code word expressions; (click here - Screen #3).

  • Any code word can be edited, by revising, adding, or deleting any record line; (click here - Screen #4). Although TECA supplies numeric code words (from 01 to 99), any alphanumeric two characters can be used also to create one code word.

  • Finally, to create report comprising listing of all code words, click the button REPORT and confirm the prompt. In response, TECA creates and displays the report; (click here - Screen #5).

  • To erase completely all existing Retrieval Codes, click the button ERASE CODES and  confirm the prompt.

  • DONE!


CASE #2 - Retrieving project cost data by using the Retrieval Codes

This example shows how to retrieve posted ledger cost data of the project SOFP - Safanya Offshore Facilities, by using some Retrieval Codes. There are six (6) screens that illustrate each step of the procedure described below. You may scroll down the first screen to review all related screens.

PROCEDURE:

  • Start on the screen named Retrieval by Retrieval Codes (click here - Screen #1). The upper part of this screen contains four combos with the choice of code words.

  • It is important to refresh all ledger records. Hence, click the button UPDATE and confirm the prompt with YES. In response, TECA verifies all ledger data and instantly appends existing Retrieval Code Words where applicable. If you would respond with NO, then TECA would erase all references to Retrieval Code Words in the ledger data of the project database.   

  • Select some code words (in our case ,,09,,02,,), and click the button GET RECORDS and confirm the prompt; (click here - Screen #2).

  •  In response, TECA retrieves all related records from the project database and displays them in the sub-screen; (click here - Screen #3).

  • To create report comprising the data displayed on the sub-screen, click the button REPORT and confirm the prompt. In response, TECA creates and displays the report; (click here - Screen #4).

  • You may want to select some records by checking the check-boxes in the first column, and then click the button SHOW SELECTED to display your checked records only; (click here - Screen #5). To return to the previous display, click the button SHOW SET.

  • To create report comprising only the selected data then displayed on the sub-screen, click the button REPORT and confirm the prompt. In response, TECA creates and displays the report; (click here - Screen #6).

  • To clear the sub-screen, click the button CLEAR SCREEN and  confirm the prompt.

  • To clear the applied code words, click the button CLEAR CODES and  confirm the prompt.

  • To display all posted ledger records of this project database, click SHOW ALL RECORDS and  confirm the prompt.

  • DONE!


CASE #3 - Retrieving project cost data by using two Calendar Dates

This example shows how to retrieve posted ledger cost data of the project SOFP - Safanya Offshore Facilities, by using two Calendar Dates. There are six (6) screens that illustrate each step of the procedure described below. You may scroll down the first screen to review all related screens.

PROCEDURE:

  • Start on the screen named Retrieval by Calendar Dates (click here - Screen #1). The upper part of this screen contains two combos representing Start and Finish of one Project Period, and also two data fields representing calendar dates of start and finish of some time period.

  • Enter two dates, or get them by clicking the combos, (in our case: from 12/01/1991 to 02/29/1992), and click the button GET RECORDS and confirm the prompt; (click here - Screen #2).

  •  In response, TECA retrieves all related records from the project database and displays them in the sub-screen; (click here - Screen #3).

  • To create report comprising the data displayed on the sub-screen, click the button REPORT and confirm the prompt. In response, TECA creates and displays the report; (click here - Screen #4).

  • You may want to select some records by checking the check-boxes in the first column, and then click the button SHOW SELECTED to display only the checked records; (click here - Screen #5). To return to the previous display, click the button SHOW SET.

  • To create report comprising listing of the selected data displayed on the sub-screen, click the button REPORT and confirm the prompt. In response, TECA creates and displays the report; (click here - Screen #6).

  • To clear the sub-screen, click the button CLEAR SCREEN and  confirm the prompt.

  • To clear the applied calendar dates and project periods, click the button CLEAR CODES and  confirm the prompt.

  • To display all posted ledger records of this project database, click SHOW ALL RECORDS and  confirm the prompt.

  • DONE!


CASE #4 - Appending individual Retrieval Code Words to Project Accounts (Cost and Expense)

This example shows how to append Retrieval Code Words to the Cost Account C014.c Hydrostatic other testing, and also to the Expense Account OCEANT.E Ocean Towing Corporation, Singapore. There are six (6) screens that illustrate each step of the procedure described below. You may scroll down the first screen to review all related screens.

PROCEDURE:

Let us start with the Cost Account:

  • Start on the screen named Project Account Code  - Listing (click here - Screen #1). Ensure that the radio button shows Cost.

  • Click the button ACC adjacent to the Account C014.c Hydrostatic other testing. In response TECA display this Cost Account screen  (click here - Screen #2).

  • In the combo HOW, select 06 High Risk.

  • Click on the button POST RETRIEVAL CODE and confirm the prompt; (click here - Screen #3).

  • In response, TECA confirms with the message OK.

  • DONE!

Now let us do the same with the Expense Account:

  • Start on the screen named Project Account Code  - Listing (click here - Screen #4). Click on the radio button Expense. In response, TECA displays only Expense Accounts.

  • Click the button ACC adjacent to the Account OCEANT.E Ocean Towing Corporation, Singapore. In response TECA display this Expense Account screen  (click here - Screen #5).

  • In the combo HOW, select 06 High Risk.

  • Click on the button POST RETRIEVAL CODE and confirm the prompt. In response, TECA confirm OK.

  • DONE!

Now let us verify existence of this new retrieval code in the Project Database:

  • When still on the account screen, click the button RETRIEVALS.

  • In response, TECA display the screen RETRIEVAL by Retrieval Codes.

  • Select the code 06 in the HOW combo to set the retrieval code as ",,,,,,06"; (click here - Screen #6).

  • Click the button GET RECORDS and confirm the prompt.

  • In response, TECA display all cost records with the code word "High Risk".

  • DONE!


Up Page