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
120
121
122
123
124
125
126
127
128
129
|
PROCEDURE GetCreate_BHR_BREACH_PED(
pThresholdType IN NUMBER,
pID_BREACH_MAIN IN NUMBER,
pTreatmentDate IN DATE,
pNominal IN NUMBER,
pAmount IN NUMBER,
pLEGAL_RATIO IN NUMBER
)
IS
vID_BREACH_PED NUMBER := null;
vID_BREACH_PED_ITM NUMBER := null;
BEGIN
BEGIN
SELECT P.ID_BREACH_PED INTO
vID_BREACH_PED
FROM
BHR_BREACH_PED P
WHERE
P.FK_BREACH_MAIN = pID_BREACH_MAIN
AND
P.FK_TYP_BREACH = pThresholdType
AND
P.FK_PED_STATUS = 1;
exception
when NO_DATA_FOUND then
vID_BREACH_PED := null;
END;
IF (vID_BREACH_PED is null) THEN
--Creation de l'enregistrement
INSERT INTO BHR_BREACH_PED (
FK_BREACH_MAIN,
IS_ARCHIVED,
FK_TYP_BREACH,
FK_PED_STATUS,
START_DATE,
RM_MSG
) VALUES (
pID_BREACH_MAIN,
'N',
pThresholdType,
1,
pTreatmentDate,
''
) returning BHR_BREACH_PED.ID_BREACH_PED into vID_BREACH_PED;
END IF;
/*
-- Creation de l'element
MERGE INTO BHR_BREACH_PED_ITM P1
USING
(
SELECT
Ptmp.ID_BREACH_PED_ITM,
Ptmp.FK_BREACH_PED,
Ptmp.DATE_RATIO,
Ptmp.NOMINAL,
Ptmp.AMOUNT
FROM
BHR_BREACH_PED_ITM Ptmp
WHERE
Ptmp.FK_BREACH_PED = vID_BREACH_PED
AND
Ptmp.DATE_RATIO = pTreatmentDate
) P2 ON
(
P1.ID_BREACH_PED_ITM = P2.ID_BREACH_PED_ITM
)
WHEN MATCHED THEN
UPDATE SET
P1.NOMINAL = pNominal,
P1.AMOUNT = pAmount,
P1.LEGAL_RATIO = pLEGAL_RATIO
WHEN NOT MATCHED THEN
INSERT (
FK_BREACH_PED,
DATE_RATIO,
NOMINAL,
AMOUNT,
LEGAL_RATIO
) VALUES (
vID_BREACH_PED,
pTreatmentDate,
pNominal,
pAmount,
pLEGAL_RATIO
);
*/
BEGIN
SELECT
Ptmp.ID_BREACH_PED_ITM
INTO
vID_BREACH_PED_ITM
FROM
BHR_BREACH_PED_ITM Ptmp
WHERE
Ptmp.FK_BREACH_PED = vID_BREACH_PED
AND
Ptmp.DATE_RATIO = pTreatmentDate;
exception
when NO_DATA_FOUND then
vID_BREACH_PED_ITM := null;
END;
IF vID_BREACH_PED_ITM is null THEN
INSERT INTO BHR_BREACH_PED_ITM (
FK_BREACH_PED,
DATE_RATIO,
NOMINAL,
AMOUNT,
LEGAL_RATIO
) VALUES (
vID_BREACH_PED,
pTreatmentDate,
pNominal,
pAmount,
pLEGAL_RATIO
) returning BHR_BREACH_PED_ITM.ID_BREACH_PED_ITM into vID_BREACH_PED_ITM;
ELSE
UPDATE BHR_BREACH_PED_ITM P1 SET
P1.NOMINAL = pNominal,
P1.AMOUNT = pAmount,
P1.LEGAL_RATIO = pLEGAL_RATIO
WHERE
P1.ID_BREACH_PED_ITM = vID_BREACH_PED_ITM;
END IF;
END; |
