details: /erp/devel/pi/rev/2e60729b9c13
changeset: 6544:2e60729b9c13
user: Adrián Romero <adrianromero <at> openbravo.com>
date: Tue Mar 02 12:23:55 2010 +0100
summary: Fixes issue 0012014: Error in Project Profitability
diffstat:
src-db/database/model/views/AD_REF_LIST_V.xml | 16 +-
src-db/database/model/views/AD_TABLE_POST_V.xml | 34 ++--
src-db/database/model/views/C_INVOICE_LINETAX_V.xml | 102 ++++++++--------
src-db/database/model/views/C_INVOICE_LINETAX_VT.xml | 110 +++++++++---------
src-db/database/model/views/C_ORDER_LINETAX_V.xml | 62 +++++-----
src-db/database/model/views/C_ORDER_LINETAX_VT.xml | 68 +++++-----
src-db/database/model/views/M_INOUT_LINE_V.xml | 18 +-
src-db/database/model/views/M_INOUT_LINE_VT.xml | 18 +-
src-db/database/sourcedata/AD_FIELD.xml | 44 +++---
9 files changed, 236 insertions(+), 236 deletions(-)
diffs (truncated from 569 to 300 lines):
diff -r fda935b731fe -r 2e60729b9c13
src-db/database/model/views/AD_REF_LIST_V.xml
--- a/src-db/database/model/views/AD_REF_LIST_V.xml Mon Mar 01 22:20:45
2010 +0100
+++ b/src-db/database/model/views/AD_REF_LIST_V.xml Tue Mar 02 12:23:55
2010 +0100
@@ -1,13 +1,13 @@
<?xml version="1.0"?>
<database name="VIEW AD_REF_LIST_V">
<view name="AD_REF_LIST_V"><![CDATA[SELECT ad_ref_list.ad_ref_list_id,
ad_ref_list.value, to_char(ad_ref_list.name) AS name, 'en_US' AS ad_language,
ad_ref_list.ad_reference_id
- FROM ad_ref_list
+ FROM ad_ref_list
UNION
- SELECT v1.ad_ref_list_id, v1.value, COALESCE(v1.name_trl, v2.name) AS name,
v1.ad_language, v1.ad_reference_id
- FROM ( SELECT ad_ref_list.ad_ref_list_id, ad_ref_list.value,
to_char(ad_ref_list_trl.name) AS name_trl, '' AS name,
ad_ref_list_trl.ad_language, ad_ref_list.ad_reference_id
- FROM ad_ref_list, ad_ref_list_trl
- WHERE ad_ref_list.ad_ref_list_id = ad_ref_list_trl.ad_ref_list_id)
v1, ( SELECT ad_ref_list.ad_ref_list_id, ad_ref_list.value,
to_char(ad_ref_list.name) AS name, ad_language.ad_language,
ad_ref_list.ad_reference_id
- FROM ad_ref_list, ad_language
- WHERE ad_language.issystemlanguage = 'Y') v2
- WHERE v1.ad_ref_list_id = v2.ad_ref_list_id AND v1.ad_reference_id =
v2.ad_reference_id]]></view>
+ SELECT v1.ad_ref_list_id, v1.value, COALESCE(v1.name_trl, v2.name) AS
name, v1.ad_language, v1.ad_reference_id
+ FROM ( SELECT ad_ref_list.ad_ref_list_id, ad_ref_list.value,
to_char(ad_ref_list_trl.name) AS name_trl, '' AS name,
ad_ref_list_trl.ad_language, ad_ref_list.ad_reference_id
+ FROM ad_ref_list, ad_ref_list_trl
+ WHERE ad_ref_list.ad_ref_list_id =
ad_ref_list_trl.ad_ref_list_id) v1, ( SELECT ad_ref_list.ad_ref_list_id,
ad_ref_list.value, to_char(ad_ref_list.name) AS name, ad_language.ad_language,
ad_ref_list.ad_reference_id
+ FROM ad_ref_list, ad_language
+ WHERE ad_language.issystemlanguage = 'Y') v2
+ WHERE v1.ad_ref_list_id = v2.ad_ref_list_id AND v1.ad_reference_id =
v2.ad_reference_id]]></view>
</database>
diff -r fda935b731fe -r 2e60729b9c13
src-db/database/model/views/AD_TABLE_POST_V.xml
--- a/src-db/database/model/views/AD_TABLE_POST_V.xml Mon Mar 01 22:20:45
2010 +0100
+++ b/src-db/database/model/views/AD_TABLE_POST_V.xml Tue Mar 02 12:23:55
2010 +0100
@@ -1,26 +1,26 @@
<?xml version="1.0"?>
<database name="VIEW AD_TABLE_POST_V">
<view name="AD_TABLE_POST_V"><![CDATA[SELECT ad_table.ad_table_id,
(COALESCE(to_char(w2.name), '') ||
- CASE
- WHEN w2.name IS NULL THEN ''
- ELSE ' - '
- END) || w1.name AS name, w1.ad_language, ad_table.ad_client_id,
ad_table.ad_org_id, ad_table.isactive, ad_table.created, ad_table.createdby,
ad_table.updated, ad_table.updatedby
- FROM ad_table
- LEFT JOIN ad_window_trl w1 ON ad_table.ad_window_id = w1.ad_window_id
+ CASE
+ WHEN w2.name IS NULL THEN ''
+ ELSE ' - '
+ END) || w1.name AS name, w1.ad_language,
ad_table.ad_client_id, ad_table.ad_org_id, ad_table.isactive, ad_table.created,
ad_table.createdby, ad_table.updated, ad_table.updatedby
+ FROM ad_table
+ LEFT JOIN ad_window_trl w1 ON ad_table.ad_window_id = w1.ad_window_id
LEFT JOIN ad_window_trl w2 ON ad_table.po_window_id = w2.ad_window_id
WHERE (EXISTS ( SELECT c.ad_column_id, c.ad_client_id, c.ad_org_id,
c.isactive, c.created, c.updated, c.createdby, c.updatedby, c.name,
c.description, c.help, c.columnname, c.ad_table_id, c.ad_reference_id,
c.ad_reference_value_id, c.ad_val_rule_id, c.fieldlength, c.defaultvalue,
c.iskey, c.isparent, c.ismandatory, c.isupdateable, c.readonlylogic,
c.isidentifier, c.seqno, c.istranslated, c.isencrypted, c.callout, c.vformat,
c.valuemin, c.valuemax, c.isselectioncolumn, c.ad_element_id, c.ad_process_id,
c.issessionattr, c.issecondarykey, c.isdesencryptable, c.ad_callout_id,
c.developmentstatus
- FROM ad_column c
- WHERE ad_table.ad_table_id = c.ad_table_id AND c.columnname = 'Posted')) AND
w1.ad_language = COALESCE(w2.ad_language, w1.ad_language) AND ad_table.isactive
= 'Y'
+ FROM ad_column c
+ WHERE ad_table.ad_table_id = c.ad_table_id AND c.columnname =
'Posted')) AND w1.ad_language = COALESCE(w2.ad_language, w1.ad_language) AND
ad_table.isactive = 'Y'
UNION
- SELECT ad_table.ad_table_id, (COALESCE(to_char(w2.name), '') ||
- CASE
- WHEN w2.name IS NULL THEN ''
- ELSE ' - '
- END) || w1.name AS name, 'en_US' AS ad_language,
ad_table.ad_client_id, ad_table.ad_org_id, ad_table.isactive, ad_table.created,
ad_table.createdby, ad_table.updated, ad_table.updatedby
- FROM ad_table
- LEFT JOIN ad_window w1 ON ad_table.ad_window_id = w1.ad_window_id
+ SELECT ad_table.ad_table_id, (COALESCE(to_char(w2.name), '') ||
+ CASE
+ WHEN w2.name IS NULL THEN ''
+ ELSE ' - '
+ END) || w1.name AS name, 'en_US' AS ad_language,
ad_table.ad_client_id, ad_table.ad_org_id, ad_table.isactive, ad_table.created,
ad_table.createdby, ad_table.updated, ad_table.updatedby
+ FROM ad_table
+ LEFT JOIN ad_window w1 ON ad_table.ad_window_id = w1.ad_window_id
LEFT JOIN ad_window w2 ON ad_table.po_window_id = w2.ad_window_id
WHERE (EXISTS ( SELECT c.ad_column_id, c.ad_client_id, c.ad_org_id,
c.isactive, c.created, c.updated, c.createdby, c.updatedby, c.name,
c.description, c.help, c.columnname, c.ad_table_id, c.ad_reference_id,
c.ad_reference_value_id, c.ad_val_rule_id, c.fieldlength, c.defaultvalue,
c.iskey, c.isparent, c.ismandatory, c.isupdateable, c.readonlylogic,
c.isidentifier, c.seqno, c.istranslated, c.isencrypted, c.callout, c.vformat,
c.valuemin, c.valuemax, c.isselectioncolumn, c.ad_element_id, c.ad_process_id,
c.issessionattr, c.issecondarykey, c.isdesencryptable, c.ad_callout_id,
c.developmentstatus
- FROM ad_column c
- WHERE ad_table.ad_table_id = c.ad_table_id AND c.columnname = 'Posted')) AND
ad_table.isactive = 'Y']]></view>
+ FROM ad_column c
+ WHERE ad_table.ad_table_id = c.ad_table_id AND c.columnname =
'Posted')) AND ad_table.isactive = 'Y']]></view>
</database>
diff -r fda935b731fe -r 2e60729b9c13
src-db/database/model/views/C_INVOICE_LINETAX_V.xml
--- a/src-db/database/model/views/C_INVOICE_LINETAX_V.xml Mon Mar 01
22:20:45 2010 +0100
+++ b/src-db/database/model/views/C_INVOICE_LINETAX_V.xml Tue Mar 02
12:23:55 2010 +0100
@@ -1,55 +1,55 @@
<?xml version="1.0"?>
<database name="VIEW C_INVOICE_LINETAX_V">
- <view name="C_INVOICE_LINETAX_V"><![CDATA[((( SELECT il.ad_client_id,
il.ad_org_id, il.isactive, il.created, il.createdby, il.updated, il.updatedby,
'en_US' AS ad_language, il.c_invoice_id, il.c_invoiceline_id, il.c_tax_id,
il.line,
- CASE
- WHEN il.qtyinvoiced <> 0 OR il.m_product_id IS NOT NULL THEN
il.qtyinvoiced
- ELSE NULL
- END AS qtyinvoiced,
- CASE
- WHEN il.qtyinvoiced <> 0 OR il.m_product_id IS NOT NULL THEN
uom.uomsymbol
- ELSE NULL
- END AS uomsymbol, COALESCE(p.name, il.description) AS name,
- CASE
- WHEN p.name IS NOT NULL THEN il.description
- ELSE NULL
- END AS description, p.documentnote, p.upc, p.sku, p.value AS
productvalue, ra.description AS resourcedescription,
- CASE
- WHEN i.isdiscountprinted = 'Y' AND (il.pricelist <> 0 OR
il.m_product_id IS NOT NULL) THEN il.pricelist
- ELSE NULL
- END AS pricelist,
- CASE
- WHEN i.isdiscountprinted = 'Y' AND il.pricelist > il.priceactual
THEN (il.pricelist - il.priceactual) / il.pricelist * 100
- ELSE NULL
- END AS discount,
- CASE
- WHEN il.priceactual <> 0 OR il.m_product_id IS NOT NULL THEN
il.priceactual
- ELSE NULL
- END AS priceactual,
- CASE
- WHEN il.linenetamt <> 0 OR il.m_product_id IS NOT NULL THEN
il.linenetamt
- ELSE NULL
- END AS linenetamt
- FROM c_invoiceline il
- JOIN c_uom uom ON il.c_uom_id = uom.c_uom_id
- JOIN c_invoice i ON il.c_invoice_id = i.c_invoice_id
- LEFT JOIN m_product p ON il.m_product_id = p.m_product_id
- LEFT JOIN s_resourceassignment ra ON il.s_resourceassignment_id =
ra.s_resourceassignment_id
+ <view name="C_INVOICE_LINETAX_V"><![CDATA[( ( (
SELECT il.ad_client_id, il.ad_org_id, il.isactive, il.created, il.createdby,
il.updated, il.updatedby, 'en_US' AS ad_language, il.c_invoice_id,
il.c_invoiceline_id, il.c_tax_id, il.line,
+ CASE
+ WHEN il.qtyinvoiced <> 0 OR
il.m_product_id IS NOT NULL THEN il.qtyinvoiced
+ ELSE NULL
+ END AS qtyinvoiced,
+ CASE
+ WHEN il.qtyinvoiced <> 0 OR
il.m_product_id IS NOT NULL THEN uom.uomsymbol
+ ELSE NULL
+ END AS uomsymbol, COALESCE(p.name,
il.description) AS name,
+ CASE
+ WHEN p.name IS NOT NULL THEN
il.description
+ ELSE NULL
+ END AS description, p.documentnote,
p.upc, p.sku, p.value AS productvalue, ra.description AS resourcedescription,
+ CASE
+ WHEN i.isdiscountprinted = 'Y' AND
(il.pricelist <> 0 OR il.m_product_id IS NOT NULL) THEN il.pricelist
+ ELSE NULL
+ END AS pricelist,
+ CASE
+ WHEN i.isdiscountprinted = 'Y' AND
il.pricelist > il.priceactual THEN (il.pricelist - il.priceactual) /
il.pricelist * 100
+ ELSE NULL
+ END AS discount,
+ CASE
+ WHEN il.priceactual <> 0 OR
il.m_product_id IS NOT NULL THEN il.priceactual
+ ELSE NULL
+ END AS priceactual,
+ CASE
+ WHEN il.linenetamt <> 0 OR
il.m_product_id IS NOT NULL THEN il.linenetamt
+ ELSE NULL
+ END AS linenetamt
+ FROM c_invoiceline il
+ JOIN c_uom uom ON il.c_uom_id = uom.c_uom_id
+ JOIN c_invoice i ON il.c_invoice_id = i.c_invoice_id
+ LEFT JOIN m_product p ON il.m_product_id = p.m_product_id
+ LEFT JOIN s_resourceassignment ra ON il.s_resourceassignment_id
= ra.s_resourceassignment_id
+ UNION
+ SELECT il.ad_client_id, il.ad_org_id,
il.isactive, il.created, il.createdby, il.updated, il.updatedby, 'en_US' AS
ad_language, il.c_invoice_id, il.c_invoiceline_id, il.c_tax_id, il.line +
b.line / 100 AS line, il.qtyinvoiced * b.bomqty AS qtyinvoiced, uom.uomsymbol,
p.name, b.description, p.documentnote, p.upc, p.sku, p.value AS productvalue,
NULL AS resourcedescription, NULL AS pricelist, NULL AS discount, NULL AS
priceactual, NULL AS linenetamt
+ FROM m_product_bom b
+ JOIN c_invoiceline il ON b.m_product_id =
il.m_product_id
+ JOIN m_product bp ON bp.m_product_id =
il.m_product_id AND bp.isbom = 'Y' AND bp.isverified = 'Y' AND
bp.isinvoiceprintdetails = 'Y'
+ JOIN m_product p ON b.m_productbom_id = p.m_product_id
+ JOIN c_uom uom ON p.c_uom_id = uom.c_uom_id)
+ UNION
+ SELECT il.ad_client_id, il.ad_org_id, il.isactive,
il.created, il.createdby, il.updated, il.updatedby, 'en_US' AS ad_language,
il.c_invoice_id, il.c_invoiceline_id, il.c_tax_id, il.line, NULL AS
qtyinvoiced, NULL AS uomsymbol, il.description AS name, NULL AS description,
NULL AS documentnote, NULL AS upc, NULL AS sku, NULL AS productvalue, NULL AS
resourcedescription, NULL AS pricelist, NULL AS discount, NULL AS priceactual,
NULL AS linenetamt
+ FROM c_invoiceline il
+ WHERE il.c_uom_id IS NULL)
+ UNION
+ SELECT c_invoice.ad_client_id, c_invoice.ad_org_id,
c_invoice.isactive, c_invoice.created, c_invoice.createdby, c_invoice.updated,
c_invoice.updatedby, 'en_US' AS ad_language, c_invoice.c_invoice_id, NULL AS
c_invoiceline_id, NULL AS c_tax_id, 9998 AS line, NULL AS qtyinvoiced, NULL AS
uomsymbol, NULL AS name, NULL AS description, NULL AS documentnote, NULL AS
upc, NULL AS sku, NULL AS productvalue, NULL AS resourcedescription, NULL AS
pricelist, NULL AS discount, NULL AS priceactual, NULL AS linenetamt
+ FROM c_invoice)
UNION
- SELECT il.ad_client_id, il.ad_org_id, il.isactive, il.created, il.createdby,
il.updated, il.updatedby, 'en_US' AS ad_language, il.c_invoice_id,
il.c_invoiceline_id, il.c_tax_id, il.line + b.line / 100 AS line,
il.qtyinvoiced * b.bomqty AS qtyinvoiced, uom.uomsymbol, p.name, b.description,
p.documentnote, p.upc, p.sku, p.value AS productvalue, NULL AS
resourcedescription, NULL AS pricelist, NULL AS discount, NULL AS priceactual,
NULL AS linenetamt
- FROM m_product_bom b
- JOIN c_invoiceline il ON b.m_product_id = il.m_product_id
- JOIN m_product bp ON bp.m_product_id = il.m_product_id AND bp.isbom = 'Y'
AND bp.isverified = 'Y' AND bp.isinvoiceprintdetails = 'Y'
- JOIN m_product p ON b.m_productbom_id = p.m_product_id
- JOIN c_uom uom ON p.c_uom_id = uom.c_uom_id)
-UNION
- SELECT il.ad_client_id, il.ad_org_id, il.isactive, il.created, il.createdby,
il.updated, il.updatedby, 'en_US' AS ad_language, il.c_invoice_id,
il.c_invoiceline_id, il.c_tax_id, il.line, NULL AS qtyinvoiced, NULL AS
uomsymbol, il.description AS name, NULL AS description, NULL AS documentnote,
NULL AS upc, NULL AS sku, NULL AS productvalue, NULL AS resourcedescription,
NULL AS pricelist, NULL AS discount, NULL AS priceactual, NULL AS linenetamt
- FROM c_invoiceline il
- WHERE il.c_uom_id IS NULL)
-UNION
- SELECT c_invoice.ad_client_id, c_invoice.ad_org_id, c_invoice.isactive,
c_invoice.created, c_invoice.createdby, c_invoice.updated, c_invoice.updatedby,
'en_US' AS ad_language, c_invoice.c_invoice_id, NULL AS c_invoiceline_id, NULL
AS c_tax_id, 9998 AS line, NULL AS qtyinvoiced, NULL AS uomsymbol, NULL AS
name, NULL AS description, NULL AS documentnote, NULL AS upc, NULL AS sku, NULL
AS productvalue, NULL AS resourcedescription, NULL AS pricelist, NULL AS
discount, NULL AS priceactual, NULL AS linenetamt
- FROM c_invoice)
-UNION
- SELECT it.ad_client_id, it.ad_org_id, it.isactive, it.created, it.createdby,
it.updated, it.updatedby, 'en_US' AS ad_language, it.c_invoice_id, NULL AS
c_invoiceline_id, it.c_tax_id, 9999 AS line, NULL AS qtyinvoiced, NULL AS
uomsymbol, t.name, NULL AS description, NULL AS documentnote, NULL AS upc, NULL
AS sku, NULL AS productvalue, NULL AS resourcedescription, NULL AS pricelist,
NULL AS discount, it.taxbaseamt AS priceactual, it.taxamt AS linenetamt
- FROM c_invoicetax it
- JOIN c_tax t ON it.c_tax_id = t.c_tax_id]]></view>
+ SELECT it.ad_client_id, it.ad_org_id, it.isactive, it.created,
it.createdby, it.updated, it.updatedby, 'en_US' AS ad_language,
it.c_invoice_id, NULL AS c_invoiceline_id, it.c_tax_id, 9999 AS line, NULL AS
qtyinvoiced, NULL AS uomsymbol, t.name, NULL AS description, NULL AS
documentnote, NULL AS upc, NULL AS sku, NULL AS productvalue, NULL AS
resourcedescription, NULL AS pricelist, NULL AS discount, it.taxbaseamt AS
priceactual, it.taxamt AS linenetamt
+ FROM c_invoicetax it
+ JOIN c_tax t ON it.c_tax_id = t.c_tax_id]]></view>
</database>
diff -r fda935b731fe -r 2e60729b9c13
src-db/database/model/views/C_INVOICE_LINETAX_VT.xml
--- a/src-db/database/model/views/C_INVOICE_LINETAX_VT.xml Mon Mar 01
22:20:45 2010 +0100
+++ b/src-db/database/model/views/C_INVOICE_LINETAX_VT.xml Tue Mar 02
12:23:55 2010 +0100
@@ -1,59 +1,59 @@
<?xml version="1.0"?>
<database name="VIEW C_INVOICE_LINETAX_VT">
- <view name="C_INVOICE_LINETAX_VT"><![CDATA[((( SELECT il.ad_client_id,
il.ad_org_id, il.isactive, il.created, il.createdby, il.updated, il.updatedby,
uom.ad_language, il.c_invoice_id, il.c_invoiceline_id, il.c_tax_id, il.line,
- CASE
- WHEN il.qtyinvoiced <> 0 OR il.m_product_id IS NOT NULL THEN
il.qtyinvoiced
- ELSE NULL
- END AS qtyinvoiced,
- CASE
- WHEN il.qtyinvoiced <> 0 OR il.m_product_id IS NOT NULL THEN
uom.uomsymbol
- ELSE NULL
- END AS uomsymbol, COALESCE(COALESCE(pt.name, p.name), il.description)
AS name,
- CASE
- WHEN COALESCE(pt.name, p.name) IS NOT NULL THEN il.description
- ELSE NULL
- END AS description, COALESCE(pt.documentnote, p.documentnote) AS
documentnote, p.upc, p.sku, p.value AS productvalue, ra.description AS
resourcedescription,
- CASE
- WHEN i.isdiscountprinted = 'Y' AND (il.pricelist <> 0 OR
il.m_product_id IS NOT NULL) THEN il.pricelist
- ELSE NULL
- END AS pricelist,
- CASE
- WHEN i.isdiscountprinted = 'Y' AND il.pricelist > il.priceactual
THEN (il.pricelist - il.priceactual) / il.pricelist * 100
- ELSE NULL
- END AS discount,
- CASE
- WHEN il.priceactual <> 0 OR il.m_product_id IS NOT NULL THEN
il.priceactual
- ELSE NULL
- END AS priceactual,
- CASE
- WHEN il.linenetamt <> 0 OR il.m_product_id IS NOT NULL THEN
il.linenetamt
- ELSE NULL
- END AS linenetamt
- FROM c_invoiceline il
- JOIN c_uom_trl uom ON il.c_uom_id = uom.c_uom_id
- JOIN c_invoice i ON il.c_invoice_id = i.c_invoice_id
- LEFT JOIN m_product p ON il.m_product_id = p.m_product_id
- LEFT JOIN m_product_trl pt ON il.m_product_id = pt.m_product_id AND
uom.ad_language = pt.ad_language
- LEFT JOIN s_resourceassignment ra ON il.s_resourceassignment_id =
ra.s_resourceassignment_id
+ <view name="C_INVOICE_LINETAX_VT"><![CDATA[( ( (
SELECT il.ad_client_id, il.ad_org_id, il.isactive, il.created, il.createdby,
il.updated, il.updatedby, uom.ad_language, il.c_invoice_id,
il.c_invoiceline_id, il.c_tax_id, il.line,
+ CASE
+ WHEN il.qtyinvoiced <> 0 OR
il.m_product_id IS NOT NULL THEN il.qtyinvoiced
+ ELSE NULL
+ END AS qtyinvoiced,
+ CASE
+ WHEN il.qtyinvoiced <> 0 OR
il.m_product_id IS NOT NULL THEN uom.uomsymbol
+ ELSE NULL
+ END AS uomsymbol,
COALESCE(COALESCE(pt.name, p.name), il.description) AS name,
+ CASE
+ WHEN COALESCE(pt.name, p.name) IS
NOT NULL THEN il.description
+ ELSE NULL
+ END AS description,
COALESCE(pt.documentnote, p.documentnote) AS documentnote, p.upc, p.sku,
p.value AS productvalue, ra.description AS resourcedescription,
+ CASE
+ WHEN i.isdiscountprinted = 'Y' AND
(il.pricelist <> 0 OR il.m_product_id IS NOT NULL) THEN il.pricelist
+ ELSE NULL
+ END AS pricelist,
+ CASE
+ WHEN i.isdiscountprinted = 'Y' AND
il.pricelist > il.priceactual THEN (il.pricelist - il.priceactual) /
il.pricelist * 100
+ ELSE NULL
+ END AS discount,
+ CASE
+ WHEN il.priceactual <> 0 OR
il.m_product_id IS NOT NULL THEN il.priceactual
+ ELSE NULL
+ END AS priceactual,
+ CASE
+ WHEN il.linenetamt <> 0 OR
il.m_product_id IS NOT NULL THEN il.linenetamt
+ ELSE NULL
+ END AS linenetamt
+ FROM c_invoiceline il
+ JOIN c_uom_trl uom ON il.c_uom_id = uom.c_uom_id
+ JOIN c_invoice i ON il.c_invoice_id = i.c_invoice_id
+ LEFT JOIN m_product p ON il.m_product_id = p.m_product_id
+ LEFT JOIN m_product_trl pt ON il.m_product_id = pt.m_product_id
AND uom.ad_language = pt.ad_language
+ LEFT JOIN s_resourceassignment ra ON il.s_resourceassignment_id =
ra.s_resourceassignment_id
+ UNION
+ SELECT il.ad_client_id, il.ad_org_id,
il.isactive, il.created, il.createdby, il.updated, il.updatedby,
uom.ad_language, il.c_invoice_id, il.c_invoiceline_id, il.c_tax_id, il.line +
b.line / 100 AS line, il.qtyinvoiced * b.bomqty AS qtyinvoiced, uom.uomsymbol,
COALESCE(pt.name, p.name) AS name, b.description, COALESCE(pt.documentnote,
p.documentnote) AS documentnote, p.upc, p.sku, p.value AS productvalue, NULL AS
resourcedescription, NULL AS pricelist, NULL AS discount, NULL AS priceactual,
NULL AS linenetamt
+ FROM m_product_bom b
+ JOIN c_invoiceline il ON b.m_product_id =
il.m_product_id
+ JOIN m_product bp ON bp.m_product_id =
il.m_product_id AND bp.isbom = 'Y' AND bp.isverified = 'Y' AND
bp.isinvoiceprintdetails = 'Y'
+ JOIN m_product p ON b.m_productbom_id = p.m_product_id
+ JOIN c_uom_trl uom ON p.c_uom_id = uom.c_uom_id
+ JOIN m_product_trl pt ON b.m_productbom_id = pt.m_product_id AND
uom.ad_language = pt.ad_language)
+ UNION
+ SELECT il.ad_client_id, il.ad_org_id, il.isactive,
il.created, il.createdby, il.updated, il.updatedby, l.ad_language,
il.c_invoice_id, il.c_invoiceline_id, il.c_tax_id, il.line, NULL AS
qtyinvoiced, NULL AS uomsymbol, il.description AS name, NULL AS description,
NULL AS documentnote, NULL AS upc, NULL AS sku, NULL AS productvalue, NULL AS
resourcedescription, NULL AS pricelist, NULL AS discount, NULL AS priceactual,
NULL AS linenetamt
+ FROM c_invoiceline il, ad_language l
+ WHERE il.c_uom_id IS NULL AND l.isbaselanguage = 'N'
AND l.issystemlanguage = 'Y')
+ UNION
+ SELECT i.ad_client_id, i.ad_org_id, i.isactive, i.created,
i.createdby, i.updated, i.updatedby, l.ad_language, i.c_invoice_id, NULL AS
c_invoiceline_id, NULL AS c_tax_id, 9998 AS line, NULL AS qtyinvoiced, NULL AS
uomsymbol, NULL AS name, NULL AS description, NULL AS documentnote, NULL AS
upc, NULL AS sku, NULL AS productvalue, NULL AS resourcedescription, NULL AS
pricelist, NULL AS discount, NULL AS priceactual, NULL AS linenetamt
+ FROM c_invoice i, ad_language l
+ WHERE l.isbaselanguage = 'N' AND l.issystemlanguage = 'Y')
UNION
- SELECT il.ad_client_id, il.ad_org_id, il.isactive, il.created, il.createdby,
il.updated, il.updatedby, uom.ad_language, il.c_invoice_id,
il.c_invoiceline_id, il.c_tax_id, il.line + b.line / 100 AS line,
il.qtyinvoiced * b.bomqty AS qtyinvoiced, uom.uomsymbol, COALESCE(pt.name,
p.name) AS name, b.description, COALESCE(pt.documentnote, p.documentnote) AS
documentnote, p.upc, p.sku, p.value AS productvalue, NULL AS
resourcedescription, NULL AS pricelist, NULL AS discount, NULL AS priceactual,
NULL AS linenetamt
- FROM m_product_bom b
- JOIN c_invoiceline il ON b.m_product_id = il.m_product_id
- JOIN m_product bp ON bp.m_product_id = il.m_product_id AND bp.isbom = 'Y'
AND bp.isverified = 'Y' AND bp.isinvoiceprintdetails = 'Y'
- JOIN m_product p ON b.m_productbom_id = p.m_product_id
- JOIN c_uom_trl uom ON p.c_uom_id = uom.c_uom_id
- JOIN m_product_trl pt ON b.m_productbom_id = pt.m_product_id AND
uom.ad_language = pt.ad_language)
-UNION
- SELECT il.ad_client_id, il.ad_org_id, il.isactive, il.created, il.createdby,
il.updated, il.updatedby, l.ad_language, il.c_invoice_id, il.c_invoiceline_id,
il.c_tax_id, il.line, NULL AS qtyinvoiced, NULL AS uomsymbol, il.description AS
name, NULL AS description, NULL AS documentnote, NULL AS upc, NULL AS sku, NULL
AS productvalue, NULL AS resourcedescription, NULL AS pricelist, NULL AS
discount, NULL AS priceactual, NULL AS linenetamt
- FROM c_invoiceline il, ad_language l
- WHERE il.c_uom_id IS NULL AND l.isbaselanguage = 'N' AND l.issystemlanguage
= 'Y')
-UNION
- SELECT i.ad_client_id, i.ad_org_id, i.isactive, i.created, i.createdby,
i.updated, i.updatedby, l.ad_language, i.c_invoice_id, NULL AS
c_invoiceline_id, NULL AS c_tax_id, 9998 AS line, NULL AS qtyinvoiced, NULL AS
uomsymbol, NULL AS name, NULL AS description, NULL AS documentnote, NULL AS
upc, NULL AS sku, NULL AS productvalue, NULL AS resourcedescription, NULL AS
pricelist, NULL AS discount, NULL AS priceactual, NULL AS linenetamt
- FROM c_invoice i, ad_language l
- WHERE l.isbaselanguage = 'N' AND l.issystemlanguage = 'Y')
-UNION
- SELECT it.ad_client_id, it.ad_org_id, it.isactive, it.created, it.createdby,
it.updated, it.updatedby, l.ad_language, it.c_invoice_id, NULL AS
c_invoiceline_id, it.c_tax_id, 9999 AS line, NULL AS qtyinvoiced, NULL AS
uomsymbol, t.name, NULL AS description, NULL AS documentnote, NULL AS upc, NULL
AS sku, NULL AS productvalue, NULL AS resourcedescription, NULL AS pricelist,
NULL AS discount, it.taxbaseamt AS priceactual, it.taxamt AS linenetamt
- FROM c_invoicetax it
- JOIN c_tax t ON it.c_tax_id = t.c_tax_id, ad_language l
- WHERE l.isbaselanguage = 'N' AND l.issystemlanguage = 'Y']]></view>
+ SELECT it.ad_client_id, it.ad_org_id, it.isactive, it.created,
it.createdby, it.updated, it.updatedby, l.ad_language, it.c_invoice_id, NULL AS
c_invoiceline_id, it.c_tax_id, 9999 AS line, NULL AS qtyinvoiced, NULL AS
uomsymbol, t.name, NULL AS description, NULL AS documentnote, NULL AS upc, NULL
AS sku, NULL AS productvalue, NULL AS resourcedescription, NULL AS pricelist,
NULL AS discount, it.taxbaseamt AS priceactual, it.taxamt AS linenetamt
+ FROM c_invoicetax it
+ JOIN c_tax t ON it.c_tax_id = t.c_tax_id, ad_language l
+ WHERE l.isbaselanguage = 'N' AND l.issystemlanguage = 'Y']]></view>
</database>
------------------------------------------------------------------------------
Download Intel® Parallel Studio Eval
Try the new software tools for yourself. Speed compiling, find bugs
proactively, and fine-tune applications for parallel performance.
See why Intel Parallel Studio got high marks during beta.
http://p.sf.net/sfu/intel-sw-dev
_______________________________________________
Openbravo-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/openbravo-commits
