[Home] [Help]
PACKAGE BODY: APPS.CSI_I_ORG_ASSIGNMENTS_PKG
Source
1 PACKAGE BODY CSI_I_ORG_ASSIGNMENTS_PKG as
2 /* $Header: csitioab.pls 115.12 2003/09/04 00:19:57 sguthiva ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30):= 'CSI_I_ORG_ASSIGNMENTS_PKG';
5
6 PROCEDURE Insert_Row(
7 px_INSTANCE_OU_ID IN OUT NOCOPY NUMBER,
8 p_INSTANCE_ID NUMBER,
9 p_OPERATING_UNIT_ID NUMBER,
10 p_RELATIONSHIP_TYPE_CODE VARCHAR2,
11 p_ACTIVE_START_DATE DATE,
12 p_ACTIVE_END_DATE DATE,
13 p_CONTEXT VARCHAR2,
14 p_ATTRIBUTE1 VARCHAR2,
15 p_ATTRIBUTE2 VARCHAR2,
16 p_ATTRIBUTE3 VARCHAR2,
17 p_ATTRIBUTE4 VARCHAR2,
18 p_ATTRIBUTE5 VARCHAR2,
19 p_ATTRIBUTE6 VARCHAR2,
20 p_ATTRIBUTE7 VARCHAR2,
21 p_ATTRIBUTE8 VARCHAR2,
22 p_ATTRIBUTE9 VARCHAR2,
23 p_ATTRIBUTE10 VARCHAR2,
24 p_ATTRIBUTE11 VARCHAR2,
25 p_ATTRIBUTE12 VARCHAR2,
26 p_ATTRIBUTE13 VARCHAR2,
27 p_ATTRIBUTE14 VARCHAR2,
28 p_ATTRIBUTE15 VARCHAR2,
29 p_CREATED_BY NUMBER,
30 p_CREATION_DATE DATE,
31 p_LAST_UPDATED_BY NUMBER,
32 p_LAST_UPDATE_DATE DATE,
33 p_LAST_UPDATE_LOGIN NUMBER,
34 p_OBJECT_VERSION_NUMBER NUMBER)
35
36 IS
37 CURSOR C2 IS SELECT CSI_I_ORG_ASSIGNMENTS_S.nextval FROM sys.dual;
38 BEGIN
39 If (px_INSTANCE_OU_ID IS NULL) OR (px_INSTANCE_OU_ID = FND_API.G_MISS_NUM) then
40 OPEN C2;
41 FETCH C2 INTO px_INSTANCE_OU_ID;
42 CLOSE C2;
43 End If;
44 INSERT INTO CSI_I_ORG_ASSIGNMENTS(
45 INSTANCE_OU_ID,
46 INSTANCE_ID,
47 OPERATING_UNIT_ID,
48 RELATIONSHIP_TYPE_CODE,
49 ACTIVE_START_DATE,
50 ACTIVE_END_DATE,
51 CONTEXT,
52 ATTRIBUTE1,
53 ATTRIBUTE2,
54 ATTRIBUTE3,
55 ATTRIBUTE4,
56 ATTRIBUTE5,
57 ATTRIBUTE6,
58 ATTRIBUTE7,
59 ATTRIBUTE8,
60 ATTRIBUTE9,
61 ATTRIBUTE10,
62 ATTRIBUTE11,
63 ATTRIBUTE12,
64 ATTRIBUTE13,
65 ATTRIBUTE14,
66 ATTRIBUTE15,
67 CREATED_BY,
68 CREATION_DATE,
69 LAST_UPDATED_BY,
70 LAST_UPDATE_DATE,
71 LAST_UPDATE_LOGIN,
72 OBJECT_VERSION_NUMBER
73 ) VALUES (
74 px_INSTANCE_OU_ID,
75 decode( p_INSTANCE_ID, FND_API.G_MISS_NUM, NULL, p_INSTANCE_ID),
76 decode( p_OPERATING_UNIT_ID, FND_API.G_MISS_NUM, NULL, p_OPERATING_UNIT_ID),
77 decode( p_RELATIONSHIP_TYPE_CODE, FND_API.G_MISS_CHAR, NULL, p_RELATIONSHIP_TYPE_CODE),
78 decode( p_ACTIVE_START_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_ACTIVE_START_DATE),
79 decode( p_ACTIVE_END_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_ACTIVE_END_DATE),
80 decode( p_CONTEXT, FND_API.G_MISS_CHAR, NULL, p_CONTEXT),
81 decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1),
82 decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2),
83 decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3),
84 decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4),
85 decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5),
86 decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6),
87 decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7),
88 decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8),
89 decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9),
90 decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10),
91 decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11),
92 decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12),
93 decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13),
94 decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14),
95 decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15),
96 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
97 decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE),
98 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
99 decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE),
100 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
101 decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, p_OBJECT_VERSION_NUMBER));
102 End Insert_Row;
103
104 PROCEDURE Update_Row(
105 p_INSTANCE_OU_ID NUMBER,
106 p_INSTANCE_ID NUMBER,
107 p_OPERATING_UNIT_ID NUMBER,
108 p_RELATIONSHIP_TYPE_CODE VARCHAR2,
109 p_ACTIVE_START_DATE DATE,
110 p_ACTIVE_END_DATE DATE,
111 p_CONTEXT VARCHAR2,
112 p_ATTRIBUTE1 VARCHAR2,
113 p_ATTRIBUTE2 VARCHAR2,
114 p_ATTRIBUTE3 VARCHAR2,
115 p_ATTRIBUTE4 VARCHAR2,
116 p_ATTRIBUTE5 VARCHAR2,
117 p_ATTRIBUTE6 VARCHAR2,
118 p_ATTRIBUTE7 VARCHAR2,
119 p_ATTRIBUTE8 VARCHAR2,
120 p_ATTRIBUTE9 VARCHAR2,
121 p_ATTRIBUTE10 VARCHAR2,
122 p_ATTRIBUTE11 VARCHAR2,
123 p_ATTRIBUTE12 VARCHAR2,
124 p_ATTRIBUTE13 VARCHAR2,
125 p_ATTRIBUTE14 VARCHAR2,
126 p_ATTRIBUTE15 VARCHAR2,
127 p_CREATED_BY NUMBER,
128 p_CREATION_DATE DATE,
129 p_LAST_UPDATED_BY NUMBER,
130 p_LAST_UPDATE_DATE DATE,
131 p_LAST_UPDATE_LOGIN NUMBER,
132 p_OBJECT_VERSION_NUMBER NUMBER)
133
134 IS
135 BEGIN
136 Update CSI_I_ORG_ASSIGNMENTS
137 SET
138 INSTANCE_ID = decode( p_INSTANCE_ID, FND_API.G_MISS_NUM, INSTANCE_ID, p_INSTANCE_ID),
139 OPERATING_UNIT_ID = decode( p_OPERATING_UNIT_ID, FND_API.G_MISS_NUM, OPERATING_UNIT_ID, p_OPERATING_UNIT_ID),
140 RELATIONSHIP_TYPE_CODE = decode( p_RELATIONSHIP_TYPE_CODE, FND_API.G_MISS_CHAR, RELATIONSHIP_TYPE_CODE, p_RELATIONSHIP_TYPE_CODE),
141 ACTIVE_START_DATE = decode( p_ACTIVE_START_DATE, FND_API.G_MISS_DATE, ACTIVE_START_DATE, p_ACTIVE_START_DATE),
142 ACTIVE_END_DATE = decode( p_ACTIVE_END_DATE, FND_API.G_MISS_DATE, ACTIVE_END_DATE, p_ACTIVE_END_DATE),
143 CONTEXT = decode( p_CONTEXT, FND_API.G_MISS_CHAR, CONTEXT, p_CONTEXT),
144 ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, ATTRIBUTE1, p_ATTRIBUTE1),
145 ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, ATTRIBUTE2, p_ATTRIBUTE2),
146 ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, ATTRIBUTE3, p_ATTRIBUTE3),
147 ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, ATTRIBUTE4, p_ATTRIBUTE4),
148 ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, ATTRIBUTE5, p_ATTRIBUTE5),
149 ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, ATTRIBUTE6, p_ATTRIBUTE6),
150 ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, ATTRIBUTE7, p_ATTRIBUTE7),
151 ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, ATTRIBUTE8, p_ATTRIBUTE8),
152 ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, ATTRIBUTE9, p_ATTRIBUTE9),
153 ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, ATTRIBUTE10, p_ATTRIBUTE10),
154 ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, ATTRIBUTE11, p_ATTRIBUTE11),
155 ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, ATTRIBUTE12, p_ATTRIBUTE12),
156 ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, ATTRIBUTE13, p_ATTRIBUTE13),
157 ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, ATTRIBUTE14, p_ATTRIBUTE14),
158 ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, ATTRIBUTE15, p_ATTRIBUTE15),
159 CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY),
160 CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE),
161 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
162 LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
163 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
164 OBJECT_VERSION_NUMBER = decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, OBJECT_VERSION_NUMBER, p_OBJECT_VERSION_NUMBER)
165 where INSTANCE_OU_ID = p_INSTANCE_OU_ID;
166
167 If (SQL%NOTFOUND) then
168 RAISE NO_DATA_FOUND;
169 End If;
170 END Update_Row;
171
172 PROCEDURE Delete_Row(
173 p_INSTANCE_OU_ID NUMBER)
174 IS
175 BEGIN
176 DELETE FROM CSI_I_ORG_ASSIGNMENTS
177 WHERE INSTANCE_OU_ID = p_INSTANCE_OU_ID;
178 If (SQL%NOTFOUND) then
179 RAISE NO_DATA_FOUND;
180 End If;
181 END Delete_Row;
182
183 PROCEDURE Lock_Row(
184 p_INSTANCE_OU_ID NUMBER,
185 p_INSTANCE_ID NUMBER,
186 p_OPERATING_UNIT_ID NUMBER,
187 p_RELATIONSHIP_TYPE_CODE VARCHAR2,
188 p_ACTIVE_START_DATE DATE,
189 p_ACTIVE_END_DATE DATE,
190 p_CONTEXT VARCHAR2,
191 p_ATTRIBUTE1 VARCHAR2,
192 p_ATTRIBUTE2 VARCHAR2,
193 p_ATTRIBUTE3 VARCHAR2,
194 p_ATTRIBUTE4 VARCHAR2,
195 p_ATTRIBUTE5 VARCHAR2,
196 p_ATTRIBUTE6 VARCHAR2,
197 p_ATTRIBUTE7 VARCHAR2,
198 p_ATTRIBUTE8 VARCHAR2,
199 p_ATTRIBUTE9 VARCHAR2,
200 p_ATTRIBUTE10 VARCHAR2,
201 p_ATTRIBUTE11 VARCHAR2,
202 p_ATTRIBUTE12 VARCHAR2,
203 p_ATTRIBUTE13 VARCHAR2,
204 p_ATTRIBUTE14 VARCHAR2,
205 p_ATTRIBUTE15 VARCHAR2,
206 p_CREATED_BY NUMBER,
207 p_CREATION_DATE DATE,
208 p_LAST_UPDATED_BY NUMBER,
209 p_LAST_UPDATE_DATE DATE,
210 p_LAST_UPDATE_LOGIN NUMBER,
211 p_OBJECT_VERSION_NUMBER NUMBER)
212
213 IS
214 CURSOR C IS
215 SELECT *
216 FROM CSI_I_ORG_ASSIGNMENTS
217 WHERE INSTANCE_OU_ID = p_INSTANCE_OU_ID
218 FOR UPDATE of INSTANCE_OU_ID NOWAIT;
219 Recinfo C%ROWTYPE;
220 BEGIN
221 OPEN C;
222 FETCH C INTO Recinfo;
223 If (C%NOTFOUND) then
224 CLOSE C;
225 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
226 APP_EXCEPTION.RAISE_EXCEPTION;
227 End If;
228 CLOSE C;
229 if (
230 ( Recinfo.INSTANCE_OU_ID = p_INSTANCE_OU_ID)
231 AND ( ( Recinfo.INSTANCE_ID = p_INSTANCE_ID)
232 OR ( ( Recinfo.INSTANCE_ID IS NULL )
233 AND ( p_INSTANCE_ID IS NULL )))
234 AND ( ( Recinfo.OPERATING_UNIT_ID = p_OPERATING_UNIT_ID)
235 OR ( ( Recinfo.OPERATING_UNIT_ID IS NULL )
236 AND ( p_OPERATING_UNIT_ID IS NULL )))
237 AND ( ( Recinfo.RELATIONSHIP_TYPE_CODE = p_RELATIONSHIP_TYPE_CODE)
238 OR ( ( Recinfo.RELATIONSHIP_TYPE_CODE IS NULL )
239 AND ( p_RELATIONSHIP_TYPE_CODE IS NULL )))
240 AND ( ( Recinfo.ACTIVE_START_DATE = p_ACTIVE_START_DATE)
241 OR ( ( Recinfo.ACTIVE_START_DATE IS NULL )
242 AND ( p_ACTIVE_START_DATE IS NULL )))
243 AND ( ( Recinfo.ACTIVE_END_DATE = p_ACTIVE_END_DATE)
244 OR ( ( Recinfo.ACTIVE_END_DATE IS NULL )
245 AND ( p_ACTIVE_END_DATE IS NULL )))
246 AND ( ( Recinfo.CONTEXT = p_CONTEXT)
247 OR ( ( Recinfo.CONTEXT IS NULL )
248 AND ( p_CONTEXT IS NULL )))
249 AND ( ( Recinfo.ATTRIBUTE1 = p_ATTRIBUTE1)
250 OR ( ( Recinfo.ATTRIBUTE1 IS NULL )
251 AND ( p_ATTRIBUTE1 IS NULL )))
252 AND ( ( Recinfo.ATTRIBUTE2 = p_ATTRIBUTE2)
253 OR ( ( Recinfo.ATTRIBUTE2 IS NULL )
254 AND ( p_ATTRIBUTE2 IS NULL )))
255 AND ( ( Recinfo.ATTRIBUTE3 = p_ATTRIBUTE3)
256 OR ( ( Recinfo.ATTRIBUTE3 IS NULL )
257 AND ( p_ATTRIBUTE3 IS NULL )))
258 AND ( ( Recinfo.ATTRIBUTE4 = p_ATTRIBUTE4)
259 OR ( ( Recinfo.ATTRIBUTE4 IS NULL )
260 AND ( p_ATTRIBUTE4 IS NULL )))
261 AND ( ( Recinfo.ATTRIBUTE5 = p_ATTRIBUTE5)
262 OR ( ( Recinfo.ATTRIBUTE5 IS NULL )
263 AND ( p_ATTRIBUTE5 IS NULL )))
264 AND ( ( Recinfo.ATTRIBUTE6 = p_ATTRIBUTE6)
265 OR ( ( Recinfo.ATTRIBUTE6 IS NULL )
266 AND ( p_ATTRIBUTE6 IS NULL )))
267 AND ( ( Recinfo.ATTRIBUTE7 = p_ATTRIBUTE7)
268 OR ( ( Recinfo.ATTRIBUTE7 IS NULL )
269 AND ( p_ATTRIBUTE7 IS NULL )))
270 AND ( ( Recinfo.ATTRIBUTE8 = p_ATTRIBUTE8)
271 OR ( ( Recinfo.ATTRIBUTE8 IS NULL )
272 AND ( p_ATTRIBUTE8 IS NULL )))
273 AND ( ( Recinfo.ATTRIBUTE9 = p_ATTRIBUTE9)
274 OR ( ( Recinfo.ATTRIBUTE9 IS NULL )
275 AND ( p_ATTRIBUTE9 IS NULL )))
276 AND ( ( Recinfo.ATTRIBUTE10 = p_ATTRIBUTE10)
277 OR ( ( Recinfo.ATTRIBUTE10 IS NULL )
278 AND ( p_ATTRIBUTE10 IS NULL )))
279 AND ( ( Recinfo.ATTRIBUTE11 = p_ATTRIBUTE11)
280 OR ( ( Recinfo.ATTRIBUTE11 IS NULL )
281 AND ( p_ATTRIBUTE11 IS NULL )))
282 AND ( ( Recinfo.ATTRIBUTE12 = p_ATTRIBUTE12)
283 OR ( ( Recinfo.ATTRIBUTE12 IS NULL )
284 AND ( p_ATTRIBUTE12 IS NULL )))
285 AND ( ( Recinfo.ATTRIBUTE13 = p_ATTRIBUTE13)
286 OR ( ( Recinfo.ATTRIBUTE13 IS NULL )
287 AND ( p_ATTRIBUTE13 IS NULL )))
288 AND ( ( Recinfo.ATTRIBUTE14 = p_ATTRIBUTE14)
289 OR ( ( Recinfo.ATTRIBUTE14 IS NULL )
290 AND ( p_ATTRIBUTE14 IS NULL )))
291 AND ( ( Recinfo.ATTRIBUTE15 = p_ATTRIBUTE15)
292 OR ( ( Recinfo.ATTRIBUTE15 IS NULL )
293 AND ( p_ATTRIBUTE15 IS NULL )))
294 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
295 OR ( ( Recinfo.CREATED_BY IS NULL )
296 AND ( p_CREATED_BY IS NULL )))
297 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
298 OR ( ( Recinfo.CREATION_DATE IS NULL )
299 AND ( p_CREATION_DATE IS NULL )))
300 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
301 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
302 AND ( p_LAST_UPDATED_BY IS NULL )))
303 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
304 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
305 AND ( p_LAST_UPDATE_DATE IS NULL )))
306 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
307 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
308 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
309 AND ( ( Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
310 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
311 AND ( p_OBJECT_VERSION_NUMBER IS NULL )))
312 ) then
313 return;
314 else
315 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
316 APP_EXCEPTION.RAISE_EXCEPTION;
317 End If;
318 END Lock_Row;
319
320 End CSI_I_ORG_ASSIGNMENTS_PKG;