[Home] [Help]
PACKAGE BODY: APPS.AMS_ACT_LISTS_PKG
Source
1 PACKAGE BODY AMS_ACT_LISTS_PKG as
2 /* $Header: amstalsb.pls 115.8 2003/05/08 20:55:55 jieli ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_ACT_LISTS_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_ACT_LISTS_PKG';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amstalsb.pls';
19
20
21 ----------------------------------------------------------
22 -- NAME
23 -- createInsertBody
24 -- PURPOSE
25 -- NOTES
26 -- HISTORY
27 ----------------------------------------------------------
28 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
29 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
30 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
31
32 PROCEDURE Insert_Row(
33 px_act_list_header_id IN OUT NOCOPY NUMBER,
34 p_last_update_date DATE,
35 p_last_updated_by NUMBER,
36 p_creation_date DATE,
37 p_created_by NUMBER,
38 px_object_version_number IN OUT NOCOPY NUMBER,
39 p_last_update_login NUMBER,
40 p_list_header_id NUMBER,
41 p_group_code varchar2,
42 p_list_used_by_id NUMBER,
43 p_list_used_by VARCHAR2,
44 p_list_act_type VARCHAR2,
45 p_list_action_type VARCHAR2,
46 p_order_number NUMBER
47 )
48
49 IS
50 x_rowid VARCHAR2(30);
51 BEGIN
52 px_object_version_number := 1;
53 INSERT INTO AMS_ACT_LISTS(
54 act_list_header_id,
55 last_update_date,
56 last_updated_by,
57 creation_date,
58 created_by,
59 object_version_number,
60 last_update_login,
61 list_header_id,
62 group_code,
63 list_used_by_id,
64 list_used_by,
65 list_act_type,
66 list_action_type,
67 order_number
68 )
69 VALUES (
70 DECODE(px_act_list_header_id,FND_API.g_miss_num,NULL,px_act_list_header_id),
71 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
72 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
73 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
74 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
75 DECODE( px_object_version_number,FND_API.g_miss_num,NULL,
76 px_object_version_number),
77 DECODE( p_last_update_login,FND_API.g_miss_num,NULL, p_last_update_login),
78 DECODE( p_list_header_id, FND_API.g_miss_num, NULL, p_list_header_id),
79 DECODE( p_group_code, FND_API.g_miss_char, NULL, p_group_code),
80 DECODE( p_list_used_by_id, FND_API.g_miss_num, NULL, p_list_used_by_id),
81 DECODE( p_list_used_by, FND_API.g_miss_char, NULL, p_list_used_by),
82 DECODE( p_list_act_type, FND_API.g_miss_char, NULL, p_list_act_type),
83 DECODE( p_list_action_type, FND_API.g_miss_char, NULL, p_list_action_type),
84 DECODE( p_order_number, FND_API.g_miss_num, NULL, p_order_number));
85 END Insert_Row;
86
87
88 -- ========================================================
89 -- NAME
90 -- createUpdateBody
91 -- PURPOSE
92 -- NOTES
93 -- HISTORY
94 -- ========================================================
95 PROCEDURE Update_Row(
96 p_act_list_header_id NUMBER,
97 p_last_update_date DATE,
98 p_last_updated_by NUMBER,
99 p_creation_date DATE,
100 p_created_by NUMBER,
101 p_object_version_number NUMBER,
102 p_last_update_login NUMBER,
103 p_list_header_id NUMBER,
104 p_group_code VArchar2,
105 p_list_used_by_id NUMBER,
106 p_list_used_by VARCHAR2,
107 p_list_act_type VARCHAR2,
108 p_list_action_type VARCHAR2,
109 p_order_number NUMBER
110 )
111 IS
112 BEGIN
113 Update AMS_ACT_LISTS
114 SET act_list_header_id = DECODE( p_act_list_header_id,
115 FND_API.g_miss_num,
116 act_list_header_id,
117 p_act_list_header_id),
118 last_update_date = DECODE( p_last_update_date,
119 FND_API.g_miss_date,
120 last_update_date,
121 p_last_update_date),
122 last_updated_by = DECODE( p_last_updated_by,
123 FND_API.g_miss_num,
124 last_updated_by,
125 p_last_updated_by),
126 creation_date = DECODE( p_creation_date,
127 FND_API.g_miss_date,
128 creation_date,
129 p_creation_date),
130 created_by = DECODE( p_created_by,
131 FND_API.g_miss_num,
132 created_by,
133 p_created_by),
134 object_version_number = DECODE( p_object_version_number,
135 FND_API.g_miss_num,
136 object_version_number,
137 p_object_version_number),
138 last_update_login = DECODE(
139 p_last_update_login,
140 FND_API.g_miss_num,
141 last_update_login,
142 p_last_update_login),
143 list_header_id = DECODE( p_list_header_id,
144 FND_API.g_miss_num,
145 list_header_id,
146 p_list_header_id),
147 group_code = DECODE( p_group_code ,
148 FND_API.g_miss_char,
149 group_code ,
150 p_group_code ),
151 list_used_by_id = DECODE( p_list_used_by_id,
152 FND_API.g_miss_num,
153 list_used_by_id,
157 list_used_by,
154 p_list_used_by_id),
155 list_used_by = DECODE( p_list_used_by,
156 FND_API.g_miss_char,
158 p_list_used_by),
159 list_act_type = DECODE( p_list_act_type ,
160 FND_API.g_miss_char,
161 list_act_type ,
162 p_list_act_type ),
163 list_action_type = DECODE( p_list_action_type ,
164 FND_API.g_miss_char,
165 list_action_type ,
166 p_list_action_type ),
167 order_number = DECODE( p_order_number,
168 FND_API.g_miss_num,
169 order_number,
170 p_order_number)
171 WHERE ACT_LIST_HEADER_ID = p_ACT_LIST_HEADER_ID
172 AND object_version_number = p_object_version_number;
173
174 IF (SQL%NOTFOUND) THEN
175 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
176 END IF;
177 END Update_Row;
178
179 -- ========================================================
180 -- NAME
181 -- createDeleteBody
182 -- PURPOSE
183 -- NOTES
184 -- HISTORY
185 -- ========================================================
186 PROCEDURE Delete_Row(
187 p_ACT_LIST_HEADER_ID NUMBER)
188 IS
189 BEGIN
190 DELETE FROM AMS_ACT_LISTS
191 WHERE ACT_LIST_HEADER_ID = p_ACT_LIST_HEADER_ID;
192 If (SQL%NOTFOUND) then
193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
194 End If;
195 END Delete_Row ;
196
197
198 -- NAME
199 -- createLockBody
200 --
201 -- PURPOSE
202 --
203 -- NOTES
204 --
205 -- HISTORY
206 --
207 -- ========================================================
208 PROCEDURE Lock_Row(
209 p_act_list_header_id NUMBER,
210 p_last_update_date DATE,
211 p_last_updated_by NUMBER,
212 p_creation_date DATE,
213 p_created_by NUMBER,
214 p_object_version_number NUMBER,
215 p_last_update_login NUMBER,
216 p_list_header_id NUMBER,
217 p_group_code VARCHAR2,
218 p_list_used_by_id NUMBER,
219 p_list_used_by VARCHAR2,
220 p_list_act_type VARCHAR2,
221 p_list_action_type VARCHAR2,
222 p_order_number NUMBER
223 )
224
225 IS
226 CURSOR C IS
227 SELECT *
228 FROM AMS_ACT_LISTS
229 WHERE ACT_LIST_HEADER_ID = p_ACT_LIST_HEADER_ID
230 FOR UPDATE of ACT_LIST_HEADER_ID NOWAIT;
231 Recinfo C%ROWTYPE;
232 BEGIN
233 OPEN c;
234 FETCH c INTO Recinfo;
235 If (c%NOTFOUND) then
236 CLOSE c;
237 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
238 APP_EXCEPTION.RAISE_EXCEPTION;
239 END IF;
240 CLOSE C;
241 IF (
242 ( Recinfo.act_list_header_id = p_act_list_header_id)
243 AND ( ( Recinfo.last_update_date = p_last_update_date)
244 OR ( ( Recinfo.last_update_date IS NULL )
245 AND ( p_last_update_date IS NULL )))
246 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
247 OR ( ( Recinfo.last_updated_by IS NULL )
248 AND ( p_last_updated_by IS NULL )))
249 AND ( ( Recinfo.creation_date = p_creation_date)
253 OR ( ( Recinfo.created_by IS NULL )
250 OR ( ( Recinfo.creation_date IS NULL )
251 AND ( p_creation_date IS NULL )))
252 AND ( ( Recinfo.created_by = p_created_by)
254 AND ( p_created_by IS NULL )))
255 AND ( ( Recinfo.object_version_number = p_object_version_number)
256 OR ( ( Recinfo.object_version_number IS NULL )
257 AND ( p_object_version_number IS NULL )))
258 AND ( ( Recinfo.last_update_login = p_last_update_login)
259 OR ( ( Recinfo.last_update_login IS NULL )
260 AND ( p_last_update_login IS NULL )))
261 AND ( ( Recinfo.list_header_id = p_list_header_id)
262 OR ( ( Recinfo.list_header_id IS NULL )
263 AND ( p_list_header_id IS NULL )))
264 AND ( ( Recinfo.group_code = p_group_code)
265 OR ( ( Recinfo.group_code IS NULL )
266 AND ( p_group_code IS NULL )))
267 AND ( ( Recinfo.list_used_by_id = p_list_used_by_id)
268 OR ( ( Recinfo.list_used_by_id IS NULL )
269 AND ( p_list_used_by_id IS NULL )))
270 AND ( ( Recinfo.list_used_by = p_list_used_by)
271 OR ( ( Recinfo.list_used_by IS NULL )
272 AND ( p_list_used_by IS NULL )))
273 AND ( ( Recinfo.list_act_type = p_list_act_type)
274 OR ( ( Recinfo.list_act_type IS NULL )
275 AND ( p_list_act_type IS NULL )))
276 AND ( ( Recinfo.list_action_type = p_list_action_type)
277 OR ( ( Recinfo.list_action_type IS NULL )
278 AND ( p_list_action_type IS NULL )))
279 AND ( ( Recinfo.order_number = p_order_number)
280 OR ( ( Recinfo.order_number IS NULL )
281 AND ( p_order_number IS NULL )))
282 ) THEN
283 RETURN;
284 ELSE
285 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
286 APP_EXCEPTION.RAISE_EXCEPTION;
287 END IF;
288 END Lock_Row;
289
290 END AMS_ACT_LISTS_PKG;