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
| select
pers.inss as inss
, ben.file_number AS file_number
, mdbt.managed_debt_id as DEBT_itiniris
, min(dbt.year*100 + dbt.month) as Start_Month
, max(dbt.year*100 + dbt.month) as End_Month
, sum(dbt.amount) as Month_Amount-- toutes les lignes mises en débit
,
(
select
sum(w_FDR.AMOUNT) as AMOUNT
from RESERVEFUND_WRITEOFFS w_FDR
inner join DEBT_ACCOUNTS da_FDR on w_FDR.DEBT_ACCOUNT_ID = da_FDR.DEBT_ACCOUNT_ID
inner join MANAGED_DEBTS md_FDR on da_FDR.DEBT_ACCOUNT_ID = md_FDR.DEBT_ACCOUNT_ID
where md_FDR.MANAGED_DEBT_ID = mdbt.managed_debt_id
group by md_FDR.managed_debt_id
)
as FDR
, --Retenues (autre CAF)
(
select
sum(w_WITTH_O.AMOUNT) as AMOUNT
from WITHHOLDINGS w_WITTH_O
inner join DEBT_ACCOUNTS da_WITTH_O on w_WITTH_O.DEBT_ACCOUNT_ID = da_WITTH_O.DEBT_ACCOUNT_ID
inner join MANAGED_DEBTS md_WITTH_O on da_WITTH_O.DEBT_ACCOUNT_ID = md_WITTH_O.DEBT_ACCOUNT_ID
where md_WITTH_O.MANAGED_DEBT_ID = mdbt.managed_debt_id
group by md_WITTH_O.managed_debt_id
)
as WITHH_O
, -- Remboursement
(
select
sum(w_RMB.AMOUNT) as AMOUNT
from DEBT_PAYOFFS w_RMB
inner join DEBT_ACCOUNTS da_RMB on w_RMB.DEBT_ACCOUNT_ID = da_RMB.DEBT_ACCOUNT_ID
inner join MANAGED_DEBTS md_RMB on da_RMB.DEBT_ACCOUNT_ID = md_RMB.DEBT_ACCOUNT_ID
where md_RMB.MANAGED_DEBT_ID = mdbt.managed_debt_id
group by md_RMB.managed_debt_id
)
AS remb
, -- Transfert héritiers
(
select
sum(w_HERIT.AMOUNT) as AMOUNT
from INHERITANCE_TRANSFERS w_HERIT
inner join DEBT_ACCOUNTS da_HERIT on w_HERIT.DEBT_ACCOUNT_ID = da_HERIT.DEBT_ACCOUNT_ID
inner join MANAGED_DEBTS md_HERIT on da_HERIT.DEBT_ACCOUNT_ID = md_HERIT.DEBT_ACCOUNT_ID
where md_HERIT.MANAGED_DEBT_ID = mdbt.managed_debt_id
group by md_HERIT.managed_debt_id
)
as HERIT
, -- Paiement suite renonciation OK
(
select
sum(p_RENON.AMOUNT) as AMOUNT
from PAYMENTS p_RENON
inner join RESERVEFUND_WRITEOFFS w_RENON on p_RENON.PAYMENT_ID = w_RENON.PAYMENT_ID
inner join DEBT_ACCOUNTS da_RENON on w_RENON.DEBT_ACCOUNT_ID = da_RENON.DEBT_ACCOUNT_ID
inner join MANAGED_DEBTS md_RENON on da_RENON.DEBT_ACCOUNT_ID = md_RENON.DEBT_ACCOUNT_ID
where md_RENON.MANAGED_DEBT_ID = mdbt.managed_debt_id
group by md_RENON.managed_debt_id
)
as RENON
from beneficiaries ben
inner join actors act on act.actor_id = ben.actor_id
inner join persons pers on pers.person_id = act.person_id
inner join debits dbt on dbt.owner_id = ben.actor_id
inner join managed_debts mdbt on mdbt.debt_account_id = dbt.debt_account_id
-- Ne pas prendre les débits à la vérification, Closed, Prévu...
and mdbt.debtstatus not in ('CLOSED', 'CLOSED_NO_H29', 'CLOSED_WITH_NOTIFICATION', 'VERIFIED', 'MOTIVATED', 'PROVIDED')
--where MANAGED_DEBT_ID = 100003514953
group by pers.inss, ben.file_number, mdbt.managed_debt_id
order by pers.inss, ben.file_number, mdbt.managed_debt_id
; |
