Modifying accounting functionality
So you implemented the option to specify custom material characteristics and then created some characteristics (material options).
But this is only a part of the job. We would also want to perform material accounting based on these characteristics. The goals we want to achieve are:
- Record product receipts with characteristics
- Record product expenses with characteristics
- Generate reports for products with specific characteristics instead of products in general
To do so, you need to adjust the existing registers and create a new report that displays data based on material properties.
The BalanceOfMaterials register
The GoodsReceipt document
The Services document
In Designer mode
In order to perform material accounting by characteristic values, you need to edit the structure of the BalanceOfMaterials accumulation register so that it could store data broken down by material property set.
- In Designer, open the editor of the BalanceOfMaterials accumulation register configuration object.
- On the Data tab, add the PropertySet dimension that has CatalogRef.MaterialOptions type (fig. 15.39).
Fig. 15.39. New PropertySet dimension
In Designer mode
Then you need to adjust the GoodsReceipt document. You have to add the option to specify a property set in a document and have this property set recorded when the document is posted.
- In Designer, open the editor of the GoodsReceipt document configuration object.
- On the Data tab, add the PropertySet attribute with the CatalogRef.MaterialOptions type to the document tabular section (fig. 15.40).
Fig. 15.40. New PropertySet attribute
You need to fill the Choice parameters links property for this attribute to limit the selection of property sets by those available for a specific material.
- In the property palette, in the Choice parameters links field, click the Select button.
- Drag the Materials.Material attribute from the list of available attributes to the list of parameters (fig. 15.41) and click OK.
Fig. 15.41. Choice parameters links
So you have defined that when a user attempts to select a value for the PropertySet field, the list of Material options catalog items available for selection only includes the items subordinate to the material selected in the Material column.
Let us add this attribute to the tabular section of the document form.
- On the Forms tab, in the list of forms, double-click DocumentForm.
- In the upper right pane of the form editor, on the Attributes tab, expand the Object form attribute.
You can see that it includes all the attributes of the GoodsReceipt document.
- Expand the Materials tabular section and drag the PropertySet attribute to the form elements pane (the upper left pane), to the position after the Material field (fig. 15.42).
Fig. 15.42. Modification of GoodsReceipt document form
Note that in the property palette of the PropertySet form control you can see that the PropertySet tabular section attribute is selected as the DataPath property value. The platform filled this property automatically when you dragged the attribute to the form.
The DataPath property matches a form control to a form attribute (the data displayed in the form). This property must be filled because otherwise the form control is not displayed.
Note. When you add a form control using the Add button, you have to fill its DataPath property manually in order to link the control to a form attribute.
- In the editor of the GoodsReceipt document configuration object, click the Other tab and open the object module.
- Open the Posting event handler procedure and, in the section that generates register records, assign a value to the PropertySet dimension of the BalanceOfMaterials register as shown in listing 15.3.
Listing 15.3. Posting() procedure (fragment)
... // register BalanceOfMaterials Receipt ... Record.Material = CurRowMaterials.Material; Record.PropertySet = CurRowMaterials.PropertySet; Record.Warehouse = Warehouse; ...
In Designer mode
Let us adjust the Services document in a similar manner, giving users the option to specify a property set for each material.
- In Designer, open the editor of the Services document configuration object.
- On the Data tab, add the PropertySet attribute with the CatalogRef.MaterialOptions type to the document tabular section.
- Fill the Choice parameters links property for this attribute by adding the MaterialsAndServices.MaterialOrService attribute from the list of available attributes to the list of parameters.
By doing this, you specify that the list of MaterialOptions catalog items available for selection in the PropertySet field contains only items subordinate to the material selected in the MaterialOrService column.
- Drag this attribute from the form attributes pane to the form controls pane, to the tabular section, after the MaterialOrService item.
- In the editor of the Services document configuration object, click the Other tab and open the object module.
- Open the Posting event handler procedure and, in the section that generates register records, assign a value to the PropertySet dimension of the BalanceOfMaterials register as shown in listing 15.4.
Listing 15.4. Posting() procedure (fragment)
... // register BalanceOfMaterials Expense ... Record.Material = SelectionDetailRecords.MaterialOrService; Record.PropertySet = SelectionDetailRecords.PropertySet; Record.Warehouse = Warehouse; ...
- Since during the previous lesson you adjusted the document posting procedure to get all the document data using a query, update the query to get the new document attribute (listing 15.5).
Listing 15.5. Posting() procedure (fragment)
... Query = New Query; // Specifying the temporary tables manager used by the query Query.TempTablesManager = TTManager; Query.Text = "SELECT | ServicesMaterialsAndServices.MaterialOrService, | ServicesMaterialsAndServices.MaterialOrService.MaterialServiceType AS MaterialServiceType, | ServicesMaterialsAndServices.PropertySet, | SUM(ServicesMaterialsAndServices.Quantity) AS QuantityInDocument, | SUM(ServicesMaterialsAndServices.Total) AS TotalInDocument |INTO DocumentMaterialsAndServices |FROM | Document.Services.MaterialsAndServices AS ServicesMaterialsAndServices |WHERE | ServicesMaterialsAndServices.Ref = &Ref |GROUP BY | ServicesMaterialsAndServices.MaterialOrService, | ServicesMaterialsAndServices.MaterialOrService.MaterialServiceType, | ServicesMaterialsAndServices.PropertySet"; ... Query2 = New Query; Query2.TempTablesManager = TTManager; Query2.Text = "SELECT | DocumentMaterialsAndServices.MaterialOrService, | DocumentMaterialsAndServices.MaterialServiceType, | DocumentMaterialsAndServices.PropertySet, | DocumentMaterialsAndServices.QuantityInDocument, | DocumentMaterialsAndServices.TotalInDocument, | ISNULL(CostOfMaterialsBalance.CostBalance, 0) AS Cost, | ISNULL(BalanceOfMaterialsBalance.QuantityBalance, 0) AS Quantity |FROM | DocumentMaterialsAndServices AS DocumentMaterialsAndServices ...You also have to adjust the last query that performs the check for negative balances during real-time posting. Instead of getting general balances for materials specified in the document tabular section, it should get the balances for materials with the property sets specified in the document rows (listing 15.6).
Listing 15.6. Check for negative balances during real-time posting
... Query3.Text = "SELECT | BalanceOfMaterialsBalance.Material, | BalanceOfMaterialsBalance.PropertySet, | BalanceOfMaterialsBalance.QuantityBalance |FROM | AccumulationRegister.BalanceOfMaterials.Balance( , (Material, PropertySet) IN ( | SELECT | DocumentMaterialsAndServices.MaterialOrService, | DocumentMaterialsAndServices.PropertySet | FROM | DocumentMaterialsAndServices) | AND Warehouse = &Warehouse) AS BalanceOfMaterialsBalance |WHERE | BalanceOfMaterialsBalance.QuantityBalance < 0"; Query3.SetParameter("Warehouse", Warehouse); QueryResult = Query3.Execute(); SelectionDetailRecords = QueryResult.Select(); While SelectionDetailRecords.Next() Do Message = New UserMessage(); Message.Text = String(- SelectionDetailRecords.QuantityBalance) + " units shortage for """ + SelectionDetailRecords.Material + """ with """ + SelectionDetailRecords.PropertySet + """ property set."; Message.Message(); Cancel = True; EndDo; ...