Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
0036881Openbravo ERP02. Master data managementpublic2017-09-18 16:532017-09-26 15:25
JONHM 
AtulOpenbravo 
highmajoralways
closedfixed 
5
 
3.0PR17Q4 
aferraz
OBPS
Core
48450
No
0036881: Performance issue in 'Process Purchase Plan' process
The 'Process Purchase Plan' process is having a performance issue in environments with high volumes of data
1) Open 'Purchasing Plan' window and create a new record:
 - Organization: 020801 Fairfield Store (MT-UAT)
2) Run 'Process Purchase Plan' process

duration: 285721.160 ms execute <unnamed>: SELECT * FROM MRP_PURCHASE_RUN($1)
It seems to be related to "MRP_RUN_PURCHASELINE" table.
No tags attached.
Issue History
2017-09-18 16:53JONHMNew Issue
2017-09-18 16:53JONHMAssigned To => Triage Finance
2017-09-18 16:53JONHMOBNetwork customer => Yes
2017-09-18 16:53JONHMModules => Core
2017-09-18 16:53JONHMSupport ticket => 48450
2017-09-18 16:53JONHMResolution time => 1507500000
2017-09-18 16:53JONHMTriggers an Emergency Pack => No
2017-09-19 17:20JONHMSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=15937#r15937
2017-09-19 17:30aferrazAssigned ToTriage Finance => AtulOpenbravo
2017-09-21 21:49AtulOpenbravoStatusnew => scheduled
2017-09-23 10:10AtulOpenbravoNote Added: 0099505
2017-09-25 13:04hgbotCheckin
2017-09-25 13:04hgbotNote Added: 0099537
2017-09-25 13:04hgbotStatusscheduled => resolved
2017-09-25 13:04hgbotResolutionopen => fixed
2017-09-25 13:04hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/28caa19147055dfbe0cc2de5e4000de5eff43003 [^]
2017-09-25 13:04hgbotCheckin
2017-09-25 13:04hgbotNote Added: 0099538
2017-09-25 13:05aferrazReview Assigned To => aferraz
2017-09-25 13:05aferrazNote Added: 0099539
2017-09-25 13:05aferrazStatusresolved => closed
2017-09-25 13:05aferrazFixed in Version => 3.0PR17Q4
2017-09-26 15:25hudsonbotCheckin
2017-09-26 15:25hudsonbotNote Added: 0099591
2017-09-26 15:25hudsonbotCheckin
2017-09-26 15:25hudsonbotNote Added: 0099592

Notes
(0099505)
AtulOpenbravo   
2017-09-23 10:10   
Test Plan

- Login as F&B International Group Admin.
- Create a Preference for current client and set Enable Stock Reservation = Yes.
- Logout and Login again.
- Navigate to Planning Method window, create a new method
  - Organization: F&B España, S.A
  - Name: Test Planning Method
  - Save record.
- Navigate to Lines Tab and add records for following
  - Line : 10, Transaction Type:Material Requirement, Weighting: 1, Save record
  - Line : 20, Transaction Type:Pending Purchase Order, Weighting: 1, Save record
  - Line : 30, Transaction Type:Pending Sales Order, Weighting: 1, Save record
  - Line : 40, Transaction Type:Pending Work Requirement, Weighting: 1, Save record
  - Line : 50, Transaction Type:Sales Forecast, Weighting: 1, Save record
- Navigate to Planner window, create a new planner
  - Organization: F&B España, S.A
  - Name: Test Planner
  - Save record
- Navigate to Product window and create a new product
  - Organization: F&B España, S.A
  - Search Key: 36881
  - Name: Test Product 36881
  - UOM: Unit
  - Product Category: Others
  - Tax Category: Exento
  - Purchase = Sale = Stocked = Yes
  - Product Type: Item
  - Save record.
- Navigate to Price Tab add record for
  - Price List Version: Tarifa de ventas, Unit Price:15, List Price:15
  - Price List Version: Tarifa Bebidas Alegres, Unit Price:10, List Price:10
- Navigate to Purchase Tab add record for
  - Business Partner: Bebidas Alegres, S.L
  - Minimum Order Qty: 10
  - Purchasing Lead Time: 5
  - Standard Qty: 100
  - Save record.
- Navigate to Manufacturing Tab, create record for
  - Organization: F&B España, S.A
  - Planning Method: Test Planning Method
  - Planner: Test Planner
  - Safety Stock : 50
  - Minimum Lead Time: 5
  - Preferred Vendor: Bebidas Alegres, S.L
  - Save record.
- Navigate to Sales Order for Hoteles Buenas Noches, S.A in F&B España, S.A
  - Scheduled Delivery Date:10-10-2017
  - Add line for product: Test Product 36881, Quantity: 1000
  - Complete Sales Order.
- Create another sales order with same business partner, same product with scheduled delivery date as 15-10-2017 and Qty =500, complete it.
- Navigate to MRP Forecast, create a new record
  - Organization: F&B España, S.A
  - Document Date: Today's Date
  - Business Partner: Hoteles Buenas Noches
  - Save record
