テーブル情報
システム名 http://www.as-link.com/  作成者 秦 松甫 
サブシステム名 ERPlus@iDempiere3.1_daily  作成日 2016/3/7 
スキーマ名 adempiere  更新日  
論理テーブル名   RDBMS  PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit 9.4.5 
物理テーブル名 c_invoice_linetax_v     
備考


カラム情報
No 論理名 物理名 データ型 Not Null デフォルト 備考
1 ad_client_id numeric(10)  
2 ad_org_id numeric(10)  
3 isactive character(1)  
4 created timestamp without time zone  
5 createdby numeric(10)  
6 updated timestamp without time zone  
7 updatedby numeric(10)  
8 ad_language text  
9 c_invoice_id numeric(10)  
10 c_invoiceline_id numeric  
11 c_tax_id numeric  
12 taxamt numeric  
13 linetotalamt numeric  
14 taxindicator character varying  
15 line numeric  
16 m_product_id numeric  
17 qtyinvoiced numeric  
18 qtyentered numeric  
19 uomsymbol character varying  
20 name character varying  
21 description character varying  
22 documentnote character varying  
23 upc character varying  
24 sku character varying  
25 productvalue character varying  
26 resourcedescription character varying  
27 pricelist numeric  
28 priceenteredlist numeric  
29 discount numeric  
30 priceactual numeric  
31 priceentered numeric  
32 linenetamt numeric  
33 m_attributesetinstance_id numeric  
34 m_attributeset_id numeric  
35 serno character varying  
36 lot character varying  
37 m_lot_id numeric  
38 guaranteedate timestamp without time zone  
39 productdescription character varying  
40 imageurl character varying  
41 c_campaign_id numeric  
42 c_project_id numeric  
43 c_activity_id numeric  
44 c_projectphase_id numeric  
45 c_projecttask_id numeric  
46 c_invoiceline_ad_orgtrx_id numeric  
47 a_processed character  
48 c_charge_id numeric  
49 c_orderline_id numeric  
50 c_uom_id numeric  
51 isdescription character  
52 c_invoiceline_isprinted character  
53 m_inoutline_id numeric  
54 m_rmaline_id numeric  
55 pricelimit numeric  
56 c_invoiceline_processed character  
57 ref_invoiceline_id numeric  
58 rramt numeric  
59 rrstartdate timestamp without time zone  
60 s_resourceassignment_id numeric  
61 c_invoiceline_user1_id numeric  
62 c_invoiceline_user2_id numeric  
63 c_uom_ad_org_id numeric  
64 costingprecision numeric  
65 c_uom_description character varying  
66 c_uom_isactive character  
67 c_uom_isdefault character  
68 c_uom_name character varying  
69 stdprecision numeric  
70 uomtype character varying  
71 x12de355 character varying  
72 c_invoice_ad_org_id numeric  
73 c_invoice_ad_orgtrx_id numeric  
74 c_invoice_ad_user_id numeric  
75 c_invoice_c_activity_id numeric  
76 c_invoice_c_bpartner_id numeric  
77 c_bpartner_location_id numeric  
78 c_invoice_c_campaign_id numeric  
79 c_invoice_c_charge_id numeric  
80 c_conversiontype_id numeric  
81 c_currency_id numeric  
82 c_doctype_id numeric  
83 c_doctypetarget_id numeric  
84 c_dunninglevel_id numeric  
85 c_invoice_chargeamt numeric  
86 c_order_id numeric  
87 c_payment_id numeric  
88 c_paymentterm_id numeric  
89 c_invoice_c_project_id numeric  
90 c_invoice_created timestamp without time zone  
91 c_invoice_createdby numeric  
92 dateacct timestamp without time zone  
93 dateinvoiced timestamp without time zone  
94 dateordered timestamp without time zone  
95 dateprinted timestamp without time zone  
96 c_invoice_description character varying  
97 docaction character  
98 docstatus character  
99 documentno character varying  
100 dunninggrace date  
101 generateto character  
102 grandtotal numeric  
103 invoicecollectiontype character  
104 c_invoice_isactive character  
105 isapproved character  
106 isdiscountprinted character  
107 isindispute character  
108 ispaid character  
109 ispayschedulevalid character  
110 c_invoice_isprinted character  
111 c_invoice_isselfservice character  
112 issotrx character  
113 c_invoice_istaxincluded character  
114 istransferred character  
115 m_pricelist_id numeric  
116 m_rma_id numeric  
117 paymentrule character  
118 poreference character varying  
119 posted character  
120 processedon numeric  
121 processing character  
122 ref_invoice_id numeric  
123 reversal_id numeric  
124 salesrep_id numeric  
125 sendemail character  
126 totallines numeric  
127 c_invoice_updated timestamp without time zone  
128 c_invoice_updatedby numeric  
129 c_invoice_user1_id numeric  
130 c_invoice_user2_id numeric  
131 c_tax_ad_org_id numeric  
132 ad_rule_id numeric  
133 c_country_id numeric  
134 c_region_id numeric  
135 c_tax_c_taxcategory_id numeric  
136 c_tax_description character varying  
137 c_tax_isactive character  
138 c_tax_isdefault character  
139 isdocumentlevel character  
140 issalestax character  
141 c_tax_issummary character  
142 istaxexempt character  
143 c_tax_name character varying  
144 parent_tax_id numeric  
145 rate numeric  
146 requirestaxcertificate character  
147 sopotype character  
148 to_country_id numeric  
149 to_region_id numeric  
150 validfrom timestamp without time zone  
151 m_product_ad_org_id numeric  
152 classification character varying  
153 m_product_copyfrom character  
154 m_product_created timestamp without time zone  
155 m_product_createdby numeric  
156 c_revenuerecognition_id numeric  
157 c_subscriptiontype_id numeric  
158 m_product_c_taxcategory_id numeric  
159 descriptionurl character varying  
160 discontinued character  
161 discontinuedat timestamp without time zone  
162 group1 character varying  
163 group2 character varying  
164 guaranteedays numeric  
165 guaranteedaysmin numeric  
166 help character varying  
167 m_product_isactive character  
168 isbom character  
169 isdropship character  
170 isexcludeautodelivery character  
171 isinvoiceprintdetails character  
172 ispicklistprintdetails character  
173 ispurchased character  
174 m_product_isselfservice character  
175 issold character  
176 isstocked character  
177 m_product_issummary character  
178 isverified character  
179 iswebstorefeatured character  
180 lowlevel numeric  
181 m_product_m_attributeset_id numeric  
182 m_product_m_asi_id numeric  
183 m_freightcategory_id numeric  
184 m_locator_id numeric  
185 m_product_m_prod_category_id numeric  
186 m_product_processing character  
187 producttype character  
188 r_mailtext_id numeric  
189 m_product_salesrep_id numeric  
190 s_expensetype_id numeric  
191 shelfdepth numeric  
192 shelfheight numeric  
193 shelfwidth numeric  
194 m_product_s_resource_id numeric  
195 unitsperpack numeric  
196 unitsperpallet numeric  
197 m_product_updated timestamp without time zone  
198 m_product_updatedby numeric  
199 versionno character varying  
200 volume numeric  
201 weight numeric  
202 c_charge_ad_org_id numeric  
203 c_charge_c_bpartner_id numeric  
204 c_chargetype_id numeric  
205 c_charge_chargeamt numeric  
206 c_charge_c_taxcategory_id numeric  
207 c_charge_description character varying  
208 c_charge_isactive character  
209 issamecurrency character  
210 issametax character  
211 c_charge_istaxincluded character  
212 c_bp_product_ad_org_id numeric  
213 c_bp_product_c_bpartner_id numeric  
214 c_bp_product_created timestamp without time zone  
215 c_bp_product_createdby numeric  
216 c_bp_product_description character varying  
217 c_bp_product_isactive character  
218 ismanufacturer character  
219 manufacturer character varying  
220 qualityrating numeric  
221 shelflifemindays numeric  
222 shelflifeminpct numeric  
223 c_bp_product_updated timestamp without time zone  
224 c_bp_product_updatedby numeric  
225 vendorcategory character varying  
226 s_rassignment_ad_org_id numeric  
227 assigndatefrom timestamp without time zone  
228 assigndateto timestamp without time zone  
229 s_rassignment_created timestamp without time zone  
230 s_rassignment_createby numeric  
231 s_rassignment_isactive character  
232 isconfirmed character  
233 s_rassignment_name character varying  
234 s_rassignment_qty numeric  
235 s_rassignment_s_resource_id numeric  
236 s_rassignment_updated timestamp without time zone  
237 s_rassignment_updatedby numeric  
238 m_asi_ad_org_id numeric  
239 m_asi_created timestamp without time zone  
240 m_asi_createdby numeric  
241 m_asi_description character varying  
242 m_asi_isactive character  
243 m_asi_updated timestamp without time zone  
244 m_asi_updatedby numeric  


ソース
ソース
 SELECT il.ad_client_id,
