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
| SELECT PPORTE.RDOSS_CODE colonneAxe01,
PPORTE.RDOSS_CODE dossier,
(select RFAMPR.CODE
from rul, rub, RFAMPR
where RUB.RFAMPR_CODE = RFAMPR.code(+)
and rul.code =
nvl(smvt.rul_code,
nvl(pfat_pporte.rul_code,
nvl(pporde.rul_code, null)))
and rul.rub_code = rub.code
And RUB.RSITE_CODE = '854'
and rub.rdoss_code =
RFAMPR.rdoss_code) FAM_PRD,
(select RGAMPR.CODE
from RGAMPR, rul, rub
where rul.code =
nvl(smvt.rul_code,
nvl(pfat_pporte.rul_code,
nvl(pporde.rul_code, null)))
and rul.rub_code = rub.code
and RUB.RGAMPR_CODE = RGAMPR.CODE(+)) Gam_prd,
pporte.code pporte_code,
pporte.rsite_code pporte_rsite_code,
row_number() over(partition by pporte.code order by pporte.code) pporte_num,
count(decode(pporte.b_valid,
'O',
1,
null)) over(partition by pporte.code) nb_pporte_valide,
count(decode(pporte.b_valid,
'N',
1,
null)) over(partition by pporte.code) nb_pporte_non_valide,
PPORTE.L_TRT pporte_l_trt,
count(case
when pfat_pporte.l_etat = 'E' and
pporte.l_trt = '9' then
1
else
null
end) over(partition by pfat_pporte.code, pfat_pporte.sums_code) nb_um, /*
PPORDE */
pporde.code pporde_code,
row_number() over(partition by pporte.code, pporde.code order by pporde.code) pporde_num,
PPORDE.NB_ANN pporde_nb_ann,
PPORDE.PDS_BRUT_ANN PPORDE_PDS_BRUT_ANN,
PPORDE.PDS_NET_ANN PPORDE_PDS_NET_ANN,
pporde.nb_rec pporde_nb_rec,
(select rub.l_typ
from rub, rul
where rul.code = pporde.rul_code
and rub.code = rul.rub_code) pporde_rub_typ,
pkg_rul.fn_sel_nb_rtypul_sp(pkg_rul.fn_sel_rul_code_palette_multi(pporde.rul_code),
'CO') nb_typ_pal, /*
PFAT */
pfat_pporte.code pfat_code,
row_number() over(partition by pfat_pporte.code order by pfat_pporte.code) pfat_num,
pfat_pporte.NB_UL PFAT_NB_UL,
pfat_pporte.PDS_NET PFAT_PDS_NET,
pfat_pporte.PDS_BRUT PFAT_PDS_BRUT,
pfat_pporte.l_etat pfat_l_etat,
pfat_pporte.pporde_code pfat_pporde_code,
(select rub.l_typ
from rub, rul
where rul.code = pfat_pporte.rul_code
and rub.code = rul.rub_code) pfat_rub_typ, /* SMVT */
smvt.code smvt_code,
row_number() over(partition by smvt.code order by smvt.code) smvt_num,
SMVT.NB_UL SMVT_NB_UL,
SMVT.PDS_NET SMVT_PDS_NET,
SMVT.PDS_BRUT SMVT_PDS_BRUT,
(select rub.l_typ
from rub, rul
where rul.code = smvt.rul_code
and rub.code = rul.rub_code) smvt_rub_typ,
smvt.pporde_code smvt_pporde_code*/
from pporte, pporde, (select pfat.pporde_code,pfat.code,pfat.sums_code,pfat.nb_ul,pfat.pds_net,pfat.pds_brut,pfat.l_etat,pfat.rul_code from pfat,pporte where pfat.pporte_code = pporte.code) pfat_pporte,(select smvt.pporde_code from smvt,pporte where smvt.pporte_code = pporte.code) smvt_pporte
where PPORTE.RTMVTS_CODE in
(select RTMVTS.CODE
from rtmvts
where RTMVTS.CODE_RGP = 'BA'
and RTMVTS.L_SENS = 'E'
and nvl(rtmvts.l_nat_mvt, 'R') != 'M')
And PPORTE.DHR_MVT_REEL between
to_date('28/03/2012', 'DD/MM/YYYY') and
to_date('28/03/2012', 'DD/MM/YYYY') +
.99999
AND EXISTS
(SELECT 1
FROM SMVTUL
WHERE AUUTL_CODE = 'AGA'
AND RDOSS_CODE = PPORTE.rdoss_code)
and pporte.rsite_code = '854'
and pporte.code = pporde.pporte_code(+)
and pporde.code = pfat_pporte.pporde_code (+)
and pporde.code = smvt_pporte.pporde_code (+) |
Double jointure externe sur une table
Répondre avec citation
Partager