- Add lines as
  - Planned Date: 20-10-2017, Product: Test Product 36881, Quantity: 100
  - Planned Date: 25-10-2017, Product: Test Product 36881, Quantity: 100
  - Planned Date: 30-10-2017, Product: Test Product 36881, Quantity: 100

- Create a Goods Receipt in Organization: F&B España, S.A for business partner "Bebidas Alegres" and Product: Test Product 36881, Movement Date: Today's Date, in Lines
  - Storage Bin:RN-2-0-0, Quantity:100
  - Storage Bin:RS-0-0-0, Quantity:100, process Goods Receipt.

- Create a Purchase Order in Organization: F&B España, S.A for business partner "Bebidas Alegres", Ordered Date: Today's Date, Scheduled Delivery Date:30-09-2017, in Lines
  - Line 10, Product: Test Product 36881, Quantity:200
  - Line 10, Product: Test Product 36881, Quantity:200
  - Process Purchase Order
- Navigate to each line and click on Manage Prereservation.
  - Out of Qty 200, prereserve 100 Qty for Sales Order 1 Line with qty 1000 and 100 Qty for Sales Order 2 Line 2 with qty 500.

- Create a Purchase Order in Organization: F&B España, S.A for business partner "Bebidas Alegres", Ordered Date: Today's Date, in Lines
  - Line 10, Product: Test Product 36881, Quantity:50
  - Line 10, Product: Test Product 36881, Quantity:50
  - Process Purchase Order

- Create a Goods Receipt against above Purchase Order, Movement Date: Today's Date, in Lines
  - Storage Bin:RN-0-0-0, Quantity:50
  - Storage Bin:RS-0-0-0, Quantity:50, process Goods Receipt.
  
- Navigate to Requisition, create a new record for
  - Organization:
  - Business Partner:

- Add Lines to it
  - Need By Date: 05-10-2017, Product: Test Product 36881, Quantity:100
  - Need By Date: 10-10-2017, Product: Test Product 36881, Quantity:100
  - Need By Date: 15-10-2017, Product: Test Product 36881, Quantity:100
  - Need By Date: 20-10-2017, Product: Test Product 36881, Quantity:100
  - Complete Requisition.

- Navigate to Purchasing Plan window, create a record for
  - Organization: F&B España, S.A
  - Name: Test 36881
  - Document Date: 22-09-2017
  - Time Horizon: 60
  - Business Partner Category: Customer - Tier 3
  - Save record
  - Click on button Process Purchase Plan

- Realize that 18 records are created in lines.
  
  1 line for transaction type Stock, Qty: 300, Planned Date: 22-09-2017
  1 line for transaction type Minimum Stock Qty = -50, Planned Date: 22-09-2017
  4 lines for material requirement each qty 100 planned date 05-10-2017, 10-10-2017, 15-10-2017, 20-10-2017.
  2 lines for pending purchase order planned date 30-09-2017, qty 200, 200
  2 lines for pending sales order planned date 10-10-2017, Qty:-1000, 15-10-2017, Qty:-500
  3 lines for sales forecast planned date 20-10-2017, Qty:-100, 25-10-2017, Qty:-100, 30-10-2017, Qty: -100
  5 lines for suggested purchase order planned date 10-10-2017, qty 550, 15-10-2017, qty 600, 20-10-2017, qty 200, 25-10-2017, qty 100, 30-10-2017, qty 100
(0099537)
hgbot   
2017-09-25 13:04   
Repository: erp/devel/pi
Changeset: 28caa19147055dfbe0cc2de5e4000de5eff43003
Author: Atul Gaware <atul.gaware <at> openbravo.com>
Date: Sat Sep 23 00:24:41 2017 +0530
URL: http://code.openbravo.com/erp/devel/pi/rev/28caa19147055dfbe0cc2de5e4000de5eff43003 [^]

Fixes issue 36881: Performance issue in 'Process Purchase Plan' process

Avoid subqueries for m_matchpo, m_reservation and m_reservation stock table
while inserting order lines product in MRP_RUN_INITIALIZE db procedure

---
M src-db/database/model/functions/MRP_RUN_INITIALIZE.xml
---
(0099538)
hgbot   
2017-09-25 13:04   
Repository: erp/devel/pi
Changeset: d3fd6e8223f809f9a8aff81a3ca21af73d71f92a
Author: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com>
Date: Mon Sep 25 12:27:35 2017 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/d3fd6e8223f809f9a8aff81a3ca21af73d71f92a [^]

Related to issue 36881: Format having clause

---
M src-db/database/model/functions/MRP_RUN_INITIALIZE.xml
---
(0099539)
aferraz   
2017-09-25 13:05   
Code review + Testing OK
(0099591)
hudsonbot   
2017-09-26 15:25   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/8fa643b4437e [^]
Maturity status: Test
(0099592)
hudsonbot   
2017-09-26 15:25   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/8fa643b4437e [^]
Maturity status: Test