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
| CREATE OR REPLACE PROCEDURE MSF.GA_CC_EXTRACTION_MOVEX (
param1 varchar2,
param2 varchar2,
param3 varchar2
)
IS
-- ErrorHandler
v_err_num NUMBER;
v_err_msg VARCHAR2(512);
-- curseur dynamique
type ref_cursor is REF CURSOR;
cursor1 ref_cursor;
-- definition de la structure de l'enregistrement = structure de la table GA_CC_TEMP_SIMULATION_DETAIL
record1 MSF.GA_CC_TEMP_SIMULATION_DETAIL%ROWTYPE;
-- la requête SQl construite dynamiquement en fonction des paramètres
sqlSelect varchar2(5000);
-- clauses WHERE initialisée en fonction des paramètres
sqlWhereMITBAL varchar2(500);
sqlWhereMITTRA varchar2(500);
sqlWhereMITPLO varchar2(500);
sqlWhereMITMAS varchar2(500);
--test
str_len number;
loop_count number default 0;
BEGIN
-- construction dynamique de la clause WHERE en fonction des paramètres
sqlSelect := 'select mitwhl.mwfaci,mitbal.mbwhlo,mitbal.mbitno,mitmas.mmitds,mitmas.mmstat,mitbal.mbabcd,mitbal.mbresp,count(mitbal.mbstqt),mitbal.mbleat,(case when mbwhlo=''2SD'' then 1 else 1 end) * (sum(case when mittra.mtttyp=51 then 1 else 0 end) * 1 + sum(case when mittra.mtttyp=41 then 1 else 0 end) * 1 + sum(case when mittra.mtttyp=31 then 1 else 0 end) * 1 + sum(case when mittra.mtttyp=11 then 1 else 0 end) * 1),0,case when mitbal.mbwhlo=''2DM'' then ''UORE'' when mitbal.mbwhlo = ''2SD'' then ''UORE'' when mitbal.mbwhlo = ''2HC'' then ''OUT'' when mitbal.mbwhlo = ''2JC'' then ''OUT'' when mitbal.mbwhlo = ''2ST'' then ''UORM'' else case when mitmas.MMRESP = ''UOEC'' then ''UOEC'' when mitmas.MMRESP = ''UORM'' then ''UORM'' when mitmas.MMRESP = ''UOJC'' then ''UOJC'' when mitmas.MMRESP = ''UORE'' then ''UORE'' else case when mitmas.MMITCL = ''MILIT'' then ''UORM'' else case when mitmas.MMBUAR = ''FRE'' then ''UOEC'' when mitmas.MMBUAR = ''IFR'' then ''UOEC'' when mitmas.MMBUAR = ''HYD'' then ''UOEC'' when mitmas.MMBUAR = ''IHY'' then ''UOEC'' when mitmas.MMBUAR = ''IRO'' then ''UOEC'' when mitmas.MMBUAR = ''ROU'' then ''UOEC'' when mitmas.MMBUAR = ''BSC'' then ''UOEC'' when mitmas.MMBUAR = ''IBS'' then ''UOEC'' when mitmas.MMBUAR = ''IFA'' then ''UOEC'' when mitmas.MMBUAR = ''IVR'' then ''UOEC'' when mitmas.MMBUAR = ''VER'' then ''UOEC'' when mitmas.MMBUAR = ''FAI'' then ''UOEC'' when mitmas.MMBUAR = ''ITR'' then ''UOJC'' when mitmas.MMBUAR = ''STR'' then ''UOJC'' when mitmas.MMBUAR = ''TAV'' then ''UOJC'' when mitmas.MMBUAR = ''TCE'' then ''UOJC'' when mitmas.MMBUAR = ''TPR'' then ''UOJC'' when mitmas.MMBUAR = ''ETR'' then ''UOJC'' when mitmas.MMBUAR = ''IPO'' then ''UORM'' when mitmas.MMBUAR = ''POM'' then ''UORM'' else ''????'' end end end end, DECODE(mbleat, 0, 0, count(mittra.mtitno)/mbleat), 0, DECODE(mbleat, 0, 0, 360/mbleat), ValAtt_Tous, 0, QteAtt_Tous, idsuno, idsunm, mmprgp
from mvxjdta.mitbal, mvxjdta.mitmas, mvxjdta.mittra , mvxjdta.mitwhl, mvxjdta.cidmas, MSF.GA_CC_w_VALEUR_ENCOURS
where mitbal.mbcono = 800
and mitbal.mbwhlo IN (''2SD'', ''2SP'')
and mitbal.mbitno = ''GA59231''
and mitbal.mbcono = mitmas.mmcono
and mitbal.mbitno = mitmas.mmitno
and mitmas.mmstat <> 90
and mitbal.mbcono = mittra.mtcono
and mitbal.mbwhlo = mittra.mtwhlo
and mitbal.mbitno = mittra.mtitno
and ((mtttyp = 51 and mtrftx IN (''2SP=>2SD'', ''2SP=>2DM'', ''2SD=>2SP'')) or (mtttyp = 41 and mttrtp = ''SVM'') or (mtttyp = 31 and mttrtp = ''VFI'') or (mtttyp = 11 and mttrtp = ''OFS''))
and mttrdt > to_number(to_char(sysdate - 360 + (mbleat * 1), ''yyyymmdd''))
and mitbal.mbcono = mitwhl.mwcono
and mitbal.mbwhlo = mitwhl.mwwhlo
and mitmas.mmcono = cidmas.idcono
and mitmas.mmsuno = cidmas.idsuno
and 1=1
and mitbal.mbwhlo = GA_CC_w_VALEUR_ENCOURS.Depot (+)
and mitbal.mbitno = GA_CC_w_VALEUR_ENCOURS.Reference (+)
group by mwfaci, mbwhlo, mbitno, mmitds, mmstat, mbabcd, mbresp, mbleat, mmresp, mmitcl, mmbuar, idsuno, idsunm, mmprgp, ValAtt_Tous, QteAtt_Tous';
-- affichage de la requete dans DBMS Output
str_len := length(sqlSelect);
dbms_output.put_line(str_len);
while loop_count < str_len
loop
dbms_output.put_line(substr(sqlSelect, loop_count +1, 254 ));
loop_count := loop_count +254;
end loop;
-- ouverture du curseur dynamique
open cursor1 for sqlSelect;
loop
fetch cursor1 into record1;
exit when cursor1%NOTFOUND;
dbms_output.put_line(record1.ARTICLE);
end loop;
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
v_err_num := SQLCODE;
v_err_msg := SQLERRM;
DBMS_OUTPUT.PUT_LINE ('Erreur d''exécution1 : |'||v_err_num||'|'||v_err_msg);
END;
WHEN OTHERS THEN
BEGIN
v_err_num := SQLCODE;
v_err_msg := SQLERRM;
DBMS_OUTPUT.PUT_LINE ('Erreur d''exécution2 : |'||v_err_num||'|'||v_err_msg);
END;
END;
/ |
