Fixed 24770: Fix inconsistency in view syntax for postgresql 9.3
authorStefan Hühner <stefan.huehner@openbravo.com>
Wed, 18 Sep 2013 20:23:48 +0200
changeset 21161 ec5db1dfd224
parent 21160 f9db7952ee98
child 21162 dfc0124afa8b
child 21163 ccc2155870d8
child 22187 6b9decfdf476
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.
modules/org.openbravo.advpaymentmngt/src-db/database/model/views/APRM_RECONCILIATION_V.xml
src-db/database/model/views/C_INVOICE_CANDIDATE_V.xml
src-db/database/model/views/M_RESERVATION_PICK_EDIT.xml
--- a/modules/org.openbravo.advpaymentmngt/src-db/database/model/views/APRM_RECONCILIATION_V.xml	Wed Sep 18 18:53:03 2013 +0200
+++ b/modules/org.openbravo.advpaymentmngt/src-db/database/model/views/APRM_RECONCILIATION_V.xml	Wed Sep 18 20:23:48 2013 +0200
@@ -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>
--- a/src-db/database/model/views/C_INVOICE_CANDIDATE_V.xml	Wed Sep 18 18:53:03 2013 +0200
+++ b/src-db/database/model/views/C_INVOICE_CANDIDATE_V.xml	Wed Sep 18 20:23:48 2013 +0200
@@ -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>
--- a/src-db/database/model/views/M_RESERVATION_PICK_EDIT.xml	Wed Sep 18 18:53:03 2013 +0200
+++ b/src-db/database/model/views/M_RESERVATION_PICK_EDIT.xml	Wed Sep 18 20:23:48 2013 +0200
@@ -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>