[Home] [Help]
PACKAGE BODY: APPS.IBC_ASSOCIATIONS_PKG
Source
1 PACKAGE BODY Ibc_Associations_Pkg AS
2 /* $Header: ibctasnb.pls 120.1 2005/07/29 15:10:16 appldev ship $ */
3
4
5 -- Purpose: Table Handler for IBC_ASSOCIATIONS table.
6
7 -- MODIFICATION HISTORY
8 -- Person Date Comments
9 -- --------- ------ ------------------------------------------
10 -- Sri Rangarajan 01/06/2002 Created Package
11 -- shitij.vatsa 11/04/2002 Updated for FND_API.G_MISS_XXX
12 -- SHARMA 07/04/2005 Modified LOAD_ROW, TRANSLATE_ROW and created
13 -- LOAD_SEED_ROW for R12 LCT standards bug 4411674
14
15
16 PROCEDURE INSERT_ROW (
17 x_rowid OUT NOCOPY VARCHAR2
18 ,px_association_id IN OUT NOCOPY NUMBER
19 ,p_content_item_id IN NUMBER
20 ,p_citem_version_id IN NUMBER
21 ,p_association_type_code IN VARCHAR2
22 ,p_associated_object_val1 IN VARCHAR2
23 ,p_associated_object_val2 IN VARCHAR2
24 ,p_associated_object_val3 IN VARCHAR2
25 ,p_associated_object_val4 IN VARCHAR2
26 ,p_associated_object_val5 IN VARCHAR2
27 ,p_object_version_number IN NUMBER
28 ,p_creation_date IN DATE --DEFAULT NULL
29 ,p_created_by IN NUMBER --DEFAULT NULL
30 ,p_last_update_date IN DATE --DEFAULT NULL
31 ,p_last_updated_by IN NUMBER --DEFAULT NULL
32 ,p_last_update_login IN NUMBER --DEFAULT NULL
33 ) IS
34
35 CURSOR c IS SELECT ROWID FROM ibc_associations
36 WHERE association_id = px_association_id;
37
38 CURSOR c2 IS SELECT ibc_associations_s1.NEXTVAL FROM dual;
39
40 BEGIN
41
42 -- Primary key validation check
43
44 IF ((px_association_id IS NULL) OR
45 (px_association_id = Fnd_Api.G_MISS_NUM))
46 THEN
47 OPEN c2;
48 FETCH c2 INTO px_association_id;
49 CLOSE c2;
50 END IF;
51
52 INSERT INTO ibc_associations (
53 association_id
54 ,content_item_id
55 ,citem_version_id
56 ,association_type_code
57 ,associated_object_val1
58 ,associated_object_val2
59 ,associated_object_val3
60 ,associated_object_val4
61 ,associated_object_val5
62 ,object_version_number
63 ,creation_date
64 ,created_by
65 ,last_update_date
66 ,last_updated_by
67 ,last_update_login
68 )
69 VALUES (
70 px_association_id
71 ,DECODE(p_content_item_id,Fnd_Api.G_MISS_NUM,NULL,p_content_item_id)
72 ,DECODE(p_citem_version_id,Fnd_Api.G_MISS_NUM,NULL,p_citem_version_id)
73 ,DECODE(p_association_type_code,Fnd_Api.G_MISS_CHAR,NULL,p_association_type_code)
74 ,DECODE(p_associated_object_val1,Fnd_Api.G_MISS_CHAR,NULL,p_associated_object_val1)
75 ,DECODE(p_associated_object_val2,Fnd_Api.G_MISS_CHAR,NULL,p_associated_object_val2)
76 ,DECODE(p_associated_object_val3,Fnd_Api.G_MISS_CHAR,NULL,p_associated_object_val3)
77 ,DECODE(p_associated_object_val4,Fnd_Api.G_MISS_CHAR,NULL,p_associated_object_val4)
78 ,DECODE(p_associated_object_val5,Fnd_Api.G_MISS_CHAR,NULL,p_associated_object_val5)
79 ,DECODE(p_object_version_number,Fnd_Api.G_MISS_NUM,NULL,p_object_version_number)
80 ,DECODE(p_creation_date,Fnd_Api.G_MISS_DATE,SYSDATE,NULL,SYSDATE,p_creation_date)
81 ,DECODE(p_created_by,Fnd_Api.G_MISS_NUM,Fnd_Global.user_id,NULL,Fnd_Global.user_id,p_created_by)
82 ,DECODE(p_last_update_date,Fnd_Api.G_MISS_DATE,SYSDATE,NULL,SYSDATE,p_last_update_date)
83 ,DECODE(p_last_updated_by,Fnd_Api.G_MISS_NUM,Fnd_Global.user_id,NULL,Fnd_Global.user_id,p_last_updated_by)
84 ,DECODE(p_last_update_login,Fnd_Api.G_MISS_NUM,Fnd_Global.login_id,NULL,Fnd_Global.user_id,p_last_update_login)
85 );
86
87
88
89
90 OPEN c;
91 FETCH c INTO x_rowid;
92 IF (c%NOTFOUND) THEN
93 CLOSE c;
94 RAISE NO_DATA_FOUND;
95 END IF;
96 CLOSE c;
97
98 END INSERT_ROW;
99
100
101 PROCEDURE UPDATE_ROW (
102 p_association_id IN NUMBER
103 ,p_content_item_id IN NUMBER --DEFAULT NULL
104 ,p_citem_version_id IN NUMBER
105 ,p_association_type_code IN VARCHAR2 --DEFAULT NULL
106 ,p_associated_object_val1 IN VARCHAR2 --DEFAULT NULL
107 ,p_associated_object_val2 IN VARCHAR2 --DEFAULT NULL
108 ,p_associated_object_val3 IN VARCHAR2 --DEFAULT NULL
109 ,p_associated_object_val4 IN VARCHAR2 --DEFAULT NULL
110 ,p_associated_object_val5 IN VARCHAR2 --DEFAULT NULL
111 ,p_object_version_number IN NUMBER --DEFAULT NULL
112 ,p_created_by IN NUMBER --DEFAULT NULL
113 ,p_creation_date IN DATE --DEFAULT NULL
114 ,p_last_updated_by IN NUMBER --DEFAULT NULL
115 ,p_last_update_date IN DATE --DEFAULT NULL
116 ,p_last_update_login IN NUMBER --DEFAULT NULL
117 )
118 IS
119 BEGIN
120 UPDATE ibc_associations SET
121 content_item_id = DECODE(p_content_item_id,Fnd_Api.G_MISS_NUM,NULL,NULL,content_item_id,p_content_item_id)
122 ,citem_version_id = DECODE(p_citem_version_id,Fnd_Api.G_MISS_NUM,NULL,NULL,citem_version_id,p_citem_version_id)
123 ,association_type_code = DECODE(p_association_type_code,Fnd_Api.G_MISS_CHAR,NULL,NULL,association_type_code,p_association_type_code)
124 ,associated_object_val1 = DECODE(p_associated_object_val1,Fnd_Api.G_MISS_CHAR,NULL,NULL,associated_object_val1,p_associated_object_val1)
125 ,associated_object_val2 = DECODE(p_associated_object_val2,Fnd_Api.G_MISS_CHAR,NULL,NULL,associated_object_val2,p_associated_object_val2)
126 ,associated_object_val3 = DECODE(p_associated_object_val3,Fnd_Api.G_MISS_CHAR,NULL,NULL,associated_object_val3,p_associated_object_val3)
127 ,associated_object_val4 = DECODE(p_associated_object_val4,Fnd_Api.G_MISS_CHAR,NULL,NULL,associated_object_val4,p_associated_object_val4)
128 ,associated_object_val5 = DECODE(p_associated_object_val5,Fnd_Api.G_MISS_CHAR,NULL,NULL,associated_object_val5,p_associated_object_val5)
129 ,last_updated_by = DECODE(p_last_updated_by,Fnd_Api.G_MISS_NUM,Fnd_Global.user_id,NULL,Fnd_Global.user_id,p_last_updated_by)
130 ,last_update_date = DECODE(p_last_update_date,Fnd_Api.G_MISS_DATE,SYSDATE,NULL,SYSDATE,p_last_update_date)
131 ,last_update_login = DECODE(p_last_update_login,Fnd_Api.G_MISS_NUM,Fnd_Global.login_id,NULL,Fnd_Global.user_id,p_last_update_login)
132 ,object_version_number = object_version_number + 1
133 WHERE association_id = p_association_id
134 AND object_version_number = DECODE(p_object_version_number,
135 Fnd_Api.g_miss_num,
136 object_version_number,
137 NULL,
138 object_version_number,
139 p_object_version_number);
140
141 IF (SQL%NOTFOUND) THEN
142 RAISE NO_DATA_FOUND;
143 END IF;
144
145 END UPDATE_ROW;
146
147 PROCEDURE delete_row (
148 p_association_id IN NUMBER
149 ) IS
150 BEGIN
151
152 DELETE FROM ibc_associations
153 WHERE association_id = p_association_id;
154
155 IF (SQL%NOTFOUND) THEN
156 RAISE NO_DATA_FOUND;
157 END IF;
158 END delete_row;
159
160 PROCEDURE lock_row (
161 p_association_id IN NUMBER,
162 p_content_item_id IN NUMBER,
163 p_citem_version_id IN NUMBER,
164 p_association_type_code IN VARCHAR2,
165 p_associated_object_val1 IN VARCHAR2,
166 p_associated_object_val2 IN VARCHAR2,
167 p_associated_object_val3 IN VARCHAR2,
168 p_associated_object_val4 IN VARCHAR2,
169 p_associated_object_val5 IN VARCHAR2,
170 p_object_version_number IN NUMBER
171 ) IS
172 CURSOR c IS SELECT
173 content_item_id,
174 citem_version_id,
175 association_type_code,
176 associated_object_val1,
177 associated_object_val2,
178 associated_object_val3,
179 associated_object_val4,
180 associated_object_val5,
181 object_version_number
182 FROM ibc_associations
183 WHERE association_id = p_association_id
184 FOR UPDATE OF association_id NOWAIT;
185 recinfo c%ROWTYPE;
186
187 BEGIN
188 OPEN c;
189 FETCH c INTO recinfo;
190 IF (c%NOTFOUND) THEN
191 CLOSE c;
192 Fnd_Message.set_name('fnd', 'form_record_deleted');
193 App_Exception.raise_exception;
194 END IF;
195 CLOSE c;
196 IF ( (recinfo.content_item_id = p_content_item_id)
197 AND ((recinfo.citem_version_id = p_citem_version_id)
198 OR ((recinfo.citem_version_id IS NULL) AND (p_citem_version_id IS NULL)))
199 AND (recinfo.association_type_code = p_association_type_code)
200 AND (recinfo.associated_object_val1 = p_associated_object_val1)
201 AND ((recinfo.associated_object_val2 = p_associated_object_val2)
202 OR ((recinfo.associated_object_val2 IS NULL) AND (p_associated_object_val2 IS NULL)))
203 AND ((recinfo.associated_object_val3 = p_associated_object_val3)
204 OR ((recinfo.associated_object_val3 IS NULL) AND (p_associated_object_val3 IS NULL)))
205 AND ((recinfo.associated_object_val4 = p_associated_object_val4)
206 OR ((recinfo.associated_object_val4 IS NULL) AND (p_associated_object_val4 IS NULL)))
207 AND ((recinfo.associated_object_val5 = p_associated_object_val5)
208 OR ((recinfo.associated_object_val5 IS NULL) AND (p_associated_object_val5 IS NULL)))
209 AND (recinfo.object_version_number = p_object_version_number))
210 THEN
211 NULL;
212 ELSE
213 Fnd_Message.set_name('fnd', 'form_record_changed');
214 App_Exception.raise_exception;
215 END IF;
216
217 END lock_row;
218
219 PROCEDURE LOAD_ROW (
220 p_upload_mode IN VARCHAR2,
221 p_association_id IN NUMBER
222 ,p_content_item_id IN NUMBER
223 ,p_citem_version_id IN NUMBER
224 ,p_association_type_code IN VARCHAR2
225 ,p_associated_object_val1 IN VARCHAR2
226 ,p_associated_object_val2 IN VARCHAR2
227 ,p_associated_object_val3 IN VARCHAR2
228 ,p_associated_object_val4 IN VARCHAR2
229 ,p_associated_object_val5 IN VARCHAR2
230 ,p_OWNER IN VARCHAR2
231 ,p_last_update_date in VARCHAR2) IS
232
233 l_user_id NUMBER := 0;
234 lx_rowid VARCHAR2(240);
235 lx_association_id NUMBER := p_association_id;
236 l_last_update_date DATE;
237
238 db_user_id NUMBER := 0;
239 db_last_update_date DATE;
240
241 BEGIN
242 --get last updated by user id
243 l_user_id := FND_LOAD_UTIL.OWNER_ID(p_OWNER);
244
245 --translate data type VARCHAR2 to DATE for last_update_date
246 l_last_update_date := nvl(TO_DATE(p_last_update_date, 'YYYY/MM/DD'),SYSDATE);
247
248 -- get updatedby and update_date values if existing in db
249 SELECT LAST_UPDATED_BY, LAST_UPDATE_DATE INTO db_user_id, db_last_update_date
250 FROM IBC_ASSOCIATIONS
251 WHERE ASSOCIATION_ID = To_NUMBER(p_association_id);
252 BEGIN
253
254 IF (FND_LOAD_UTIL.UPLOAD_TEST(l_user_id, l_last_update_date,
255 db_user_id, db_last_update_date, p_upload_mode )) THEN
256
257 Ibc_Associations_Pkg.UPDATE_ROW (
258 p_CONTENT_ITEM_ID => p_content_item_id
259 ,p_CITEM_VERSION_ID => p_citem_version_id
260 ,p_ASSOCIATION_ID => p_association_id
261 ,p_ASSOCIATION_TYPE_CODE => p_association_type_code
262 ,p_ASSOCIATED_OBJECT_VAL1 => p_ASSOCIATED_OBJECT_VAL1
263 ,p_ASSOCIATED_OBJECT_VAL2 => NVL(p_ASSOCIATED_OBJECT_VAL2,Fnd_Api.G_MISS_CHAR)
264 ,p_ASSOCIATED_OBJECT_VAL3 => NVL(p_ASSOCIATED_OBJECT_VAL3,Fnd_Api.G_MISS_CHAR)
265 ,p_ASSOCIATED_OBJECT_VAL4 => NVL(p_ASSOCIATED_OBJECT_VAL4,Fnd_Api.G_MISS_CHAR)
266 ,p_ASSOCIATED_OBJECT_VAL5 => NVL(p_ASSOCIATED_OBJECT_VAL5,Fnd_Api.G_MISS_CHAR)
267 ,p_last_updated_by => l_user_id
268 ,p_last_update_date => SYSDATE
269 ,p_last_update_login => 0
270 ,p_object_version_number => NULL);
271
272 END IF;
273 EXCEPTION
274 WHEN NO_DATA_FOUND THEN
275
276 Ibc_Associations_Pkg.INSERT_ROW (
277 x_rowid => lx_rowid
278 ,px_ASSOCIATION_ID => lx_association_id
279 ,p_CONTENT_ITEM_ID => p_content_item_id
280 ,p_CITEM_VERSION_ID => p_citem_version_id
281 ,p_ASSOCIATION_TYPE_CODE => p_association_type_code
282 ,p_ASSOCIATED_OBJECT_VAL1 => p_ASSOCIATED_OBJECT_VAL1
283 ,p_ASSOCIATED_OBJECT_VAL2 => NVL(p_ASSOCIATED_OBJECT_VAL2,Fnd_Api.G_MISS_CHAR)
284 ,p_ASSOCIATED_OBJECT_VAL3 => NVL(p_ASSOCIATED_OBJECT_VAL3,Fnd_Api.G_MISS_CHAR)
285 ,p_ASSOCIATED_OBJECT_VAL4 => NVL(p_ASSOCIATED_OBJECT_VAL4,Fnd_Api.G_MISS_CHAR)
286 ,p_ASSOCIATED_OBJECT_VAL5 => NVL(p_ASSOCIATED_OBJECT_VAL5,Fnd_Api.G_MISS_CHAR)
287 ,p_CREATION_DATE => SYSDATE
288 ,p_CREATED_BY => l_user_id
289 ,p_LAST_UPDATE_DATE => SYSDATE
290 ,p_LAST_UPDATED_BY => l_user_id
291 ,p_LAST_UPDATE_LOGIN => 0
292 ,p_OBJECT_VERSION_NUMBER => 1);
293 END;
294
295 END LOAD_ROW;
296
297 PROCEDURE LOAD_SEED_ROW (
298 p_upload_mode IN VARCHAR2,
299 p_association_id IN NUMBER
300 ,p_content_item_id IN NUMBER
301 ,p_citem_version_id IN NUMBER DEFAULT NULL
302 ,p_association_type_code IN VARCHAR2
303 ,p_associated_object_val1 IN VARCHAR2
304 ,p_associated_object_val2 IN VARCHAR2
305 ,p_associated_object_val3 IN VARCHAR2
306 ,p_associated_object_val4 IN VARCHAR2
307 ,p_associated_object_val5 IN VARCHAR2
308 ,p_OWNER IN VARCHAR2,
309 p_last_update_date in VARCHAR2) IS
310 BEGIN
311 IF (p_UPLOAD_MODE = 'NLS') THEN
312 NULL;
313 ELSE
314 IBC_ASSOCIATIONS_PKG.LOAD_ROW (
315 p_upload_mode => p_upload_mode,
316 p_association_id => p_association_id
317 ,p_content_item_id => p_content_item_id
318 ,p_citem_version_id => p_citem_version_id
319 ,p_association_type_code => p_association_type_code
320 ,p_associated_object_val1 => p_associated_object_val1
321 ,p_associated_object_val2 => p_associated_object_val2
322 ,p_associated_object_val3 => p_associated_object_val3
323 ,p_associated_object_val4 => p_associated_object_val4
324 ,p_associated_object_val5 => p_associated_object_val5
325 ,p_OWNER => p_OWNER,
326 p_last_update_date => p_last_update_date);
327 END IF;
328 END;
329
330 END Ibc_Associations_Pkg;