[Home] [Help]
PACKAGE BODY: APPS.AMS_DM_LIFT_PKG
Source
1 PACKAGE BODY AMS_DM_LIFT_PKG as
2 /* $Header: amstdlfb.pls 120.1 2005/06/15 23:57:13 appldev $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_DM_LIFT_PKG
7 -- Purpose
8 --
9 -- History
10 -- 26-jan-2001 choang Removed object ver num from where in update
11 -- 07-Jan-2002 choang removed security group id
12 --
13 -- NOTE
14 --
15 -- End of Comments
16 -- ===============================================================
17
18
19 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_DM_LIFT_PKG';
20 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amstdlfb.pls';
21
22 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
23 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
24 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
25
26 PROCEDURE Insert_Row(
27 px_LIFT_ID IN OUT NOCOPY NUMBER,
28 p_LAST_UPDATE_DATE DATE,
29 p_LAST_UPDATED_BY NUMBER,
30 p_CREATION_DATE DATE,
31 p_CREATED_BY NUMBER,
32 p_LAST_UPDATE_LOGIN NUMBER,
33 px_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
34 p_MODEL_ID NUMBER,
35 p_QUANTILE NUMBER,
36 p_LIFT NUMBER,
37 p_TARGETS NUMBER,
38 p_NON_TARGETS NUMBER,
39 p_TARGETS_CUMM NUMBER,
40 p_TARGET_DENSITY_CUMM NUMBER,
41 p_TARGET_DENSITY NUMBER,
42 p_MARGIN NUMBER,
43 p_ROI NUMBER,
44 p_TARGET_CONFIDENCE NUMBER,
45 p_NON_TARGET_CONFIDENCE NUMBER
46 )
47 IS
48 BEGIN
49
50
51 px_object_version_number := 1;
52
53
54 INSERT INTO AMS_DM_LIFT(
55 lift_id,
56 last_update_date,
57 last_updated_by,
58 creation_date,
59 created_by,
60 last_update_login,
61 object_version_number,
62 model_id,
63 quantile,
64 lift,
65 targets,
66 non_targets,
67 targets_cumm,
68 target_density_cumm,
69 target_density,
70 margin,
71 roi,
72 target_confidence,
73 non_target_confidence
74 ) VALUES (
75 DECODE( px_LIFT_ID, FND_API.g_miss_num, NULL, px_LIFT_ID),
76 DECODE( p_LAST_UPDATE_DATE, FND_API.g_miss_date, NULL, p_LAST_UPDATE_DATE),
77 DECODE( p_LAST_UPDATED_BY, FND_API.g_miss_num, NULL, p_LAST_UPDATED_BY),
78 DECODE( p_CREATION_DATE, FND_API.g_miss_date, NULL, p_CREATION_DATE),
79 DECODE( p_CREATED_BY, FND_API.g_miss_num, NULL, p_CREATED_BY),
80 DECODE( p_LAST_UPDATE_LOGIN, FND_API.g_miss_num, NULL, p_LAST_UPDATE_LOGIN),
81 DECODE( px_OBJECT_VERSION_NUMBER, FND_API.g_miss_num, NULL, px_OBJECT_VERSION_NUMBER),
82 DECODE( p_MODEL_ID, FND_API.g_miss_num, NULL, p_MODEL_ID),
83 DECODE( p_QUANTILE, FND_API.g_miss_num, NULL, p_QUANTILE),
84 DECODE( p_LIFT, FND_API.g_miss_num, NULL, p_LIFT),
85 DECODE( p_TARGETS, FND_API.g_miss_num, NULL, p_TARGETS),
86 DECODE( p_NON_TARGETS, FND_API.g_miss_num, NULL, p_NON_TARGETS),
87 DECODE( p_TARGETS_CUMM, FND_API.g_miss_num, NULL, p_TARGETS_CUMM),
88 DECODE( p_TARGET_DENSITY_CUMM, FND_API.g_miss_num, NULL, p_TARGET_DENSITY_CUMM),
89 DECODE( p_TARGET_DENSITY, FND_API.g_miss_num, NULL, p_TARGET_DENSITY),
90 DECODE( p_MARGIN, FND_API.g_miss_num, NULL, p_MARGIN),
91 DECODE( p_ROI, FND_API.g_miss_num, NULL, p_ROI),
92 DECODE( p_TARGET_CONFIDENCE, FND_API.g_miss_num, NULL, p_TARGET_CONFIDENCE),
93 DECODE( p_NON_TARGET_CONFIDENCE, FND_API.g_miss_num, NULL, p_NON_TARGET_CONFIDENCE)
94 );
95 END Insert_Row;
96
97 PROCEDURE Update_Row(
98 p_LIFT_ID NUMBER,
99 p_LAST_UPDATE_DATE DATE,
100 p_LAST_UPDATED_BY NUMBER,
101 p_LAST_UPDATE_LOGIN NUMBER,
102 p_OBJECT_VERSION_NUMBER NUMBER,
103 p_MODEL_ID NUMBER,
104 p_QUANTILE NUMBER,
105 p_LIFT NUMBER,
106 p_TARGETS NUMBER,
107 p_NON_TARGETS NUMBER,
108 p_TARGETS_CUMM NUMBER,
109 p_TARGET_DENSITY_CUMM NUMBER,
110 p_TARGET_DENSITY NUMBER,
111 p_MARGIN NUMBER,
112 p_ROI NUMBER,
113 p_TARGET_CONFIDENCE NUMBER,
114 p_NON_TARGET_CONFIDENCE NUMBER
115 )
116 IS
117 BEGIN
118 Update AMS_DM_LIFT
119 SET
120 lift_id = DECODE( p_LIFT_ID, FND_API.g_miss_num, LIFT_ID, p_LIFT_ID),
121 last_update_date = DECODE( p_LAST_UPDATE_DATE, FND_API.g_miss_date, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
122 last_updated_by = DECODE( p_LAST_UPDATED_BY, FND_API.g_miss_num, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
123 last_update_login = DECODE( p_LAST_UPDATE_LOGIN, FND_API.g_miss_num, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
124 object_version_number = DECODE( p_OBJECT_VERSION_NUMBER, FND_API.g_miss_num, OBJECT_VERSION_NUMBER, p_OBJECT_VERSION_NUMBER),
125 model_id = DECODE( p_MODEL_ID, FND_API.g_miss_num, MODEL_ID, p_MODEL_ID),
126 quantile = DECODE( p_QUANTILE, FND_API.g_miss_num, QUANTILE, p_QUANTILE),
127 lift = DECODE( p_LIFT, FND_API.g_miss_num, LIFT, p_LIFT),
128 targets = DECODE( p_TARGETS, FND_API.g_miss_num, TARGETS, p_TARGETS),
129 non_targets = DECODE( p_NON_TARGETS, FND_API.g_miss_num, NON_TARGETS, p_NON_TARGETS),
130 targets_cumm = DECODE( p_TARGETS_CUMM, FND_API.g_miss_num, TARGETS_CUMM, p_TARGETS_CUMM),
131 target_density_cumm = DECODE( p_TARGET_DENSITY_CUMM, FND_API.g_miss_num, TARGET_DENSITY_CUMM, p_TARGET_DENSITY_CUMM),
132 target_density = DECODE( p_TARGET_DENSITY, FND_API.g_miss_num, TARGET_DENSITY, p_TARGET_DENSITY),
133 margin = DECODE( p_MARGIN, FND_API.g_miss_num, MARGIN, p_MARGIN),
134 roi = DECODE( p_ROI, FND_API.g_miss_num, ROI, p_ROI),
135 target_confidence = DECODE( p_TARGET_CONFIDENCE, FND_API.g_miss_num, TARGET_CONFIDENCE, p_TARGET_CONFIDENCE),
136 non_target_confidence = DECODE( p_NON_TARGET_CONFIDENCE, FND_API.g_miss_num, NON_TARGET_CONFIDENCE, p_NON_TARGET_CONFIDENCE)
137 WHERE LIFT_ID = p_LIFT_ID;
138
139 IF (SQL%NOTFOUND) THEN
140 RAISE NO_DATA_FOUND;
141 END IF;
142 END Update_Row;
143
144 PROCEDURE Delete_Row(
145 p_LIFT_ID NUMBER)
146 IS
147 BEGIN
148 DELETE FROM AMS_DM_LIFT
149 WHERE LIFT_ID = p_LIFT_ID;
150 If (SQL%NOTFOUND) then
151 RAISE NO_DATA_FOUND;
152 End If;
153 END Delete_Row;
154
155 PROCEDURE Lock_Row(
156 p_LIFT_ID NUMBER,
157 p_LAST_UPDATE_DATE DATE,
158 p_LAST_UPDATED_BY NUMBER,
159 p_CREATION_DATE DATE,
160 p_CREATED_BY NUMBER,
161 p_LAST_UPDATE_LOGIN NUMBER,
162 p_OBJECT_VERSION_NUMBER NUMBER,
163 p_MODEL_ID NUMBER,
164 p_QUANTILE NUMBER,
165 p_LIFT NUMBER,
166 p_TARGETS NUMBER,
167 p_NON_TARGETS NUMBER,
168 p_TARGETS_CUMM NUMBER,
169 p_TARGET_DENSITY_CUMM NUMBER,
170 p_TARGET_DENSITY NUMBER,
171 p_MARGIN NUMBER,
172 p_ROI NUMBER,
173 p_TARGET_CONFIDENCE NUMBER,
174 p_NON_TARGET_CONFIDENCE NUMBER
175 )
176 IS
177 CURSOR C IS
178 SELECT *
179 FROM AMS_DM_LIFT
180 WHERE LIFT_ID = p_LIFT_ID
181 FOR UPDATE of LIFT_ID NOWAIT;
182 Recinfo C%ROWTYPE;
183 BEGIN
184 OPEN c;
185 FETCH c INTO Recinfo;
186 If (c%NOTFOUND) then
187 CLOSE c;
188 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
189 APP_EXCEPTION.RAISE_EXCEPTION;
190 END IF;
191 CLOSE C;
192 IF (
193 ( Recinfo.LIFT_ID = p_LIFT_ID)
194 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
195 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
196 AND ( p_LAST_UPDATE_DATE IS NULL )))
197 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
198 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
199 AND ( p_LAST_UPDATED_BY IS NULL )))
200 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
201 OR ( ( Recinfo.CREATION_DATE IS NULL )
202 AND ( p_CREATION_DATE IS NULL )))
203 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
204 OR ( ( Recinfo.CREATED_BY IS NULL )
205 AND ( p_CREATED_BY IS NULL )))
206 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
207 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
208 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
209 AND ( ( Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
210 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
211 AND ( p_OBJECT_VERSION_NUMBER IS NULL )))
212 AND ( ( Recinfo.MODEL_ID = p_MODEL_ID)
213 OR ( ( Recinfo.MODEL_ID IS NULL )
214 AND ( p_MODEL_ID IS NULL )))
215 AND ( ( Recinfo.QUANTILE = p_QUANTILE)
216 OR ( ( Recinfo.QUANTILE IS NULL )
217 AND ( p_QUANTILE IS NULL )))
218 AND ( ( Recinfo.LIFT = p_LIFT)
219 OR ( ( Recinfo.LIFT IS NULL )
220 AND ( p_LIFT IS NULL )))
221 AND ( ( Recinfo.TARGETS = p_TARGETS)
222 OR ( ( Recinfo.TARGETS IS NULL )
223 AND ( p_TARGETS IS NULL )))
224 AND ( ( Recinfo.NON_TARGETS = p_NON_TARGETS)
225 OR ( ( Recinfo.NON_TARGETS IS NULL )
226 AND ( p_NON_TARGETS IS NULL )))
227 AND ( ( Recinfo.TARGETS_CUMM = p_TARGETS_CUMM)
228 OR ( ( Recinfo.TARGETS_CUMM IS NULL )
229 AND ( p_TARGETS_CUMM IS NULL )))
230 AND ( ( Recinfo.TARGET_DENSITY_CUMM = p_TARGET_DENSITY_CUMM)
231 OR ( ( Recinfo.TARGET_DENSITY_CUMM IS NULL )
232 AND ( p_TARGET_DENSITY_CUMM IS NULL )))
233 AND ( ( Recinfo.TARGET_DENSITY = p_TARGET_DENSITY)
234 OR ( ( Recinfo.TARGET_DENSITY IS NULL )
235 AND ( p_TARGET_DENSITY IS NULL )))
236 AND ( ( Recinfo.MARGIN = p_MARGIN)
237 OR ( ( Recinfo.MARGIN IS NULL )
238 AND ( p_MARGIN IS NULL )))
239 AND ( ( Recinfo.ROI = p_ROI)
240 OR ( ( Recinfo.ROI IS NULL )
241 AND ( p_ROI IS NULL )))
242 AND ( ( Recinfo.TARGET_CONFIDENCE = p_TARGET_CONFIDENCE)
243 OR ( ( Recinfo.TARGET_CONFIDENCE IS NULL )
244 AND ( p_TARGET_CONFIDENCE IS NULL )))
245 AND ( ( Recinfo.NON_TARGET_CONFIDENCE = p_NON_TARGET_CONFIDENCE)
246 OR ( ( Recinfo.NON_TARGET_CONFIDENCE IS NULL )
247 AND ( p_NON_TARGET_CONFIDENCE IS NULL )))
248 ) THEN
249 RETURN;
250 ELSE
251 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
252 APP_EXCEPTION.RAISE_EXCEPTION;
253 END IF;
254 END Lock_Row;
255
256 END AMS_DM_LIFT_PKG;