[Home] [Help]
PACKAGE BODY: APPS.CSI_II_RELATIONSHIPS_H_PKG
Source
1 PACKAGE BODY csi_ii_relationships_h_pkg AS
2 /* $Header: csitirhb.pls 115.14 2003/09/04 00:20:36 sguthiva ship $ */
3 -- start of comments
4 -- package name : csi_ii_relationships_h_pkg
5 -- purpose :
6 -- history :
7 -- note :
8 -- end of comments
9
10
11 g_pkg_name CONSTANT VARCHAR2(30):= 'csi_ii_relationships_h_pkg';
12 g_file_name CONSTANT VARCHAR2(12) := 'csitirhb.pls';
13
14 PROCEDURE insert_row(
15 px_relationship_history_id IN OUT NOCOPY NUMBER ,
16 p_relationship_id NUMBER ,
17 p_transaction_id NUMBER ,
18 p_old_subject_id NUMBER ,
19 p_new_subject_id NUMBER ,
20 p_old_position_reference VARCHAR2,
21 p_new_position_reference VARCHAR2,
22 p_old_active_start_date DATE ,
23 p_new_active_start_date DATE ,
24 p_old_active_end_date DATE ,
25 p_new_active_end_date DATE ,
26 p_old_mandatory_flag VARCHAR2,
27 p_new_mandatory_flag VARCHAR2,
28 p_old_context VARCHAR2,
29 p_new_context VARCHAR2,
30 p_old_attribute1 VARCHAR2,
31 p_new_attribute1 VARCHAR2,
32 p_old_attribute2 VARCHAR2,
33 p_new_attribute2 VARCHAR2,
34 p_old_attribute3 VARCHAR2,
35 p_new_attribute3 VARCHAR2,
36 p_old_attribute4 VARCHAR2,
37 p_new_attribute4 VARCHAR2,
38 p_old_attribute5 VARCHAR2,
39 p_new_attribute5 VARCHAR2,
40 p_old_attribute6 VARCHAR2,
41 p_new_attribute6 VARCHAR2,
42 p_old_attribute7 VARCHAR2,
43 p_new_attribute7 VARCHAR2,
44 p_old_attribute8 VARCHAR2,
45 p_new_attribute8 VARCHAR2,
46 p_old_attribute9 VARCHAR2,
47 p_new_attribute9 VARCHAR2,
48 p_old_attribute10 VARCHAR2,
49 p_new_attribute10 VARCHAR2,
50 p_old_attribute11 VARCHAR2,
51 p_new_attribute11 VARCHAR2,
52 p_old_attribute12 VARCHAR2,
53 p_new_attribute12 VARCHAR2,
54 p_old_attribute13 VARCHAR2,
55 p_new_attribute13 VARCHAR2,
56 p_old_attribute14 VARCHAR2,
57 p_new_attribute14 VARCHAR2,
58 p_old_attribute15 VARCHAR2,
59 p_new_attribute15 VARCHAR2,
60 p_full_dump_flag VARCHAR2,
61 p_created_by NUMBER ,
62 p_creation_date DATE ,
63 p_last_updated_by NUMBER ,
64 p_last_update_date DATE ,
65 p_last_update_login NUMBER ,
66 p_object_version_number NUMBER )
67
68 IS
69 CURSOR c2 IS SELECT csi_ii_relationships_h_s.NEXTVAL FROM sys.dual;
70 BEGIN
71 IF (px_relationship_history_id IS NULL) OR (px_relationship_history_id = fnd_api.g_miss_num) THEN
72 OPEN c2;
73 FETCH c2 INTO px_relationship_history_id;
74 CLOSE c2;
75 END IF;
76 INSERT INTO csi_ii_relationships_h(
77 relationship_history_id,
78 relationship_id,
79 transaction_id,
80 old_subject_id,
81 new_subject_id,
82 old_position_reference,
83 new_position_reference,
84 old_active_start_date,
85 new_active_start_date,
86 old_active_end_date,
87 new_active_end_date,
88 old_mandatory_flag,
89 new_mandatory_flag,
90 old_context,
91 new_context,
92 old_attribute1,
93 new_attribute1,
94 old_attribute2,
95 new_attribute2,
96 old_attribute3,
97 new_attribute3,
98 old_attribute4,
99 new_attribute4,
100 old_attribute5,
101 new_attribute5,
102 old_attribute6,
103 new_attribute6,
104 old_attribute7,
105 new_attribute7,
106 old_attribute8,
107 new_attribute8,
108 old_attribute9,
109 new_attribute9,
110 old_attribute10,
111 new_attribute10,
112 old_attribute11,
113 new_attribute11,
114 old_attribute12,
115 new_attribute12,
116 old_attribute13,
117 new_attribute13,
118 old_attribute14,
119 new_attribute14,
120 old_attribute15,
121 new_attribute15,
122 full_dump_flag,
123 created_by,
124 creation_date,
125 last_updated_by,
126 last_update_date,
127 last_update_login,
128 object_version_number
129 ) VALUES (
130 px_relationship_history_id,
131 DECODE( p_relationship_id, fnd_api.g_miss_num, NULL, p_relationship_id),
132 DECODE( p_transaction_id, fnd_api.g_miss_num, NULL, p_transaction_id),
133 DECODE( p_old_subject_id, fnd_api.g_miss_num, NULL, p_old_subject_id),
134 DECODE( p_new_subject_id, fnd_api.g_miss_num, NULL, p_new_subject_id),
135 DECODE( p_old_position_reference, fnd_api.g_miss_char, NULL, p_old_position_reference),
136 DECODE( p_new_position_reference, fnd_api.g_miss_char, NULL, p_new_position_reference),
137 DECODE( p_old_active_start_date, fnd_api.g_miss_date, TO_DATE(NULL), p_old_active_start_date),
138 DECODE( p_new_active_start_date, fnd_api.g_miss_date, TO_DATE(NULL), p_new_active_start_date),
139 DECODE( p_old_active_end_date, fnd_api.g_miss_date, TO_DATE(NULL), p_old_active_end_date),
140 DECODE( p_new_active_end_date, fnd_api.g_miss_date, TO_DATE(NULL), p_new_active_end_date),
141 DECODE( p_old_mandatory_flag, fnd_api.g_miss_char, NULL, p_old_mandatory_flag),
142 DECODE( p_new_mandatory_flag, fnd_api.g_miss_char, NULL, p_new_mandatory_flag),
143 DECODE( p_old_context, fnd_api.g_miss_char, NULL, p_old_context),
144 DECODE( p_new_context, fnd_api.g_miss_char, NULL, p_new_context),
145 DECODE( p_old_attribute1, fnd_api.g_miss_char, NULL, p_old_attribute1),
146 DECODE( p_new_attribute1, fnd_api.g_miss_char, NULL, p_new_attribute1),
147 DECODE( p_old_attribute2, fnd_api.g_miss_char, NULL, p_old_attribute2),
148 DECODE( p_new_attribute2, fnd_api.g_miss_char, NULL, p_new_attribute2),
149 DECODE( p_old_attribute3, fnd_api.g_miss_char, NULL, p_old_attribute3),
150 DECODE( p_new_attribute3, fnd_api.g_miss_char, NULL, p_new_attribute3),
151 DECODE( p_old_attribute4, fnd_api.g_miss_char, NULL, p_old_attribute4),
152 DECODE( p_new_attribute4, fnd_api.g_miss_char, NULL, p_new_attribute4),
153 DECODE( p_old_attribute5, fnd_api.g_miss_char, NULL, p_old_attribute5),
154 DECODE( p_new_attribute5, fnd_api.g_miss_char, NULL, p_new_attribute5),
155 DECODE( p_old_attribute6, fnd_api.g_miss_char, NULL, p_old_attribute6),
156 DECODE( p_new_attribute6, fnd_api.g_miss_char, NULL, p_new_attribute6),
157 DECODE( p_old_attribute7, fnd_api.g_miss_char, NULL, p_old_attribute7),
158 DECODE( p_new_attribute7, fnd_api.g_miss_char, NULL, p_new_attribute7),
159 DECODE( p_old_attribute8, fnd_api.g_miss_char, NULL, p_old_attribute8),
160 DECODE( p_new_attribute8, fnd_api.g_miss_char, NULL, p_new_attribute8),
161 DECODE( p_old_attribute9, fnd_api.g_miss_char, NULL, p_old_attribute9),
162 DECODE( p_new_attribute9, fnd_api.g_miss_char, NULL, p_new_attribute9),
163 DECODE( p_old_attribute10, fnd_api.g_miss_char, NULL, p_old_attribute10),
164 DECODE( p_new_attribute10, fnd_api.g_miss_char, NULL, p_new_attribute10),
165 DECODE( p_old_attribute11, fnd_api.g_miss_char, NULL, p_old_attribute11),
166 DECODE( p_new_attribute11, fnd_api.g_miss_char, NULL, p_new_attribute11),
167 DECODE( p_old_attribute12, fnd_api.g_miss_char, NULL, p_old_attribute12),
168 DECODE( p_new_attribute12, fnd_api.g_miss_char, NULL, p_new_attribute12),
169 DECODE( p_old_attribute13, fnd_api.g_miss_char, NULL, p_old_attribute13),
170 DECODE( p_new_attribute13, fnd_api.g_miss_char, NULL, p_new_attribute13),
171 DECODE( p_old_attribute14, fnd_api.g_miss_char, NULL, p_old_attribute14),
172 DECODE( p_new_attribute14, fnd_api.g_miss_char, NULL, p_new_attribute14),
173 DECODE( p_old_attribute15, fnd_api.g_miss_char, NULL, p_old_attribute15),
174 DECODE( p_new_attribute15, fnd_api.g_miss_char, NULL, p_new_attribute15),
175 DECODE( p_full_dump_flag, fnd_api.g_miss_char, NULL, p_full_dump_flag),
176 DECODE( p_created_by, fnd_api.g_miss_num, NULL, p_created_by),
177 DECODE( p_creation_date, fnd_api.g_miss_date, TO_DATE(NULL), p_creation_date),
178 DECODE( p_last_updated_by, fnd_api.g_miss_num, NULL, p_last_updated_by),
179 DECODE( p_last_update_date, fnd_api.g_miss_date, TO_DATE(NULL), p_last_update_date),
180 DECODE( p_last_update_login, fnd_api.g_miss_num, NULL, p_last_update_login),
181 DECODE( p_object_version_number, fnd_api.g_miss_num, NULL, p_object_version_number));
182 END insert_row;
183
184
185 PROCEDURE update_row(
186 p_relationship_history_id NUMBER ,
187 p_relationship_id NUMBER ,
188 p_transaction_id NUMBER ,
189 p_old_subject_id NUMBER ,
190 p_new_subject_id NUMBER ,
191 p_old_position_reference VARCHAR2,
192 p_new_position_reference VARCHAR2,
193 p_old_active_start_date DATE ,
194 p_new_active_start_date DATE ,
195 p_old_active_end_date DATE ,
196 p_new_active_end_date DATE ,
197 p_old_mandatory_flag VARCHAR2,
198 p_new_mandatory_flag VARCHAR2,
199 p_old_context VARCHAR2,
200 p_new_context VARCHAR2,
201 p_old_attribute1 VARCHAR2,
202 p_new_attribute1 VARCHAR2,
203 p_old_attribute2 VARCHAR2,
204 p_new_attribute2 VARCHAR2,
205 p_old_attribute3 VARCHAR2,
206 p_new_attribute3 VARCHAR2,
207 p_old_attribute4 VARCHAR2,
208 p_new_attribute4 VARCHAR2,
209 p_old_attribute5 VARCHAR2,
210 p_new_attribute5 VARCHAR2,
211 p_old_attribute6 VARCHAR2,
212 p_new_attribute6 VARCHAR2,
213 p_old_attribute7 VARCHAR2,
214 p_new_attribute7 VARCHAR2,
215 p_old_attribute8 VARCHAR2,
216 p_new_attribute8 VARCHAR2,
217 p_old_attribute9 VARCHAR2,
218 p_new_attribute9 VARCHAR2,
219 p_old_attribute10 VARCHAR2,
220 p_new_attribute10 VARCHAR2,
221 p_old_attribute11 VARCHAR2,
222 p_new_attribute11 VARCHAR2,
223 p_old_attribute12 VARCHAR2,
224 p_new_attribute12 VARCHAR2,
225 p_old_attribute13 VARCHAR2,
226 p_new_attribute13 VARCHAR2,
227 p_old_attribute14 VARCHAR2,
228 p_new_attribute14 VARCHAR2,
229 p_old_attribute15 VARCHAR2,
230 p_new_attribute15 VARCHAR2,
231 p_full_dump_flag VARCHAR2,
232 p_created_by NUMBER ,
233 p_creation_date DATE ,
234 p_last_updated_by NUMBER ,
235 p_last_update_date DATE ,
236 p_last_update_login NUMBER ,
237 p_object_version_number NUMBER )
238
239 IS
240 BEGIN
241 UPDATE csi_ii_relationships_h
242 SET
243 relationship_id = DECODE( p_relationship_id, fnd_api.g_miss_num, relationship_id, p_relationship_id),
244 transaction_id = DECODE( p_transaction_id, fnd_api.g_miss_num, transaction_id, p_transaction_id),
245 old_subject_id = DECODE( p_old_subject_id, fnd_api.g_miss_num, old_subject_id, p_old_subject_id),
246 new_subject_id = DECODE( p_new_subject_id, fnd_api.g_miss_num, new_subject_id, p_new_subject_id),
247 old_position_reference = DECODE( p_old_position_reference, fnd_api.g_miss_char, old_position_reference, p_old_position_reference),
248 new_position_reference = DECODE( p_new_position_reference, fnd_api.g_miss_char, new_position_reference, p_new_position_reference),
249 old_active_start_date = DECODE( p_old_active_start_date, fnd_api.g_miss_date, old_active_start_date, p_old_active_start_date),
250 new_active_start_date = DECODE( p_new_active_start_date, fnd_api.g_miss_date, new_active_start_date, p_new_active_start_date),
251 old_active_end_date = DECODE( p_old_active_end_date, fnd_api.g_miss_date, old_active_end_date, p_old_active_end_date),
252 new_active_end_date = DECODE( p_new_active_end_date, fnd_api.g_miss_date, new_active_end_date, p_new_active_end_date),
253 old_mandatory_flag = DECODE( p_old_mandatory_flag, fnd_api.g_miss_char, old_mandatory_flag, p_old_mandatory_flag),
254 new_mandatory_flag = DECODE( p_new_mandatory_flag, fnd_api.g_miss_char, new_mandatory_flag, p_new_mandatory_flag),
255 old_context = DECODE( p_old_context, fnd_api.g_miss_char, old_context, p_old_context),
256 new_context = DECODE( p_new_context, fnd_api.g_miss_char, new_context, p_new_context),
257 old_attribute1 = DECODE( p_old_attribute1, fnd_api.g_miss_char, old_attribute1, p_old_attribute1),
258 new_attribute1 = DECODE( p_new_attribute1, fnd_api.g_miss_char, new_attribute1, p_new_attribute1),
259 old_attribute2 = DECODE( p_old_attribute2, fnd_api.g_miss_char, old_attribute2, p_old_attribute2),
260 new_attribute2 = DECODE( p_new_attribute2, fnd_api.g_miss_char, new_attribute2, p_new_attribute2),
261 old_attribute3 = DECODE( p_old_attribute3, fnd_api.g_miss_char, old_attribute3, p_old_attribute3),
262 new_attribute3 = DECODE( p_new_attribute3, fnd_api.g_miss_char, new_attribute3, p_new_attribute3),
263 old_attribute4 = DECODE( p_old_attribute4, fnd_api.g_miss_char, old_attribute4, p_old_attribute4),
264 new_attribute4 = DECODE( p_new_attribute4, fnd_api.g_miss_char, new_attribute4, p_new_attribute4),
265 old_attribute5 = DECODE( p_old_attribute5, fnd_api.g_miss_char, old_attribute5, p_old_attribute5),
266 new_attribute5 = DECODE( p_new_attribute5, fnd_api.g_miss_char, new_attribute5, p_new_attribute5),
267 old_attribute6 = DECODE( p_old_attribute6, fnd_api.g_miss_char, old_attribute6, p_old_attribute6),
268 new_attribute6 = DECODE( p_new_attribute6, fnd_api.g_miss_char, new_attribute6, p_new_attribute6),
269 old_attribute7 = DECODE( p_old_attribute7, fnd_api.g_miss_char, old_attribute7, p_old_attribute7),
270 new_attribute7 = DECODE( p_new_attribute7, fnd_api.g_miss_char, new_attribute7, p_new_attribute7),
274 new_attribute9 = DECODE( p_new_attribute9, fnd_api.g_miss_char, new_attribute9, p_new_attribute9),
271 old_attribute8 = DECODE( p_old_attribute8, fnd_api.g_miss_char, old_attribute8, p_old_attribute8),
272 new_attribute8 = DECODE( p_new_attribute8, fnd_api.g_miss_char, new_attribute8, p_new_attribute8),
273 old_attribute9 = DECODE( p_old_attribute9, fnd_api.g_miss_char, old_attribute9, p_old_attribute9),
275 old_attribute10 = DECODE( p_old_attribute10, fnd_api.g_miss_char, old_attribute10, p_old_attribute10),
276 new_attribute10 = DECODE( p_new_attribute10, fnd_api.g_miss_char, new_attribute10, p_new_attribute10),
277 old_attribute11 = DECODE( p_old_attribute11, fnd_api.g_miss_char, old_attribute11, p_old_attribute11),
278 new_attribute11 = DECODE( p_new_attribute11, fnd_api.g_miss_char, new_attribute11, p_new_attribute11),
279 old_attribute12 = DECODE( p_old_attribute12, fnd_api.g_miss_char, old_attribute12, p_old_attribute12),
280 new_attribute12 = DECODE( p_new_attribute12, fnd_api.g_miss_char, new_attribute12, p_new_attribute12),
281 old_attribute13 = DECODE( p_old_attribute13, fnd_api.g_miss_char, old_attribute13, p_old_attribute13),
282 new_attribute13 = DECODE( p_new_attribute13, fnd_api.g_miss_char, new_attribute13, p_new_attribute13),
283 old_attribute14 = DECODE( p_old_attribute14, fnd_api.g_miss_char, old_attribute14, p_old_attribute14),
284 new_attribute14 = DECODE( p_new_attribute14, fnd_api.g_miss_char, new_attribute14, p_new_attribute14),
285 old_attribute15 = DECODE( p_old_attribute15, fnd_api.g_miss_char, old_attribute15, p_old_attribute15),
286 new_attribute15 = DECODE( p_new_attribute15, fnd_api.g_miss_char, new_attribute15, p_new_attribute15),
287 full_dump_flag = DECODE( p_full_dump_flag, fnd_api.g_miss_char, full_dump_flag, p_full_dump_flag),
288 created_by = DECODE( p_created_by, fnd_api.g_miss_num, created_by, p_created_by),
289 creation_date = DECODE( p_creation_date, fnd_api.g_miss_date, creation_date, p_creation_date),
290 last_updated_by = DECODE( p_last_updated_by, fnd_api.g_miss_num, last_updated_by, p_last_updated_by),
291 last_update_date = DECODE( p_last_update_date, fnd_api.g_miss_date, last_update_date, p_last_update_date),
292 last_update_login = DECODE( p_last_update_login, fnd_api.g_miss_num, last_update_login, p_last_update_login),
293 --object_version_number = DECODE( p_object_version_number, fnd_api.g_miss_num, object_version_number, p_object_version_number)
294 object_version_number = object_version_number + 1
295 WHERE relationship_history_id = p_relationship_history_id;
296
297 IF (SQL%NOTFOUND) THEN
298 RAISE NO_DATA_FOUND;
299 END IF;
300 END Update_Row;
301
302 END csi_ii_relationships_h_pkg;