CREATE OR REPLACE package body engagement as
procedure tdd_eng_ListeConcours ( pUserId in VARCHAR2, pConNum in VARCHAR2, pConLieu in VARCHAR2, pConDisc in VARCHAR2, pConCat in VARCHAR2, pDateDeb in VARCHAR2, pDateFin in VARCHAR2, pInit in VARCHAR2, pNbMaxEltPage in VARCHAR2, pNumPage in VARCHAR2, pSelClos in VARCHAR2, pmObjId out WNP_TOOLS.AStype, pmIndex out WNP_TOOLS.AStype, pmConCat out WNP_TOOLS.AStype, pmConLieu out WNP_TOOLS.AStype, pmConDisc out WNP_TOOLS.AStype, pmDiscLib out WNP_TOOLS.AStype, pmConDept out WNP_TOOLS.AStype, pmConDate2 out WNP_TOOLS.AStype, pmConClot out WNP_TOOLS.AStype, pmConNum out WNP_TOOLS.AStype, pmDateCloture_SSAAMMJJ out WNP_TOOLS.AStype, pmConcoursEtat out WNP_TOOLS.AStype, pNbTrop out VARCHAR2, pNbPage out VARCHAR2, EvenementDeRetour out VARCHAR2 ) is
viConNum VARCHAR2(13);
viConLieu VARCHAR2(40);
viConDisc VARCHAR2(4);
viConCat VARCHAR2(8);
viDateDeb Date;
viDateFin Date;
tmpDateDeb VARCHAR2(10);
tmpDateFin VARCHAR2(10);
ROBID NUMBER;
DESIGNATION VARCHAR2(40);
CODE_DEP VARCHAR2(2);
CODE_DISC VARCHAR2(2);
LIBELLE VARCHAR2(40);
DATEDEBUT VARCHAR2(10);
DATEFIN VARCHAR2(10);
DATE_ENG DATE;
NUMEROSEQUENCE NUMBER(9);
ETATCONCOURS VARCHAR2(255);
Id VARCHAR2(15);
i number;
i1 number;
A EXCEPTION;
viNumPage number;
NbEltRemontes integer;
TropEltRemontes integer;
Premier_Elt integer;
vomObjId WNP_TOOLS.AStype;
vomIndex WNP_TOOLS.AStype;
vomConCat WNP_TOOLS.AStype;
vomConLieu WNP_TOOLS.AStype;
vomConDisc WNP_TOOLS.AStype;
vomDiscLib WNP_TOOLS.AStype;
vomConDept WNP_TOOLS.AStype;
vomConDate2 WNP_TOOLS.AStype;
vomConClot WNP_TOOLS.AStype;
vomConNum WNP_TOOLS.AStype;
vomDateCloture_SSAAMMJJ WNP_TOOLS.AStype;
vomConcoursEtat WNP_TOOLS.AStype;
sql_stmt VARCHAR2(1200);
sql_count_stmt VARCHAR2(1200);
sql_where_stmt VARCHAR2(1000);
type rc is ref cursor;
rec_ListeCon rc;
BEGIN
sql_stmt := 'SELECT DISTINCT ref_con.ROBID, ref_con.DESIGNATION, ref_dep.CODE AS CODE_DEP,
ref_disc.CODE AS CODE_DISC, ref_disc.LIBELLE, to_char(ref_con.DATEDEBUT,''DD/MM/YYYY'') AS DATEDEBUT,
to_char(ref_con.DATEFIN,''DD/MM/YYYY'') AS DATEFIN,
ref_d.DATECLOTUREENGAGEMENTS AS DATE_ENG, ref_con.NUMEROSEQUENCE, ref_con.ETATCONCOURS
FROM refconcours ref_con, refdepartement ref_dep, refdetconc ref_d, refdiscipline ref_disc, refconccatconc ref_conc_cat_c, refcategorieconcours ref_cat_c
WHERE ref_con.DEPARTEMENT_ROBID = ref_dep.ROBID AND ref_con.ROBID = ref_d.CONCOURS_ROBID
AND ref_con.DISCIPLINE_ROBID = ref_disc.ROBID
AND ref_conc_cat_c.CONCOURS_ROBID = ref_con.ROBID
AND ref_conc_cat_c.CATEGORIECONCOURS_ROBID = ref_cat_c.ROBID
AND ref_disc.ROBID <> 6';
sql_where_stmt := '';
--
IF( NVL( pSelClos, 0 ) = 0 ) THEN
sql_where_stmt := sql_where_stmt || ' AND ref_con.ETATCONCOURS = ''Ouvert aux engagements''';
sql_where_stmt := sql_where_stmt || ' AND ref_con.FERMEMINITEL = ''false''';
ELSE
sql_where_stmt := sql_where_stmt || ' AND ref_con.ETATCONCOURS IN (''Ouvert aux engagements'',''Engagements extraits'')';
END IF;
-- On ajoute le pConNum pour les requêtes si nécessaire
IF (NVL(pConNum,' ') <> ' ') THEN
sql_where_stmt := sql_where_stmt || ' AND ref_con.NUMEROSEQUENCE LIKE ''' || pConNum || '%''';
END IF;
-- On ajoute le pConLieu pour les requêtes si nécessaire
IF (NVL(pConLieu,' ') <> ' ') THEN
sql_where_stmt := sql_where_stmt || ' AND ref_con.DESIGNATION LIKE ''%' || TRIM(UPPER(REPLACE(pConLieu,'''',''''''))) || '%''';
END IF;
-- On ajoute le pConDisc pour les requêtes si nécessaire
IF (NVL(pConDisc,' ') <> ' ') THEN
sql_where_stmt := sql_where_stmt || ' AND ref_disc.CODE LIKE ''%' || TRIM(UPPER(pConDisc)) || '%''';
END IF;
-- On ajoute le pConCat pour les requêtes si nécessaire
IF (NVL(pConCat,' ') <> ' ') THEN
sql_where_stmt := sql_where_stmt || ' AND ref_cat_c.CODE LIKE ''%' || TRIM(UPPER(pConCat)) || '%''';
END IF;
-- On ajoute le pDateDeb pour les requêtes si nécessaire
IF (NVL(pDateDeb,' ') <> ' ') THEN
sql_where_stmt := sql_where_stmt || ' AND ref_con.DATEDEBUT >= TO_DATE(''' || SUBSTR(pDateDeb, 1, 2) || '/' || SUBSTR(pDateDeb, 3, 2) || '/' || SUBSTR(pDateDeb, 5, 4) || ''',''DD/MM/YYYY'')';
ELSE
null;
END IF;
-- On ajoute le pDateFin pour les requêtes si nécessaire
IF (NVL(pDateFin,' ') <> ' ') THEN
sql_where_stmt := sql_where_stmt || ' AND ref_con.DATEFIN <= TO_DATE(''' || SUBSTR(pDateFin, 1, 2) || '/' || SUBSTR(pDateFin, 3, 2) || '/' || SUBSTR(pDateFin, 5, 4) || ''',''DD/MM/YYYY'')';
END IF;
viNumPage := TO_NUMBER(pNumPage);
-- Formatage de la première ligne de sortie du tableau
BEGIN
IF (viNumPage = 0) THEN
EvenementDeRetour := 'ERRPAR';
RAISE A;
END IF;
EXCEPTION
WHEN A THEN
i := 1;
pmObjId(i) := ' ';
pmIndex(i) := ' ';
pmConCat(i) := ' ';
pmConLieu(i) := ' ';
pmConDisc(i) := ' ';
pmDiscLib(i) := ' ';
pmConDept(i) := ' ';
pmConDate2(i) := ' ';
pmConClot(i) := ' ';
pmConNum(i) := ' ';
pmDateCloture_SSAAMMJJ(i) := ' ';
pmConcoursEtat(i) := ' ';
pNbTrop := 0;
pNbPage := 0;
RETURN;
END;
-- Renvoi l'évènement de retour par défaut
vomObjid(1) := ' ';
vomIndex(1) := ' ';
vomConCat(1) := ' ';
vomConLieu(1) := ' ';
vomConDisc(1) := ' ';
vomDiscLib(1) := ' ';
vomConDept(1) := ' ';
vomConDate2(1) := ' ';
vomConClot(1) := ' ';
vomConNum(1) := ' ';
vomDateCloture_SSAAMMJJ(1) := ' ';
vomConcoursEtat(1) := ' ';
pNbTrop := 0;
pNbPage := 0;
BEGIN
sql_count_stmt := 'SELECT count(DISTINCT (ref_con.ROBID))
FROM refconcours ref_con, refdepartement ref_dep, refdetconc ref_d, refdiscipline ref_disc, refconccatconc ref_conc_cat_c, refcategorieconcours ref_cat_c
WHERE ref_con.DEPARTEMENT_ROBID = ref_dep.ROBID AND ref_con.ROBID = ref_d.CONCOURS_ROBID
AND ref_con.DISCIPLINE_ROBID = ref_disc.ROBID
AND ref_conc_cat_c.CONCOURS_ROBID = ref_con.ROBID
AND ref_conc_cat_c.CATEGORIECONCOURS_ROBID = ref_cat_c.ROBID
AND ref_disc.ROBID <> 6' || sql_where_stmt;
EXECUTE IMMEDIATE sql_count_stmt INTO NbEltRemontes;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NbEltRemontes := 0;
END;
IF (pInit = 1) THEN
TropEltRemontes := WNP_FUNCTION.recupererEltMaxListe('LMXCONC'); --MaxEngListeConcours
IF (NbEltRemontes = 0) THEN
EvenementDeRetour := 'TDD_ZERO_ELT';
RETURN;
ELSIF (NbEltRemontes > TropEltRemontes) THEN
pNbTrop := NbEltRemontes;
EvenementDeRetour := 'TDD_TROP_ELT';
RETURN;
ELSIF (pInit = 1) THEN
pNbPage := WNP_FUNCTION.getNbPagesListe(TO_NUMBER(NbEltRemontes) ,TO_NUMBER(pNbMaxEltPage));
END IF;
ELSE
pNbPage := 0;
--NbEltRemontes := 0;
END IF;
Premier_Elt := WNP_FUNCTION.getPremierElementPage(TO_NUMBER(pNbMaxEltPage) ,TO_NUMBER(pNumPage));
IF (Premier_Elt is null) THEN
EvenementDeRetour := 'ERRPAR';
Return;
END IF;
pNbTrop := NbEltRemontes;
-- Remonter de la liste des concours
i:=1;
sql_stmt := sql_stmt || sql_where_stmt;
OPEN rec_ListeCon FOR sql_stmt;
LOOP
FETCH rec_ListeCon INTO ROBID, DESIGNATION, CODE_DEP, CODE_DISC, LIBELLE, DATEDEBUT, DATEFIN, DATE_ENG, NUMEROSEQUENCE, ETATCONCOURS;
EXIT WHEN rec_ListeCon%NOTFOUND;
IF (ROBID IS NULL) THEN
EvenementDeRetour := 'ERRWRN';
return;
END IF;
Id := WNP_FUNCTION.GET_ID(ROBID,NUMEROSEQUENCE,'PreConcours','REFCONCOURS','NUMEROSEQUENCE');
vomObjId(i) := WNP_TOOLS.FMT_CHAINE( WNP_TOOLS.GET_FMT( 'tdd_eng_ListeConcours','pmObjId' ), Id);
vomConCat(i) := WNP_TOOLS.FMT_CHAINE( WNP_TOOLS.GET_FMT( 'tdd_eng_ListeConcours','pmConCat' ), WNP_FUNCTION.rechercherCategorieConcours(ROBID) );
vomConLieu(i) := WNP_TOOLS.FMT_CHAINE( WNP_TOOLS.GET_FMT( 'tdd_eng_ListeConcours','pmConLieu' ), DESIGNATION );
vomConDisc(i) := WNP_TOOLS.FMT_CHAINE( WNP_TOOLS.GET_FMT( 'tdd_eng_ListeConcours','pmConDisc' ), CODE_DISC );
vomDiscLib(i) := WNP_TOOLS.FMT_CHAINE( WNP_TOOLS.GET_FMT( 'tdd_eng_ListeConcours','pmDiscLib' ), LIBELLE );
vomConDept(i) := WNP_TOOLS.FMT_CHAINE( WNP_TOOLS.GET_FMT( 'tdd_eng_ListeConcours','pmConDept' ), CODE_DEP );
vomConDate2(i):= WNP_TOOLS.FMT_CHAINE( WNP_TOOLS.GET_FMT( 'tdd_eng_ListeConcours','pmConDate2' ), 'DU '||SUBSTR(DATEDEBUT,1,5)||' AU '||DATEFIN );
vomConClot(i) := WNP_TOOLS.FMT_DATE( WNP_TOOLS.GET_FMT( 'tdd_eng_ListeConcours','pmConClot' ), DATE_ENG );
vomConNum(i) := WNP_TOOLS.FMT_CHAINE( WNP_TOOLS.GET_FMT( 'tdd_eng_ListeConcours','pmConNum' ), NUMEROSEQUENCE );
vomDateCloture_SSAAMMJJ(i) := WNP_TOOLS.FMT_DATE( WNP_TOOLS.GET_FMT( 'tdd_eng_ListeConcours','pmDateCloture_SSAAMMJJ' ), DATE_ENG) ;
IF (ETATCONCOURS = 'Ouvert aux engagements') THEN
vomConcoursEtat(i) := WNP_TOOLS.FMT_CHAINE( WNP_TOOLS.GET_FMT( 'tdd_eng_ListeConcours','pmConcoursEtat' ), 'O' );
ELSE
vomConcoursEtat(i) := WNP_TOOLS.FMT_CHAINE( WNP_TOOLS.GET_FMT( 'tdd_eng_ListeConcours','pmConcoursEtat' ), 'C' );
END IF;
i := i + 1;
END LOOP;
i1 := i1 + 1;
FOR i1 IN 1..(TO_NUMBER(pNbMaxEltPage)) LOOP
IF ((i1+Premier_Elt) <= TO_NUMBER(NbEltRemontes)) THEN
pmObjId(i1) := vomObjId(i1+Premier_Elt);
pmIndex(i1) := TO_CHAR(i1);
pmConCat(i1) := vomConCat(i1+Premier_Elt);
pmConLieu(i1) := vomConLieu(i1+Premier_Elt);
pmConDisc(i1) := vomConDisc(i1+Premier_Elt);
pmDiscLib(i1) := vomDiscLib(i1+Premier_Elt);
pmConDept(i1) := vomConDept(i1+Premier_Elt);
pmConDate2(i1):= vomConDate2(i1+Premier_Elt);
pmConClot(i1) := vomConClot(i1+Premier_Elt);
pmConNum(i1) := vomConNum(i1+Premier_Elt);
pmDateCloture_SSAAMMJJ(i1) := vomDateCloture_SSAAMMJJ(i1+Premier_Elt);
pmConcoursEtat(i1) := vomConcoursEtat(i1+Premier_Elt);
END IF;
END LOOP;
pNbTrop := WNP_TOOLS.FMT_ENTIER( WNP_TOOLS.GET_FMT( 'tdd_eng_ListeConcours','pNbTrop' ), pNbTrop );
pNbPage := WNP_TOOLS.FMT_ENTIER( WNP_TOOLS.GET_FMT( 'tdd_eng_ListeConcours','pNbPage' ), pNbPage );
IF (NbEltRemontes = 1) THEN
EvenementDeRetour := 'TDD_UN_ELT';
ELSE
EvenementDeRetour := 'TDD_OK';
END IF;
END;
|