[Home] [Help]
PACKAGE BODY: APPS.IBE_COPY_LOGICALCONTENT_GRP
Source
1 package body IBE_COPY_LogicalContent_GRP AS
2 /* $Header: IBECLCTB.pls 120.3 2009/12/16 17:51:06 ytian noship $ */
3 /*===========================================================================+
4 | Copyright (c) 2000 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +===========================================================================+
8 | File name |
9 | IBECLCTB.pls |
10 | Body file for Copy Content component |
11 | |
12 | Description |
13 | |
14 | |
15 | Dec 15, 2009 YTIAN - Created |
16 |___________________________________________________________________________|*/
17 l_true VARCHAR2(1) := FND_API.G_TRUE;
18 --- Generate primary key for the table
19 CURSOR obj_lgl_ctnt_id_seq IS
20 SELECT ibe_dsp_obj_lgl_ctnt_s1.NEXTVAL
21 FROM DUAL;
22
23 PROCEDURE copy_lgl_ctnt(
24 p_api_version IN NUMBER,
25 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
26 p_commit IN VARCHAR2 := FND_API.g_false,
27 p_object_type_code IN VARCHAR2,
28 p_from_Product_id IN NUMBER,
29 p_from_Context_ids IN Ids_List,
30 p_to_product_ids IN Ids_List,
31 x_copy_status OUT NOCOPY IDS_LIST,
32 x_return_status OUT NOCOPY VARCHAR2,
33 x_msg_count OUT NOCOPY NUMBER,
34 x_msg_data OUT NOCOPY VARCHAR2
35 )
36 IS
37 l_api_name CONSTANT VARCHAR2(30) := 'copy_lgl_ctnt';
38 i PLS_INTEGER;
39 l_cntcount NUMBER;
40 l_context_id NUMBER;
41 l_from_deliverable_ids Ids_List;
42 l_deliverable_id NUMBER;
43
44 BEGIN
45 SAVEPOINT copy_logical_content;
46 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
47 IBE_Util.Debug('Copy_lg_ctnt: p_from_product_id'
48 || p_from_product_id);
49
50 END IF;
51
52 IF (p_from_context_ids is not null) then
53 l_cntCount := p_from_context_ids.count;
54 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
55 IBE_Util.Debug('l_cntCount='
56 || l_cntCount);
57
58 END IF;
59 end if;
60
61
62 FOR i in 1..l_cntcount LOOP
63 l_context_id := p_from_context_ids(i);
64 if (l_from_deliverable_ids is null ) then
65 l_from_deliverable_ids := IDS_LIST();
66 END IF;
67 l_from_deliverable_ids.extend();
68 BEGIN
69 select item_id
70 into l_deliverable_id
71 from ibe_dsp_obj_lgl_ctnt
72 where context_id = l_context_id
73 and object_id = p_from_product_id;
74 l_from_deliverable_ids(i) := l_deliverable_id;
75
76 EXCEPTION
77 WHEN NO_DATA_FOUND then
78 l_from_deliverable_ids(i) := null;
79 END;
80 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
81 IBE_Util.Debug('i='||i||' l_deliverable_id='
82 || l_deliverable_id);
83
84 END IF;
85
86
87 end LOOP;
88
89 copy_lgl_ctnt(
90 p_api_version ,
91 p_init_msg_list ,
92 p_commit ,
93 p_object_type_code ,
94 p_from_Product_id ,
95 p_from_Context_ids ,
96 l_from_deliverable_ids ,
97 p_to_product_ids ,
98 x_copy_status,
99 x_return_status ,
100 x_msg_count ,
101 x_msg_data
102 );
103
104 EXCEPTION
105 WHEN OTHERS THEN
106 --ROLLBACK TO copy_logical_content;
107 x_return_status := FND_API.g_ret_sts_unexp_error ;
108 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
109 THEN
110 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
111 END IF;
112 FND_MSG_PUB.count_and_get(
113 p_encoded => FND_API.g_false,
114 p_count => x_msg_count,
115 p_data => x_msg_data );
116
117
118 END copy_lgl_ctnt;
119
120 PROCEDURE copy_lgl_ctnt(
121 p_api_version IN NUMBER,
122 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
123 p_commit IN VARCHAR2 := FND_API.g_false,
124 p_object_type_code IN VARCHAR2,
125 p_from_Product_id IN NUMBER,
126 p_from_Context_ids IN Ids_List,
127 p_from_deliverable_ids IN Ids_List,
128 p_to_product_ids IN Ids_List,
129 x_copy_status OUT NOCOPY IDS_LIST,
130 x_return_status OUT NOCOPY VARCHAR2,
131 x_msg_count OUT NOCOPY NUMBER,
132 x_msg_data OUT NOCOPY VARCHAR2
133 )
134 IS
135
136 l_api_name CONSTANT VARCHAR2(30) := 'copy_lgl_ctnt';
137 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
138 l_return_status VARCHAR2(1);
139 l_index NUMBER ;
140 l_context_id NUMBER;
141 l_deliverable_id NUMBER := null;
142 l_exists NUMBER := null;
143 l_context_type VARCHAR2(100);
144 l_obj_lgl_ctnt_id NUMBER;
145 l_applicable_to VARCHAR2(40);
146 l_cntcount NUMBER;
147 l_object_type varchar2(1) := 'I';
148 l_to_count NUMBER;
149 l_to_product_id NUMBER;
150 l_ctnt_id NUMBER;
151 l_version_number NUMBER;
152 i PLS_INTEGER;
153 j PLS_INTEGER;
154 l_item_id NUMBER;
155
156 BEGIN
157 SAVEPOINT copy_logical_content;
158
159 if (p_from_context_ids is not null) then
160
161 l_cntCount := p_from_context_ids.count;
162 l_to_count := p_to_product_ids.count;
163
164 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
165 IBE_Util.Debug('from product context numbers:'||l_cntCount);
166 IBE_Util.Debug('to product numbers:'||l_to_Count);
167 END IF;
168
169 FOR i in 1..l_cntcount LOOP
170
171 l_context_id := p_from_context_ids(i);
172 l_deliverable_id := p_from_deliverable_ids(i);
173
174 if (x_copy_status is null ) then
175 x_copy_status := IDS_LIST();
176 END IF;
177 x_copy_status.extend();
178
179 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
180 IBE_Util.Debug('Content Component loop i:'||i);
181 IBE_Util.Debug('context_id=:'||l_context_id);
182 IBE_Util.Debug('deliverableid=:'||l_deliverable_id);
183
184 END IF;
185
186 SAVEPOINT copy_logical_content2;
187
188 for j in 1..l_to_count Loop
189
190 l_to_product_id := p_to_product_ids(j);
191
192 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
193 IBE_Util.Debug('Target Products loop j:'||j);
194 IBE_Util.Debug('toproductid=:'||l_to_product_id);
195 END IF;
196
197 BEGIN
198 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
199 IBE_Util.Debug('check if existing in the target product');
200 END IF;
201
202 select obj_lgl_ctnt_id, object_version_number,item_id
203 into l_ctnt_id, l_version_number, l_item_id
204 from ibe_dsp_obj_lgl_ctnt
205 where context_id = l_context_id
206 and object_id = l_to_product_id;
207
208 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
209 IBE_Util.Debug('target product Content rec exists l_ctnt_id='||l_ctnt_id || ' version='||l_version_number);
210 END IF;
211
212 -- update the existing record
213 IF (l_deliverable_id is null) then
214 DELETE FROM IBE_DSP_OBJ_LGL_ctnt
215 WHERE obj_lgl_ctnt_id = l_ctnt_id
216 AND object_version_number = l_version_number
217 AND object_type = l_object_type;
218
219 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
220 IBE_Util.Debug('delete if l_deliverable_id is null');
221 END IF;
222
223
224 ELSE
225 if (l_item_id <> l_deliverable_id) THEN
226 UPDATE IBE_DSP_OBJ_LGL_CTNT
227 SET LAST_UPDATE_DATE = SYSDATE,
228 LAST_UPDATED_BY = FND_GLOBAL.user_id,
229 LAST_UPDATE_LOGIN = FND_GLOBAL.user_id,
230 OBJECT_ID = l_to_product_id,
231 OBJECT_TYPE = l_object_type,
232 CONTEXT_id = l_context_id,
233 ITEM_id = l_deliverable_id ,
234 OBJECT_VERSION_NUMBER = l_version_number+1
235 WHERE OBJ_LGL_CTNT_id = l_ctnt_id
236 AND OBJECT_VERSION_NUMBER = l_version_number;
237
238 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
239 IBE_Util.Debug('update with the new l_deliverable_id is null');
240 END IF;
241 END IF; --updating the target product record
242
243 END IF; -- end if updating the product record
244 EXCEPTION
245 when no_data_found then
246 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
247 IBE_Util.Debug('target product content rec not exist, insert one if l_deliverable_id is null');
248 END IF;
249
250 IF (l_deliverable_id is not null) then
251 --insert new rec
252 OPEN obj_lgl_ctnt_id_seq;
253 FETCH obj_lgl_ctnt_id_seq INTO l_obj_lgl_ctnt_id;
254 CLOSE obj_lgl_ctnt_id_seq;
255
256 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
257 IBE_Util.Debug('begin insert new rec for target product, seq id='||l_obj_lgl_ctnt_id);
258 END IF;
259
260 INSERT INTO IBE_DSP_OBJ_LGL_CTNT (
261 OBJ_LGL_CTNT_ID,
262 OBJECT_VERSION_NUMBER,
263 LAST_UPDATE_DATE,
264 LAST_UPDATED_BY,
265 CREATION_DATE,
266 CREATED_BY,
267 LAST_UPDATE_LOGIN,
268 OBJECT_ID,
269 OBJECT_TYPE,
270 CONTEXT_ID,
271 ITEM_ID )
272 VALUES (
273 l_obj_lgl_ctnt_id,
274 1,
275 SYSDATE,
276 FND_GLOBAL.user_id,
277 SYSDATE,
278 FND_GLOBAL.user_id,
279 FND_GLOBAL.user_id,
280 l_to_product_id,
281 l_object_type,
282 l_context_id,
283 l_deliverable_id);
284
285 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
286 IBE_Util.Debug('done inserting, l_to_product_id'||l_to_product_id
287 ||' object_type='||l_object_type
288 ||' context_id='||l_context_id||' l_deveriableid='||l_deliverable_id);
289 END IF;
290
291 END IF; -- l_deliverable_id is not null
292
293 end; -- end inserting if target product content rec not exists
294
295 end LOOP;
296
297 x_copy_status(i) := 0; -- 0 success, -1 fail
298
299 END Loop;
300
301
302
303 end if;
304 x_return_status := FND_API.G_RET_STS_SUCCESS;
305
306 FND_MSG_PUB.count_and_get(
307 p_encoded => FND_API.g_false,
308 p_count => x_msg_count,
309 p_data => x_msg_data );
310
311 EXCEPTION
312 WHEN OTHERS THEN
313 ROLLBACK TO copy_logical_content2;
314 x_return_status := FND_API.g_ret_sts_unexp_error ;
315 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
316 THEN
317 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
318 END IF;
322 p_data => x_msg_data );
319 FND_MSG_PUB.count_and_get(
320 p_encoded => FND_API.g_false,
321 p_count => x_msg_count,
323
324
325 END copy_lgl_ctnt;
326
327 END IBE_copy_LogicalContent_GRP;