[Home] [Help]
PACKAGE BODY: APPS.ZPB_DC_DISTRIBUTE
Source
1 PACKAGE BODY ZPB_DC_DISTRIBUTE AS
2 /* $Header: ZPBDCDBB.pls 120.4 2007/12/04 14:33:04 mbhat noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ZPB_DC_DISTRIBUTE';
5
6 /*=========================================================================+
7 | PROCEDURE submit_distrib_requests_cp
8 |
9 | DESCRIPTION
10 | Procedure calls distribute_data_cp and pass in necessary
11 | parameters.
12 |
13 +=========================================================================*/
14 PROCEDURE submit_distrib_requests_cp
15 (
16 errbuf OUT NOCOPY VARCHAR2,
17 retcode OUT NOCOPY VARCHAR2
18 )
19 IS
20 l_api_name CONSTANT VARCHAR2(30) := 'submit_distrib_requests_cp';
21 l_api_version CONSTANT NUMBER := 1.0 ;
22 --
23 l_error_api_name VARCHAR2(2000);
24 l_return_status VARCHAR2(1) ;
25 l_msg_count NUMBER ;
26 l_msg_data VARCHAR2(2000) ;
27 l_msg_index_out NUMBER;
28 --
29 l_user_id NUMBER;
30 l_business_area_id NUMBER;
31 l_SID NUMBER;
32 l_serial_no NUMBER;
33 l_sess_user VARCHAR2(200);
34 l_os_user VARCHAR2(200);
35 l_status VARCHAR2(200);
36 l_schema_name VARCHAR2(200);
37 l_machine VARCHAR2(200);
38 l_req_id NUMBER;
39 l_resp_id NUMBER;
40 l_expired_user_exists BOOLEAN;
41
42
43 CURSOR dist_pending_csr IS
44 SELECT distinct object_user_id,
45 business_area_id
46 FROM ZPB_DC_OBJECTS
47 WHERE status = 'DISTRIBUTION_PENDING'
48 --AND object_user_id = 1009108
49 --AND business_area_id = 22
50 AND object_type in ('W','C');
51
52
53
54 BEGIN
55
56 zpb_log.WRITE(l_api_name ,'Starting execution ');
57
58 l_expired_user_exists := FALSE;
59
60 FOR l_dist_pending_row_rec IN dist_pending_csr
61 LOOP
62 l_user_id := l_dist_pending_row_rec.object_user_id;
63 l_business_area_id := l_dist_pending_row_rec.business_area_id;
64 --Check if the user's AW is attached read/write
65 zpb_personal_aw.personal_aw_rw_scan(p_user => TO_CHAR(l_user_id),
66 p_business_area => l_business_area_id,
67 p_SID => l_SID,
68 p_serial_no => l_serial_no,
69 p_sess_user => l_sess_user,
70 p_os_user => l_os_user,
71 p_status => l_status,
72 p_schema_name => l_schema_name,
73 p_machine => l_machine);
74
75
76 IF (l_SID is NULL OR l_SID = 0) THEN
77 --We know that user's AW is now available for distribution
78 --Submit a concurrent request
79 zpb_log.WRITE_STATEMENT(l_api_name ,'Submit Conc Request for user=' || l_user_id ||' Buss Area= ' || l_business_area_id );
80 --
81 BEGIN
82 SELECT fugp.responsibility_id
83 INTO l_resp_id
84 FROM fnd_user_resp_groups fugp, fnd_responsibility fr
85 WHERE responsibility_application_id = 210
86 AND fugp.responsibility_id = fr.responsibility_id
87 AND user_id = l_user_id
88 AND responsibility_key IN ('ZPB_CONTROLLER_RESP', 'ZPB_SUPER_CONTROLLER_RESP', 'ZPB_ANALYST_RESP')
89 AND ROWNUM = 1;
90
91
92 --fnd_global.apps_initialize(1009109,l_resp_id,210);
93 fnd_global.apps_initialize(l_user_id,l_resp_id,210);
94
95 l_req_id := FND_REQUEST.SUBMIT_REQUEST ('ZPB',
96 'ZPB_DC_DATA_DISTRIB_REQ', NULL, NULL, FALSE,
97 l_user_id,l_business_area_id);
98
99 zpb_log.WRITE_STATEMENT(l_api_name ,'Conc Request id for user =' || l_req_id);
100 COMMIT;
101
102 EXCEPTION
103 WHEN NO_DATA_FOUND
104 THEN
105 l_expired_user_exists := TRUE;
106 zpb_log.WRITE_TO_CONCMGR_LOG_TR('ZPB_DC_EXPIRED_USER',
107 'USER_NAME',
108 ZPB_WF_NTF.ID_to_FNDUser(l_user_id));
109 END;
110
111 END IF;
112
113
114 END LOOP;
115
116 zpb_log.WRITE_STATEMENT(l_api_name ,'Return Status=' || l_return_status);
117 zpb_log.WRITE(l_api_name ,'Execution end');
118
119 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
120 raise FND_API.G_EXC_ERROR;
121 END IF;
122
123
124 IF l_expired_user_exists
125 THEN
126 retcode := '1';
127 ELSE
128 retcode := '0';
129 END IF;
130
131 COMMIT;
132 RETURN;
133
134 EXCEPTION
135
136 WHEN FND_API.G_EXC_ERROR THEN
137 retcode := '2' ;
138 errbuf:=substr(sqlerrm, 1, 255);
139
140 WHEN OTHERS THEN
141 retcode := '2' ;
142 errbuf:=substr(sqlerrm, 1, 255);
143 END submit_distrib_requests_cp ;
144
145
146 /*=========================================================================+
147 | PROCEDURE distribute_data_cp
148 |
149 | DESCRIPTION
150 | Procedure calls distribute_data_cp and pass in necessary
151 | parameters.
152 |
153 +=========================================================================*/
154
155
156 PROCEDURE distribute_data_cp( errbuf OUT NOCOPY VARCHAR2,
157 retcode OUT NOCOPY VARCHAR2,
158 p_user_id IN VARCHAR2,
159 p_business_area_id IN NUMBER
160 )
161
162 IS
163 l_api_name CONSTANT VARCHAR2(30) := 'distribute_data_cp';
164 l_api_version CONSTANT NUMBER := 1.0 ;
165 --
166 l_conc_request_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
167 l_SID NUMBER;
168 l_serial_no NUMBER;
169 l_sess_user VARCHAR2(200);
170 l_os_user VARCHAR2(200);
171 l_status VARCHAR2(200);
172 l_schema_name VARCHAR2(200);
173 l_machine VARCHAR2(200);
174 l_return_status VARCHAR2(1);
175 l_msg_count NUMBER;
176 l_msg_data VARCHAR2(2000);
177 l_currency_view VARCHAR2(2000);
178 l_currency_profile_option VARCHAR2(2000);
179 l_template_id NUMBER;
180 l_distributor_user_id NUMBER;
181 l_copy_instance_data_flag VARCHAR2(1);
182 l_copy_target_data_flag VARCHAR2(1);
183 l_create_inst_meas_flag VARCHAR2(1);
184 l_create_solve_prg_flag VARCHAR2(1);
185 l_command VARCHAR2(2000);
186 l_view_type VARCHAR2(15);
187 l_object_type VARCHAR2(1);
188 l_object_id NUMBER;
189 l_currency_flag VARCHAR2(1);
190
191 CURSOR dist_pending_csr IS
192 SELECT template_id,distributor_user_id,
193 copy_instance_data_flag,
194 copy_target_data_flag,
195 view_type,
196 create_instance_measures_flag,
197 create_solve_program_flag,
198 object_type,
199 object_id,
200 currency_flag,
201 ac_instance_id,
202 generate_template_task_id
203 FROM zpb_dc_objects
204 WHERE status = 'DISTRIBUTION_PENDING'
205 AND object_type in ('W','C')
206 AND object_user_id = p_user_id
207 AND business_area_id = p_business_area_id;
208
209
210 BEGIN
211 zpb_log.WRITE(l_api_name ,'Starting execution ');
212 zpb_log.WRITE_STATEMENT(l_api_name ,'Processing for user=' || p_user_id ||' Buss Area= ' || p_business_area_id );
213
214 --Check if any other user has attached the user's AW r/w
215 zpb_personal_aw.personal_aw_rw_scan(p_user => TO_CHAR(p_user_id),
216 p_business_area => p_business_area_id,
217 p_SID => l_SID,
218 p_serial_no => l_serial_no,
219 p_sess_user => l_sess_user,
220 p_os_user => l_os_user,
221 p_status => l_status,
222 p_schema_name => l_schema_name,
223 p_machine => l_machine);
224
225
226 IF (l_SID is NULL OR l_SID = 0) THEN
227 --We know that user's AW is now available for distribution
228 --Do distribution
229 zpb_log.WRITE_STATEMENT(l_api_name ,'User AW available');
230 --Initialise User AW and attach read write
231 --ZPB_AW.INITIALIZE_USER(p_api_version => 1.0,
232 -- p_init_msg_list => FND_API.G_FALSE,
233 -- p_validation_level => p_validation_level,
234 -- x_return_status => x_return_status,
235 -- x_msg_count => x_msg_count,
236 -- x_msg_data => x_msg_data,
237 -- p_user => p_user_id,
238 -- p_business_area_id => l_business_area_id,
239 -- p_attach_readwrite => FND_API.G_TRUE,
240 -- p_sync_shared => FND_API.G_TRUE);
241
242
243 --Initialize context
244 ZPB_SECURITY_CONTEXT.INITCONTEXT(p_user_id => p_user_id,
245 p_shadow_id => p_user_id,
246 p_resp_id => 1 ,
247 p_session_id => 1 ,
248 p_business_area_id => p_business_area_id);
249 --Start up user's AW
250 ZPB_PERSONAL_AW.STARTUP(p_api_version => 1.0,
251 p_init_msg_list => FND_API.G_FALSE,
252 p_commit => FND_API.G_TRUE,
253 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
254 x_return_status => l_return_status,
255 x_msg_count => l_msg_count,
256 x_msg_data => l_msg_data,
257 p_user => p_user_id,
258 p_read_only => FND_API.G_FALSE);
259
260 zpb_log.WRITE_STATEMENT(l_api_name ,'Start up return status =' || l_return_status);
261 zpb_log.WRITE_STATEMENT(l_api_name ,'Executed Startup');
262 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
263 raise FND_API.G_EXC_ERROR;
264 END IF;
265
266 --Update the ZPB_USERS table with the conc req id
267 UPDATE zpb_users
268 SET conc_request_id = l_conc_request_id,
269 conc_request_start_time = SYSDATE,
270 -- WHO columns
271 last_update_date = SYSDATE,
272 last_updated_by = fnd_global.user_id,
273 last_update_login = fnd_global.LOGIN_ID
274 WHERE user_id = p_user_id
275 AND business_area_id = p_business_area_id;
276
277 COMMIT;
278
279 --Get the currency profile option for the user
280 l_currency_profile_option := FND_PROFILE.VALUE_SPECIFIC('ZPB_DEF_CURR_VIEW',p_user_id);
281 zpb_log.WRITE_STATEMENT(l_api_name ,'Users currency profile =' || l_currency_profile_option);
282
283 --For each row that has distribution pending for this user do a distribute
284
285 FOR l_dist_pending_row_rec IN dist_pending_csr
286 LOOP
287 --reset variable
288 l_currency_view := l_currency_profile_option;
289 l_template_id := l_dist_pending_row_rec.template_id;
290 l_distributor_user_id := l_dist_pending_row_rec.distributor_user_id;
291 l_copy_instance_data_flag := l_dist_pending_row_rec.copy_instance_data_flag;
292 l_copy_target_data_flag := l_dist_pending_row_rec.copy_target_data_flag;
293 l_view_type := l_dist_pending_row_rec.view_type;
294 l_create_inst_meas_flag := l_dist_pending_row_rec.create_instance_measures_flag;
295 l_create_solve_prg_flag := l_dist_pending_row_rec.create_solve_program_flag;
296 l_object_type := l_dist_pending_row_rec.object_type;
297 l_object_id := l_dist_pending_row_rec.object_id;
298 l_currency_flag := l_dist_pending_row_rec.currency_flag;
299
300 IF (l_currency_flag = 'Y') THEN
301 IF (l_create_inst_meas_flag <> 'Y') THEN
302 --this a redistribution so use the current currency view for the worksheet
303 l_currency_view := l_view_type;
304 ELSE
305 --this is a new distribution
306 IF(l_currency_profile_option is NULL OR l_currency_profile_option = 'SAME') THEN
307 --use the distributors currency
308 IF(l_object_type = 'W' AND l_distributor_user_id <> -100) THEN
309 SELECT view_type
310 INTO l_currency_view
311 FROM zpb_dc_objects
312 WHERE template_id = l_template_id
313 AND object_user_id = l_distributor_user_id
314 AND object_type in ('W','C');
315 END IF;
316 END IF;
317 END IF;
318 END IF;
319
320
321 IF (l_currency_view is NULL OR l_currency_view ='SAME') THEN
322 l_currency_view := 'BASE';
323 END IF;
324
325
326 -- dbms_aw.execute('cm.logfile=''KGOYAL/dis.log''');
327
328 IF(l_create_inst_meas_flag = 'Y' or l_create_solve_prg_flag = 'Y') THEN
329 --Need to create structures
330 l_command := 'call dc.distribute(' || '''' || l_template_id || '''' ||
331 ',' || '''' || l_distributor_user_id || '''' ||
332 ',' || '''' || p_user_id || '''' ||
333 ',' || '''' || l_copy_instance_data_flag || '''' ||
334 ',' || '''' || l_copy_target_data_flag || '''' ||
335 ',' || '''' || 'S' || '''' ||
336 ',' || '''' || l_currency_view || '''' ||
337 ')';
338 zpb_log.WRITE_STATEMENT(l_api_name ,'Create Structure Command =' || l_command);
339 zpb_aw.EXECUTE(l_command);
340 zpb_log.WRITE_STATEMENT(l_api_name ,'Structure Created');
341 END IF;
342
343 --Do data distribution
344 l_command := 'call dc.distribute(' || '''' || l_template_id || '''' ||
345 ',' || '''' || l_distributor_user_id || '''' ||
346 ',' || '''' || p_user_id || '''' ||
347 ',' || '''' || l_copy_instance_data_flag || '''' ||
348 ',' || '''' || l_copy_target_data_flag || '''' ||
349 ',' || '''' || 'D' || '''' ||
350 ',' || '''' || l_currency_view || '''' ||
351 ')';
352 zpb_log.WRITE_STATEMENT(l_api_name ,'Distribute Data Command =' || l_command);
353 zpb_aw.EXECUTE(l_command);
354 zpb_log.WRITE_STATEMENT(l_api_name ,'Distribute Distributed');
355
356 l_command := 'call sv.exe.dcsolve('''||l_template_id||''' '''||
357 l_dist_pending_row_rec.ac_instance_id||''' '''||
358 l_dist_pending_row_rec.generate_template_task_id||''')';
359
360 zpb_log.WRITE_STATEMENT(l_api_name ,'Recalc Command =' || l_command);
361 zpb_aw.EXECUTE(l_command);
362 zpb_log.WRITE_STATEMENT(l_api_name ,'Recalculated');
363
364 --Commit data to AW
365 l_command := 'call pa.commit()';
366 zpb_aw.EXECUTE(l_command);
367 zpb_log.WRITE_STATEMENT(l_api_name ,'Data Committed');
368
369 --Update ZPB_DC_OBJECTS with the currency
370 IF (l_currency_flag = 'Y') THEN
371 UPDATE zpb_dc_objects
372 SET view_type = l_currency_view,
373 -- WHO columns
374 last_update_date = SYSDATE,
375 last_updated_by = fnd_global.user_id,
376 last_update_login = fnd_global.LOGIN_ID
377 WHERE template_id = l_template_id
378 AND object_user_id = p_user_id;
379 END IF;
380 COMMIT;
381
382 END LOOP;
383
384 --All distribution is done
385 --DO the AW clean up
386 --
387 ZPB_AW.clean_workspace (
388 p_api_version => 1.0,
389 p_init_msg_list => FND_API.G_FALSE,
390 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
391 x_return_status => l_return_status,
392 x_msg_count => l_msg_count,
393 x_msg_data => l_msg_data);
394
395
396
397
398 --Update ZPB_USERS and remove the request id
399 UPDATE zpb_users
400 SET conc_request_id = NULL,
401 conc_request_start_time = NULL,
402 -- WHO columns
403 last_update_date = SYSDATE,
404 last_updated_by = fnd_global.user_id,
405 last_update_login = fnd_global.LOGIN_ID
406 WHERE user_id = p_user_id
407 AND business_area_id = p_business_area_id;
408
409 END IF;
410 zpb_log.WRITE(l_api_name ,'Execution end');
411 retcode := '0';
412 COMMIT;
413 RETURN;
414
415 EXCEPTION
416
417 WHEN FND_API.G_EXC_ERROR THEN
418 retcode := '2' ;
419 errbuf:=substr(sqlerrm, 1, 255);
420
421 WHEN OTHERS THEN
422 retcode := '2' ;
423 errbuf:=substr(sqlerrm, 1, 255);
424 END distribute_data_cp ;
425 END ZPB_DC_DISTRIBUTE;