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 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119
|
PROCEDURE P_GL_UPD_STG_INTPRIXV (s_ID_INSTANCE_PROP IN VARCHAR2
,s_PGM_NAM_PROP IN VARCHAR2)
IS
-- DECLARATION DES TYPES
TYPE T_CTHEMT IS TABLE OF RGDADM.INTPRIXV.PVFNTAR%TYPE INDEX BY PLS_INTEGER;
TYPE T_CIDCMT IS TABLE OF RGDADM.INTPRIXV.PVFRESCINT%TYPE INDEX BY PLS_INTEGER;
TYPE T_CTHETM IS TABLE OF RGDADM.INTPRIXV.PVFNTAR%TYPE INDEX BY PLS_INTEGER;
TYPE T_TMAGTM IS TABLE OF NUMBER(1) INDEX BY PLS_INTEGER;
TYPE T_PVFODDEB IS TABLE OF RGDADM.INTPRIXV.PVFODDEB%TYPE INDEX BY PLS_INTEGER;
TYPE T_PVFODFIN IS TABLE OF RGDADM.INTPRIXV.PVFODFIN%TYPE INDEX BY PLS_INTEGER;
TYPE T_CTHEPP IS TABLE OF RGDADM.INTPRIXV.PVFNTAR%TYPE INDEX BY PLS_INTEGER;
-- DECLARATION DES TABLEAUX
V_CTHEMT T_CTHEMT;
V_CIDCMT T_CIDCMT;
V_CTHETM T_CTHETM;
V_TMAGTM T_TMAGTM;
V_PVFODDEB T_PVFODDEB;
V_PVFODFIN T_PVFODFIN;
V_CTHEPP T_CTHEPP;
n_max_robid NUMBER(5) := NULL;
s_max_sobcextin VARCHAR2(13) := NULL;
n_Interval_Fetch NUMBER := 10000;
CURSOR C
IS
SELECT EXT6.CTHEPP AS CTHEPP
,EXT5.CTHE AS CTHEMT
,EXT5.CIDC AS CIDCMT
,EXT4.CTHE AS CTHETM
,EXT4.TMAG AS TMAGTM
,TO_DATE(DECODE(EXT4.SDVV, '1', '20', '19')||LPAD(EXT4.DDVV,6,'0'), 'YYYYMMDD') AS DDEB
,TO_DATE(DECODE(EXT4.SFVV, '1', '20', '19')||LPAD(EXT4.DFVV,6,'0'), 'YYYYMMDD') AS DFIN
FROM DMTWRK.EXT_PT52_005_LGI EXT5
,DMTWRK.EXT_PT52_004_LGI EXT4
,DMTWRK.EXT_PT52_006_LGI EXT6
WHERE EXT4.CTHE = EXT5.CTHE
AND EXT4.CTHE = EXT6.CTHEPP
GROUP BY EXT6.CTHEPP
,EXT5.CTHE
,EXT5.CIDC
,EXT4.CTHE
,EXT4.TMAG
,TO_DATE(DECODE(EXT4.SDVV, '1', '20', '19')||LPAD(EXT4.DDVV,6,'0'), 'YYYYMMDD')
,TO_DATE(DECODE(EXT4.SFVV, '1', '20', '19')||LPAD(EXT4.DFVV,6,'0'), 'YYYYMMDD');
BEGIN
-- SELECTION DU PLUS HAUT NIVEAU DU NETWORK
-- POUR LA MISE A JOUR DE PVFRESCINT DANS EXT_PT52_005_LGI
SELECT MAX(ROBID)
INTO n_max_robid
FROM RGDADM.RESOBJ
WHERE ROBRESID = 'PN'
AND ROBPROF = 1;
-- SELECTION DU PLUS HAUT NIVEAU DE STRUCTURE MARCHANDISE POUR
-- LA MISE A JOUR DE PVFSTRUC DANS EXT_PT52_005_LGI
SELECT MAX(SOBCEXTIN)
INTO s_max_sobcextin
FROM RGDADM.STRUCOBJ
WHERE SOBIDSTR = '1'
AND SOBIDNIV = 1;
OPEN C;
LOOP
FETCH C BULK COLLECT
INTO V_CTHEPP
,V_CTHEMT
,V_CIDCMT
,V_CTHETM
,V_TMAGTM
,V_PVFODDEB
,V_PVFODFIN
Limit n_Interval_Fetch;
BEGIN
FORALL i IN 1..V_CTHEMT.COUNT
LOOP
-- MISE A JOUR DE PVFRESCINT DANS EXT_PT52_005_LGI POUR LE TMAGTM != 1
UPDATE DMTWRK.STG_INTPRIXV
SET PVFRESCINT = V_CIDCMT(i)
WHERE ID_INSTANCE = s_ID_INSTANCE_PROP
AND PVFNTAR = V_CTHETM(i)
AND V_TMAGTM(i) != 1;
-- MISE A JOUR DE PVFRESCINT DANS EXT_PT52_005_LGI POUR LE TMAGTM = 1
UPDATE DMTWRK.STG_INTPRIXV
SET PVFRESCINT = n_max_robid
WHERE ID_INSTANCE = s_ID_INSTANCE_PROP
AND PVFNTAR = V_CTHETM(i)
AND V_TMAGTM(i) = 1;
-- MISE A JOUR DE PVFSTRUC, PVFODDEB, PVFODFIN DANS EXT_PT52_005_LGI
UPDATE DMTWRK.STG_INTPRIXV
SET PVFSTRUC = s_max_sobcextin
,PVFODDEB = V_PVFODDEB(i)
,PVFODFIN = V_PVFODFIN(i)
,PVFIDDEB = V_PVFODDEB(i)
,PVFIDFIN = V_PVFODFIN(i)
WHERE ID_INSTANCE = s_ID_INSTANCE_PROP
AND PVFNTAR = V_CTHETM(i);
EXCEPTION
WHEN OTHERS THEN RAISE;
END;
EXIT WHEN C%NOTFOUND;
END LOOP;
CLOSE C;
END P_GL_UPD_STG_INTPRIXV; |
Partager