Projects:Requisitions/Development Status
Contents |
Introduction
To fulfill the specifications of the project there has been detected three main developments that are going to be done each one on its own development iteration. These are the refactoring of the Requisitions window, the creation of a manual window to create a purchase order from the selected requisition lines and to create a process to create purchase orders automatically from a Requisition. There is a 5th iteration to ensure that the MRP Module continues working as expected.
The development is being done in the requisitions branch created for this project. The new developments will not be merged to the subversion trunk till QA Team test them.
Iteration 1: Refactoring of Requisitions window
This iteration consists on moving the existing Requisition window from the MRP Management module to Procurement Management and creating the necessary new fields. There will also be necessary to modify and create other Application Dictionary elements such as callouts. The window is splitted in two different windows with the same fields but different access levels. In Requisition window the user can enter new requisitions and complete them, the window is filtered so only are viewed own requisitions. The Manage Requisitions is mean for the Purchase Manager, in this window are viewed all the requisitions (by default only completed requisitions) and it is possible to create purchase orders from the requisition.
Both windows have three tabs (header, lines and order line) that correspond with the M_Requisition, M_RequisitionLine and M_RequisitionOrder tables.
Requisition window
Header tab
The header tab groups the different products that are required at a time by an user.
It is possible to print the requisition using the print icon on the toolbar.
| ColumnName | Description | Status | O. | R. |
|---|---|---|---|---|
| DocumentNo | Numerical identifier of the requisition, is automatically generated | Ready | ||
| Description | Description of the requisition | Ready | * | |
| C_BPartner_ID | Preferred vendor for the requisition | Ready | * | |
| M_PriceList_ID | Preferred price list for the requisition | Ready | * | |
| C_Currency_ID | Currency of the selected price list, is updated by a callout when a price list is selected | Ready | * | * |
| CreatePO | Button for the Create purchase order process | Ready (new) | * |
Note: O. stands for Optional, R. stands for Read only
Lines tab
The lines tab are the requisitions. In this tab is set the requested product with the quantity and due date.
| ColumnName | Description | Status | O. | R. |
|---|---|---|---|---|
| M_Product_ID | Requested Product | Ready | ||
| NeedByDate | Date when is needed the product | Ready | ||
| Qty | Requested quantity | Ready | ||
| C_UOM_ID | Unit of measure of the product. Filled by a callout | Ready | * | |
| OrderQty | Requested quantity in the order unit of measure | Ready (new) | * | |
| M_Product_UOM_ID | Order unit of measure of the request | Ready (new) | * | |
| M_AttributeSetInstance_ID | Requested attributes of the product | Ready (new) | * | |
| C_BPartner_ID | Preferred vendor for the requisition line | Ready (new) | * | |
| Description | Description field for interesting facts about the requisition line | Ready (new) | * | |
| PriceStd | Price standard at which the purchase order is done. Filled automatically when the product is chosen | Ready | * | |
| PriceLimit | Highest price at which the purchase order is done. Filled automatically when the product is chosen | Ready | * | |
| PriceList | Catalog price of the product. Filled automatically when the product is chosen | Ready | * | |
| LineNetAmt | Amount of the requisition line. Automatically calculated when PriceStd and Qty are filled | Ready | * | * |
| ReqStatus | Status of the requisition, possible values are Open (default), Closed, Planed and Discarded. | Ready (new) | ||
| OrderedQty | Quantity that has been ordered. It is updated by a trigger in M_RequisitionOrder table. | Ready (new) | * | |
| InternalNotes | Internal notes about the requisition line. | Ready (new) | * | |
| SupplierNotes | Notes for the supplier. | Ready (new) | * | |
| C_Order_ID | Deprecated |
Note: O. stands for Optional, R. stands for Read only
Order Lines tab
The order lines tab are the created purchase order lines to satisfy the requisitions. In this tab is also included the quantity that has been satisfied.
| ColumnName | Description | Status | O. | R. |
|---|---|---|---|---|
| C_OrderLine_ID | Purchase Order line associated to the Requisition. | Ready (new) | ||
| Qty | Quantity of the Order Line that is associated with the requisition. | Ready (new) |
Note: O. stands for Optional, R. stands for Read only
Callouts
SL_RequisitionLine_Product
This callout updates the PriceStd, PriceLimit, PriceList, C_UOM_ID and M_AttributeSetInstance_ID It also populates the Product UOM combo when the selected product has any. It also updates the prices when the price list changes.
- It only calculates prices where there are selected both a Product and a Price List in the header tab.
- To calculate the PricelistVersion it uses the Due Date, if this is null it defaults the date to now().
- It shows a message when is not found an active price list version.
- It shows a message when the selected product is not in the active price list version.
SL_RequisitionLine_Amt
This callout updates the LineNetAmt when Qty or PriceStd are changed.
SL_RequisitionLine_Conversion
This callout updates Qty when the OrderQuantity or Product_UOM are changed.
SL_RequisitionOrder_Order
This callout updates the quantity with the total quantity of the Purchase Order line. If the quantity of the requisition line that is pending to order is smaller than the quantity of the order line the quantity that is set by the callout is just the pending quantity.
Triggers
M_RequisitionOrder_Trg
This trigger updates the OrderedQty column of the correspondent M_RequisitionLine when a record is Inserted, Updated or Created on M_RequisitionOrder table.
Iteration 2: Requisition status implementation
In this iteration is going to be properly implemented the different statuses of requisitions and requisition lines. For that purpose there will be created new procedures and triggers.
Triggers
M_Requisition_Trg
Trigger to block the requisitions depending on their status.
| Req Status | Action | Permission |
|---|---|---|
| Closed | Any | Blocked |
| Draft | Any | Allowed |
| Completed | INSERT DELETE | Blocked |
| Completed | UPDATE | Some columns blocked (1) |
(1) Blocked columns: DocumentNo, C_BPartner_ID, M_PriceList_ID, C_Currency_ID, AD_User_ID
M_RequisitionLine_Trg
Trigger to block the requisition lines depending on the status of the requisition and the requisition line. It is only possible to insert new lines when the requisition is in draft status.
| Req Status | Req Line Status | Action | Permission |
|---|---|---|---|
| Closed | Any | Any | Blocked |
| Draft | Open (orderedqty=0) | Any | Allowed |
| Draft | Open (orderedqty<>0) | UPDATE | Some columns blocked (1) |
| Draft | Open (orderedqty<>0) | DELETE | Blocked |
| Draft | NOT open | any | Blocked |
| Completed | Any | DELETE | Blocked |
| Completed | Open | UPDATE | Some columns blocked (1) |
| Completed | NOT Open | UPDATE | Blocked |
| Any | Planned | Any | Blocked |
(1) Blocked columns: M_Requisition_ID, M_Product_ID, Qty, PriceStd, PriceLimit, PriceList, LineNetAmt, C_BPartner_ID, C_UOM_ID, M_Product_UOM_ID, QtyOrder, M_AttributeSetInstance_ID, NeedByDate
M_RequisitionOrder_Trg
Extended to only enable modifications when the requisition is Completed and the Requisition line open.
Procedures
M_RequisitionLine_Status
This procedure changes the status of the requisitionline.
The planned status is managed by the MRP Module.
An open requisition is closed or canceled depending on the ordered qty. When the requisition has some purchase orders assigned it is closed.
The process checks if exists requisition lines in open status, when none exists the requisition is closed.
The procedure can be called from the application or from other procedures (a M_RequisitionLine_Status0 is also created).
M_Requisition_Post
This procedure manages all the status changes of the requisitions.
When the requisition is being closed the procedure calls the M_RequisitionLine_Status to close/cancel all open requisition lines.
In the following chart is shown how can change the status the requisitions and requisition lines.
Iteration 3: Create Purchase Orders from Requisition manual window
In this iteration is created a new manual window. This window will show the open requisition lines, with the possibility to filter them. The user then has to select the requisition lines that are desired to be included in the Purchase Order and has the possibility to change the quantity to order and the price. Pressing the Create PO button a popup is opened where the necessary parameters to create the Purchase Order are set, then pressing the OK button one purchase order is generated. When a line is selected it is locked by the user that is logged.
The window has 4 main areas:
- Filter area: Several filters to search completed requisitions.
- Searched lines: Relation of searched requisition lines that fulfills the filters.
- Selected lines: Relation of selected requisition lines.
- Process area: Button to open the popup that creates the Purchase Order.
The popup has the necessary parameters to create the purchase order. Some of them are automatically filled depending on the selected requisition lines.
Requisition to Order form
Filter area
There are several available filters:
- Product: Filters by the product of the requisition line.
- Need by date: It is possible to filter by need by date using a date range.
- Requester: Filters by the requester of the requisition line.
- Vendor: Filters by the preferred vendor of the requisition line (it is prioritized the vendor that is set in the requisition line, if this is null it is used the vendor of the requisition).
- Lines without Vendor: When is checked there are also included requisition lines that doesn't have an associated vendor when the window is being filtered by vendor.
- Organization: Filters by the selected organization and all the child organizations.
Searched requisition lines
In this section there is a table with all the requisition lines that fulfills the filters. For each requisition line is shown:
- Need by date
- Product
- Quantity
- Requester
- Vendor - PriceList
- Price
The result is ordered by need by date, product and attribute. The desired lines have to be added to the selected lines using the add button. It is possible to select all the lines using the check in the header of the table.
Selected requisition lines
This area shows all the lines locked by the user. The requisition lines are grouped by vendor and price list.
For each vendor and price list there is a table where is shown the product, the attribute, the need by date, the requested quantity, the matched quantity and the price list. For each requisition line it is necessary to set the quantity to order and optionally the price.
Create PO
In the last area there is the Create PO button that opens the popup that creates the Purchase Order.
Create Purchase Order popup
In the top of the popup there is a description of the necessary data to correctly create the purchase order. The popup will not open if no requisition line is selected in the locked area.
The parameters that have to be set are:
- Order date, defaulted to today.
- Vendor
- Price list
- Organization
- Warehouse
When the popup is loaded depending on the selected requisition lines it might be filled the vendor, the price list and the organization. The result of this process is shown in an info message. Those fields are filled when there all the requisition lines have the same vendor, price list and/or organization. To set the vendor and price list is used the value set in the requisition line and if this value is null is used the value of the requisition. Null values are not considered to set the vendor or price list when the popup is loaded. e.g. if 3 lines are selected and two of them haven't a preferred vendor and the 3rd has vendor A the field will be filled with vendor A. If lines of different vendors, price lists of organizations are selected a message will appear when the popup is opened and the fields (Vendor, Organization, Price list) will not be filled.
Create purchase order process
When is pressed to OK button in the popup to create the purchase order is checked that it is possible to get a price for each selected requisition line. To set the price actual of each order line is used the price of the selected price list, if the product is not included in the price list is used the price actual set in the Requisition To Order form. For the rest of the different prices (price list and price limit) are used the prices defined in the selected price list, if the product is not in the selected price list are used the prices defined in the requisition line and if those are not defined it is used the price actual.
Several requisition lines can be grouped in a unique order line when they have the same product, attribute, price and description (set as supplier notes in the requisition line).
The created purchase order is also completed and the message of the completion process is shown in the popup window including the Document number of the new purchase order. When the complete process of the Purchase Order fails the order stays in draft status and the message is shown so it is possible to fix it. If the Purchase Order is created properly the order lines are matched with the correspondent requisition lines and these are unlocked. The process button will do some checks before creating the purchase order:
Iteration 4: Create Purchase Order process
This process is launched from the Requisition Header and creates and processes Purchase Orders according to the Product requested. It takes into account the possible preferred vendors and prices when they are not defined.
When the button is pressed it is opened a pop-up with several parameters:
- Order Date, mandatory: it is used for the necessary dates of the Purchase Orders.
- Business Partner, optional: it is the preferred vendor for the Purchase Orders.
- Price List, optional: it is the preferred price list for the Purchase Orders.
- Organization, mandatory: it is the Organization used for the Purchase Orders.
- Service Point, mandatory: it is the Warehouse needed in the Purchase Order headers.
At the beginning of the process is checked that all the requisition line has a vendor available. The vendor used for the PO is the selected one in the pop-up. If no vendor has been selected it is searched in the requisition line, the requisition header and finally the default vendor of the product. When it is not possible to get a vendor for a requisition line is thrown an error message with the line number and the product.
The requisitions lines are processed one by one ordered by vendor and price list, as is necessary to create a new header when one of these values change. The price list used is the price list selected in the pop-up. If it hasn't been selected one it is searched in the requisition line, the requisition header and finally the default purchase price list of the correspondent vendor. If it is not possible to get a price list for a requisition line an error message is thrown.
For each combination of vendor and price list is created a purchase order header. There are used the date, organization and warehouse set in the pop-up window. To get the rest of the mandatory fields of the header are used the correspondent default values. And is created a purchase order line for each requisition line.
To get the necessary prices for the purchase order lines are used the values set in the requisition line and the correspondent price list version according to the date set in the pop-up. For the price actual is prioritized the price set in the requisition line, if this is blank it is used the price defined in the price list version. For the price list is used the price list of the price list version, if this is null is used the price list defined in the requisition line and finally the price actual of the requisition line. For the price standard and the price limit are used the prices of the price list version, and if those are null is used the price actual of the requisition line.
The quantity ordered is the requested quantity minus the previously ordered quantity. So when the purchase order is created the requisition line will be completely ordered. After the order line it is also inserted a record in the requisition order tab to relate the newly created order line and the correspondent requisition line. This insert has a trigger that also updates the ordered quantity of the requisition line. At the end the requisition line is closed, and when the requisition does not have any requisition line opened it is also closed.
All the created purchase orders are processed and the output message shows all the document numbers of those purchase orders.
Iteration 5: Fixes on MRP Module
In this iteration there will be made the necessary changes to the MRP Module detected during the development of the project.
List of changes:
- The generation of the requisition has to include the new columns, (ReqStatus,...)
- When the Purchase Orders are generated in the Purchase Plan the Requisitions involved has to change the ReqStatus column to Planned.
- Only open and unlocked requisitions are taken into account.
- The requisition lines are locked when are included in Purchase Plan run, and set planned when the Purchase Orders are generated.