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
|
select rtax.tax_cod t_cod,
tax_amount tax_amount,
tax_dsc tdsc,
tax_lin t_lin,
bud.bud_cod,
bud_dsc,
trunc((cme.cash_mean_amount * tax_amount) / cdi.cdi_amount_total) v_montant,
sum(trunc((cme.cash_mean_amount * tax_amount) / cdi.cdi_amount_total)) v_somme,
HADI.v_max,
HADI.v_reste,
'in'status,
cme.cash_mean_cod mea_cod
from csh_gen_cdi cdi, rcp_tax rtax, untaxtab tax, unbudtab bud,
(select RCP_YEAR, RCP_CUO, RCP_SERIAL, RCP_NBER, CASH_MEAN_COD,
max(nvl(CASH_MEAN_REFNBER,'$')) CASH_MEAN_REFNBER,
max(nvl(CASH_BANK_COD,'$')) CASH_BANK_COD, sum(CASH_MEAN_AMOUNT) CASH_MEAN_AMOUNT
from csh_mea
group by RCP_YEAR, RCP_CUO, RCP_SERIAL, RCP_NBER, CASH_MEAN_COD) cme,
(select cm.cash_mean_cod, cm.rcp_year, cm.rcp_cuo, cm.rcp_serial, cm.rcp_nber,
max(trunc((cm.cash_mean_amount * rtx.tax_amount) / cdii.cdi_amount_total)) as v_max,
cm.cash_mean_amount - sum(trunc((cm.cash_mean_amount * rtx.tax_amount) / cdii.cdi_amount_total)) as v_reste
from csh_gen_cdi cdii, rcp_tax rtx, untaxtab tt, unbudtab bt,
(select RCP_YEAR, RCP_CUO, RCP_SERIAL, RCP_NBER, CASH_MEAN_COD,
max(nvl(CASH_MEAN_REFNBER,'$')) CASH_MEAN_REFNBER,
max(nvl(CASH_BANK_COD,'$')) CASH_BANK_COD, sum(CASH_MEAN_AMOUNT) CASH_MEAN_AMOUNT
from csh_mea
group by RCP_YEAR, RCP_CUO, RCP_SERIAL, RCP_NBER, CASH_MEAN_COD) cm
where
cdii.ope_dat = '20060905' and
cdii.rcp_cuo = 'CMDLP' and
rtx.rcp_serial = cdii.rcp_serial and
rtx.rcp_nber = cdii.rcp_nber and
rtx.rcp_year = cdii.rcp_year and
rtx.rcp_cuo = cdii.rcp_cuo and
rtx.tax_cod = tt.tax_cod and
tt.bud_cod = bt.bud_cod and
tt.lst_ope != 'D' and
cm.rcp_serial = rtx.rcp_serial and
cm.rcp_nber = rtx.rcp_nber and
cm.rcp_year = rtx.rcp_year and
cm.rcp_cuo = rtx.rcp_cuo
group by cm.cash_mean_cod, cm.rcp_year, cm.rcp_cuo, cm.rcp_serial, cm.rcp_nber, cm.cash_mean_amount) HADI
where
cdi.ope_dat = '20060905'and
cdi.rcp_cuo = 'CMDLP' and
rtax.rcp_serial = cdi.rcp_serial and
rtax.rcp_nber = cdi.rcp_nber and
rtax.rcp_year = cdi.rcp_year and
rtax.rcp_cuo = cdi.rcp_cuo and
rtax.tax_cod = tax.tax_cod and
bud.bud_cod = tax.bud_cod and
tax.lst_ope != 'D' and
cme.rcp_serial = rtax.rcp_serial and
cme.rcp_nber = rtax.rcp_nber and
cme.rcp_year = rtax.rcp_year and
cme.rcp_cuo = rtax.rcp_cuo and
HADI.rcp_year = cdi.rcp_year and
HADI.rcp_cuo = cdi.rcp_cuo and
HADI.rcp_serial = cdi.rcp_serial and
HADI.rcp_nber = cdi.rcp_nber and
HADI.cash_mean_cod = cme.cash_mean_cod
order by 5, 4, 1 |
