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