[Home] [Help]
PACKAGE BODY: APPS.CSI_I_ORG_ASSIGNMENTS_H_PKG
Source
1 PACKAGE BODY CSI_I_ORG_ASSIGNMENTS_H_PKG as
2 /* $Header: csitorgb.pls 115.17 2003/09/04 00:21:31 sguthiva ship $ */
3
4
5 g_pkg_name CONSTANT VARCHAR2(30):= 'CSI_I_ORG_ASSIGNMENTS_H_PKG';
6
7 PROCEDURE Insert_Row(
8 px_INSTANCE_OU_HISTORY_ID IN OUT NOCOPY NUMBER,
9 p_INSTANCE_OU_ID NUMBER,
10 p_TRANSACTION_ID NUMBER,
11 p_OLD_OPERATING_UNIT_ID NUMBER,
12 p_NEW_OPERATING_UNIT_ID NUMBER,
13 p_OLD_RELATIONSHIP_TYPE_CODE VARCHAR2,
14 p_NEW_RELATIONSHIP_TYPE_CODE VARCHAR2,
15 p_OLD_ACTIVE_START_DATE DATE,
16 p_NEW_ACTIVE_START_DATE DATE,
17 p_OLD_ACTIVE_END_DATE DATE,
18 p_NEW_ACTIVE_END_DATE DATE,
19 p_OLD_CONTEXT VARCHAR2,
20 p_NEW_CONTEXT VARCHAR2,
21 p_OLD_ATTRIBUTE1 VARCHAR2,
22 p_NEW_ATTRIBUTE1 VARCHAR2,
23 p_OLD_ATTRIBUTE2 VARCHAR2,
24 p_NEW_ATTRIBUTE2 VARCHAR2,
25 p_OLD_ATTRIBUTE3 VARCHAR2,
26 p_NEW_ATTRIBUTE3 VARCHAR2,
27 p_OLD_ATTRIBUTE4 VARCHAR2,
28 p_NEW_ATTRIBUTE4 VARCHAR2,
29 p_OLD_ATTRIBUTE5 VARCHAR2,
30 p_NEW_ATTRIBUTE5 VARCHAR2,
31 p_OLD_ATTRIBUTE6 VARCHAR2,
32 p_NEW_ATTRIBUTE6 VARCHAR2,
33 p_OLD_ATTRIBUTE7 VARCHAR2,
34 p_NEW_ATTRIBUTE7 VARCHAR2,
35 p_OLD_ATTRIBUTE8 VARCHAR2,
36 p_NEW_ATTRIBUTE8 VARCHAR2,
37 p_OLD_ATTRIBUTE9 VARCHAR2,
38 p_NEW_ATTRIBUTE9 VARCHAR2,
39 p_OLD_ATTRIBUTE10 VARCHAR2,
40 p_NEW_ATTRIBUTE10 VARCHAR2,
41 p_OLD_ATTRIBUTE11 VARCHAR2,
42 p_NEW_ATTRIBUTE11 VARCHAR2,
43 p_OLD_ATTRIBUTE12 VARCHAR2,
44 p_NEW_ATTRIBUTE12 VARCHAR2,
45 p_OLD_ATTRIBUTE13 VARCHAR2,
46 p_NEW_ATTRIBUTE13 VARCHAR2,
47 p_OLD_ATTRIBUTE14 VARCHAR2,
48 p_NEW_ATTRIBUTE14 VARCHAR2,
49 p_OLD_ATTRIBUTE15 VARCHAR2,
50 p_NEW_ATTRIBUTE15 VARCHAR2,
51 p_FULL_DUMP_FLAG VARCHAR2,
52 p_CREATED_BY NUMBER,
53 p_CREATION_DATE DATE,
54 p_LAST_UPDATED_BY NUMBER,
55 p_LAST_UPDATE_DATE DATE,
56 p_LAST_UPDATE_LOGIN NUMBER,
57 p_OBJECT_VERSION_NUMBER NUMBER)
58 IS
59 CURSOR C2 IS SELECT CSI_I_ORG_ASSIGNMENTS_H_S.nextval FROM sys.dual;
60 BEGIN
61 If (px_INSTANCE_OU_HISTORY_ID IS NULL) OR (px_INSTANCE_OU_HISTORY_ID = FND_API.G_MISS_NUM) then
62 OPEN C2;
63 FETCH C2 INTO px_INSTANCE_OU_HISTORY_ID;
64 CLOSE C2;
65 End If;
66 INSERT INTO CSI_I_ORG_ASSIGNMENTS_H(
67 INSTANCE_OU_HISTORY_ID,
68 INSTANCE_OU_ID,
69 TRANSACTION_ID,
70 OLD_OPERATING_UNIT_ID,
71 NEW_OPERATING_UNIT_ID,
72 OLD_RELATIONSHIP_TYPE_CODE,
73 NEW_RELATIONSHIP_TYPE_CODE,
74 OLD_ACTIVE_START_DATE,
75 NEW_ACTIVE_START_DATE,
76 OLD_ACTIVE_END_DATE,
77 NEW_ACTIVE_END_DATE,
78 OLD_CONTEXT,
79 NEW_CONTEXT,
80 OLD_ATTRIBUTE1,
81 NEW_ATTRIBUTE1,
82 OLD_ATTRIBUTE2,
83 NEW_ATTRIBUTE2,
84 OLD_ATTRIBUTE3,
85 NEW_ATTRIBUTE3,
86 OLD_ATTRIBUTE4,
87 NEW_ATTRIBUTE4,
88 OLD_ATTRIBUTE5,
89 NEW_ATTRIBUTE5,
90 OLD_ATTRIBUTE6,
91 NEW_ATTRIBUTE6,
92 OLD_ATTRIBUTE7,
93 NEW_ATTRIBUTE7,
94 OLD_ATTRIBUTE8,
95 NEW_ATTRIBUTE8,
96 OLD_ATTRIBUTE9,
97 NEW_ATTRIBUTE9,
98 OLD_ATTRIBUTE10,
99 NEW_ATTRIBUTE10,
100 OLD_ATTRIBUTE11,
101 NEW_ATTRIBUTE11,
102 OLD_ATTRIBUTE12,
103 NEW_ATTRIBUTE12,
104 OLD_ATTRIBUTE13,
105 NEW_ATTRIBUTE13,
106 OLD_ATTRIBUTE14,
107 NEW_ATTRIBUTE14,
108 OLD_ATTRIBUTE15,
109 NEW_ATTRIBUTE15,
110 FULL_DUMP_FLAG,
111 CREATED_BY,
112 CREATION_DATE,
113 LAST_UPDATED_BY,
114 LAST_UPDATE_DATE,
115 LAST_UPDATE_LOGIN,
116 OBJECT_VERSION_NUMBER
117 ) VALUES (
118 px_INSTANCE_OU_HISTORY_ID,
119 decode( p_INSTANCE_OU_ID, FND_API.G_MISS_NUM, NULL, p_INSTANCE_OU_ID),
120 decode( p_TRANSACTION_ID, FND_API.G_MISS_NUM, NULL, p_TRANSACTION_ID),
121 decode( p_OLD_OPERATING_UNIT_ID, FND_API.G_MISS_NUM, NULL, p_OLD_OPERATING_UNIT_ID),
122 decode( p_NEW_OPERATING_UNIT_ID, FND_API.G_MISS_NUM, NULL, p_NEW_OPERATING_UNIT_ID),
123 decode( p_OLD_RELATIONSHIP_TYPE_CODE, FND_API.G_MISS_CHAR, NULL, p_OLD_RELATIONSHIP_TYPE_CODE),
124 decode( p_NEW_RELATIONSHIP_TYPE_CODE, FND_API.G_MISS_CHAR, NULL, p_NEW_RELATIONSHIP_TYPE_CODE),
125 decode( p_OLD_ACTIVE_START_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_OLD_ACTIVE_START_DATE),
126 decode( p_NEW_ACTIVE_START_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_NEW_ACTIVE_START_DATE),
127 decode( p_OLD_ACTIVE_END_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_OLD_ACTIVE_END_DATE),
128 decode( p_NEW_ACTIVE_END_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_NEW_ACTIVE_END_DATE),
129 decode( p_OLD_CONTEXT, FND_API.G_MISS_CHAR, NULL, p_OLD_CONTEXT),
130 decode( p_NEW_CONTEXT, FND_API.G_MISS_CHAR, NULL, p_NEW_CONTEXT),
131 decode( p_OLD_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_OLD_ATTRIBUTE1),
132 decode( p_NEW_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_NEW_ATTRIBUTE1),
133 decode( p_OLD_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_OLD_ATTRIBUTE2),
134 decode( p_NEW_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_NEW_ATTRIBUTE2),
135 decode( p_OLD_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_OLD_ATTRIBUTE3),
136 decode( p_NEW_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_NEW_ATTRIBUTE3),
137 decode( p_OLD_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_OLD_ATTRIBUTE4),
138 decode( p_NEW_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_NEW_ATTRIBUTE4),
139 decode( p_OLD_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_OLD_ATTRIBUTE5),
140 decode( p_NEW_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_NEW_ATTRIBUTE5),
141 decode( p_OLD_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_OLD_ATTRIBUTE6),
142 decode( p_NEW_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_NEW_ATTRIBUTE6),
143 decode( p_OLD_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_OLD_ATTRIBUTE7),
144 decode( p_NEW_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_NEW_ATTRIBUTE7),
145 decode( p_OLD_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_OLD_ATTRIBUTE8),
146 decode( p_NEW_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_NEW_ATTRIBUTE8),
147 decode( p_OLD_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_OLD_ATTRIBUTE9),
148 decode( p_NEW_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_NEW_ATTRIBUTE9),
149 decode( p_OLD_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_OLD_ATTRIBUTE10),
150 decode( p_NEW_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_NEW_ATTRIBUTE10),
151 decode( p_OLD_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_OLD_ATTRIBUTE11),
152 decode( p_NEW_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_NEW_ATTRIBUTE11),
153 decode( p_OLD_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_OLD_ATTRIBUTE12),
154 decode( p_NEW_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_NEW_ATTRIBUTE12),
155 decode( p_OLD_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_OLD_ATTRIBUTE13),
156 decode( p_NEW_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_NEW_ATTRIBUTE13),
157 decode( p_OLD_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_OLD_ATTRIBUTE14),
158 decode( p_NEW_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_NEW_ATTRIBUTE14),
159 decode( p_OLD_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_OLD_ATTRIBUTE15),
160 decode( p_NEW_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_NEW_ATTRIBUTE15),
161 decode( p_FULL_DUMP_FLAG, FND_API.G_MISS_CHAR, NULL, p_FULL_DUMP_FLAG),
162 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
163 decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE),
164 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
165 decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE),
166 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
167 decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, p_OBJECT_VERSION_NUMBER));
168 End Insert_Row;
169
170 PROCEDURE Update_Row(
171 p_INSTANCE_OU_HISTORY_ID NUMBER,
172 p_INSTANCE_OU_ID NUMBER,
173 p_TRANSACTION_ID NUMBER,
174 p_OLD_OPERATING_UNIT_ID NUMBER,
175 p_NEW_OPERATING_UNIT_ID NUMBER,
176 p_OLD_RELATIONSHIP_TYPE_CODE VARCHAR2,
177 p_NEW_RELATIONSHIP_TYPE_CODE VARCHAR2,
178 p_OLD_ACTIVE_START_DATE DATE,
179 p_NEW_ACTIVE_START_DATE DATE,
180 p_OLD_ACTIVE_END_DATE DATE,
181 p_NEW_ACTIVE_END_DATE DATE,
182 p_OLD_CONTEXT VARCHAR2,
183 p_NEW_CONTEXT VARCHAR2,
184 p_OLD_ATTRIBUTE1 VARCHAR2,
185 p_NEW_ATTRIBUTE1 VARCHAR2,
186 p_OLD_ATTRIBUTE2 VARCHAR2,
187 p_NEW_ATTRIBUTE2 VARCHAR2,
188 p_OLD_ATTRIBUTE3 VARCHAR2,
189 p_NEW_ATTRIBUTE3 VARCHAR2,
190 p_OLD_ATTRIBUTE4 VARCHAR2,
191 p_NEW_ATTRIBUTE4 VARCHAR2,
192 p_OLD_ATTRIBUTE5 VARCHAR2,
193 p_NEW_ATTRIBUTE5 VARCHAR2,
194 p_OLD_ATTRIBUTE6 VARCHAR2,
195 p_NEW_ATTRIBUTE6 VARCHAR2,
196 p_OLD_ATTRIBUTE7 VARCHAR2,
197 p_NEW_ATTRIBUTE7 VARCHAR2,
198 p_OLD_ATTRIBUTE8 VARCHAR2,
199 p_NEW_ATTRIBUTE8 VARCHAR2,
200 p_OLD_ATTRIBUTE9 VARCHAR2,
201 p_NEW_ATTRIBUTE9 VARCHAR2,
202 p_OLD_ATTRIBUTE10 VARCHAR2,
203 p_NEW_ATTRIBUTE10 VARCHAR2,
204 p_OLD_ATTRIBUTE11 VARCHAR2,
205 p_NEW_ATTRIBUTE11 VARCHAR2,
206 p_OLD_ATTRIBUTE12 VARCHAR2,
207 p_NEW_ATTRIBUTE12 VARCHAR2,
208 p_OLD_ATTRIBUTE13 VARCHAR2,
209 p_NEW_ATTRIBUTE13 VARCHAR2,
210 p_OLD_ATTRIBUTE14 VARCHAR2,
211 p_NEW_ATTRIBUTE14 VARCHAR2,
212 p_OLD_ATTRIBUTE15 VARCHAR2,
213 p_NEW_ATTRIBUTE15 VARCHAR2,
214 p_FULL_DUMP_FLAG VARCHAR2,
215 p_CREATED_BY NUMBER,
216 p_CREATION_DATE DATE,
217 p_LAST_UPDATED_BY NUMBER,
218 p_LAST_UPDATE_DATE DATE,
219 p_LAST_UPDATE_LOGIN NUMBER,
220 p_OBJECT_VERSION_NUMBER NUMBER)
221
222 IS
223 BEGIN
224 Update CSI_I_ORG_ASSIGNMENTS_H
225 SET
226 INSTANCE_OU_ID = decode( p_INSTANCE_OU_ID, FND_API.G_MISS_NUM, INSTANCE_OU_ID, p_INSTANCE_OU_ID),
227 TRANSACTION_ID = decode( p_TRANSACTION_ID, FND_API.G_MISS_NUM, TRANSACTION_ID, p_TRANSACTION_ID),
228 OLD_OPERATING_UNIT_ID = decode( p_OLD_OPERATING_UNIT_ID, FND_API.G_MISS_NUM, OLD_OPERATING_UNIT_ID, p_OLD_OPERATING_UNIT_ID),
229 NEW_OPERATING_UNIT_ID = decode( p_NEW_OPERATING_UNIT_ID, FND_API.G_MISS_NUM, NEW_OPERATING_UNIT_ID, p_NEW_OPERATING_UNIT_ID),
230 OLD_RELATIONSHIP_TYPE_CODE = decode( p_OLD_RELATIONSHIP_TYPE_CODE, FND_API.G_MISS_CHAR, OLD_RELATIONSHIP_TYPE_CODE, p_OLD_RELATIONSHIP_TYPE_CODE),
231 NEW_RELATIONSHIP_TYPE_CODE = decode( p_NEW_RELATIONSHIP_TYPE_CODE, FND_API.G_MISS_CHAR, NEW_RELATIONSHIP_TYPE_CODE, p_NEW_RELATIONSHIP_TYPE_CODE),
232 OLD_ACTIVE_START_DATE = decode( p_OLD_ACTIVE_START_DATE, FND_API.G_MISS_DATE, OLD_ACTIVE_START_DATE, p_OLD_ACTIVE_START_DATE),
233 NEW_ACTIVE_START_DATE = decode( p_NEW_ACTIVE_START_DATE, FND_API.G_MISS_DATE, NEW_ACTIVE_START_DATE, p_NEW_ACTIVE_START_DATE),
234 OLD_ACTIVE_END_DATE = decode( p_OLD_ACTIVE_END_DATE, FND_API.G_MISS_DATE, OLD_ACTIVE_END_DATE, p_OLD_ACTIVE_END_DATE),
235 NEW_ACTIVE_END_DATE = decode( p_NEW_ACTIVE_END_DATE, FND_API.G_MISS_DATE, NEW_ACTIVE_END_DATE, p_NEW_ACTIVE_END_DATE),
236 OLD_CONTEXT = decode( p_OLD_CONTEXT, FND_API.G_MISS_CHAR, OLD_CONTEXT, p_OLD_CONTEXT),
237 NEW_CONTEXT = decode( p_NEW_CONTEXT, FND_API.G_MISS_CHAR, NEW_CONTEXT, p_NEW_CONTEXT),
238 OLD_ATTRIBUTE1 = decode( p_OLD_ATTRIBUTE1, FND_API.G_MISS_CHAR, OLD_ATTRIBUTE1, p_OLD_ATTRIBUTE1),
239 NEW_ATTRIBUTE1 = decode( p_NEW_ATTRIBUTE1, FND_API.G_MISS_CHAR, NEW_ATTRIBUTE1, p_NEW_ATTRIBUTE1),
240 OLD_ATTRIBUTE2 = decode( p_OLD_ATTRIBUTE2, FND_API.G_MISS_CHAR, OLD_ATTRIBUTE2, p_OLD_ATTRIBUTE2),
241 NEW_ATTRIBUTE2 = decode( p_NEW_ATTRIBUTE2, FND_API.G_MISS_CHAR, NEW_ATTRIBUTE2, p_NEW_ATTRIBUTE2),
242 OLD_ATTRIBUTE3 = decode( p_OLD_ATTRIBUTE3, FND_API.G_MISS_CHAR, OLD_ATTRIBUTE3, p_OLD_ATTRIBUTE3),
243 NEW_ATTRIBUTE3 = decode( p_NEW_ATTRIBUTE3, FND_API.G_MISS_CHAR, NEW_ATTRIBUTE3, p_NEW_ATTRIBUTE3),
244 OLD_ATTRIBUTE4 = decode( p_OLD_ATTRIBUTE4, FND_API.G_MISS_CHAR, OLD_ATTRIBUTE4, p_OLD_ATTRIBUTE4),
245 NEW_ATTRIBUTE4 = decode( p_NEW_ATTRIBUTE4, FND_API.G_MISS_CHAR, NEW_ATTRIBUTE4, p_NEW_ATTRIBUTE4),
246 OLD_ATTRIBUTE5 = decode( p_OLD_ATTRIBUTE5, FND_API.G_MISS_CHAR, OLD_ATTRIBUTE5, p_OLD_ATTRIBUTE5),
247 NEW_ATTRIBUTE5 = decode( p_NEW_ATTRIBUTE5, FND_API.G_MISS_CHAR, NEW_ATTRIBUTE5, p_NEW_ATTRIBUTE5),
248 OLD_ATTRIBUTE6 = decode( p_OLD_ATTRIBUTE6, FND_API.G_MISS_CHAR, OLD_ATTRIBUTE6, p_OLD_ATTRIBUTE6),
249 NEW_ATTRIBUTE6 = decode( p_NEW_ATTRIBUTE6, FND_API.G_MISS_CHAR, NEW_ATTRIBUTE6, p_NEW_ATTRIBUTE6),
250 OLD_ATTRIBUTE7 = decode( p_OLD_ATTRIBUTE7, FND_API.G_MISS_CHAR, OLD_ATTRIBUTE7, p_OLD_ATTRIBUTE7),
251 NEW_ATTRIBUTE7 = decode( p_NEW_ATTRIBUTE7, FND_API.G_MISS_CHAR, NEW_ATTRIBUTE7, p_NEW_ATTRIBUTE7),
252 OLD_ATTRIBUTE8 = decode( p_OLD_ATTRIBUTE8, FND_API.G_MISS_CHAR, OLD_ATTRIBUTE8, p_OLD_ATTRIBUTE8),
253 NEW_ATTRIBUTE8 = decode( p_NEW_ATTRIBUTE8, FND_API.G_MISS_CHAR, NEW_ATTRIBUTE8, p_NEW_ATTRIBUTE8),
254 OLD_ATTRIBUTE9 = decode( p_OLD_ATTRIBUTE9, FND_API.G_MISS_CHAR, OLD_ATTRIBUTE9, p_OLD_ATTRIBUTE9),
255 NEW_ATTRIBUTE9 = decode( p_NEW_ATTRIBUTE9, FND_API.G_MISS_CHAR, NEW_ATTRIBUTE9, p_NEW_ATTRIBUTE9),
256 OLD_ATTRIBUTE10 = decode( p_OLD_ATTRIBUTE10, FND_API.G_MISS_CHAR, OLD_ATTRIBUTE10, p_OLD_ATTRIBUTE10),
257 NEW_ATTRIBUTE10 = decode( p_NEW_ATTRIBUTE10, FND_API.G_MISS_CHAR, NEW_ATTRIBUTE10, p_NEW_ATTRIBUTE10),
258 OLD_ATTRIBUTE11 = decode( p_OLD_ATTRIBUTE11, FND_API.G_MISS_CHAR, OLD_ATTRIBUTE11, p_OLD_ATTRIBUTE11),
259 NEW_ATTRIBUTE11 = decode( p_NEW_ATTRIBUTE11, FND_API.G_MISS_CHAR, NEW_ATTRIBUTE11, p_NEW_ATTRIBUTE11),
260 OLD_ATTRIBUTE12 = decode( p_OLD_ATTRIBUTE12, FND_API.G_MISS_CHAR, OLD_ATTRIBUTE12, p_OLD_ATTRIBUTE12),
261 NEW_ATTRIBUTE12 = decode( p_NEW_ATTRIBUTE12, FND_API.G_MISS_CHAR, NEW_ATTRIBUTE12, p_NEW_ATTRIBUTE12),
262 OLD_ATTRIBUTE13 = decode( p_OLD_ATTRIBUTE13, FND_API.G_MISS_CHAR, OLD_ATTRIBUTE13, p_OLD_ATTRIBUTE13),
263 NEW_ATTRIBUTE13 = decode( p_NEW_ATTRIBUTE13, FND_API.G_MISS_CHAR, NEW_ATTRIBUTE13, p_NEW_ATTRIBUTE13),
264 OLD_ATTRIBUTE14 = decode( p_OLD_ATTRIBUTE14, FND_API.G_MISS_CHAR, OLD_ATTRIBUTE14, p_OLD_ATTRIBUTE14),
268 FULL_DUMP_FLAG = decode( p_FULL_DUMP_FLAG, FND_API.G_MISS_CHAR, FULL_DUMP_FLAG, p_FULL_DUMP_FLAG),
265 NEW_ATTRIBUTE14 = decode( p_NEW_ATTRIBUTE14, FND_API.G_MISS_CHAR, NEW_ATTRIBUTE14, p_NEW_ATTRIBUTE14),
266 OLD_ATTRIBUTE15 = decode( p_OLD_ATTRIBUTE15, FND_API.G_MISS_CHAR, OLD_ATTRIBUTE15, p_OLD_ATTRIBUTE15),
267 NEW_ATTRIBUTE15 = decode( p_NEW_ATTRIBUTE15, FND_API.G_MISS_CHAR, NEW_ATTRIBUTE15, p_NEW_ATTRIBUTE15),
269 CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY),
270 CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE),
271 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
272 LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
273 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
274 -- OBJECT_VERSION_NUMBER = decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, OBJECT_VERSION_NUMBER, p_OBJECT_VERSION_NUMBER)
275 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
276 where INSTANCE_OU_HISTORY_ID = p_INSTANCE_OU_HISTORY_ID;
277
278 If (SQL%NOTFOUND) then
279 RAISE NO_DATA_FOUND;
280 End If;
281 END Update_Row;
282
283 PROCEDURE Delete_Row(
284 p_INSTANCE_OU_HISTORY_ID NUMBER)
285 IS
286 BEGIN
287 DELETE FROM CSI_I_ORG_ASSIGNMENTS_H
288 WHERE INSTANCE_OU_HISTORY_ID = p_INSTANCE_OU_HISTORY_ID;
289 If (SQL%NOTFOUND) then
290 RAISE NO_DATA_FOUND;
291 End If;
292 END Delete_Row;
293
294 PROCEDURE Lock_Row(
295 p_INSTANCE_OU_HISTORY_ID NUMBER,
296 p_INSTANCE_OU_ID NUMBER,
297 p_TRANSACTION_ID NUMBER,
298 p_OLD_OPERATING_UNIT_ID NUMBER,
299 p_NEW_OPERATING_UNIT_ID NUMBER,
300 p_OLD_RELATIONSHIP_TYPE_CODE VARCHAR2,
301 p_NEW_RELATIONSHIP_TYPE_CODE VARCHAR2,
302 p_OLD_ACTIVE_START_DATE DATE,
303 p_NEW_ACTIVE_START_DATE DATE,
304 p_OLD_ACTIVE_END_DATE DATE,
305 p_NEW_ACTIVE_END_DATE DATE,
306 p_OLD_CONTEXT VARCHAR2,
307 p_NEW_CONTEXT VARCHAR2,
308 p_OLD_ATTRIBUTE1 VARCHAR2,
309 p_NEW_ATTRIBUTE1 VARCHAR2,
310 p_OLD_ATTRIBUTE2 VARCHAR2,
311 p_NEW_ATTRIBUTE2 VARCHAR2,
312 p_OLD_ATTRIBUTE3 VARCHAR2,
313 p_NEW_ATTRIBUTE3 VARCHAR2,
314 p_OLD_ATTRIBUTE4 VARCHAR2,
315 p_NEW_ATTRIBUTE4 VARCHAR2,
316 p_OLD_ATTRIBUTE5 VARCHAR2,
317 p_NEW_ATTRIBUTE5 VARCHAR2,
318 p_OLD_ATTRIBUTE6 VARCHAR2,
319 p_NEW_ATTRIBUTE6 VARCHAR2,
320 p_OLD_ATTRIBUTE7 VARCHAR2,
321 p_NEW_ATTRIBUTE7 VARCHAR2,
322 p_OLD_ATTRIBUTE8 VARCHAR2,
323 p_NEW_ATTRIBUTE8 VARCHAR2,
324 p_OLD_ATTRIBUTE9 VARCHAR2,
325 p_NEW_ATTRIBUTE9 VARCHAR2,
326 p_OLD_ATTRIBUTE10 VARCHAR2,
327 p_NEW_ATTRIBUTE10 VARCHAR2,
328 p_OLD_ATTRIBUTE11 VARCHAR2,
329 p_NEW_ATTRIBUTE11 VARCHAR2,
330 p_OLD_ATTRIBUTE12 VARCHAR2,
331 p_NEW_ATTRIBUTE12 VARCHAR2,
332 p_OLD_ATTRIBUTE13 VARCHAR2,
333 p_NEW_ATTRIBUTE13 VARCHAR2,
334 p_OLD_ATTRIBUTE14 VARCHAR2,
335 p_NEW_ATTRIBUTE14 VARCHAR2,
336 p_OLD_ATTRIBUTE15 VARCHAR2,
337 p_NEW_ATTRIBUTE15 VARCHAR2,
338 p_FULL_DUMP_FLAG VARCHAR2,
339 p_CREATED_BY NUMBER,
340 p_CREATION_DATE DATE,
341 p_LAST_UPDATED_BY NUMBER,
342 p_LAST_UPDATE_DATE DATE,
343 p_LAST_UPDATE_LOGIN NUMBER,
344 p_OBJECT_VERSION_NUMBER NUMBER)
345
346 IS
347 CURSOR C IS
348 SELECT *
349 FROM CSI_I_ORG_ASSIGNMENTS_H
350 WHERE INSTANCE_OU_HISTORY_ID = p_INSTANCE_OU_HISTORY_ID
351 FOR UPDATE of INSTANCE_OU_HISTORY_ID NOWAIT;
352 Recinfo C%ROWTYPE;
353 BEGIN
354 OPEN C;
355 FETCH C INTO Recinfo;
356 If (C%NOTFOUND) then
357 CLOSE C;
358 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
359 APP_EXCEPTION.RAISE_EXCEPTION;
360 End If;
361 CLOSE C;
362 if (
363 ( Recinfo.INSTANCE_OU_HISTORY_ID = p_INSTANCE_OU_HISTORY_ID)
364 AND ( ( Recinfo.INSTANCE_OU_ID = p_INSTANCE_OU_ID)
365 OR ( ( Recinfo.INSTANCE_OU_ID IS NULL )
366 AND ( p_INSTANCE_OU_ID IS NULL )))
367 AND ( ( Recinfo.TRANSACTION_ID = p_TRANSACTION_ID)
368 OR ( ( Recinfo.TRANSACTION_ID IS NULL )
369 AND ( p_TRANSACTION_ID IS NULL )))
370 AND ( ( Recinfo.OLD_OPERATING_UNIT_ID = p_OLD_OPERATING_UNIT_ID)
371 OR ( ( Recinfo.OLD_OPERATING_UNIT_ID IS NULL )
372 AND ( p_OLD_OPERATING_UNIT_ID IS NULL )))
373 AND ( ( Recinfo.NEW_OPERATING_UNIT_ID = p_NEW_OPERATING_UNIT_ID)
374 OR ( ( Recinfo.NEW_OPERATING_UNIT_ID IS NULL )
375 AND ( p_NEW_OPERATING_UNIT_ID IS NULL )))
376 AND ( ( Recinfo.OLD_RELATIONSHIP_TYPE_CODE = p_OLD_RELATIONSHIP_TYPE_CODE)
377 OR ( ( Recinfo.OLD_RELATIONSHIP_TYPE_CODE IS NULL )
378 AND ( p_OLD_RELATIONSHIP_TYPE_CODE IS NULL )))
382 AND ( ( Recinfo.OLD_ACTIVE_START_DATE = p_OLD_ACTIVE_START_DATE)
379 AND ( ( Recinfo.NEW_RELATIONSHIP_TYPE_CODE = p_NEW_RELATIONSHIP_TYPE_CODE)
380 OR ( ( Recinfo.NEW_RELATIONSHIP_TYPE_CODE IS NULL )
381 AND ( p_NEW_RELATIONSHIP_TYPE_CODE IS NULL )))
383 OR ( ( Recinfo.OLD_ACTIVE_START_DATE IS NULL )
384 AND ( p_OLD_ACTIVE_START_DATE IS NULL )))
385 AND ( ( Recinfo.NEW_ACTIVE_START_DATE = p_NEW_ACTIVE_START_DATE)
386 OR ( ( Recinfo.NEW_ACTIVE_START_DATE IS NULL )
387 AND ( p_NEW_ACTIVE_START_DATE IS NULL )))
388 AND ( ( Recinfo.OLD_ACTIVE_END_DATE = p_OLD_ACTIVE_END_DATE)
389 OR ( ( Recinfo.OLD_ACTIVE_END_DATE IS NULL )
390 AND ( p_OLD_ACTIVE_END_DATE IS NULL )))
391 AND ( ( Recinfo.NEW_ACTIVE_END_DATE = p_NEW_ACTIVE_END_DATE)
392 OR ( ( Recinfo.NEW_ACTIVE_END_DATE IS NULL )
393 AND ( p_NEW_ACTIVE_END_DATE IS NULL )))
394
395 AND ( ( Recinfo.OLD_CONTEXT = p_OLD_CONTEXT)
396 OR ( ( Recinfo.OLD_CONTEXT IS NULL )
397 AND ( p_OLD_CONTEXT IS NULL )))
398 AND ( ( Recinfo.NEW_CONTEXT = p_NEW_CONTEXT)
399 OR ( ( Recinfo.NEW_CONTEXT IS NULL )
400 AND ( p_NEW_CONTEXT IS NULL )))
401
402 AND ( ( Recinfo.OLD_ATTRIBUTE1 = p_OLD_ATTRIBUTE1)
403 OR ( ( Recinfo.OLD_ATTRIBUTE1 IS NULL )
404 AND ( p_OLD_ATTRIBUTE1 IS NULL )))
405 AND ( ( Recinfo.NEW_ATTRIBUTE1 = p_NEW_ATTRIBUTE1)
406 OR ( ( Recinfo.NEW_ATTRIBUTE1 IS NULL )
407 AND ( p_NEW_ATTRIBUTE1 IS NULL )))
408
409 AND ( ( Recinfo.OLD_ATTRIBUTE2 = p_OLD_ATTRIBUTE2)
410 OR ( ( Recinfo.OLD_ATTRIBUTE2 IS NULL )
411 AND ( p_OLD_ATTRIBUTE2 IS NULL )))
412 AND ( ( Recinfo.NEW_ATTRIBUTE2 = p_NEW_ATTRIBUTE2)
413 OR ( ( Recinfo.NEW_ATTRIBUTE2 IS NULL )
414 AND ( p_NEW_ATTRIBUTE2 IS NULL )))
415
416 AND ( ( Recinfo.OLD_ATTRIBUTE3 = p_OLD_ATTRIBUTE3)
417 OR ( ( Recinfo.OLD_ATTRIBUTE3 IS NULL )
418 AND ( p_OLD_ATTRIBUTE3 IS NULL )))
419 AND ( ( Recinfo.NEW_ATTRIBUTE3 = p_NEW_ATTRIBUTE3)
420 OR ( ( Recinfo.NEW_ATTRIBUTE3 IS NULL )
421 AND ( p_NEW_ATTRIBUTE3 IS NULL )))
422
423 AND ( ( Recinfo.OLD_ATTRIBUTE4 = p_OLD_ATTRIBUTE4)
424 OR ( ( Recinfo.OLD_ATTRIBUTE4 IS NULL )
425 AND ( p_OLD_ATTRIBUTE4 IS NULL )))
426 AND ( ( Recinfo.NEW_ATTRIBUTE4 = p_NEW_ATTRIBUTE4)
427 OR ( ( Recinfo.NEW_ATTRIBUTE4 IS NULL )
428 AND ( p_NEW_ATTRIBUTE4 IS NULL )))
429
430 AND ( ( Recinfo.OLD_ATTRIBUTE5 = p_OLD_ATTRIBUTE5)
431 OR ( ( Recinfo.OLD_ATTRIBUTE5 IS NULL )
432 AND ( p_OLD_ATTRIBUTE5 IS NULL )))
433 AND ( ( Recinfo.NEW_ATTRIBUTE5 = p_NEW_ATTRIBUTE5)
434 OR ( ( Recinfo.NEW_ATTRIBUTE5 IS NULL )
435 AND ( p_NEW_ATTRIBUTE5 IS NULL )))
436
437 AND ( ( Recinfo.OLD_ATTRIBUTE6 = p_OLD_ATTRIBUTE6)
438 OR ( ( Recinfo.OLD_ATTRIBUTE6 IS NULL )
439 AND ( p_OLD_ATTRIBUTE6 IS NULL )))
440 AND ( ( Recinfo.NEW_ATTRIBUTE6 = p_NEW_ATTRIBUTE6)
441 OR ( ( Recinfo.NEW_ATTRIBUTE6 IS NULL )
442 AND ( p_NEW_ATTRIBUTE6 IS NULL )))
443
444 AND ( ( Recinfo.OLD_ATTRIBUTE7 = p_OLD_ATTRIBUTE7)
445 OR ( ( Recinfo.OLD_ATTRIBUTE7 IS NULL )
446 AND ( p_OLD_ATTRIBUTE7 IS NULL )))
447 AND ( ( Recinfo.NEW_ATTRIBUTE7 = p_NEW_ATTRIBUTE7)
448 OR ( ( Recinfo.NEW_ATTRIBUTE7 IS NULL )
449 AND ( p_NEW_ATTRIBUTE7 IS NULL )))
450
451 AND ( ( Recinfo.OLD_ATTRIBUTE8 = p_OLD_ATTRIBUTE8)
452 OR ( ( Recinfo.OLD_ATTRIBUTE8 IS NULL )
453 AND ( p_OLD_ATTRIBUTE8 IS NULL )))
454 AND ( ( Recinfo.NEW_ATTRIBUTE8 = p_NEW_ATTRIBUTE8)
455 OR ( ( Recinfo.NEW_ATTRIBUTE8 IS NULL )
456 AND ( p_NEW_ATTRIBUTE8 IS NULL )))
457
458 AND ( ( Recinfo.OLD_ATTRIBUTE9 = p_OLD_ATTRIBUTE9)
459 OR ( ( Recinfo.OLD_ATTRIBUTE9 IS NULL )
460 AND ( p_OLD_ATTRIBUTE9 IS NULL )))
461 AND ( ( Recinfo.NEW_ATTRIBUTE9 = p_NEW_ATTRIBUTE9)
462 OR ( ( Recinfo.NEW_ATTRIBUTE9 IS NULL )
463 AND ( p_NEW_ATTRIBUTE9 IS NULL )))
464
465 AND ( ( Recinfo.OLD_ATTRIBUTE10 = p_OLD_ATTRIBUTE10)
466 OR ( ( Recinfo.OLD_ATTRIBUTE10 IS NULL )
467 AND ( p_OLD_ATTRIBUTE10 IS NULL )))
468 AND ( ( Recinfo.NEW_ATTRIBUTE10 = p_NEW_ATTRIBUTE10)
469 OR ( ( Recinfo.NEW_ATTRIBUTE10 IS NULL )
470 AND ( p_NEW_ATTRIBUTE10 IS NULL )))
471
472 AND ( ( Recinfo.OLD_ATTRIBUTE11 = p_OLD_ATTRIBUTE11)
473 OR ( ( Recinfo.OLD_ATTRIBUTE11 IS NULL )
474 AND ( p_OLD_ATTRIBUTE11 IS NULL )))
475 AND ( ( Recinfo.NEW_ATTRIBUTE11 = p_NEW_ATTRIBUTE11)
476 OR ( ( Recinfo.NEW_ATTRIBUTE12 IS NULL )
477 AND ( p_NEW_ATTRIBUTE12 IS NULL )))
478
479 AND ( ( Recinfo.OLD_ATTRIBUTE12 = p_OLD_ATTRIBUTE12)
480 OR ( ( Recinfo.OLD_ATTRIBUTE12 IS NULL )
481 AND ( p_OLD_ATTRIBUTE12 IS NULL )))
482 AND ( ( Recinfo.NEW_ATTRIBUTE12 = p_NEW_ATTRIBUTE12)
483 OR ( ( Recinfo.NEW_ATTRIBUTE12 IS NULL )
484 AND ( p_NEW_ATTRIBUTE12 IS NULL )))
485
486 AND ( ( Recinfo.OLD_ATTRIBUTE13 = p_OLD_ATTRIBUTE13)
487 OR ( ( Recinfo.OLD_ATTRIBUTE13 IS NULL )
488 AND ( p_OLD_ATTRIBUTE13 IS NULL )))
489 AND ( ( Recinfo.NEW_ATTRIBUTE13 = p_NEW_ATTRIBUTE13)
490 OR ( ( Recinfo.NEW_ATTRIBUTE13 IS NULL )
491 AND ( p_NEW_ATTRIBUTE13 IS NULL )))
492
493 AND ( ( Recinfo.OLD_ATTRIBUTE14 = p_OLD_ATTRIBUTE14)
494 OR ( ( Recinfo.OLD_ATTRIBUTE14 IS NULL )
495 AND ( p_OLD_ATTRIBUTE14 IS NULL )))
496 AND ( ( Recinfo.NEW_ATTRIBUTE14 = p_NEW_ATTRIBUTE14)
497 OR ( ( Recinfo.NEW_ATTRIBUTE14 IS NULL )
498 AND ( p_NEW_ATTRIBUTE14 IS NULL )))
499
500 AND ( ( Recinfo.OLD_ATTRIBUTE15 = p_OLD_ATTRIBUTE15)
501 OR ( ( Recinfo.OLD_ATTRIBUTE15 IS NULL )
502 AND ( p_OLD_ATTRIBUTE15 IS NULL )))
503 AND ( ( Recinfo.NEW_ATTRIBUTE15 = p_NEW_ATTRIBUTE15)
504 OR ( ( Recinfo.NEW_ATTRIBUTE15 IS NULL )
505 AND ( p_NEW_ATTRIBUTE15 IS NULL )))
506
507 AND ( ( Recinfo.FULL_DUMP_FLAG = p_FULL_DUMP_FLAG)
508 OR ( ( Recinfo.FULL_DUMP_FLAG IS NULL )
509 AND ( p_FULL_DUMP_FLAG IS NULL )))
510
511 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
512 OR ( ( Recinfo.CREATED_BY IS NULL )
513 AND ( p_CREATED_BY IS NULL )))
514 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
515 OR ( ( Recinfo.CREATION_DATE IS NULL )
516 AND ( p_CREATION_DATE IS NULL )))
517 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
518 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
519 AND ( p_LAST_UPDATED_BY IS NULL )))
520 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
521 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
522 AND ( p_LAST_UPDATE_DATE IS NULL )))
523 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
524 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
525 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
526 AND ( ( Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
527 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
528 AND ( p_OBJECT_VERSION_NUMBER IS NULL )))
529 ) then
530 return;
531 else
532 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
533 APP_EXCEPTION.RAISE_EXCEPTION;
534 End If;
535 END Lock_Row;
536
537 End CSI_I_ORG_ASSIGNMENTS_H_PKG;