Project:
View Issue Details[ Jump to Notes ] | [ Issue History ] [ Print ] | |||||||
ID | ||||||||
0024770 | ||||||||
Type | Category | Severity | Reproducibility | Date Submitted | Last Update | |||
defect | [Openbravo ERP] A. Platform | minor | have not tried | 2013-09-14 09:06 | 2013-09-19 10:40 | |||
Reporter | shuehner | View Status | public | |||||
Assigned To | shuehner | |||||||
Priority | normal | Resolution | fixed | Fixed in Version | ||||
Status | closed | Fix in branch | Fixed in SCM revision | ec5db1dfd224 | ||||
Projection | none | ETA | none | Target Version | 3.0MP28 | |||
OS | Any | Database | Any | Java version | ||||
OS Version | Database version | Ant version | ||||||
Product Version | SCM revision | |||||||
Review Assigned To | dmiguelez | |||||||
Web browser | ||||||||
Modules | Core | |||||||
Regression level | ||||||||
Regression date | ||||||||
Regression introduced in release | ||||||||
Regression introduced by commit | ||||||||
Triggers an Emergency Pack | No | |||||||
Summary | 0024770: Some views are exported differently on postgresql9.3 | |||||||
Description | When running a dbconsistency test on postgresql9.3 some views are exported slightly different. If a view is doing joins and is using the same alias in the joins more then once then postgres does change this on export of views to have more unique aliases. | |||||||
Steps To Reproduce | On postgresql9.3 run install.source put modules into development (type <> 'T') run export.database hg diff | |||||||
Proposed Solution | Review and commit the slight view changes as they should work on every version and are also imo more readable. Maybe adjust the auto-generated alias names if that is preferred. | |||||||
Tags | No tags attached. | |||||||
Attached Files | 24770.diff [^] (32,411 bytes) 2013-09-14 09:07 [Show Content] [Hide Content]diff --git a/modules/org.openbravo.advpaymentmngt/src-db/database/model/views/APRM_RECONCILIATION_V.xml b/modules/org.openbravo.advpaymentmngt/src-db/database/model/views/APRM_RECONCILIATION_V.xml --- a/modules/org.openbravo.advpaymentmngt/src-db/database/model/views/APRM_RECONCILIATION_V.xml +++ b/modules/org.openbravo.advpaymentmngt/src-db/database/model/views/APRM_RECONCILIATION_V.xml @@ -1,4 +1,4 @@ <?xml version="1.0"?> <database name="VIEW APRM_RECONCILIATION_V"> - <view name="APRM_RECONCILIATION_V"><![CDATA[SELECT fin_reconciliation.fin_reconciliation_id AS aprm_reconciliation_v_id, fin_reconciliation.fin_reconciliation_id, fin_reconciliation.ad_client_id, fin_reconciliation.ad_org_id, fin_reconciliation.created, fin_reconciliation.createdby, fin_reconciliation.updated, fin_reconciliation.updatedby, fin_reconciliation.isactive, fin_reconciliation.fin_financial_account_id, fin_reconciliation.documentno, fin_reconciliation.c_doctype_id, fin_reconciliation.dateto, fin_reconciliation.statementdate, fin_reconciliation.endingbalance, fin_reconciliation.startingbalance, fin_reconciliation.docstatus, fin_reconciliation.processing, fin_reconciliation.processed, fin_reconciliation.posted, fin_reconciliation.printdetailed, fin_reconciliation.em_aprm_process_reconciliation AS process_reconciliation, fin_reconciliation.printsummary, COALESCE(item.item_no, 0) AS item_no, COALESCE(item.item_amt, 0) AS item_amt, COALESCE(unrec.unrec_no, 0) AS unrec_no, COALESCE(unrec.unrec_amt, 0) AS unrec_amt, COALESCE(outstanding.payment_no, 0) AS payment_no, COALESCE(outstanding.payment_amt, 0) AS payment_amt, COALESCE(outstanding.deposit_no, 0) AS deposit_no, COALESCE(outstanding.deposit_amt, 0) AS deposit_amt, (SELECT ad_table.ad_table_id FROM ad_table WHERE lower(ad_table.tablename) = 'fin_reconciliation') AS forced_table_id FROM fin_reconciliation LEFT JOIN (SELECT fin_finacc_transaction.fin_reconciliation_id, count(*) AS item_no, COALESCE(sum(fin_finacc_transaction.depositamt - fin_finacc_transaction.paymentamt), 0) AS item_amt FROM fin_finacc_transaction GROUP BY fin_finacc_transaction.fin_reconciliation_id) item ON fin_reconciliation.fin_reconciliation_id = item.fin_reconciliation_id LEFT JOIN (SELECT fin_reconciliation.fin_reconciliation_id, count(*) AS unrec_no, COALESCE(sum(fin_bankstatementline.cramount - fin_bankstatementline.dramount), 0) AS unrec_amt FROM fin_bankstatementline, fin_bankstatement, fin_reconciliation WHERE (fin_bankstatementline.fin_finacc_transaction_id IS NULL OR (EXISTS (SELECT 1 FROM fin_finacc_transaction, fin_reconciliation rec WHERE fin_finacc_transaction.fin_reconciliation_id = rec.fin_reconciliation_id AND fin_bankstatementline.fin_finacc_transaction_id = fin_finacc_transaction.fin_finacc_transaction_id AND fin_finacc_transaction.fin_financial_account_id = fin_bankstatement.fin_financial_account_id AND rec.created > fin_reconciliation.created))) AND fin_bankstatementline.datetrx <= fin_reconciliation.dateto AND fin_bankstatement.fin_bankstatement_id = fin_bankstatementline.fin_bankstatement_id AND fin_bankstatement.fin_financial_account_id = fin_reconciliation.fin_financial_account_id GROUP BY fin_reconciliation.fin_reconciliation_id) unrec ON fin_reconciliation.fin_reconciliation_id = unrec.fin_reconciliation_id LEFT JOIN (SELECT fin_reconciliation.fin_reconciliation_id, COALESCE(sum(CASE COALESCE(fin_finacc_transaction.paymentamt, 0) WHEN 0 THEN 0 ELSE 1 END), 0) AS payment_no, COALESCE(sum(fin_finacc_transaction.paymentamt), 0) AS payment_amt, COALESCE(sum(CASE COALESCE(fin_finacc_transaction.depositamt, 0) WHEN 0 THEN 0 ELSE 1 END), 0) AS deposit_no, COALESCE(sum(fin_finacc_transaction.depositamt), 0) AS deposit_amt FROM fin_finacc_transaction, fin_reconciliation WHERE (fin_finacc_transaction.fin_reconciliation_id IS NULL OR (EXISTS (SELECT 1 FROM fin_reconciliation r WHERE r.dateto > fin_reconciliation.dateto AND fin_finacc_transaction.fin_reconciliation_id = r.fin_reconciliation_id))) AND fin_finacc_transaction.statementdate <= fin_reconciliation.dateto AND fin_finacc_transaction.fin_financial_account_id = fin_reconciliation.fin_financial_account_id GROUP BY fin_reconciliation.fin_reconciliation_id) outstanding ON fin_reconciliation.fin_reconciliation_id = outstanding.fin_reconciliation_id]]></view> + <view name="APRM_RECONCILIATION_V"><![CDATA[SELECT fin_reconciliation.fin_reconciliation_id AS aprm_reconciliation_v_id, fin_reconciliation.fin_reconciliation_id, fin_reconciliation.ad_client_id, fin_reconciliation.ad_org_id, fin_reconciliation.created, fin_reconciliation.createdby, fin_reconciliation.updated, fin_reconciliation.updatedby, fin_reconciliation.isactive, fin_reconciliation.fin_financial_account_id, fin_reconciliation.documentno, fin_reconciliation.c_doctype_id, fin_reconciliation.dateto, fin_reconciliation.statementdate, fin_reconciliation.endingbalance, fin_reconciliation.startingbalance, fin_reconciliation.docstatus, fin_reconciliation.processing, fin_reconciliation.processed, fin_reconciliation.posted, fin_reconciliation.printdetailed, fin_reconciliation.em_aprm_process_reconciliation AS process_reconciliation, fin_reconciliation.printsummary, COALESCE(item.item_no, 0) AS item_no, COALESCE(item.item_amt, 0) AS item_amt, COALESCE(unrec.unrec_no, 0) AS unrec_no, COALESCE(unrec.unrec_amt, 0) AS unrec_amt, COALESCE(outstanding.payment_no, 0) AS payment_no, COALESCE(outstanding.payment_amt, 0) AS payment_amt, COALESCE(outstanding.deposit_no, 0) AS deposit_no, COALESCE(outstanding.deposit_amt, 0) AS deposit_amt, (SELECT ad_table.ad_table_id FROM ad_table WHERE lower(ad_table.tablename) = 'fin_reconciliation') AS forced_table_id FROM fin_reconciliation LEFT JOIN (SELECT fin_finacc_transaction.fin_reconciliation_id, count(*) AS item_no, COALESCE(sum(fin_finacc_transaction.depositamt - fin_finacc_transaction.paymentamt), 0) AS item_amt FROM fin_finacc_transaction GROUP BY fin_finacc_transaction.fin_reconciliation_id) item ON fin_reconciliation.fin_reconciliation_id = item.fin_reconciliation_id LEFT JOIN (SELECT fin_reconciliation_1.fin_reconciliation_id, count(*) AS unrec_no, COALESCE(sum(fin_bankstatementline.cramount - fin_bankstatementline.dramount), 0) AS unrec_amt FROM fin_bankstatementline, fin_bankstatement, fin_reconciliation fin_reconciliation_1 WHERE (fin_bankstatementline.fin_finacc_transaction_id IS NULL OR (EXISTS (SELECT 1 FROM fin_finacc_transaction, fin_reconciliation rec WHERE fin_finacc_transaction.fin_reconciliation_id = rec.fin_reconciliation_id AND fin_bankstatementline.fin_finacc_transaction_id = fin_finacc_transaction.fin_finacc_transaction_id AND fin_finacc_transaction.fin_financial_account_id = fin_bankstatement.fin_financial_account_id AND rec.created > fin_reconciliation_1.created))) AND fin_bankstatementline.datetrx <= fin_reconciliation_1.dateto AND fin_bankstatement.fin_bankstatement_id = fin_bankstatementline.fin_bankstatement_id AND fin_bankstatement.fin_financial_account_id = fin_reconciliation_1.fin_financial_account_id GROUP BY fin_reconciliation_1.fin_reconciliation_id) unrec ON fin_reconciliation.fin_reconciliation_id = unrec.fin_reconciliation_id LEFT JOIN (SELECT fin_reconciliation_1.fin_reconciliation_id, COALESCE(sum(CASE COALESCE(fin_finacc_transaction.paymentamt, 0) WHEN 0 THEN 0 ELSE 1 END), 0) AS payment_no, COALESCE(sum(fin_finacc_transaction.paymentamt), 0) AS payment_amt, COALESCE(sum(CASE COALESCE(fin_finacc_transaction.depositamt, 0) WHEN 0 THEN 0 ELSE 1 END), 0) AS deposit_no, COALESCE(sum(fin_finacc_transaction.depositamt), 0) AS deposit_amt FROM fin_finacc_transaction, fin_reconciliation fin_reconciliation_1 WHERE (fin_finacc_transaction.fin_reconciliation_id IS NULL OR (EXISTS (SELECT 1 FROM fin_reconciliation r WHERE r.dateto > fin_reconciliation_1.dateto AND fin_finacc_transaction.fin_reconciliation_id = r.fin_reconciliation_id))) AND fin_finacc_transaction.statementdate <= fin_reconciliation_1.dateto AND fin_finacc_transaction.fin_financial_account_id = fin_reconciliation_1.fin_financial_account_id GROUP BY fin_reconciliation_1.fin_reconciliation_id) outstanding ON fin_reconciliation.fin_reconciliation_id = outstanding.fin_reconciliation_id]]></view> </database> diff --git a/src-db/database/model/views/C_INVOICE_CANDIDATE_V.xml b/src-db/database/model/views/C_INVOICE_CANDIDATE_V.xml --- a/src-db/database/model/views/C_INVOICE_CANDIDATE_V.xml +++ b/src-db/database/model/views/C_INVOICE_CANDIDATE_V.xml @@ -1,4 +1,4 @@ <?xml version="1.0"?> <database name="VIEW C_INVOICE_CANDIDATE_V"> - <view name="C_INVOICE_CANDIDATE_V"><![CDATA[SELECT sq.ad_client_id, sq.ad_org_id, sq.c_bpartner_id, sq.c_order_id, sq.documentno, sq.dateordered, sq.c_doctype_id, sq.amountlines, sq.amountlinesgross, sq.notinvoicedlines, round(sq.notinvoicedlinesgross, c.stdprecision) AS notinvoicedlinesgross, sq.term, sq.pendinglines, round(sq.pendinglinesgross, c.stdprecision) AS pendinglinesgross, sq.qtyordered, sq.qtydelivered FROM (SELECT o.ad_client_id, o.ad_org_id, o.c_bpartner_id, o.c_order_id, o.documentno, o.dateordered, o.c_doctype_id, o.totallines AS amountlines, o.grandtotal AS amountlinesgross, sum(round((l.qtyordered - l.qtyinvoiced) * l.priceactual, c.stdprecision)) AS notinvoicedlines, sum(round((l.qtyordered - l.qtyinvoiced) * l.priceactual, c.stdprecision) * (1 + t.rate / 100)) AS notinvoicedlinesgross, o.invoicerule AS term, sum(round((l.qtydelivered - l.qtyinvoiced) * l.priceactual, c.stdprecision)) AS pendinglines, sum(round((l.qtydelivered - l.qtyinvoiced) * l.priceactual, c.stdprecision) * (1 + t.rate / 100)) AS pendinglinesgross, sum(abs(l.qtyordered)) AS qtyordered, sum(abs(l.qtydelivered)) AS qtydelivered, o.m_pricelist_id, c.c_currency_id FROM c_order o JOIN m_pricelist p ON o.m_pricelist_id = p.m_pricelist_id JOIN c_currency c ON p.c_currency_id = c.c_currency_id JOIN c_orderline l ON o.c_order_id = l.c_order_id JOIN c_bpartner bp ON o.c_bpartner_id = bp.c_bpartner_id JOIN c_tax t ON t.c_tax_id = l.c_tax_id LEFT JOIN c_invoiceschedule si ON bp.c_invoiceschedule_id = si.c_invoiceschedule_id WHERE (o.docstatus IN ('CO', 'CL', 'IP')) AND (o.c_doctype_id IN (SELECT c_doctype.c_doctype_id FROM c_doctype WHERE c_doctype.docbasetype = 'SOO' AND (c_doctype.docsubtypeso NOT IN ('ON', 'OB', 'WR')))) AND (o.invoicerule = 'I' OR o.invoicerule = 'O' OR o.invoicerule = 'N' OR o.invoicerule = 'D' OR o.invoicerule = 'S' AND (si.invoicefrequency IS NULL OR si.invoicefrequency = 'D' OR si.invoicefrequency = 'W' OR si.invoicefrequency = 'T' AND trunc(o.dateordered) <= (TRUNC(now(), 'MM') + si.invoicedaycutoff - 1) AND trunc(now()) >= (TRUNC(o.dateordered, 'MM') + si.invoiceday - 1) OR trunc(o.dateordered) <= (TRUNC(now(), 'MM') + si.invoicedaycutoff + 14) AND trunc(now()) >= (TRUNC(o.dateordered, 'MM') + si.invoiceday + 14) OR si.invoicefrequency = 'M' AND trunc(o.dateordered) <= (TRUNC(now(), 'MM') + si.invoicedaycutoff - 1) AND trunc(now()) >= (TRUNC(o.dateordered, 'MM') + si.invoiceday - 1))) AND (abs(l.qtyordered - l.qtyinvoiced) <> 0 OR abs(l.qtydelivered - l.qtyinvoiced) <> 0) GROUP BY o.ad_client_id, o.ad_org_id, o.c_bpartner_id, o.c_order_id, o.documentno, o.dateordered, o.c_doctype_id, o.totallines, o.grandtotal, o.invoicerule, o.m_pricelist_id, c.c_currency_id) sq JOIN m_pricelist p ON sq.m_pricelist_id = p.m_pricelist_id JOIN c_currency c ON sq.c_currency_id = c.c_currency_id]]></view> + <view name="C_INVOICE_CANDIDATE_V"><![CDATA[SELECT sq.ad_client_id, sq.ad_org_id, sq.c_bpartner_id, sq.c_order_id, sq.documentno, sq.dateordered, sq.c_doctype_id, sq.amountlines, sq.amountlinesgross, sq.notinvoicedlines, round(sq.notinvoicedlinesgross, c.stdprecision) AS notinvoicedlinesgross, sq.term, sq.pendinglines, round(sq.pendinglinesgross, c.stdprecision) AS pendinglinesgross, sq.qtyordered, sq.qtydelivered FROM (SELECT o.ad_client_id, o.ad_org_id, o.c_bpartner_id, o.c_order_id, o.documentno, o.dateordered, o.c_doctype_id, o.totallines AS amountlines, o.grandtotal AS amountlinesgross, sum(round((l.qtyordered - l.qtyinvoiced) * l.priceactual, c_1.stdprecision)) AS notinvoicedlines, sum(round((l.qtyordered - l.qtyinvoiced) * l.priceactual, c_1.stdprecision) * (1 + t.rate / 100)) AS notinvoicedlinesgross, o.invoicerule AS term, sum(round((l.qtydelivered - l.qtyinvoiced) * l.priceactual, c_1.stdprecision)) AS pendinglines, sum(round((l.qtydelivered - l.qtyinvoiced) * l.priceactual, c_1.stdprecision) * (1 + t.rate / 100)) AS pendinglinesgross, sum(abs(l.qtyordered)) AS qtyordered, sum(abs(l.qtydelivered)) AS qtydelivered, o.m_pricelist_id, c_1.c_currency_id FROM c_order o JOIN m_pricelist p_1 ON o.m_pricelist_id = p_1.m_pricelist_id JOIN c_currency c_1 ON p_1.c_currency_id = c_1.c_currency_id JOIN c_orderline l ON o.c_order_id = l.c_order_id JOIN c_bpartner bp ON o.c_bpartner_id = bp.c_bpartner_id JOIN c_tax t ON t.c_tax_id = l.c_tax_id LEFT JOIN c_invoiceschedule si ON bp.c_invoiceschedule_id = si.c_invoiceschedule_id WHERE (o.docstatus IN ('CO', 'CL', 'IP')) AND (o.c_doctype_id IN (SELECT c_doctype.c_doctype_id FROM c_doctype WHERE c_doctype.docbasetype = 'SOO' AND (c_doctype.docsubtypeso NOT IN ('ON', 'OB', 'WR')))) AND (o.invoicerule = 'I' OR o.invoicerule = 'O' OR o.invoicerule = 'N' OR o.invoicerule = 'D' OR o.invoicerule = 'S' AND (si.invoicefrequency IS NULL OR si.invoicefrequency = 'D' OR si.invoicefrequency = 'W' OR si.invoicefrequency = 'T' AND trunc(o.dateordered) <= (TRUNC(now(), 'MM') + si.invoicedaycutoff - 1) AND trunc(now()) >= (TRUNC(o.dateordered, 'MM') + si.invoiceday - 1) OR trunc(o.dateordered) <= (TRUNC(now(), 'MM') + si.invoicedaycutoff + 14) AND trunc(now()) >= (TRUNC(o.dateordered, 'MM') + si.invoiceday + 14) OR si.invoicefrequency = 'M' AND trunc(o.dateordered) <= (TRUNC(now(), 'MM') + si.invoicedaycutoff - 1) AND trunc(now()) >= (TRUNC(o.dateordered, 'MM') + si.invoiceday - 1))) AND (abs(l.qtyordered - l.qtyinvoiced) <> 0 OR abs(l.qtydelivered - l.qtyinvoiced) <> 0) GROUP BY o.ad_client_id, o.ad_org_id, o.c_bpartner_id, o.c_order_id, o.documentno, o.dateordered, o.c_doctype_id, o.totallines, o.grandtotal, o.invoicerule, o.m_pricelist_id, c_1.c_currency_id) sq JOIN m_pricelist p ON sq.m_pricelist_id = p.m_pricelist_id JOIN c_currency c ON sq.c_currency_id = c.c_currency_id]]></view> </database> diff --git a/src-db/database/model/views/M_RESERVATION_PICK_EDIT.xml b/src-db/database/model/views/M_RESERVATION_PICK_EDIT.xml --- a/src-db/database/model/views/M_RESERVATION_PICK_EDIT.xml +++ b/src-db/database/model/views/M_RESERVATION_PICK_EDIT.xml @@ -1,4 +1,4 @@ <?xml version="1.0"?> <database name="VIEW M_RESERVATION_PICK_EDIT"> - <view name="M_RESERVATION_PICK_EDIT"><![CDATA[SELECT COALESCE(r.m_reservation_id, sol.c_orderline_id) || COALESCE(stock.m_storage_detail_id, stock.c_orderline_id) AS m_reservation_pick_edit_id, COALESCE(r.ad_client_id, sol.ad_client_id) AS ad_client_id, COALESCE(r.ad_org_id, sol.ad_org_id) AS ad_org_id, 'Y' AS isactive, COALESCE(r.updated, sol.updated) AS updated, COALESCE(r.updatedby, sol.updatedby) AS updatedby, COALESCE(r.created, sol.created) AS created, COALESCE(r.createdby, sol.createdby) AS createdby, 'N' AS ob_selected, NULL AS m_reservation_stock_id, stock.m_warehouse_id, stock.m_locator_id, stock.m_attributesetinstance_id, stock.c_orderline_id, stock.availableqty, COALESCE(stock.resqty, 0) AS reservedinothers, NULL AS quantity, NULL AS releasedqty, 'N' AS isallocated, sol.c_orderline_id AS sales_orderline_id, r.m_reservation_id, COALESCE(r.quantity, sol.qtyordered) AS resqty FROM (SELECT ol.c_orderline_id, ol.m_product_id, ol.qtyordered, ol.qtydelivered, COALESCE(ol.m_attributesetinstance_id, '0') AS m_attributesetinstance_id, ol.c_uom_id, ol.ad_org_id, ol.ad_client_id, ol.updated, ol.updatedby, ol.created, ol.createdby FROM c_orderline ol JOIN c_order o ON o.c_order_id = ol.c_order_id AND o.issotrx = 'Y' AND o.docstatus = 'CO' WHERE ol.qtyordered <> ol.qtydelivered) sol FULL JOIN m_reservation r ON r.c_orderline_id = sol.c_orderline_id JOIN (SELECT sd.m_product_id, sd.c_uom_id, sd.m_locator_id, l.m_warehouse_id, COALESCE(sd.m_attributesetinstance_id, '0') AS m_attributesetinstance_id, sum(res.quantity) AS resqty, sd.qtyonhand AS availableqty, sd.m_storage_detail_id, NULL AS c_orderline_id, NULL AS ad_org_id FROM m_storage_detail sd JOIN m_locator l ON sd.m_locator_id = l.m_locator_id LEFT JOIN (SELECT rs.quantity - COALESCE(rs.releasedqty, 0) AS quantity, COALESCE(rs.m_attributesetinstance_id, '0') AS m_attributesetinstance_id, rs.m_locator_id, r.m_product_id, r.c_uom_id FROM m_reservation_stock rs JOIN m_reservation r ON rs.m_reservation_id = r.m_reservation_id WHERE r.res_status NOT IN ('CL', 'DR')) res ON res.m_product_id = sd.m_product_id AND res.c_uom_id = sd.c_uom_id AND res.m_attributesetinstance_id = COALESCE(sd.m_attributesetinstance_id, '0') AND res.m_locator_id = sd.m_locator_id WHERE sd.qtyonhand > 0 AND sd.m_product_uom_id IS NULL GROUP BY sd.m_product_id, sd.c_uom_id, sd.m_locator_id, l.m_warehouse_id, COALESCE(sd.m_attributesetinstance_id, '0'), sd.qtyonhand, sd.m_storage_detail_id UNION ALL SELECT ol.m_product_id, ol.c_uom_id, NULL AS m_locator_id, NULL AS m_warehouse_id, COALESCE(ol.m_attributesetinstance_id, '0') AS m_attributesetinstance_id, COALESCE(res.qty, 0) AS resqty, ol.qtyordered AS availableqty, NULL AS m_storage_detail_id, ol.c_orderline_id, ol.ad_org_id FROM c_orderline ol JOIN c_order o ON o.c_order_id = ol.c_order_id AND o.issotrx = 'N' AND o.docstatus = 'CO' LEFT JOIN m_matchpo mp ON mp.c_orderline_id = ol.c_orderline_id AND mp.m_inoutline_id IS NOT NULL LEFT JOIN (SELECT rs.c_orderline_id, sum(rs.quantity - COALESCE(rs.releasedqty, 0)) AS qty FROM m_reservation_stock rs JOIN m_reservation r ON rs.m_reservation_id = r.m_reservation_id WHERE (r.res_status NOT IN ('CL', 'DR')) AND rs.c_orderline_id IS NOT NULL GROUP BY rs.c_orderline_id) res ON res.c_orderline_id = ol.c_orderline_id GROUP BY ol.c_orderline_id, ol.m_product_id, ol.c_uom_id, COALESCE(ol.m_attributesetinstance_id, '0'), ol.qtyordered, COALESCE(res.qty, 0), ol.ad_org_id HAVING ol.qtyordered <> COALESCE(sum(mp.qty), 0)) stock ON stock.m_storage_detail_id IS NOT NULL AND stock.m_product_id = COALESCE(r.m_product_id, sol.m_product_id) AND stock.c_uom_id = COALESCE(r.c_uom_id, sol.c_uom_id) AND stock.m_attributesetinstance_id = COALESCE(r.m_attributesetinstance_id, stock.m_attributesetinstance_id) AND stock.m_locator_id = COALESCE(r.m_locator_id, stock.m_locator_id) AND stock.m_warehouse_id = COALESCE(r.m_warehouse_id, stock.m_warehouse_id) AND (stock.m_warehouse_id IN (SELECT ow.m_warehouse_id FROM ad_org_warehouse ow WHERE ow.ad_org_id = COALESCE(r.ad_org_id, sol.ad_org_id))) AND NOT (EXISTS (SELECT 1 FROM m_reservation_stock rs WHERE rs.m_reservation_id = r.m_reservation_id AND rs.m_locator_id = stock.m_locator_id AND COALESCE(rs.m_attributesetinstance_id, '0') = stock.m_attributesetinstance_id)) AND stock.m_warehouse_id = COALESCE(r.m_warehouse_id, stock.m_warehouse_id) AND stock.m_locator_id = COALESCE(r.m_locator_id, stock.m_locator_id) AND stock.m_attributesetinstance_id = COALESCE(r.m_attributesetinstance_id, stock.m_attributesetinstance_id) OR stock.c_orderline_id IS NOT NULL AND NOT (EXISTS (SELECT 1 FROM m_reservation_stock rs WHERE rs.m_reservation_id = r.m_reservation_id AND rs.c_orderline_id = stock.c_orderline_id)) AND ad_get_org_le_bu(stock.ad_org_id, 'LE') = ad_get_org_le_bu(COALESCE(r.ad_org_id, sol.ad_org_id), 'LE') AND stock.m_product_id = COALESCE(r.m_product_id, sol.m_product_id) AND stock.c_uom_id = COALESCE(r.c_uom_id, sol.c_uom_id) AND stock.m_attributesetinstance_id = COALESCE(r.m_attributesetinstance_id, sol.m_attributesetinstance_id) UNION ALL SELECT rs.m_reservation_stock_id AS m_reservation_pick_edit_id, COALESCE(rs.ad_client_id, sol.ad_client_id) AS ad_client_id, COALESCE(r.ad_org_id, sol.ad_org_id) AS ad_org_id, 'Y' AS isactive, COALESCE(r.updated, sol.updated) AS updated, COALESCE(r.updatedby, sol.updatedby) AS updatedby, COALESCE(r.created, sol.created) AS created, COALESCE(r.createdby, sol.createdby) AS createdby, 'Y' AS ob_selected, rs.m_reservation_stock_id, stock.m_warehouse_id, stock.m_locator_id, stock.m_attributesetinstance_id, stock.c_orderline_id, stock.availableqty, COALESCE(stock.resqty, 0) - CASE r.res_status WHEN 'DR' THEN 0 ELSE COALESCE(rs.quantity, 0) END AS reservedinothers, rs.quantity, rs.releasedqty, rs.isallocated, sol.c_orderline_id AS sales_orderline_id, r.m_reservation_id, COALESCE(r.quantity, sol.qtyordered) AS resqty FROM m_reservation r JOIN m_reservation_stock rs ON r.m_reservation_id = rs.m_reservation_id LEFT JOIN (SELECT ol.c_orderline_id, ol.m_product_id, ol.qtyordered, ol.qtydelivered, COALESCE(ol.m_attributesetinstance_id, '0') AS m_attributesetinstance_id, ol.c_uom_id, ol.ad_org_id, ol.ad_client_id, ol.updated, ol.updatedby, ol.created, ol.createdby FROM c_orderline ol JOIN c_order o ON o.c_order_id = ol.c_order_id AND o.issotrx = 'Y' AND o.docstatus = 'CO' WHERE ol.qtyordered <> ol.qtydelivered) sol ON r.c_orderline_id = sol.c_orderline_id LEFT JOIN (SELECT sd.m_product_id, sd.c_uom_id, sd.m_locator_id, l.m_warehouse_id, COALESCE(sd.m_attributesetinstance_id, '0') AS m_attributesetinstance_id, sum(res.quantity) AS resqty, sd.qtyonhand AS availableqty, sd.m_storage_detail_id, NULL AS c_orderline_id FROM m_storage_detail sd JOIN m_locator l ON sd.m_locator_id = l.m_locator_id LEFT JOIN (SELECT rs.quantity - COALESCE(rs.releasedqty, 0) AS quantity, COALESCE(rs.m_attributesetinstance_id, '0') AS m_attributesetinstance_id, rs.m_locator_id, r.m_product_id, r.c_uom_id FROM m_reservation_stock rs JOIN m_reservation r ON rs.m_reservation_id = r.m_reservation_id WHERE r.res_status NOT IN ('CL', 'DR')) res ON res.m_product_id = sd.m_product_id AND res.c_uom_id = sd.c_uom_id AND res.m_attributesetinstance_id = COALESCE(sd.m_attributesetinstance_id, '0') AND res.m_locator_id = sd.m_locator_id WHERE sd.qtyonhand > 0 AND sd.m_product_uom_id IS NULL GROUP BY sd.m_product_id, sd.c_uom_id, sd.m_locator_id, l.m_warehouse_id, COALESCE(sd.m_attributesetinstance_id, '0'), sd.qtyonhand, sd.m_storage_detail_id UNION ALL SELECT ol.m_product_id, ol.c_uom_id, NULL AS m_locator_id, NULL AS m_warehouse_id, COALESCE(ol.m_attributesetinstance_id, '0') AS m_attributesetinstance_id, COALESCE(res.qty, 0) AS resqty, ol.qtyordered AS availableqty, NULL AS m_storage_detail_id, ol.c_orderline_id FROM c_orderline ol JOIN c_order o ON o.c_order_id = ol.c_order_id AND o.issotrx = 'N' AND o.docstatus = 'CO' LEFT JOIN m_matchpo mp ON mp.c_orderline_id = ol.c_orderline_id AND mp.m_inoutline_id IS NOT NULL LEFT JOIN (SELECT rs.c_orderline_id, sum(rs.quantity) AS qty FROM m_reservation_stock rs JOIN m_reservation r ON rs.m_reservation_id = r.m_reservation_id WHERE (r.res_status NOT IN ('CL', 'DR')) AND rs.c_orderline_id IS NOT NULL GROUP BY rs.c_orderline_id) res ON res.c_orderline_id = ol.c_orderline_id GROUP BY ol.c_orderline_id, ol.m_product_id, ol.c_uom_id, COALESCE(ol.m_attributesetinstance_id, '0'), ol.qtyordered, COALESCE(res.qty, 0) HAVING ol.qtyordered <> COALESCE(sum(mp.qty), 0)) stock ON stock.m_storage_detail_id IS NOT NULL AND stock.m_product_id = COALESCE(r.m_product_id, sol.m_product_id) AND stock.c_uom_id = COALESCE(r.c_uom_id, sol.c_uom_id) AND stock.m_locator_id = rs.m_locator_id AND stock.m_attributesetinstance_id = COALESCE(rs.m_attributesetinstance_id, '0') OR stock.c_orderline_id IS NOT NULL AND rs.c_orderline_id = stock.c_orderline_id]]></view> + <view name="M_RESERVATION_PICK_EDIT"><![CDATA[SELECT COALESCE(r.m_reservation_id, sol.c_orderline_id) || COALESCE(stock.m_storage_detail_id, stock.c_orderline_id) AS m_reservation_pick_edit_id, COALESCE(r.ad_client_id, sol.ad_client_id) AS ad_client_id, COALESCE(r.ad_org_id, sol.ad_org_id) AS ad_org_id, 'Y' AS isactive, COALESCE(r.updated, sol.updated) AS updated, COALESCE(r.updatedby, sol.updatedby) AS updatedby, COALESCE(r.created, sol.created) AS created, COALESCE(r.createdby, sol.createdby) AS createdby, 'N' AS ob_selected, NULL AS m_reservation_stock_id, stock.m_warehouse_id, stock.m_locator_id, stock.m_attributesetinstance_id, stock.c_orderline_id, stock.availableqty, COALESCE(stock.resqty, 0) AS reservedinothers, NULL AS quantity, NULL AS releasedqty, 'N' AS isallocated, sol.c_orderline_id AS sales_orderline_id, r.m_reservation_id, COALESCE(r.quantity, sol.qtyordered) AS resqty FROM (SELECT ol.c_orderline_id, ol.m_product_id, ol.qtyordered, ol.qtydelivered, COALESCE(ol.m_attributesetinstance_id, '0') AS m_attributesetinstance_id, ol.c_uom_id, ol.ad_org_id, ol.ad_client_id, ol.updated, ol.updatedby, ol.created, ol.createdby FROM c_orderline ol JOIN c_order o ON o.c_order_id = ol.c_order_id AND o.issotrx = 'Y' AND o.docstatus = 'CO' WHERE ol.qtyordered <> ol.qtydelivered) sol FULL JOIN m_reservation r ON r.c_orderline_id = sol.c_orderline_id JOIN (SELECT sd.m_product_id, sd.c_uom_id, sd.m_locator_id, l.m_warehouse_id, COALESCE(sd.m_attributesetinstance_id, '0') AS m_attributesetinstance_id, sum(res.quantity) AS resqty, sd.qtyonhand AS availableqty, sd.m_storage_detail_id, NULL AS c_orderline_id, NULL AS ad_org_id FROM m_storage_detail sd JOIN m_locator l ON sd.m_locator_id = l.m_locator_id LEFT JOIN (SELECT rs.quantity - COALESCE(rs.releasedqty, 0) AS quantity, COALESCE(rs.m_attributesetinstance_id, '0') AS m_attributesetinstance_id, rs.m_locator_id, r_1.m_product_id, r_1.c_uom_id FROM m_reservation_stock rs JOIN m_reservation r_1 ON rs.m_reservation_id = r_1.m_reservation_id WHERE r_1.res_status NOT IN ('CL', 'DR')) res ON res.m_product_id = sd.m_product_id AND res.c_uom_id = sd.c_uom_id AND res.m_attributesetinstance_id = COALESCE(sd.m_attributesetinstance_id, '0') AND res.m_locator_id = sd.m_locator_id WHERE sd.qtyonhand > 0 AND sd.m_product_uom_id IS NULL GROUP BY sd.m_product_id, sd.c_uom_id, sd.m_locator_id, l.m_warehouse_id, COALESCE(sd.m_attributesetinstance_id, '0'), sd.qtyonhand, sd.m_storage_detail_id UNION ALL SELECT ol.m_product_id, ol.c_uom_id, NULL AS m_locator_id, NULL AS m_warehouse_id, COALESCE(ol.m_attributesetinstance_id, '0') AS m_attributesetinstance_id, COALESCE(res.qty, 0) AS resqty, ol.qtyordered AS availableqty, NULL AS m_storage_detail_id, ol.c_orderline_id, ol.ad_org_id FROM c_orderline ol JOIN c_order o ON o.c_order_id = ol.c_order_id AND o.issotrx = 'N' AND o.docstatus = 'CO' LEFT JOIN m_matchpo mp ON mp.c_orderline_id = ol.c_orderline_id AND mp.m_inoutline_id IS NOT NULL LEFT JOIN (SELECT rs.c_orderline_id, sum(rs.quantity - COALESCE(rs.releasedqty, 0)) AS qty FROM m_reservation_stock rs JOIN m_reservation r_1 ON rs.m_reservation_id = r_1.m_reservation_id WHERE (r_1.res_status NOT IN ('CL', 'DR')) AND rs.c_orderline_id IS NOT NULL GROUP BY rs.c_orderline_id) res ON res.c_orderline_id = ol.c_orderline_id GROUP BY ol.c_orderline_id, ol.m_product_id, ol.c_uom_id, COALESCE(ol.m_attributesetinstance_id, '0'), ol.qtyordered, COALESCE(res.qty, 0), ol.ad_org_id HAVING ol.qtyordered <> COALESCE(sum(mp.qty), 0)) stock ON stock.m_storage_detail_id IS NOT NULL AND stock.m_product_id = COALESCE(r.m_product_id, sol.m_product_id) AND stock.c_uom_id = COALESCE(r.c_uom_id, sol.c_uom_id) AND stock.m_attributesetinstance_id = COALESCE(r.m_attributesetinstance_id, stock.m_attributesetinstance_id) AND stock.m_locator_id = COALESCE(r.m_locator_id, stock.m_locator_id) AND stock.m_warehouse_id = COALESCE(r.m_warehouse_id, stock.m_warehouse_id) AND (stock.m_warehouse_id IN (SELECT ow.m_warehouse_id FROM ad_org_warehouse ow WHERE ow.ad_org_id = COALESCE(r.ad_org_id, sol.ad_org_id))) AND NOT (EXISTS (SELECT 1 FROM m_reservation_stock rs WHERE rs.m_reservation_id = r.m_reservation_id AND rs.m_locator_id = stock.m_locator_id AND COALESCE(rs.m_attributesetinstance_id, '0') = stock.m_attributesetinstance_id)) AND stock.m_warehouse_id = COALESCE(r.m_warehouse_id, stock.m_warehouse_id) AND stock.m_locator_id = COALESCE(r.m_locator_id, stock.m_locator_id) AND stock.m_attributesetinstance_id = COALESCE(r.m_attributesetinstance_id, stock.m_attributesetinstance_id) OR stock.c_orderline_id IS NOT NULL AND NOT (EXISTS (SELECT 1 FROM m_reservation_stock rs WHERE rs.m_reservation_id = r.m_reservation_id AND rs.c_orderline_id = stock.c_orderline_id)) AND ad_get_org_le_bu(stock.ad_org_id, 'LE') = ad_get_org_le_bu(COALESCE(r.ad_org_id, sol.ad_org_id), 'LE') AND stock.m_product_id = COALESCE(r.m_product_id, sol.m_product_id) AND stock.c_uom_id = COALESCE(r.c_uom_id, sol.c_uom_id) AND stock.m_attributesetinstance_id = COALESCE(r.m_attributesetinstance_id, sol.m_attributesetinstance_id) UNION ALL SELECT rs.m_reservation_stock_id AS m_reservation_pick_edit_id, COALESCE(rs.ad_client_id, sol.ad_client_id) AS ad_client_id, COALESCE(r.ad_org_id, sol.ad_org_id) AS ad_org_id, 'Y' AS isactive, COALESCE(r.updated, sol.updated) AS updated, COALESCE(r.updatedby, sol.updatedby) AS updatedby, COALESCE(r.created, sol.created) AS created, COALESCE(r.createdby, sol.createdby) AS createdby, 'Y' AS ob_selected, rs.m_reservation_stock_id, stock.m_warehouse_id, stock.m_locator_id, stock.m_attributesetinstance_id, stock.c_orderline_id, stock.availableqty, COALESCE(stock.resqty, 0) - CASE r.res_status WHEN 'DR' THEN 0 ELSE COALESCE(rs.quantity, 0) END AS reservedinothers, rs.quantity, rs.releasedqty, rs.isallocated, sol.c_orderline_id AS sales_orderline_id, r.m_reservation_id, COALESCE(r.quantity, sol.qtyordered) AS resqty FROM m_reservation r JOIN m_reservation_stock rs ON r.m_reservation_id = rs.m_reservation_id LEFT JOIN (SELECT ol.c_orderline_id, ol.m_product_id, ol.qtyordered, ol.qtydelivered, COALESCE(ol.m_attributesetinstance_id, '0') AS m_attributesetinstance_id, ol.c_uom_id, ol.ad_org_id, ol.ad_client_id, ol.updated, ol.updatedby, ol.created, ol.createdby FROM c_orderline ol JOIN c_order o ON o.c_order_id = ol.c_order_id AND o.issotrx = 'Y' AND o.docstatus = 'CO' WHERE ol.qtyordered <> ol.qtydelivered) sol ON r.c_orderline_id = sol.c_orderline_id LEFT JOIN (SELECT sd.m_product_id, sd.c_uom_id, sd.m_locator_id, l.m_warehouse_id, COALESCE(sd.m_attributesetinstance_id, '0') AS m_attributesetinstance_id, sum(res.quantity) AS resqty, sd.qtyonhand AS availableqty, sd.m_storage_detail_id, NULL AS c_orderline_id FROM m_storage_detail sd JOIN m_locator l ON sd.m_locator_id = l.m_locator_id LEFT JOIN (SELECT rs_1.quantity - COALESCE(rs_1.releasedqty, 0) AS quantity, COALESCE(rs_1.m_attributesetinstance_id, '0') AS m_attributesetinstance_id, rs_1.m_locator_id, r_1.m_product_id, r_1.c_uom_id FROM m_reservation_stock rs_1 JOIN m_reservation r_1 ON rs_1.m_reservation_id = r_1.m_reservation_id WHERE r_1.res_status NOT IN ('CL', 'DR')) res ON res.m_product_id = sd.m_product_id AND res.c_uom_id = sd.c_uom_id AND res.m_attributesetinstance_id = COALESCE(sd.m_attributesetinstance_id, '0') AND res.m_locator_id = sd.m_locator_id WHERE sd.qtyonhand > 0 AND sd.m_product_uom_id IS NULL GROUP BY sd.m_product_id, sd.c_uom_id, sd.m_locator_id, l.m_warehouse_id, COALESCE(sd.m_attributesetinstance_id, '0'), sd.qtyonhand, sd.m_storage_detail_id UNION ALL SELECT ol.m_product_id, ol.c_uom_id, NULL AS m_locator_id, NULL AS m_warehouse_id, COALESCE(ol.m_attributesetinstance_id, '0') AS m_attributesetinstance_id, COALESCE(res.qty, 0) AS resqty, ol.qtyordered AS availableqty, NULL AS m_storage_detail_id, ol.c_orderline_id FROM c_orderline ol JOIN c_order o ON o.c_order_id = ol.c_order_id AND o.issotrx = 'N' AND o.docstatus = 'CO' LEFT JOIN m_matchpo mp ON mp.c_orderline_id = ol.c_orderline_id AND mp.m_inoutline_id IS NOT NULL LEFT JOIN (SELECT rs_1.c_orderline_id, sum(rs_1.quantity) AS qty FROM m_reservation_stock rs_1 JOIN m_reservation r_1 ON rs_1.m_reservation_id = r_1.m_reservation_id WHERE (r_1.res_status NOT IN ('CL', 'DR')) AND rs_1.c_orderline_id IS NOT NULL GROUP BY rs_1.c_orderline_id) res ON res.c_orderline_id = ol.c_orderline_id GROUP BY ol.c_orderline_id, ol.m_product_id, ol.c_uom_id, COALESCE(ol.m_attributesetinstance_id, '0'), ol.qtyordered, COALESCE(res.qty, 0) HAVING ol.qtyordered <> COALESCE(sum(mp.qty), 0)) stock ON stock.m_storage_detail_id IS NOT NULL AND stock.m_product_id = COALESCE(r.m_product_id, sol.m_product_id) AND stock.c_uom_id = COALESCE(r.c_uom_id, sol.c_uom_id) AND stock.m_locator_id = rs.m_locator_id AND stock.m_attributesetinstance_id = COALESCE(rs.m_attributesetinstance_id, '0') OR stock.c_orderline_id IS NOT NULL AND rs.c_orderline_id = stock.c_orderline_id]]></view> </database> | |||||||
Relationships [ Relation Graph ] [ Dependency Graph ] | |
Notes | |
(0061285) hgbot (developer) 2013-09-18 20:24 |
Repository: erp/devel/pi Changeset: ec5db1dfd22454dfce754a4cadb2c7af293505ab Author: Stefan Hühner <stefan.huehner <at> openbravo.com> Date: Wed Sep 18 20:23:48 2013 +0200 URL: http://code.openbravo.com/erp/devel/pi/rev/ec5db1dfd22454dfce754a4cadb2c7af293505ab [^] Fixed 24770: Fix inconsistency in view syntax for postgresql 9.3 When exporting those 3 views on postgres 9.3 they are exported slightly different as postgres avoids some duplicate (implicit) join table aliases by adding missing aliases to make those unique. Those are purely formal changes not doing any functional change. As after this change duplicate alias name in different subqueries of the views are avoided the resulting sql is even slightly easier to read. --- M modules/org.openbravo.advpaymentmngt/src-db/database/model/views/APRM_RECONCILIATION_V.xml M src-db/database/model/views/C_INVOICE_CANDIDATE_V.xml M src-db/database/model/views/M_RESERVATION_PICK_EDIT.xml --- |
(0061288) hudsonbot (developer) 2013-09-19 04:13 |
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/ccc2155870d8 [^] Maturity status: Test |
(0061290) dmiguelez (developer) 2013-09-19 10:40 |
Code Review + Testing Ok |
Issue History | |||
Date Modified | Username | Field | Change |
2013-09-14 09:06 | shuehner | New Issue | |
2013-09-14 09:06 | shuehner | Assigned To | => dmiguelez |
2013-09-14 09:06 | shuehner | Modules | => Core |
2013-09-14 09:06 | shuehner | Triggers an Emergency Pack | => No |
2013-09-14 09:07 | shuehner | File Added: 24770.diff | |
2013-09-18 20:22 | shuehner | Review Assigned To | => dmiguelez |
2013-09-18 20:22 | shuehner | Assigned To | dmiguelez => shuehner |
2013-09-18 20:24 | hgbot | Checkin | |
2013-09-18 20:24 | hgbot | Note Added: 0061285 | |
2013-09-18 20:24 | hgbot | Status | new => resolved |
2013-09-18 20:24 | hgbot | Resolution | open => fixed |
2013-09-18 20:24 | hgbot | Fixed in SCM revision | => http://code.openbravo.com/erp/devel/pi/rev/ec5db1dfd22454dfce754a4cadb2c7af293505ab [^] |
2013-09-19 04:13 | hudsonbot | Checkin | |
2013-09-19 04:13 | hudsonbot | Note Added: 0061288 | |
2013-09-19 10:40 | dmiguelez | Note Added: 0061290 | |
2013-09-19 10:40 | dmiguelez | Status | resolved => closed |
Copyright © 2000 - 2009 MantisBT Group |