[Home] [Help]
PACKAGE BODY: APPS.IBC_BULKUPLOAD_PVT
Source
1 PACKAGE BODY IBC_BULKUPLOAD_PVT AS
2 /* $Header: ibcblkub.pls 120.1 2005/12/16 17:43 srrangar noship $ */
3 PROCEDURE write_log(p_statement IN VARCHAR2)
4 IS
5 BEGIN
6
7 Fnd_Message.SET_ENCODED(p_statement);
8 Fnd_Msg_Pub.ADD;
9 END;
10 -- --------------------------------------------------------------
11 -- UPSERT ITEM FULL
12 --
13 --
14 -- --------------------------------------------------------------
15 PROCEDURE upsert_item_full(
16 p_ctype_code IN VARCHAR2
17 ,p_citem_name IN VARCHAR2
18 ,p_dir_node_id IN NUMBER
19 ,p_owner_resource_id IN NUMBER
20 ,p_owner_resource_type IN VARCHAR2
21 ,p_trans_required IN VARCHAR2
22 ,p_start_date IN DATE
23 ,p_end_date IN DATE
24 ,p_attach_file_id IN NUMBER
25 ,p_item_renditions IN NUMBER
26 ,p_status IN VARCHAR2
27 ,p_language IN VARCHAR2
28 ,px_content_item_id IN OUT NOCOPY NUMBER
29 ,px_citem_ver_id IN OUT NOCOPY NUMBER
30 ,x_return_status OUT NOCOPY VARCHAR2
31 ,x_msg_count OUT NOCOPY NUMBER
32 ,x_msg_data OUT NOCOPY VARCHAR2
33 )IS
34
35 l_attribute_type_codes JTF_VARCHAR2_TABLE_100 := NULL;
36 l_attributes JTF_VARCHAR2_TABLE_32767 := NULL;
37 l_item_renditions JTF_NUMBER_TABLE := NULL;
38 l_object_version_number NUMBER := NULL;
39
40 BEGIN
41
42 IF p_item_renditions IS NOT NULL THEN
43 l_item_renditions := JTF_NUMBER_TABLE();
44 l_item_renditions.EXTEND();
45 l_item_renditions(1) := p_item_renditions;
46 END IF;
47
48 Ibc_Citem_Admin_Grp.upsert_item_full(
49 p_ctype_code => p_ctype_code
50 ,p_citem_name => p_citem_name
51 ,p_citem_description => 'Description of ' || p_citem_name
52 ,p_dir_node_id => p_dir_node_id
53 ,p_owner_resource_id => p_owner_resource_id
54 ,p_owner_resource_type => p_owner_resource_type
55 ,p_reference_code => NULL
56 ,p_trans_required => p_trans_required
57 ,p_parent_item_id => NULL
58 ,p_lock_flag => Fnd_Api.G_FALSE
59 ,p_wd_restricted => Fnd_Api.G_FALSE
60 ,p_start_date => p_start_date
61 ,p_end_date => p_end_date
62 ,p_attribute_type_codes => l_attribute_type_codes
63 ,p_attributes => l_attributes
64 ,p_attach_file_id => p_attach_file_id
65 ,p_item_renditions => l_item_renditions -- if the user chooses to upload as rendition l_cl_item_renditions
66 ,p_default_rendition => NULL
67 ,p_component_citems => NULL
68 ,p_component_citem_ver_ids => NULL
69 ,p_component_atypes => NULL
70 ,p_sort_order => NULL
71 ,p_keywords => NULL
72 ,p_status => p_status
73 ,p_log_action => Fnd_Api.G_TRUE
74 ,p_language => p_language
75 ,p_update => Fnd_Api.g_true
76 ,p_commit => Fnd_Api.G_FALSE
77 ,p_api_version_number => 1.0
78 ,p_init_msg_list => Fnd_Api.G_FALSE
79 ,px_content_item_id => px_content_item_id
80 ,px_citem_ver_id => px_citem_ver_id
81 ,px_object_version_number => l_object_version_number
82 ,x_return_status => x_return_status
83 ,x_msg_count => x_msg_count
84 ,x_msg_data => x_msg_data);
85
86 END upsert_item_full;
87
88 PROCEDURE detect_item_conflict(l_content_item_id IN OUT NOCOPY NUMBER
89 ,l_citem_version_id IN OUT NOCOPY NUMBER
90 ,l_content_item_name IN OUT NOCOPY VARCHAR2
91 ,l_dir_node_id IN NUMBER
92 ,l_user_option IN VARCHAR2)
93 IS
94
95 CURSOR cur_item_info IS
96 --query if the content item exists
97 SELECT a.content_item_id
98 ,a.CITEM_VERSION_ID
99 ,a.CITEM_VERSION_STATUS
100 ,a.version_number
101 FROM
102 ibc_citem_versions_vl a,
103 ibc_content_items b
104 WHERE
105 a.content_item_id=b.content_item_id AND
106 b.directory_node_id = l_dir_node_id AND
107 a.content_item_name = l_content_item_name AND
108 a.version_number = (SELECT MAX(version_number) FROM ibc_citem_versions_b c
109 WHERE c.content_item_id = a.content_item_id);
110
111 l_citem_version_status VARCHAR2(30);
112 l_citem_version_number NUMBER;
113
114 BEGIN
115
116 OPEN cur_item_info;
117 FETCH cur_item_info INTO l_content_item_id,l_citem_version_id,l_citem_version_status,l_citem_version_number;
118 CLOSE cur_item_info;
119
120 IF l_citem_version_id IS NOT NULL THEN
121
122 IF l_user_option='IBC_UPLOAD_ITEMEXIST_OVERWRITE' THEN
123 IF l_citem_version_status IN ('SUBMITTED','APPROVED') THEN
124 l_citem_version_id := NULL;
125 END IF;
126 ELSIF l_user_option='IBC_UPLOAD_ITEMEXIST_VERSION' THEN
127 l_citem_version_id := NULL;
128 ELSIF l_user_option='IBC_UPLOAD_ITEMEXIST_SKIP' THEN
129 --
130 -- skip creating content item Don't do anything
131 --
132 NULL;
133 ELSIF l_user_option='IBC_UPLOAD_ITEMEXIST_NEW' THEN
134 l_citem_version_id := NULL;
135 l_content_item_id := NULL;
136 l_content_item_name := l_content_item_name||TO_CHAR(SYSDATE,'jHHmiSSSSS');
137 END IF;
138
139 END IF;
140
141
142 END;
143
144
145
146 PROCEDURE BULKUPLOAD_PROCESS(p_bulkupload_id IN NUMBER
147 ,x_return_status OUT NOCOPY VARCHAR2
148 ,x_msg_count OUT NOCOPY NUMBER
149 ,x_msg_data OUT NOCOPY VARCHAR2)
150 IS
151
152 l_content_type_code VARCHAR2(240);
153 l_content_item_name VARCHAR2(240);
154 l_dir_node_id NUMBER;
155 l_owner_resource_id NUMBER;
156 l_owner_resource_type VARCHAR2(240);
157 l_trans_required CHAR(1);
158 l_start_date DATE;
159 l_end_date DATE;
160 l_attach_file_id NUMBER;
161 l_citem_version_status VARCHAR2(30);
162 l_language_code CHAR(2);
163 l_content_item_id NUMBER;
164 l_citem_version_id NUMBER;
165
166 x_wf_item_key VARCHAR2(240);
167
168 l_count NUMBER;
169 l_msg VARCHAR2(4000);
170 l_access_control CHAR(1);
171 l_user_option VARCHAR2(100);
172 l_item_renditions NUMBER;
173 l_temp NUMBER;
174
175
176 CURSOR cur_bulk_item_process IS
177 SELECT --a.ROWID
178 a.content_item_id
179 ,citem_version_id
180 ,content_type_code
181 ,content_item_name
182 ,directory_node_id
183 ,owner_resource_id
184 ,owner_resource_type
185 ,translation_required_flag
186 ,start_date
187 ,end_date
188 ,attachment_file_id
189 ,'IBC_UPLOAD_ITEM_STATUS_SUBMIT' citem_version_status
190 ,'US' language_code
191 ,NULL content_creation_status
192 ,'IBC_UPLOAD_ITEMEXIST_NEW' user_option_for_item
193 ,'IBC_UPLOAD_FILEAS_RENDITION' citem_upload_as
194 FROM ibc_content_items a, ibc_citem_versions_vl b
195 WHERE content_type_code='IBC_FILE'
196 AND a.content_item_id=b.content_item_id
197 AND citem_version_id=16799;
198
199 BEGIN
200
201 l_access_control := NVL(Fnd_Profile.Value_specific('IBC_USE_ACCESS_CONTROL',-999,-999,-999), 'N');
202
203 -- ---------------------------------
204 -- I N I T I A L I Z E
205 -- ---------------------------------
206 --
207 --
208 --
209 --
210 --
211
212 FOR i_rec IN cur_bulk_item_process
213 LOOP
214 -- -----------------------------------------
215 -- C R E A T E C O N T E N T I T E M
216 -- -----------------------------------------
217
218 write_log('Creating Content Item content item .. ');
219
220 l_content_type_code := i_rec.content_type_code;
221 l_content_item_name := i_rec.content_item_name;
222 l_dir_node_id := i_rec.directory_node_id;
223 l_owner_resource_id := i_rec.owner_resource_id;
224 l_owner_resource_type := i_rec.owner_resource_type;
225 l_trans_required := i_rec.translation_required_flag;
226 l_start_date := i_rec.start_date;
227 l_end_date := i_rec.end_date;
228 l_citem_version_status := i_rec.citem_version_status;
229 l_language_code := i_rec.language_code;
230 l_content_item_id := i_rec.content_item_id;
231 l_citem_version_id := i_rec.citem_version_id;
232 l_user_option := i_rec.user_option_for_item;
233 l_item_renditions := NULL;
234 l_attach_file_id := NULL;
235
236 IF (i_rec.citem_upload_as = 'IBC_UPLOAD_FILEAS_RENDITION') THEN
237 l_item_renditions := i_rec.attachment_file_id;
238 ELSE
239 l_attach_file_id := i_rec.attachment_file_id;
240 END IF;
241
242 Fnd_Global.apps_initialize(l_owner_resource_id, 23812, 549);
243
244 -- ---------------------------------
245 -- I N I T I A L I Z E
246 -- ---------------------------------
247 -- Detect conflict
248 --
249 --
250 --
251 --
252
253 detect_item_conflict(l_content_item_id
254 ,l_citem_version_id
255 ,l_content_item_name
256 ,l_dir_node_id
257 ,l_user_option);
258
259
260 IF ((l_access_control = 'N') AND (i_rec.citem_version_status IN ('IBC_UPLOAD_ITEM_STATUS_SUBMIT'))) THEN
261 -- access control is off and user submits the item for approval
262 -- it should be auto approved
263 l_citem_version_status := 'APPROVED';
264 ELSE
265 l_citem_version_status := 'INPROGRESS';
266 END IF;
267
268 write_log('.. Item id to upsert' || l_content_item_id || ' ; Version id = '||l_citem_version_id ||';'|| l_user_option);
269
270
271 upsert_item_full(
272 p_ctype_code => l_content_type_code
273 ,p_citem_name => l_content_item_name
274 ,p_dir_node_id => l_dir_node_id
275 ,p_owner_resource_id => l_owner_resource_id
276 ,p_owner_resource_type => l_owner_resource_type
277 ,p_trans_required => l_trans_required
278 ,p_start_date => l_start_date
279 ,p_end_date => l_end_date
280 ,p_attach_file_id => l_attach_file_id
281 ,p_item_renditions => l_item_renditions
282 ,p_status => l_citem_version_status
283 ,p_language => l_language_code
284 ,px_content_item_id => l_content_item_id
285 ,px_citem_ver_id => l_citem_version_id
286 ,x_return_status => x_return_status
287 ,x_msg_count => x_msg_count
288 ,x_msg_data => x_msg_data);
289
290 write_log('Content Item created .. return status = '|| x_return_status );
291 write_log('Content Item created .. Item id = ' || l_content_item_id || ' ; Version id = '||l_citem_version_id);
292
293
294
295
296 write_log('Trying to approve the item created');
297 write_log('i_rec.citem_version_status ' || i_rec.citem_version_status);
298
299 IF ((l_access_control = 'Y') AND (i_rec.citem_version_status IN ('IBC_UPLOAD_ITEM_STATUS_SUBMIT'))) THEN
300 Ibc_Citem_Workflow_Pvt.Submit_For_Approval(
301 p_citem_ver_id => l_citem_version_id
302 ,px_object_version_number => l_temp
303 ,x_wf_item_key => x_wf_item_key
304 ,x_return_status => x_return_status
305 ,x_msg_count => x_msg_count
306 ,x_msg_data => x_msg_data
307 );
308
309 write_log('Approve Item .. return status = '|| x_return_status );
310 write_log(' Version id = '||l_citem_version_id);
311
312 END IF;
313
314
315 -- FOR i IN 1 .. l_count LOOP
316 -- l_msg := Fnd_Msg_Pub.get(i,Fnd_Api.G_FALSE);
317 -- write_log('(' || i || ') ' || l_msg);
318 -- END LOOP;
319 --
320 -- --Fnd_Msg_Pub.Delete_Msg;
321
322 -- UPDATE IBC_BULK_UPLOAD SET content_item_id=l_content_item_id
323 -- WHERE ROWID=i_rec.ROWID;
324
325 END LOOP;
326
327
328
329 END BULKUPLOAD_PROCESS;
330
331 END Ibc_Bulkupload_Pvt;