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
|
CREATE TABLE mapu
(numero_periode NUMBER,
date_debut_periode DATE,
date_fin_periode DATE,
numero_lot NUMBER,
code_uag NUMBER,
mt_max_uag NUMBER,
id_marche NUMBER,
id_periode NUMBER);
> table créée
CREATE TABLE EJ
(montant_ht_ej NUMBER,
numero_periode NUMBER,
numero_lot NUMBER,
marche NUMBER,
code_uag NUMBER);
> table créée
CREATE TABLE marche
(id_marche NUMBER,
code_marche_national NUMBER,
code_statut VARCHAR2(1),
code_prm VARCHAR2(2));
> table créée
CREATE TABLE enveloppe
(code_marche_national NUMBER,
numero_periode NUMBER,
date_debut_periode DATE,
date_fin_periode DATE,
numero_lot NUMBER,
code_uag NUMBER,
mt_max_uag NUMBER,
diff_mt NUMBER);
> table créée
CREATE OR REPLACE PROCEDURE p_test1( pVar_codret IN out number, pOra_error IN out varchar2 ) IS
BEGIN
INSERT
INTO ENVELOPPE
SELECT DISTINCT CODE_MARCHE_NATIONAL,
MAPU.NUMERO_PERIODE,
DATE_DEBUT_PERIODE,
DATE_FIN_PERIODE,
MAPU.NUMERO_LOT,
MAPU.code_uag ,
MAPU.MT_MAX_UAG,
MAPU.MT_MAX_UAG - (SELECT NVL(SUM(MONTANT_HT_EJ),0) TOTAL
FROM EJ
WHERE EJ.NUMERO_PERIODE = MAPU.NUMERO_PERIODE
AND EJ.NUMERO_LOT = MAPU.NUMERO_LOT
AND EJ.MARCHE = V_MARCHE.CODE_MARCHE_NATIONAL
AND EJ.CODE_UAG = MAPU.code_uag)
FROM MAPU,
(SELECT DISTINCT id_marche,CODE_MARCHE_NATIONAL FROM marche
WHERE CODE_STATUT = 'N'
AND CODE_PRM <> '02') V_MARCHE, PERIODE
WHERE MAPU.id_marche = V_MARCHE.id_marche
AND MAPU.ID_PERIODE = PERIODE.ID_PERIODE;
END p_test1;
> procédure créée |
Partager