[Home] [Help]
PACKAGE BODY: APPS.CSI_ORGANIZATION_UNIT_PVT
Source
1 PACKAGE BODY csi_organization_unit_pvt AS
2 /* $Header: csivoub.pls 120.1 2005/08/16 10:50:01 sguthiva noship $ */
3
4 g_pkg_name VARCHAR2(30) := 'csi_organization_unit_pvt';
5 g_expire_flag VARCHAR2(1) := 'N';
6
7
8 /*----------------------------------------------------------*/
9 /* Procedure name: Initialize_ou_rec_no_dump */
10 /* Description : This gets the first record from history */
11 /* */
12 /*----------------------------------------------------------*/
13
14 PROCEDURE Initialize_ou_rec_no_dump
15 (
16 x_ou_rec IN OUT NOCOPY csi_datastructures_pub.org_units_header_rec,
17 p_ou_id IN NUMBER,
18 x_first_no_dump IN OUT NOCOPY DATE
19 ) IS
20
21 CURSOR Int_no_dump(p_ou_id IN NUMBER ) IS
22 SELECT creation_date,
23 NEW_operating_unit_id,
24 NEW_relationship_type_code,
25 NEW_active_start_date,
26 NEW_active_end_date,
27 NEW_context,
28 NEW_attribute1 ,
29 NEW_attribute2,
30 NEW_attribute3,
31 NEW_attribute4,
32 NEW_attribute5,
33 NEW_attribute6,
34 NEW_attribute7,
35 NEW_attribute8,
36 NEW_attribute9,
37 NEW_attribute10,
38 NEW_attribute11,
39 NEW_attribute12,
40 NEW_attribute13,
41 NEW_attribute14,
42 NEW_attribute15
43 FROM csi_i_org_assignments_h
44 WHERE instance_ou_id = p_ou_id
45 ORDER BY creation_date;
46
47 BEGIN
48
49 FOR C1 IN Int_no_dump(p_ou_id)
50 LOOP
51 IF Int_no_dump%ROWCOUNT = 1 THEN
52 x_first_no_dump := C1.creation_date;
53 x_ou_rec.operating_unit_id := C1.NEW_operating_unit_id;
54 x_ou_rec.relationship_type_code := C1.NEW_relationship_type_code;
55 x_ou_rec.active_start_date := C1.NEW_active_start_date;
56 x_ou_rec.active_end_date := C1.NEW_active_end_date;
57 x_ou_rec.context := C1.NEW_context;
58 x_ou_rec.attribute1 := C1.NEW_attribute1;
59 x_ou_rec.attribute2 := C1.NEW_attribute2;
60 x_ou_rec.attribute3 := C1.NEW_attribute3;
61 x_ou_rec.attribute4 := C1.NEW_attribute4;
62 x_ou_rec.attribute5 := C1.NEW_attribute5;
63 x_ou_rec.attribute6 := C1.NEW_attribute6;
64 x_ou_rec.attribute7 := C1.NEW_attribute7;
65 x_ou_rec.attribute8 := C1.NEW_attribute8;
66 x_ou_rec.attribute9 := C1.NEW_attribute9;
67 x_ou_rec.attribute10 := C1.NEW_attribute10;
68 x_ou_rec.attribute11 := C1.NEW_attribute11;
69 x_ou_rec.attribute12 := C1.NEW_attribute12;
70 x_ou_rec.attribute13 := C1.NEW_attribute13;
71 x_ou_rec.attribute14 := C1.NEW_attribute14;
72 x_ou_rec.attribute15 := C1.NEW_attribute15;
73 ELSE
74 EXIT;
75 END IF;
76 END LOOP;
77 END Initialize_ou_rec_no_dump;
78
79
80
81 /*----------------------------------------------------------*/
82 /* Procedure name: Initialize_ou_rec */
83 /* Description : This procudure recontructs the record */
84 /* from the history */
85 /*----------------------------------------------------------*/
86
87 PROCEDURE Initialize_ou_rec
88 (
89 x_ou_rec IN OUT NOCOPY csi_datastructures_pub.org_units_header_rec,
90 p_ou_h_id IN NUMBER,
91 x_nearest_full_dump IN OUT NOCOPY DATE
92 ) IS
93
94 CURSOR Int_nearest_full_dump(p_ou_hist_id IN NUMBER ) IS
95 SELECT creation_date,
96 NEW_operating_unit_id,
97 NEW_relationship_type_code,
98 NEW_active_start_date,
99 NEW_active_end_date,
100 NEW_context,
101 NEW_attribute1 ,
102 NEW_attribute2,
103 NEW_attribute3,
104 NEW_attribute4,
105 NEW_attribute5,
106 NEW_attribute6,
107 NEW_attribute7,
108 NEW_attribute8,
109 NEW_attribute9,
110 NEW_attribute10,
111 NEW_attribute11,
112 NEW_attribute12,
113 NEW_attribute13,
114 NEW_attribute14,
115 NEW_attribute15
116 FROM csi_i_org_assignments_h
117 WHERE instance_ou_history_id = p_ou_hist_id
118 AND full_dump_flag = 'Y';
119
120 BEGIN
121
122 FOR C1 IN Int_nearest_full_dump(p_ou_h_id)
123 LOOP
124 x_nearest_full_dump := C1.creation_date;
125 x_ou_rec.operating_unit_id := C1.NEW_operating_unit_id;
126 x_ou_rec.relationship_type_code := C1.NEW_relationship_type_code;
127 x_ou_rec.active_start_date := C1.NEW_active_start_date;
128 x_ou_rec.active_end_date := C1.NEW_active_end_date;
129 x_ou_rec.context := C1.NEW_context;
130 x_ou_rec.attribute1 := C1.NEW_attribute1;
131 x_ou_rec.attribute2 := C1.NEW_attribute2;
132 x_ou_rec.attribute3 := C1.NEW_attribute3;
133 x_ou_rec.attribute4 := C1.NEW_attribute4;
134 x_ou_rec.attribute5 := C1.NEW_attribute5;
135 x_ou_rec.attribute6 := C1.NEW_attribute6;
136 x_ou_rec.attribute7 := C1.NEW_attribute7;
137 x_ou_rec.attribute8 := C1.NEW_attribute8;
138 x_ou_rec.attribute9 := C1.NEW_attribute9;
139 x_ou_rec.attribute10 := C1.NEW_attribute10;
140 x_ou_rec.attribute11 := C1.NEW_attribute11;
141 x_ou_rec.attribute12 := C1.NEW_attribute12;
142 x_ou_rec.attribute13 := C1.NEW_attribute13;
143 x_ou_rec.attribute14 := C1.NEW_attribute14;
144 x_ou_rec.attribute15 := C1.NEW_attribute15;
145 END LOOP;
146 END Initialize_ou_rec ;
147
148
149
150 /*----------------------------------------------------------*/
151 /* Procedure name: Construct_ou_from_hist */
152 /* Description : This procudure recontructs the record */
153 /* from the history */
154 /*----------------------------------------------------------*/
155
156 PROCEDURE Construct_ou_from_hist
157 ( x_ou_tbl IN OUT NOCOPY csi_datastructures_pub.org_units_header_tbl,
158 p_time_stamp IN DATE
159 ) IS
160
161 l_nearest_full_dump DATE := p_time_stamp;
162 l_ou_hist_id NUMBER;
163 l_ou_tbl csi_datastructures_pub.org_units_header_tbl;
164 l_ou_count NUMBER := 0;
165 --
166 Process_next EXCEPTION;
167
168 CURSOR get_nearest_full_dump(p_inst_ou_id IN NUMBER ,p_time IN DATE) IS
169 SELECT MAX(instance_ou_history_id)
170 FROM csi_i_org_assignments_h
171 WHERE creation_date <= p_time
172 AND instance_ou_id = p_inst_ou_id
173 AND full_dump_flag = 'Y';
174
175
176 CURSOR get_ou_hist(p_inst_ou_id IN NUMBER ,
177 p_nearest_full_dump IN DATE,
178 p_time IN DATE ) IS
179 SELECT
180 OLD_OPERATING_UNIT_ID,
181 NEW_OPERATING_UNIT_ID ,
182 OLD_RELATIONSHIP_TYPE_CODE ,
183 NEW_RELATIONSHIP_TYPE_CODE ,
184 OLD_ACTIVE_START_DATE,
185 NEW_ACTIVE_START_DATE,
186 OLD_ACTIVE_END_DATE ,
187 NEW_ACTIVE_END_DATE ,
188 OLD_CONTEXT ,
189 NEW_CONTEXT ,
190 OLD_ATTRIBUTE1 ,
191 NEW_ATTRIBUTE1 ,
192 OLD_ATTRIBUTE2 ,
193 NEW_ATTRIBUTE2 ,
194 OLD_ATTRIBUTE3 ,
195 NEW_ATTRIBUTE3 ,
196 OLD_ATTRIBUTE4 ,
197 NEW_ATTRIBUTE4 ,
198 OLD_ATTRIBUTE5 ,
199 NEW_ATTRIBUTE5 ,
200 OLD_ATTRIBUTE6 ,
201 NEW_ATTRIBUTE6 ,
202 OLD_ATTRIBUTE7 ,
203 NEW_ATTRIBUTE7 ,
204 OLD_ATTRIBUTE8 ,
205 NEW_ATTRIBUTE8 ,
206 OLD_ATTRIBUTE9 ,
207 NEW_ATTRIBUTE9 ,
208 OLD_ATTRIBUTE10 ,
209 NEW_ATTRIBUTE10 ,
210 OLD_ATTRIBUTE11 ,
211 NEW_ATTRIBUTE11 ,
212 OLD_ATTRIBUTE12 ,
213 NEW_ATTRIBUTE12 ,
214 OLD_ATTRIBUTE13 ,
215 NEW_ATTRIBUTE13 ,
216 OLD_ATTRIBUTE14 ,
217 NEW_ATTRIBUTE14 ,
218 OLD_ATTRIBUTE15 ,
219 NEW_ATTRIBUTE15
220 FROM CSI_I_ORG_ASSIGNMENTS_H
221 WHERE creation_date <= p_time
222 AND creation_date >= p_nearest_full_dump
223 AND instance_ou_id = p_inst_ou_id
224 ORDER BY creation_date;
225
226 l_time_stamp DATE := p_time_stamp;
227
228 BEGIN
229 l_ou_tbl := x_ou_tbl;
230 IF l_ou_tbl.COUNT > 0 THEN
231 FOR i IN l_ou_tbl.FIRST..l_ou_tbl.LAST LOOP
232 BEGIN
233 OPEN get_nearest_full_dump(l_ou_tbl(i).instance_ou_id,p_time_stamp);
234 FETCH get_nearest_full_dump INTO l_ou_hist_id;
235 CLOSE get_nearest_full_dump;
236
237 IF l_ou_hist_id IS NOT NULL THEN
238 Initialize_ou_rec( l_ou_tbl(i), l_ou_hist_id ,l_nearest_full_dump);
239 ELSE
240 Initialize_ou_rec_no_dump(l_ou_tbl(i), l_ou_tbl(i).instance_ou_id,l_time_stamp);
241
242 l_nearest_full_dump := l_time_stamp;
243 -- If the user chooses a date before the creation date of the instance
244 -- then raise an error
245 IF p_time_stamp < l_time_stamp THEN
246 -- Messages Commented for bug 2423342. Records that do not qualify should get deleted.
247 -- FND_MESSAGE.SET_NAME('CSI','CSI_H_DATE_BEFORE_CRE_DATE');
248 -- FND_MESSAGE.SET_TOKEN('CREATION_DATE',to_char(l_time_stamp, 'DD-MON-YYYY HH24:MI:SS'));
249 -- FND_MESSAGE.SET_TOKEN('USER_DATE',to_char(p_time_stamp, 'DD-MON-YYYY HH24:MI:SS'));
250 -- FND_MSG_PUB.Add;
251 l_ou_tbl.DELETE(i);
252 RAISE Process_next;
253 END IF;
254 END IF;
255
256 FOR C2 IN get_ou_hist(l_ou_tbl(i).instance_ou_id ,l_nearest_full_dump,p_time_stamp ) LOOP
257
258 IF (C2.OLD_OPERATING_UNIT_ID IS NULL AND C2.NEW_OPERATING_UNIT_ID IS NOT NULL)
259 OR (C2.OLD_OPERATING_UNIT_ID IS NOT NULL AND C2.NEW_OPERATING_UNIT_ID IS NULL)
260 OR (C2.OLD_OPERATING_UNIT_ID <> C2.NEW_OPERATING_UNIT_ID) THEN
261 l_ou_tbl(i).OPERATING_UNIT_ID := C2.NEW_OPERATING_UNIT_ID;
262 END IF;
263
264 IF (C2.OLD_RELATIONSHIP_TYPE_CODE IS NULL AND C2.NEW_RELATIONSHIP_TYPE_CODE IS NOT NULL)
265 OR (C2.OLD_RELATIONSHIP_TYPE_CODE IS NOT NULL AND C2.NEW_RELATIONSHIP_TYPE_CODE IS NULL)
266 OR (C2.OLD_RELATIONSHIP_TYPE_CODE <> C2.NEW_RELATIONSHIP_TYPE_CODE) THEN
267 l_ou_tbl(i).RELATIONSHIP_TYPE_CODE := C2.NEW_RELATIONSHIP_TYPE_CODE;
268 END IF;
269
270
271 IF (C2.OLD_ACTIVE_START_DATE IS NULL AND C2.NEW_ACTIVE_START_DATE IS NOT NULL)
272 OR (C2.OLD_ACTIVE_START_DATE IS NOT NULL AND C2.NEW_ACTIVE_START_DATE IS NULL)
273 OR (C2.OLD_ACTIVE_START_DATE <> C2.NEW_ACTIVE_START_DATE) THEN
274 l_ou_tbl(i).ACTIVE_START_DATE := C2.NEW_ACTIVE_START_DATE;
275 END IF;
276
277
278 IF (C2.OLD_ACTIVE_END_DATE IS NULL AND C2.NEW_ACTIVE_END_DATE IS NOT NULL)
279 OR (C2.OLD_ACTIVE_END_DATE IS NOT NULL AND C2.NEW_ACTIVE_END_DATE IS NULL)
280 OR (C2.OLD_ACTIVE_END_DATE <> C2.NEW_ACTIVE_END_DATE) THEN
281 l_ou_tbl(i).ACTIVE_END_DATE := C2.NEW_ACTIVE_END_DATE;
282 END IF;
283
284
285 IF (C2.OLD_CONTEXT IS NULL AND C2.NEW_CONTEXT IS NOT NULL)
286 OR (C2.OLD_CONTEXT IS NOT NULL AND C2.NEW_CONTEXT IS NULL)
287 OR (C2.OLD_CONTEXT <> C2.NEW_CONTEXT) THEN
288 l_ou_tbl(i).CONTEXT := C2.NEW_CONTEXT;
289 END IF;
290
291 IF (C2.OLD_ATTRIBUTE1 IS NULL AND C2.NEW_ATTRIBUTE1 IS NOT NULL)
292 OR (C2.OLD_ATTRIBUTE1 IS NOT NULL AND C2.NEW_ATTRIBUTE1 IS NULL)
293 OR (C2.OLD_ATTRIBUTE1 <> C2.NEW_ATTRIBUTE1) THEN
294 l_ou_tbl(i).ATTRIBUTE1 := C2.NEW_ATTRIBUTE1;
295 END IF;
296
297 IF (C2.OLD_ATTRIBUTE2 IS NULL AND C2.NEW_ATTRIBUTE2 IS NOT NULL)
298 OR (C2.OLD_ATTRIBUTE2 IS NOT NULL AND C2.NEW_ATTRIBUTE2 IS NULL)
299 OR (C2.OLD_ATTRIBUTE2 <> C2.NEW_ATTRIBUTE2) THEN
300 l_ou_tbl(i).ATTRIBUTE2 := C2.NEW_ATTRIBUTE2;
301 END IF;
302
303 IF (C2.OLD_ATTRIBUTE3 IS NULL AND C2.NEW_ATTRIBUTE3 IS NOT NULL)
304 OR (C2.OLD_ATTRIBUTE3 IS NOT NULL AND C2.NEW_ATTRIBUTE3 IS NULL)
305 OR (C2.OLD_ATTRIBUTE3 <> C2.NEW_ATTRIBUTE3) THEN
306 l_ou_tbl(i).ATTRIBUTE3 := C2.NEW_ATTRIBUTE3;
307 END IF;
308
309 IF (C2.OLD_ATTRIBUTE4 IS NULL AND C2.NEW_ATTRIBUTE4 IS NOT NULL)
310 OR (C2.OLD_ATTRIBUTE4 IS NOT NULL AND C2.NEW_ATTRIBUTE4 IS NULL)
311 OR (C2.OLD_ATTRIBUTE4 <> C2.NEW_ATTRIBUTE4) THEN
312 l_ou_tbl(i).ATTRIBUTE4 := C2.NEW_ATTRIBUTE4;
313 END IF;
314
315
316 IF (C2.OLD_ATTRIBUTE5 IS NULL AND C2.NEW_ATTRIBUTE5 IS NOT NULL)
317 OR (C2.OLD_ATTRIBUTE5 IS NOT NULL AND C2.NEW_ATTRIBUTE5 IS NULL)
318 OR (C2.OLD_ATTRIBUTE5 <> C2.NEW_ATTRIBUTE5) THEN
319 l_ou_tbl(i).ATTRIBUTE5 := C2.NEW_ATTRIBUTE5;
320 END IF;
321
322
323 IF (C2.OLD_ATTRIBUTE6 IS NULL AND C2.NEW_ATTRIBUTE6 IS NOT NULL)
324 OR (C2.OLD_ATTRIBUTE6 IS NOT NULL AND C2.NEW_ATTRIBUTE6 IS NULL)
325 OR (C2.OLD_ATTRIBUTE6 <> C2.NEW_ATTRIBUTE6) THEN
326 l_ou_tbl(i).ATTRIBUTE6 := C2.NEW_ATTRIBUTE6;
327 END IF;
328
329 IF (C2.OLD_ATTRIBUTE7 IS NULL AND C2.NEW_ATTRIBUTE7 IS NOT NULL)
330 OR (C2.OLD_ATTRIBUTE7 IS NOT NULL AND C2.NEW_ATTRIBUTE7 IS NULL)
331 OR (C2.OLD_ATTRIBUTE7 <> C2.NEW_ATTRIBUTE7) THEN
332 l_ou_tbl(i).ATTRIBUTE7 := C2.NEW_ATTRIBUTE7;
333 END IF;
334
335 IF (C2.OLD_ATTRIBUTE8 IS NULL AND C2.NEW_ATTRIBUTE8 IS NOT NULL)
336 OR (C2.OLD_ATTRIBUTE8 IS NOT NULL AND C2.NEW_ATTRIBUTE8 IS NULL)
337 OR (C2.OLD_ATTRIBUTE8 <> C2.NEW_ATTRIBUTE8) THEN
338 l_ou_tbl(i).ATTRIBUTE8 := C2.NEW_ATTRIBUTE8;
339 END IF;
340
341 IF (C2.OLD_ATTRIBUTE9 IS NULL AND C2.NEW_ATTRIBUTE9 IS NOT NULL)
342 OR (C2.OLD_ATTRIBUTE9 IS NOT NULL AND C2.NEW_ATTRIBUTE9 IS NULL)
343 OR (C2.OLD_ATTRIBUTE9 <> C2.NEW_ATTRIBUTE9) THEN
344 l_ou_tbl(i).ATTRIBUTE3 := C2.NEW_ATTRIBUTE3;
345 END IF;
346
347
348 IF (C2.OLD_ATTRIBUTE10 IS NULL AND C2.NEW_ATTRIBUTE10 IS NOT NULL)
349 OR (C2.OLD_ATTRIBUTE10 IS NOT NULL AND C2.NEW_ATTRIBUTE10 IS NULL)
350 OR (C2.OLD_ATTRIBUTE10 <> C2.NEW_ATTRIBUTE10) THEN
351 l_ou_tbl(i).ATTRIBUTE10 := C2.NEW_ATTRIBUTE10;
352 END IF;
353
354
355
356 IF (C2.OLD_ATTRIBUTE11 IS NULL AND C2.NEW_ATTRIBUTE11 IS NOT NULL)
357 OR (C2.OLD_ATTRIBUTE11 IS NOT NULL AND C2.NEW_ATTRIBUTE11 IS NULL)
358 OR (C2.OLD_ATTRIBUTE11 <> C2.NEW_ATTRIBUTE11) THEN
359 l_ou_tbl(i).ATTRIBUTE11 := C2.NEW_ATTRIBUTE11;
360 END IF;
361
362 IF (C2.OLD_ATTRIBUTE12 IS NULL AND C2.NEW_ATTRIBUTE12 IS NOT NULL)
363 OR (C2.OLD_ATTRIBUTE12 IS NOT NULL AND C2.NEW_ATTRIBUTE12 IS NULL)
364 OR (C2.OLD_ATTRIBUTE12 <> C2.NEW_ATTRIBUTE12) THEN
365 l_ou_tbl(i).ATTRIBUTE12 := C2.NEW_ATTRIBUTE12;
366 END IF;
367
368
369 IF (C2.OLD_ATTRIBUTE13 IS NULL AND C2.NEW_ATTRIBUTE13 IS NOT NULL)
370 OR (C2.OLD_ATTRIBUTE13 IS NOT NULL AND C2.NEW_ATTRIBUTE13 IS NULL)
371 OR (C2.OLD_ATTRIBUTE13 <> C2.NEW_ATTRIBUTE13) THEN
372 l_ou_tbl(i).ATTRIBUTE13 := C2.NEW_ATTRIBUTE13;
373 END IF;
374
375
376 IF (C2.OLD_ATTRIBUTE14 IS NULL AND C2.NEW_ATTRIBUTE14 IS NOT NULL)
377 OR (C2.OLD_ATTRIBUTE14 IS NOT NULL AND C2.NEW_ATTRIBUTE14 IS NULL)
378 OR (C2.OLD_ATTRIBUTE14 <> C2.NEW_ATTRIBUTE14) THEN
379 l_ou_tbl(i).ATTRIBUTE14 := C2.NEW_ATTRIBUTE14;
380 END IF;
381
382 IF (C2.OLD_ATTRIBUTE15 IS NULL AND C2.NEW_ATTRIBUTE15 IS NOT NULL)
383 OR (C2.OLD_ATTRIBUTE15 IS NOT NULL AND C2.NEW_ATTRIBUTE15 IS NULL)
384 OR (C2.OLD_ATTRIBUTE15 <> C2.NEW_ATTRIBUTE15) THEN
385 l_ou_tbl(i).ATTRIBUTE15 := C2.NEW_ATTRIBUTE15;
386 END IF;
387
388 END LOOP;
389 EXCEPTION
390 WHEN Process_next THEN
391 NULL;
392 END;
393 END LOOP;
394 --
395 x_ou_tbl.DELETE;
396 IF l_ou_tbl.count > 0 THEN
397 FOR ou_row in l_ou_tbl.FIRST .. l_ou_tbl.LAST
398 LOOP
399 IF l_ou_tbl.EXISTS(ou_row) THEN
400 l_ou_count := l_ou_count + 1;
401 x_ou_tbl(l_ou_count) := l_ou_tbl(ou_row);
402 END IF;
403 END LOOP;
404 END IF;
405 END IF;
406 END Construct_ou_from_hist;
407
408 /*----------------------------------------------------------*/
409 /* Procedure name: Resolve_id_columns */
410 /* Description : This procudure gets the descriptions for */
411 /* id columns */
412 /*----------------------------------------------------------*/
413
414 PROCEDURE Resolve_id_columns
415 (p_org_units_header_tbl IN OUT NOCOPY csi_datastructures_pub.org_units_header_tbl)
416
417 IS
418
419 l_rltn_lookup_type VARCHAR2(30) := 'CSI_IO_RELATIONSHIP_TYPE_CODE';
420
421 BEGIN
422
423 FOR tab_row in p_org_units_header_tbl.FIRST..p_org_units_header_tbl.LAST
424 LOOP
425
426 BEGIN
427 SELECT name
428 INTO p_org_units_header_tbl(tab_row).operating_unit_name
429 FROM hr_operating_units
430 WHERE organization_id = p_org_units_header_tbl(tab_row).operating_unit_id;
431 EXCEPTION
432 WHEN OTHERS THEN
433 NULL;
434 END;
435
436 --type_name
437
438 BEGIN
439 SELECT meaning
440 INTO p_org_units_header_tbl(tab_row).relationship_type_name
441 FROM csi_lookups
442 WHERE lookup_code = p_org_units_header_tbl(tab_row).relationship_type_code
443 AND lookup_type = l_rltn_lookup_type;
444 EXCEPTION
445 WHEN OTHERS THEN
446 NULL;
447 END;
448 END LOOP;
449 END Resolve_id_columns;
450
451 /*----------------------------------------------------------*/
452 /* Procedure name: Define_ou_Columns */
453 /* Description : This procudure defines the columns */
454 /* for the Dynamic SQL */
455 /*----------------------------------------------------------*/
456
457 PROCEDURE Define_ou_Columns
458 ( p_get_ou_cursor_id IN NUMBER
459 ) IS
460 l_ou_rec csi_datastructures_pub.org_units_header_rec;
461 BEGIN
462 dbms_sql.define_column(p_get_ou_cursor_id, 1, l_ou_rec.instance_ou_id);
463 dbms_sql.define_column(p_get_ou_cursor_id, 2, l_ou_rec.instance_id);
464 dbms_sql.define_column(p_get_ou_cursor_id, 3, l_ou_rec.operating_unit_id);
465 dbms_sql.define_column(p_get_ou_cursor_id, 4, l_ou_rec.relationship_type_code,30);
466 dbms_sql.define_column(p_get_ou_cursor_id, 5, l_ou_rec.active_start_date);
467 dbms_sql.define_column(p_get_ou_cursor_id, 6, l_ou_rec.active_end_date);
468 dbms_sql.define_column(p_get_ou_cursor_id, 7, l_ou_rec.context,30);
469 dbms_sql.define_column(p_get_ou_cursor_id, 8, l_ou_rec.attribute1,150);
470 dbms_sql.define_column(p_get_ou_cursor_id, 9, l_ou_rec.attribute2,150);
471 dbms_sql.define_column(p_get_ou_cursor_id, 10, l_ou_rec.attribute3,150);
472 dbms_sql.define_column(p_get_ou_cursor_id, 11, l_ou_rec.attribute4,150);
473 dbms_sql.define_column(p_get_ou_cursor_id, 12, l_ou_rec.attribute5,150);
474 dbms_sql.define_column(p_get_ou_cursor_id, 13, l_ou_rec.attribute6,150);
475 dbms_sql.define_column(p_get_ou_cursor_id, 14, l_ou_rec.attribute7,150);
476 dbms_sql.define_column(p_get_ou_cursor_id, 15, l_ou_rec.attribute8,150);
477 dbms_sql.define_column(p_get_ou_cursor_id, 16, l_ou_rec.attribute9,150);
478 dbms_sql.define_column(p_get_ou_cursor_id, 17, l_ou_rec.attribute10,150);
479 dbms_sql.define_column(p_get_ou_cursor_id, 18, l_ou_rec.attribute11,150);
480 dbms_sql.define_column(p_get_ou_cursor_id, 19, l_ou_rec.attribute12,150);
481 dbms_sql.define_column(p_get_ou_cursor_id, 20, l_ou_rec.attribute13,150);
482 dbms_sql.define_column(p_get_ou_cursor_id, 21, l_ou_rec.attribute14,150);
483 dbms_sql.define_column(p_get_ou_cursor_id, 22, l_ou_rec.attribute15,150);
484 dbms_sql.define_column(p_get_ou_cursor_id, 28, l_ou_rec.object_version_number);
485 END Define_ou_Columns;
486
487
488 /*----------------------------------------------------------*/
489 /* Procedure name: Get_ou_Column_Values */
490 /* Description : This procudure gets the column values */
491 /* for the Dynamic SQL */
492 /*----------------------------------------------------------*/
493
494 PROCEDURE Get_ou_Column_Values
495 ( p_get_ou_cursor_id IN NUMBER,
496 x_ou_rec OUT NOCOPY csi_datastructures_pub.org_units_header_rec
497 ) IS
498
499 BEGIN
500 dbms_sql.column_value(p_get_ou_cursor_id, 1, x_ou_rec.instance_ou_id);
501 dbms_sql.column_value(p_get_ou_cursor_id, 2, x_ou_rec.instance_id);
502 dbms_sql.column_value(p_get_ou_cursor_id, 3, x_ou_rec.operating_unit_id);
503 dbms_sql.column_value(p_get_ou_cursor_id, 4, x_ou_rec.relationship_type_code);
504 dbms_sql.column_value(p_get_ou_cursor_id, 5, x_ou_rec.active_start_date);
505 dbms_sql.column_value(p_get_ou_cursor_id, 6, x_ou_rec.active_end_date);
506 dbms_sql.column_value(p_get_ou_cursor_id, 7, x_ou_rec.context);
507 dbms_sql.column_value(p_get_ou_cursor_id, 8, x_ou_rec.attribute1);
508 dbms_sql.column_value(p_get_ou_cursor_id, 9, x_ou_rec.attribute2);
509 dbms_sql.column_value(p_get_ou_cursor_id, 10, x_ou_rec.attribute3);
510 dbms_sql.column_value(p_get_ou_cursor_id, 11, x_ou_rec.attribute4);
511 dbms_sql.column_value(p_get_ou_cursor_id, 12, x_ou_rec.attribute5);
512 dbms_sql.column_value(p_get_ou_cursor_id, 13, x_ou_rec.attribute6);
513 dbms_sql.column_value(p_get_ou_cursor_id, 14, x_ou_rec.attribute7);
514 dbms_sql.column_value(p_get_ou_cursor_id, 15, x_ou_rec.attribute8);
515 dbms_sql.column_value(p_get_ou_cursor_id, 16, x_ou_rec.attribute9);
516 dbms_sql.column_value(p_get_ou_cursor_id, 17, x_ou_rec.attribute10);
517 dbms_sql.column_value(p_get_ou_cursor_id, 18, x_ou_rec.attribute11);
518 dbms_sql.column_value(p_get_ou_cursor_id, 19, x_ou_rec.attribute12);
519 dbms_sql.column_value(p_get_ou_cursor_id, 20, x_ou_rec.attribute13);
520 dbms_sql.column_value(p_get_ou_cursor_id, 21, x_ou_rec.attribute14);
521 dbms_sql.column_value(p_get_ou_cursor_id, 22, x_ou_rec.attribute15);
522 dbms_sql.column_value(p_get_ou_cursor_id, 28, x_ou_rec.object_version_number);
523
524 END Get_ou_Column_Values;
525
526
527 PROCEDURE Bind_ou_variable
528 (p_ou_query_rec IN csi_datastructures_pub.organization_unit_query_rec,
529 p_cur_get_ou IN NUMBER
530 )
531 IS
532 BEGIN
533 IF( (p_ou_query_rec.instance_ou_id IS NOT NULL)
534 AND (p_ou_query_rec.instance_ou_id <> FND_API.G_MISS_NUM)) THEN
535 DBMS_SQL.BIND_VARIABLE(p_cur_get_ou, ':instance_ou_id', p_ou_query_rec.instance_ou_id);
536 END IF;
537
538 IF( (p_ou_query_rec.instance_id IS NOT NULL)
539 AND (p_ou_query_rec.instance_id <> FND_API.G_MISS_NUM)) THEN
540 DBMS_SQL.BIND_VARIABLE(p_cur_get_ou, ':instance_id', p_ou_query_rec.instance_id);
541 END IF;
542
543 IF( (p_ou_query_rec.operating_unit_id IS NOT NULL)
544 AND (p_ou_query_rec.operating_unit_id<> FND_API.G_MISS_NUM)) THEN
545 DBMS_SQL.BIND_VARIABLE(p_cur_get_ou, ':operating_unit_id', p_ou_query_rec.operating_unit_id);
546 END IF;
547
548 IF( (p_ou_query_rec.relationship_type_code IS NOT NULL)
549 AND (p_ou_query_rec.relationship_type_code <> FND_API.G_MISS_CHAR)) THEN
550 DBMS_SQL.BIND_VARIABLE(p_cur_get_ou, ':relationship_type_code', p_ou_query_rec.relationship_type_code);
551 END IF;
552
553 END Bind_ou_variable;
554
555
556
557
558 /*----------------------------------------------------------*/
559 /* Procedure name: Gen_ou_Where_Clause */
560 /* Description : Procedure used to generate the where */
561 /* clause for Organization units */
562 /*----------------------------------------------------------*/
563
564 PROCEDURE Gen_ou_Where_Clause
565 ( p_ou_query_rec IN csi_datastructures_pub.organization_unit_query_rec
566 ,x_where_clause OUT NOCOPY VARCHAR2
567 ) IS
568
569 BEGIN
570
571 -- Assign null at the start
572 x_where_clause := '';
573
574 IF (( p_ou_query_rec.instance_ou_id IS NOT NULL) AND
575 (p_ou_query_rec.instance_ou_id <> FND_API.G_MISS_NUM)) THEN
576 x_where_clause := ' instance_ou_id = :instance_ou_id ';
577 ELSIF ( p_ou_query_rec.instance_ou_id IS NULL) THEN
578 x_where_clause := ' instance_ou_id IS NULL ';
579 END IF;
580
581 IF ((p_ou_query_rec.instance_id IS NOT NULL) AND
582 (p_ou_query_rec.instance_id <> FND_API.G_MISS_NUM)) THEN
583 IF x_where_clause IS NULL THEN
584 x_where_clause := ' instance_id = :instance_id ';
585 ELSE
586 x_where_clause := x_where_clause||' AND '||' instance_id = :instance_id ';
587 END IF;
588 ELSIF (p_ou_query_rec.instance_id IS NULL) THEN
589 IF x_where_clause IS NULL THEN
590 x_where_clause := ' instance_id IS NULL ';
591 ELSE
592 x_where_clause := x_where_clause||' AND '||' instance_id IS NULL ';
593 END IF;
594 END IF;
595
596 IF ((p_ou_query_rec.operating_unit_id IS NOT NULL) AND
597 (p_ou_query_rec.operating_unit_id <> FND_API.G_MISS_NUM)) THEN
598 IF x_where_clause IS NULL THEN
599 x_where_clause := ' operating_unit_id = :operating_unit_id ';
600 ELSE
601 x_where_clause := x_where_clause||' AND '||' operating_unit_id = :operating_unit_id ';
602 END IF;
603 ELSIF (p_ou_query_rec.operating_unit_id IS NULL) THEN
604 IF x_where_clause IS NULL THEN
605 x_where_clause := ' operating_unit_id IS NULL ';
606 ELSE
607 x_where_clause := x_where_clause||' AND '||' operating_unit_id IS NULL ';
608 END IF;
609 END IF ;
610
611 IF ((p_ou_query_rec.relationship_type_code IS NOT NULL) AND
612 (p_ou_query_rec.relationship_type_code <> FND_API.G_MISS_CHAR)) THEN
613
614 IF x_where_clause IS NULL THEN
615 x_where_clause := ' relationship_type_code = :relationship_type_code ';
616 ELSE
617 x_where_clause := x_where_clause||' AND '||
618 ' relationship_type_code = :relationship_type_code ';
619 END IF;
620 ELSIF (p_ou_query_rec.relationship_type_code IS NULL) THEN
621
622 IF x_where_clause IS NULL THEN
623 x_where_clause := ' relationship_type_code IS NULL ';
624 ELSE
625 x_where_clause := x_where_clause||' AND '||' relationship_type_code IS NULL ';
626 END IF;
627 END IF;
628
629 END Gen_ou_Where_Clause;
630
631
632
633 /*-------------------------------------------------------*/
634 /* procedure name: create_organization_unit */
635 /* description : Creates new association between an */
636 /* organization unit and an item instance */
637 /* */
638 /*-------------------------------------------------------*/
639
640 PROCEDURE create_organization_unit
641 (
642 p_api_version IN NUMBER
643 ,p_commit IN VARCHAR2
644 ,p_init_msg_list IN VARCHAR2
645 ,p_validation_level IN NUMBER
646 ,p_org_unit_rec IN OUT NOCOPY csi_datastructures_pub.organization_units_rec
647 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
648 ,x_return_status OUT NOCOPY VARCHAR2
649 ,x_msg_count OUT NOCOPY NUMBER
650 ,x_msg_data OUT NOCOPY VARCHAR2
651 ,p_lookup_tbl IN OUT NOCOPY csi_organization_unit_pvt.lookup_tbl
652 ,p_ou_count_rec IN OUT NOCOPY csi_organization_unit_pvt.ou_count_rec
653 ,p_ou_id_tbl IN OUT NOCOPY csi_organization_unit_pvt.ou_id_tbl
654 ,p_called_from_grp IN VARCHAR2
655 )
656 IS
657
658 l_api_name CONSTANT VARCHAR2(30) := 'create_organization_unit';
659 l_api_version CONSTANT NUMBER := 1.0;
660 l_debug_level NUMBER;
661 l_instance_ou_id NUMBER := p_org_unit_rec.instance_ou_id;
662 l_csi_i_org_assign_h_id NUMBER;
663 l_dump_frequency_flag VARCHAR2(30);
664 l_msg_index NUMBER;
665 l_msg_count NUMBER;
666 l_record_found BOOLEAN := FALSE;
667 l_exists_flag VARCHAR2(1);
668 l_valid_flag VARCHAR2(1);
669 l_ou_lookup_tbl csi_organization_unit_pvt.lookup_tbl;
670 l_ou_count_rec csi_organization_unit_pvt.ou_count_rec;
671 l_ou_id_tbl csi_organization_unit_pvt.ou_id_tbl;
672
673 BEGIN
674
675 -- Standard Start of API savepoint
676 SAVEPOINT create_organization_unit;
677
678 -- Standard call to check for call compatibility.
679 IF NOT FND_API.Compatible_API_Call (l_api_version,
680 p_api_version,
681 l_api_name ,
682 g_pkg_name)
683 THEN
684 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
685 END IF;
686
687
688 -- Initialize message list if p_init_msg_list is set to TRUE.
689 IF FND_API.to_Boolean( p_init_msg_list ) THEN
690 FND_MSG_PUB.initialize;
691 END IF;
692
693
694 -- Initialize API return status to success
695 x_return_status := FND_API.G_RET_STS_SUCCESS;
696
697
698 -- Check the profile option debug_level for debug message reporting
699 l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
700
701 -- If debug_level = 1 then dump the procedure name
702 IF (l_debug_level > 0) THEN
703 csi_gen_utility_pvt.put_line( 'create_organization_unit');
704 END IF;
705
706
707 -- If the debug level = 2 then dump all the parameters values.
708 IF (l_debug_level > 1) THEN
709 csi_gen_utility_pvt.put_line( p_api_version ||'-'
710 || p_commit ||'-'
711 || p_init_msg_list ||'-'
712 || p_validation_level);
713 -- Dump txn_rec
714 csi_gen_utility_pvt.dump_txn_rec(p_txn_rec);
715 -- Dump org_unit_tbl
716 csi_gen_utility_pvt.dump_organization_unit_rec(p_org_unit_rec);
717
718 END IF;
719
720 -- Start API body
721 --
722 -- Initialize the Instance count
723 --
724 IF p_ou_count_rec.ou_count IS NULL OR
725 p_ou_count_rec.ou_count = FND_API.G_MISS_NUM THEN
726 p_ou_count_rec.ou_count := 0;
727 END IF;
728 --
729 IF p_ou_count_rec.lookup_count IS NULL OR
730 p_ou_count_rec.lookup_count = FND_API.G_MISS_NUM THEN
731 p_ou_count_rec.lookup_count := 0;
732 END IF;
733 --
734 -- Verify if instance id is ok
735 IF p_called_from_grp <> FND_API.G_TRUE THEN
736 IF NOT(csi_org_unit_vld_pvt.Is_valid_instance_id
737 (p_org_unit_rec.instance_id,
738 'INSERT')) THEN
739 RAISE FND_API.G_EXC_ERROR;
740 END IF;
741 END IF;
742 --
743 --validation for the operating unit id
744 l_valid_flag := 'Y';
745 l_exists_flag := 'N';
746 IF p_org_unit_rec.operating_unit_id IS NOT NULL AND
747 p_org_unit_rec.operating_unit_id <> FND_API.G_MISS_NUM THEN
748 IF p_ou_id_tbl.count > 0 then
749 FOR ou_count IN p_ou_id_tbl.FIRST .. p_ou_id_tbl.LAST
750 LOOP
751 IF p_ou_id_tbl(ou_count).ou_id = p_org_unit_rec.operating_unit_id
752 THEN
753 l_valid_flag := p_ou_id_tbl(ou_count).valid_flag;
754 l_exists_flag := 'Y';
755 EXIT;
756 END IF;
757 END LOOP;
758 --
759 IF l_valid_flag <> 'Y' THEN
760 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_OPERATING_UNIT');
761 FND_MESSAGE.SET_TOKEN('OPERATING_UNIT',p_org_unit_rec.operating_unit_id);
762 FND_MSG_PUB.Add;
763 RAISE fnd_api.g_exc_error;
764 END IF;
765 END IF;
766 --
767 IF l_exists_flag <> 'Y' THEN
768 p_ou_count_rec.ou_count := p_ou_count_rec.ou_count + 1;
769 p_ou_id_tbl(p_ou_count_rec.ou_count).ou_id := p_org_unit_rec.operating_unit_id;
770 IF NOT(csi_org_unit_vld_pvt.Is_valid_operating_unit_id
771 (p_org_unit_rec.operating_unit_id)) THEN
772 p_ou_id_tbl(p_ou_count_rec.ou_count).valid_flag := 'N';
773 RAISE fnd_api.g_exc_error;
774 ELSE
775 p_ou_id_tbl(p_ou_count_rec.ou_count).valid_flag := 'Y';
776 END IF;
777 END IF;
778 ELSE
779 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_OPERATING_UNIT');
780 FND_MESSAGE.SET_TOKEN('OPERATING_UNIT',p_org_unit_rec.operating_unit_id);
781 FND_MSG_PUB.Add;
782 RAISE fnd_api.g_exc_error;
783 END IF;
784 --
785 -- Check start effective date
786 IF p_called_from_grp <> FND_API.G_TRUE THEN
787 IF NOT(csi_org_unit_vld_pvt.Is_StartDate_Valid
788 (p_org_unit_rec.ACTIVE_START_DATE,
789 p_org_unit_rec.ACTIVE_END_DATE ,
790 p_org_unit_rec.INSTANCE_ID )) THEN
791 RAISE FND_API.G_EXC_ERROR;
792 END IF;
793 END IF;
794
795 -- Check end effective date
796 IF p_called_from_grp <> FND_API.G_TRUE THEN
797 IF p_org_unit_rec.ACTIVE_END_DATE is NOT NULL THEN
798 IF NOT(csi_org_unit_vld_pvt.Is_EndDate_Valid
799 (p_org_unit_rec.ACTIVE_START_DATE,
800 p_org_unit_rec.ACTIVE_END_DATE ,
801 p_org_unit_rec.INSTANCE_ID ,
802 p_org_unit_rec.INSTANCE_OU_ID,
803 p_txn_rec.TRANSACTION_ID)) THEN
804 RAISE FND_API.G_EXC_ERROR;
805 END IF;
806 END IF;
807 END IF;
808 --
809 --validation for the relationship type code
810 l_valid_flag := 'Y';
811 l_exists_flag := 'N';
812 IF ((p_org_unit_rec.relationship_type_code IS NOT NULL) AND
813 (p_org_unit_rec.relationship_type_code <> FND_API.G_MISS_CHAR)) THEN
814 IF p_lookup_tbl.count > 0 THEN
815 FOR lookup_count IN p_lookup_tbl.FIRST .. p_lookup_tbl.LAST
816 LOOP
817 IF p_lookup_tbl(lookup_count).lookup_code = p_org_unit_rec.relationship_type_code THEN
818 l_valid_flag := p_lookup_tbl(lookup_count).valid_flag;
819 l_exists_flag := 'Y';
820 EXIT;
821 END IF;
822 END LOOP;
823 --
824 if l_valid_flag <> 'Y' then
825 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_REL_TYPE_CODE');
826 FND_MESSAGE.SET_TOKEN('RELATIONSHIP_TYPE_CODE',p_org_unit_rec.relationship_type_code);
827 FND_MSG_PUB.Add;
828 RAISE fnd_api.g_exc_error;
829 end if;
830 End if;
831 --
832 IF l_exists_flag <> 'Y' THEN
833 p_ou_count_rec.lookup_count := p_ou_count_rec.lookup_count + 1;
834 p_lookup_tbl(p_ou_count_rec.lookup_count).lookup_code := p_org_unit_rec.relationship_type_code;
835 IF NOT(csi_org_unit_vld_pvt.Is_valid_rel_type_code
836 (p_org_unit_rec.relationship_type_code)) THEN
837 p_lookup_tbl(p_ou_count_rec.lookup_count).valid_flag := 'N';
838 RAISE FND_API.G_EXC_ERROR;
839 ELSE
840 p_lookup_tbl(p_ou_count_rec.lookup_count).valid_flag := 'Y';
841 END IF;
842 END IF;
843 END IF;
844 --
845 -- Added by sk for bug 2232880.
846 l_record_found := FALSE;
847 IF ( (p_called_from_grp <> FND_API.G_TRUE) AND
848 (p_org_unit_rec.instance_ou_id IS NULL OR
849 p_org_unit_rec.instance_ou_id = fnd_api.g_miss_num) )
850 THEN
851 BEGIN
852 SELECT instance_ou_id,
853 object_version_number
854 INTO p_org_unit_rec.instance_ou_id,
855 p_org_unit_rec.object_version_number
856 FROM csi_i_org_assignments
857 WHERE instance_id = p_org_unit_rec.instance_id
858 -- AND operating_unit_id = p_org_unit_rec.operating_unit_id -- Fix for Bug 3918188
859 AND relationship_type_code = p_org_unit_rec.relationship_type_code
860 AND active_end_date < SYSDATE
861 AND ROWNUM = 1 ;
862 l_record_found := TRUE;
863 EXCEPTION
864 WHEN OTHERS THEN
865 NULL;
866 END;
867 END IF;
868
869 IF l_record_found
870 THEN
871 IF p_org_unit_rec.active_end_date = fnd_api.g_miss_date
872 THEN
873 p_org_unit_rec.active_end_date := NULL;
874 END IF;
875 csi_organization_unit_pvt.update_organization_unit
876 ( p_api_version => p_api_version
877 ,p_commit => fnd_api.g_false
878 ,p_init_msg_list => p_init_msg_list
879 ,p_validation_level => p_validation_level
880 ,p_org_unit_rec => p_org_unit_rec
881 ,p_txn_rec => p_txn_rec
882 ,x_return_status => x_return_status
883 ,x_msg_count => x_msg_count
884 ,x_msg_data => x_msg_data
885 ,p_lookup_tbl => l_ou_lookup_tbl
886 ,p_ou_count_rec => l_ou_count_rec
887 ,p_ou_id_tbl => l_ou_id_tbl
888 );
889
890 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
891 l_msg_index := 1;
892 l_msg_count := x_msg_count;
893 WHILE l_msg_count > 0 LOOP
894 x_msg_data := FND_MSG_PUB.GET
895 ( l_msg_index,
896 FND_API.G_FALSE );
897
898 csi_gen_utility_pvt.put_line( ' Failed Pvt:update_organization_unit..');
899 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
900 l_msg_index := l_msg_index + 1;
901 l_msg_count := l_msg_count - 1;
902 END LOOP;
903 RAISE FND_API.G_EXC_ERROR;
904 END IF;
905 ELSE
906 -- If the instance_ou_id passed is null then generate from sequence
907 -- and check if the value exists . If exists then generate again from the sequence
908 -- till we get a value that does not exist
909 IF l_instance_ou_id IS NULL OR
910 l_instance_ou_id = FND_API.G_MISS_NUM THEN
911 l_instance_ou_id := csi_org_unit_vld_pvt.get_instance_ou_id;
912 p_org_unit_rec.instance_ou_id := l_instance_ou_id;
913 WHILE NOT(csi_org_unit_vld_pvt.Is_valid_instance_ou_id
914 (l_instance_ou_id))
915 LOOP
916 l_instance_ou_id := csi_org_unit_vld_pvt.get_instance_ou_id;
917 p_org_unit_rec.instance_ou_id := l_instance_ou_id;
918 END LOOP;
919 ELSE
920 -- Validate instance_ou_id
921 IF NOT(csi_org_unit_vld_pvt.Is_valid_instance_ou_id
922 (p_org_unit_rec.instance_ou_id)) THEN
923 RAISE FND_API.G_EXC_ERROR;
924 END IF;
925 END IF;
926 --
927 -- Validate alternate_pk_exists
928 IF p_called_from_grp <> FND_API.G_TRUE THEN
929 IF NOT (csi_org_unit_vld_pvt.Alternate_PK_exists
930 (p_org_unit_rec.instance_id
931 ,p_org_unit_rec.operating_unit_id
932 ,p_org_unit_rec.relationship_type_code
933 )) THEN
934 RAISE FND_API.G_EXC_ERROR;
935 END IF;
936 END IF;
937 --
938 -- End addition by sk for bug 2232880.
939 -- Create a row in csi_i_org_assignments table
940 IF p_called_from_grp <> FND_API.G_TRUE THEN
941 CSI_I_ORG_ASSIGNMENTS_PKG.Insert_Row(
942 l_instance_ou_id
943 ,p_org_unit_rec.instance_id
944 ,p_org_unit_rec.operating_unit_id
945 ,p_org_unit_rec.relationship_type_code
946 ,p_org_unit_rec.active_start_date
947 ,p_org_unit_rec.active_end_date
948 ,p_org_unit_rec.context
949 ,p_org_unit_rec.attribute1
950 ,p_org_unit_rec.attribute2
951 ,p_org_unit_rec.attribute3
952 ,p_org_unit_rec.attribute4
953 ,p_org_unit_rec.attribute5
954 ,p_org_unit_rec.attribute6
955 ,p_org_unit_rec.attribute7
956 ,p_org_unit_rec.attribute8
957 ,p_org_unit_rec.attribute9
958 ,p_org_unit_rec.attribute10
959 ,p_org_unit_rec.attribute11
960 ,p_org_unit_rec.attribute12
961 ,p_org_unit_rec.attribute13
962 ,p_org_unit_rec.attribute14
963 ,p_org_unit_rec.attribute15
964 ,fnd_global.user_id
965 ,sysdate
966 ,fnd_global.user_id
967 ,sysdate
968 ,fnd_global.user_id
969 ,1
970 );
971
972 -- IF CSI_Instance_parties_vld_pvt.Is_Instance_creation_complete( p_org_unit_rec.INSTANCE_ID ) THEN
973
974 -- Call create_transaction to create txn log
975 CSI_TRANSACTIONS_PVT.Create_transaction
976 (
977 p_api_version => p_api_version
978 ,p_commit => fnd_api.g_false
979 ,p_init_msg_list => p_init_msg_list
980 ,p_validation_level => p_validation_level
981 ,p_Success_If_Exists_Flag => 'Y'
982 ,p_transaction_rec => p_txn_rec
983 ,x_return_status => x_return_status
984 ,x_msg_count => x_msg_count
985 ,x_msg_data => x_msg_data
986 );
987
988 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
989 l_msg_index := 1;
990 l_msg_count := x_msg_count;
991 WHILE l_msg_count > 0 LOOP
992 x_msg_data := FND_MSG_PUB.GET
993 (l_msg_index,
994 FND_API.G_FALSE );
995
996 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
997 l_msg_index := l_msg_index + 1;
998 l_msg_count := l_msg_count - 1;
999 END LOOP;
1000 RAISE FND_API.G_EXC_ERROR;
1001 END IF;
1002
1003
1004 -- Get a unique org_assignment history number from the sequence
1005 l_csi_i_org_assign_h_id := csi_org_unit_vld_pvt.get_cis_i_org_assign_h_id;
1006 l_dump_frequency_flag := 'N';
1007 -- Create a row in csi_i_org_assignment history table
1008 CSI_I_ORG_ASSIGNMENTS_H_PKG.Insert_Row(
1009 l_csi_i_org_assign_h_id
1010 ,l_instance_ou_id
1011 ,p_txn_rec.TRANSACTION_ID
1012 ,NULL
1013 ,p_org_unit_rec.OPERATING_UNIT_ID
1014 ,NULL
1015 ,p_org_unit_rec.RELATIONSHIP_TYPE_CODE
1016 ,NULL
1017 ,NVL(p_org_unit_rec.ACTIVE_START_DATE, SYSDATE)
1018 ,NULL
1019 ,p_org_unit_rec.ACTIVE_END_DATE
1020 ,NULL
1021 ,p_org_unit_rec.context
1022 ,NULL
1023 ,p_org_unit_rec.ATTRIBUTE1
1024 ,NULL
1025 ,p_org_unit_rec.ATTRIBUTE2
1026 ,NULL
1027 ,p_org_unit_rec.ATTRIBUTE3
1028 ,NULL
1029 ,p_org_unit_rec.ATTRIBUTE4
1030 ,NULL
1031 ,p_org_unit_rec.ATTRIBUTE5
1032 ,NULL
1033 ,p_org_unit_rec.ATTRIBUTE6
1034 ,NULL
1035 ,p_org_unit_rec.ATTRIBUTE7
1036 ,NULL
1037 ,p_org_unit_rec.ATTRIBUTE8
1038 ,NULL
1039 ,p_org_unit_rec.ATTRIBUTE9
1040 ,NULL
1041 ,p_org_unit_rec.ATTRIBUTE10
1042 ,NULL
1043 ,p_org_unit_rec.ATTRIBUTE11
1044 ,NULL
1045 ,p_org_unit_rec.ATTRIBUTE12
1046 ,NULL
1047 ,p_org_unit_rec.ATTRIBUTE13
1048 ,NULL
1049 ,p_org_unit_rec.ATTRIBUTE14
1050 ,NULL
1051 ,p_org_unit_rec.ATTRIBUTE15
1052 ,l_dump_frequency_flag
1053 ,fnd_global.user_id
1054 ,sysdate
1055 ,fnd_global.user_id
1056 ,sysdate
1057 ,fnd_global.user_id
1058 ,1);
1059
1060 -- END IF;
1061 END IF; -- called from grp check
1062
1063 END IF; -- Added by sk for bug 2232880.
1064 -- Standard check of p_commit
1065 IF FND_API.To_Boolean( p_commit ) THEN
1066 COMMIT WORK;
1067 END IF;
1068 -- End of API body
1069 -- Standard call to get message count and if count is get message info.
1070 FND_MSG_PUB.Count_And_Get
1071 (p_count => x_msg_count ,
1072 p_data => x_msg_data
1073 );
1074
1075
1076 EXCEPTION
1077
1078 WHEN FND_API.G_EXC_ERROR THEN
1079 ROLLBACK TO create_organization_unit;
1080 x_return_status := FND_API.G_RET_STS_ERROR ;
1081 FND_MSG_PUB.Count_And_Get
1082 ( p_count => x_msg_count,
1083 p_data => x_msg_data
1084 );
1085
1086 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1087 ROLLBACK TO create_organization_unit;
1088 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1089 FND_MSG_PUB.Count_And_Get
1090 ( p_count => x_msg_count,
1091 p_data => x_msg_data
1092 );
1093
1094 WHEN OTHERS THEN
1095 ROLLBACK TO create_organization_unit;
1096 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1097
1098 IF FND_MSG_PUB.Check_Msg_Level
1099 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1100 THEN
1101 FND_MSG_PUB.Add_Exc_Msg
1102 ( g_pkg_name ,
1103 l_api_name
1104 );
1105 END IF;
1106
1107 FND_MSG_PUB.Count_And_Get
1108 ( p_count => x_msg_count,
1109 p_data => x_msg_data
1110 );
1111
1112 END create_organization_unit;
1113
1114
1115
1116 /*-------------------------------------------------------*/
1117 /* procedure name: update_organization_unit */
1118 /* description : Updates an existing instance-org */
1119 /* association */
1120 /* */
1121 /*-------------------------------------------------------*/
1122
1123 PROCEDURE update_organization_unit
1124 (
1125 p_api_version IN NUMBER
1126 ,p_commit IN VARCHAR2
1127 ,p_init_msg_list IN VARCHAR2
1128 ,p_validation_level IN NUMBER
1129 ,p_org_unit_rec IN csi_datastructures_pub.organization_units_rec
1130 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
1131 ,x_return_status OUT NOCOPY VARCHAR2
1132 ,x_msg_count OUT NOCOPY NUMBER
1133 ,x_msg_data OUT NOCOPY VARCHAR2
1134 ,p_lookup_tbl IN OUT NOCOPY csi_organization_unit_pvt.lookup_tbl
1135 ,p_ou_count_rec IN OUT NOCOPY csi_organization_unit_pvt.ou_count_rec
1136 ,p_ou_id_tbl IN OUT NOCOPY csi_organization_unit_pvt.ou_id_tbl
1137 )
1138
1139
1140 IS
1141 l_api_name CONSTANT VARCHAR2(30) := 'update_organization_unit';
1142 l_api_version CONSTANT NUMBER := 1.0;
1143 l_debug_level NUMBER;
1144 l_instance_ou_id NUMBER := p_org_unit_rec.instance_ou_id;
1145 l_start_date DATE;
1146 l_dump_frequency NUMBER;
1147 l_csi_i_org_assign_h_id NUMBER;
1148 l_org_unit_rec csi_datastructures_pub.organization_units_rec;
1149 l_temp_org_unit_rec csi_datastructures_pub.organization_units_rec;
1150 l_dump_frequency_flag VARCHAR2(30);
1151 l_msg_index NUMBER;
1152 l_msg_count NUMBER;
1153 l_exists_flag VARCHAR2(1);
1154 l_valid_flag VARCHAR2(1);
1155 l_org_units_hist_rec csi_datastructures_pub.org_units_history_rec;
1156 l_operating_unit_id NUMBER;
1157 l_rel_type_code VARCHAR2(30);
1158
1159 CURSOR org_hist_csr (p_org_hist_id NUMBER) IS
1160 SELECT instance_ou_history_id
1161 ,instance_ou_id
1162 ,transaction_id
1163 ,old_operating_unit_id
1164 ,new_operating_unit_id
1165 ,old_relationship_type_code
1166 ,new_relationship_type_code
1167 ,old_active_start_date
1168 ,new_active_start_date
1169 ,old_active_end_date
1170 ,new_active_end_date
1171 ,old_context
1172 ,new_context
1173 ,old_attribute1
1174 ,new_attribute1
1175 ,old_attribute2
1176 ,new_attribute2
1177 ,old_attribute3
1178 ,new_attribute3
1179 ,old_attribute4
1180 ,new_attribute4
1181 ,old_attribute5
1182 ,new_attribute5
1183 ,old_attribute6
1184 ,new_attribute6
1185 ,old_attribute7
1186 ,new_attribute7
1187 ,old_attribute8
1188 ,new_attribute8
1189 ,old_attribute9
1190 ,new_attribute9
1191 ,old_attribute10
1192 ,new_attribute10
1193 ,old_attribute11
1194 ,new_attribute11
1195 ,old_attribute12
1196 ,new_attribute12
1197 ,old_attribute13
1198 ,new_attribute13
1199 ,old_attribute14
1200 ,new_attribute14
1201 ,old_attribute15
1202 ,new_attribute15
1203 ,full_dump_flag
1204 ,object_version_number
1205 FROM csi_i_org_assignments_h
1206 WHERE csi_i_org_assignments_h.instance_ou_history_id = p_org_hist_id
1207 FOR UPDATE OF object_version_number ;
1208 l_org_hist_csr org_hist_csr%ROWTYPE;
1209 l_org_hist_id NUMBER;
1210
1211 BEGIN
1212 -- Standard Start of API savepoint
1213 SAVEPOINT update_organization_unit;
1214
1215 -- Standard call to check for call compatibility.
1216 IF NOT FND_API.Compatible_API_Call (l_api_version,
1217 p_api_version,
1218 l_api_name ,
1219 g_pkg_name)
1220 THEN
1221 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1222 END IF;
1223
1224 -- Initialize message list if p_init_msg_list is set to TRUE.
1225 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1226 FND_MSG_PUB.initialize;
1227 END IF;
1228
1229 -- Initialize API return status to success
1230 x_return_status := FND_API.G_RET_STS_SUCCESS;
1231
1232 -- Check the profile option debug_level for debug message reporting
1233 l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
1234
1235 -- If debug_level = 1 then dump the procedure name
1236 IF (l_debug_level > 0) THEN
1237 csi_gen_utility_pvt.put_line( 'update_organization_unit');
1238 END IF;
1239
1240 -- If the debug level = 2 then dump all the parameters values.
1241 IF (l_debug_level > 1) THEN
1242 csi_gen_utility_pvt.put_line(
1243 p_api_version ||'-'
1244 || p_commit ||'-'
1245 || p_init_msg_list ||'-'
1246 || p_validation_level);
1247 -- Dump org_unit_rec
1248 csi_gen_utility_pvt.dump_organization_unit_rec(p_org_unit_rec);
1249 -- Dump txn_rec
1250 csi_gen_utility_pvt.dump_txn_rec(p_txn_rec);
1251 END IF;
1252
1253 -- Start API body
1254 --
1255 -- Initialize the Instance count
1256 --
1257 IF p_ou_count_rec.ou_count IS NULL OR
1258 p_ou_count_rec.ou_count = FND_API.G_MISS_NUM THEN
1259 p_ou_count_rec.ou_count := 0;
1260 END IF;
1261 --
1262 IF p_ou_count_rec.lookup_count IS NULL OR
1263 p_ou_count_rec.lookup_count = FND_API.G_MISS_NUM THEN
1264 p_ou_count_rec.lookup_count := 0;
1265 END IF;
1266 --
1267 -- Validate instance_ou_id
1268 IF NOT(csi_org_unit_vld_pvt.Val_and_get_inst_ou_id
1269 (p_org_unit_rec.instance_ou_id,
1270 l_org_unit_rec)) THEN
1271 RAISE FND_API.G_EXC_ERROR;
1272 END IF;
1273
1274 -- Validate object_version_number
1275 IF NOT(csi_org_unit_vld_pvt.Is_valid_obj_ver_num
1276 (p_org_unit_rec.object_version_number
1277 ,l_org_unit_rec.object_version_number
1278 )) THEN
1279 RAISE FND_API.G_EXC_ERROR;
1280 END IF;
1281
1282 -- Validate if the instance is updatable
1283 IF NOT(csi_org_unit_vld_pvt.Is_Updatable(
1284 l_org_unit_rec.active_end_date,
1285 p_org_unit_rec.active_end_date)) THEN
1286 RAISE FND_API.G_EXC_ERROR;
1287 END IF;
1288
1289 -- Validate instance id for which the update is related to
1290 IF NOT(csi_org_unit_vld_pvt.Is_Valid_instance_id
1291 (l_org_unit_rec.instance_id,
1292 'UPDATE'
1293 )) THEN
1294 -- Check if it is an expire operation
1295 IF NOT(csi_org_unit_vld_pvt.Is_Expire_Op
1296 (p_org_unit_rec)) THEN
1297 RAISE FND_API.G_EXC_ERROR;
1298 END IF;
1299 END IF;
1300
1301 -- Validate instance id
1302 IF ( p_org_unit_rec.instance_id <> FND_API.G_MISS_NUM ) THEN
1303 IF NOT(csi_org_unit_vld_pvt.Val_inst_id_for_update
1304 (p_org_unit_rec.instance_id
1305 ,l_org_unit_rec.instance_id
1306 )) THEN
1307 RAISE FND_API.G_EXC_ERROR;
1308 END IF;
1309 END IF;
1310
1311 --
1312 --validation for the operating unit id
1313 l_valid_flag := 'Y';
1314 l_exists_flag := 'N';
1315 IF p_org_unit_rec.operating_unit_id IS NOT NULL AND
1316 p_org_unit_rec.operating_unit_id <> FND_API.G_MISS_NUM THEN
1317 IF p_ou_id_tbl.count > 0 THEN
1318 FOR ou_count in p_ou_id_tbl.FIRST .. p_ou_id_tbl.LAST
1319 LOOP
1320 IF p_ou_id_tbl(ou_count).ou_id = p_org_unit_rec.operating_unit_id
1321 THEN
1322 l_valid_flag := p_ou_id_tbl(ou_count).valid_flag;
1323 l_exists_flag := 'Y';
1324 EXIT;
1325 END IF;
1326 END LOOP;
1327 --
1328 IF l_valid_flag <> 'Y' THEN
1329 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_OPERATING_UNIT');
1330 FND_MESSAGE.SET_TOKEN('OPERATING_UNIT',p_org_unit_rec.operating_unit_id);
1331 FND_MSG_PUB.Add;
1332 RAISE fnd_api.g_exc_error;
1333 END IF;
1334 END IF;
1335 --
1336 IF l_exists_flag <> 'Y' THEN
1337 p_ou_count_rec.ou_count := p_ou_count_rec.ou_count + 1;
1338 p_ou_id_tbl(p_ou_count_rec.ou_count).ou_id := p_org_unit_rec.operating_unit_id;
1339 IF NOT(csi_org_unit_vld_pvt.Is_valid_operating_unit_id
1340 (p_org_unit_rec.operating_unit_id)) THEN
1341 p_ou_id_tbl(p_ou_count_rec.ou_count).valid_flag := 'N';
1342 RAISE fnd_api.g_exc_error;
1343 ELSE
1344 p_ou_id_tbl(p_ou_count_rec.ou_count).valid_flag := 'Y';
1345 END IF;
1346 END IF;
1347 END IF;
1348 --
1349
1350 --
1351 --validation for the relationship type code
1352 l_valid_flag := 'Y';
1353 l_exists_flag := 'N';
1354 IF ((p_org_unit_rec.relationship_type_code IS NOT NULL) AND
1355 (p_org_unit_rec.relationship_type_code <> FND_API.G_MISS_CHAR)) THEN
1356 IF p_lookup_tbl.count > 0 THEN
1357 FOR lookup_count in p_lookup_tbl.FIRST .. p_lookup_tbl.LAST
1358 LOOP
1359 IF p_lookup_tbl(lookup_count).lookup_code = p_org_unit_rec.relationship_type_code THEN
1360 l_valid_flag := p_lookup_tbl(lookup_count).valid_flag;
1361 l_exists_flag := 'Y';
1362 EXIT;
1363 END IF;
1364 END LOOP;
1365 --
1366 IF l_valid_flag <> 'Y' THEN
1367 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_REL_TYPE_CODE');
1368 FND_MESSAGE.SET_TOKEN('RELATIONSHIP_TYPE_CODE',p_org_unit_rec.relationship_type_code);
1369 FND_MSG_PUB.Add;
1370 RAISE fnd_api.g_exc_error;
1371 END IF;
1372 END IF;
1373 --
1374 IF l_exists_flag <> 'Y' THEN
1375 p_ou_count_rec.lookup_count := p_ou_count_rec.lookup_count + 1;
1376 p_lookup_tbl(p_ou_count_rec.lookup_count).lookup_code := p_org_unit_rec.relationship_type_code;
1377 IF NOT(csi_org_unit_vld_pvt.Is_valid_rel_type_code
1378 (p_org_unit_rec.relationship_type_code)) THEN
1379 p_lookup_tbl(p_ou_count_rec.lookup_count).valid_flag := 'N';
1380 RAISE FND_API.G_EXC_ERROR;
1381 ELSE
1382 p_lookup_tbl(p_ou_count_rec.lookup_count).valid_flag := 'Y';
1383 END IF;
1384 END IF;
1385 END IF;
1386 --
1387 -- Verify start effective date
1388 IF ( p_org_unit_rec.active_start_date <> FND_API.G_MISS_DATE) THEN
1389 IF (p_org_unit_rec.active_start_date <> l_org_unit_rec.active_start_date) THEN
1390 l_start_date := p_org_unit_rec.ACTIVE_START_DATE;
1391 IF NOT(csi_org_unit_vld_pvt.Is_StartDate_Valid
1392 (l_start_date,
1393 p_org_unit_rec.ACTIVE_END_DATE ,
1394 p_org_unit_rec.INSTANCE_ID )) THEN
1395 RAISE FND_API.G_EXC_ERROR;
1396 END IF;
1397
1398 END IF;
1399 END IF;
1400
1401 -- Verify end effective date
1402 IF ( p_org_unit_rec.active_end_date <> FND_API.G_MISS_DATE) THEN
1403 IF p_org_unit_rec.active_end_date is NOT NULL THEN
1404 IF g_expire_flag <> 'Y' THEN
1405 IF NOT(csi_org_unit_vld_pvt.Is_EndDate_Valid
1406 (p_org_unit_rec.ACTIVE_START_DATE,
1407 p_org_unit_rec.ACTIVE_END_DATE,
1408 p_org_unit_rec.INSTANCE_ID ,
1409 p_org_unit_rec.INSTANCE_OU_ID,
1410 p_txn_rec.TRANSACTION_ID)) THEN
1411 RAISE FND_API.G_EXC_ERROR;
1412 END IF;
1413 END IF;
1414 END IF;
1415 END IF;
1416 -- srramakr Bug 3918188
1417 -- Validate alternate_pk_exists
1418 IF p_org_unit_rec.operating_unit_id IS NULL OR
1419 p_org_unit_rec.operating_unit_id = FND_API.G_MISS_NUM THEN
1420 l_operating_unit_id := l_org_unit_rec.operating_unit_id;
1421 ELSE
1422 l_operating_unit_id := p_org_unit_rec.operating_unit_id;
1423 END IF;
1424 --
1425 IF p_org_unit_rec.relationship_type_code IS NULL OR
1426 p_org_unit_rec.relationship_type_code = FND_API.G_MISS_CHAR THEN
1427 l_rel_type_code := l_org_unit_rec.relationship_type_code;
1428 ELSE
1429 l_rel_type_code := p_org_unit_rec.relationship_type_code;
1430 END IF;
1431 --
1432 IF NOT (csi_org_unit_vld_pvt.Alternate_PK_exists
1433 (p_instance_id => l_org_unit_rec.instance_id
1434 ,p_operating_unit_id => l_operating_unit_id
1435 ,p_relationship_type_code => l_rel_type_code
1436 ,p_instance_ou_id => p_org_unit_rec.INSTANCE_OU_ID
1437 )) THEN
1438 RAISE FND_API.G_EXC_ERROR;
1439 END IF;
1440 -- End of 3918188
1441 -- Validate alternate_pk_exists
1442 /**** IF (p_org_unit_rec.operating_unit_id IS NULL) AND
1443 (p_org_unit_rec.relationship_type_code IS NULL) THEN
1444 IF NOT (csi_org_unit_vld_pvt.Alternate_PK_exists
1445 (l_org_unit_rec.instance_id
1446 ,l_org_unit_rec.operating_unit_id
1447 ,l_org_unit_rec.relationship_type_code
1448 )) THEN
1449 RAISE FND_API.G_EXC_ERROR;
1450 END IF;
1451 ELSIF (p_org_unit_rec.operating_unit_id IS NULL) AND
1452 (p_org_unit_rec.relationship_type_code IS NOT NULL) THEN
1453 IF NOT (csi_org_unit_vld_pvt.Alternate_PK_exists
1454 (l_org_unit_rec.instance_id
1455 ,l_org_unit_rec.operating_unit_id
1456 ,p_org_unit_rec.relationship_type_code
1457 )) THEN
1458 RAISE FND_API.G_EXC_ERROR;
1459 END IF;
1460 ELSIF (p_org_unit_rec.operating_unit_id IS NOT NULL) AND
1461 (p_org_unit_rec.relationship_type_code IS NULL) THEN
1462 IF NOT (csi_org_unit_vld_pvt.Alternate_PK_exists
1463 (l_org_unit_rec.instance_id
1464 ,p_org_unit_rec.operating_unit_id
1465 ,l_org_unit_rec.relationship_type_code
1466 )) THEN
1467 RAISE FND_API.G_EXC_ERROR;
1468 END IF;
1469 END IF;
1470 *****/
1471 -- Get the new object version number
1472 l_org_unit_rec.object_version_number :=
1473 csi_org_unit_vld_pvt.get_object_version_number(l_org_unit_rec.object_version_number);
1474
1475 CSI_I_ORG_ASSIGNMENTS_PKG.Update_Row(
1476 l_instance_ou_id
1477 ,p_org_unit_rec.instance_id
1478 ,p_org_unit_rec.operating_unit_id
1479 ,p_org_unit_rec.relationship_type_code
1480 ,p_org_unit_rec.active_start_date
1481 ,p_org_unit_rec.active_end_date
1482 ,p_org_unit_rec.context
1483 ,p_org_unit_rec.attribute1
1484 ,p_org_unit_rec.attribute2
1485 ,p_org_unit_rec.attribute3
1486 ,p_org_unit_rec.attribute4
1487 ,p_org_unit_rec.attribute5
1488 ,p_org_unit_rec.attribute6
1489 ,p_org_unit_rec.attribute7
1490 ,p_org_unit_rec.attribute8
1491 ,p_org_unit_rec.attribute9
1492 ,p_org_unit_rec.attribute10
1493 ,p_org_unit_rec.attribute11
1494 ,p_org_unit_rec.attribute12
1495 ,p_org_unit_rec.attribute13
1496 ,p_org_unit_rec.attribute14
1497 ,p_org_unit_rec.attribute15
1498 ,fnd_api.g_miss_num -- fnd_global.user_id
1499 ,fnd_api.g_miss_date
1500 ,fnd_global.user_id
1501 ,sysdate
1502 ,fnd_global.user_id
1503 ,l_org_unit_rec.object_version_number
1504 );
1505
1506 -- Call create_transaction to create txn log
1507 CSI_TRANSACTIONS_PVT.Create_transaction
1508 (
1509 p_api_version => p_api_version
1510 ,p_commit => fnd_api.g_false
1511 ,p_init_msg_list => p_init_msg_list
1512 ,p_validation_level => p_validation_level
1513 ,p_Success_If_Exists_Flag => 'Y'
1514 ,P_transaction_rec => p_txn_rec
1515 ,x_return_status => x_return_status
1516 ,x_msg_count => x_msg_count
1517 ,x_msg_data => x_msg_data
1518 );
1519
1520 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1521 l_msg_index := 1;
1522 l_msg_count := x_msg_count;
1523 WHILE l_msg_count > 0 LOOP
1524 x_msg_data := FND_MSG_PUB.GET
1525 (l_msg_index,
1526 FND_API.G_FALSE );
1527
1528 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
1529 l_msg_index := l_msg_index + 1;
1530 l_msg_count := l_msg_count - 1;
1531 END LOOP;
1532 RAISE FND_API.G_EXC_ERROR;
1533 END IF;
1534
1535 -- Get a unique org_assignment history number from the sequence
1536 l_csi_i_org_assign_h_id := csi_org_unit_vld_pvt.get_cis_i_org_assign_h_id;
1537
1538 -- Get full dump frequency from CSI_INSTALL_PARAMETERS
1539 l_dump_frequency := csi_org_unit_vld_pvt.get_full_dump_frequency;
1540 IF l_dump_frequency IS NULL THEN
1541 RAISE FND_API.G_EXC_ERROR;
1542 END IF;
1543 -- Grab the input record in a temporary record
1544 l_temp_org_unit_rec := p_org_unit_rec;
1545
1546 -- Start of modifications for Bug#2547034 on 09/20/02 - rtalluri
1547 BEGIN
1548 SELECT instance_ou_history_id
1549 INTO l_org_hist_id
1550 FROM csi_i_org_assignments_h h
1551 WHERE h.transaction_id = p_txn_rec.transaction_id
1552 AND h.instance_ou_id = p_org_unit_rec.instance_ou_id;
1553
1554 OPEN org_hist_csr(l_org_hist_id);
1555 FETCH org_hist_csr INTO l_org_hist_csr ;
1556 CLOSE org_hist_csr;
1557 IF l_org_hist_csr.full_dump_flag = 'Y'
1558 THEN
1559 csi_i_org_assignments_h_pkg.update_row(
1560 p_instance_ou_history_id => l_org_hist_id ,
1561 p_instance_ou_id => fnd_api.g_miss_num ,
1562 p_transaction_id => fnd_api.g_miss_num ,
1563 p_old_operating_unit_id => fnd_api.g_miss_num ,
1564 p_new_operating_unit_id => l_temp_org_unit_rec.operating_unit_id ,
1565 p_old_relationship_type_code => fnd_api.g_miss_char ,
1566 p_new_relationship_type_code => l_temp_org_unit_rec.relationship_type_code,
1567 p_old_active_start_date => fnd_api.g_miss_date ,
1568 p_new_active_start_date => l_temp_org_unit_rec.active_start_date ,
1569 p_old_active_end_date => fnd_api.g_miss_date ,
1570 p_new_active_end_date => l_temp_org_unit_rec.active_end_date ,
1571 p_old_context => fnd_api.g_miss_char ,
1572 p_new_context => l_temp_org_unit_rec.context ,
1573 p_old_attribute1 => fnd_api.g_miss_char ,
1574 p_new_attribute1 => l_temp_org_unit_rec.attribute1 ,
1575 p_old_attribute2 => fnd_api.g_miss_char ,
1576 p_new_attribute2 => l_temp_org_unit_rec.attribute2 ,
1577 p_old_attribute3 => fnd_api.g_miss_char ,
1578 p_new_attribute3 => l_temp_org_unit_rec.attribute3 ,
1579 p_old_attribute4 => fnd_api.g_miss_char ,
1580 p_new_attribute4 => l_temp_org_unit_rec.attribute4 ,
1581 p_old_attribute5 => fnd_api.g_miss_char ,
1582 p_new_attribute5 => l_temp_org_unit_rec.attribute5 ,
1583 p_old_attribute6 => fnd_api.g_miss_char ,
1584 p_new_attribute6 => l_temp_org_unit_rec.attribute6 ,
1585 p_old_attribute7 => fnd_api.g_miss_char ,
1586 p_new_attribute7 => l_temp_org_unit_rec.attribute7 ,
1587 p_old_attribute8 => fnd_api.g_miss_char ,
1588 p_new_attribute8 => l_temp_org_unit_rec.attribute8 ,
1589 p_old_attribute9 => fnd_api.g_miss_char ,
1590 p_new_attribute9 => l_temp_org_unit_rec.attribute9 ,
1591 p_old_attribute10 => fnd_api.g_miss_char ,
1592 p_new_attribute10 => l_temp_org_unit_rec.attribute10 ,
1593 p_old_attribute11 => fnd_api.g_miss_char ,
1594 p_new_attribute11 => l_temp_org_unit_rec.attribute11 ,
1595 p_old_attribute12 => fnd_api.g_miss_char ,
1596 p_new_attribute12 => l_temp_org_unit_rec.attribute12 ,
1597 p_old_attribute13 => fnd_api.g_miss_char ,
1598 p_new_attribute13 => l_temp_org_unit_rec.attribute13 ,
1599 p_old_attribute14 => fnd_api.g_miss_char ,
1600 p_new_attribute14 => l_temp_org_unit_rec.attribute14 ,
1601 p_old_attribute15 => fnd_api.g_miss_char ,
1602 p_new_attribute15 => l_temp_org_unit_rec.attribute15 ,
1603 p_full_dump_flag => fnd_api.g_miss_char ,
1604 p_created_by => fnd_api.g_miss_num, -- fnd_global.user_id ,
1605 p_creation_date => fnd_api.g_miss_date ,
1606 p_last_updated_by => fnd_global.user_id ,
1607 p_last_update_date => sysdate ,
1608 p_last_update_login => fnd_global.user_id ,
1609 p_object_version_number => fnd_api.g_miss_num );
1610
1611 ELSE
1612
1613 IF ( l_org_hist_csr.old_operating_unit_id IS NULL
1614 AND l_org_hist_csr.new_operating_unit_id IS NULL ) THEN
1615 IF ( l_temp_org_unit_rec.operating_unit_id = l_org_unit_rec.operating_unit_id )
1616 OR ( l_temp_org_unit_rec.operating_unit_id = fnd_api.g_miss_num ) THEN
1617 l_org_hist_csr.old_operating_unit_id := NULL;
1618 l_org_hist_csr.new_operating_unit_id := NULL;
1619 ELSE
1620 l_org_hist_csr.old_operating_unit_id := fnd_api.g_miss_num;
1621 l_org_hist_csr.new_operating_unit_id := l_temp_org_unit_rec.operating_unit_id;
1622 END IF;
1623 ELSE
1624 l_org_hist_csr.old_operating_unit_id := fnd_api.g_miss_num;
1625 l_org_hist_csr.new_operating_unit_id := l_temp_org_unit_rec.operating_unit_id;
1626 END IF;
1627 --
1628 IF ( l_org_hist_csr.old_relationship_type_code IS NULL
1629 AND l_org_hist_csr.new_relationship_type_code IS NULL ) THEN
1630 IF ( l_temp_org_unit_rec.relationship_type_code = l_org_unit_rec.relationship_type_code )
1631 OR ( l_temp_org_unit_rec.relationship_type_code = fnd_api.g_miss_char ) THEN
1632 l_org_hist_csr.old_relationship_type_code := NULL;
1633 l_org_hist_csr.new_relationship_type_code := NULL;
1634 ELSE
1635 l_org_hist_csr.old_relationship_type_code := fnd_api.g_miss_char;
1636 l_org_hist_csr.new_relationship_type_code := l_temp_org_unit_rec.relationship_type_code;
1637 END IF;
1638 ELSE
1639 l_org_hist_csr.old_relationship_type_code := fnd_api.g_miss_char;
1640 l_org_hist_csr.new_relationship_type_code := l_temp_org_unit_rec.relationship_type_code;
1641 END IF;
1642 --
1643 IF ( l_org_hist_csr.old_active_start_date IS NULL
1644 AND l_org_hist_csr.new_active_start_date IS NULL ) THEN
1645 IF ( l_temp_org_unit_rec.active_start_date = l_org_unit_rec.active_start_date )
1646 OR ( l_temp_org_unit_rec.active_start_date = fnd_api.g_miss_date ) THEN
1647 l_org_hist_csr.old_active_start_date := NULL;
1648 l_org_hist_csr.new_active_start_date := NULL;
1649 ELSE
1650 l_org_hist_csr.old_active_start_date := fnd_api.g_miss_date;
1651 l_org_hist_csr.new_active_start_date := l_temp_org_unit_rec.active_start_date;
1652 END IF;
1653 ELSE
1654 l_org_hist_csr.old_active_start_date := fnd_api.g_miss_date;
1655 l_org_hist_csr.new_active_start_date := l_temp_org_unit_rec.active_start_date;
1656 END IF;
1657 --
1658 IF ( l_org_hist_csr.old_active_end_date IS NULL
1659 AND l_org_hist_csr.new_active_end_date IS NULL ) THEN
1660 IF ( l_temp_org_unit_rec.active_end_date = l_org_unit_rec.active_end_date )
1661 OR ( l_temp_org_unit_rec.active_end_date = fnd_api.g_miss_date ) THEN
1662 l_org_hist_csr.old_active_end_date := NULL;
1663 l_org_hist_csr.new_active_end_date := NULL;
1664 ELSE
1665 l_org_hist_csr.old_active_end_date := fnd_api.g_miss_date;
1666 l_org_hist_csr.new_active_end_date := l_temp_org_unit_rec.active_end_date;
1667 END IF;
1668 ELSE
1669 l_org_hist_csr.old_active_end_date := fnd_api.g_miss_date;
1670 l_org_hist_csr.new_active_end_date := l_temp_org_unit_rec.active_end_date;
1671 END IF;
1672 --
1673 IF ( l_org_hist_csr.old_context IS NULL
1674 AND l_org_hist_csr.new_context IS NULL ) THEN
1675 IF ( l_temp_org_unit_rec.context = l_org_unit_rec.context )
1676 OR ( l_temp_org_unit_rec.context = fnd_api.g_miss_char ) THEN
1677 l_org_hist_csr.old_context := NULL;
1678 l_org_hist_csr.new_context := NULL;
1679 ELSE
1680 l_org_hist_csr.old_context := fnd_api.g_miss_char;
1681 l_org_hist_csr.new_context := l_temp_org_unit_rec.context;
1682 END IF;
1683 ELSE
1684 l_org_hist_csr.old_context := fnd_api.g_miss_char;
1685 l_org_hist_csr.new_context := l_temp_org_unit_rec.context;
1686 END IF;
1687 --
1688 IF ( l_org_hist_csr.old_attribute1 IS NULL
1689 AND l_org_hist_csr.new_attribute1 IS NULL ) THEN
1690 IF ( l_temp_org_unit_rec.attribute1 = l_org_unit_rec.attribute1 )
1691 OR ( l_temp_org_unit_rec.attribute1 = fnd_api.g_miss_char ) THEN
1692 l_org_hist_csr.old_attribute1 := NULL;
1693 l_org_hist_csr.new_attribute1 := NULL;
1694 ELSE
1695 l_org_hist_csr.old_attribute1 := fnd_api.g_miss_char;
1696 l_org_hist_csr.new_attribute1 := l_temp_org_unit_rec.attribute1;
1697 END IF;
1698 ELSE
1699 l_org_hist_csr.old_attribute1 := fnd_api.g_miss_char;
1700 l_org_hist_csr.new_attribute1 := l_temp_org_unit_rec.attribute1;
1701 END IF;
1702 --
1703 IF ( l_org_hist_csr.old_attribute2 IS NULL
1704 AND l_org_hist_csr.new_attribute2 IS NULL ) THEN
1705 IF ( l_temp_org_unit_rec.attribute2 = l_org_unit_rec.attribute2 )
1706 OR ( l_temp_org_unit_rec.attribute2 = fnd_api.g_miss_char ) THEN
1707 l_org_hist_csr.old_attribute2 := NULL;
1708 l_org_hist_csr.new_attribute2 := NULL;
1709 ELSE
1710 l_org_hist_csr.old_attribute2 := fnd_api.g_miss_char;
1711 l_org_hist_csr.new_attribute2 := l_temp_org_unit_rec.attribute2;
1712 END IF;
1713 ELSE
1714 l_org_hist_csr.old_attribute2 := fnd_api.g_miss_char;
1715 l_org_hist_csr.new_attribute2 := l_temp_org_unit_rec.attribute2;
1716 END IF;
1717 --
1718 IF ( l_org_hist_csr.old_attribute3 IS NULL
1719 AND l_org_hist_csr.new_attribute3 IS NULL ) THEN
1720 IF ( l_temp_org_unit_rec.attribute3 = l_org_unit_rec.attribute3 )
1721 OR ( l_temp_org_unit_rec.attribute3 = fnd_api.g_miss_char ) THEN
1722 l_org_hist_csr.old_attribute3 := NULL;
1723 l_org_hist_csr.new_attribute3 := NULL;
1724 ELSE
1725 l_org_hist_csr.old_attribute3 := fnd_api.g_miss_char;
1726 l_org_hist_csr.new_attribute3 := l_temp_org_unit_rec.attribute3;
1727 END IF;
1728 ELSE
1729 l_org_hist_csr.old_attribute3 := fnd_api.g_miss_char;
1730 l_org_hist_csr.new_attribute3 := l_temp_org_unit_rec.attribute3;
1731 END IF;
1732 --
1733 IF ( l_org_hist_csr.old_attribute4 IS NULL
1734 AND l_org_hist_csr.new_attribute4 IS NULL ) THEN
1735 IF ( l_temp_org_unit_rec.attribute4 = l_org_unit_rec.attribute4 )
1736 OR ( l_temp_org_unit_rec.attribute4 = fnd_api.g_miss_char ) THEN
1737 l_org_hist_csr.old_attribute4 := NULL;
1738 l_org_hist_csr.new_attribute4 := NULL;
1739 ELSE
1740 l_org_hist_csr.old_attribute4 := fnd_api.g_miss_char;
1741 l_org_hist_csr.new_attribute4 := l_temp_org_unit_rec.attribute4;
1742 END IF;
1743 ELSE
1744 l_org_hist_csr.old_attribute4 := fnd_api.g_miss_char;
1745 l_org_hist_csr.new_attribute4 := l_temp_org_unit_rec.attribute4;
1746 END IF;
1747 --
1748 IF ( l_org_hist_csr.old_attribute5 IS NULL
1749 AND l_org_hist_csr.new_attribute5 IS NULL ) THEN
1750 IF ( l_temp_org_unit_rec.attribute5 = l_org_unit_rec.attribute5 )
1751 OR ( l_temp_org_unit_rec.attribute5 = fnd_api.g_miss_char ) THEN
1752 l_org_hist_csr.old_attribute5 := NULL;
1753 l_org_hist_csr.new_attribute5 := NULL;
1754 ELSE
1755 l_org_hist_csr.old_attribute5 := fnd_api.g_miss_char;
1756 l_org_hist_csr.new_attribute5 := l_temp_org_unit_rec.attribute5;
1757 END IF;
1758 ELSE
1759 l_org_hist_csr.old_attribute5 := fnd_api.g_miss_char;
1760 l_org_hist_csr.new_attribute5 := l_temp_org_unit_rec.attribute5;
1761 END IF;
1762 --
1763 IF ( l_org_hist_csr.old_attribute6 IS NULL
1764 AND l_org_hist_csr.new_attribute6 IS NULL ) THEN
1765 IF ( l_temp_org_unit_rec.attribute6 = l_org_unit_rec.attribute6 )
1766 OR ( l_temp_org_unit_rec.attribute6 = fnd_api.g_miss_char ) THEN
1767 l_org_hist_csr.old_attribute6 := NULL;
1768 l_org_hist_csr.new_attribute6 := NULL;
1769 ELSE
1770 l_org_hist_csr.old_attribute6 := fnd_api.g_miss_char;
1771 l_org_hist_csr.new_attribute6 := l_temp_org_unit_rec.attribute6;
1772 END IF;
1773 ELSE
1774 l_org_hist_csr.old_attribute6 := fnd_api.g_miss_char;
1775 l_org_hist_csr.new_attribute6 := l_temp_org_unit_rec.attribute6;
1776 END IF;
1777 --
1778 IF ( l_org_hist_csr.old_attribute7 IS NULL
1779 AND l_org_hist_csr.new_attribute7 IS NULL ) THEN
1780 IF ( l_temp_org_unit_rec.attribute7 = l_org_unit_rec.attribute7 )
1781 OR ( l_temp_org_unit_rec.attribute7 = fnd_api.g_miss_char ) THEN
1782 l_org_hist_csr.old_attribute7 := NULL;
1783 l_org_hist_csr.new_attribute7 := NULL;
1784 ELSE
1785 l_org_hist_csr.old_attribute7 := fnd_api.g_miss_char;
1786 l_org_hist_csr.new_attribute7 := l_temp_org_unit_rec.attribute7;
1787 END IF;
1788 ELSE
1789 l_org_hist_csr.old_attribute7 := fnd_api.g_miss_char;
1790 l_org_hist_csr.new_attribute7 := l_temp_org_unit_rec.attribute7;
1791 END IF;
1792 --
1793 IF ( l_org_hist_csr.old_attribute8 IS NULL
1794 AND l_org_hist_csr.new_attribute8 IS NULL ) THEN
1795 IF ( l_temp_org_unit_rec.attribute8 = l_org_unit_rec.attribute8 )
1796 OR ( l_temp_org_unit_rec.attribute8 = fnd_api.g_miss_char ) THEN
1797 l_org_hist_csr.old_attribute8 := NULL;
1798 l_org_hist_csr.new_attribute8 := NULL;
1799 ELSE
1800 l_org_hist_csr.old_attribute8 := fnd_api.g_miss_char;
1801 l_org_hist_csr.new_attribute8 := l_temp_org_unit_rec.attribute8;
1802 END IF;
1803 ELSE
1804 l_org_hist_csr.old_attribute8 := fnd_api.g_miss_char;
1805 l_org_hist_csr.new_attribute8 := l_temp_org_unit_rec.attribute8;
1806 END IF;
1807 --
1808 IF ( l_org_hist_csr.old_attribute9 IS NULL
1809 AND l_org_hist_csr.new_attribute9 IS NULL ) THEN
1810 IF ( l_temp_org_unit_rec.attribute9 = l_org_unit_rec.attribute9 )
1811 OR ( l_temp_org_unit_rec.attribute9 = fnd_api.g_miss_char ) THEN
1812 l_org_hist_csr.old_attribute9 := NULL;
1813 l_org_hist_csr.new_attribute9 := NULL;
1814 ELSE
1815 l_org_hist_csr.old_attribute9 := fnd_api.g_miss_char;
1816 l_org_hist_csr.new_attribute9 := l_temp_org_unit_rec.attribute9;
1817 END IF;
1818 ELSE
1819 l_org_hist_csr.old_attribute9 := fnd_api.g_miss_char;
1820 l_org_hist_csr.new_attribute9 := l_temp_org_unit_rec.attribute9;
1821 END IF;
1822 --
1823 IF ( l_org_hist_csr.old_attribute10 IS NULL
1824 AND l_org_hist_csr.new_attribute10 IS NULL ) THEN
1825 IF ( l_temp_org_unit_rec.attribute10 = l_org_unit_rec.attribute10 )
1826 OR ( l_temp_org_unit_rec.attribute10 = fnd_api.g_miss_char ) THEN
1827 l_org_hist_csr.old_attribute10 := NULL;
1828 l_org_hist_csr.new_attribute10 := NULL;
1829 ELSE
1830 l_org_hist_csr.old_attribute10 := fnd_api.g_miss_char;
1831 l_org_hist_csr.new_attribute10 := l_temp_org_unit_rec.attribute10;
1832 END IF;
1833 ELSE
1834 l_org_hist_csr.old_attribute10 := fnd_api.g_miss_char;
1835 l_org_hist_csr.new_attribute10 := l_temp_org_unit_rec.attribute10;
1836 END IF;
1837 --
1838 IF ( l_org_hist_csr.old_attribute11 IS NULL
1839 AND l_org_hist_csr.new_attribute11 IS NULL ) THEN
1840 IF ( l_temp_org_unit_rec.attribute11 = l_org_unit_rec.attribute11 )
1841 OR ( l_temp_org_unit_rec.attribute11 = fnd_api.g_miss_char ) THEN
1842 l_org_hist_csr.old_attribute11 := NULL;
1843 l_org_hist_csr.new_attribute11 := NULL;
1844 ELSE
1845 l_org_hist_csr.old_attribute11 := fnd_api.g_miss_char;
1846 l_org_hist_csr.new_attribute11 := l_temp_org_unit_rec.attribute11;
1847 END IF;
1848 ELSE
1849 l_org_hist_csr.old_attribute11 := fnd_api.g_miss_char;
1850 l_org_hist_csr.new_attribute11 := l_temp_org_unit_rec.attribute11;
1851 END IF;
1852 --
1853 IF ( l_org_hist_csr.old_attribute12 IS NULL
1854 AND l_org_hist_csr.new_attribute12 IS NULL ) THEN
1855 IF ( l_temp_org_unit_rec.attribute12 = l_org_unit_rec.attribute12 )
1856 OR ( l_temp_org_unit_rec.attribute12 = fnd_api.g_miss_char ) THEN
1857 l_org_hist_csr.old_attribute12 := NULL;
1858 l_org_hist_csr.new_attribute12 := NULL;
1859 ELSE
1860 l_org_hist_csr.old_attribute12 := fnd_api.g_miss_char;
1861 l_org_hist_csr.new_attribute12 := l_temp_org_unit_rec.attribute12;
1862 END IF;
1863 ELSE
1864 l_org_hist_csr.old_attribute12 := fnd_api.g_miss_char;
1865 l_org_hist_csr.new_attribute12 := l_temp_org_unit_rec.attribute12;
1866 END IF;
1867 --
1868 IF ( l_org_hist_csr.old_attribute13 IS NULL
1869 AND l_org_hist_csr.new_attribute13 IS NULL ) THEN
1870 IF ( l_temp_org_unit_rec.attribute13 = l_org_unit_rec.attribute13 )
1871 OR ( l_temp_org_unit_rec.attribute13 = fnd_api.g_miss_char ) THEN
1872 l_org_hist_csr.old_attribute13 := NULL;
1873 l_org_hist_csr.new_attribute13 := NULL;
1874 ELSE
1875 l_org_hist_csr.old_attribute13 := fnd_api.g_miss_char;
1876 l_org_hist_csr.new_attribute13 := l_temp_org_unit_rec.attribute13;
1877 END IF;
1878 ELSE
1879 l_org_hist_csr.old_attribute13 := fnd_api.g_miss_char;
1880 l_org_hist_csr.new_attribute13 := l_temp_org_unit_rec.attribute13;
1881 END IF;
1882 --
1883 IF ( l_org_hist_csr.old_attribute14 IS NULL
1884 AND l_org_hist_csr.new_attribute14 IS NULL ) THEN
1885 IF ( l_temp_org_unit_rec.attribute14 = l_org_unit_rec.attribute14 )
1886 OR ( l_temp_org_unit_rec.attribute14 = fnd_api.g_miss_char ) THEN
1887 l_org_hist_csr.old_attribute14 := NULL;
1888 l_org_hist_csr.new_attribute14 := NULL;
1889 ELSE
1890 l_org_hist_csr.old_attribute14 := fnd_api.g_miss_char;
1891 l_org_hist_csr.new_attribute14 := l_temp_org_unit_rec.attribute14;
1892 END IF;
1893 ELSE
1894 l_org_hist_csr.old_attribute14 := fnd_api.g_miss_char;
1895 l_org_hist_csr.new_attribute14 := l_temp_org_unit_rec.attribute14;
1896 END IF;
1897 --
1898 IF ( l_org_hist_csr.old_attribute15 IS NULL
1899 AND l_org_hist_csr.new_attribute15 IS NULL ) THEN
1900 IF ( l_temp_org_unit_rec.attribute15 = l_org_unit_rec.attribute15 )
1901 OR ( l_temp_org_unit_rec.attribute15 = fnd_api.g_miss_char ) THEN
1902 l_org_hist_csr.old_attribute15 := NULL;
1903 l_org_hist_csr.new_attribute15 := NULL;
1904 ELSE
1905 l_org_hist_csr.old_attribute15 := fnd_api.g_miss_char;
1906 l_org_hist_csr.new_attribute15 := l_temp_org_unit_rec.attribute15;
1907 END IF;
1908 ELSE
1909 l_org_hist_csr.old_attribute15 := fnd_api.g_miss_char;
1910 l_org_hist_csr.new_attribute15 := l_temp_org_unit_rec.attribute15;
1911 END IF;
1912 --
1913
1914 csi_i_org_assignments_h_pkg.update_row(
1915 p_instance_ou_history_id => l_org_hist_id ,
1916 p_instance_ou_id => fnd_api.g_miss_num ,
1917 p_transaction_id => fnd_api.g_miss_num ,
1918 p_old_operating_unit_id => l_org_hist_csr.old_operating_unit_id ,
1919 p_new_operating_unit_id => l_org_hist_csr.new_operating_unit_id ,
1920 p_old_relationship_type_code => l_org_hist_csr.old_relationship_type_code ,
1921 p_new_relationship_type_code => l_org_hist_csr.new_relationship_type_code,
1922 p_old_active_start_date => l_org_hist_csr.old_active_start_date ,
1923 p_new_active_start_date => l_org_hist_csr.new_active_start_date ,
1924 p_old_active_end_date => l_org_hist_csr.old_active_end_date ,
1925 p_new_active_end_date => l_org_hist_csr.new_active_end_date ,
1926 p_old_context => l_org_hist_csr.old_context ,
1927 p_new_context => l_org_hist_csr.new_context ,
1928 p_old_attribute1 => l_org_hist_csr.old_attribute1 ,
1929 p_new_attribute1 => l_org_hist_csr.new_attribute1 ,
1930 p_old_attribute2 => l_org_hist_csr.old_attribute2 ,
1931 p_new_attribute2 => l_org_hist_csr.new_attribute2 ,
1932 p_old_attribute3 => l_org_hist_csr.old_attribute3 ,
1933 p_new_attribute3 => l_org_hist_csr.new_attribute3 ,
1934 p_old_attribute4 => l_org_hist_csr.old_attribute4 ,
1935 p_new_attribute4 => l_org_hist_csr.new_attribute4 ,
1936 p_old_attribute5 => l_org_hist_csr.old_attribute5 ,
1937 p_new_attribute5 => l_org_hist_csr.new_attribute5 ,
1938 p_old_attribute6 => l_org_hist_csr.old_attribute6 ,
1939 p_new_attribute6 => l_org_hist_csr.new_attribute6 ,
1940 p_old_attribute7 => l_org_hist_csr.old_attribute7 ,
1941 p_new_attribute7 => l_org_hist_csr.new_attribute7 ,
1942 p_old_attribute8 => l_org_hist_csr.old_attribute8 ,
1943 p_new_attribute8 => l_org_hist_csr.new_attribute8 ,
1944 p_old_attribute9 => l_org_hist_csr.old_attribute9 ,
1945 p_new_attribute9 => l_org_hist_csr.new_attribute9 ,
1946 p_old_attribute10 => l_org_hist_csr.old_attribute10 ,
1947 p_new_attribute10 => l_org_hist_csr.new_attribute10 ,
1948 p_old_attribute11 => l_org_hist_csr.old_attribute11 ,
1949 p_new_attribute11 => l_org_hist_csr.new_attribute11 ,
1950 p_old_attribute12 => l_org_hist_csr.old_attribute12 ,
1951 p_new_attribute12 => l_org_hist_csr.new_attribute12 ,
1952 p_old_attribute13 => l_org_hist_csr.old_attribute13 ,
1953 p_new_attribute13 => l_org_hist_csr.new_attribute13 ,
1954 p_old_attribute14 => l_org_hist_csr.old_attribute14 ,
1955 p_new_attribute14 => l_org_hist_csr.new_attribute14 ,
1956 p_old_attribute15 => l_org_hist_csr.old_attribute15 ,
1957 p_new_attribute15 => l_org_hist_csr.new_attribute15 ,
1958 p_full_dump_flag => fnd_api.g_miss_char ,
1959 p_created_by => fnd_api.g_miss_num ,
1960 p_creation_date => fnd_api.g_miss_date ,
1961 p_last_updated_by => fnd_global.user_id ,
1962 p_last_update_date => sysdate ,
1963 p_last_update_login => fnd_global.user_id ,
1964 p_object_version_number => fnd_api.g_miss_num );
1965 END IF;
1966 EXCEPTION
1967 WHEN NO_DATA_FOUND THEN
1968
1969 IF (mod(l_org_unit_rec.object_version_number, l_dump_frequency) = 0) THEN
1970
1971 l_dump_frequency_flag := 'Y';
1972 -- Grab the input record in a temporary record
1973 l_temp_org_unit_rec := p_org_unit_rec;
1974 IF (p_org_unit_rec.OPERATING_UNIT_ID = FND_API.G_MISS_NUM) THEN
1975 l_temp_org_unit_rec.OPERATING_UNIT_ID := l_org_unit_rec.OPERATING_UNIT_ID;
1976 END IF;
1977
1978 IF (p_org_unit_rec.RELATIONSHIP_TYPE_CODE = FND_API.G_MISS_CHAR) THEN
1979 l_temp_org_unit_rec.RELATIONSHIP_TYPE_CODE := l_org_unit_rec.RELATIONSHIP_TYPE_CODE;
1980 END IF;
1981
1982 IF (p_org_unit_rec.ACTIVE_START_DATE = FND_API.G_MISS_DATE) THEN
1983 l_temp_org_unit_rec.ACTIVE_START_DATE := l_org_unit_rec.ACTIVE_START_DATE;
1984 END IF;
1985
1986 IF (p_org_unit_rec.ACTIVE_END_DATE = FND_API.G_MISS_DATE) THEN
1987 l_temp_org_unit_rec.ACTIVE_END_DATE := l_org_unit_rec.ACTIVE_END_DATE;
1988 END IF;
1989
1990 IF (p_org_unit_rec.CONTEXT = FND_API.G_MISS_CHAR) THEN
1991 l_temp_org_unit_rec.CONTEXT := l_org_unit_rec.CONTEXT;
1992 END IF;
1993
1994 IF (p_org_unit_rec.ATTRIBUTE1 = FND_API.G_MISS_CHAR) THEN
1995 l_temp_org_unit_rec.ATTRIBUTE1 := l_org_unit_rec.ATTRIBUTE1;
1996 END IF;
1997
1998 IF (p_org_unit_rec.ATTRIBUTE2 = FND_API.G_MISS_CHAR) THEN
1999 l_temp_org_unit_rec.ATTRIBUTE2 := l_org_unit_rec.ATTRIBUTE2;
2000 END IF;
2001
2002 IF (p_org_unit_rec.ATTRIBUTE3 = FND_API.G_MISS_CHAR) THEN
2003 l_temp_org_unit_rec.ATTRIBUTE3 := l_org_unit_rec.ATTRIBUTE3;
2004 END IF;
2005
2006 IF (p_org_unit_rec.ATTRIBUTE4 = FND_API.G_MISS_CHAR) THEN
2007 l_temp_org_unit_rec.ATTRIBUTE4 := l_org_unit_rec.ATTRIBUTE4;
2008 END IF;
2009
2010 IF (p_org_unit_rec.ATTRIBUTE5 = FND_API.G_MISS_CHAR) THEN
2011 l_temp_org_unit_rec.ATTRIBUTE5 := l_org_unit_rec.ATTRIBUTE5;
2012 END IF;
2013
2014 IF (p_org_unit_rec.ATTRIBUTE6 = FND_API.G_MISS_CHAR) THEN
2015 l_temp_org_unit_rec.ATTRIBUTE6 := l_org_unit_rec.ATTRIBUTE6;
2016 END IF;
2017
2018 IF (p_org_unit_rec.ATTRIBUTE7 = FND_API.G_MISS_CHAR) THEN
2019 l_temp_org_unit_rec.ATTRIBUTE7 := l_org_unit_rec.ATTRIBUTE7;
2020 END IF;
2021
2022 IF (p_org_unit_rec.ATTRIBUTE8 = FND_API.G_MISS_CHAR) THEN
2023 l_temp_org_unit_rec.ATTRIBUTE8 := l_org_unit_rec.ATTRIBUTE8;
2024 END IF;
2025
2026 IF (p_org_unit_rec.ATTRIBUTE9 = FND_API.G_MISS_CHAR) THEN
2027 l_temp_org_unit_rec.ATTRIBUTE9 := l_org_unit_rec.ATTRIBUTE9;
2028 END IF;
2029
2030 IF (p_org_unit_rec.ATTRIBUTE10 = FND_API.G_MISS_CHAR) THEN
2031 l_temp_org_unit_rec.ATTRIBUTE10 := l_org_unit_rec.ATTRIBUTE10;
2032 END IF;
2033
2034 IF (p_org_unit_rec.ATTRIBUTE11 = FND_API.G_MISS_CHAR) THEN
2035 l_temp_org_unit_rec.ATTRIBUTE11 := l_org_unit_rec.ATTRIBUTE11;
2036 END IF;
2037
2038 IF (p_org_unit_rec.ATTRIBUTE12 = FND_API.G_MISS_CHAR) THEN
2039 l_temp_org_unit_rec.ATTRIBUTE12 := l_org_unit_rec.ATTRIBUTE12;
2040 END IF;
2041
2042 IF (p_org_unit_rec.ATTRIBUTE13 = FND_API.G_MISS_CHAR) THEN
2043 l_temp_org_unit_rec.ATTRIBUTE13 := l_org_unit_rec.ATTRIBUTE13;
2044 END IF;
2045
2046 IF (p_org_unit_rec.ATTRIBUTE14 = FND_API.G_MISS_CHAR) THEN
2047 l_temp_org_unit_rec.ATTRIBUTE14 := l_org_unit_rec.ATTRIBUTE14;
2048 END IF;
2049
2050 IF (p_org_unit_rec.ATTRIBUTE15 = FND_API.G_MISS_CHAR) THEN
2051 l_temp_org_unit_rec.ATTRIBUTE15 := l_org_unit_rec.ATTRIBUTE15;
2052 END IF;
2053
2054 -- Create a row in csi_i_org_assignment history table
2055 CSI_I_ORG_ASSIGNMENTS_H_PKG.Insert_Row(
2056 l_csi_i_org_assign_h_id
2057 ,l_instance_ou_id
2058 ,p_txn_rec.TRANSACTION_ID
2059 ,l_org_unit_rec.OPERATING_UNIT_ID
2060 ,l_temp_org_unit_rec.OPERATING_UNIT_ID
2061 ,l_org_unit_rec.RELATIONSHIP_TYPE_CODE
2062 ,l_temp_org_unit_rec.RELATIONSHIP_TYPE_CODE
2063 ,l_org_unit_rec.ACTIVE_START_DATE
2064 ,l_temp_org_unit_rec.ACTIVE_START_DATE
2065 ,l_org_unit_rec.ACTIVE_END_DATE
2066 ,l_temp_org_unit_rec.ACTIVE_END_DATE
2067 ,l_org_unit_rec.context
2068 ,l_temp_org_unit_rec.context
2069 ,l_org_unit_rec.ATTRIBUTE1
2070 ,l_temp_org_unit_rec.ATTRIBUTE1
2071 ,l_org_unit_rec.ATTRIBUTE2
2072 ,l_temp_org_unit_rec.ATTRIBUTE2
2073 ,l_org_unit_rec.ATTRIBUTE3
2074 ,l_temp_org_unit_rec.ATTRIBUTE3
2075 ,l_org_unit_rec.ATTRIBUTE4
2076 ,l_temp_org_unit_rec.ATTRIBUTE4
2077 ,l_org_unit_rec.ATTRIBUTE5
2078 ,l_temp_org_unit_rec.ATTRIBUTE5
2079 ,l_org_unit_rec.ATTRIBUTE6
2080 ,l_temp_org_unit_rec.ATTRIBUTE6
2081 ,l_org_unit_rec.ATTRIBUTE7
2082 ,l_temp_org_unit_rec.ATTRIBUTE7
2083 ,l_org_unit_rec.ATTRIBUTE8
2084 ,l_temp_org_unit_rec.ATTRIBUTE8
2085 ,l_org_unit_rec.ATTRIBUTE9
2086 ,l_temp_org_unit_rec.ATTRIBUTE9
2087 ,l_org_unit_rec.ATTRIBUTE10
2088 ,l_temp_org_unit_rec.ATTRIBUTE10
2089 ,l_org_unit_rec.ATTRIBUTE11
2090 ,l_temp_org_unit_rec.ATTRIBUTE11
2091 ,l_org_unit_rec.ATTRIBUTE12
2092 ,l_temp_org_unit_rec.ATTRIBUTE12
2093 ,l_org_unit_rec.ATTRIBUTE13
2094 ,l_temp_org_unit_rec.ATTRIBUTE13
2095 ,l_org_unit_rec.ATTRIBUTE14
2096 ,l_temp_org_unit_rec.ATTRIBUTE14
2097 ,l_org_unit_rec.ATTRIBUTE15
2098 ,l_temp_org_unit_rec.ATTRIBUTE15
2099 ,l_dump_frequency_flag
2100 ,fnd_global.user_id
2101 ,sysdate
2102 ,fnd_global.user_id
2103 ,sysdate
2104 ,fnd_global.user_id
2105 ,1
2106 );
2107
2108 ELSE
2109
2110 l_dump_frequency_flag := 'N';
2111
2112 IF (p_org_unit_rec.operating_unit_id = fnd_api.g_miss_num) OR
2113 NVL(p_org_unit_rec.operating_unit_id, fnd_api.g_miss_num) = NVL(l_org_unit_rec.operating_unit_id, fnd_api.g_miss_num) THEN
2114 l_org_units_hist_rec.old_operating_unit_id := NULL;
2115 l_org_units_hist_rec.new_operating_unit_id := NULL;
2116 ELSIF
2117 NVL(l_org_unit_rec.operating_unit_id,fnd_api.g_miss_num) <> NVL(p_org_unit_rec.operating_unit_id,fnd_api.g_miss_num) THEN
2118 l_org_units_hist_rec.old_operating_unit_id := l_org_unit_rec.operating_unit_id ;
2119 l_org_units_hist_rec.new_operating_unit_id := p_org_unit_rec.operating_unit_id ;
2120 END IF;
2121 --
2122 IF (p_org_unit_rec.relationship_type_code = fnd_api.g_miss_char) OR
2123 NVL(p_org_unit_rec.relationship_type_code, fnd_api.g_miss_char) = NVL(l_org_unit_rec.relationship_type_code, fnd_api.g_miss_char) THEN
2124 l_org_units_hist_rec.old_relationship_type_code := NULL;
2125 l_org_units_hist_rec.new_relationship_type_code := NULL;
2126 ELSIF
2127 NVL(l_org_unit_rec.relationship_type_code,fnd_api.g_miss_char) <> NVL(p_org_unit_rec.relationship_type_code,fnd_api.g_miss_char) THEN
2128 l_org_units_hist_rec.old_relationship_type_code := l_org_unit_rec.relationship_type_code ;
2129 l_org_units_hist_rec.new_relationship_type_code := p_org_unit_rec.relationship_type_code ;
2130 END IF;
2131 --
2132 IF (p_org_unit_rec.active_start_date = fnd_api.g_miss_date) OR
2133 NVL(p_org_unit_rec.active_start_date, fnd_api.g_miss_date) = NVL(l_org_unit_rec.active_start_date, fnd_api.g_miss_date) THEN
2134 l_org_units_hist_rec.old_active_start_date := NULL;
2135 l_org_units_hist_rec.new_active_start_date := NULL;
2136 ELSIF
2137 NVL(l_org_unit_rec.active_start_date,fnd_api.g_miss_date) <> NVL(p_org_unit_rec.active_start_date,fnd_api.g_miss_date) THEN
2138 l_org_units_hist_rec.old_active_start_date := l_org_unit_rec.active_start_date ;
2139 l_org_units_hist_rec.new_active_start_date := p_org_unit_rec.active_start_date ;
2140 END IF;
2141 --
2142 IF (p_org_unit_rec.active_end_date = fnd_api.g_miss_date) OR
2143 NVL(p_org_unit_rec.active_end_date, fnd_api.g_miss_date) = NVL(l_org_unit_rec.active_end_date, fnd_api.g_miss_date) THEN
2144 l_org_units_hist_rec.old_active_end_date := NULL;
2145 l_org_units_hist_rec.new_active_end_date := NULL;
2146 ELSIF
2147 NVL(l_org_unit_rec.active_end_date,fnd_api.g_miss_date) <> NVL(p_org_unit_rec.active_end_date,fnd_api.g_miss_date) THEN
2148 l_org_units_hist_rec.old_active_end_date := l_org_unit_rec.active_end_date ;
2149 l_org_units_hist_rec.new_active_end_date := p_org_unit_rec.active_end_date ;
2150 END IF;
2151 --
2152 IF (p_org_unit_rec.context = fnd_api.g_miss_char) OR
2153 NVL(p_org_unit_rec.context, fnd_api.g_miss_char) = NVL(l_org_unit_rec.context, fnd_api.g_miss_char) THEN
2154 l_org_units_hist_rec.old_context := NULL;
2155 l_org_units_hist_rec.new_context := NULL;
2156 ELSIF
2157 NVL(l_org_unit_rec.context,fnd_api.g_miss_char) <> NVL(p_org_unit_rec.context,fnd_api.g_miss_char) THEN
2158 l_org_units_hist_rec.old_context := l_org_unit_rec.context ;
2159 l_org_units_hist_rec.new_context := p_org_unit_rec.context ;
2160 END IF;
2161 --
2162 IF (p_org_unit_rec.attribute1 = fnd_api.g_miss_char) OR
2163 NVL(p_org_unit_rec.attribute1, fnd_api.g_miss_char) = NVL(l_org_unit_rec.attribute1, fnd_api.g_miss_char) THEN
2164 l_org_units_hist_rec.old_attribute1 := NULL;
2165 l_org_units_hist_rec.new_attribute1 := NULL;
2166 ELSIF
2167 NVL(l_org_unit_rec.attribute1,fnd_api.g_miss_char) <> NVL(p_org_unit_rec.attribute1,fnd_api.g_miss_char) THEN
2168 l_org_units_hist_rec.old_attribute1 := l_org_unit_rec.attribute1 ;
2169 l_org_units_hist_rec.new_attribute1 := p_org_unit_rec.attribute1 ;
2170 END IF;
2171 --
2172 IF (p_org_unit_rec.attribute2 = fnd_api.g_miss_char) OR
2173 NVL(p_org_unit_rec.attribute2, fnd_api.g_miss_char) = NVL(l_org_unit_rec.attribute2, fnd_api.g_miss_char) THEN
2174 l_org_units_hist_rec.old_attribute2 := NULL;
2175 l_org_units_hist_rec.new_attribute2 := NULL;
2176 ELSIF
2177 NVL(l_org_unit_rec.attribute2,fnd_api.g_miss_char) <> NVL(p_org_unit_rec.attribute2,fnd_api.g_miss_char) THEN
2178 l_org_units_hist_rec.old_attribute2 := l_org_unit_rec.attribute2 ;
2179 l_org_units_hist_rec.new_attribute2 := p_org_unit_rec.attribute2 ;
2180 END IF;
2181 --
2182 IF (p_org_unit_rec.attribute3 = fnd_api.g_miss_char) OR
2183 NVL(p_org_unit_rec.attribute3, fnd_api.g_miss_char) = NVL(l_org_unit_rec.attribute3, fnd_api.g_miss_char) THEN
2184 l_org_units_hist_rec.old_attribute3 := NULL;
2185 l_org_units_hist_rec.new_attribute3 := NULL;
2186 ELSIF
2187 NVL(l_org_unit_rec.attribute3,fnd_api.g_miss_char) <> NVL(p_org_unit_rec.attribute3,fnd_api.g_miss_char) THEN
2188 l_org_units_hist_rec.old_attribute3 := l_org_unit_rec.attribute3 ;
2189 l_org_units_hist_rec.new_attribute3 := p_org_unit_rec.attribute3 ;
2190 END IF;
2191 --
2192 IF (p_org_unit_rec.attribute4 = fnd_api.g_miss_char) OR
2193 NVL(p_org_unit_rec.attribute4, fnd_api.g_miss_char) = NVL(l_org_unit_rec.attribute4, fnd_api.g_miss_char) THEN
2194 l_org_units_hist_rec.old_attribute4 := NULL;
2195 l_org_units_hist_rec.new_attribute4 := NULL;
2196 ELSIF
2197 NVL(l_org_unit_rec.attribute4,fnd_api.g_miss_char) <> NVL(p_org_unit_rec.attribute4,fnd_api.g_miss_char) THEN
2198 l_org_units_hist_rec.old_attribute4 := l_org_unit_rec.attribute4 ;
2199 l_org_units_hist_rec.new_attribute4 := p_org_unit_rec.attribute4 ;
2200 END IF;
2201 --
2202 IF (p_org_unit_rec.attribute5 = fnd_api.g_miss_char) OR
2203 NVL(p_org_unit_rec.attribute5, fnd_api.g_miss_char) = NVL(l_org_unit_rec.attribute5, fnd_api.g_miss_char) THEN
2204 l_org_units_hist_rec.old_attribute5 := NULL;
2205 l_org_units_hist_rec.new_attribute5 := NULL;
2206 ELSIF
2207 NVL(l_org_unit_rec.attribute5,fnd_api.g_miss_char) <> NVL(p_org_unit_rec.attribute5,fnd_api.g_miss_char) THEN
2208 l_org_units_hist_rec.old_attribute5 := l_org_unit_rec.attribute5 ;
2209 l_org_units_hist_rec.new_attribute5 := p_org_unit_rec.attribute5 ;
2210 END IF;
2211 --
2212 IF (p_org_unit_rec.attribute6 = fnd_api.g_miss_char) OR
2213 NVL(p_org_unit_rec.attribute6, fnd_api.g_miss_char) = NVL(l_org_unit_rec.attribute6, fnd_api.g_miss_char) THEN
2214 l_org_units_hist_rec.old_attribute6 := NULL;
2215 l_org_units_hist_rec.new_attribute6 := NULL;
2216 ELSIF
2217 NVL(l_org_unit_rec.attribute6,fnd_api.g_miss_char) <> NVL(p_org_unit_rec.attribute6,fnd_api.g_miss_char) THEN
2218 l_org_units_hist_rec.old_attribute6 := l_org_unit_rec.attribute6 ;
2219 l_org_units_hist_rec.new_attribute6 := p_org_unit_rec.attribute6 ;
2220 END IF;
2221 --
2222 IF (p_org_unit_rec.attribute7 = fnd_api.g_miss_char) OR
2223 NVL(p_org_unit_rec.attribute7, fnd_api.g_miss_char) = NVL(l_org_unit_rec.attribute7, fnd_api.g_miss_char) THEN
2224 l_org_units_hist_rec.old_attribute7 := NULL;
2225 l_org_units_hist_rec.new_attribute7 := NULL;
2226 ELSIF
2227 NVL(l_org_unit_rec.attribute7,fnd_api.g_miss_char) <> NVL(p_org_unit_rec.attribute7,fnd_api.g_miss_char) THEN
2228 l_org_units_hist_rec.old_attribute7 := l_org_unit_rec.attribute7 ;
2229 l_org_units_hist_rec.new_attribute7 := p_org_unit_rec.attribute7 ;
2230 END IF;
2231 --
2232 IF (p_org_unit_rec.attribute8 = fnd_api.g_miss_char) OR
2233 NVL(p_org_unit_rec.attribute8, fnd_api.g_miss_char) = NVL(l_org_unit_rec.attribute8, fnd_api.g_miss_char) THEN
2234 l_org_units_hist_rec.old_attribute8 := NULL;
2235 l_org_units_hist_rec.new_attribute8 := NULL;
2236 ELSIF
2237 NVL(l_org_unit_rec.attribute8,fnd_api.g_miss_char) <> NVL(p_org_unit_rec.attribute8,fnd_api.g_miss_char) THEN
2238 l_org_units_hist_rec.old_attribute8 := l_org_unit_rec.attribute8 ;
2239 l_org_units_hist_rec.new_attribute8 := p_org_unit_rec.attribute8 ;
2240 END IF;
2241 --
2242 IF (p_org_unit_rec.attribute9 = fnd_api.g_miss_char) OR
2243 NVL(p_org_unit_rec.attribute9, fnd_api.g_miss_char) = NVL(l_org_unit_rec.attribute9, fnd_api.g_miss_char) THEN
2244 l_org_units_hist_rec.old_attribute9 := NULL;
2245 l_org_units_hist_rec.new_attribute9 := NULL;
2246 ELSIF
2247 NVL(l_org_unit_rec.attribute9,fnd_api.g_miss_char) <> NVL(p_org_unit_rec.attribute9,fnd_api.g_miss_char) THEN
2248 l_org_units_hist_rec.old_attribute9 := l_org_unit_rec.attribute9 ;
2249 l_org_units_hist_rec.new_attribute9 := p_org_unit_rec.attribute9 ;
2250 END IF;
2251 --
2252 IF (p_org_unit_rec.attribute10 = fnd_api.g_miss_char) OR
2253 NVL(p_org_unit_rec.attribute10, fnd_api.g_miss_char) = NVL(l_org_unit_rec.attribute10, fnd_api.g_miss_char) THEN
2254 l_org_units_hist_rec.old_attribute10 := NULL;
2255 l_org_units_hist_rec.new_attribute10 := NULL;
2256 ELSIF
2257 NVL(l_org_unit_rec.attribute10,fnd_api.g_miss_char) <> NVL(p_org_unit_rec.attribute10,fnd_api.g_miss_char) THEN
2258 l_org_units_hist_rec.old_attribute10 := l_org_unit_rec.attribute10 ;
2259 l_org_units_hist_rec.new_attribute10 := p_org_unit_rec.attribute10 ;
2260 END IF;
2261 --
2262 IF (p_org_unit_rec.attribute11 = fnd_api.g_miss_char) OR
2263 NVL(p_org_unit_rec.attribute11, fnd_api.g_miss_char) = NVL(l_org_unit_rec.attribute11, fnd_api.g_miss_char) THEN
2264 l_org_units_hist_rec.old_attribute11 := NULL;
2265 l_org_units_hist_rec.new_attribute11 := NULL;
2266 ELSIF
2267 NVL(l_org_unit_rec.attribute11,fnd_api.g_miss_char) <> NVL(p_org_unit_rec.attribute11,fnd_api.g_miss_char) THEN
2268 l_org_units_hist_rec.old_attribute11 := l_org_unit_rec.attribute11 ;
2269 l_org_units_hist_rec.new_attribute11 := p_org_unit_rec.attribute11 ;
2270 END IF;
2271 --
2272 IF (p_org_unit_rec.attribute12 = fnd_api.g_miss_char) OR
2273 NVL(p_org_unit_rec.attribute12, fnd_api.g_miss_char) = NVL(l_org_unit_rec.attribute12, fnd_api.g_miss_char) THEN
2274 l_org_units_hist_rec.old_attribute12 := NULL;
2275 l_org_units_hist_rec.new_attribute12 := NULL;
2276 ELSIF
2277 NVL(l_org_unit_rec.attribute12,fnd_api.g_miss_char) <> NVL(p_org_unit_rec.attribute12,fnd_api.g_miss_char) THEN
2278 l_org_units_hist_rec.old_attribute12 := l_org_unit_rec.attribute12 ;
2279 l_org_units_hist_rec.new_attribute12 := p_org_unit_rec.attribute12 ;
2280 END IF;
2281 --
2282 IF (p_org_unit_rec.attribute13 = fnd_api.g_miss_char) OR
2283 NVL(p_org_unit_rec.attribute13, fnd_api.g_miss_char) = NVL(l_org_unit_rec.attribute13, fnd_api.g_miss_char) THEN
2284 l_org_units_hist_rec.old_attribute13 := NULL;
2285 l_org_units_hist_rec.new_attribute13 := NULL;
2286 ELSIF
2287 NVL(l_org_unit_rec.attribute13,fnd_api.g_miss_char) <> NVL(p_org_unit_rec.attribute13,fnd_api.g_miss_char) THEN
2288 l_org_units_hist_rec.old_attribute13 := l_org_unit_rec.attribute13 ;
2289 l_org_units_hist_rec.new_attribute13 := p_org_unit_rec.attribute13 ;
2290 END IF;
2291 --
2292 IF (p_org_unit_rec.attribute14 = fnd_api.g_miss_char) OR
2293 NVL(p_org_unit_rec.attribute14, fnd_api.g_miss_char) = NVL(l_org_unit_rec.attribute14, fnd_api.g_miss_char) THEN
2294 l_org_units_hist_rec.old_attribute14 := NULL;
2295 l_org_units_hist_rec.new_attribute14 := NULL;
2296 ELSIF
2297 NVL(l_org_unit_rec.attribute14,fnd_api.g_miss_char) <> NVL(p_org_unit_rec.attribute14,fnd_api.g_miss_char) THEN
2298 l_org_units_hist_rec.old_attribute14 := l_org_unit_rec.attribute14 ;
2299 l_org_units_hist_rec.new_attribute14 := p_org_unit_rec.attribute14 ;
2300 END IF;
2301 --
2302 IF (p_org_unit_rec.attribute15 = fnd_api.g_miss_char) OR
2303 NVL(p_org_unit_rec.attribute15, fnd_api.g_miss_char) = NVL(l_org_unit_rec.attribute15, fnd_api.g_miss_char) THEN
2304 l_org_units_hist_rec.old_attribute15 := NULL;
2305 l_org_units_hist_rec.new_attribute15 := NULL;
2306 ELSIF
2307 NVL(l_org_unit_rec.attribute15,fnd_api.g_miss_char) <> NVL(p_org_unit_rec.attribute15,fnd_api.g_miss_char) THEN
2308 l_org_units_hist_rec.old_attribute15 := l_org_unit_rec.attribute15 ;
2309 l_org_units_hist_rec.new_attribute15 := p_org_unit_rec.attribute15 ;
2310 END IF;
2311 --
2312
2313 -- Create a row in csi_i_org_assignment history table
2314 CSI_I_ORG_ASSIGNMENTS_H_PKG.Insert_Row(
2315 l_csi_i_org_assign_h_id
2316 ,l_instance_ou_id
2317 ,p_txn_rec.transaction_id
2318 ,l_org_units_hist_rec.old_operating_unit_id
2319 ,l_org_units_hist_rec.new_operating_unit_id
2320 ,l_org_units_hist_rec.old_relationship_type_code
2321 ,l_org_units_hist_rec.new_relationship_type_code
2322 ,l_org_units_hist_rec.old_active_start_date
2323 ,l_org_units_hist_rec.new_active_start_date
2324 ,l_org_units_hist_rec.old_active_end_date
2325 ,l_org_units_hist_rec.new_active_end_date
2326 ,l_org_units_hist_rec.old_context
2327 ,l_org_units_hist_rec.new_context
2328 ,l_org_units_hist_rec.old_attribute1
2329 ,l_org_units_hist_rec.new_attribute1
2330 ,l_org_units_hist_rec.old_attribute2
2331 ,l_org_units_hist_rec.new_attribute2
2332 ,l_org_units_hist_rec.old_attribute3
2333 ,l_org_units_hist_rec.new_attribute3
2334 ,l_org_units_hist_rec.old_attribute4
2335 ,l_org_units_hist_rec.new_attribute4
2336 ,l_org_units_hist_rec.old_attribute5
2337 ,l_org_units_hist_rec.new_attribute5
2338 ,l_org_units_hist_rec.old_attribute6
2339 ,l_org_units_hist_rec.new_attribute6
2340 ,l_org_units_hist_rec.old_attribute7
2341 ,l_org_units_hist_rec.new_attribute7
2342 ,l_org_units_hist_rec.old_attribute8
2343 ,l_org_units_hist_rec.new_attribute8
2344 ,l_org_units_hist_rec.old_attribute9
2345 ,l_org_units_hist_rec.new_attribute9
2346 ,l_org_units_hist_rec.old_attribute10
2347 ,l_org_units_hist_rec.new_attribute10
2348 ,l_org_units_hist_rec.old_attribute11
2349 ,l_org_units_hist_rec.new_attribute11
2350 ,l_org_units_hist_rec.old_attribute12
2351 ,l_org_units_hist_rec.new_attribute12
2352 ,l_org_units_hist_rec.old_attribute13
2353 ,l_org_units_hist_rec.new_attribute13
2354 ,l_org_units_hist_rec.old_attribute14
2355 ,l_org_units_hist_rec.new_attribute14
2356 ,l_org_units_hist_rec.old_attribute15
2357 ,l_org_units_hist_rec.new_attribute15
2358 ,l_dump_frequency_flag
2359 ,fnd_global.user_id
2360 ,sysdate
2361 ,fnd_global.user_id
2362 ,sysdate
2363 ,fnd_global.user_id
2364 ,1
2365 );
2366 END IF;
2367 END;
2368 -- End of modification for Bug#2547034 on 09/20/02 - rtalluri
2369 -- End of API body
2370
2371 -- Standard check of p_commit.
2372 IF FND_API.To_Boolean( p_commit ) THEN
2373 COMMIT WORK;
2374 END IF;
2375
2376 FND_MSG_PUB.Count_And_Get
2377 (p_count => x_msg_count ,
2378 p_data => x_msg_data
2379 );
2380
2381
2382 EXCEPTION
2383
2384 WHEN FND_API.G_EXC_ERROR THEN
2385 ROLLBACK TO update_organization_unit;
2386 x_return_status := FND_API.G_RET_STS_ERROR ;
2387 FND_MSG_PUB.Count_And_Get
2388 ( p_count => x_msg_count,
2389 p_data => x_msg_data
2390 );
2391
2392 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2393 ROLLBACK TO update_organization_unit;
2394 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2395 FND_MSG_PUB.Count_And_Get
2396 ( p_count => x_msg_count,
2397 p_data => x_msg_data
2398 );
2399
2400 WHEN OTHERS THEN
2401 ROLLBACK TO update_organization_unit;
2402 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2403
2404 IF FND_MSG_PUB.Check_Msg_Level
2405 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2406 THEN
2407 FND_MSG_PUB.Add_Exc_Msg
2408 ( g_pkg_name ,
2409 l_api_name
2410 );
2411 END IF;
2412
2413 FND_MSG_PUB.Count_And_Get
2414 ( p_count => x_msg_count,
2415 p_data => x_msg_data
2416 );
2417
2418 END update_organization_unit;
2419
2420 /*--------------------------------------------------*/
2421 /* procedure name: expire_organization_unit */
2422 /* description : Expires an existing instance-org */
2423 /* association */
2424 /*--------------------------------------------------*/
2425
2426 PROCEDURE expire_organization_unit
2427 (
2428 p_api_version IN NUMBER
2429 ,p_commit IN VARCHAR2
2430 ,p_init_msg_list IN VARCHAR2
2431 ,p_validation_level IN NUMBER
2432 ,p_org_unit_rec IN csi_datastructures_pub.organization_units_rec
2433 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
2434 ,x_return_status OUT NOCOPY VARCHAR2
2435 ,x_msg_count OUT NOCOPY NUMBER
2436 ,x_msg_data OUT NOCOPY VARCHAR2
2437 )
2438
2439 IS
2440 l_api_name CONSTANT VARCHAR2(30) := 'expire_organization_unit';
2441 l_api_version CONSTANT NUMBER := 1.0;
2442 l_debug_level NUMBER;
2443 l_org_unit_rec csi_datastructures_pub.organization_units_rec;
2444 l_object_version_number NUMBER;
2445 l_msg_count NUMBER;
2446 l_msg_index NUMBER;
2447 l_exists_flag VARCHAR2(1);
2448 l_valid_flag VARCHAR2(1);
2449 l_ou_lookup_tbl csi_organization_unit_pvt.lookup_tbl;
2450 l_ou_count_rec csi_organization_unit_pvt.ou_count_rec;
2451 l_ou_id_tbl csi_organization_unit_pvt.ou_id_tbl;
2452
2453 BEGIN
2454
2455 -- Standard Start of API savepoint
2456 SAVEPOINT expire_organization_unit;
2457
2458 -- Standard call to check for call compatibility.
2459 IF NOT FND_API.Compatible_API_Call (l_api_version,
2460 p_api_version,
2461 l_api_name ,
2462 g_pkg_name)
2463 THEN
2464 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2465 END IF;
2466
2467
2468 -- Initialize message list if p_init_msg_list is set to TRUE.
2469 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2470 FND_MSG_PUB.initialize;
2471 END IF;
2472
2473
2474 -- Initialize API return status to success
2475 x_return_status := FND_API.G_RET_STS_SUCCESS;
2476
2477
2478 -- Check the profile option debug_level for debug message reporting
2479 l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
2480
2481 -- If debug_level = 1 then dump the procedure name
2482 IF (l_debug_level > 0) THEN
2483 csi_gen_utility_pvt.put_line( 'expire_organization_unit');
2484 END IF;
2485
2486
2487 -- If the debug level = 2 then dump all the parameters values.
2488 IF (l_debug_level > 1) THEN
2489 csi_gen_utility_pvt.put_line(
2490 p_api_version ||'-'
2491 || p_commit ||'-'
2492 || p_init_msg_list ||'-'
2493 || p_validation_level);
2494 -- Dump org_unit_rec
2495 csi_gen_utility_pvt.dump_organization_unit_rec(p_org_unit_rec);
2496 -- Dump txn_rec
2497 csi_gen_utility_pvt.dump_txn_rec(p_txn_rec);
2498 END IF;
2499
2500
2501 -- Start API body
2502
2503 -- Validate instance_ou_id
2504 IF NOT(csi_org_unit_vld_pvt.Val_and_get_inst_ou_id
2505 (p_org_unit_rec.instance_ou_id,
2506 l_org_unit_rec)) THEN
2507 RAISE FND_API.G_EXC_ERROR;
2508 END IF;
2509
2510 l_org_unit_rec.instance_ou_id := p_org_unit_rec.instance_ou_id;
2511 l_org_unit_rec.instance_id := FND_API.G_MISS_NUM ;
2512 l_org_unit_rec.operating_unit_id := FND_API.G_MISS_NUM ;
2513 l_org_unit_rec.relationship_type_code := FND_API.G_MISS_CHAR;
2514 l_org_unit_rec.active_start_date := FND_API.G_MISS_DATE;
2515 l_org_unit_rec.active_end_date := SYSDATE;
2516 l_org_unit_rec.context := FND_API.G_MISS_CHAR;
2517 l_org_unit_rec.attribute1 := FND_API.G_MISS_CHAR ;
2518 l_org_unit_rec.attribute2 := FND_API.G_MISS_CHAR;
2519 l_org_unit_rec.attribute3 := FND_API.G_MISS_CHAR;
2520 l_org_unit_rec.attribute4 := FND_API.G_MISS_CHAR;
2521 l_org_unit_rec.attribute5 := FND_API.G_MISS_CHAR;
2522 l_org_unit_rec.attribute6 := FND_API.G_MISS_CHAR;
2523 l_org_unit_rec.attribute7 := FND_API.G_MISS_CHAR;
2524 l_org_unit_rec.attribute8 := FND_API.G_MISS_CHAR;
2525 l_org_unit_rec.attribute9 := FND_API.G_MISS_CHAR;
2526 l_org_unit_rec.attribute10 := FND_API.G_MISS_CHAR;
2527 l_org_unit_rec.attribute11 := FND_API.G_MISS_CHAR;
2528 l_org_unit_rec.attribute12 := FND_API.G_MISS_CHAR;
2529 l_org_unit_rec.attribute13 := FND_API.G_MISS_CHAR;
2530 l_org_unit_rec.attribute14 := FND_API.G_MISS_CHAR;
2531 l_org_unit_rec.attribute15 := FND_API.G_MISS_CHAR;
2532 l_org_unit_rec.object_version_number := p_org_unit_rec.object_version_number;
2533
2534 -- Call update org unit
2535 g_expire_flag := 'Y';
2536 csi_organization_unit_pvt.update_organization_unit
2537 ( p_api_version => p_api_version
2538 ,p_commit => fnd_api.g_false
2539 ,p_init_msg_list => p_init_msg_list
2540 ,p_validation_level => p_validation_level
2541 ,p_org_unit_rec => l_org_unit_rec
2542 ,p_txn_rec => p_txn_rec
2543 ,x_return_status => x_return_status
2544 ,x_msg_count => x_msg_count
2545 ,x_msg_data => x_msg_data
2546 ,p_lookup_tbl => l_ou_lookup_tbl
2547 ,p_ou_count_rec => l_ou_count_rec
2548 ,p_ou_id_tbl => l_ou_id_tbl
2549 );
2550
2551 g_expire_flag := 'N';
2552
2553 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2554 l_msg_index := 1;
2555 l_msg_count := x_msg_count;
2556 WHILE l_msg_count > 0 LOOP
2557 x_msg_data := FND_MSG_PUB.GET
2558 (l_msg_index,
2559 FND_API.G_FALSE );
2560
2561 csi_gen_utility_pvt.put_line('message data = '||x_msg_data);
2562 l_msg_index := l_msg_index + 1;
2563 l_msg_count := l_msg_count - 1;
2564 END LOOP;
2565 RAISE FND_API.G_EXC_ERROR;
2566 END IF;
2567
2568 -- End of API body
2569
2570
2571 -- Standard check of p_commit.
2572 IF FND_API.To_Boolean( p_commit ) THEN
2573 COMMIT WORK;
2574 END IF;
2575
2576
2577 -- Standard call to get message count and if count is get message info.
2578 FND_MSG_PUB.Count_And_Get
2579 (p_count => x_msg_count ,
2580 p_data => x_msg_data
2581 );
2582
2583
2584 EXCEPTION
2585
2586 WHEN FND_API.G_EXC_ERROR THEN
2587
2588 ROLLBACK TO expire_organization_unit;
2589 x_return_status := FND_API.G_RET_STS_ERROR;
2590 FND_MSG_PUB.Count_And_Get
2591 ( p_count => x_msg_count,
2592 p_data => x_msg_data
2593 );
2594
2595 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2596
2597 ROLLBACK TO expire_organization_unit;
2598 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2599 FND_MSG_PUB.Count_And_Get
2600 ( p_count => x_msg_count,
2601 p_data => x_msg_data
2602 );
2603
2604 WHEN OTHERS THEN
2605
2606 ROLLBACK TO expire_organization_unit;
2607 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2608
2609 IF FND_MSG_PUB.Check_Msg_Level
2610 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2611 THEN
2612 FND_MSG_PUB.Add_Exc_Msg
2613 ( g_pkg_name,
2614 l_api_name
2615 );
2616 END IF;
2617
2618 FND_MSG_PUB.Count_And_Get
2619 ( p_count => x_msg_count,
2620 p_data => x_msg_data
2621 );
2622
2623 END expire_organization_unit;
2624
2625 /*--------------------------------------------------*/
2626 /* procedure name: get_org_unit_history */
2627 /* description : Gets organization history */
2628 /* */
2629 /*--------------------------------------------------*/
2630
2631 PROCEDURE get_org_unit_history
2632 ( p_api_version IN NUMBER
2633 ,p_commit IN VARCHAR2
2634 ,p_init_msg_list IN VARCHAR2
2635 ,p_validation_level IN NUMBER
2636 ,p_transaction_id IN NUMBER
2637 ,x_org_unit_history_tbl OUT NOCOPY csi_datastructures_pub.org_units_history_tbl
2638 ,x_return_status OUT NOCOPY VARCHAR2
2639 ,x_msg_count OUT NOCOPY NUMBER
2640 ,x_msg_data OUT NOCOPY VARCHAR2
2641 ) IS
2642 l_api_name CONSTANT VARCHAR2(30) := 'get_org_unit_history' ;
2643 l_api_version CONSTANT NUMBER := 1.0 ;
2644 l_csi_debug_level NUMBER ;
2645 l_count NUMBER := 0 ;
2646 l_flag VARCHAR2(1) :='N' ;
2647 l_org_unit_tbl csi_datastructures_pub.org_units_header_tbl;
2648 i NUMBER :=1;
2649
2650 CURSOR get_org_unit_hist(i_transaction_id NUMBER)
2651 IS
2652 SELECT oah.INSTANCE_OU_HISTORY_ID,
2653 oah.INSTANCE_OU_ID ,
2654 oah.TRANSACTION_ID ,
2655 oah.OLD_OPERATING_UNIT_ID ,
2656 oah.NEW_OPERATING_UNIT_ID ,
2657 oah.OLD_RELATIONSHIP_TYPE_CODE,
2658 oah.NEW_RELATIONSHIP_TYPE_CODE,
2659 oah.OLD_ACTIVE_START_DATE,
2660 oah.NEW_ACTIVE_START_DATE,
2661 oah.OLD_ACTIVE_END_DATE,
2662 oah.NEW_ACTIVE_END_DATE,
2663 oah.OLD_CONTEXT ,
2664 oah.NEW_CONTEXT ,
2665 oah.OLD_ATTRIBUTE1 ,
2666 oah.NEW_ATTRIBUTE1 ,
2667 oah.OLD_ATTRIBUTE2 ,
2668 oah.NEW_ATTRIBUTE2 ,
2669 oah.OLD_ATTRIBUTE3 ,
2670 oah.NEW_ATTRIBUTE3 ,
2671 oah.OLD_ATTRIBUTE4 ,
2672 oah.NEW_ATTRIBUTE4 ,
2673 oah.OLD_ATTRIBUTE5 ,
2674 oah.NEW_ATTRIBUTE5 ,
2675 oah.OLD_ATTRIBUTE6 ,
2676 oah.NEW_ATTRIBUTE6 ,
2677 oah.OLD_ATTRIBUTE7 ,
2678 oah.NEW_ATTRIBUTE7 ,
2679 oah.OLD_ATTRIBUTE8 ,
2680 oah.NEW_ATTRIBUTE8 ,
2681 oah.OLD_ATTRIBUTE9 ,
2682 oah.NEW_ATTRIBUTE9 ,
2683 oah.OLD_ATTRIBUTE10 ,
2684 oah.NEW_ATTRIBUTE10 ,
2685 oah.OLD_ATTRIBUTE11 ,
2686 oah.NEW_ATTRIBUTE11 ,
2687 oah.OLD_ATTRIBUTE12 ,
2688 oah.NEW_ATTRIBUTE12 ,
2689 oah.OLD_ATTRIBUTE13 ,
2690 oah.NEW_ATTRIBUTE13 ,
2691 oah.OLD_ATTRIBUTE14 ,
2692 oah.NEW_ATTRIBUTE14 ,
2693 oah.OLD_ATTRIBUTE15 ,
2694 oah.NEW_ATTRIBUTE15 ,
2695 oah.FULL_DUMP_FLAG ,
2696 oah.OBJECT_VERSION_NUMBER,
2697 oa.INSTANCE_ID
2698 FROM csi_i_org_assignments_h oah,
2699 csi_i_org_assignments oa
2700 WHERE oah.transaction_id = i_transaction_id
2701 AND oah.instance_ou_id = oa.instance_ou_id;
2702
2703 BEGIN
2704 -- Standard Start of API savepoint
2705 -- SAVEPOINT get_org_unit_history;
2706
2707
2708 -- Standard call to check for call compatibility.
2709 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2710 p_api_version ,
2711 l_api_name ,
2712 g_pkg_name )
2713 THEN
2714 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2715 END IF;
2716
2717 -- Initialize message list if p_init_msg_list is set to TRUE.
2718 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2719 FND_MSG_PUB.initialize;
2720 END IF;
2721
2722 -- Initialize API return status to success
2723 x_return_status := FND_API.G_RET_STS_SUCCESS;
2724
2725 -- Check the profile option CSI_DEBUG_LEVEL for debug message reporting
2726 l_csi_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
2727
2728 -- If CSI_DEBUG_LEVEL = 1 then dump the procedure name
2729 IF (l_csi_debug_level > 0) THEN
2730 csi_gen_utility_pvt.put_line( 'get_org_unit_history');
2731 END IF;
2732
2733 -- If the debug level = 2 then dump all the parameters values.
2734 IF (l_csi_debug_level > 1) THEN
2735 csi_gen_utility_pvt.put_line( 'get_org_unit_history' ||
2736 p_api_version ||'-'||
2737 p_commit ||'-'||
2738 p_init_msg_list ||'-'||
2739 p_validation_level ||'-'||
2740 p_transaction_id );
2741 -- dump the in parameter in the log file
2742
2743 END IF;
2744
2745 /***** srramakr commented for bug # 3304439
2746 -- Check for the profile option and enable trace
2747 l_flag:=csi_gen_utility_pvt.enable_trace(l_trace_flag => l_flag);
2748 -- End enable trace
2749 ****/
2750
2751 -- Start API body
2752
2753 FOR C1 IN get_org_unit_hist(p_transaction_id)
2754 LOOP
2755 x_org_unit_history_tbl(i).INSTANCE_OU_HISTORY_ID := C1.INSTANCE_OU_HISTORY_ID;
2756 x_org_unit_history_tbl(i).INSTANCE_OU_ID := C1.INSTANCE_OU_ID;
2757 x_org_unit_history_tbl(i).TRANSACTION_ID := C1.TRANSACTION_ID;
2758 x_org_unit_history_tbl(i).OLD_OPERATING_UNIT_ID := C1.OLD_OPERATING_UNIT_ID;
2759 x_org_unit_history_tbl(i).NEW_OPERATING_UNIT_ID := C1.NEW_OPERATING_UNIT_ID;
2760 x_org_unit_history_tbl(i).OLD_RELATIONSHIP_TYPE_CODE := C1.OLD_RELATIONSHIP_TYPE_CODE;
2761 x_org_unit_history_tbl(i).NEW_RELATIONSHIP_TYPE_CODE := C1.NEW_RELATIONSHIP_TYPE_CODE;
2762 x_org_unit_history_tbl(i).OLD_ACTIVE_START_DATE := C1.OLD_ACTIVE_START_DATE;
2763 x_org_unit_history_tbl(i).NEW_ACTIVE_START_DATE := C1.NEW_ACTIVE_START_DATE;
2764 x_org_unit_history_tbl(i).OLD_ACTIVE_END_DATE := C1.OLD_ACTIVE_END_DATE;
2765 x_org_unit_history_tbl(i).NEW_ACTIVE_END_DATE := C1.NEW_ACTIVE_END_DATE;
2766 x_org_unit_history_tbl(i).OLD_CONTEXT := C1.OLD_CONTEXT;
2767 x_org_unit_history_tbl(i).NEW_CONTEXT := C1.NEW_CONTEXT;
2768 x_org_unit_history_tbl(i).OLD_ATTRIBUTE1 := C1.OLD_ATTRIBUTE1;
2769 x_org_unit_history_tbl(i).NEW_ATTRIBUTE1 := C1.NEW_ATTRIBUTE1;
2770 x_org_unit_history_tbl(i).OLD_ATTRIBUTE2 := C1.OLD_ATTRIBUTE2;
2771 x_org_unit_history_tbl(i).NEW_ATTRIBUTE2 := C1.NEW_ATTRIBUTE2;
2772 x_org_unit_history_tbl(i).OLD_ATTRIBUTE3 := C1.OLD_ATTRIBUTE3;
2773 x_org_unit_history_tbl(i).NEW_ATTRIBUTE3 := C1.NEW_ATTRIBUTE3;
2774 x_org_unit_history_tbl(i).OLD_ATTRIBUTE4 := C1.OLD_ATTRIBUTE4;
2775 x_org_unit_history_tbl(i).NEW_ATTRIBUTE4 := C1.NEW_ATTRIBUTE4;
2776 x_org_unit_history_tbl(i).OLD_ATTRIBUTE5 := C1.OLD_ATTRIBUTE5;
2777 x_org_unit_history_tbl(i).NEW_ATTRIBUTE5 := C1.NEW_ATTRIBUTE5;
2778 x_org_unit_history_tbl(i).OLD_ATTRIBUTE6 := C1.OLD_ATTRIBUTE6;
2779 x_org_unit_history_tbl(i).NEW_ATTRIBUTE6 := C1.NEW_ATTRIBUTE6;
2780 x_org_unit_history_tbl(i).OLD_ATTRIBUTE7 := C1.OLD_ATTRIBUTE7;
2781 x_org_unit_history_tbl(i).NEW_ATTRIBUTE7 := C1.NEW_ATTRIBUTE7;
2782 x_org_unit_history_tbl(i).OLD_ATTRIBUTE8 := C1.OLD_ATTRIBUTE8;
2783 x_org_unit_history_tbl(i).NEW_ATTRIBUTE8 := C1.NEW_ATTRIBUTE8;
2784 x_org_unit_history_tbl(i).OLD_ATTRIBUTE9 := C1.OLD_ATTRIBUTE9;
2785 x_org_unit_history_tbl(i).NEW_ATTRIBUTE9 := C1.NEW_ATTRIBUTE9;
2786 x_org_unit_history_tbl(i).OLD_ATTRIBUTE10 := C1.OLD_ATTRIBUTE10;
2787 x_org_unit_history_tbl(i).NEW_ATTRIBUTE10 := C1.NEW_ATTRIBUTE10;
2788 x_org_unit_history_tbl(i).OLD_ATTRIBUTE11 := C1.OLD_ATTRIBUTE11;
2789 x_org_unit_history_tbl(i).NEW_ATTRIBUTE11 := C1.NEW_ATTRIBUTE11;
2790 x_org_unit_history_tbl(i).OLD_ATTRIBUTE12 := C1.OLD_ATTRIBUTE12;
2791 x_org_unit_history_tbl(i).NEW_ATTRIBUTE12 := C1.NEW_ATTRIBUTE12;
2792 x_org_unit_history_tbl(i).OLD_ATTRIBUTE13 := C1.OLD_ATTRIBUTE13;
2793 x_org_unit_history_tbl(i).NEW_ATTRIBUTE13 := C1.NEW_ATTRIBUTE13;
2794 x_org_unit_history_tbl(i).OLD_ATTRIBUTE14 := C1.OLD_ATTRIBUTE14;
2795 x_org_unit_history_tbl(i).NEW_ATTRIBUTE14 := C1.NEW_ATTRIBUTE14;
2796 x_org_unit_history_tbl(i).OLD_ATTRIBUTE15 := C1.OLD_ATTRIBUTE15;
2797 x_org_unit_history_tbl(i).NEW_ATTRIBUTE15 := C1.NEW_ATTRIBUTE15;
2798 x_org_unit_history_tbl(i).FULL_DUMP_FLAG := C1.FULL_DUMP_FLAG;
2799 x_org_unit_history_tbl(i).OBJECT_VERSION_NUMBER := C1.OBJECT_VERSION_NUMBER;
2800 x_org_unit_history_tbl(i).INSTANCE_ID := C1.INSTANCE_ID;
2801
2802 IF x_org_unit_history_tbl(i).old_operating_unit_id IS NOT NULL
2803 THEN
2804 l_org_unit_tbl(1).operating_unit_id := x_org_unit_history_tbl(i).old_operating_unit_id;
2805 csi_organization_unit_pvt.Resolve_id_columns(l_org_unit_tbl);
2806 x_org_unit_history_tbl(i).old_operating_unit_name := l_org_unit_tbl(1).operating_unit_name;
2807 END IF;
2808 IF x_org_unit_history_tbl(i).new_operating_unit_id IS NOT NULL
2809 THEN
2810 l_org_unit_tbl(1).operating_unit_id := x_org_unit_history_tbl(i).new_operating_unit_id;
2811 csi_organization_unit_pvt.Resolve_id_columns(l_org_unit_tbl);
2812 x_org_unit_history_tbl(i).new_operating_unit_name := l_org_unit_tbl(1).operating_unit_name;
2813 END IF;
2814
2815 i := i + 1;
2816
2817 END LOOP;
2818
2819
2820
2821 -- End of API body
2822
2823 -- Standard check of p_commit.
2824 /*
2825 IF FND_API.To_Boolean( p_commit ) THEN
2826 COMMIT WORK;
2827 END IF;
2828 */
2829
2830
2831 -- Standard call to get message count and if count is get message info.
2832 FND_MSG_PUB.Count_And_Get
2833 (p_count => x_msg_count,
2834 p_data => x_msg_data
2835 );
2836
2837
2838 EXCEPTION
2839
2840 WHEN FND_API.G_EXC_ERROR THEN
2841
2842 -- ROLLBACK TO get_org_unit_history;
2843 x_return_status := FND_API.G_RET_STS_ERROR;
2844 FND_MSG_PUB.Count_And_Get
2845 ( p_count => x_msg_count,
2846 p_data => x_msg_data
2847 );
2848
2849 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2850
2851 -- ROLLBACK TO get_org_unit_history;
2852 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2853 FND_MSG_PUB.Count_And_Get
2854 ( p_count => x_msg_count,
2855 p_data => x_msg_data
2856 );
2857
2858 WHEN OTHERS THEN
2859
2860 -- ROLLBACK TO get_org_unit_history;
2861 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2862
2863 IF FND_MSG_PUB.Check_Msg_Level
2864 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2865 THEN
2866 FND_MSG_PUB.Add_Exc_Msg
2867 ( g_pkg_name,
2868 l_api_name
2869 );
2870 END IF;
2871
2872 FND_MSG_PUB.Count_And_Get
2873 ( p_count => x_msg_count,
2874 p_data => x_msg_data
2875 );
2876
2877 END get_org_unit_history;
2878
2879
2880
2881
2882 END csi_organization_unit_pvt;