il.ad_org_id,
il.isactive,
il.created,
il.createdby,
il.updated,
il.updatedby,
'en_US'::text AS ad_language,
il.c_invoice_id,
il.c_invoiceline_id,
il.c_tax_id,
il.taxamt,
il.linetotalamt,
t.taxindicator,
il.line,
p.m_product_id,
CASE
WHEN ((il.qtyinvoiced <> (0)::numeric) OR (il.m_product_id IS NOT NULL)) THEN il.qtyinvoiced
ELSE NULL::numeric
END AS qtyinvoiced,
CASE
WHEN ((il.qtyentered <> (0)::numeric) OR (il.m_product_id IS NOT NULL)) THEN il.qtyentered
ELSE NULL::numeric
END AS qtyentered,
CASE
WHEN ((il.qtyentered <> (0)::numeric) OR (il.m_product_id IS NOT NULL)) THEN uom.uomsymbol
ELSE NULL::character varying
END AS uomsymbol,
COALESCE(c.name, (((p.name)::text || (COALESCE(productattribute(il.m_attributesetinstance_id), ''::character varying))::text))::character varying, il.description) AS name,
CASE
WHEN (COALESCE(c.name, p.name) IS NOT NULL) THEN il.description
ELSE NULL::character varying
END AS description,
p.documentnote,
p.upc,
p.sku,
COALESCE(pp.vendorproductno, p.value) AS productvalue,
ra.description AS resourcedescription,
CASE
WHEN ((i.isdiscountprinted = 'Y'::bpchar) AND (il.pricelist <> (0)::numeric)) THEN il.pricelist
ELSE NULL::numeric
END AS pricelist,
CASE
WHEN (((i.isdiscountprinted = 'Y'::bpchar) AND (il.pricelist <> (0)::numeric)) AND (il.qtyentered <> (0)::numeric)) THEN ((il.pricelist * il.qtyinvoiced) / il.qtyentered)
ELSE NULL::numeric
END AS priceenteredlist,
CASE
WHEN (((i.isdiscountprinted = 'Y'::bpchar) AND (il.pricelist > il.priceactual)) AND (il.pricelist <> (0)::numeric)) THEN (((il.pricelist - il.priceactual) / il.pricelist) * (100)::numeric)
ELSE NULL::numeric
END AS discount,
CASE
WHEN ((il.priceactual <> (0)::numeric) OR (il.m_product_id IS NOT NULL)) THEN il.priceactual
ELSE NULL::numeric
END AS priceactual,
CASE
WHEN ((il.priceentered <> (0)::numeric) OR (il.m_product_id IS NOT NULL)) THEN il.priceentered
ELSE NULL::numeric
END AS priceentered,
CASE
WHEN ((il.linenetamt <> (0)::numeric) OR (il.m_product_id IS NOT NULL)) THEN il.linenetamt
ELSE NULL::numeric
END AS linenetamt,
il.m_attributesetinstance_id,
asi.m_attributeset_id,
asi.serno,
asi.lot,
asi.m_lot_id,
asi.guaranteedate,
p.description AS productdescription,
p.imageurl,
il.c_campaign_id,
il.c_project_id,
il.c_activity_id,
il.c_projectphase_id,
il.c_projecttask_id,
il.ad_orgtrx_id AS c_invoiceline_ad_orgtrx_id,
il.a_processed,
il.c_charge_id,
il.c_orderline_id,
il.c_uom_id,
il.isdescription,
il.isprinted AS c_invoiceline_isprinted,
il.m_inoutline_id,
il.m_rmaline_id,
il.pricelimit,
il.processed AS c_invoiceline_processed,
il.ref_invoiceline_id,
il.rramt,
il.rrstartdate,
il.s_resourceassignment_id,
il.user1_id AS c_invoiceline_user1_id,
il.user2_id AS c_invoiceline_user2_id,
uom.ad_org_id AS c_uom_ad_org_id,
uom.costingprecision,
uom.description AS c_uom_description,
uom.isactive AS c_uom_isactive,
uom.isdefault AS c_uom_isdefault,
uom.name AS c_uom_name,
uom.stdprecision,
uom.uomtype,
uom.x12de355,
i.ad_org_id AS c_invoice_ad_org_id,
i.ad_orgtrx_id AS c_invoice_ad_orgtrx_id,
i.ad_user_id AS c_invoice_ad_user_id,
i.c_activity_id AS c_invoice_c_activity_id,
i.c_bpartner_id AS c_invoice_c_bpartner_id,
i.c_bpartner_location_id,
i.c_campaign_id AS c_invoice_c_campaign_id,
i.c_charge_id AS c_invoice_c_charge_id,
i.c_conversiontype_id,
i.c_currency_id,
i.c_doctype_id,
i.c_doctypetarget_id,
i.c_dunninglevel_id,
i.chargeamt AS c_invoice_chargeamt,
i.c_order_id,
i.c_payment_id,
i.c_paymentterm_id,
i.c_project_id AS c_invoice_c_project_id,
i.created AS c_invoice_created,
i.createdby AS c_invoice_createdby,
i.dateacct,
i.dateinvoiced,
i.dateordered,
i.dateprinted,
i.description AS c_invoice_description,
i.docaction,
i.docstatus,
i.documentno,
i.dunninggrace,
i.generateto,
i.grandtotal,
i.invoicecollectiontype,
i.isactive AS c_invoice_isactive,
i.isapproved,
i.isdiscountprinted,
i.isindispute,
i.ispaid,
i.ispayschedulevalid,
i.isprinted AS c_invoice_isprinted,
i.isselfservice AS c_invoice_isselfservice,
i.issotrx,
i.istaxincluded AS c_invoice_istaxincluded,
i.istransferred,
i.m_pricelist_id,
i.m_rma_id,
i.paymentrule,
i.poreference,
i.posted,
i.processedon,
i.processing,
i.ref_invoice_id,
i.reversal_id,
i.salesrep_id,
i.sendemail,
i.totallines,
i.updated AS c_invoice_updated,
i.updatedby AS c_invoice_updatedby,
i.user1_id AS c_invoice_user1_id,
i.user2_id AS c_invoice_user2_id,
t.ad_org_id AS c_tax_ad_org_id,
t.ad_rule_id,
t.c_country_id,
t.c_region_id,
t.c_taxcategory_id AS c_tax_c_taxcategory_id,
t.description AS c_tax_description,
t.isactive AS c_tax_isactive,
t.isdefault AS c_tax_isdefault,
t.isdocumentlevel,
t.issalestax,
t.issummary AS c_tax_issummary,
t.istaxexempt,
t.name AS c_tax_name,
t.parent_tax_id,
t.rate,
t.requirestaxcertificate,
t.sopotype,
t.to_country_id,
t.to_region_id,
t.validfrom,
p.ad_org_id AS m_product_ad_org_id,
p.classification,
p.copyfrom AS m_product_copyfrom,
p.created AS m_product_created,
p.createdby AS m_product_createdby,
p.c_revenuerecognition_id,
p.c_subscriptiontype_id,
p.c_taxcategory_id AS m_product_c_taxcategory_id,
p.descriptionurl,
p.discontinued,
p.discontinuedat,
p.group1,
p.group2,
p.guaranteedays,
p.guaranteedaysmin,
p.help,
p.isactive AS m_product_isactive,
p.isbom,
p.isdropship,
p.isexcludeautodelivery,
p.isinvoiceprintdetails,
p.ispicklistprintdetails,
p.ispurchased,
p.isselfservice AS m_product_isselfservice,
p.issold,
p.isstocked,
p.issummary AS m_product_issummary,
p.isverified,
p.iswebstorefeatured,
p.lowlevel,
p.m_attributeset_id AS m_product_m_attributeset_id,
p.m_attributesetinstance_id AS m_product_m_asi_id,
p.m_freightcategory_id,
p.m_locator_id,
p.m_product_category_id AS m_product_m_prod_category_id,
p.processing AS m_product_processing,
p.producttype,
p.r_mailtext_id,
p.salesrep_id AS m_product_salesrep_id,
p.s_expensetype_id,
p.shelfdepth,
p.shelfheight,
p.shelfwidth,
p.s_resource_id AS m_product_s_resource_id,
p.unitsperpack,
p.unitsperpallet,
p.updated AS m_product_updated,
p.updatedby AS m_product_updatedby,
p.versionno,
p.volume,
p.weight,
c.ad_org_id AS c_charge_ad_org_id,
c.c_bpartner_id AS c_charge_c_bpartner_id,
c.c_chargetype_id,
c.chargeamt AS c_charge_chargeamt,
c.c_taxcategory_id AS c_charge_c_taxcategory_id,
c.description AS c_charge_description,
c.isactive AS c_charge_isactive,
c.issamecurrency,
c.issametax,
c.istaxincluded AS c_charge_istaxincluded,
pp.ad_org_id AS c_bp_product_ad_org_id,
pp.c_bpartner_id AS c_bp_product_c_bpartner_id,
pp.created AS c_bp_product_created,
pp.createdby AS c_bp_product_createdby,
pp.description AS c_bp_product_description,
pp.isactive AS c_bp_product_isactive,
pp.ismanufacturer,
pp.manufacturer,
pp.qualityrating,
pp.shelflifemindays,
pp.shelflifeminpct,
pp.updated AS c_bp_product_updated,
pp.updatedby AS c_bp_product_updatedby,
pp.vendorcategory,
ra.ad_org_id AS s_rassignment_ad_org_id,
ra.assigndatefrom,
ra.assigndateto,
ra.created AS s_rassignment_created,
ra.createdby AS s_rassignment_createby,
ra.isactive AS s_rassignment_isactive,
ra.isconfirmed,
ra.name AS s_rassignment_name,
ra.qty AS s_rassignment_qty,
ra.s_resource_id AS s_rassignment_s_resource_id,
ra.updated AS s_rassignment_updated,
ra.updatedby AS s_rassignment_updatedby,
asi.ad_org_id AS m_asi_ad_org_id,
asi.created AS m_asi_created,
asi.createdby AS m_asi_createdby,
asi.description AS m_asi_description,
asi.isactive AS m_asi_isactive,
asi.updated AS m_asi_updated,
asi.updatedby AS m_asi_updatedby
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 c_tax t ON ((il.c_tax_id = t.c_tax_id)))
LEFT JOIN m_product p ON ((il.m_product_id = p.m_product_id)))
LEFT JOIN c_charge c ON ((il.c_charge_id = c.c_charge_id)))
LEFT JOIN c_bpartner_product pp ON (((il.m_product_id = pp.m_product_id) AND (i.c_bpartner_id = pp.c_bpartner_id))))
LEFT JOIN s_resourceassignment ra ON ((il.s_resourceassignment_id = ra.s_resourceassignment_id)))
LEFT JOIN m_attributesetinstance asi ON ((il.m_attributesetinstance_id = asi.m_attributesetinstance_id)))
UNION
SELECT il.ad_client_id,
il.ad_org_id,
il.isactive,
il.created,
il.createdby,
il.updated,
il.updatedby,
'en_US'::text AS ad_language,
il.c_invoice_id,
il.c_invoiceline_id,
il.c_tax_id,
il.taxamt,
il.linetotalamt,
t.taxindicator,
(il.line + (bl.line / (100)::numeric)) AS line,
p.m_product_id,
CASE
WHEN (bl.isqtypercentage = 'N'::bpchar) THEN (il.qtyinvoiced * bl.qtybom)
ELSE (il.qtyinvoiced * (bl.qtybatch / (100)::numeric))
END AS qtyinvoiced,
CASE
WHEN (bl.isqtypercentage = 'N'::bpchar) THEN (il.qtyentered * bl.qtybom)
ELSE (il.qtyentered * (bl.qtybatch / (100)::numeric))
END AS qtyentered,
uom.uomsymbol,
p.name,
b.description,
p.documentnote,
p.upc,
p.sku,
p.value AS productvalue,
NULL::character varying AS resourcedescription,
NULL::numeric AS pricelist,
NULL::numeric AS priceenteredlist,
NULL::numeric AS discount,
NULL::numeric AS priceactual,
NULL::numeric AS priceentered,
NULL::numeric AS linenetamt,
il.m_attributesetinstance_id,
asi.m_attributeset_id,
asi.serno,
asi.lot,
asi.m_lot_id,
asi.guaranteedate,
p.description AS productdescription,
p.imageurl,
il.c_campaign_id,
il.c_project_id,
il.c_activity_id,
il.c_projectphase_id,
il.c_projecttask_id,
il.ad_orgtrx_id AS c_invoiceline_ad_orgtrx_id,
il.a_processed,
il.c_charge_id,
il.c_orderline_id,
il.c_uom_id,
il.isdescription,
il.isprinted AS c_invoiceline_isprinted,
il.m_inoutline_id,
il.m_rmaline_id,
il.pricelimit,
il.processed AS c_invoiceline_processed,
il.ref_invoiceline_id,
il.rramt,
il.rrstartdate,
il.s_resourceassignment_id,
il.user1_id AS c_invoiceline_user1_id,
il.user2_id AS c_invoiceline_user2_id,
uom.ad_org_id AS c_uom_ad_org_id,
uom.costingprecision,
uom.description AS c_uom_description,
uom.isactive AS c_uom_isactive,
uom.isdefault AS c_uom_isdefault,
uom.name AS c_uom_name,
uom.stdprecision,
uom.uomtype,
uom.x12de355,
NULL::numeric AS c_invoice_ad_org_id,
NULL::numeric AS c_invoice_ad_orgtrx_id,
NULL::numeric AS c_invoice_ad_user_id,
NULL::numeric AS c_invoice_c_activity_id,
NULL::numeric AS c_invoice_c_bpartner_id,
NULL::numeric AS c_bpartner_location_id,
NULL::numeric AS c_invoice_c_campaign_id,
NULL::numeric AS c_invoice_c_charge_id,
NULL::numeric AS c_conversiontype_id,
NULL::numeric AS c_currency_id,
NULL::numeric AS c_doctype_id,
NULL::numeric AS c_doctypetarget_id,
NULL::numeric AS c_dunninglevel_id,
NULL::numeric AS c_invoice_chargeamt,
NULL::numeric AS c_order_id,
NULL::numeric AS c_payment_id,
NULL::numeric AS c_paymentterm_id,
NULL::numeric AS c_invoice_c_project_id,
NULL::timestamp without time zone AS c_invoice_created,
NULL::numeric AS c_invoice_createdby,
NULL::timestamp without time zone AS dateacct,
NULL::timestamp without time zone AS dateinvoiced,
NULL::timestamp without time zone AS dateordered,
NULL::timestamp without time zone AS dateprinted,
NULL::character varying AS c_invoice_description,
NULL::bpchar AS docaction,
NULL::bpchar AS docstatus,
NULL::character varying AS documentno,
NULL::date AS dunninggrace,
NULL::bpchar AS generateto,
NULL::numeric AS grandtotal,
NULL::bpchar AS invoicecollectiontype,
NULL::bpchar AS c_invoice_isactive,
NULL::bpchar AS isapproved,
NULL::bpchar AS isdiscountprinted,
NULL::bpchar AS isindispute,
NULL::bpchar AS ispaid,
NULL::bpchar AS ispayschedulevalid,
NULL::bpchar AS c_invoice_isprinted,
NULL::bpchar AS c_invoice_isselfservice,
NULL::bpchar AS issotrx,
NULL::bpchar AS c_invoice_istaxincluded,
NULL::bpchar AS istransferred,
NULL::numeric AS m_pricelist_id,
NULL::numeric AS m_rma_id,
NULL::bpchar AS paymentrule,
NULL::character varying AS poreference,
NULL::bpchar AS posted,
NULL::numeric AS processedon,
NULL::bpchar AS processing,
NULL::numeric AS ref_invoice_id,
NULL::numeric AS reversal_id,
NULL::numeric AS salesrep_id,
NULL::bpchar AS sendemail,
NULL::numeric AS totallines,
NULL::timestamp without time zone AS c_invoice_updated,
NULL::numeric AS c_invoice_updatedby,
NULL::numeric AS c_invoice_user1_id,
NULL::numeric AS c_invoice_user2_id,
t.ad_org_id AS c_tax_ad_org_id,
t.ad_rule_id,
t.c_country_id,
t.c_region_id,
t.c_taxcategory_id AS c_tax_c_taxcategory_id,
t.description AS c_tax_description,
t.isactive AS c_tax_isactive,
t.isdefault AS c_tax_isdefault,
t.isdocumentlevel,
t.issalestax,
t.issummary AS c_tax_issummary,
t.istaxexempt,
t.name AS c_tax_name,
t.parent_tax_id,
t.rate,
t.requirestaxcertificate,
t.sopotype,
t.to_country_id,
t.to_region_id,
t.validfrom,
p.ad_org_id AS m_product_ad_org_id,
p.classification,
p.copyfrom AS m_product_copyfrom,
p.created AS m_product_created,
p.createdby AS m_product_createdby,
p.c_revenuerecognition_id,
p.c_subscriptiontype_id,
p.c_taxcategory_id AS m_product_c_taxcategory_id,
p.descriptionurl,
p.discontinued,
p.discontinuedat,
p.group1,
p.group2,
p.guaranteedays,
p.guaranteedaysmin,
p.help,
p.isactive AS m_product_isactive,
p.isbom,
p.isdropship,
p.isexcludeautodelivery,
p.isinvoiceprintdetails,
p.ispicklistprintdetails,
p.ispurchased,
p.isselfservice AS m_product_isselfservice,
p.issold,
p.isstocked,
p.issummary AS m_product_issummary,
p.isverified,
p.iswebstorefeatured,
p.lowlevel,
p.m_attributeset_id AS m_product_m_attributeset_id,
p.m_attributesetinstance_id AS m_product_m_asi_id,
p.m_freightcategory_id,
p.m_locator_id,
p.m_product_category_id AS m_product_m_prod_category_id,
p.processing AS m_product_processing,
p.producttype,
p.r_mailtext_id,
p.salesrep_id AS m_product_salesrep_id,
p.s_expensetype_id,
p.shelfdepth,
p.shelfheight,
p.shelfwidth,
p.s_resource_id AS m_product_s_resource_id,
p.unitsperpack,
p.unitsperpallet,
p.updated AS m_product_updated,
p.updatedby AS m_product_updatedby,
p.versionno,
p.volume,
p.weight,
NULL::numeric AS c_charge_ad_org_id,
NULL::numeric AS c_charge_c_bpartner_id,
NULL::numeric AS c_chargetype_id,
NULL::numeric AS c_charge_chargeamt,
NULL::numeric AS c_charge_c_taxcategory_id,
NULL::character varying AS c_charge_description,
NULL::bpchar AS c_charge_isactive,
NULL::bpchar AS issamecurrency,
NULL::bpchar AS issametax,
NULL::bpchar AS c_charge_istaxincluded,
NULL::numeric AS c_bp_product_ad_org_id,
NULL::numeric AS c_bp_product_c_bpartner_id,
NULL::timestamp without time zone AS c_bp_product_created,
NULL::numeric AS c_bp_product_createdby,
NULL::character varying AS c_bp_product_description,
NULL::bpchar AS c_bp_product_isactive,
NULL::bpchar AS ismanufacturer,
NULL::character varying AS manufacturer,
NULL::numeric AS qualityrating,
NULL::numeric AS shelflifemindays,
NULL::numeric AS shelflifeminpct,
NULL::timestamp without time zone AS c_bp_product_updated,
NULL::numeric AS c_bp_product_updatedby,
NULL::character varying AS vendorcategory,
NULL::numeric AS s_rassignment_ad_org_id,
NULL::timestamp without time zone AS assigndatefrom,
NULL::timestamp without time zone AS assigndateto,
NULL::timestamp without time zone AS s_rassignment_created,
NULL::numeric AS s_rassignment_createby,
NULL::bpchar AS s_rassignment_isactive,
NULL::bpchar AS isconfirmed,
NULL::character varying AS s_rassignment_name,
NULL::numeric AS s_rassignment_qty,
NULL::numeric AS s_rassignment_s_resource_id,
NULL::timestamp without time zone AS s_rassignment_updated,
NULL::numeric AS s_rassignment_updatedby,
asi.ad_org_id AS m_asi_ad_org_id,
asi.created AS m_asi_created,
asi.createdby AS m_asi_createdby,
asi.description AS m_asi_description,
asi.isactive AS m_asi_isactive,
asi.updated AS m_asi_updated,
asi.updatedby AS m_asi_updatedby
FROM (((((((pp_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'::bpchar)) AND (bp.isverified = 'Y'::bpchar)) AND (bp.isinvoiceprintdetails = 'Y'::bpchar))))
JOIN pp_product_bomline bl ON ((bl.pp_product_bom_id = b.pp_product_bom_id)))
JOIN m_product p ON ((bl.m_product_id = p.m_product_id)))
JOIN c_uom uom ON ((p.c_uom_id = uom.c_uom_id)))
LEFT JOIN c_tax t ON ((il.c_tax_id = t.c_tax_id)))
LEFT JOIN m_attributesetinstance asi ON ((il.m_attributesetinstance_id = asi.m_attributesetinstance_id)))
UNION
SELECT il.ad_client_id,
il.ad_org_id,
il.isactive,
il.created,
il.createdby,
il.updated,
il.updatedby,
'en_US'::text AS ad_language,
il.c_invoice_id,
il.c_invoiceline_id,
NULL::numeric AS c_tax_id,
NULL::numeric AS taxamt,
NULL::numeric AS linetotalamt,
NULL::character varying AS taxindicator,
il.line,
NULL::numeric AS m_product_id,
NULL::numeric AS qtyinvoiced,
NULL::numeric AS qtyentered,
NULL::character varying AS uomsymbol,
il.description AS name,
NULL::character varying AS description,
NULL::character varying AS documentnote,
NULL::character varying AS upc,
NULL::character varying AS sku,
NULL::character varying AS productvalue,
NULL::character varying AS resourcedescription,
NULL::numeric AS pricelist,
NULL::numeric AS priceenteredlist,
NULL::numeric AS discount,
NULL::numeric AS priceactual,
NULL::numeric AS priceentered,
NULL::numeric AS linenetamt,
NULL::numeric AS m_attributesetinstance_id,
NULL::numeric AS m_attributeset_id,
NULL::character varying AS serno,
NULL::character varying AS lot,
NULL::numeric AS m_lot_id,
NULL::timestamp without time zone AS guaranteedate,
NULL::character varying AS productdescription,
NULL::character varying AS imageurl,
NULL::numeric AS c_campaign_id,
NULL::numeric AS c_project_id,
NULL::numeric AS c_activity_id,
NULL::numeric AS c_projectphase_id,
NULL::numeric AS c_projecttask_id,
NULL::numeric AS c_invoiceline_ad_orgtrx_id,
NULL::bpchar AS a_processed,
NULL::numeric AS c_charge_id,
NULL::numeric AS c_orderline_id,
NULL::numeric AS c_uom_id,
NULL::bpchar AS isdescription,
NULL::bpchar AS c_invoiceline_isprinted,
NULL::numeric AS m_inoutline_id,
NULL::numeric AS m_rmaline_id,
NULL::numeric AS pricelimit,
NULL::bpchar AS c_invoiceline_processed,
NULL::numeric AS ref_invoiceline_id,
NULL::numeric AS rramt,
NULL::timestamp without time zone AS rrstartdate,
NULL::numeric AS s_resourceassignment_id,
NULL::numeric AS c_invoiceline_user1_id,
NULL::numeric AS c_invoiceline_user2_id,
NULL::numeric AS c_uom_ad_org_id,
NULL::numeric AS costingprecision,
NULL::character varying AS c_uom_description,
NULL::bpchar AS c_uom_isactive,
NULL::bpchar AS c_uom_isdefault,
NULL::character varying AS c_uom_name,
NULL::numeric AS stdprecision,
NULL::character varying AS uomtype,
NULL::character varying AS x12de355,
NULL::numeric AS c_invoice_ad_org_id,
NULL::numeric AS c_invoice_ad_orgtrx_id,
NULL::numeric AS c_invoice_ad_user_id,
NULL::numeric AS c_invoice_c_activity_id,
NULL::numeric AS c_invoice_c_bpartner_id,
NULL::numeric AS c_bpartner_location_id,
NULL::numeric AS c_invoice_c_campaign_id,
NULL::numeric AS c_invoice_c_charge_id,
NULL::numeric AS c_conversiontype_id,
NULL::numeric AS c_currency_id,
NULL::numeric AS c_doctype_id,
NULL::numeric AS c_doctypetarget_id,
NULL::numeric AS c_dunninglevel_id,
NULL::numeric AS c_invoice_chargeamt,
NULL::numeric AS c_order_id,
NULL::numeric AS c_payment_id,
NULL::numeric AS c_paymentterm_id,
NULL::numeric AS c_invoice_c_project_id,
NULL::timestamp without time zone AS c_invoice_created,
NULL::numeric AS c_invoice_createdby,
NULL::timestamp without time zone AS dateacct,
NULL::timestamp without time zone AS dateinvoiced,
NULL::timestamp without time zone AS dateordered,
NULL::timestamp without time zone AS dateprinted,
NULL::character varying AS c_invoice_description,
NULL::bpchar AS docaction,
NULL::bpchar AS docstatus,
NULL::character varying AS documentno,
NULL::date AS dunninggrace,
NULL::bpchar AS generateto,
NULL::numeric AS grandtotal,
NULL::bpchar AS invoicecollectiontype,
NULL::bpchar AS c_invoice_isactive,
NULL::bpchar AS isapproved,
NULL::bpchar AS isdiscountprinted,
NULL::bpchar AS isindispute,
NULL::bpchar AS ispaid,
NULL::bpchar AS ispayschedulevalid,
NULL::bpchar AS c_invoice_isprinted,
NULL::bpchar AS c_invoice_isselfservice,
NULL::bpchar AS issotrx,
NULL::bpchar AS c_invoice_istaxincluded,
NULL::bpchar AS istransferred,
NULL::numeric AS m_pricelist_id,
NULL::numeric AS m_rma_id,
NULL::bpchar AS paymentrule,
NULL::character varying AS poreference,
NULL::bpchar AS posted,
NULL::numeric AS processedon,
NULL::bpchar AS processing,
NULL::numeric AS ref_invoice_id,
NULL::numeric AS reversal_id,
NULL::numeric AS salesrep_id,
NULL::bpchar AS sendemail,
NULL::numeric AS totallines,
NULL::timestamp without time zone AS c_invoice_updated,
NULL::numeric AS c_invoice_updatedby,
NULL::numeric AS c_invoice_user1_id,
NULL::numeric AS c_invoice_user2_id,
NULL::numeric AS c_tax_ad_org_id,
NULL::numeric AS ad_rule_id,
NULL::numeric AS c_country_id,
NULL::numeric AS c_region_id,
NULL::numeric AS c_tax_c_taxcategory_id,
NULL::character varying AS c_tax_description,
NULL::bpchar AS c_tax_isactive,
NULL::bpchar AS c_tax_isdefault,
NULL::bpchar AS isdocumentlevel,
NULL::bpchar AS issalestax,
NULL::bpchar AS c_tax_issummary,
NULL::bpchar AS istaxexempt,
NULL::character varying AS c_tax_name,
NULL::numeric AS parent_tax_id,
NULL::numeric AS rate,
NULL::bpchar AS requirestaxcertificate,
NULL::bpchar AS sopotype,
NULL::numeric AS to_country_id,
NULL::numeric AS to_region_id,
NULL::timestamp without time zone AS validfrom,
NULL::numeric AS m_product_ad_org_id,
NULL::character varying AS classification,
NULL::bpchar AS m_product_copyfrom,
NULL::timestamp without time zone AS m_product_created,
NULL::numeric AS m_product_createdby,
NULL::numeric AS c_revenuerecognition_id,
NULL::numeric AS c_subscriptiontype_id,
NULL::numeric AS m_product_c_taxcategory_id,
NULL::character varying AS descriptionurl,
NULL::bpchar AS discontinued,
NULL::timestamp without time zone AS discontinuedat,
NULL::character varying AS group1,
NULL::character varying AS group2,
NULL::numeric AS guaranteedays,
NULL::numeric AS guaranteedaysmin,
NULL::character varying AS help,
NULL::bpchar AS m_product_isactive,
NULL::bpchar AS isbom,
NULL::bpchar AS isdropship,
NULL::bpchar AS isexcludeautodelivery,
NULL::bpchar AS isinvoiceprintdetails,
NULL::bpchar AS ispicklistprintdetails,
NULL::bpchar AS ispurchased,
NULL::bpchar AS m_product_isselfservice,
NULL::bpchar AS issold,
NULL::bpchar AS isstocked,
NULL::bpchar AS m_product_issummary,
NULL::bpchar AS isverified,
NULL::bpchar AS iswebstorefeatured,
NULL::numeric AS lowlevel,
NULL::numeric AS m_product_m_attributeset_id,
NULL::numeric AS m_product_m_asi_id,
NULL::numeric AS m_freightcategory_id,
NULL::numeric AS m_locator_id,
NULL::numeric AS m_product_m_prod_category_id,
NULL::bpchar AS m_product_processing,
NULL::bpchar AS producttype,
NULL::numeric AS r_mailtext_id,
NULL::numeric AS m_product_salesrep_id,
NULL::numeric AS s_expensetype_id,
NULL::numeric AS shelfdepth,
NULL::numeric AS shelfheight,
NULL::numeric AS shelfwidth,
NULL::numeric AS m_product_s_resource_id,
NULL::numeric AS unitsperpack,
NULL::numeric AS unitsperpallet,
NULL::timestamp without time zone AS m_product_updated,
NULL::numeric AS m_product_updatedby,
NULL::character varying AS versionno,
NULL::numeric AS volume,
NULL::numeric AS weight,
NULL::numeric AS c_charge_ad_org_id,
NULL::numeric AS c_charge_c_bpartner_id,
NULL::numeric AS c_chargetype_id,
NULL::numeric AS c_charge_chargeamt,
NULL::numeric AS c_charge_c_taxcategory_id,
NULL::character varying AS c_charge_description,
NULL::bpchar AS c_charge_isactive,
NULL::bpchar AS issamecurrency,
NULL::bpchar AS issametax,
NULL::bpchar AS c_charge_istaxincluded,
NULL::numeric AS c_bp_product_ad_org_id,
NULL::numeric AS c_bp_product_c_bpartner_id,
NULL::timestamp without time zone AS c_bp_product_created,
NULL::numeric AS c_bp_product_createdby,
NULL::character varying AS c_bp_product_description,
NULL::bpchar AS c_bp_product_isactive,
NULL::bpchar AS ismanufacturer,
NULL::character varying AS manufacturer,
NULL::numeric AS qualityrating,
NULL::numeric AS shelflifemindays,
NULL::numeric AS shelflifeminpct,
NULL::timestamp without time zone AS c_bp_product_updated,
NULL::numeric AS c_bp_product_updatedby,
NULL::character varying AS vendorcategory,
NULL::numeric AS s_rassignment_ad_org_id,
NULL::timestamp without time zone AS assigndatefrom,
NULL::timestamp without time zone AS assigndateto,
NULL::timestamp without time zone AS s_rassignment_created,
NULL::numeric AS s_rassignment_createby,
NULL::bpchar AS s_rassignment_isactive,
NULL::bpchar AS isconfirmed,
NULL::character varying AS s_rassignment_name,
NULL::numeric AS s_rassignment_qty,
NULL::numeric AS s_rassignment_s_resource_id,
NULL::timestamp without time zone AS s_rassignment_updated,
NULL::numeric AS s_rassignment_updatedby,
NULL::numeric AS m_asi_ad_org_id,
NULL::timestamp without time zone AS m_asi_created,
NULL::numeric AS m_asi_createdby,
NULL::character varying AS m_asi_description,
NULL::bpchar AS m_asi_isactive,
NULL::timestamp without time zone AS m_asi_updated,
NULL::numeric AS m_asi_updatedby
FROM c_invoiceline il
WHERE (il.c_uom_id IS NULL)
UNION
SELECT i.ad_client_id,
i.ad_org_id,
i.isactive,
i.created,
i.createdby,
i.updated,
i.updatedby,
'en_US'::text AS ad_language,
i.c_invoice_id,
NULL::numeric AS c_invoiceline_id,
NULL::numeric AS c_tax_id,
NULL::numeric AS taxamt,
NULL::numeric AS linetotalamt,
NULL::character varying AS taxindicator,
999998 AS line,
NULL::numeric AS m_product_id,
NULL::numeric AS qtyinvoiced,
NULL::numeric AS qtyentered,
NULL::character varying AS uomsymbol,
NULL::character varying AS name,
NULL::character varying AS description,
NULL::character varying AS documentnote,
NULL::character varying AS upc,
NULL::character varying AS sku,
NULL::character varying AS productvalue,
NULL::character varying AS resourcedescription,
NULL::numeric AS pricelist,
NULL::numeric AS priceenteredlist,
NULL::numeric AS discount,
NULL::numeric AS priceactual,
NULL::numeric AS priceentered,
NULL::numeric AS linenetamt,
NULL::numeric AS m_attributesetinstance_id,
NULL::numeric AS m_attributeset_id,
NULL::character varying AS serno,
NULL::character varying AS lot,
NULL::numeric AS m_lot_id,
NULL::timestamp without time zone AS guaranteedate,
NULL::character varying AS productdescription,
NULL::character varying AS imageurl,
NULL::numeric AS c_campaign_id,
NULL::numeric AS c_project_id,
NULL::numeric AS c_activity_id,
NULL::numeric AS c_projectphase_id,
NULL::numeric AS c_projecttask_id,
NULL::numeric AS c_invoiceline_ad_orgtrx_id,
NULL::bpchar AS a_processed,
NULL::numeric AS c_charge_id,
NULL::numeric AS c_orderline_id,
NULL::numeric AS c_uom_id,
NULL::bpchar AS isdescription,
NULL::bpchar AS c_invoiceline_isprinted,
NULL::numeric AS m_inoutline_id,
NULL::numeric AS m_rmaline_id,
NULL::numeric AS pricelimit,
NULL::bpchar AS c_invoiceline_processed,
NULL::numeric AS ref_invoiceline_id,
NULL::numeric AS rramt,
NULL::timestamp without time zone AS rrstartdate,
NULL::numeric AS s_resourceassignment_id,
NULL::numeric AS c_invoiceline_user1_id,
NULL::numeric AS c_invoiceline_user2_id,
NULL::numeric AS c_uom_ad_org_id,
NULL::numeric AS costingprecision,
NULL::character varying AS c_uom_description,
NULL::bpchar AS c_uom_isactive,
NULL::bpchar AS c_uom_isdefault,
NULL::character varying AS c_uom_name,
NULL::numeric AS stdprecision,
NULL::character varying AS uomtype,
NULL::character varying AS x12de355,
NULL::numeric AS c_invoice_ad_org_id,
NULL::numeric AS c_invoice_ad_orgtrx_id,
NULL::numeric AS c_invoice_ad_user_id,
NULL::numeric AS c_invoice_c_activity_id,
NULL::numeric AS c_invoice_c_bpartner_id,
NULL::numeric AS c_bpartner_location_id,
NULL::numeric AS c_invoice_c_campaign_id,
NULL::numeric AS c_invoice_c_charge_id,
NULL::numeric AS c_conversiontype_id,
NULL::numeric AS c_currency_id,
NULL::numeric AS c_doctype_id,
NULL::numeric AS c_doctypetarget_id,
NULL::numeric AS c_dunninglevel_id,
NULL::numeric AS c_invoice_chargeamt,
NULL::numeric AS c_order_id,
NULL::numeric AS c_payment_id,
NULL::numeric AS c_paymentterm_id,
NULL::numeric AS c_invoice_c_project_id,
NULL::timestamp without time zone AS c_invoice_created,
NULL::numeric AS c_invoice_createdby,
NULL::timestamp without time zone AS dateacct,
NULL::timestamp without time zone AS dateinvoiced,
NULL::timestamp without time zone AS dateordered,
NULL::timestamp without time zone AS dateprinted,
NULL::character varying AS c_invoice_description,
NULL::bpchar AS docaction,
NULL::bpchar AS docstatus,
NULL::character varying AS documentno,
NULL::date AS dunninggrace,
NULL::bpchar AS generateto,
NULL::numeric AS grandtotal,
NULL::bpchar AS invoicecollectiontype,
NULL::bpchar AS c_invoice_isactive,
NULL::bpchar AS isapproved,
NULL::bpchar AS isdiscountprinted,
NULL::bpchar AS isindispute,
NULL::bpchar AS ispaid,
NULL::bpchar AS ispayschedulevalid,
NULL::bpchar AS c_invoice_isprinted,
NULL::bpchar AS c_invoice_isselfservice,
NULL::bpchar AS issotrx,
NULL::bpchar AS c_invoice_istaxincluded,
NULL::bpchar AS istransferred,
NULL::numeric AS m_pricelist_id,
NULL::numeric AS m_rma_id,
NULL::bpchar AS paymentrule,
NULL::character varying AS poreference,
NULL::bpchar AS posted,
NULL::numeric AS processedon,
NULL::bpchar AS processing,
NULL::numeric AS ref_invoice_id,
NULL::numeric AS reversal_id,
NULL::numeric AS salesrep_id,
NULL::bpchar AS sendemail,
NULL::numeric AS totallines,
NULL::timestamp without time zone AS c_invoice_updated,
NULL::numeric AS c_invoice_updatedby,
NULL::numeric AS c_invoice_user1_id,
NULL::numeric AS c_invoice_user2_id,
NULL::numeric AS c_tax_ad_org_id,
NULL::numeric AS ad_rule_id,
NULL::numeric AS c_country_id,
NULL::numeric AS c_region_id,
NULL::numeric AS c_tax_c_taxcategory_id,
NULL::character varying AS c_tax_description,
NULL::bpchar AS c_tax_isactive,
NULL::bpchar AS c_tax_isdefault,
NULL::bpchar AS isdocumentlevel,
NULL::bpchar AS issalestax,
NULL::bpchar AS c_tax_issummary,
NULL::bpchar AS istaxexempt,
NULL::character varying AS c_tax_name,
NULL::numeric AS parent_tax_id,
NULL::numeric AS rate,
NULL::bpchar AS requirestaxcertificate,
NULL::bpchar AS sopotype,
NULL::numeric AS to_country_id,
NULL::numeric AS to_region_id,
NULL::timestamp without time zone AS validfrom,
NULL::numeric AS m_product_ad_org_id,
NULL::character varying AS classification,
NULL::bpchar AS m_product_copyfrom,
NULL::timestamp without time zone AS m_product_created,
NULL::numeric AS m_product_createdby,
NULL::numeric AS c_revenuerecognition_id,
NULL::numeric AS c_subscriptiontype_id,
NULL::numeric AS m_product_c_taxcategory_id,
NULL::character varying AS descriptionurl,
NULL::bpchar AS discontinued,
NULL::timestamp without time zone AS discontinuedat,
NULL::character varying AS group1,
NULL::character varying AS group2,
NULL::numeric AS guaranteedays,
NULL::numeric AS guaranteedaysmin,
NULL::character varying AS help,
NULL::bpchar AS m_product_isactive,
NULL::bpchar AS isbom,
NULL::bpchar AS isdropship,
NULL::bpchar AS isexcludeautodelivery,
NULL::bpchar AS isinvoiceprintdetails,
NULL::bpchar AS ispicklistprintdetails,
NULL::bpchar AS ispurchased,
NULL::bpchar AS m_product_isselfservice,
NULL::bpchar AS issold,
NULL::bpchar AS isstocked,
NULL::bpchar AS m_product_issummary,
NULL::bpchar AS isverified,
NULL::bpchar AS iswebstorefeatured,
NULL::numeric AS lowlevel,
NULL::numeric AS m_product_m_attributeset_id,
NULL::numeric AS m_product_m_asi_id,
NULL::numeric AS m_freightcategory_id,
NULL::numeric AS m_locator_id,
NULL::numeric AS m_product_m_prod_category_id,
NULL::bpchar AS m_product_processing,
NULL::bpchar AS producttype,
NULL::numeric AS r_mailtext_id,
NULL::numeric AS m_product_salesrep_id,
NULL::numeric AS s_expensetype_id,
NULL::numeric AS shelfdepth,
NULL::numeric AS shelfheight,
NULL::numeric AS shelfwidth,
NULL::numeric AS m_product_s_resource_id,
NULL::numeric AS unitsperpack,
NULL::numeric AS unitsperpallet,
NULL::timestamp without time zone AS m_product_updated,
NULL::numeric AS m_product_updatedby,
NULL::character varying AS versionno,
NULL::numeric AS volume,
NULL::numeric AS weight,
NULL::numeric AS c_charge_ad_org_id,
NULL::numeric AS c_charge_c_bpartner_id,
NULL::numeric AS c_chargetype_id,
NULL::numeric AS c_charge_chargeamt,
NULL::numeric AS c_charge_c_taxcategory_id,
NULL::character varying AS c_charge_description,
NULL::bpchar AS c_charge_isactive,
NULL::bpchar AS issamecurrency,
NULL::bpchar AS issametax,
NULL::bpchar AS c_charge_istaxincluded,
NULL::numeric AS c_bp_product_ad_org_id,
NULL::numeric AS c_bp_product_c_bpartner_id,
NULL::timestamp without time zone AS c_bp_product_created,
NULL::numeric AS c_bp_product_createdby,
NULL::character varying AS c_bp_product_description,
NULL::bpchar AS c_bp_product_isactive,
NULL::bpchar AS ismanufacturer,
NULL::character varying AS manufacturer,
NULL::numeric AS qualityrating,
NULL::numeric AS shelflifemindays,
NULL::numeric AS shelflifeminpct,
NULL::timestamp without time zone AS c_bp_product_updated,
NULL::numeric AS c_bp_product_updatedby,
NULL::character varying AS vendorcategory,
NULL::numeric AS s_rassignment_ad_org_id,
NULL::timestamp without time zone AS assigndatefrom,
NULL::timestamp without time zone AS assigndateto,
NULL::timestamp without time zone AS s_rassignment_created,
NULL::numeric AS s_rassignment_createby,
NULL::bpchar AS s_rassignment_isactive,
NULL::bpchar AS isconfirmed,
NULL::character varying AS s_rassignment_name,
NULL::numeric AS s_rassignment_qty,
NULL::numeric AS s_rassignment_s_resource_id,
NULL::timestamp without time zone AS s_rassignment_updated,
NULL::numeric AS s_rassignment_updatedby,
NULL::numeric AS m_asi_ad_org_id,
NULL::timestamp without time zone AS m_asi_created,
NULL::numeric AS m_asi_createdby,
NULL::character varying AS m_asi_description,
NULL::bpchar AS m_asi_isactive,
NULL::timestamp without time zone AS m_asi_updated,
NULL::numeric AS m_asi_updatedby
FROM c_invoice i
UNION
SELECT it.ad_client_id,
it.ad_org_id,
it.isactive,
it.created,
it.createdby,
it.updated,
it.updatedby,
'en_US'::text AS ad_language,
it.c_invoice_id,
NULL::numeric AS c_invoiceline_id,
it.c_tax_id,
NULL::numeric AS taxamt,
NULL::numeric AS linetotalamt,
t.taxindicator,
999999 AS line,
NULL::numeric AS m_product_id,
NULL::numeric AS qtyinvoiced,
NULL::numeric AS qtyentered,
NULL::character varying AS uomsymbol,
t.name,
NULL::character varying AS description,
NULL::character varying AS documentnote,
NULL::character varying AS upc,
NULL::character varying AS sku,
NULL::character varying AS productvalue,
NULL::character varying AS resourcedescription,
NULL::numeric AS pricelist,
NULL::numeric AS priceenteredlist,
NULL::numeric AS discount,
CASE
WHEN (it.istaxincluded = 'Y'::bpchar) THEN it.taxamt
ELSE it.taxbaseamt
END AS priceactual,
CASE
WHEN (it.istaxincluded = 'Y'::bpchar) THEN it.taxamt
ELSE it.taxbaseamt
END AS priceentered,
CASE
WHEN (it.istaxincluded = 'Y'::bpchar) THEN NULL::numeric
ELSE it.taxamt
END AS linenetamt,
NULL::numeric AS m_attributesetinstance_id,
NULL::numeric AS m_attributeset_id,
NULL::character varying AS serno,
NULL::character varying AS lot,
NULL::numeric AS m_lot_id,
NULL::timestamp without time zone AS guaranteedate,
NULL::character varying AS productdescription,
NULL::character varying AS imageurl,
NULL::numeric AS c_campaign_id,
NULL::numeric AS c_project_id,
NULL::numeric AS c_activity_id,
NULL::numeric AS c_projectphase_id,
NULL::numeric AS c_projecttask_id,
NULL::numeric AS c_invoiceline_ad_orgtrx_id,
NULL::bpchar AS a_processed,
NULL::numeric AS c_charge_id,
NULL::numeric AS c_orderline_id,
NULL::numeric AS c_uom_id,
NULL::bpchar AS isdescription,
NULL::bpchar AS c_invoiceline_isprinted,
NULL::numeric AS m_inoutline_id,
NULL::numeric AS m_rmaline_id,
NULL::numeric AS pricelimit,
NULL::bpchar AS c_invoiceline_processed,
NULL::numeric AS ref_invoiceline_id,
NULL::numeric AS rramt,
NULL::timestamp without time zone AS rrstartdate,
NULL::numeric AS s_resourceassignment_id,
NULL::numeric AS c_invoiceline_user1_id,
NULL::numeric AS c_invoiceline_user2_id,
NULL::numeric AS c_uom_ad_org_id,
NULL::numeric AS costingprecision,
NULL::character varying AS c_uom_description,
NULL::bpchar AS c_uom_isactive,
NULL::bpchar AS c_uom_isdefault,
NULL::character varying AS c_uom_name,
NULL::numeric AS stdprecision,
NULL::character varying AS uomtype,
NULL::character varying AS x12de355,
NULL::numeric AS c_invoice_ad_org_id,
NULL::numeric AS c_invoice_ad_orgtrx_id,
NULL::numeric AS c_invoice_ad_user_id,
NULL::numeric AS c_invoice_c_activity_id,
NULL::numeric AS c_invoice_c_bpartner_id,
NULL::numeric AS c_bpartner_location_id,
NULL::numeric AS c_invoice_c_campaign_id,
NULL::numeric AS c_invoice_c_charge_id,
NULL::numeric AS c_conversiontype_id,
NULL::numeric AS c_currency_id,
NULL::numeric AS c_doctype_id,
NULL::numeric AS c_doctypetarget_id,
NULL::numeric AS c_dunninglevel_id,
NULL::numeric AS c_invoice_chargeamt,
NULL::numeric AS c_order_id,
NULL::numeric AS c_payment_id,
NULL::numeric AS c_paymentterm_id,
NULL::numeric AS c_invoice_c_project_id,
NULL::timestamp without time zone AS c_invoice_created,
NULL::numeric AS c_invoice_createdby,
NULL::timestamp without time zone AS dateacct,
NULL::timestamp without time zone AS dateinvoiced,
NULL::timestamp without time zone AS dateordered,
NULL::timestamp without time zone AS dateprinted,
NULL::character varying AS c_invoice_description,
NULL::bpchar AS docaction,
NULL::bpchar AS docstatus,
NULL::character varying AS documentno,
NULL::date AS dunninggrace,
NULL::bpchar AS generateto,
NULL::numeric AS grandtotal,
NULL::bpchar AS invoicecollectiontype,
NULL::bpchar AS c_invoice_isactive,
NULL::bpchar AS isapproved,
NULL::bpchar AS isdiscountprinted,
NULL::bpchar AS isindispute,
NULL::bpchar AS ispaid,
NULL::bpchar AS ispayschedulevalid,
NULL::bpchar AS c_invoice_isprinted,
NULL::bpchar AS c_invoice_isselfservice,
NULL::bpchar AS issotrx,
NULL::bpchar AS c_invoice_istaxincluded,
NULL::bpchar AS istransferred,
NULL::numeric AS m_pricelist_id,
NULL::numeric AS m_rma_id,
NULL::bpchar AS paymentrule,
NULL::character varying AS poreference,
NULL::bpchar AS posted,
NULL::numeric AS processedon,
NULL::bpchar AS processing,
NULL::numeric AS ref_invoice_id,
NULL::numeric AS reversal_id,
NULL::numeric AS salesrep_id,
NULL::bpchar AS sendemail,
NULL::numeric AS totallines,
NULL::timestamp without time zone AS c_invoice_updated,
NULL::numeric AS c_invoice_updatedby,
NULL::numeric AS c_invoice_user1_id,
NULL::numeric AS c_invoice_user2_id,
NULL::numeric AS c_tax_ad_org_id,
NULL::numeric AS ad_rule_id,
NULL::numeric AS c_country_id,
NULL::numeric AS c_region_id,
NULL::numeric AS c_tax_c_taxcategory_id,
NULL::character varying AS c_tax_description,
NULL::bpchar AS c_tax_isactive,
NULL::bpchar AS c_tax_isdefault,
NULL::bpchar AS isdocumentlevel,
NULL::bpchar AS issalestax,
NULL::bpchar AS c_tax_issummary,
NULL::bpchar AS istaxexempt,
NULL::character varying AS c_tax_name,
NULL::numeric AS parent_tax_id,
NULL::numeric AS rate,
NULL::bpchar AS requirestaxcertificate,
NULL::bpchar AS sopotype,
NULL::numeric AS to_country_id,
NULL::numeric AS to_region_id,
NULL::timestamp without time zone AS validfrom,
NULL::numeric AS m_product_ad_org_id,
NULL::character varying AS classification,
NULL::bpchar AS m_product_copyfrom,
NULL::timestamp without time zone AS m_product_created,
NULL::numeric AS m_product_createdby,
NULL::numeric AS c_revenuerecognition_id,
NULL::numeric AS c_subscriptiontype_id,
NULL::numeric AS m_product_c_taxcategory_id,
NULL::character varying AS descriptionurl,
NULL::bpchar AS discontinued,
NULL::timestamp without time zone AS discontinuedat,
NULL::character varying AS group1,
NULL::character varying AS group2,
NULL::numeric AS guaranteedays,
NULL::numeric AS guaranteedaysmin,
NULL::character varying AS help,
NULL::bpchar AS m_product_isactive,
NULL::bpchar AS isbom,
NULL::bpchar AS isdropship,
NULL::bpchar AS isexcludeautodelivery,
NULL::bpchar AS isinvoiceprintdetails,
NULL::bpchar AS ispicklistprintdetails,
NULL::bpchar AS ispurchased,
NULL::bpchar AS m_product_isselfservice,
NULL::bpchar AS issold,
NULL::bpchar AS isstocked,
NULL::bpchar AS m_product_issummary,
NULL::bpchar AS isverified,
NULL::bpchar AS iswebstorefeatured,
NULL::numeric AS lowlevel,
NULL::numeric AS m_product_m_attributeset_id,
NULL::numeric AS m_product_m_asi_id,
NULL::numeric AS m_freightcategory_id,
NULL::numeric AS m_locator_id,
NULL::numeric AS m_product_m_prod_category_id,
NULL::bpchar AS m_product_processing,
NULL::bpchar AS producttype,
NULL::numeric AS r_mailtext_id,
NULL::numeric AS m_product_salesrep_id,
NULL::numeric AS s_expensetype_id,
NULL::numeric AS shelfdepth,
NULL::numeric AS shelfheight,
NULL::numeric AS shelfwidth,
NULL::numeric AS m_product_s_resource_id,
NULL::numeric AS unitsperpack,
NULL::numeric AS unitsperpallet,
NULL::timestamp without time zone AS m_product_updated,
NULL::numeric AS m_product_updatedby,
NULL::character varying AS versionno,
NULL::numeric AS volume,
NULL::numeric AS weight,
NULL::numeric AS c_charge_ad_org_id,
NULL::numeric AS c_charge_c_bpartner_id,
NULL::numeric AS c_chargetype_id,
NULL::numeric AS c_charge_chargeamt,
NULL::numeric AS c_charge_c_taxcategory_id,
NULL::character varying AS c_charge_description,
NULL::bpchar AS c_charge_isactive,
NULL::bpchar AS issamecurrency,
NULL::bpchar AS issametax,
NULL::bpchar AS c_charge_istaxincluded,
NULL::numeric AS c_bp_product_ad_org_id,
NULL::numeric AS c_bp_product_c_bpartner_id,
NULL::timestamp without time zone AS c_bp_product_created,
NULL::numeric AS c_bp_product_createdby,
NULL::character varying AS c_bp_product_description,
NULL::bpchar AS c_bp_product_isactive,
NULL::bpchar AS ismanufacturer,
NULL::character varying AS manufacturer,
NULL::numeric AS qualityrating,
NULL::numeric AS shelflifemindays,
NULL::numeric AS shelflifeminpct,
NULL::timestamp without time zone AS c_bp_product_updated,
NULL::numeric AS c_bp_product_updatedby,
NULL::character varying AS vendorcategory,
NULL::numeric AS s_rassignment_ad_org_id,
NULL::timestamp without time zone AS assigndatefrom,
NULL::timestamp without time zone AS assigndateto,
NULL::timestamp without time zone AS s_rassignment_created,
NULL::numeric AS s_rassignment_createby,
NULL::bpchar AS s_rassignment_isactive,
NULL::bpchar AS isconfirmed,
NULL::character varying AS s_rassignment_name,
NULL::numeric AS s_rassignment_qty,
NULL::numeric AS s_rassignment_s_resource_id,
NULL::timestamp without time zone AS s_rassignment_updated,
NULL::numeric AS s_rassignment_updatedby,
NULL::numeric AS m_asi_ad_org_id,
NULL::timestamp without time zone AS m_asi_created,
NULL::numeric AS m_asi_createdby,
NULL::character varying AS m_asi_description,
NULL::bpchar AS m_asi_isactive,
NULL::timestamp without time zone AS m_asi_updated,
NULL::numeric AS m_asi_updatedby
FROM (c_invoicetax it
JOIN c_tax t ON ((it.c_tax_id = t.c_tax_id)));


インデックス情報
No インデックス名 カラムリスト ユニーク 備考


外部キー情報
No 外部キー名 カラムリスト 参照先 参照先カラムリスト


外部キー情報(PK側)
No 外部キー名 カラムリスト 参照元 参照元カラムリスト


RDBMS固有の情報
No プロパティ名 プロパティ値
1 schemaname adempiere
2 viewname c_invoice_linetax_v
3 viewowner adempiere
4 definition SELECT il.ad_client_id,
il.ad_org_id,
il.isactive,
il.created,
il.createdby,
il.updated,
il.updatedby,
'en_US'::text AS ad_language,
il.c_invoice_id,
il.c_invoiceline_id,
il.c_tax_id,
il.taxamt,
il.linetotalamt,
t.taxindicator,
il.line,
p.m_product_id,
CASE
WHEN ((il.qtyinvoiced <> (0)::numeric) OR (il.m_product_id IS NOT NULL)) THEN il.qtyinvoiced
ELSE NULL::numeric
END AS qtyinvoiced,
CASE
WHEN ((il.qtyentered <> (0)::numeric) OR (il.m_product_id IS NOT NULL)) THEN il.qtyentered
ELSE NULL::numeric
END AS qtyentered,
CASE
WHEN ((il.qtyentered <> (0)::numeric) OR (il.m_product_id IS NOT NULL)) THEN uom.uomsymbol
ELSE NULL::character varying
END AS uomsymbol,
COALESCE(c.name, (((p.name)::text || (COALESCE(productattribute(il.m_attributesetinstance_id), ''::character varying))::text))::character varying, il.description) AS name,
CASE
WHEN (COALESCE(c.name, p.name) IS NOT NULL) THEN il.description
ELSE NULL::character varying
END AS description,
p.documentnote,
p.upc,
p.sku,
COALESCE(pp.vendorproductno, p.value) AS productvalue,
ra.description AS resourcedescription,
CASE
WHEN ((i.isdiscountprinted = 'Y'::bpchar) AND (il.pricelist <> (0)::numeric)) THEN il.pricelist
ELSE NULL::numeric
END AS pricelist,
CASE
WHEN (((i.isdiscountprinted = 'Y'::bpchar) AND (il.pricelist <> (0)::numeric)) AND (il.qtyentered <> (0)::numeric)) THEN ((il.pricelist * il.qtyinvoiced) / il.qtyentered)
ELSE NULL::numeric
END AS priceenteredlist,
CASE
WHEN (((i.isdiscountprinted = 'Y'::bpchar) AND (il.pricelist > il.priceactual)) AND (il.pricelist <> (0)::numeric)) THEN (((il.pricelist - il.priceactual) / il.pricelist) * (100)::numeric)
ELSE NULL::numeric
END AS discount,
CASE
WHEN ((il.priceactual <> (0)::numeric) OR (il.m_product_id IS NOT NULL)) THEN il.priceactual
ELSE NULL::numeric
END AS priceactual,
CASE
WHEN ((il.priceentered <> (0)::numeric) OR (il.m_product_id IS NOT NULL)) THEN il.priceentered
ELSE NULL::numeric
END AS priceentered,
CASE
WHEN ((il.linenetamt <> (0)::numeric) OR (il.m_product_id IS NOT NULL)) THEN il.linenetamt
ELSE NULL::numeric
END AS linenetamt,
il.m_attributesetinstance_id,
asi.m_attributeset_id,
asi.serno,
asi.lot,
asi.m_lot_id,
asi.guaranteedate,
p.description AS productdescription,
p.imageurl,
il.c_campaign_id,
il.c_project_id,
il.c_activity_id,
il.c_projectphase_id,
il.c_projecttask_id,
il.ad_orgtrx_id AS c_invoiceline_ad_orgtrx_id,
il.a_processed,
il.c_charge_id,
il.c_orderline_id,
il.c_uom_id,
il.isdescription,
il.isprinted AS c_invoiceline_isprinted,
il.m_inoutline_id,
il.m_rmaline_id,
il.pricelimit,
il.processed AS c_invoiceline_processed,
il.ref_invoiceline_id,
il.rramt,
il.rrstartdate,
il.s_resourceassignment_id,
il.user1_id AS c_invoiceline_user1_id,
il.user2_id AS c_invoiceline_user2_id,
uom.ad_org_id AS c_uom_ad_org_id,
uom.costingprecision,
uom.description AS c_uom_description,
uom.isactive AS c_uom_isactive,
uom.isdefault AS c_uom_isdefault,
uom.name AS c_uom_name,
uom.stdprecision,
uom.uomtype,
uom.x12de355,
i.ad_org_id AS c_invoice_ad_org_id,
i.ad_orgtrx_id AS c_invoice_ad_orgtrx_id,
i.ad_user_id AS c_invoice_ad_user_id,
i.c_activity_id AS c_invoice_c_activity_id,
i.c_bpartner_id AS c_invoice_c_bpartner_id,
i.c_bpartner_location_id,
i.c_campaign_id AS c_invoice_c_campaign_id,
i.c_charge_id AS c_invoice_c_charge_id,
i.c_conversiontype_id,
i.c_currency_id,
i.c_doctype_id,
i.c_doctypetarget_id,
i.c_dunninglevel_id,
i.chargeamt AS c_invoice_chargeamt,
i.c_order_id,
i.c_payment_id,
i.c_paymentterm_id,
i.c_project_id AS c_invoice_c_project_id,
i.created AS c_invoice_created,
i.createdby AS c_invoice_createdby,
i.dateacct,
i.dateinvoiced,
i.dateordered,
i.dateprinted,
i.description AS c_invoice_description,
i.docaction,
i.docstatus,
i.documentno,
i.dunninggrace,
i.generateto,
i.grandtotal,
i.invoicecollectiontype,
i.isactive AS c_invoice_isactive,
i.isapproved,
i.isdiscountprinted,
i.isindispute,
i.ispaid,
i.ispayschedulevalid,
i.isprinted AS c_invoice_isprinted,
i.isselfservice AS c_invoice_isselfservice,
i.issotrx,
i.istaxincluded AS c_invoice_istaxincluded,
i.istransferred,
i.m_pricelist_id,
i.m_rma_id,
i.paymentrule,
i.poreference,
i.posted,
i.processedon,
i.processing,
i.ref_invoice_id,
i.reversal_id,
i.salesrep_id,
i.sendemail,
i.totallines,
i.updated AS c_invoice_updated,
i.updatedby AS c_invoice_updatedby,
i.user1_id AS c_invoice_user1_id,
i.user2_id AS c_invoice_user2_id,
t.ad_org_id AS c_tax_ad_org_id,
t.ad_rule_id,
t.c_country_id,
t.c_region_id,
t.c_taxcategory_id AS c_tax_c_taxcategory_id,
t.description AS c_tax_description,
t.isactive AS c_tax_isactive,
t.isdefault AS c_tax_isdefault,
t.isdocumentlevel,
t.issalestax,
t.issummary AS c_tax_issummary,
t.istaxexempt,
t.name AS c_tax_name,
t.parent_tax_id,
t.rate,
t.requirestaxcertificate,
t.sopotype,
t.to_country_id,
t.to_region_id,
t.validfrom,
p.ad_org_id AS m_product_ad_org_id,
p.classification,
p.copyfrom AS m_product_copyfrom,
p.created AS m_product_created,
p.createdby AS m_product_createdby,
p.c_revenuerecognition_id,
p.c_subscriptiontype_id,
p.c_taxcategory_id AS m_product_c_taxcategory_id,
p.descriptionurl,
p.discontinued,
p.discontinuedat,
p.group1,
p.group2,
p.guaranteedays,
p.guaranteedaysmin,
p.help,
p.isactive AS m_product_isactive,
p.isbom,
p.isdropship,
p.isexcludeautodelivery,
p.isinvoiceprintdetails,
p.ispicklistprintdetails,
p.ispurchased,
p.isselfservice AS m_product_isselfservice,
p.issold,
p.isstocked,
p.issummary AS m_product_issummary,
p.isverified,
p.iswebstorefeatured,
p.lowlevel,
p.m_attributeset_id AS m_product_m_attributeset_id,
p.m_attributesetinstance_id AS m_product_m_asi_id,
p.m_freightcategory_id,
p.m_locator_id,
p.m_product_category_id AS m_product_m_prod_category_id,
p.processing AS m_product_processing,
p.producttype,
p.r_mailtext_id,
p.salesrep_id AS m_product_salesrep_id,
p.s_expensetype_id,
p.shelfdepth,
p.shelfheight,
p.shelfwidth,
p.s_resource_id AS m_product_s_resource_id,
p.unitsperpack,
p.unitsperpallet,
p.updated AS m_product_updated,
p.updatedby AS m_product_updatedby,
p.versionno,
p.volume,
p.weight,
c.ad_org_id AS c_charge_ad_org_id,
c.c_bpartner_id AS c_charge_c_bpartner_id,
c.c_chargetype_id,
c.chargeamt AS c_charge_chargeamt,
c.c_taxcategory_id AS c_charge_c_taxcategory_id,
c.description AS c_charge_description,
c.isactive AS c_charge_isactive,
c.issamecurrency,
c.issametax,
c.istaxincluded AS c_charge_istaxincluded,
pp.ad_org_id AS c_bp_product_ad_org_id,
pp.c_bpartner_id AS c_bp_product_c_bpartner_id,
pp.created AS c_bp_product_created,
pp.createdby AS c_bp_product_createdby,
pp.description AS c_bp_product_description,
pp.isactive AS c_bp_product_isactive,
pp.ismanufacturer,
pp.manufacturer,
pp.qualityrating,
pp.shelflifemindays,
pp.shelflifeminpct,
pp.updated AS c_bp_product_updated,
pp.updatedby AS c_bp_product_updatedby,
pp.vendorcategory,
ra.ad_org_id AS s_rassignment_ad_org_id,
ra.assigndatefrom,
ra.assigndateto,
ra.created AS s_rassignment_created,
ra.createdby AS s_rassignment_createby,
ra.isactive AS s_rassignment_isactive,
ra.isconfirmed,
ra.name AS s_rassignment_name,
ra.qty AS s_rassignment_qty,
ra.s_resource_id AS s_rassignment_s_resource_id,
ra.updated AS s_rassignment_updated,
ra.updatedby AS s_rassignment_updatedby,
asi.ad_org_id AS m_asi_ad_org_id,
asi.created AS m_asi_created,
asi.createdby AS m_asi_createdby,
asi.description AS m_asi_description,
asi.isactive AS m_asi_isactive,
asi.updated AS m_asi_updated,
asi.updatedby AS m_asi_updatedby
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 c_tax t ON ((il.c_tax_id = t.c_tax_id)))
LEFT JOIN m_product p ON ((il.m_product_id = p.m_product_id)))
LEFT JOIN c_charge c ON ((il.c_charge_id = c.c_charge_id)))
LEFT JOIN c_bpartner_product pp ON (((il.m_product_id = pp.m_product_id) AND (i.c_bpartner_id = pp.c_bpartner_id))))
LEFT JOIN s_resourceassignment ra ON ((il.s_resourceassignment_id = ra.s_resourceassignment_id)))
LEFT JOIN m_attributesetinstance asi ON ((il.m_attributesetinstance_id = asi.m_attributesetinstance_id)))
UNION
SELECT il.ad_client_id,
il.ad_org_id,
il.isactive,
il.created,
il.createdby,
il.updated,
il.updatedby,
'en_US'::text AS ad_language,
il.c_invoice_id,
il.c_invoiceline_id,
il.c_tax_id,
il.taxamt,
il.linetotalamt,
t.taxindicator,
(il.line + (bl.line / (100)::numeric)) AS line,
p.m_product_id,
CASE
WHEN (bl.isqtypercentage = 'N'::bpchar) THEN (il.qtyinvoiced * bl.qtybom)
ELSE (il.qtyinvoiced * (bl.qtybatch / (100)::numeric))
END AS qtyinvoiced,
CASE
WHEN (bl.isqtypercentage = 'N'::bpchar) THEN (il.qtyentered * bl.qtybom)
ELSE (il.qtyentered * (bl.qtybatch / (100)::numeric))
END AS qtyentered,
uom.uomsymbol,
p.name,
b.description,
p.documentnote,
p.upc,
p.sku,
p.value AS productvalue,
NULL::character varying AS resourcedescription,
NULL::numeric AS pricelist,
NULL::numeric AS priceenteredlist,
NULL::numeric AS discount,
NULL::numeric AS priceactual,
NULL::numeric AS priceentered,
NULL::numeric AS linenetamt,
il.m_attributesetinstance_id,
asi.m_attributeset_id,
asi.serno,
asi.lot,
asi.m_lot_id,
asi.guaranteedate,
p.description AS productdescription,
p.imageurl,
il.c_campaign_id,
il.c_project_id,
il.c_activity_id,
il.c_projectphase_id,
il.c_projecttask_id,
il.ad_orgtrx_id AS c_invoiceline_ad_orgtrx_id,
il.a_processed,
il.c_charge_id,
il.c_orderline_id,
il.c_uom_id,
il.isdescription,
il.isprinted AS c_invoiceline_isprinted,
il.m_inoutline_id,
il.m_rmaline_id,
il.pricelimit,
il.processed AS c_invoiceline_processed,
il.ref_invoiceline_id,
il.rramt,
il.rrstartdate,
il.s_resourceassignment_id,
il.user1_id AS c_invoiceline_user1_id,
il.user2_id AS c_invoiceline_user2_id,
uom.ad_org_id AS c_uom_ad_org_id,
uom.costingprecision,
uom.description AS c_uom_description,
uom.isactive AS c_uom_isactive,
uom.isdefault AS c_uom_isdefault,
uom.name AS c_uom_name,
uom.stdprecision,
uom.uomtype,
uom.x12de355,
NULL::numeric AS c_invoice_ad_org_id,
NULL::numeric AS c_invoice_ad_orgtrx_id,
NULL::numeric AS c_invoice_ad_user_id,
NULL::numeric AS c_invoice_c_activity_id,
NULL::numeric AS c_invoice_c_bpartner_id,
NULL::numeric AS c_bpartner_location_id,
NULL::numeric AS c_invoice_c_campaign_id,
NULL::numeric AS c_invoice_c_charge_id,
NULL::numeric AS c_conversiontype_id,
NULL::numeric AS c_currency_id,
NULL::numeric AS c_doctype_id,
NULL::numeric AS c_doctypetarget_id,
NULL::numeric AS c_dunninglevel_id,
NULL::numeric AS c_invoice_chargeamt,
NULL::numeric AS c_order_id,
NULL::numeric AS c_payment_id,
NULL::numeric AS c_paymentterm_id,
NULL::numeric AS c_invoice_c_project_id,
NULL::timestamp without time zone AS c_invoice_created,
NULL::numeric AS c_invoice_createdby,
NULL::timestamp without time zone AS dateacct,
NULL::timestamp without time zone AS dateinvoiced,
NULL::timestamp without time zone AS dateordered,
NULL::timestamp without time zone AS dateprinted,
NULL::character varying AS c_invoice_description,
NULL::bpchar AS docaction,
NULL::bpchar AS docstatus,
NULL::character varying AS documentno,
NULL::date AS dunninggrace,
NULL::bpchar AS generateto,
NULL::numeric AS grandtotal,
NULL::bpchar AS invoicecollectiontype,
NULL::bpchar AS c_invoice_isactive,
NULL::bpchar AS isapproved,
NULL::bpchar AS isdiscountprinted,
NULL::bpchar AS isindispute,
NULL::bpchar AS ispaid,
NULL::bpchar AS ispayschedulevalid,
NULL::bpchar AS c_invoice_isprinted,
NULL::bpchar AS c_invoice_isselfservice,
NULL::bpchar AS issotrx,
NULL::bpchar AS c_invoice_istaxincluded,
NULL::bpchar AS istransferred,
NULL::numeric AS m_pricelist_id,
NULL::numeric AS m_rma_id,
NULL::bpchar AS paymentrule,
NULL::character varying AS poreference,
NULL::bpchar AS posted,
NULL::numeric AS processedon,
NULL::bpchar AS processing,
NULL::numeric AS ref_invoice_id,
NULL::numeric AS reversal_id,
NULL::numeric AS salesrep_id,
NULL::bpchar AS sendemail,
NULL::numeric AS totallines,
NULL::timestamp without time zone AS c_invoice_updated,
NULL::numeric AS c_invoice_updatedby,
NULL::numeric AS c_invoice_user1_id,
NULL::numeric AS c_invoice_user2_id,
t.ad_org_id AS c_tax_ad_org_id,
t.ad_rule_id,
t.c_country_id,
t.c_region_id,
t.c_taxcategory_id AS c_tax_c_taxcategory_id,
t.description AS c_tax_description,
t.isactive AS c_tax_isactive,
t.isdefault AS c_tax_isdefault,
t.isdocumentlevel,
t.issalestax,
t.issummary AS c_tax_issummary,
t.istaxexempt,
t.name AS c_tax_name,
t.parent_tax_id,
t.rate,
t.requirestaxcertificate,
t.sopotype,
t.to_country_id,
t.to_region_id,
t.validfrom,
p.ad_org_id AS m_product_ad_org_id,
p.classification,
p.copyfrom AS m_product_copyfrom,
p.created AS m_product_created,
p.createdby AS m_product_createdby,
p.c_revenuerecognition_id,
p.c_subscriptiontype_id,
p.c_taxcategory_id AS m_product_c_taxcategory_id,
p.descriptionurl,
p.discontinued,
p.discontinuedat,
p.group1,
p.group2,
p.guaranteedays,
p.guaranteedaysmin,
p.help,
p.isactive AS m_product_isactive,
p.isbom,
p.isdropship,
p.isexcludeautodelivery,
p.isinvoiceprintdetails,
p.ispicklistprintdetails,
p.ispurchased,
p.isselfservice AS m_product_isselfservice,
p.issold,
p.isstocked,
p.issummary AS m_product_issummary,
p.isverified,
p.iswebstorefeatured,
p.lowlevel,
p.m_attributeset_id AS m_product_m_attributeset_id,
p.m_attributesetinstance_id AS m_product_m_asi_id,
p.m_freightcategory_id,
p.m_locator_id,
p.m_product_category_id AS m_product_m_prod_category_id,
p.processing AS m_product_processing,
p.producttype,
p.r_mailtext_id,
p.salesrep_id AS m_product_salesrep_id,
p.s_expensetype_id,
p.shelfdepth,
p.shelfheight,
p.shelfwidth,
p.s_resource_id AS m_product_s_resource_id,
p.unitsperpack,
p.unitsperpallet,
p.updated AS m_product_updated,
p.updatedby AS m_product_updatedby,
p.versionno,
p.volume,
p.weight,
NULL::numeric AS c_charge_ad_org_id,
NULL::numeric AS c_charge_c_bpartner_id,
NULL::numeric AS c_chargetype_id,
NULL::numeric AS c_charge_chargeamt,
NULL::numeric AS c_charge_c_taxcategory_id,
NULL::character varying AS c_charge_description,
NULL::bpchar AS c_charge_isactive,
NULL::bpchar AS issamecurrency,
NULL::bpchar AS issametax,
NULL::bpchar AS c_charge_istaxincluded,
NULL::numeric AS c_bp_product_ad_org_id,
NULL::numeric AS c_bp_product_c_bpartner_id,
NULL::timestamp without time zone AS c_bp_product_created,
NULL::numeric AS c_bp_product_createdby,
NULL::character varying AS c_bp_product_description,
NULL::bpchar AS c_bp_product_isactive,
NULL::bpchar AS ismanufacturer,
NULL::character varying AS manufacturer,
NULL::numeric AS qualityrating,
NULL::numeric AS shelflifemindays,
NULL::numeric AS shelflifeminpct,
NULL::timestamp without time zone AS c_bp_product_updated,
NULL::numeric AS c_bp_product_updatedby,
NULL::character varying AS vendorcategory,
NULL::numeric AS s_rassignment_ad_org_id,
NULL::timestamp without time zone AS assigndatefrom,
NULL::timestamp without time zone AS assigndateto,
NULL::timestamp without time zone AS s_rassignment_created,
NULL::numeric AS s_rassignment_createby,
NULL::bpchar AS s_rassignment_isactive,
NULL::bpchar AS isconfirmed,
NULL::character varying AS s_rassignment_name,
NULL::numeric AS s_rassignment_qty,
NULL::numeric AS s_rassignment_s_resource_id,
NULL::timestamp without time zone AS s_rassignment_updated,
NULL::numeric AS s_rassignment_updatedby,
asi.ad_org_id AS m_asi_ad_org_id,
asi.created AS m_asi_created,
asi.createdby AS m_asi_createdby,
asi.description AS m_asi_description,
asi.isactive AS m_asi_isactive,
asi.updated AS m_asi_updated,
asi.updatedby AS m_asi_updatedby
FROM (((((((pp_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'::bpchar)) AND (bp.isverified = 'Y'::bpchar)) AND (bp.isinvoiceprintdetails = 'Y'::bpchar))))
JOIN pp_product_bomline bl ON ((bl.pp_product_bom_id = b.pp_product_bom_id)))
JOIN m_product p ON ((bl.m_product_id = p.m_product_id)))
JOIN c_uom uom ON ((p.c_uom_id = uom.c_uom_id)))
LEFT JOIN c_tax t ON ((il.c_tax_id = t.c_tax_id)))
LEFT JOIN m_attributesetinstance asi ON ((il.m_attributesetinstance_id = asi.m_attributesetinstance_id)))
UNION
SELECT il.ad_client_id,
il.ad_org_id,
il.isactive,
il.created,
il.createdby,
il.updated,
il.updatedby,
'en_US'::text AS ad_language,
il.c_invoice_id,
il.c_invoiceline_id,
NULL::numeric AS c_tax_id,
NULL::numeric AS taxamt,
NULL::numeric AS linetotalamt,
NULL::character varying AS taxindicator,
il.line,
NULL::numeric AS m_product_id,
NULL::numeric AS qtyinvoiced,
NULL::numeric AS qtyentered,
NULL::character varying AS uomsymbol,
il.description AS name,
NULL::character varying AS description,
NULL::character varying AS documentnote,
NULL::character varying AS upc,
NULL::character varying AS sku,
NULL::character varying AS productvalue,
NULL::character varying AS resourcedescription,
NULL::numeric AS pricelist,
NULL::numeric AS priceenteredlist,
NULL::numeric AS discount,
NULL::numeric AS priceactual,
NULL::numeric AS priceentered,
NULL::numeric AS linenetamt,
NULL::numeric AS m_attributesetinstance_id,
NULL::numeric AS m_attributeset_id,
NULL::character varying AS serno,
NULL::character varying AS lot,
NULL::numeric AS m_lot_id,
NULL::timestamp without time zone AS guaranteedate,
NULL::character varying AS productdescription,
NULL::character varying AS imageurl,
NULL::numeric AS c_campaign_id,
NULL::numeric AS c_project_id,
NULL::numeric AS c_activity_id,
NULL::numeric AS c_projectphase_id,
NULL::numeric AS c_projecttask_id,
NULL::numeric AS c_invoiceline_ad_orgtrx_id,
NULL::bpchar AS a_processed,
NULL::numeric AS c_charge_id,
NULL::numeric AS c_orderline_id,
NULL::numeric AS c_uom_id,
NULL::bpchar AS isdescription,
NULL::bpchar AS c_invoiceline_isprinted,
NULL::numeric AS m_inoutline_id,
NULL::numeric AS m_rmaline_id,
NULL::numeric AS pricelimit,
NULL::bpchar AS c_invoiceline_processed,
NULL::numeric AS ref_invoiceline_id,
NULL::numeric AS rramt,
NULL::timestamp without time zone AS rrstartdate,
NULL::numeric AS s_resourceassignment_id,
NULL::numeric AS c_invoiceline_user1_id,
NULL::numeric AS c_invoiceline_user2_id,
NULL::numeric AS c_uom_ad_org_id,
NULL::numeric AS costingprecision,
NULL::character varying AS c_uom_description,
NULL::bpchar AS c_uom_isactive,
NULL::bpchar AS c_uom_isdefault,
NULL::character varying AS c_uom_name,
NULL::numeric AS stdprecision,
NULL::character varying AS uomtype,
NULL::character varying AS x12de355,
NULL::numeric AS c_invoice_ad_org_id,
NULL::numeric AS c_invoice_ad_orgtrx_id,
NULL::numeric AS c_invoice_ad_user_id,
NULL::numeric AS c_invoice_c_activity_id,
NULL::numeric AS c_invoice_c_bpartner_id,
NULL::numeric AS c_bpartner_location_id,
NULL::numeric AS c_invoice_c_campaign_id,
NULL::numeric AS c_invoice_c_charge_id,
NULL::numeric AS c_conversiontype_id,
NULL::numeric AS c_currency_id,
NULL::numeric AS c_doctype_id,
NULL::numeric AS c_doctypetarget_id,
NULL::numeric AS c_dunninglevel_id,
NULL::numeric AS c_invoice_chargeamt,
NULL::numeric AS c_order_id,
NULL::numeric AS c_payment_id,
NULL::numeric AS c_paymentterm_id,
NULL::numeric AS c_invoice_c_project_id,
NULL::timestamp without time zone AS c_invoice_created,
NULL::numeric AS c_invoice_createdby,
NULL::timestamp without time zone AS dateacct,
NULL::timestamp without time zone AS dateinvoiced,
NULL::timestamp without time zone AS dateordered,
NULL::timestamp without time zone AS dateprinted,
NULL::character varying AS c_invoice_description,
NULL::bpchar AS docaction,
NULL::bpchar AS docstatus,
NULL::character varying AS documentno,
NULL::date AS dunninggrace,
NULL::bpchar AS generateto,
NULL::numeric AS grandtotal,
NULL::bpchar AS invoicecollectiontype,
NULL::bpchar AS c_invoice_isactive,
NULL::bpchar AS isapproved,
NULL::bpchar AS isdiscountprinted,
NULL::bpchar AS isindispute,
NULL::bpchar AS ispaid,
NULL::bpchar AS ispayschedulevalid,
NULL::bpchar AS c_invoice_isprinted,
NULL::bpchar AS c_invoice_isselfservice,
NULL::bpchar AS issotrx,
NULL::bpchar AS c_invoice_istaxincluded,
NULL::bpchar AS istransferred,
NULL::numeric AS m_pricelist_id,
NULL::numeric AS m_rma_id,
NULL::bpchar AS paymentrule,
NULL::character varying AS poreference,
NULL::bpchar AS posted,
NULL::numeric AS processedon,
NULL::bpchar AS processing,
NULL::numeric AS ref_invoice_id,
NULL::numeric AS reversal_id,
NULL::numeric AS salesrep_id,
NULL::bpchar AS sendemail,
NULL::numeric AS totallines,
NULL::timestamp without time zone AS c_invoice_updated,
NULL::numeric AS c_invoice_updatedby,
NULL::numeric AS c_invoice_user1_id,
NULL::numeric AS c_invoice_user2_id,
NULL::numeric AS c_tax_ad_org_id,
NULL::numeric AS ad_rule_id,
NULL::numeric AS c_country_id,
NULL::numeric AS c_region_id,
NULL::numeric AS c_tax_c_taxcategory_id,
NULL::character varying AS c_tax_description,
NULL::bpchar AS c_tax_isactive,
NULL::bpchar AS c_tax_isdefault,
NULL::bpchar AS isdocumentlevel,
NULL::bpchar AS issalestax,
NULL::bpchar AS c_tax_issummary,
NULL::bpchar AS istaxexempt,
NULL::character varying AS c_tax_name,
NULL::numeric AS parent_tax_id,
NULL::numeric AS rate,
NULL::bpchar AS requirestaxcertificate,
NULL::bpchar AS sopotype,
NULL::numeric AS to_country_id,
NULL::numeric AS to_region_id,
NULL::timestamp without time zone AS validfrom,
NULL::numeric AS m_product_ad_org_id,
NULL::character varying AS classification,
NULL::bpchar AS m_product_copyfrom,
NULL::timestamp without time zone AS m_product_created,
NULL::numeric AS m_product_createdby,
NULL::numeric AS c_revenuerecognition_id,
NULL::numeric AS c_subscriptiontype_id,
NULL::numeric AS m_product_c_taxcategory_id,
NULL::character varying AS descriptionurl,
NULL::bpchar AS discontinued,
NULL::timestamp without time zone AS discontinuedat,
NULL::character varying AS group1,
NULL::character varying AS group2,
NULL::numeric AS guaranteedays,
NULL::numeric AS guaranteedaysmin,
NULL::character varying AS help,
NULL::bpchar AS m_product_isactive,
NULL::bpchar AS isbom,
NULL::bpchar AS isdropship,
NULL::bpchar AS isexcludeautodelivery,
NULL::bpchar AS isinvoiceprintdetails,
NULL::bpchar AS ispicklistprintdetails,
NULL::bpchar AS ispurchased,
NULL::bpchar AS m_product_isselfservice,
NULL::bpchar AS issold,
NULL::bpchar AS isstocked,
NULL::bpchar AS m_product_issummary,
NULL::bpchar AS isverified,
NULL::bpchar AS iswebstorefeatured,
NULL::numeric AS lowlevel,
NULL::numeric AS m_product_m_attributeset_id,
NULL::numeric AS m_product_m_asi_id,
NULL::numeric AS m_freightcategory_id,
NULL::numeric AS m_locator_id,
NULL::numeric AS m_product_m_prod_category_id,
NULL::bpchar AS m_product_processing,
NULL::bpchar AS producttype,
NULL::numeric AS r_mailtext_id,
NULL::numeric AS m_product_salesrep_id,
NULL::numeric AS s_expensetype_id,
NULL::numeric AS shelfdepth,
NULL::numeric AS shelfheight,
NULL::numeric AS shelfwidth,
NULL::numeric AS m_product_s_resource_id,
NULL::numeric AS unitsperpack,
NULL::numeric AS unitsperpallet,
NULL::timestamp without time zone AS m_product_updated,
NULL::numeric AS m_product_updatedby,
NULL::character varying AS versionno,
NULL::numeric AS volume,
NULL::numeric AS weight,
NULL::numeric AS c_charge_ad_org_id,
NULL::numeric AS c_charge_c_bpartner_id,
NULL::numeric AS c_chargetype_id,
NULL::numeric AS c_charge_chargeamt,
NULL::numeric AS c_charge_c_taxcategory_id,
NULL::character varying AS c_charge_description,
NULL::bpchar AS c_charge_isactive,
NULL::bpchar AS issamecurrency,
NULL::bpchar AS issametax,
NULL::bpchar AS c_charge_istaxincluded,
NULL::numeric AS c_bp_product_ad_org_id,
NULL::numeric AS c_bp_product_c_bpartner_id,
NULL::timestamp without time zone AS c_bp_product_created,
NULL::numeric AS c_bp_product_createdby,
NULL::character varying AS c_bp_product_description,
NULL::bpchar AS c_bp_product_isactive,
NULL::bpchar AS ismanufacturer,
NULL::character varying AS manufacturer,
NULL::numeric AS qualityrating,
NULL::numeric AS shelflifemindays,
NULL::numeric AS shelflifeminpct,
NULL::timestamp without time zone AS c_bp_product_updated,
NULL::numeric AS c_bp_product_updatedby,
NULL::character varying AS vendorcategory,
NULL::numeric AS s_rassignment_ad_org_id,
NULL::timestamp without time zone AS assigndatefrom,
NULL::timestamp without time zone AS assigndateto,
NULL::timestamp without time zone AS s_rassignment_created,
NULL::numeric AS s_rassignment_createby,
NULL::bpchar AS s_rassignment_isactive,
NULL::bpchar AS isconfirmed,
NULL::character varying AS s_rassignment_name,
NULL::numeric AS s_rassignment_qty,
NULL::numeric AS s_rassignment_s_resource_id,
NULL::timestamp without time zone AS s_rassignment_updated,
NULL::numeric AS s_rassignment_updatedby,
NULL::numeric AS m_asi_ad_org_id,
NULL::timestamp without time zone AS m_asi_created,
NULL::numeric AS m_asi_createdby,
NULL::character varying AS m_asi_description,
NULL::bpchar AS m_asi_isactive,
NULL::timestamp without time zone AS m_asi_updated,
NULL::numeric AS m_asi_updatedby
FROM c_invoiceline il
WHERE (il.c_uom_id IS NULL)
UNION
SELECT i.ad_client_id,
i.ad_org_id,
i.isactive,
i.created,
i.createdby,
i.updated,
i.updatedby,
'en_US'::text AS ad_language,
i.c_invoice_id,
NULL::numeric AS c_invoiceline_id,
NULL::numeric AS c_tax_id,
NULL::numeric AS taxamt,
NULL::numeric AS linetotalamt,
NULL::character varying AS taxindicator,
999998 AS line,
NULL::numeric AS m_product_id,
NULL::numeric AS qtyinvoiced,
NULL::numeric AS qtyentered,
NULL::character varying AS uomsymbol,
NULL::character varying AS name,
NULL::character varying AS description,
NULL::character varying AS documentnote,
NULL::character varying AS upc,
NULL::character varying AS sku,
NULL::character varying AS productvalue,
NULL::character varying AS resourcedescription,
NULL::numeric AS pricelist,
NULL::numeric AS priceenteredlist,
NULL::numeric AS discount,
NULL::numeric AS priceactual,
NULL::numeric AS priceentered,
NULL::numeric AS linenetamt,
NULL::numeric AS m_attributesetinstance_id,
NULL::numeric AS m_attributeset_id,
NULL::character varying AS serno,
NULL::character varying AS lot,
NULL::numeric AS m_lot_id,
NULL::timestamp without time zone AS guaranteedate,
NULL::character varying AS productdescription,
NULL::character varying AS imageurl,
NULL::numeric AS c_campaign_id,
NULL::numeric AS c_project_id,
NULL::numeric AS c_activity_id,
NULL::numeric AS c_projectphase_id,
NULL::numeric AS c_projecttask_id,
NULL::numeric AS c_invoiceline_ad_orgtrx_id,
NULL::bpchar AS a_processed,
NULL::numeric AS c_charge_id,
NULL::numeric AS c_orderline_id,
NULL::numeric AS c_uom_id,
NULL::bpchar AS isdescription,
NULL::bpchar AS c_invoiceline_isprinted,
NULL::numeric AS m_inoutline_id,
NULL::numeric AS m_rmaline_id,
NULL::numeric AS pricelimit,
NULL::bpchar AS c_invoiceline_processed,
NULL::numeric AS ref_invoiceline_id,
NULL::numeric AS rramt,
NULL::timestamp without time zone AS rrstartdate,
NULL::numeric AS s_resourceassignment_id,
NULL::numeric AS c_invoiceline_user1_id,
NULL::numeric AS c_invoiceline_user2_id,
NULL::numeric AS c_uom_ad_org_id,
NULL::numeric AS costingprecision,
NULL::character varying AS c_uom_description,
NULL::bpchar AS c_uom_isactive,
NULL::bpchar AS c_uom_isdefault,
NULL::character varying AS c_uom_name,
NULL::numeric AS stdprecision,
NULL::character varying AS uomtype,
NULL::character varying AS x12de355,
NULL::numeric AS c_invoice_ad_org_id,
NULL::numeric AS c_invoice_ad_orgtrx_id,
NULL::numeric AS c_invoice_ad_user_id,
NULL::numeric AS c_invoice_c_activity_id,
NULL::numeric AS c_invoice_c_bpartner_id,
NULL::numeric AS c_bpartner_location_id,
NULL::numeric AS c_invoice_c_campaign_id,
NULL::numeric AS c_invoice_c_charge_id,
NULL::numeric AS c_conversiontype_id,
NULL::numeric AS c_currency_id,
NULL::numeric AS c_doctype_id,
NULL::numeric AS c_doctypetarget_id,
NULL::numeric AS c_dunninglevel_id,
NULL::numeric AS c_invoice_chargeamt,
NULL::numeric AS c_order_id,
NULL::numeric AS c_payment_id,
NULL::numeric AS c_paymentterm_id,
NULL::numeric AS c_invoice_c_project_id,
NULL::timestamp without time zone AS c_invoice_created,
NULL::numeric AS c_invoice_createdby,
NULL::timestamp without time zone AS dateacct,
NULL::timestamp without time zone AS dateinvoiced,
NULL::timestamp without time zone AS dateordered,
NULL::timestamp without time zone AS dateprinted,
NULL::character varying AS c_invoice_description,
NULL::bpchar AS docaction,
NULL::bpchar AS docstatus,
NULL::character varying AS documentno,
NULL::date AS dunninggrace,
NULL::bpchar AS generateto,
NULL::numeric AS grandtotal,
NULL::bpchar AS invoicecollectiontype,
NULL::bpchar AS c_invoice_isactive,
NULL::bpchar AS isapproved,
NULL::bpchar AS isdiscountprinted,
NULL::bpchar AS isindispute,
NULL::bpchar AS ispaid,
NULL::bpchar AS ispayschedulevalid,
NULL::bpchar AS c_invoice_isprinted,
NULL::bpchar AS c_invoice_isselfservice,
NULL::bpchar AS issotrx,
NULL::bpchar AS c_invoice_istaxincluded,
NULL::bpchar AS istransferred,
NULL::numeric AS m_pricelist_id,
NULL::numeric AS m_rma_id,
NULL::bpchar AS paymentrule,
NULL::character varying AS poreference,
NULL::bpchar AS posted,
NULL::numeric AS processedon,
NULL::bpchar AS processing,
NULL::numeric AS ref_invoice_id,
NULL::numeric AS reversal_id,
NULL::numeric AS salesrep_id,
NULL::bpchar AS sendemail,
NULL::numeric AS totallines,
NULL::timestamp without time zone AS c_invoice_updated,
NULL::numeric AS c_invoice_updatedby,
NULL::numeric AS c_invoice_user1_id,
NULL::numeric AS c_invoice_user2_id,
NULL::numeric AS c_tax_ad_org_id,
NULL::numeric AS ad_rule_id,
NULL::numeric AS c_country_id,
NULL::numeric AS c_region_id,
NULL::numeric AS c_tax_c_taxcategory_id,
NULL::character varying AS c_tax_description,
NULL::bpchar AS c_tax_isactive,
NULL::bpchar AS c_tax_isdefault,
NULL::bpchar AS isdocumentlevel,
NULL::bpchar AS issalestax,
NULL::bpchar AS c_tax_issummary,
NULL::bpchar AS istaxexempt,
NULL::character varying AS c_tax_name,
NULL::numeric AS parent_tax_id,
NULL::numeric AS rate,
NULL::bpchar AS requirestaxcertificate,
NULL::bpchar AS sopotype,
NULL::numeric AS to_country_id,
NULL::numeric AS to_region_id,
NULL::timestamp without time zone AS validfrom,
NULL::numeric AS m_product_ad_org_id,
NULL::character varying AS classification,
NULL::bpchar AS m_product_copyfrom,
NULL::timestamp without time zone AS m_product_created,
NULL::numeric AS m_product_createdby,
NULL::numeric AS c_revenuerecognition_id,
NULL::numeric AS c_subscriptiontype_id,
NULL::numeric AS m_product_c_taxcategory_id,
NULL::character varying AS descriptionurl,
NULL::bpchar AS discontinued,
NULL::timestamp without time zone AS discontinuedat,
NULL::character varying AS group1,
NULL::character varying AS group2,
NULL::numeric AS guaranteedays,
NULL::numeric AS guaranteedaysmin,
NULL::character varying AS help,
NULL::bpchar AS m_product_isactive,
NULL::bpchar AS isbom,
NULL::bpchar AS isdropship,
NULL::bpchar AS isexcludeautodelivery,
NULL::bpchar AS isinvoiceprintdetails,
NULL::bpchar AS ispicklistprintdetails,
NULL::bpchar AS ispurchased,
NULL::bpchar AS m_product_isselfservice,
NULL::bpchar AS issold,
NULL::bpchar AS isstocked,
NULL::bpchar AS m_product_issummary,
NULL::bpchar AS isverified,
NULL::bpchar AS iswebstorefeatured,
NULL::numeric AS lowlevel,
NULL::numeric AS m_product_m_attributeset_id,
NULL::numeric AS m_product_m_asi_id,
NULL::numeric AS m_freightcategory_id,
NULL::numeric AS m_locator_id,
NULL::numeric AS m_product_m_prod_category_id,
NULL::bpchar AS m_product_processing,
NULL::bpchar AS producttype,
NULL::numeric AS r_mailtext_id,
NULL::numeric AS m_product_salesrep_id,
NULL::numeric AS s_expensetype_id,
NULL::numeric AS shelfdepth,
NULL::numeric AS shelfheight,
NULL::numeric AS shelfwidth,
NULL::numeric AS m_product_s_resource_id,
NULL::numeric AS unitsperpack,
NULL::numeric AS unitsperpallet,
NULL::timestamp without time zone AS m_product_updated,
NULL::numeric AS m_product_updatedby,
NULL::character varying AS versionno,
NULL::numeric AS volume,
NULL::numeric AS weight,
NULL::numeric AS c_charge_ad_org_id,
NULL::numeric AS c_charge_c_bpartner_id,
NULL::numeric AS c_chargetype_id,
NULL::numeric AS c_charge_chargeamt,
NULL::numeric AS c_charge_c_taxcategory_id,
NULL::character varying AS c_charge_description,
NULL::bpchar AS c_charge_isactive,
NULL::bpchar AS issamecurrency,
NULL::bpchar AS issametax,
NULL::bpchar AS c_charge_istaxincluded,
NULL::numeric AS c_bp_product_ad_org_id,
NULL::numeric AS c_bp_product_c_bpartner_id,
NULL::timestamp without time zone AS c_bp_product_created,
NULL::numeric AS c_bp_product_createdby,
NULL::character varying AS c_bp_product_description,
NULL::bpchar AS c_bp_product_isactive,
NULL::bpchar AS ismanufacturer,
NULL::character varying AS manufacturer,
NULL::numeric AS qualityrating,
NULL::numeric AS shelflifemindays,
NULL::numeric AS shelflifeminpct,
NULL::timestamp without time zone AS c_bp_product_updated,
NULL::numeric AS c_bp_product_updatedby,
NULL::character varying AS vendorcategory,
NULL::numeric AS s_rassignment_ad_org_id,
NULL::timestamp without time zone AS assigndatefrom,
NULL::timestamp without time zone AS assigndateto,
NULL::timestamp without time zone AS s_rassignment_created,
NULL::numeric AS s_rassignment_createby,
NULL::bpchar AS s_rassignment_isactive,
NULL::bpchar AS isconfirmed,
NULL::character varying AS s_rassignment_name,
NULL::numeric AS s_rassignment_qty,
NULL::numeric AS s_rassignment_s_resource_id,
NULL::timestamp without time zone AS s_rassignment_updated,
NULL::numeric AS s_rassignment_updatedby,
NULL::numeric AS m_asi_ad_org_id,
NULL::timestamp without time zone AS m_asi_created,
NULL::numeric AS m_asi_createdby,
NULL::character varying AS m_asi_description,
NULL::bpchar AS m_asi_isactive,
NULL::timestamp without time zone AS m_asi_updated,
NULL::numeric AS m_asi_updatedby
FROM c_invoice i
UNION
SELECT it.ad_client_id,
it.ad_org_id,
it.isactive,
it.created,
it.createdby,
it.updated,
it.updatedby,
'en_US'::text AS ad_language,
it.c_invoice_id,
NULL::numeric AS c_invoiceline_id,
it.c_tax_id,
NULL::numeric AS taxamt,
NULL::numeric AS linetotalamt,
t.taxindicator,
999999 AS line,
NULL::numeric AS m_product_id,
NULL::numeric AS qtyinvoiced,
NULL::numeric AS qtyentered,
NULL::character varying AS uomsymbol,
t.name,
NULL::character varying AS description,
NULL::character varying AS documentnote,
NULL::character varying AS upc,
NULL::character varying AS sku,
NULL::character varying AS productvalue,
NULL::character varying AS resourcedescription,
NULL::numeric AS pricelist,
NULL::numeric AS priceenteredlist,
NULL::numeric AS discount,
CASE
WHEN (it.istaxincluded = 'Y'::bpchar) THEN it.taxamt
ELSE it.taxbaseamt
END AS priceactual,
CASE
WHEN (it.istaxincluded = 'Y'::bpchar) THEN it.taxamt
ELSE it.taxbaseamt
END AS priceentered,
CASE
WHEN (it.istaxincluded = 'Y'::bpchar) THEN NULL::numeric
ELSE it.taxamt
END AS linenetamt,
NULL::numeric AS m_attributesetinstance_id,
NULL::numeric AS m_attributeset_id,
NULL::character varying AS serno,
NULL::character varying AS lot,
NULL::numeric AS m_lot_id,
NULL::timestamp without time zone AS guaranteedate,
NULL::character varying AS productdescription,
NULL::character varying AS imageurl,
NULL::numeric AS c_campaign_id,
NULL::numeric AS c_project_id,
NULL::numeric AS c_activity_id,
NULL::numeric AS c_projectphase_id,
NULL::numeric AS c_projecttask_id,
NULL::numeric AS c_invoiceline_ad_orgtrx_id,
NULL::bpchar AS a_processed,
NULL::numeric AS c_charge_id,
NULL::numeric AS c_orderline_id,
NULL::numeric AS c_uom_id,
NULL::bpchar AS isdescription,
NULL::bpchar AS c_invoiceline_isprinted,
NULL::numeric AS m_inoutline_id,
NULL::numeric AS m_rmaline_id,
NULL::numeric AS pricelimit,
NULL::bpchar AS c_invoiceline_processed,
NULL::numeric AS ref_invoiceline_id,
NULL::numeric AS rramt,
NULL::timestamp without time zone AS rrstartdate,
NULL::numeric AS s_resourceassignment_id,
NULL::numeric AS c_invoiceline_user1_id,
NULL::numeric AS c_invoiceline_user2_id,
NULL::numeric AS c_uom_ad_org_id,
NULL::numeric AS costingprecision,
NULL::character varying AS c_uom_description,
NULL::bpchar AS c_uom_isactive,
NULL::bpchar AS c_uom_isdefault,
NULL::character varying AS c_uom_name,
NULL::numeric AS stdprecision,
NULL::character varying AS uomtype,
NULL::character varying AS x12de355,
NULL::numeric AS c_invoice_ad_org_id,
NULL::numeric AS c_invoice_ad_orgtrx_id,
NULL::numeric AS c_invoice_ad_user_id,
NULL::numeric AS c_invoice_c_activity_id,
NULL::numeric AS c_invoice_c_bpartner_id,
NULL::numeric AS c_bpartner_location_id,
NULL::numeric AS c_invoice_c_campaign_id,
NULL::numeric AS c_invoice_c_charge_id,
NULL::numeric AS c_conversiontype_id,
NULL::numeric AS c_currency_id,
NULL::numeric AS c_doctype_id,
NULL::numeric AS c_doctypetarget_id,
NULL::numeric AS c_dunninglevel_id,
NULL::numeric AS c_invoice_chargeamt,
NULL::numeric AS c_order_id,
NULL::numeric AS c_payment_id,
NULL::numeric AS c_paymentterm_id,
NULL::numeric AS c_invoice_c_project_id,
NULL::timestamp without time zone AS c_invoice_created,
NULL::numeric AS c_invoice_createdby,
NULL::timestamp without time zone AS dateacct,
NULL::timestamp without time zone AS dateinvoiced,
NULL::timestamp without time zone AS dateordered,
NULL::timestamp without time zone AS dateprinted,
NULL::character varying AS c_invoice_description,
NULL::bpchar AS docaction,
NULL::bpchar AS docstatus,
NULL::character varying AS documentno,
NULL::date AS dunninggrace,
NULL::bpchar AS generateto,
NULL::numeric AS grandtotal,
NULL::bpchar AS invoicecollectiontype,
NULL::bpchar AS c_invoice_isactive,
NULL::bpchar AS isapproved,
NULL::bpchar AS isdiscountprinted,
NULL::bpchar AS isindispute,
NULL::bpchar AS ispaid,
NULL::bpchar AS ispayschedulevalid,
NULL::bpchar AS c_invoice_isprinted,
NULL::bpchar AS c_invoice_isselfservice,
NULL::bpchar AS issotrx,
NULL::bpchar AS c_invoice_istaxincluded,
NULL::bpchar AS istransferred,
NULL::numeric AS m_pricelist_id,
NULL::numeric AS m_rma_id,
NULL::bpchar AS paymentrule,
NULL::character varying AS poreference,
NULL::bpchar AS posted,
NULL::numeric AS processedon,
NULL::bpchar AS processing,
NULL::numeric AS ref_invoice_id,
NULL::numeric AS reversal_id,
NULL::numeric AS salesrep_id,
NULL::bpchar AS sendemail,
NULL::numeric AS totallines,
NULL::timestamp without time zone AS c_invoice_updated,
NULL::numeric AS c_invoice_updatedby,
NULL::numeric AS c_invoice_user1_id,
NULL::numeric AS c_invoice_user2_id,
NULL::numeric AS c_tax_ad_org_id,
NULL::numeric AS ad_rule_id,
NULL::numeric AS c_country_id,
NULL::numeric AS c_region_id,
NULL::numeric AS c_tax_c_taxcategory_id,
NULL::character varying AS c_tax_description,
NULL::bpchar AS c_tax_isactive,
NULL::bpchar AS c_tax_isdefault,
NULL::bpchar AS isdocumentlevel,
NULL::bpchar AS issalestax,
NULL::bpchar AS c_tax_issummary,
NULL::bpchar AS istaxexempt,
NULL::character varying AS c_tax_name,
NULL::numeric AS parent_tax_id,
NULL::numeric AS rate,
NULL::bpchar AS requirestaxcertificate,
NULL::bpchar AS sopotype,
NULL::numeric AS to_country_id,
NULL::numeric AS to_region_id,
NULL::timestamp without time zone AS validfrom,
NULL::numeric AS m_product_ad_org_id,
NULL::character varying AS classification,
NULL::bpchar AS m_product_copyfrom,
NULL::timestamp without time zone AS m_product_created,
NULL::numeric AS m_product_createdby,
NULL::numeric AS c_revenuerecognition_id,
NULL::numeric AS c_subscriptiontype_id,
NULL::numeric AS m_product_c_taxcategory_id,
NULL::character varying AS descriptionurl,
NULL::bpchar AS discontinued,
NULL::timestamp without time zone AS discontinuedat,
NULL::character varying AS group1,
NULL::character varying AS group2,
NULL::numeric AS guaranteedays,
NULL::numeric AS guaranteedaysmin,
NULL::character varying AS help,
NULL::bpchar AS m_product_isactive,
NULL::bpchar AS isbom,
NULL::bpchar AS isdropship,
NULL::bpchar AS isexcludeautodelivery,
NULL::bpchar AS isinvoiceprintdetails,
NULL::bpchar AS ispicklistprintdetails,
NULL::bpchar AS ispurchased,
NULL::bpchar AS m_product_isselfservice,
NULL::bpchar AS issold,
NULL::bpchar AS isstocked,
NULL::bpchar AS m_product_issummary,
NULL::bpchar AS isverified,
NULL::bpchar AS iswebstorefeatured,
NULL::numeric AS lowlevel,
NULL::numeric AS m_product_m_attributeset_id,
NULL::numeric AS m_product_m_asi_id,
NULL::numeric AS m_freightcategory_id,
NULL::numeric AS m_locator_id,
NULL::numeric AS m_product_m_prod_category_id,
NULL::bpchar AS m_product_processing,
NULL::bpchar AS producttype,
NULL::numeric AS r_mailtext_id,
NULL::numeric AS m_product_salesrep_id,
NULL::numeric AS s_expensetype_id,
NULL::numeric AS shelfdepth,
NULL::numeric AS shelfheight,
NULL::numeric AS shelfwidth,
NULL::numeric AS m_product_s_resource_id,
NULL::numeric AS unitsperpack,
NULL::numeric AS unitsperpallet,
NULL::timestamp without time zone AS m_product_updated,
NULL::numeric AS m_product_updatedby,
NULL::character varying AS versionno,
NULL::numeric AS volume,
NULL::numeric AS weight,
NULL::numeric AS c_charge_ad_org_id,
NULL::numeric AS c_charge_c_bpartner_id,
NULL::numeric AS c_chargetype_id,
NULL::numeric AS c_charge_chargeamt,
NULL::numeric AS c_charge_c_taxcategory_id,
NULL::character varying AS c_charge_description,
NULL::bpchar AS c_charge_isactive,
NULL::bpchar AS issamecurrency,
NULL::bpchar AS issametax,
NULL::bpchar AS c_charge_istaxincluded,
NULL::numeric AS c_bp_product_ad_org_id,
NULL::numeric AS c_bp_product_c_bpartner_id,
NULL::timestamp without time zone AS c_bp_product_created,
NULL::numeric AS c_bp_product_createdby,
NULL::character varying AS c_bp_product_description,
NULL::bpchar AS c_bp_product_isactive,
NULL::bpchar AS ismanufacturer,
NULL::character varying AS manufacturer,
NULL::numeric AS qualityrating,
NULL::numeric AS shelflifemindays,
NULL::numeric AS shelflifeminpct,
NULL::timestamp without time zone AS c_bp_product_updated,
NULL::numeric AS c_bp_product_updatedby,
NULL::character varying AS vendorcategory,
NULL::numeric AS s_rassignment_ad_org_id,
NULL::timestamp without time zone AS assigndatefrom,
NULL::timestamp without time zone AS assigndateto,
NULL::timestamp without time zone AS s_rassignment_created,
NULL::numeric AS s_rassignment_createby,
NULL::bpchar AS s_rassignment_isactive,
NULL::bpchar AS isconfirmed,
NULL::character varying AS s_rassignment_name,
NULL::numeric AS s_rassignment_qty,
NULL::numeric AS s_rassignment_s_resource_id,
NULL::timestamp without time zone AS s_rassignment_updated,
NULL::numeric AS s_rassignment_updatedby,
NULL::numeric AS m_asi_ad_org_id,
NULL::timestamp without time zone AS m_asi_created,
NULL::numeric AS m_asi_createdby,
NULL::character varying AS m_asi_description,
NULL::bpchar AS m_asi_isactive,
NULL::timestamp without time zone AS m_asi_updated,
NULL::numeric AS m_asi_updatedby
FROM (c_invoicetax it
JOIN c_tax t ON ((it.c_tax_id = t.c_tax_id)));