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
| update arprepost set notes =
(
select
'TOTAL MARCHANDISE : ' ||
sum(round(id.invoice_qty*id.unit_price,2)) || chr(13) || chr(10) ||
'ESCOMPTE: ' ||
round(sum(id.invoice_qty*id.unit_price*NVL(t.discount,0)/100),2) || chr(13) || chr(10) ||
'TOTAL HT: ' ||
(
sum(round(id.invoice_qty*id.unit_price,2)) -
round(sum(id.invoice_qty*id.unit_price*NVL(t.discount,0)/100),2)
)
from
arprepost i,
arprepost_detail id,
arcusto c,
tax_codes tc,
terms t,
v_ud_arcusto ud,
iqsys
where
-- Linking tables
id.arprepost_id = i.id and
i.arcusto_id = c.id and
id.tax_code_id = tc.id and
ud.parent_id = c.id and
i.terms_id = t.id and
-- Other constraints
upper(ud.auto_discount_use) = 'X' and
-- Constraint from outside brackets
i.id = arprepost.id and
-- End constraint
1=1
group by
i.id
)
where
-- eplant_id = 23 and
id not in
(
select
i.id
from
arprepost i,
arprepost_detail id,
v_ud_arcusto ud
where
id.arprepost_id = i.id and
id.misc_comment = ud.auto_discount_desc
) and
id in
(
select
i.id
from
arprepost i,
v_ud_arcusto ud
where
i.arcusto_id = ud.parent_id and
upper(ud.auto_discount_use) = 'X'
);
insert into arprepost_detail
(
arprepost_id,
ord_detail_id,
invoice_qty,
unit_price,
glacct_ID_sales,
tax_code_id,
misc_comment,
price_per_1000,
eplant_id)
select
i.id as arprepost_id,
0 as ord_detail_id,
1 as invoice_qty,
-round(sum(id.invoice_qty*id.unit_price*NVL(t.discount,0)/100)*100)/100 as unit_price,
max(iqsys.acct_id_ardisc) as glacct_ID_sales,
tc.id as tax_codes_id,
max(ud.auto_discount_desc) as misc_comment,
-sum(id.invoice_qty*id.unit_price*NVL(t.discount,0)/100)*1000 as price_per_1000,
max(i.eplant_id) as eplant_id
from
arprepost i,
arprepost_detail id,
arcusto c,
tax_codes tc,
terms t,
v_ud_arcusto ud,
iqsys
where
-- Linking tables
id.arprepost_id = i.id and
i.arcusto_id = c.id and
id.tax_code_id = tc.id and
ud.parent_id = c.id and
i.terms_id = t.id and
-- Other constraints
upper(ud.auto_discount_use) = 'X' and
i.id not in
(select i.id
from arprepost i, arprepost_detail id, v_ud_arcusto ud
where id.arprepost_id = i.id and id.misc_comment = ud.auto_discount_desc) and
-- End constraint
1=1
group by
i.id,
tc.id;
exit |
