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
|
CREATE OR REPLACE PACKAGE Historique AS
TYPE HistoCurTyp IS REF CURSOR ;
PROCEDURE PR_HISTORIQUE_ETENDU (cur_histo OUT HistoCurTyp
, pClient NUMBER
, pEtab NUMBER
, pAffaires NUMBER
, pBenef VARCHAR2
, pActivite VARCHAR2
, pAssocie NUMBER
, pTypeRequete NUMBER);
END Historique;
CREATE OR REPLACE PACKAGE BODY Historique AS
PROCEDURE PR_HISTORIQUE_ETENDU (cur_histo OUT HistoCurTyp
, pClient NUMBER
, pEtab NUMBER
, pAffaires NUMBER
, pBenef VARCHAR2
, pActivite VARCHAR2
, pAssocie NUMBER
, pTypeRequete NUMBER)
AS
GINT_EcrClient Number;
GINT_EcrEtablissement Number;
str_RequeteDeb Varchar2(32767);
str_RequeteFin Varchar2(32767);
BEGIN
GINT_EcrClient := 10;
GINT_EcrEtablissement := 11;
str_RequeteDeb := 'Select H.Dat_Hst "Date", T.Lib_Tab "Niveau",' ||
' Decode(H.COD_DOC, Null, '''', H.COD_DOC || '': '') || E.Lib_Evt "Evènement", Nvl(H.Lib_ImgAvt, CO.LIB_NOMCOUDFT) "Image avant",' ||
' H.lib_ImgApr "Image après", U.Lib_NomUsr "Utilisateur",' ||
' H.Num_Eta "Etablissement", H.Num_Cli ||decode (H.Num_Aff, null,'''', ''/'')|| H.Num_Aff "Client/Affaire",' ||
' B.Lib_Bnf "Bénéficiaire", A.Lib_Act "Activité", AR.Lib_Art "Article", ' ||
' H.ROWID ' ||
' From op.tr_Utilisateurs U,' ||
' op.T_historique H,' ||
' op.Tr_Tables T, op.Tr_Evenements E,' ||
' op.tr_Beneficiaires B, op.Tr_Activites A, op.Tr_Articles AR,' ||
' op.Tr_Courriers CO ' ||
' Where T.Cod_Tab = H.Cod_Tab' ||
' And U.Cod_USr = H.Cod_Usr' ||
' And E.Cod_Evt = H.Cod_Evt' ||
' And B.Cod_Bnf (+)= H.Cod_Bnf' ||
' And A.Cod_Act (+)= H.Cod_Act' ||
' And AR.Cod_Art(+) = H.Cod_Art' ||
' And CO.Cod_Cou (+) = H.Cod_Cou' ;
str_RequeteFin := ' And (H.Num_Aff = :Affaire or :Affaire = 0 or H.Num_Aff is null)' ||
' And (H.Cod_Bnf = :Benef or :Benef is null or H.Cod_Bnf is null)' ||
' And (H.Cod_Act = :Activite or :Activite is null or H.Cod_Act is null)' ||
' And (H.Num_Ass = :Associe or :Associe = 0 or H.Num_Ass is null)' ;
--Complément de requète selon l'écran appelant
If pTypeRequete = GINT_EcrEtablissement Then
str_RequeteFin := str_RequeteFin || ' And (H.Num_Cli = :Client or :Client = 0 or H.Num_Cli is null)' ||
' And (H.Num_Eta = :Etab or :Etab = 0) ' ;
Else
If pTypeRequete = GINT_EcrClient Then
str_RequeteFin := str_RequeteFin || ' And (H.Num_Cli = :Client or :Client = 0)' ||
' And (H.Num_Eta = :Etab or :Etab = 0 or H.Num_Eta is null)' ;
Else
str_RequeteFin := str_RequeteFin || ' And (H.Num_Cli = :Client or :Client = 0 or H.Num_Cli is null)' ||
' And (H.Num_Eta = :Etab or :Etab = 0 or H.Num_Eta is null)' ;
End if;
End If;
str_RequeteFin := str_RequeteFin || ' Order by H.Dat_Hst desc' ;
OPEN cur_histo FOR
str_RequeteDeb || str_RequeteFin
Using pAffaires, pAffaires, pBenef, pBenef, pActivite, pActivite, pAssocie, pAssocie, pClient, pClient, pEtab, pEtab;
END PR_HISTORIQUE_ETENDU;
END Historique; |
