[Home] [Help]
PACKAGE BODY: APPS.IBC_COMPOUND_RELATIONS_PKG
Source
1 PACKAGE BODY Ibc_Compound_Relations_Pkg AS
2 /* $Header: ibctcrlb.pls 120.1 2005/07/29 15:03:56 appldev ship $*/
3
4 -- Purpose: Table Handler for Ibc_Compound_Relations table.
5
6 -- MODIFICATION HISTORY
7 -- Person Date Comments
8 -- --------- ------ ------------------------------------------
9 -- Sri Rangarajan 01/06/2002 Created Package
10 -- shitij.vatsa 11/04/2002 Updated for FND_API.G_MISS_XXX
11 -- shitij.vatsa 02/11/2003 Added parameter p_subitem_version_id
12 -- to the APIs
13 -- SHARMA 07/04/2005 Modified LOAD_ROW, TRANSLATE_ROW and created
14 -- LOAD_SEED_ROW for R12 LCT standards bug 4411674
15
16 PROCEDURE INSERT_ROW (
17 x_rowid OUT NOCOPY VARCHAR2
18 ,px_compound_relation_id IN OUT NOCOPY NUMBER
19 ,p_content_item_id IN NUMBER
20 ,p_attribute_type_code IN VARCHAR2
21 ,p_content_type_code IN VARCHAR2
22 ,p_citem_version_id IN NUMBER
23 ,p_object_version_number IN NUMBER
24 ,p_sort_order IN NUMBER
25 ,p_creation_date IN DATE --DEFAULT NULL
26 ,p_created_by IN NUMBER --DEFAULT NULL
27 ,p_last_update_date IN DATE --DEFAULT NULL
28 ,p_last_updated_by IN NUMBER --DEFAULT NULL
29 ,p_last_update_login IN NUMBER --DEFAULT NULL
30 ,p_subitem_version_id IN NUMBER --DEFAULT NULL
31 ) IS
32 CURSOR C IS SELECT ROWID FROM IBC_COMPOUND_RELATIONS
33 WHERE compound_relation_id = compound_relation_id;
34
35 CURSOR c2 IS SELECT ibc_compound_relations_s1.NEXTVAL FROM dual;
36
37 BEGIN
38
39 -- Primary key validation check
40
41 IF ((px_compound_relation_id IS NULL) OR
42 (px_compound_relation_id = FND_API.G_MISS_NUM))
43 THEN
44 OPEN c2;
45 FETCH c2 INTO px_compound_relation_id;
46 CLOSE c2;
47 END IF;
48
49 INSERT INTO IBC_COMPOUND_RELATIONS (
50 compound_relation_id,
51 CONTENT_ITEM_ID,
52 ATTRIBUTE_TYPE_CODE,
53 CONTENT_TYPE_CODE,
54 CITEM_VERSION_ID,
55 SORT_ORDER,
56 OBJECT_VERSION_NUMBER,
57 CREATION_DATE,
58 CREATED_BY,
59 LAST_UPDATE_DATE,
60 LAST_UPDATED_BY,
61 LAST_UPDATE_LOGIN,
62 SUBITEM_VERSION_ID
63 ) VALUES (
64 px_compound_relation_id
65 ,p_content_item_id
66 ,p_attribute_type_code
67 ,p_content_type_code
68 ,p_citem_version_id
69 ,p_sort_order
70 ,DECODE(p_object_version_number,FND_API.G_MISS_NUM,1,NULL,1,p_object_version_number)
71 ,DECODE(p_creation_date,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,p_creation_date)
72 ,DECODE(p_created_by,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,p_created_by)
73 ,DECODE(p_last_update_date,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,p_last_update_date)
74 ,DECODE(p_last_updated_by,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,p_last_updated_by)
75 ,DECODE(p_last_update_login,FND_API.G_MISS_NUM,FND_GLOBAL.login_id,NULL,FND_GLOBAL.user_id,p_last_update_login)
76 ,p_subitem_version_id
77 );
78
79 OPEN c;
80 FETCH c INTO x_ROWID;
81 IF (c%NOTFOUND) THEN
82 CLOSE c;
83 RAISE NO_DATA_FOUND;
84 END IF;
85 CLOSE c;
86
87 END INSERT_ROW;
88
89 PROCEDURE LOCK_ROW (
90 p_compound_relation_id IN NUMBER,
91 p_CONTENT_ITEM_ID IN NUMBER,
92 p_ATTRIBUTE_TYPE_CODE IN VARCHAR2,
93 p_CONTENT_TYPE_CODE IN VARCHAR2,
94 p_CITEM_VERSION_ID IN NUMBER,
95 p_OBJECT_VERSION_NUMBER IN NUMBER,
96 p_SORT_ORDER IN NUMBER
97 ) IS
98 CURSOR c IS SELECT
99 OBJECT_VERSION_NUMBER,
100 SORT_ORDER
101 FROM IBC_COMPOUND_RELATIONS
102 WHERE compound_relation_id = p_compound_relation_id
103 FOR UPDATE OF CONTENT_ITEM_ID NOWAIT;
104 recinfo c%ROWTYPE;
105
106 BEGIN
107 OPEN c;
108 FETCH c INTO recinfo;
109 IF (c%NOTFOUND) THEN
110 CLOSE c;
111 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
112 app_exception.raise_exception;
113 END IF;
114 CLOSE c;
115 IF ( (recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
116 AND (recinfo.SORT_ORDER = p_SORT_ORDER)
117 ) THEN
118 NULL;
119 ELSE
120 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
121 app_exception.raise_exception;
122 END IF;
123
124 END LOCK_ROW;
125
126 PROCEDURE UPDATE_ROW (
127 p_compound_relation_id IN NUMBER
128 ,p_attribute_type_code IN VARCHAR2 --DEFAULT NULL
129 ,p_citem_version_id IN NUMBER --DEFAULT NULL
130 ,p_content_item_id IN NUMBER --DEFAULT NULL
131 ,p_content_type_code IN VARCHAR2 --DEFAULT NULL
132 ,p_last_updated_by IN NUMBER --DEFAULT NULL
133 ,p_last_update_date IN DATE --DEFAULT NULL
134 ,p_last_update_login IN NUMBER --DEFAULT NULL
135 ,p_object_version_number IN NUMBER --DEFAULT NULL
136 ,p_sort_order IN NUMBER --DEFAULT NULL
137 ,p_subitem_version_id IN NUMBER --DEFAULT NULL
138 ) IS
139 BEGIN
140 UPDATE IBC_COMPOUND_RELATIONS SET
141 content_item_id = DECODE(p_content_item_id,FND_API.G_MISS_NUM,NULL,NULL,content_item_id,p_content_item_id)
142 ,attribute_type_code = DECODE(p_attribute_type_code,FND_API.G_MISS_CHAR,NULL,NULL,attribute_type_code,p_attribute_type_code)
143 ,content_type_code = DECODE(p_content_type_code,FND_API.G_MISS_CHAR,NULL,NULL,content_type_code,p_content_type_code)
144 ,citem_version_id = DECODE(p_citem_version_id,FND_API.G_MISS_NUM,NULL,NULL,citem_version_id,p_citem_version_id)
145 ,sort_order = DECODE(p_sort_order,FND_API.G_MISS_NUM,NULL,NULL,sort_order,p_sort_order)
146 ,object_version_number = NVL(object_version_number,0) + 1
147 ,last_update_date = DECODE(p_last_update_date,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,p_last_update_date)
148 ,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)
149 ,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)
150 ,subitem_version_id = DECODE(p_subitem_version_id,FND_API.G_MISS_NUM,NULL,NULL,subitem_version_id,p_subitem_version_id)
151 WHERE compound_relation_id = p_compound_relation_id;
152
153 -- Ignore object_version number as this table will always be updated in conjuction
154 -- with
155 /* AND object_version_number = DECODE(p_object_version_number,
156 FND_API.G_MISS_NUM,
157 object_version_number,
158 NULL,
159 object_version_number,
160 p_object_version_number);*/
161
162 IF (SQL%NOTFOUND) THEN
163 RAISE NO_DATA_FOUND;
164 END IF;
165
166 END UPDATE_ROW;
167
168 PROCEDURE DELETE_ROW (
169 p_compound_relation_id IN NUMBER
170 ) IS
171 BEGIN
172
173 DELETE FROM IBC_COMPOUND_RELATIONS
174 WHERE compound_relation_id = p_compound_relation_id;
175
176 IF (SQL%NOTFOUND) THEN
177 RAISE NO_DATA_FOUND;
178 END IF;
179 END DELETE_ROW;
180
181 PROCEDURE LOAD_SEED_ROW (
182 p_UPLOAD_MODE IN VARCHAR2,
183 p_CONTENT_ITEM_ID NUMBER,
184 p_ATTRIBUTE_TYPE_CODE VARCHAR2,
185 p_CONTENT_TYPE_CODE VARCHAR2,
186 p_COMPOUND_RELATION_ID NUMBER,
187 p_CITEM_VERSION_ID NUMBER,
188 p_SORT_ORDER NUMBER,
189 p_OWNER IN VARCHAR2,
190 p_subitem_version_id IN NUMBER DEFAULT NULL,
191 p_LAST_UPDATE_DATE IN VARCHAR2) IS
192 BEGIN
193 IF (p_UPLOAD_MODE = 'NLS') THEN
194 NULL;
195 ELSE
196 Ibc_Compound_Relations_Pkg.LOAD_ROW (
197 p_UPLOAD_MODE => p_UPLOAD_MODE,
198 p_CONTENT_ITEM_ID => p_CONTENT_ITEM_ID,
199 p_ATTRIBUTE_TYPE_CODE => p_ATTRIBUTE_TYPE_CODE,
200 p_CONTENT_TYPE_CODE => p_CONTENT_TYPE_CODE,
201 p_COMPOUND_RELATION_ID => p_COMPOUND_RELATION_ID,
202 p_CITEM_VERSION_ID => p_CITEM_VERSION_ID,
203 p_SORT_ORDER => p_SORT_ORDER,
204 p_OWNER => p_OWNER,
205 p_subitem_version_id => p_subitem_version_id,
206 p_LAST_UPDATE_DATE => p_LAST_UPDATE_DATE );
207 END IF;
208 END;
209
210
211 PROCEDURE LOAD_ROW (
212 p_UPLOAD_MODE IN VARCHAR2,
213 p_CONTENT_ITEM_ID NUMBER,
214 p_ATTRIBUTE_TYPE_CODE VARCHAR2,
215 p_CONTENT_TYPE_CODE VARCHAR2,
216 p_COMPOUND_RELATION_ID NUMBER,
217 p_CITEM_VERSION_ID NUMBER,
218 p_SORT_ORDER NUMBER,
219 p_OWNER IN VARCHAR2,
220 p_subitem_version_id IN NUMBER DEFAULT NULL,
221 p_LAST_UPDATE_DATE IN VARCHAR2) IS
222
223 l_user_id NUMBER := 0;
224 l_row_id VARCHAR2(64);
225 lx_object_version_number NUMBER := FND_API.G_MISS_NUM;
226 lx_COMPOUND_RELATION_ID NUMBER := p_COMPOUND_RELATION_ID;
227 l_last_update_date DATE;
228
229 db_user_id NUMBER := 0;
230 db_last_update_date DATE;
231
232 BEGIN
233 --get last updated by user id
234 l_user_id := FND_LOAD_UTIL.OWNER_ID(p_OWNER);
235
236 --translate data type VARCHAR2 to DATE for last_update_date
237 l_last_update_date := nvl(TO_DATE(p_last_update_date, 'YYYY/MM/DD'),SYSDATE);
238
239 -- get updatedby and update_date values if existing in db
240 SELECT LAST_UPDATED_BY, LAST_UPDATE_DATE INTO db_user_id, db_last_update_date
241 FROM IBC_COMPOUND_RELATIONS
242 WHERE p_compound_relation_id = p_compound_relation_id;
243
244 IF (FND_LOAD_UTIL.UPLOAD_TEST(l_user_id, l_last_update_date,
245 db_user_id, db_last_update_date, p_upload_mode )) THEN
246
247 UPDATE_ROW (
248 p_compound_relation_id => NVL(p_compound_relation_id,FND_API.G_MISS_NUM)
249 ,p_content_item_id => NVL(p_content_item_id,FND_API.G_MISS_NUM)
250 ,p_attribute_type_code => NVL(p_attribute_type_code,FND_API.G_MISS_CHAR)
251 ,p_content_type_code => NVL(p_content_type_code,FND_API.G_MISS_CHAR)
252 ,p_citem_version_id => NVL(p_citem_version_id,FND_API.G_MISS_NUM)
253 ,p_sort_order => NVL(p_sort_order,FND_API.G_MISS_NUM)
254 ,p_last_updated_by => l_user_id
255 ,p_last_update_date => SYSDATE
256 ,p_last_update_login => 0
257 ,p_object_version_number => NULL
258 ,p_subitem_version_id => NVL(p_subitem_version_id,FND_API.G_MISS_NUM)
259 );
260 END IF;
261
262 EXCEPTION
263 WHEN NO_DATA_FOUND THEN
264 INSERT_ROW (
265 X_ROWID => l_row_id,
266 px_COMPOUND_RELATION_ID => lx_COMPOUND_RELATION_ID,
267 p_CONTENT_ITEM_ID => p_CONTENT_ITEM_ID,
268 p_ATTRIBUTE_TYPE_CODE => p_ATTRIBUTE_TYPE_CODE,
269 p_CONTENT_TYPE_CODE => p_CONTENT_TYPE_CODE,
270 p_CITEM_VERSION_ID => p_CITEM_VERSION_ID,
271 p_SORT_ORDER => p_SORT_ORDER,
272 p_OBJECT_VERSION_NUMBER => 1,
273 p_CREATION_DATE => SYSDATE,
274 p_CREATED_BY => l_user_id,
275 p_LAST_UPDATE_DATE => SYSDATE,
276 p_LAST_UPDATED_BY => l_user_id,
277 p_LAST_UPDATE_LOGIN => 0,
278 p_subitem_version_id => p_subitem_version_id);
279 END LOAD_ROW;
280
281
282 END Ibc_Compound_Relations_Pkg;