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; |
Partager