Alguien que tenga idea de como puedo optimizar este query??
Publicado por jaime (2 intervenciones) el 11/10/2022 01:26:58
Es un query bastante complejo y tarda demasiado en ejecutarse, hasta 20 minutos y eso tarda por los updates que tiene que hacer, alguien tiene idea de como puedo optimizarlo?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
drop table if exists rpl_csv;
CREATE TEMPORARY TABLE rpl_csv AS (SELECT DISTINCT ON (pp.id) pp.id,
pt.id as product_tmpl_id,
pp.default_code,
pp.name_template,
ts.name as section,
tm.name as brand,
tl.name as line,
tser.name as serie,
divs.name division,
pt."amazonAsin" asin,
pt.state status,
0 as exisGUADALAJARAFacturacionGDL, 0 as apartGUADALAJARAFacturacionGDL, 0 as totGUADALAJARAFacturacionGDL, 0.00 as costGUADALAJARAFacturacionGDL,
0 as exisAEROPUERTOFacturacionAeropuerto, 0 as apartAEROPUERTOFacturacionAeropuerto, 0 as totAEROPUERTOFacturacionAeropuerto, 0.00 as costAEROPUERTOFacturacionAeropuerto,
0 as exisVIRTUALFacturacionVirtual, 0 as apartVIRTUALFacturacionVirtual, 0 as totVIRTUALFacturacionVirtual, 0.00 as costVIRTUALFacturacionVirtual,
0 as exisMONTERREYFacturacionMTY, 0 as apartMONTERREYFacturacionMTY, 0 as totMONTERREYFacturacionMTY, 0.00 as costMONTERREYFacturacionMTY,
0 as exisMERIDAFacturacionMID, 0 as apartMERIDAFacturacionMID, 0 as totMERIDAFacturacionMID, 0.00 as costMERIDAFacturacionMID,
0 as exisDFPLAZAFacturacionPTDF, 0 as apartDFPLAZAFacturacionPTDF, 0 as totDFPLAZAFacturacionPTDF, 0.00 as costDFPLAZAFacturacionPTDF,
0 as exisPUEBLAFacturacionPUE, 0 as apartPUEBLAFacturacionPUE, 0 as totPUEBLAFacturacionPUE, 0.00 as costPUEBLAFacturacionPUE,
0 as exisCEDISGDLFacturacionCEDIS, 0 as apartCEDISGDLFacturacionCEDIS, 0 as totCEDISGDLFacturacionCEDIS, 0.00 as costCEDISGDLFacturacionCEDIS,
0 as exisLEONFacturacionLEON, 0 as apartLEONFacturacionLEON, 0 as totLEONFacturacionLEON, 0.00 as costLEONFacturacionLEON,
0 as exisAJONJOLIFacturacionAjonjoli, 0 as apartAJONJOLIFacturacionAjonjoli, 0 as totAJONJOLIFacturacionAjonjoli, 0.00 as costAJONJOLIFacturacionAjonjoli,
0 as exist_total, 0 as apart_total, 0 as tot_total, 0.00 as cost_total, 0.00 as cost_lp, 0.00 as average_cost, pt.reposition_cost,
pro_t.lvl1 price1,
pro_t.lvl2 price2,
pro_t.lvl3 price3,
pro_t.lvl4 price4,
pro_t.lvl5 price5,
pro_t.lvl6 price6,
pro_t.lvl7 price7,
pro_t.lvl8 price8,
pro_t.lvl9 price9,
pro_t.lvl10 price10,
rc.name, pp.ean13, cg.garantia_en_catalogo, pt.clave_fabricante
FROM product_product PP
LEFT JOIN product_template PT
ON PP.product_tmpl_id = PT.id
LEFT JOIN ( SELECT * FROM crosstab
('SELECT product, price_level, price FROM product_price_level ORDER BY 1,2')
AS ("product" int,
"lvl1" numeric,
"lvl2" numeric,
"lvl3" numeric,
"lvl4" numeric,
"lvl5" numeric,
"lvl6" numeric,
"lvl7" numeric,
"lvl8" numeric,
"lvl9" numeric,
"lvl10" numeric)) as pro_t on pro_t.product = pt.id
JOIN product_category ts on ts.id=pt.section
JOIN product_category tl on tl.id=pt.line
JOIN product_category tm on tm.id=pt.brand
JOIN product_category tser on tser.id=pt.serial
LEFT JOIN divisions divs on pt.division_id=divs.id
LEFT OUTER JOIN res_currency rc on rc.id=pt.list_price_currency
LEFT OUTER JOIN catalogo_garantias cg on cg.id=pt.garantia
LEFT OUTER JOIN stock_quant sq on sq.product_id=pp.id
LEFT OUTER JOIN stock_location sl on sl.id=sq.location_id
LEFT OUTER JOIN stock_warehouse sw on sw.lot_stock_id=sl.id
LEFT OUTER JOIN stock_warehouse_type swt on swt.id=sw.stock_warehouse_type_id WHERE pp.active=true and pt.sale_ok=true );
UPDATE rpl_csv set exisGUADALAJARAFacturacionGDL,apartGUADALAJARAFacturacionGDL=
(COALESCE((SELECT sum(sq.qty) from stock_quant sq join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' inner join stock_location sl on sl.id=sq.location_id inner join stock_warehouse sw on sw.lot_stock_id=sl.id inner join stock_warehouse_type swt on swt.id=sw.stock_warehouse_type_id where sq.product_id=rpl_csv.id and sw.centro_costo_id='1' and sw.id='1' and sq.reservation_id isnull and swt.code='fac' and sl.usage='internal'),0)
- COALESCE((SELECT SUM(sol.product_uom_qty) FROM sale_order_line sol join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' WHERE sol.product_id = rpl_csv.id and exists (select 'f' from sale_order so,stock_warehouse sw where so.id=sol.order_id and so.state = 'remission' and so.warehouse_id = sw.id AND sw.centro_costo_id=1)),0)
); UPDATE rpl_csv set apartGUADALAJARAFacturacionGDL= (COALESCE((SELECT sum(sq.qty) from stock_quant sq join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' inner join stock_location sl on sl.id=sq.location_id inner join stock_warehouse sw on sw.lot_stock_id=sl.id inner join stock_warehouse_type swt on swt.id=sw.stock_warehouse_type_id where sq.product_id=rpl_csv.id and sw.centro_costo_id='1' and sw.id='1' and sq.reservation_id notnull and swt.code='fac' and sl.usage='internal'),0)
+ COALESCE((SELECT SUM(sol.product_uom_qty) FROM sale_order_line sol join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' WHERE sol.product_id = rpl_csv.id and exists (select 'f' from sale_order so,stock_warehouse sw where so.id=sol.order_id and so.state = 'remission' and so.warehouse_id = sw.id AND sw.centro_costo_id=1)),0)
); UPDATE rpl_csv set totGUADALAJARAFacturacionGDL=exisGUADALAJARAFacturacionGDL+apartGUADALAJARAFacturacionGDL; UPDATE rpl_csv set exisAEROPUERTOFacturacionAeropuerto=
(COALESCE((SELECT sum(sq.qty) from stock_quant sq join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' inner join stock_location sl on sl.id=sq.location_id inner join stock_warehouse sw on sw.lot_stock_id=sl.id inner join stock_warehouse_type swt on swt.id=sw.stock_warehouse_type_id where sq.product_id=rpl_csv.id and sw.centro_costo_id='3' and sw.id='5' and sq.reservation_id isnull and swt.code='fac' and sl.usage='internal'),0)
- COALESCE((SELECT SUM(sol.product_uom_qty) FROM sale_order_line sol join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' WHERE sol.product_id = rpl_csv.id and exists (select 'f' from sale_order so,stock_warehouse sw where so.id=sol.order_id and so.state = 'remission' and so.warehouse_id = sw.id AND sw.centro_costo_id=3)),0)
); UPDATE rpl_csv set apartAEROPUERTOFacturacionAeropuerto=
(COALESCE((SELECT sum(sq.qty) from stock_quant sq join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' inner join stock_location sl on sl.id=sq.location_id inner join stock_warehouse sw on sw.lot_stock_id=sl.id inner join stock_warehouse_type swt on swt.id=sw.stock_warehouse_type_id where sq.product_id=rpl_csv.id and sw.centro_costo_id='3' and sw.id='5' and sq.reservation_id notnull and swt.code='fac' and sl.usage='internal'),0)
+ COALESCE((SELECT SUM(sol.product_uom_qty) FROM sale_order_line sol join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' WHERE sol.product_id = rpl_csv.id and exists (select 'f' from sale_order so,stock_warehouse sw where so.id=sol.order_id and so.state = 'remission' and so.warehouse_id = sw.id AND sw.centro_costo_id=3)),0)
); UPDATE rpl_csv set totAEROPUERTOFacturacionAeropuerto=exisAEROPUERTOFacturacionAeropuerto+apartAEROPUERTOFacturacionAeropuerto; UPDATE rpl_csv set exisVIRTUALFacturacionVirtual=
(COALESCE((SELECT sum(sq.qty) from stock_quant sq join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' inner join stock_location sl on sl.id=sq.location_id inner join stock_warehouse sw on sw.lot_stock_id=sl.id inner join stock_warehouse_type swt on swt.id=sw.stock_warehouse_type_id where sq.product_id=rpl_csv.id and sw.centro_costo_id='4' and sw.id='6' and sq.reservation_id isnull and swt.code='fac' and sl.usage='internal'),0)
- COALESCE((SELECT SUM(sol.product_uom_qty) FROM sale_order_line sol join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' WHERE sol.product_id = rpl_csv.id and exists (select 'f' from sale_order so,stock_warehouse sw where so.id=sol.order_id and so.state = 'remission' and so.warehouse_id = sw.id AND sw.centro_costo_id=4)),0)
); UPDATE rpl_csv set apartVIRTUALFacturacionVirtual=
(COALESCE((SELECT sum(sq.qty) from stock_quant sq join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' inner join stock_location sl on sl.id=sq.location_id inner join stock_warehouse sw on sw.lot_stock_id=sl.id inner join stock_warehouse_type swt on swt.id=sw.stock_warehouse_type_id where sq.product_id=rpl_csv.id and sw.centro_costo_id='4' and sw.id='6' and sq.reservation_id notnull and swt.code='fac' and sl.usage='internal'),0)
+ COALESCE((SELECT SUM(sol.product_uom_qty) FROM sale_order_line sol join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' WHERE sol.product_id = rpl_csv.id and exists (select 'f' from sale_order so,stock_warehouse sw where so.id=sol.order_id and so.state = 'remission' and so.warehouse_id = sw.id AND sw.centro_costo_id=4)),0)
); UPDATE rpl_csv set totVIRTUALFacturacionVirtual=exisVIRTUALFacturacionVirtual+apartVIRTUALFacturacionVirtual; UPDATE rpl_csv set exisMONTERREYFacturacionMTY=
(COALESCE((SELECT sum(sq.qty) from stock_quant sq join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' inner join stock_location sl on sl.id=sq.location_id inner join stock_warehouse sw on sw.lot_stock_id=sl.id inner join stock_warehouse_type swt on swt.id=sw.stock_warehouse_type_id where sq.product_id=rpl_csv.id and sw.centro_costo_id='5' and sw.id='11' and sq.reservation_id isnull and swt.code='fac' and sl.usage='internal'),0)
- COALESCE((SELECT SUM(sol.product_uom_qty) FROM sale_order_line sol join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' WHERE sol.product_id = rpl_csv.id and exists (select 'f' from sale_order so,stock_warehouse sw where so.id=sol.order_id and so.state = 'remission' and so.warehouse_id = sw.id AND sw.centro_costo_id=5)),0)
); UPDATE rpl_csv set apartMONTERREYFacturacionMTY=
(COALESCE((SELECT sum(sq.qty) from stock_quant sq join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' inner join stock_location sl on sl.id=sq.location_id inner join stock_warehouse sw on sw.lot_stock_id=sl.id inner join stock_warehouse_type swt on swt.id=sw.stock_warehouse_type_id where sq.product_id=rpl_csv.id and sw.centro_costo_id='5' and sw.id='11' and sq.reservation_id notnull and swt.code='fac' and sl.usage='internal'),0)
+ COALESCE((SELECT SUM(sol.product_uom_qty) FROM sale_order_line sol join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' WHERE sol.product_id = rpl_csv.id and exists (select 'f' from sale_order so,stock_warehouse sw where so.id=sol.order_id and so.state = 'remission' and so.warehouse_id = sw.id AND sw.centro_costo_id=5)),0)
); UPDATE rpl_csv set totMONTERREYFacturacionMTY=exisMONTERREYFacturacionMTY+apartMONTERREYFacturacionMTY; UPDATE rpl_csv set exisMERIDAFacturacionMID=
(COALESCE((SELECT sum(sq.qty) from stock_quant sq join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' inner join stock_location sl on sl.id=sq.location_id inner join stock_warehouse sw on sw.lot_stock_id=sl.id inner join stock_warehouse_type swt on swt.id=sw.stock_warehouse_type_id where sq.product_id=rpl_csv.id and sw.centro_costo_id='6' and sw.id='15' and sq.reservation_id isnull and swt.code='fac' and sl.usage='internal'),0)
- COALESCE((SELECT SUM(sol.product_uom_qty) FROM sale_order_line sol join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' WHERE sol.product_id = rpl_csv.id and exists (select 'f' from sale_order so,stock_warehouse sw where so.id=sol.order_id and so.state = 'remission' and so.warehouse_id = sw.id AND sw.centro_costo_id=6)),0)
); UPDATE rpl_csv set apartMERIDAFacturacionMID=
(COALESCE((SELECT sum(sq.qty) from stock_quant sq join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' inner join stock_location sl on sl.id=sq.location_id inner join stock_warehouse sw on sw.lot_stock_id=sl.id inner join stock_warehouse_type swt on swt.id=sw.stock_warehouse_type_id where sq.product_id=rpl_csv.id and sw.centro_costo_id='6' and sw.id='15' and sq.reservation_id notnull and swt.code='fac' and sl.usage='internal'),0)
+ COALESCE((SELECT SUM(sol.product_uom_qty) FROM sale_order_line sol join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' WHERE sol.product_id = rpl_csv.id and exists (select 'f' from sale_order so,stock_warehouse sw where so.id=sol.order_id and so.state = 'remission' and so.warehouse_id = sw.id AND sw.centro_costo_id=6)),0)
); UPDATE rpl_csv set totMERIDAFacturacionMID=exisMERIDAFacturacionMID+apartMERIDAFacturacionMID; UPDATE rpl_csv set exisDFPLAZAFacturacionPTDF=
(COALESCE((SELECT sum(sq.qty) from stock_quant sq join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' inner join stock_location sl on sl.id=sq.location_id inner join stock_warehouse sw on sw.lot_stock_id=sl.id inner join stock_warehouse_type swt on swt.id=sw.stock_warehouse_type_id where sq.product_id=rpl_csv.id and sw.centro_costo_id='7' and sw.id='19' and sq.reservation_id isnull and swt.code='fac' and sl.usage='internal'),0)
- COALESCE((SELECT SUM(sol.product_uom_qty) FROM sale_order_line sol join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' WHERE sol.product_id = rpl_csv.id and exists (select 'f' from sale_order so,stock_warehouse sw where so.id=sol.order_id and so.state = 'remission' and so.warehouse_id = sw.id AND sw.centro_costo_id=7)),0)
); UPDATE rpl_csv set apartDFPLAZAFacturacionPTDF=
(COALESCE((SELECT sum(sq.qty) from stock_quant sq join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' inner join stock_location sl on sl.id=sq.location_id inner join stock_warehouse sw on sw.lot_stock_id=sl.id inner join stock_warehouse_type swt on swt.id=sw.stock_warehouse_type_id where sq.product_id=rpl_csv.id and sw.centro_costo_id='7' and sw.id='19' and sq.reservation_id notnull and swt.code='fac' and sl.usage='internal'),0)
+ COALESCE((SELECT SUM(sol.product_uom_qty) FROM sale_order_line sol join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' WHERE sol.product_id = rpl_csv.id and exists (select 'f' from sale_order so,stock_warehouse sw where so.id=sol.order_id and so.state = 'remission' and so.warehouse_id = sw.id AND sw.centro_costo_id=7)),0)
); UPDATE rpl_csv set totDFPLAZAFacturacionPTDF=exisDFPLAZAFacturacionPTDF+apartDFPLAZAFacturacionPTDF; UPDATE rpl_csv set exisPUEBLAFacturacionPUE=
(COALESCE((SELECT sum(sq.qty) from stock_quant sq join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' inner join stock_location sl on sl.id=sq.location_id inner join stock_warehouse sw on sw.lot_stock_id=sl.id inner join stock_warehouse_type swt on swt.id=sw.stock_warehouse_type_id where sq.product_id=rpl_csv.id and sw.centro_costo_id='8' and sw.id='23' and sq.reservation_id isnull and swt.code='fac' and sl.usage='internal'),0)
- COALESCE((SELECT SUM(sol.product_uom_qty) FROM sale_order_line sol join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' WHERE sol.product_id = rpl_csv.id and exists (select 'f' from sale_order so,stock_warehouse sw where so.id=sol.order_id and so.state = 'remission' and so.warehouse_id = sw.id AND sw.centro_costo_id=8)),0)
); UPDATE rpl_csv set apartPUEBLAFacturacionPUE=
(COALESCE((SELECT sum(sq.qty) from stock_quant sq join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' inner join stock_location sl on sl.id=sq.location_id inner join stock_warehouse sw on sw.lot_stock_id=sl.id inner join stock_warehouse_type swt on swt.id=sw.stock_warehouse_type_id where sq.product_id=rpl_csv.id and sw.centro_costo_id='8' and sw.id='23' and sq.reservation_id notnull and swt.code='fac' and sl.usage='internal'),0)
+ COALESCE((SELECT SUM(sol.product_uom_qty) FROM sale_order_line sol join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' WHERE sol.product_id = rpl_csv.id and exists (select 'f' from sale_order so,stock_warehouse sw where so.id=sol.order_id and so.state = 'remission' and so.warehouse_id = sw.id AND sw.centro_costo_id=8)),0)
); UPDATE rpl_csv set totPUEBLAFacturacionPUE=exisPUEBLAFacturacionPUE+apartPUEBLAFacturacionPUE; UPDATE rpl_csv set exisCEDISGDLFacturacionCEDIS=
(COALESCE((SELECT sum(sq.qty) from stock_quant sq join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' inner join stock_location sl on sl.id=sq.location_id inner join stock_warehouse sw on sw.lot_stock_id=sl.id inner join stock_warehouse_type swt on swt.id=sw.stock_warehouse_type_id where sq.product_id=rpl_csv.id and sw.centro_costo_id='9' and sw.id='27' and sq.reservation_id isnull and swt.code='fac' and sl.usage='internal'),0)
- COALESCE((SELECT SUM(sol.product_uom_qty) FROM sale_order_line sol join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' WHERE sol.product_id = rpl_csv.id and exists (select 'f' from sale_order so,stock_warehouse sw where so.id=sol.order_id and so.state = 'remission' and so.warehouse_id = sw.id AND sw.centro_costo_id=9)),0)
); UPDATE rpl_csv set apartCEDISGDLFacturacionCEDIS=
(COALESCE((SELECT sum(sq.qty) from stock_quant sq join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' inner join stock_location sl on sl.id=sq.location_id inner join stock_warehouse sw on sw.lot_stock_id=sl.id inner join stock_warehouse_type swt on swt.id=sw.stock_warehouse_type_id where sq.product_id=rpl_csv.id and sw.centro_costo_id='9' and sw.id='27' and sq.reservation_id notnull and swt.code='fac' and sl.usage='internal'),0)
+ COALESCE((SELECT SUM(sol.product_uom_qty) FROM sale_order_line sol join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' WHERE sol.product_id = rpl_csv.id and exists (select 'f' from sale_order so,stock_warehouse sw where so.id=sol.order_id and so.state = 'remission' and so.warehouse_id = sw.id AND sw.centro_costo_id=9)),0)
); UPDATE rpl_csv set totCEDISGDLFacturacionCEDIS=exisCEDISGDLFacturacionCEDIS+apartCEDISGDLFacturacionCEDIS; UPDATE rpl_csv set exisLEONFacturacionLEON=
(COALESCE((SELECT sum(sq.qty) from stock_quant sq join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' inner join stock_location sl on sl.id=sq.location_id inner join stock_warehouse sw on sw.lot_stock_id=sl.id inner join stock_warehouse_type swt on swt.id=sw.stock_warehouse_type_id where sq.product_id=rpl_csv.id and sw.centro_costo_id='11' and sw.id='36' and sq.reservation_id isnull and swt.code='fac' and sl.usage='internal'),0)
- COALESCE((SELECT SUM(sol.product_uom_qty) FROM sale_order_line sol join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' WHERE sol.product_id = rpl_csv.id and exists (select 'f' from sale_order so,stock_warehouse sw where so.id=sol.order_id and so.state = 'remission' and so.warehouse_id = sw.id AND sw.centro_costo_id=11)),0)
); UPDATE rpl_csv set apartLEONFacturacionLEON=
(COALESCE((SELECT sum(sq.qty) from stock_quant sq join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' inner join stock_location sl on sl.id=sq.location_id inner join stock_warehouse sw on sw.lot_stock_id=sl.id inner join stock_warehouse_type swt on swt.id=sw.stock_warehouse_type_id where sq.product_id=rpl_csv.id and sw.centro_costo_id='11' and sw.id='36' and sq.reservation_id notnull and swt.code='fac' and sl.usage='internal'),0)
+ COALESCE((SELECT SUM(sol.product_uom_qty) FROM sale_order_line sol join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' WHERE sol.product_id = rpl_csv.id and exists (select 'f' from sale_order so,stock_warehouse sw where so.id=sol.order_id and so.state = 'remission' and so.warehouse_id = sw.id AND sw.centro_costo_id=11)),0)
); UPDATE rpl_csv set totLEONFacturacionLEON=exisLEONFacturacionLEON+apartLEONFacturacionLEON; UPDATE rpl_csv set exisAJONJOLIFacturacionAjonjoli=
(COALESCE((SELECT sum(sq.qty) from stock_quant sq join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' inner join stock_location sl on sl.id=sq.location_id inner join stock_warehouse sw on sw.lot_stock_id=sl.id inner join stock_warehouse_type swt on swt.id=sw.stock_warehouse_type_id where sq.product_id=rpl_csv.id and sw.centro_costo_id='12' and sw.id='40' and sq.reservation_id isnull and swt.code='fac' and sl.usage='internal'),0)
- COALESCE((SELECT SUM(sol.product_uom_qty) FROM sale_order_line sol join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' WHERE sol.product_id = rpl_csv.id and exists (select 'f' from sale_order so,stock_warehouse sw where so.id=sol.order_id and so.state = 'remission' and so.warehouse_id = sw.id AND sw.centro_costo_id=12)),0)
); UPDATE rpl_csv set apartAJONJOLIFacturacionAjonjoli=
(COALESCE((SELECT sum(sq.qty) from stock_quant sq join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' inner join stock_location sl on sl.id=sq.location_id inner join stock_warehouse sw on sw.lot_stock_id=sl.id inner join stock_warehouse_type swt on swt.id=sw.stock_warehouse_type_id where sq.product_id=rpl_csv.id and sw.centro_costo_id='12' and sw.id='40' and sq.reservation_id notnull and swt.code='fac' and sl.usage='internal'),0)
+ COALESCE((SELECT SUM(sol.product_uom_qty) FROM sale_order_line sol join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' WHERE sol.product_id = rpl_csv.id and exists (select 'f' from sale_order so,stock_warehouse sw where so.id=sol.order_id and so.state = 'remission' and so.warehouse_id = sw.id AND sw.centro_costo_id=12)),0)
); UPDATE rpl_csv set totAJONJOLIFacturacionAjonjoli=exisAJONJOLIFacturacionAjonjoli+apartAJONJOLIFacturacionAjonjoli; UPDATE rpl_csv set exist_total =
(COALESCE((SELECT sum(sq.qty) from stock_quant sq join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' inner join stock_location sl on sl.id=sq.location_id inner join stock_warehouse sw on sw.lot_stock_id=sl.id inner join stock_warehouse_type swt on swt.id=sw.stock_warehouse_type_id where sq.product_id=rpl_csv.id and sq.reservation_id isnull and swt.code='fac' and sl.usage='internal'),0)
- COALESCE((SELECT SUM(sol.product_uom_qty) FROM sale_order_line sol INNER JOIN sale_order so ON (sol.order_id = so.id) join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' WHERE so.state = 'remission' AND sol.product_id = rpl_csv.id),0)
); UPDATE rpl_csv set apart_total = (COALESCE((SELECT sum(sq.qty) from stock_quant sq join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' inner join stock_location sl on sl.id=sq.location_id inner join stock_warehouse sw on sw.lot_stock_id=sl.id inner join stock_warehouse_type swt on swt.id=sw.stock_warehouse_type_id where sq.product_id=rpl_csv.id and sq.reservation_id notnull and swt.code='fac' and sl.usage='internal'),0)
+ COALESCE((SELECT SUM(sol.product_uom_qty) FROM sale_order_line sol INNER JOIN sale_order so ON (sol.order_id = so.id) join product_template pt on pt.id = rpl_csv.product_tmpl_id and pt.type = 'product' WHERE so.state = 'remission' AND sol.product_id = rpl_csv.id),0)
); UPDATE rpl_csv set tot_total=exist_total+apart_total;
UPDATE rpl_csv set cost_lp=COALESCE((SELECT price_unit FROM purchase_order_line pol LEFT JOIN purchase_order po ON (pol.order_id = po.id) WHERE po.shipped = TRUE AND pol.product_id = rpl_csv.id AND pol.invoiced = TRUE ORDER BY pol.write_date DESC LIMIT 1),0.00);
UPDATE rpl_csv set average_cost=COALESCE((SELECT round(cost::numeric,2) FROM product_price_history WHERE product_template_id =rpl_csv.product_tmpl_id AND cost != 0 ORDER BY datetime DESC LIMIT 1),0.00); UPDATE rpl_csv set costGUADALAJARAFacturacionGDL=round(average_cost*totGUADALAJARAFacturacionGDL,2);UPDATE rpl_csv set costAEROPUERTOFacturacionAeropuerto=round(average_cost*totAEROPUERTOFacturacionAeropuerto,2);UPDATE rpl_csv set costVIRTUALFacturacionVirtual=round(average_cost*totVIRTUALFacturacionVirtual,2);UPDATE rpl_csv set costMONTERREYFacturacionMTY=round(average_cost*totMONTERREYFacturacionMTY,2);UPDATE rpl_csv set costMERIDAFacturacionMID=round(average_cost*totMERIDAFacturacionMID,2);UPDATE rpl_csv set costDFPLAZAFacturacionPTDF=round(average_cost*totDFPLAZAFacturacionPTDF,2);UPDATE rpl_csv set costPUEBLAFacturacionPUE=round(average_cost*totPUEBLAFacturacionPUE,2);UPDATE rpl_csv set costCEDISGDLFacturacionCEDIS=round(average_cost*totCEDISGDLFacturacionCEDIS,2);UPDATE rpl_csv set costLEONFacturacionLEON=round(average_cost*totLEONFacturacionLEON,2);UPDATE rpl_csv set costAJONJOLIFacturacionAjonjoli=round(average_cost*totAJONJOLIFacturacionAjonjoli,2);UPDATE rpl_csv set cost_total=round(tot_total*average_cost,2);
SELECT ctid from rpl_csv order by section, line, brand, serie, default_code;
Valora esta pregunta
0
