Costing Recalculation Project Cloudempiere
From iDempiere en
INTRO
our solution designed allow run on scheduler server, without any touch-intelligent way found when to re-calculate costing records. we have users with habit backdating and re-activating documents, which make quite huge issue with processing order. in worst case, we can run eg 1 month for all material transactions.
example
company period is definitely closed till 31.12.2022. jan, feb closed for document level, the users re-activate or backdating documents to feb 10. so the product cost issue known the scope, and a nightly scheduler convert them by process to product cost queue, take them delete facts, costing from feb 10 to now() then re-submit by accounting engine (invoked by costing recalculation process). the documents are posted sequentially based on view ordering by document processing date or regular document date.
components
- window product cost issue - collect by re-activation, backdating documents which are broke average - driven by event handler
- window product cost queue - when process running, able to collect products eg. all products, un-posted document products, product cost issues, can be extended, they are postgres procedures.
- process costing recalculation. this process when starting . First check period statuses when, closed or not document control closed, then exit. Load all products to hasmap, collect all related products from same documents, then uses rv_document_costing view to take all documents as input.
parameters
- date column: document date/docunent processing date
- schema
- Cost Queue Insert Type> none, all products, un-posted document products, product cost issues
some significant changes affect the core
- schema includes: inventory method: Only Costing (no facts), Periodic (no material facts), Perpetual (costing, accounting - this is legacy)
- we are calculating based on costing records instead of posting (because inventory method). this allow run costing in companies where no posting persisted.
- movementdate field added added to m_costdetail - used for calculate costamt to date
- we added m_transaction_id to m_costdetail_id - allow easier work (not sure required
- we also implement document close level allowing recalculate costing backward in running or previous not audited period.
- product without cost report: IMPORTANT. most of problems coming from this issue.
- report inventory valuation - improved for Average PO.
required changes (IMPORTANT)
- refactor costing and posting classes. atm our process has copy of costing recalculation
We have still lot of questions, mainly what is easier, make a complex costing documentation, make refactor in core, instead developing very complex recalculation process - however this solution is still LIFESAVER.
1 DROP VIEW IF EXISTS rv_document_costing CASCADE;
2
3CREATE OR REPLACE VIEW rv_document_costing
4(
5 ad_client_id,
6 ad_org_id,
7 created,
8 updated,
9 createdby,
10 updatedby,
11 isactive,
12 issotrx,
13 record_id,
14 m_inout_id,
15 c_order_id,
16 c_invoice_id,
17 qty,
18 movementqty,
19 m_product_id,
20 c_uom_id,
21 documentno,
22 datedoc,
23 processingdate,
24 daysdiff,
25 nextprocessingdate,
26 m_attributesetinstance_id,
27 m_movement_id,
28 m_inventory_id,
29 m_inoutline_id,
30 c_orderline_id,
31 c_invoiceline_id,
32 m_matchinv_id,
33 m_inventoryline_id,
34 m_movementline_id,
35 m_matchpo_id,
36 m_productionline_id,
37 ad_table_id,
38 ad_table_line_id,
39 amount,
40 movementtype,
41 processingorder,
42 docbasetype,
43 docsubtype,
44 c_doctype_id,
45 posted,
46 backdated,
47 docdatesseq,
48 creatingseq,
49 procdateseq,
50 dbegbal_qty,
51 dendbal_qty,
52 cbegbal_qty,
53 cendbal_qty,
54 pbegbal_qty,
55 pendbal_qty
56)
57AS
58 SELECT dcc.ad_client_id,
59 dcc.ad_org_id,
60 dcc.created,
61 dcc.updated,
62 dcc.createdby,
63 dcc.updatedby,
64 dcc.isactive,
65 dcc.issotrx,
66 dcc.record_id,
67 dcc.m_inout_id,
68 dcc.c_order_id,
69 dcc.c_invoice_id,
70 dcc.qty,
71 dcc.movementqty,
72 dcc.m_product_id,
73 dcc.c_uom_id,
74 dcc.documentno,
75 dcc.datedoc,
76 dcc.processingdate::timestamp without time zone AS processingdate,
77 dcc.daysdiff,
78 lead(dcc.processingdate::timestamp without time zone) OVER (PARTITION BY dcc.m_product_id ORDER BY dcc.procdateseq) AS nextprocessingdate,
79 dcc.m_attributesetinstance_id,
80 dcc.m_movement_id,
81 dcc.m_inventory_id,
82 dcc.m_inoutline_id,
83 dcc.c_orderline_id,
84 dcc.c_invoiceline_id,
85 dcc.m_matchinv_id,
86 dcc.m_inventoryline_id,
87 dcc.m_movementline_id,
88 dcc.m_matchpo_id,
89 dcc.m_productionline_id,
90 dcc.ad_table_id,
91 dcc.ad_table_line_id,
92 dcc.amount,
93 dcc.movementtype,
94 dcc.processingorder,
95 dcc.docbasetype,
96 dcc.docsubtype,
97 dcc.c_doctype_id,
98 dcc.posted,
99 dcc.backdated,
100 dcc.docdatesseq,
101 dcc.creatingseq,
102 row_number() OVER (PARTITION BY dcc.m_product_id ORDER BY (dcc.processingdate::date), dcc.processingorder, dcc.record_id) AS procdateseq,
103 dcc.dbegbal_qty,
104 dcc.dendbal_qty,
105 dcc.cbegbal_qty,
106 dcc.cendbal_qty,
107 sum(dcc.movementqty) OVER (PARTITION BY dcc.m_product_id ORDER BY (dcc.processingdate::date), dcc.processingorder, dcc.record_id) - dcc.movementqty AS pbegbal_qty,
108 sum(dcc.movementqty) OVER (PARTITION BY dcc.m_product_id ORDER BY (dcc.processingdate::date), dcc.processingorder, dcc.record_id) AS pendbal_qty
109 FROM ( SELECT dcf.ad_client_id,
110 dcf.ad_org_id,
111 dcf.created,
112 dcf.updated,
113 dcf.createdby,
114 dcf.updatedby,
115 dcf.isactive,
116 dcf.issotrx,
117 dcf.record_id,
118 dcf.m_inout_id,
119 dcf.c_order_id,
120 dcf.c_invoice_id,
121 dcf.qty,
122 dcf.movementqty,
123 dcf.m_product_id,
124 dcf.c_uom_id,
125 dcf.documentno,
126 dcf.datedoc,
127 CASE
128 WHEN dcf.pendbal_qty < 0::numeric THEN
129 CASE
130 WHEN lead(dcf.pendbal_qty) OVER (PARTITION BY dcf.m_product_id ORDER BY dcf.procdateseq) >= 0::numeric THEN lead(dcf.processingdate) OVER (PARTITION BY dcf.m_product_id ORDER BY dcf.procdateseq)
131 WHEN lead(dcf.pendbal_qty, 2) OVER (PARTITION BY dcf.m_product_id ORDER BY dcf.procdateseq) >= 0::numeric THEN lead(dcf.processingdate, 2) OVER (PARTITION BY dcf.m_product_id ORDER BY dcf.procdateseq)
132 WHEN lead(dcf.pendbal_qty, 3) OVER (PARTITION BY dcf.m_product_id ORDER BY dcf.procdateseq) >= 0::numeric THEN lead(dcf.processingdate, 3) OVER (PARTITION BY dcf.m_product_id ORDER BY dcf.procdateseq)
133 WHEN lead(dcf.pendbal_qty, 4) OVER (PARTITION BY dcf.m_product_id ORDER BY dcf.procdateseq) >= 0::numeric THEN lead(dcf.processingdate, 4) OVER (PARTITION BY dcf.m_product_id ORDER BY dcf.procdateseq)
134 WHEN lead(dcf.pendbal_qty, 5) OVER (PARTITION BY dcf.m_product_id ORDER BY dcf.procdateseq) >= 0::numeric THEN lead(dcf.processingdate, 5) OVER (PARTITION BY dcf.m_product_id ORDER BY dcf.procdateseq)
135 ELSE lead(dcf.processingdate, 6) OVER (PARTITION BY dcf.m_product_id ORDER BY dcf.procdateseq)
136 END
137 ELSE dcf.processingdate
138 END AS processingdate,
139 dcf.daysdiff,
140 lead(dcf.processingdate) OVER (PARTITION BY dcf.m_product_id ORDER BY dcf.procdateseq) AS nextprocessingdate,
141 dcf.m_attributesetinstance_id,
142 dcf.m_movement_id,
143 dcf.m_inventory_id,
144 dcf.m_inoutline_id,
145 dcf.c_orderline_id,
146 dcf.c_invoiceline_id,
147 dcf.m_matchinv_id,
148 dcf.m_inventoryline_id,
149 dcf.m_movementline_id,
150 dcf.m_matchpo_id,
151 dcf.m_productionline_id,
152 dcf.ad_table_id,
153 dcf.ad_table_line_id,
154 dcf.amount,
155 dcf.movementtype,
156 dcf.processingorder,
157 dcf.docbasetype,
158 dcf.docsubtype,
159 dcf.c_doctype_id,
160 dcf.posted,
161 dcf.backdated,
162 dcf.docdatesseq,
163 dcf.creatingseq,
164 dcf.procdateseq,
165 dcf.dbegbal_qty,
166 dcf.dendbal_qty,
167 dcf.cbegbal_qty,
168 dcf.cendbal_qty,
169 dcf.pendbal_qty
170 FROM ( SELECT dc.ad_client_id,
171 dc.ad_org_id,
172 dc.created,
173 dc.updated,
174 dc.createdby,
175 dc.updatedby,
176 dc.isactive,
177 dc.issotrx,
178 dc.record_id,
179 dc.m_inout_id,
180 dc.c_order_id,
181 dc.c_invoice_id,
182 dc.qty,
183 dc.movementqty,
184 dc.m_product_id,
185 dc.c_uom_id,
186 dc.documentno,
187 dc.datedoc,
188 dc.processingdate,
189 dc.daysdiff,
190 dc.m_attributesetinstance_id,
191 dc.m_movement_id,
192 dc.m_inventory_id,
193 dc.m_inoutline_id,
194 dc.c_orderline_id,
195 dc.c_invoiceline_id,
196 dc.m_matchinv_id,
197 dc.m_inventoryline_id,
198 dc.m_movementline_id,
199 dc.m_matchpo_id,
200 dc.m_productionline_id,
201 dc.ad_table_id,
202 dc.ad_table_line_id,
203 dc.amount,
204 dc.movementtype,
205 dc.processingorder,
206 dc.docbasetype,
207 dc.docsubtype,
208 dc.c_doctype_id,
209 dc.posted,
210 dc.backdated,
211 row_number() OVER (PARTITION BY dc.m_product_id ORDER BY (dc.datedoc::date), dc.processingorder, dc.record_id) AS docdatesseq,
212 row_number() OVER (PARTITION BY dc.m_product_id ORDER BY (dc.created::date), dc.processingorder, dc.record_id) AS creatingseq,
213 row_number() OVER (PARTITION BY dc.m_product_id ORDER BY (dc.processingdate::date), dc.processingorder, dc.record_id) AS procdateseq,
214 sum(dc.movementqty) OVER (PARTITION BY dc.m_product_id ORDER BY (dc.created::date), dc.processingorder, dc.record_id) - dc.movementqty AS cbegbal_qty,
215 sum(dc.movementqty) OVER (PARTITION BY dc.m_product_id ORDER BY (dc.created::date), dc.processingorder, dc.record_id) AS cendbal_qty,
216 sum(dc.movementqty) OVER (PARTITION BY dc.m_product_id ORDER BY (dc.datedoc::date), dc.processingorder, dc.record_id) AS dendbal_qty,
217 sum(dc.movementqty) OVER (PARTITION BY dc.m_product_id ORDER BY (dc.datedoc::date), dc.processingorder, dc.record_id) - dc.movementqty AS dbegbal_qty,
218 sum(dc.movementqty) OVER (PARTITION BY dc.m_product_id ORDER BY (dc.processingdate::date), dc.processingorder, dc.record_id) AS pendbal_qty
219 FROM ( SELECT iol.ad_client_id,
220 io.ad_org_id,
221 io.created,
222 io.updated,
223 io.createdby,
224 io.updatedby,
225 io.isactive,
226 io.issotrx,
227 iol.m_inoutline_id AS record_id,
228 iol.m_inout_id,
229 0 AS c_order_id,
230 0 AS c_invoice_id,
231 iol.movementqty AS qty,
232 CASE
233 WHEN d.docbasetype = 'MMR'::bpchar AND io.issotrx = 'N'::bpchar AND iol.movementqty >= 0::numeric THEN iol.movementqty
234 WHEN d.docbasetype = 'MMR'::bpchar AND io.issotrx = 'Y'::bpchar AND iol.movementqty >= 0::numeric THEN iol.movementqty
235 WHEN d.docbasetype = 'MMR'::bpchar AND io.issotrx = 'Y'::bpchar AND iol.movementqty < 0::numeric THEN iol.movementqty
236 WHEN d.docbasetype = 'MMR'::bpchar AND io.issotrx = 'N'::bpchar AND iol.movementqty < 0::numeric THEN iol.movementqty
237 WHEN d.docbasetype = 'MMS'::bpchar AND io.issotrx = 'N'::bpchar AND iol.movementqty >= 0::numeric THEN - iol.movementqty
238 WHEN d.docbasetype = 'MMS'::bpchar AND io.issotrx = 'Y'::bpchar AND iol.movementqty >= 0::numeric THEN - iol.movementqty
239 WHEN d.docbasetype = 'MMS'::bpchar AND io.issotrx = 'Y'::bpchar AND iol.movementqty < 0::numeric THEN - iol.movementqty
240 WHEN d.docbasetype = 'MMS'::bpchar AND io.issotrx = 'N'::bpchar AND iol.movementqty < 0::numeric THEN - iol.movementqty
241 ELSE iol.movementqty
242 END AS movementqty,
243 iol.m_product_id,
244 iol.c_uom_id,
245 io.documentno,
246 io.movementdate AS datedoc,
247 CASE
248 WHEN d.docbasetype = 'MMR'::bpchar AND io.issotrx = 'N'::bpchar AND iol.movementqty >= 0::numeric THEN io.movementdate::timestamp with time zone
249 WHEN d.docbasetype = 'MMR'::bpchar AND io.issotrx = 'Y'::bpchar AND iol.movementqty >= 0::numeric THEN io.movementdate::timestamp with time zone
250 ELSE trunc(to_timestamp((io.processedon / 1000::numeric)::double precision), 'DD'::character varying)
251 END AS processingdate,
252 to_char(trunc(to_timestamp((io.processedon / 1000::numeric)::double precision), 'DD'::character varying) - io.movementdate::timestamp with time zone, 'DD'::text)::integer AS daysdiff,
253 iol.m_attributesetinstance_id,
254 0 AS m_movement_id,
255 0 AS m_inventory_id,
256 iol.m_inoutline_id,
257 0 AS c_orderline_id,
258 0 AS c_invoiceline_id,
259 0 AS m_matchinv_id,
260 0 AS m_inventoryline_id,
261 0 AS m_movementline_id,
262 0 AS m_matchpo_id,
263 0 AS m_productionline_id,
264 319 AS ad_table_id,
265 320 AS ad_table_line_id,
266 0::numeric AS amount,
267 CASE
268 WHEN d.docbasetype = 'MMR'::bpchar AND io.issotrx = 'N'::bpchar THEN 'V+'::text
269 WHEN d.docbasetype = 'MMR'::bpchar AND io.issotrx = 'Y'::bpchar THEN 'V-'::text
270 WHEN d.docbasetype = 'MMS'::bpchar AND io.issotrx = 'Y'::bpchar THEN 'C-'::text
271 WHEN d.docbasetype = 'MMS'::bpchar AND io.issotrx = 'N'::bpchar THEN 'C+'::text
272 ELSE NULL::text
273 END AS movementtype,
274 CASE
275 WHEN d.docbasetype = 'MMR'::bpchar AND io.issotrx = 'N'::bpchar AND iol.movementqty >= 0::numeric THEN 10
276 WHEN d.docbasetype = 'MMR'::bpchar AND io.issotrx = 'Y'::bpchar AND iol.movementqty >= 0::numeric THEN 30
277 WHEN d.docbasetype = 'MMS'::bpchar AND io.issotrx = 'N'::bpchar AND iol.movementqty >= 0::numeric THEN 60
278 WHEN d.docbasetype = 'MMS'::bpchar AND io.issotrx = 'Y'::bpchar AND iol.movementqty >= 0::numeric THEN 100
279 WHEN d.docbasetype = 'MMS'::bpchar AND io.issotrx = 'Y'::bpchar AND iol.movementqty < 0::numeric THEN 80
280 WHEN d.docbasetype = 'MMS'::bpchar AND io.issotrx = 'N'::bpchar AND iol.movementqty < 0::numeric THEN 110
281 WHEN d.docbasetype = 'MMR'::bpchar AND io.issotrx = 'Y'::bpchar AND iol.movementqty < 0::numeric THEN 130
282 WHEN d.docbasetype = 'MMR'::bpchar AND io.issotrx = 'N'::bpchar AND iol.movementqty < 0::numeric THEN 140
283 ELSE NULL::integer
284 END AS processingorder,
285 d.docbasetype,
286 ''::text AS docsubtype,
287 io.c_doctype_id,
288 io.posted,
289 CASE
290 WHEN trunc(to_timestamp((io.processedon / 1000::numeric)::double precision), 'DD'::character varying) > io.movementdate::date THEN 'B'::text
291 WHEN trunc(to_timestamp((io.processedon / 1000::numeric)::double precision), 'DD'::character varying) < io.movementdate::date THEN 'F'::text
292 ELSE 'N'::text
293 END AS backdated
294 FROM m_inout io
295 JOIN m_inoutline iol ON io.m_inout_id = iol.m_inout_id
296 JOIN c_doctype d ON io.c_doctype_id = d.c_doctype_id
297 WHERE (io.docstatus::text = ANY (ARRAY['CO'::text, 'CL'::text, 'RE'::text, 'VO'::text])) AND (d.docbasetype::text = ANY (ARRAY['MMR'::text, 'MMS'::text]))
298 UNION ALL
299 SELECT il.ad_client_id,
300 il.ad_org_id,
301 il.created,
302 il.updated,
303 il.createdby,
304 il.updatedby,
305 il.isactive,
306 'Y'::character(1) AS issotrx,
307 il.m_inventoryline_id AS record_id,
308 0 AS m_inout_id,
309 0 AS c_order_id,
310 0 AS c_invoice_id,
311 CASE
312 WHEN d.docsubtypeinv::text = 'PI'::text THEN il.qtycount - il.qtybook
313 WHEN d.docsubtypeinv::text = 'CA'::text AND il.qtyinternaluse = 0::numeric THEN 0::numeric
314 WHEN d.docsubtypeinv::text = 'IU'::text AND il.qtyinternaluse > 0::numeric THEN il.qtyinternaluse
315 ELSE NULL::numeric
316 END AS qty,
317 CASE
318 WHEN d.docsubtypeinv::text = 'PI'::text THEN il.qtycount - il.qtybook
319 WHEN d.docsubtypeinv::text = 'CA'::text AND il.qtyinternaluse = 0::numeric THEN 0::numeric
320 WHEN d.docsubtypeinv::text = 'IU'::text AND il.qtyinternaluse > 0::numeric THEN - il.qtyinternaluse
321 ELSE NULL::numeric
322 END AS movementqty,
323 il.m_product_id,
324 p.c_uom_id,
325 inv.documentno,
326 inv.movementdate AS datedoc,
327 CASE
328 WHEN inv.movementdate::date < inv.created::date AND il.qtyinternaluse > 0::numeric OR (il.qtycount - il.qtybook) < 0::numeric THEN inv.created::date
329 ELSE inv.movementdate::date
330 END AS processingdate,
331 to_char(trunc(to_timestamp((inv.processedon / 1000::numeric)::double precision), 'DD'::character varying) - inv.movementdate::timestamp with time zone, 'DD'::text)::integer AS daysdiff,
332 il.m_attributesetinstance_id,
333 0 AS m_movement_id,
334 inv.m_inventory_id,
335 0 AS m_inoutline_id,
336 0 AS c_orderline_id,
337 0 AS c_invoiceline_id,
338 0 AS m_matchinv_id,
339 il.m_inventoryline_id,
340 0 AS m_movementline_id,
341 0 AS m_matchpo_id,
342 0 AS m_productionline_id,
343 321 AS ad_table_id,
344 322 AS ad_table_line_id,
345 il.newcostprice AS amount,
346 CASE
347 WHEN d.docsubtypeinv::text = 'PI'::text AND (il.qtycount - il.qtybook) < 0::numeric THEN 'I-'::text
348 WHEN d.docsubtypeinv::text = 'PI'::text AND (il.qtycount - il.qtybook) >= 0::numeric THEN 'I+'::text
349 WHEN d.docsubtypeinv::text = 'CA'::text AND il.qtyinternaluse = 0::numeric THEN 'I'::text
350 WHEN d.docsubtypeinv::text = 'IU'::text AND il.qtyinternaluse > 0::numeric THEN 'I-'::text
351 ELSE NULL::text
352 END AS movementtype,
353 CASE
354 WHEN d.docsubtypeinv::text = 'PI'::text AND (il.qtycount - il.qtybook) >= 0::numeric THEN 25
355 WHEN d.docsubtypeinv::text = 'CA'::text AND il.qtyinternaluse = 0::numeric THEN 20
356 WHEN d.docsubtypeinv::text = 'IU'::text AND il.qtyinternaluse > 0::numeric THEN 150
357 WHEN d.docsubtypeinv::text = 'PI'::text AND (il.qtycount - il.qtybook) < 0::numeric THEN 160
358 ELSE NULL::integer
359 END AS processingorder,
360 d.docbasetype,
361 CASE
362 WHEN d.docsubtypeinv::text = 'PI'::text THEN 'Phys. Inv.'::text
363 WHEN d.docsubtypeinv::text = 'CA'::text AND il.qtyinternaluse = 0::numeric THEN 'Cost Adjustment'::text
364 WHEN d.docsubtypeinv::text = 'IU'::text AND il.qtyinternaluse > 0::numeric THEN 'Int. Use'::text
365 ELSE NULL::text
366 END AS docsubtype,
367 inv.c_doctype_id,
368 inv.posted,
369 CASE
370 WHEN trunc(to_timestamp((inv.processedon / 1000::numeric)::double precision), 'DD'::character varying) > inv.movementdate::date THEN 'B'::text
371 WHEN trunc(to_timestamp((inv.processedon / 1000::numeric)::double precision), 'DD'::character varying) < inv.movementdate::date THEN 'F'::text
372 ELSE 'N'::text
373 END AS backdated
374 FROM m_inventory inv
375 JOIN m_inventoryline il ON il.m_inventory_id = inv.m_inventory_id
376 JOIN c_doctype d ON inv.c_doctype_id = d.c_doctype_id
377 JOIN m_product p ON il.m_product_id = p.m_product_id
378 WHERE inv.docstatus::text = ANY (ARRAY['CO'::text, 'CL'::text, 'RE'::text, 'VO'::text])
379 UNION ALL
380 SELECT po.ad_client_id,
381 po.ad_org_id,
382 po.created,
383 po.updated,
384 po.createdby,
385 po.updatedby,
386 po.isactive,
387 'N'::character(1) AS issotrx,
388 po.m_matchpo_id AS record_id,
389 0 AS m_inout_id,
390 0::numeric AS c_order_id,
391 0 AS c_invoice_id,
392 po.qty,
393 NULL::numeric AS movementqty,
394 po.m_product_id,
395 ol.c_uom_id,
396 po.documentno,
397 po.dateacct AS datedoc,
398 CASE
399 WHEN po.qty > 0::numeric THEN po.dateacct::timestamp with time zone
400 ELSE trunc(to_timestamp((po.processedon / 1000::numeric)::double precision), 'DD'::character varying)
401 END AS processingdate,
402 to_char(trunc(to_timestamp((po.processedon / 1000::numeric)::double precision), 'DD'::character varying) - po.dateacct::timestamp with time zone, 'DD'::text)::integer AS daysdiff,
403 po.m_attributesetinstance_id,
404 0 AS m_movement_id,
405 0 AS m_inventory_id,
406 0 AS m_inoutline_id,
407 po.c_orderline_id,
408 0 AS c_invoiceline_id,
409 0 AS m_matchinv_id,
410 0 AS m_inventoryline_id,
411 0 AS m_movementline_id,
412 po.m_matchpo_id,
413 0 AS m_productionline_id,
414 473 AS ad_table_id,
415 473 AS ad_table_line_id,
416 ol.priceentered AS amount,
417 'MO'::text AS movementtype,
418 5 AS processingorder,
419 NULL::bpchar AS docbasetype,
420 ''::text AS docsubtype,
421 1001564::numeric AS c_doctype_id,
422 po.posted,
423 CASE
424 WHEN trunc(to_timestamp((po.processedon / 1000::numeric)::double precision), 'DD'::character varying) > po.dateacct::date THEN 'B'::text
425 WHEN trunc(to_timestamp((po.processedon / 1000::numeric)::double precision), 'DD'::character varying) < po.dateacct::date THEN 'F'::text
426 ELSE 'N'::text
427 END AS backdated
428 FROM m_matchpo po
429 LEFT JOIN c_orderline ol ON po.c_orderline_id = ol.c_orderline_id
430 UNION ALL
431 SELECT il.ad_client_id,
432 il.ad_org_id,
433 il.created,
434 il.updated,
435 il.createdby,
436 il.updatedby,
437 il.isactive,
438 i.issotrx,
439 lca.c_landedcostallocation_id AS record_id,
440 0 AS m_inout_id,
441 0 AS c_order_id,
442 il.c_invoice_id,
443 lca.qty,
444 0 AS movementqty,
445 lca.m_product_id,
446 il.c_uom_id,
447 i.documentno,
448 i.dateinvoiced AS datedoc,
449 trunc(to_timestamp((io2.processedon / 1000::numeric)::double precision), 'DD'::character varying) AS processingdate,
450 to_char(trunc(to_timestamp((io2.processedon / 1000::numeric)::double precision), 'DD'::character varying) - io2.movementdate::timestamp with time zone, 'DD'::text)::integer AS daysdiff,
451 lca.m_attributesetinstance_id,
452 0 AS m_movement_id,
453 0 AS m_inventory_id,
454 0 AS m_inoutline_id,
455 0 AS c_orderline_id,
456 il.c_invoiceline_id,
457 0 AS m_matchinv_id,
458 0 AS m_inventoryline_id,
459 0 AS m_movementline_id,
460 0 AS m_matchpo_id,
461 0 AS m_productionline_id,
462 318 AS ad_table_id,
463 333 AS ad_table_line_id,
464 lca.amt AS amount,
465 'L'::text AS movementtype,
466 12 AS processingorder,
467 d.docbasetype,
468 ''::text AS docsubtype,
469 i.c_doctype_id,
470 i.posted,
471 CASE
472 WHEN trunc(to_timestamp((io2.processedon / 1000::numeric)::double precision), 'DD'::character varying) > i.dateacct::date THEN 'B'::text
473 WHEN trunc(to_timestamp((io2.processedon / 1000::numeric)::double precision), 'DD'::character varying) < i.dateacct::date THEN 'F'::text
474 ELSE 'N'::text
475 END AS backdated
476 FROM c_invoiceline il
477 JOIN c_invoice i ON i.c_invoice_id = il.c_invoice_id AND i.issotrx = 'N'::bpchar AND (i.docstatus::text = ANY (ARRAY['CO'::text, 'CL'::text, 'RE'::text, 'VO'::text]))
478 JOIN c_landedcostallocation lca ON lca.c_invoiceline_id = il.c_invoiceline_id
479 JOIN c_doctype d ON d.c_doctype_id = i.c_doctype_id
480 JOIN m_inoutline iol ON lca.m_inoutline_id = iol.m_inoutline_id
481 JOIN m_inout io2 ON io2.m_inout_id = iol.m_inout_id
482 UNION ALL
483 SELECT mi.ad_client_id,
484 mi.ad_org_id,
485 mi.created,
486 mi.updated,
487 mi.createdby,
488 mi.updatedby,
489 mi.isactive,
490 'N'::character(1) AS issotrx,
491 mi.m_matchinv_id AS record_id,
492 0 AS m_inout_id,
493 0 AS c_order_id,
494 0 AS c_invoice_id,
495 mi.qty,
496 NULL::numeric AS movementqty,
497 mi.m_product_id,
498 il.c_uom_id,
499 mi.documentno,
500 mi.dateacct AS datedoc,
501 mi.dateacct::date AS processingdate,
502 to_char(trunc(to_timestamp((mi.processedon / 1000::numeric)::double precision), 'DD'::character varying) - mi.dateacct::timestamp with time zone, 'DD'::text)::integer AS daysdiff,
503 mi.m_attributesetinstance_id,
504 0 AS m_movement_id,
505 0 AS m_inventory_id,
506 0 AS m_inoutline_id,
507 0 AS c_orderline_id,
508 0 AS c_invoiceline_id,
509 mi.m_matchinv_id,
510 0 AS m_inventoryline_id,
511 0 AS m_movementline_id,
512 0 AS m_matchpo_id,
513 0 AS m_productionline_id,
514 472 AS ad_table_id,
515 472 AS ad_table_line_id,
516 il.priceentered AS amount,
517 'MI'::text AS movementtype,
518 13 AS processingorder,
519 NULL::bpchar AS docbasetype,
520 ''::text AS docsubtype,
521 1001565::numeric AS c_doctype_id,
522 mi.posted,
523 CASE
524 WHEN trunc(to_timestamp((mi.processedon / 1000::numeric)::double precision), 'DD'::character varying) > mi.dateacct::date THEN 'B'::text
525 WHEN trunc(to_timestamp((mi.processedon / 1000::numeric)::double precision), 'DD'::character varying) > mi.dateacct::date THEN 'F'::text
526 ELSE 'N'::text
527 END AS backdated
528 FROM m_matchinv mi
529 LEFT JOIN c_invoiceline il ON il.c_invoiceline_id = mi.c_invoiceline_id
530 UNION ALL
531 SELECT ml.ad_client_id,
532 ml.ad_org_id,
533 ml.created,
534 ml.updated,
535 ml.createdby,
536 ml.updatedby,
537 ml.isactive,
538 'Y'::character(1) AS issotrx,
539 ml.m_movementline_id AS record_id,
540 0 AS m_inout_id,
541 0 AS c_order_id,
542 0 AS c_invoice_id,
543 ml.movementqty AS qty,
544 ml.movementqty,
545 ml.m_product_id,
546 NULL::numeric AS c_uom_id,
547 m.documentno,
548 m.movementdate AS datedoc,
549 trunc(to_timestamp((m.processedon / 1000::numeric)::double precision), 'DD'::character varying) AS processingdate,
550 to_char(trunc(to_timestamp((m.processedon / 1000::numeric)::double precision), 'DD'::character varying) - m.movementdate::timestamp with time zone, 'DD'::text)::integer AS daysdiff,
551 ml.m_attributesetinstance_id,
552 m.m_movement_id,
553 0 AS m_inventory_id,
554 0 AS m_inoutline_id,
555 0 AS c_orderline_id,
556 0 AS c_invoiceline_id,
557 0 AS m_matchinv_id,
558 0 AS m_inventoryline_id,
559 ml.m_movementline_id,
560 0 AS m_matchpo_id,
561 0 AS m_productionline_id,
562 323 AS ad_table_id,
563 324 AS ad_table_line_id,
564 NULL::numeric AS amount,
565 'M-'::text AS movementtype,
566 35 AS processingorder,
567 d.docbasetype,
568 ''::text AS docsubtype,
569 m.c_doctype_id,
570 m.posted,
571 CASE
572 WHEN trunc(to_timestamp((m.processedon / 1000::numeric)::double precision), 'DD'::character varying) > m.movementdate::date THEN 'B'::text
573 WHEN trunc(to_timestamp((m.processedon / 1000::numeric)::double precision), 'DD'::character varying) < m.movementdate::date THEN 'F'::text
574 ELSE 'N'::text
575 END AS backdated
576 FROM m_movementline ml
577 JOIN m_movement m ON ml.m_movement_id = m.m_movement_id AND (m.docstatus::text = ANY (ARRAY['CO'::text, 'CL'::text, 'RE'::text, 'VO'::text]))
578 JOIN c_doctype d ON m.c_doctype_id = d.c_doctype_id
579 WHERE ml.movementqty > 0::numeric AND ml.locatororgmovetype = 'O'::bpchar
580 UNION ALL
581 SELECT pl.ad_client_id,
582 pl.ad_org_id,
583 pl.created,
584 pl.updated,
585 pl.createdby,
586 pl.updatedby,
587 pl.isactive,
588 'Y'::character(1) AS issotrx,
589 pl.m_productionline_id AS record_id,
590 0 AS m_inout_id,
591 0 AS c_order_id,
592 0 AS c_invoice_id,
593 pl.movementqty AS qty,
594 pl.movementqty,
595 pl.m_product_id,
596 NULL::numeric AS c_uom_id,
597 p.documentno,
598 p.movementdate AS datedoc,
599 trunc(to_timestamp((p.processedon / 1000::numeric)::double precision), 'DD'::character varying) AS processingdate,
600 to_char(trunc(to_timestamp((p.processedon / 1000::numeric)::double precision), 'DD'::character varying) - p.movementdate::timestamp with time zone, 'DD'::text)::integer AS daysdiff,
601 pl.m_attributesetinstance_id,
602 0 AS m_movement_id,
603 0 AS m_inventory_id,
604 0 AS m_inoutline_id,
605 0 AS c_orderline_id,
606 0 AS c_invoiceline_id,
607 0 AS m_matchinv_id,
608 0 AS m_inventoryline_id,
609 0 AS m_movementline_id,
610 0 AS m_matchpo_id,
611 pl.m_productionline_id,
612 325 AS ad_table_id,
613 326 AS ad_table_line_id,
614 NULL::numeric AS amount,
615 CASE
616 WHEN d.docbasetype = 'MMP'::bpchar AND pl.isendproduct = 'Y'::bpchar THEN 'P+'::text
617 ELSE 'P-'::text
618 END AS movementtype,
619 35 AS processingorder,
620 NULL::bpchar AS docbasetype,
621 ''::text AS docsubtype,
622 1001565::numeric AS c_doctype_id,
623 p.posted,
624 CASE
625 WHEN trunc(p.created::date::timestamp with time zone, 'DD'::character varying) > p.movementdate::date THEN 'B'::text
626 WHEN trunc(p.created::date::timestamp with time zone, 'DD'::character varying) < p.movementdate::date THEN 'F'::text
627 ELSE 'N'::text
628 END AS backdated
629 FROM m_productionline pl
630 JOIN m_production p ON pl.m_production_id = p.m_production_id AND (p.docstatus::text = ANY (ARRAY['CO'::text, 'CL'::text, 'RE'::text, 'VO'::text]))
631 JOIN c_doctype d ON p.c_doctype_id = d.c_doctype_id
632 WHERE pl.movementqty <> 0::numeric) dc) dcf) dcc;
