[Home] [Help]
PACKAGE BODY: APPS.AMS_IBA_PS_RULES_PKG
Source
1 PACKAGE BODY AMS_IBA_PS_RULES_PKG as
2 /* $Header: amstrulb.pls 120.0 2005/05/31 14:42:20 appldev noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_IBA_PS_RULES_PKG
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_IBA_PS_RULES_PKG';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amstrulb.pls';
19
20
21 ----------------------------------------------------------
22 ---- MEDIA ----
23 ----------------------------------------------------------
24
25 -- ========================================================
26 --
27 -- NAME
28 -- createInsertBody
29 --
30 -- PURPOSE
31 --
32 -- NOTES
33 --
34 -- HISTORY
35 --
36 -- ========================================================
37 PROCEDURE Insert_Row(
38 p_created_by NUMBER,
39 p_creation_date DATE,
40 p_last_updated_by NUMBER,
41 p_last_update_date DATE,
42 p_last_update_login NUMBER,
43 px_object_version_number IN OUT NOCOPY NUMBER,
44 px_rule_id IN OUT NOCOPY NUMBER,
45 p_rulegroup_id NUMBER,
46 p_posting_id NUMBER,
47 p_strategy_id NUMBER,
48 p_exec_priority NUMBER,
49 p_bus_priority_code VARCHAR2,
50 p_bus_priority_disp_order VARCHAR2,
51 p_clausevalue1 VARCHAR2,
52 p_clausevalue2 NUMBER,
53 p_clausevalue3 VARCHAR2,
54 p_clausevalue4 VARCHAR2,
55 p_clausevalue5 NUMBER,
56 p_clausevalue6 VARCHAR2,
57 p_clausevalue7 VARCHAR2,
58 p_clausevalue8 VARCHAR2,
59 p_clausevalue9 VARCHAR2,
60 p_clausevalue10 VARCHAR2,
61 p_use_clause6 VARCHAR2,
62 p_use_clause7 VARCHAR2,
63 p_use_clause8 VARCHAR2,
64 p_use_clause9 VARCHAR2,
65 p_use_clause10 VARCHAR2)
66
67 IS
68 x_rowid VARCHAR2(30);
69
70
71 BEGIN
72
73
74 px_object_version_number := 1;
75
76
77 INSERT INTO AMS_IBA_PS_RULES(
78 created_by,
79 creation_date,
80 last_updated_by,
81 last_update_date,
82 last_update_login,
83 object_version_number,
84 rule_id,
85 rulegroup_id,
86 posting_id,
87 strategy_id,
88 exec_priority,
89 bus_priority_code,
90 bus_priority_disp_order,
91 clausevalue1,
92 clausevalue2,
93 clausevalue3,
94 clausevalue4,
95 clausevalue5,
96 clausevalue6,
97 clausevalue7,
98 clausevalue8,
99 clausevalue9,
100 clausevalue10,
101 use_clause6,
102 use_clause7,
103 use_clause8,
104 use_clause9,
105 use_clause10
106 ) VALUES (
107 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
108 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
109 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
110 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
111 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
112 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
113 DECODE( px_rule_id, FND_API.g_miss_num, NULL, px_rule_id),
114 DECODE( p_rulegroup_id, FND_API.g_miss_num, NULL, p_rulegroup_id),
115 DECODE( p_posting_id, FND_API.g_miss_num, NULL, p_posting_id),
116 DECODE( p_strategy_id, FND_API.g_miss_num, NULL, p_strategy_id),
117 DECODE( p_exec_priority, FND_API.g_miss_num, NULL, p_exec_priority),
118 DECODE( p_bus_priority_code, FND_API.g_miss_char, NULL, p_bus_priority_code),
119 DECODE( p_bus_priority_disp_order, FND_API.g_miss_char, NULL, p_bus_priority_disp_order),
120 DECODE( p_clausevalue1, FND_API.g_miss_char, NULL, p_clausevalue1),
121 DECODE( p_clausevalue2, FND_API.g_miss_num, NULL, p_clausevalue2),
122 DECODE( p_clausevalue3, FND_API.g_miss_char, NULL, p_clausevalue3),
123 DECODE( p_clausevalue4, FND_API.g_miss_char, NULL, p_clausevalue4),
124 DECODE( p_clausevalue5, FND_API.g_miss_num, NULL, p_clausevalue5),
125 DECODE( p_clausevalue6, FND_API.g_miss_char, NULL, p_clausevalue6),
126 DECODE( p_clausevalue7, FND_API.g_miss_char, NULL, p_clausevalue7),
127 DECODE( p_clausevalue8, FND_API.g_miss_char, NULL, p_clausevalue8),
128 DECODE( p_clausevalue9, FND_API.g_miss_char, NULL, p_clausevalue9),
129 DECODE( p_clausevalue10, FND_API.g_miss_char, NULL, p_clausevalue10),
130 DECODE( p_use_clause6, FND_API.g_miss_char, NULL, p_use_clause6),
131 DECODE( p_use_clause7, FND_API.g_miss_char, NULL, p_use_clause7),
132 DECODE( p_use_clause8, FND_API.g_miss_char, NULL, p_use_clause8),
133 DECODE( p_use_clause9, FND_API.g_miss_char, NULL, p_use_clause9),
134 DECODE( p_use_clause10, FND_API.g_miss_char, NULL, p_use_clause10));
135
136 END Insert_Row;
137
138
139 ----------------------------------------------------------
140 ---- MEDIA ----
141 ----------------------------------------------------------
142
143 -- ======================================================
144 --
145 -- NAME
146 -- createUpdateBody
147 --
148 -- PURPOSE
149 --
150 -- NOTES
151 --
152 -- HISTORY
153 --
154 -- ======================================================
155 PROCEDURE Update_Row(
156 p_created_by NUMBER,
157 p_creation_date DATE,
158 p_last_updated_by NUMBER,
159 p_last_update_date DATE,
160 p_last_update_login NUMBER,
161 p_object_version_number NUMBER,
162 p_rule_id NUMBER,
163 p_rulegroup_id NUMBER,
164 p_posting_id NUMBER,
165 p_strategy_id NUMBER,
166 p_exec_priority NUMBER,
167 p_bus_priority_code VARCHAR2,
168 p_bus_priority_disp_order VARCHAR2,
169 p_clausevalue1 VARCHAR2,
170 p_clausevalue2 NUMBER,
171 p_clausevalue3 VARCHAR2,
172 p_clausevalue4 VARCHAR2,
173 p_clausevalue5 NUMBER,
174 p_clausevalue6 VARCHAR2,
175 p_clausevalue7 VARCHAR2,
176 p_clausevalue8 VARCHAR2,
177 p_clausevalue9 VARCHAR2,
178 p_clausevalue10 VARCHAR2,
179 p_use_clause6 VARCHAR2,
180 p_use_clause7 VARCHAR2,
181 p_use_clause8 VARCHAR2,
182 p_use_clause9 VARCHAR2,
183 p_use_clause10 VARCHAR2)
184 IS
185 BEGIN
186 Update AMS_IBA_PS_RULES
187 SET
188 created_by = DECODE( p_created_by, FND_API.g_miss_num, created_by, p_created_by),
189 creation_date = DECODE( p_creation_date, FND_API.g_miss_date, creation_date, p_creation_date),
190 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
191 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
192 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
193 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number),
194 rule_id = DECODE( p_rule_id, FND_API.g_miss_num, rule_id, p_rule_id),
195 rulegroup_id = DECODE( p_rulegroup_id, FND_API.g_miss_num, rulegroup_id, p_rulegroup_id),
196 posting_id = DECODE( p_posting_id, FND_API.g_miss_num, posting_id, p_posting_id),
197 strategy_id = DECODE( p_strategy_id, FND_API.g_miss_num, strategy_id, p_strategy_id),
198 bus_priority_code = DECODE( p_bus_priority_code, FND_API.g_miss_char, bus_priority_code, p_bus_priority_code),
199 bus_priority_disp_order = DECODE( p_bus_priority_disp_order, FND_API.g_miss_char, bus_priority_disp_order, p_bus_priority_disp_order),
200 clausevalue1 = DECODE( p_clausevalue1, FND_API.g_miss_char, clausevalue1, p_clausevalue1),
201 clausevalue2 = DECODE( p_clausevalue2, FND_API.g_miss_num, clausevalue2, p_clausevalue2),
202 clausevalue3 = DECODE( p_clausevalue3, FND_API.g_miss_char, clausevalue3, p_clausevalue3),
203 clausevalue4 = DECODE( p_clausevalue4, FND_API.g_miss_char, clausevalue4, p_clausevalue4),
204 clausevalue5 = DECODE( p_clausevalue5, FND_API.g_miss_num, clausevalue5, p_clausevalue5),
205 clausevalue6 = DECODE( p_clausevalue6, FND_API.g_miss_char, clausevalue6, p_clausevalue6),
206 clausevalue7 = DECODE( p_clausevalue7, FND_API.g_miss_char, clausevalue7, p_clausevalue7),
207 clausevalue8 = DECODE( p_clausevalue8, FND_API.g_miss_char, clausevalue8, p_clausevalue8),
208 clausevalue9 = DECODE( p_clausevalue9, FND_API.g_miss_char, clausevalue9, p_clausevalue9),
209 clausevalue10 = DECODE( p_clausevalue10, FND_API.g_miss_char, clausevalue10, p_clausevalue10),
210 use_clause6 = DECODE( p_use_clause6, FND_API.g_miss_char, use_clause6, p_use_clause6),
211 use_clause7 = DECODE( p_use_clause7, FND_API.g_miss_char, use_clause7, p_use_clause7),
212 use_clause8 = DECODE( p_use_clause8, FND_API.g_miss_char, use_clause8, p_use_clause8),
213 use_clause9 = DECODE( p_use_clause9, FND_API.g_miss_char, use_clause9, p_use_clause9),
214 use_clause10 = DECODE( p_use_clause10, FND_API.g_miss_char, use_clause10, p_use_clause10)
215
216 WHERE RULE_ID = p_RULE_ID
217 AND object_version_number = p_object_version_number;
218
219 IF (SQL%NOTFOUND) THEN
220 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
221 END IF;
222 END Update_Row;
223
224 ----------------------------------------------------------
225 ---- MEDIA ----
226 ----------------------------------------------------------
227
228 -- ======================================================
229 --
230 -- NAME
231 -- createDeleteBody
232 --
233 -- PURPOSE
234 --
235 -- NOTES
236 --
237 -- HISTORY
238 --
239 -- ======================================================
240 PROCEDURE Delete_Row(
241 p_RULE_ID NUMBER)
242 IS
243 BEGIN
244 DELETE FROM AMS_IBA_PS_RULES
245 WHERE RULE_ID = p_RULE_ID;
246 If (SQL%NOTFOUND) then
247 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
248 End If;
249 END Delete_Row ;
250
251
252
253 ----------------------------------------------------------
254 ---- MEDIA ----
255 ----------------------------------------------------------
256
257 -- ======================================================
258 --
259 -- NAME
260 -- createLockBody
261 --
262 -- PURPOSE
263 --
264 -- NOTES
265 --
266 -- HISTORY
267 --
268 -- ======================================================
269 PROCEDURE Lock_Row(
270 p_created_by NUMBER,
271 p_creation_date DATE,
272 p_last_updated_by NUMBER,
273 p_last_update_date DATE,
274 p_last_update_login NUMBER,
275 p_object_version_number NUMBER,
276 p_rule_id NUMBER,
277 p_rulegroup_id NUMBER,
278 p_posting_id NUMBER,
279 p_strategy_id NUMBER,
280 p_bus_priority_code VARCHAR2,
281 p_bus_priority_disp_order VARCHAR2,
282 p_clausevalue1 VARCHAR2,
283 p_clausevalue2 NUMBER,
284 p_clausevalue3 VARCHAR2,
285 p_clausevalue4 VARCHAR2,
286 p_clausevalue5 NUMBER,
287 p_clausevalue6 VARCHAR2,
288 p_clausevalue7 VARCHAR2,
289 p_clausevalue8 VARCHAR2,
290 p_clausevalue9 VARCHAR2,
291 p_clausevalue10 VARCHAR2)
292
293 IS
294 CURSOR C IS
295 SELECT *
296 FROM AMS_IBA_PS_RULES
297 WHERE RULE_ID = p_RULE_ID
298 FOR UPDATE of RULE_ID NOWAIT;
299 Recinfo C%ROWTYPE;
300 BEGIN
301 OPEN c;
302 FETCH c INTO Recinfo;
303 If (c%NOTFOUND) then
304 CLOSE c;
305 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
306 APP_EXCEPTION.RAISE_EXCEPTION;
307 END IF;
308 CLOSE C;
309 IF (
310 ( Recinfo.created_by = p_created_by)
311 AND ( ( Recinfo.creation_date = p_creation_date)
312 OR ( ( Recinfo.creation_date IS NULL )
313 AND ( p_creation_date IS NULL )))
314 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
315 OR ( ( Recinfo.last_updated_by IS NULL )
316 AND ( p_last_updated_by IS NULL )))
317 AND ( ( Recinfo.last_update_date = p_last_update_date)
318 OR ( ( Recinfo.last_update_date IS NULL )
319 AND ( p_last_update_date IS NULL )))
320 AND ( ( Recinfo.last_update_login = p_last_update_login)
321 OR ( ( Recinfo.last_update_login IS NULL )
322 AND ( p_last_update_login IS NULL )))
323 AND ( ( Recinfo.object_version_number = p_object_version_number)
324 OR ( ( Recinfo.object_version_number IS NULL )
325 AND ( p_object_version_number IS NULL )))
326 AND ( ( Recinfo.rule_id = p_rule_id)
327 OR ( ( Recinfo.rule_id IS NULL )
328 AND ( p_rule_id IS NULL )))
329 AND ( ( Recinfo.rulegroup_id = p_rulegroup_id)
330 OR ( ( Recinfo.rulegroup_id IS NULL )
331 AND ( p_rulegroup_id IS NULL )))
332 AND ( ( Recinfo.posting_id = p_posting_id)
333 OR ( ( Recinfo.posting_id IS NULL )
334 AND ( p_posting_id IS NULL )))
335 AND ( ( Recinfo.strategy_id = p_strategy_id)
336 OR ( ( Recinfo.strategy_id IS NULL )
337 AND ( p_strategy_id IS NULL )))
338 AND ( ( Recinfo.bus_priority_code = p_bus_priority_code)
339 OR ( ( Recinfo.bus_priority_code IS NULL )
340 AND ( p_bus_priority_code IS NULL )))
341 AND ( ( Recinfo.bus_priority_disp_order = p_bus_priority_disp_order)
342 OR ( ( Recinfo.bus_priority_disp_order IS NULL )
343 AND ( p_bus_priority_disp_order IS NULL )))
344 AND ( ( Recinfo.clausevalue1 = p_clausevalue1)
345 OR ( ( Recinfo.clausevalue1 IS NULL )
346 AND ( p_clausevalue1 IS NULL )))
347 AND ( ( Recinfo.clausevalue2 = p_clausevalue2)
348 OR ( ( Recinfo.clausevalue2 IS NULL )
349 AND ( p_clausevalue2 IS NULL )))
350 AND ( ( Recinfo.clausevalue3 = p_clausevalue3)
351 OR ( ( Recinfo.clausevalue3 IS NULL )
352 AND ( p_clausevalue3 IS NULL )))
353 AND ( ( Recinfo.clausevalue4 = p_clausevalue4)
354 OR ( ( Recinfo.clausevalue4 IS NULL )
355 AND ( p_clausevalue4 IS NULL )))
356 AND ( ( Recinfo.clausevalue5 = p_clausevalue5)
357 OR ( ( Recinfo.clausevalue5 IS NULL )
358 AND ( p_clausevalue5 IS NULL )))
359 AND ( ( Recinfo.clausevalue6 = p_clausevalue6)
360 OR ( ( Recinfo.clausevalue6 IS NULL )
361 AND ( p_clausevalue6 IS NULL )))
362 AND ( ( Recinfo.clausevalue7 = p_clausevalue7)
363 OR ( ( Recinfo.clausevalue7 IS NULL )
364 AND ( p_clausevalue7 IS NULL )))
365 AND ( ( Recinfo.clausevalue8 = p_clausevalue8)
366 OR ( ( Recinfo.clausevalue8 IS NULL )
367 AND ( p_clausevalue8 IS NULL )))
368 AND ( ( Recinfo.clausevalue9 = p_clausevalue9)
369 OR ( ( Recinfo.clausevalue9 IS NULL )
370 AND ( p_clausevalue9 IS NULL )))
371 AND ( ( Recinfo.clausevalue10 = p_clausevalue10)
375 RETURN;
372 OR ( ( Recinfo.clausevalue10 IS NULL )
373 AND ( p_clausevalue10 IS NULL )))
374 ) THEN
376 ELSE
377 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
378 APP_EXCEPTION.RAISE_EXCEPTION;
379 END IF;
382 END AMS_IBA_PS_RULES_PKG;
380 END Lock_Row;
381