[Home] [Help]
PACKAGE BODY: APPS.ZPB_AW_WRITE_BACK
Source
1 PACKAGE BODY ZPB_AW_WRITE_BACK AS
2 /* $Header: zpbwriteback.plb 120.10 2007/12/05 12:53:22 mbhat ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(17) := 'zpb_aw_write_back';
5
6 ------------------------------------------------------------------------------
7 -- INITIALIZE - Initializes the session by attaching the AW's and setting the
8 -- context for a given business area
9 ------------------------------------------------------------------------------
10 PROCEDURE INITIALIZE (p_user_name IN VARCHAR2,
11 p_business_area_id IN NUMBER,
12 p_return_status OUT NOCOPY VARCHAR2,
13 p_msg_data OUT NOCOPY VARCHAR2)
14 is
15 l_msg_count NUMBER;
16 l_user_id NUMBER;
17 begin
18 select USER_ID into l_user_id from FND_USER where USER_NAME = p_user_name;
19
20 ZPB_AW.INITIALIZE (p_api_version => 1.0,
21 p_init_msg_list => FND_API.G_TRUE,
22 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
23 x_return_status => p_return_status,
24 x_msg_count => l_msg_count,
25 x_msg_data => p_msg_data,
26 p_business_area_id => p_business_area_id,
27 p_shadow_id => l_user_id,
28 p_shared_rw => FND_API.G_TRUE,
29 p_annot_rw => FND_API.G_FALSE);
30
31 end INITIALIZE;
32
33 ------------------------------------------------------------------------------
34 -- L_LOG_USER - Procedure that logs various parameters about the CR
35 --
36 ------------------------------------------------------------------------------
37 PROCEDURE l_log_user (P_TASK IN VARCHAR2,
38 P_USER IN VARCHAR2,
39 P_RESP IN VARCHAR2,
40 P_ORDER IN NUMBER,
41 P_QDR IN VARCHAR2)
42 IS
43 BEGIN
44 ZPB_LOG.WRITE_EVENT_TR ('zpb_aw_write_back.l_log_user',
45 'ZPB_WRITEMGR_PROCESS_TASK',
46 'TASK_NUMBER', to_char(p_task));
47 ZPB_LOG.WRITE_EVENT_TR ('zpb_aw_write_back.l_log_user',
48 'ZPB_WRITEMGR_SUBMITTED_BY',
49 'USER', p_user,
50 'RESP', p_resp);
51 ZPB_LOG.WRITE_EVENT_TR ('zpb_aw_write_back.l_log_user',
52 'ZPB_WRITEMGR_EXECUTE',
53 'ORDER', to_char(p_order));
54 ZPB_LOG.WRITE_EVENT_TR ('zpb_aw_write_back.l_log_user',
55 'ZPB_WRITEMGR_PROCEDURE',
56 'PROC', p_qdr);
57 END l_log_user;
58
59 ------------------------------------------------------------------------------
60 -- L_REMOVE_OLD_RECORDS - Procedure that updates ZPB_WRITEBACK_TASKS after
61 -- a CR has been run
62 --
63 ------------------------------------------------------------------------------
64 PROCEDURE l_remove_old_records
65 IS
66 default_num_of_days number := 30;
67 prof_num_of_days varchar2(255);
68 num_of_days number;
69 rcd_ct number;
70 prof_name varchar2(35);
71 l_aws varchar2(256);
72 BEGIN
73 zpb_log.write ('ZPB_AW_WRITE_BACK.l_remove_old_records','Begin program :');
74
75 prof_name := 'ZPB_WRITEBACK_TABLE_NUMBER_OF_DAYS';
76 fnd_profile.get(prof_name, prof_num_of_days);
77 if prof_num_of_days is null then
78 num_of_days := default_num_of_days;
79 else
80 num_of_days := to_number(prof_num_of_days);
81 end if;
82
83 select count(*) into rcd_ct
84 from ZPB_WRITEBACK_TASKS
85 where (status = COMPLETED or status = FAILED)
86 and completion_date <= (SYSDATE - num_of_days);
87
88 if rcd_ct > 0 then
89 ZPB_LOG.WRITE_EVENT_TR ('zpb_aw_write_back.l_remove_old_records',
90 'ZPB_WRITEMGR_CLEANUP',
91 'DATE', (SYSDATE - (num_of_days - 1)));
92
93 delete from ZPB_WRITEBACK_TASKS
94 where (status = COMPLETED or status = FAILED)
95 and completion_date <= (SYSDATE - num_of_days);
96 end if;
97 zpb_log.write ('ZPB_AW_WRITE_BACK.l_remove_old_records','End program :');
98 END l_remove_old_records;
99
100 ------------------------------------------------------------------------------
101 -- SUBMIT_WRITEBACK_REQUEST - Submits the writeback request
102 --
103 ------------------------------------------------------------------------------
104 PROCEDURE submit_writeback_request ( P_BUSINESS_AREA_ID IN NUMBER,
105 P_USER_ID IN NUMBER,
106 P_RESP_ID IN NUMBER,
107 P_SESSION_ID IN NUMBER,
108 P_TASK_TYPE IN VARCHAR2,
109 P_SPL IN VARCHAR2,
110 P_START_TIME IN DATE,
111 P_OUTVAL OUT NOCOPY Number )
112 IS
113 req_id NUMBER;
114 req_nm ZPB_REQUESTS.req_name%type;
115 resp_name FND_RESPONSIBILITY_VL.RESPONSIBILITY_NAME%type;
116 zpb_user FND_USER.USER_NAME%type;
117 stmt_ln NUMBER;
118 cmd_str VARCHAR2(32767);
119 spl_stmt ZPB_WRITEBACK_TRANSACTION.QDR%type;
120 str_ptr NUMBER;
121 ctr NUMBER;
122 tsk_seq NUMBER;
123 delim varchar2(1);
124
125 -- Added for Bug: 5475982
126 l_conc_request_id NUMBER;
127
128 BEGIN
129
130 ZPB_ERROR_HANDLER.INITIALIZE;
131
132 -- Added for Bug: 5475982
133 l_conc_request_id := fnd_global.conc_request_id;
134
135 SELECT distinct(responsibility_name) into resp_name
136 FROM FND_RESPONSIBILITY_VL
137 WHERE responsibility_id = P_RESP_ID;
138
139 SELECT distinct(user_name) into zpb_user
140 from FND_USER
141 where user_id = P_USER_ID;
142
143 select req_name
144 into req_nm
145 from ZPB_REQUESTS
146 where req_task_type = P_TASK_TYPE;
147
148 select zpb_writeback_seq.nextval into tsk_seq from dual;
149
150 INSERT INTO zpb_writeback_tasks (task_type, business_area_id, user_id,
151 session_id, task_seq, status, resp_id,
152 submit_date,
153 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
154 LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
155 VALUES (P_TASK_TYPE, p_business_area_id, zpb_user,
156 P_SESSION_ID, tsk_seq,
157 PENDING, resp_name, P_START_TIME,
158 fnd_global.USER_ID, SYSDATE, fnd_global.USER_ID,
159 SYSDATE, fnd_global.LOGIN_ID);
160
161 cmd_str := P_SPL;
162 stmt_ln := lengthb(cmd_str);
163 ctr := 0;
164 delim := ';';
165 loop
166 ctr := ctr + 1;
167 str_ptr := instrb(cmd_str, delim);
168 exit when str_ptr = 0 or str_ptr is null;
169 spl_stmt := substrb(cmd_str, 0, str_ptr - 1);
170 INSERT INTO zpb_writeback_transaction
171 (task_seq, exec_order, qdr,
172 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
173 LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
174 VALUES (tsk_seq, ctr, spl_stmt,
175 fnd_global.USER_ID, SYSDATE, fnd_global.USER_ID,
176 SYSDATE, fnd_global.LOGIN_ID);
177
178 cmd_str := substrb(cmd_str, (str_ptr - stmt_ln),
179 (stmt_ln - str_ptr));
180 stmt_ln := lengthb(cmd_str);
181 end loop;
182
183 -- handles the condition when no delimiter is at the end of cmd_str
184 if cmd_str is not null then
185 INSERT INTO zpb_writeback_transaction
186 (task_seq, exec_order, qdr,
187 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
188 LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
189 VALUES (tsk_seq, ctr, cmd_str,
190 fnd_global.USER_ID, SYSDATE, fnd_global.USER_ID,
191 SYSDATE, fnd_global.LOGIN_ID);
192 end if;
193
194 -- Fix for Bug: 5475982
195 -- If this procedure is invoked from an interactive UI session or
196 -- for User Maintanence task only then the following Conc. program
197 -- should be launched otherwise it will be bundled for all
198 -- users/rules and launched once.
199
200 IF l_conc_request_id < 0 OR p_task_type = 'UM' THEN
201 IF req_nm = 'ZPB_DO_WRTBK' THEN
202 req_id := FND_REQUEST.SUBMIT_REQUEST ('ZPB',
203 req_nm,
204 null,
205 TO_CHAR(P_START_TIME, 'DD-MON-YYYY HH24:MI:SS'),
206 FALSE,
207 tsk_seq,
208 null,
209 P_BUSINESS_AREA_ID);
210 ELSE
211 req_id := FND_REQUEST.SUBMIT_REQUEST ('ZPB',
212 req_nm,
213 null,
214 TO_CHAR(P_START_TIME, 'DD-MON-YYYY HH24:MI:SS'),
215 FALSE,
216 tsk_seq,
217 P_BUSINESS_AREA_ID);
218 END IF;
219
220 END IF;
221
222 P_OUTVAL := req_id;
223
224 EXCEPTION
225 when others then
226 ZPB_ERROR_HANDLER.HANDLE_EXCEPTION (G_PKG_NAME,
227 'submit_writeback_request');
228
229 END submit_writeback_request;
230
231 PROCEDURE process_cleanup ( ERRBUF OUT NOCOPY VARCHAR2,
232 RETCODE OUT NOCOPY VARCHAR2,
233 P_TASK_SEQ IN NUMBER,
234 P_BUSINESS_AREA_ID IN NUMBER)
235 is
236 errNum number;
237 aw_attached boolean := FALSE;
238 last_task number;
239 l_aws varchar2(256);
240 l_dataAw varchar2(32);
241 l_annotAw varchar2(32);
242
243 cursor tasks is
244 select a.task_seq taskseq, a.user_id asuser,
245 a.resp_id asresp, b.qdr type, b.exec_order exorder
246 from zpb_writeback_tasks a, zpb_writeback_transaction b
247 where b.task_seq = a.task_seq
248 and a.task_seq = P_TASK_SEQ
249 order by b.exec_order ASC;
250
251 BEGIN
252 errbuf := ' ';
253 RETCODE := '0';
254 /*
255 FOR each in tasks loop
256 if NOT aw_attached then
257 l_attach_aws (each.read_aws, false, each.asuser);
258 l_attach_aws (each.write_aws, true, each.asuser);
259 aw_attached := TRUE;
260 l_aws := each.write_aws||'|'||each.read_aws;
261 end if;
262
263 --log user/resp
264 last_task := each.taskseq;
265 l_log_user (last_task, each.asuser, each.asresp,
266 each.exorder, each.type);
267
268 --set the status to failed
269 update zpb_writeback_tasks set
270 status = FAILED, completion_date = SYSDATE,
271 LAST_UPDATED_BY = fnd_global.USER_ID, LAST_UPDATE_DATE = SYSDATE,
272 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
273 where task_seq = each.taskseq;
274
275 if (each.type = 'FULL') then
276 /*
277 delete * from zpb_ac_param_values;
278 delete * from zpb_analysis_cycles;
279 delete * from zpb_analysis_cycle_instances;
280 delete * from zpb_analysis_cycle_tasks;
281 --delete * from zpb_aw_references;
282 delete * from zpb_cycle_model_dimensions;
283 delete * from zpb_cycle_relationships;
284 delete * from zpb_dc_distribution_lists;
285 delete * from zpb_dc_distribution_list_items;
286 delete * from zpb_dc_instruction_text;
287 delete * from zpb_dc_instruction_text_items;
288 delete * from zpb_home_page_data;
289 delete * from zpb_label_lookups;
290 delete * from zpb_solve_allocation_basis;
291 delete * from zpb_solve_allocation_rules;
292 delete * from zpb_solve_definitions;
293 delete * from zpb_solve_input_levels;
294 delete * from zpb_solve_output_hierarchies;
295 delete * from zpb_solve_output_levels;
296 delete * from zpb_status_sql;
297 delete * from zpb_status_sql_lines;
298 delete * from zpb_task_parameters;
299 delete * from zpb_writeback_tasks;
300 delete * from zpb_writeback_transaction;
301
302 null;
303 --
304 -- TODO: Delete from WF tables
305 --
306 end if;
307 /*
308 delete * from zpb_univ_attributes;
309 delete * from zpb_univ_dimensions;
310 delete * from zpb_univ_dimension_abbrevs;
311 delete * from zpb_univ_dimension_groups;
312 delete * from zpb_univ_hierarchies;
313
314 l_dataAw := substr (each.write_aws, 1, instr (each.write_aws, '|')-1);
315 l_annotAw := substr (each.write_aws, instr (each.write_aws, '|')+1);
316 zpb_build_metadata.remove_metadata (l_dataAw, l_annotAw);
317
318 END LOOP;
319 if aw_attached then
320 --detach AW
321 l_detach_aws (l_aws, true);
322 aw_attached := FALSE;
323
324 --update the tasks table
325 update zpb_writeback_tasks set
326 status = COMPLETED, completion_date = SYSDATE,
327 LAST_UPDATED_BY = fnd_global.USER_ID, LAST_UPDATE_DATE = SYSDATE,
328 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
329 where task_seq = last_task;
330
331 else
332 --log no tasks found
333 FND_MESSAGE.SET_NAME('ZPB','ZPB_WRITEMGR_NO_TASKS_FOUND');
334 FND_FILE.put_line(FND_FILE.LOG, FND_MESSAGE.GET);
335 end if;
336
337 --remove records timed by ZPB_WRITEBACK_TABLE_NUMBER_OF_DAYS profile
338 l_remove_old_records;
339
340 EXCEPTION
341 when others then
342 ZPB_ERROR_HANDLER.HANDLE_EXCEPTION (G_PKG_NAME,
343 'process_cleanup');
344 retcode := '2';
345 errNum := SQLCODE;
346 errbuf := sqlerrm(errNum);
347 if aw_attached then
348 l_detach_aws (l_aws, false);
349 end if;
350 */
351 END process_cleanup;
352
353 --process_dvac_admin_task will maintain data view access controls.
354 --
355 -- Procedure will read in two task tokens for a data view access control request.
356 -- The first token will specify whether the request is from a task or action.
357 -- The second token contains the instance_id.
358 --
359 PROCEDURE process_dvac_writeback ( ERRBUF OUT NOCOPY VARCHAR2,
360 RETCODE OUT NOCOPY VARCHAR2,
361 P_TASK_SEQ IN NUMBER,
365 l_initialized boolean := FALSE;
362 P_BUSINESS_AREA_ID IN NUMBER)
363 IS
364 errNum number;
366 x_return_status varchar2(1);
367 l_spl varchar2(80);
368 x_msg_count number;
369 x_msg_data varchar2(4000);
370 l_api_version NUMBER := 1.0;
371
372 cursor tasks is
373 select task_seq taskseq, business_Area_id,
374 user_id asuser, resp_id asresp
375 from zpb_writeback_tasks
376 where task_seq = P_TASK_SEQ;
377
378 cursor tokens is
379 select qdr token, exec_order exorder
380 from zpb_writeback_transaction
381 where task_seq = P_TASK_SEQ
382 order by exec_order ASC;
383
384 BEGIN
385
386 errbuf := ' ';
387
388 for v_task in tasks loop
389
390 --set the initial status to failed
391 update zpb_writeback_tasks set
392 status = FAILED, completion_date = SYSDATE,
393 LAST_UPDATED_BY = fnd_global.USER_ID, LAST_UPDATE_DATE = SYSDATE,
394 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
395 where task_seq = v_task.taskseq;
396
397 if (not l_initialized) then
398 INITIALIZE (v_task.asuser,
399 v_task.business_Area_id,
400 x_return_status,
401 errbuf);
402 l_initialized := true;
403 end if;
404
405 for v_tokens in tokens loop
406 -- start of bug 5007057
407 -- Delete the logic that would only execute the first dvac olap
408 -- command and execute the olap command for each v_tokens
409 -- if (v_tokens.exorder = 1) then
410 l_spl := v_tokens.token;
411 zpb_aw.execute(l_spl);
412 -- end if;
413 end loop;
414
415 -- the following line is deleted to fix the bug 5007057
416 -- zpb_aw.execute(l_spl);
417 -- end of bug 5007057
418
419 --update the tasks table
420 update zpb_writeback_tasks set
421 status = COMPLETED, completion_date = SYSDATE,
422 LAST_UPDATED_BY = fnd_global.USER_ID, LAST_UPDATE_DATE = SYSDATE,
423 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
424 where task_seq = v_task.taskseq;
425
426 end loop;
427
428 ZPB_AW.EXECUTE ('update');
429 commit;
430
431 ZPB_AW.DETACH_ALL;
432
433 l_remove_old_records;
434 RETCODE := '0';
435
436 EXCEPTION
437 when others then
438 ZPB_ERROR_HANDLER.HANDLE_EXCEPTION (G_PKG_NAME,
439 'process_dvac_admin_task');
440 retcode := '2';
441 errNum := SQLCODE;
442 errbuf := sqlerrm(errNum);
443 ZPB_AW.DETACH_ALL;
444
445 END process_dvac_writeback;
446
447
448 --process_scoping_admin_tasks will set ownership, write or read scoping assignments
449 --for a selected user.
450 -- Procedure will handle a rule set given query_path search key
451 -- of the form <FOLDER_PATH>/<USER_ID>_<TASK_TYPE>_
452 -- This key is stored as the first token in the task transaction table.
453 -- The second token contains the SPL procedure call.
454
455 PROCEDURE process_scoping_admin_tasks ( ERRBUF OUT NOCOPY VARCHAR2,
456 RETCODE OUT NOCOPY VARCHAR2,
457 P_TASK_SEQ IN NUMBER,
458 P_CONC_REQUEST_ID IN NUMBER DEFAULT NULL,
459 P_BUSINESS_AREA_ID IN NUMBER)
460 IS
461 errNum number;
462 l_initialized boolean := FALSE;
463 task number;
464 l_start number;
465 l_end number;
466 s_user_id varchar2(80);
467 l_user_id number;
468 l_aws varchar2(256);
469 l_query_path varchar2(80);
470 set_reset boolean := TRUE;
471 query_path_key varchar2(256);
472 x_user_account_state varchar2(12);
473 x_return_status varchar2(1);
474 x_has_read_acc number;
475 x_msg_count number;
476 x_msg_data varchar2(4000);
477 l_invalid_user VARCHAR2(12) := 'INVALID_USER';
478 l_has_read_acc VARCHAR2(12) := 'HAS_READ_ACC';
479 l_no_read_acc VARCHAR2(11) := 'NO_READ_ACC';
480 l_api_version NUMBER := 1.0;
481
482 -- Added for Bug:5475982
483 l_err_msg VARCHAR2(256);
484 l_status VARCHAR2(256);
485
486 cursor tasks is
487 select a.task_seq taskseq, a.business_Area_id,
488 a.user_id asuser,
489 a.resp_id asresp, b.qdr token, b.exec_order exorder
490 from zpb_writeback_tasks a, zpb_writeback_transaction b
491 where b.task_seq = a.task_seq
492 and a.task_seq = P_TASK_SEQ
493 order by b.exec_order ASC;
494
495 -- Added for Bug: 5475982
499 a.business_Area_id,
496 CURSOR tasks_bulk_mode
497 IS
498 SELECT a.task_seq taskseq,
500 a.user_id asuser,
501 a.resp_id asresp,
502 b.qdr token,
503 b.exec_order exorder
504 FROM zpb_writeback_tasks a,
505 zpb_writeback_transaction b
506 WHERE b.task_seq = a.task_seq
507 AND a.session_id = p_conc_request_id
508 AND a.task_type = 'DO'
509 ORDER BY b.exec_order ASC;
510
511 BEGIN
512 zpb_log.write ('ZPB_AW_WRITE_BACK.process_scoping_admin_tasks','Begin program :');
513 errbuf := ' ';
514 retcode := '0';
515
516 -- Added for Bug:5475982
517 IF p_conc_request_id > 0 THEN
518
519 for v_task in tasks_bulk_mode loop
520 task := v_task.taskseq;
521
522 if not l_initialized then
523 INITIALIZE (v_task.asuser,
524 v_task.business_Area_id,
525 x_return_status,
526 errbuf);
527 l_initialized := true;
528 end if;
529
530 zpb_log.write_statement ('ZPB_AW_WRITE_BACK.process_scoping_admin_tasks',
531 'Updating writeback_tasks table with Status as FAILED for Task Seq: '||task );
532 --set the initial status to failed
533 update zpb_writeback_tasks set
534 status = FAILED, completion_date = SYSDATE,
535 LAST_UPDATED_BY = fnd_global.USER_ID, LAST_UPDATE_DATE = SYSDATE,
536 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
537 where task_seq = task;
538
539 if (v_task.exorder = 1) then
540 s_user_id := v_task.token;
541 l_user_id := to_number(s_user_id);
542 else
543 l_log_user (v_task.taskseq, v_task.asuser, v_task.asresp,
544 v_task.exorder, v_task.token);
545
546 -- Added exception handling code for Bug:5475982
547 BEGIN
548 ZPB_AW.EXECUTE(v_task.token);
549 EXCEPTION
550 WHEN OTHERS THEN
551 fnd_message.set_name('ZPB','ZPB_MNTDATASEC_FAILED');
552
553 l_status := fnd_message.get;
554 l_err_msg := SQLERRM;
555
556 IF INSTR(v_task.token, 'ReadAccess',1) > 0 THEN
557
558 UPDATE zpb_security_rule_definition_t
559 SET status = l_status,
560 error = error || '; ' || l_err_msg
561 WHERE business_area = v_task.business_Area_id
562 AND rule_type = 'READ'
563 AND user_id = v_task.asuser;
564
565 ELSIF INSTR(v_task.token, 'WriteAccess',1) > 0 THEN
566
567 UPDATE zpb_security_rule_definition_t
568 SET status = l_status,
569 error = error || '; ' || l_err_msg
570 WHERE business_area = v_task.business_Area_id
571 AND rule_type = 'WRITE'
572 AND user_id = v_task.asuser;
573
574 ELSIF INSTR(v_task.token, 'Ownership',1) > 0 THEN
575
576 UPDATE zpb_security_rule_definition_t
577 SET status = l_status,
578 error = error || '; ' || l_err_msg
579 WHERE business_area = v_task.business_Area_id
580 AND rule_type = 'OWNERSHIP'
581 AND user_id = v_task.asuser;
582
583 END IF;
584
585 UPDATE zpb_account_states
586 SET has_read_access = 0
587 WHERE user_id = v_task.asuser
588 AND business_area_id = v_task.business_Area_id;
589
590 retcode := '2';
591 END;
592 end if;
593
594 zpb_log.write_statement ('ZPB_AW_WRITE_BACK.process_scoping_admin_tasks',
595 'Checking for Read access in accounts_states table for User'||l_user_id);
596
597 --check this user for read access and update has_read_access flag in zpb_account_states
598 ZPB_SECURITY_UTIL_PVT.validate_user(l_user_id,
599 v_task.business_area_id,
600 l_api_version,
601 FND_API.G_FALSE,
602 FND_API.G_FALSE,
603 FND_API.G_VALID_LEVEL_FULL,
604 x_user_account_state,
605 x_return_status,
606 x_msg_count,
607 x_msg_data);
608
609 if (x_user_account_state = l_has_read_acc) then
610 x_has_read_acc := 1;
611 else
612 x_has_read_acc := 0;
613 end if;
614
615 zpb_log.write_statement ('ZPB_AW_WRITE_BACK.process_scoping_admin_tasks',
616 'Updating accounts_states table has_read_access col for User'||l_user_id);
617 update zpb_account_states
618 set has_read_access = x_has_read_acc
619 where user_id = l_user_id
620 and business_area_id = v_task.business_area_id;
621
622 end loop;
623
624 ELSE
625
626 for v_task in tasks loop
627 task := v_task.taskseq;
628
629 if not l_initialized then
633 errbuf);
630 INITIALIZE (v_task.asuser,
631 v_task.business_Area_id,
632 x_return_status,
634 l_initialized := true;
635 end if;
636
637 zpb_log.write_statement ('ZPB_AW_WRITE_BACK.process_scoping_admin_tasks',
638 'Updating writeback_tasks table with Status as FAILED for Task Seq: '||task );
639 --set the initial status to failed
640 update zpb_writeback_tasks set
641 status = FAILED, completion_date = SYSDATE,
642 LAST_UPDATED_BY = fnd_global.USER_ID, LAST_UPDATE_DATE = SYSDATE,
643 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
644 where task_seq = task;
645
646 if (v_task.exorder = 1) then
647 s_user_id := v_task.token;
648 l_user_id := to_number(s_user_id);
649 else
650 l_log_user (v_task.taskseq, v_task.asuser, v_task.asresp,
651 v_task.exorder, v_task.token);
652 ZPB_AW.EXECUTE(v_task.token);
653 end if;
654
655 zpb_log.write_statement ('ZPB_AW_WRITE_BACK.process_scoping_admin_tasks',
656 'Checking for Read access in accounts_states table for User'||l_user_id);
657
658 --check this user for read access and update has_read_access flag in zpb_account_states
659 ZPB_SECURITY_UTIL_PVT.validate_user(l_user_id,
660 v_task.business_area_id,
661 l_api_version,
662 FND_API.G_FALSE,
663 FND_API.G_FALSE,
664 FND_API.G_VALID_LEVEL_FULL,
665 x_user_account_state,
666 x_return_status,
667 x_msg_count,
668 x_msg_data);
669
670 if (x_user_account_state = l_has_read_acc) then
671 x_has_read_acc := 1;
672 else
673 x_has_read_acc := 0;
674 end if;
675
676 zpb_log.write_statement ('ZPB_AW_WRITE_BACK.process_scoping_admin_tasks',
677 'Updating accounts_states table has_read_access col for User'||l_user_id);
678 update zpb_account_states
679 set has_read_access = x_has_read_acc
680 where user_id = l_user_id
681 and business_area_id = v_task.business_area_id;
682
683 end loop;
684 END IF;
685
686
687 ZPB_AW.EXECUTE ('update');
688 commit;
689
690 ZPB_AW.DETACH_ALL;
691
692 --update the tasks table
693 -- Added for Bug: 5475982
694 IF p_conc_request_id > 0 THEN
695
696 zpb_log.write_statement ('ZPB_AW_WRITE_BACK.process_scoping_admin_tasks',
697 'Updating writeback_tasks table with Status as COMPLETED for SessionID: '||p_conc_request_id );
698
699 UPDATE zpb_writeback_tasks
700 SET status = COMPLETED,
701 completion_date = SYSDATE,
702 last_updated_by = fnd_global.user_id,
703 last_update_date = SYSDATE,
704 last_update_login = fnd_global.login_id
705 WHERE session_id = p_conc_request_id;
706
707 ELSE
708
709 zpb_log.write_statement ('ZPB_AW_WRITE_BACK.process_scoping_admin_tasks',
710 'Updating writeback_tasks table with Status as COMPLETED for Task Seq: '||task );
711
712 UPDATE zpb_writeback_tasks
713 SET status = COMPLETED,
714 completion_date = SYSDATE,
715 last_updated_by = fnd_global.user_id,
716 last_update_date = SYSDATE,
717 last_update_login = fnd_global.login_id
718 WHERE task_seq = task;
719
720 END IF;
721
722 COMMIT;
723
724 --remove records timed by ZPB_WRITEBACK_TABLE_NUMBER_OF_DAYS profile
725 l_remove_old_records;
726
727 -- Moved this line to the beginning of the procedure for Bug: 5475982.
728 -- It is set to '0' to begin with and if an execution of a qdr fails
729 -- then the retcode is set to '2'.
730
731 -- RETCODE := '0';
732
733 zpb_log.write ('ZPB_AW_WRITE_BACK.process_scoping_admin_tasks','End program :');
734
735 EXCEPTION
736 when others then
737 ZPB_ERROR_HANDLER.HANDLE_EXCEPTION (G_PKG_NAME,
738 'process_ownership_tasks');
739 retcode := '2';
740 errNum := SQLCODE;
741 errbuf := sqlerrm(errNum);
742 ZPB_AW.DETACH_ALL;
743
744 END process_scoping_admin_tasks;
745
746 ------------------------------------------------------------------------------
747 -- REAPPLY_ALL_SCOPES - Procedure to rebuild all read/write/ownership scopes
748 -- for a given Business Area. Called after a BA refresh
749 -- in case any of the rules have changed.
750 ------------------------------------------------------------------------------
751 PROCEDURE reapply_all_scopes ( ERRBUF OUT NOCOPY VARCHAR2,
752 RETCODE OUT NOCOPY VARCHAR2,
753 P_BUSINESS_AREA IN NUMBER )
754 is
755 l_query_path VARCHAR2(255);
756 l_user_name FND_USER.USER_NAME%type;
760 -- This goes for all cursors.
757 l_retcode VARCHAR2(2);
758
759 -- filter out bad queries that are in the ZPB_VALIDATION_TEMP_DATA table.
761
762 cursor all_readaccess is
763 select distinct substr(SSQL.QUERY_PATH, 1, instr(SSQL.QUERY_PATH, 'ReadAccess')+10) QUERY_PATH
764 from ZPB_STATUS_SQL SSQL
765 where QUERY_PATH like 'oracle/apps/zpb/BusArea' ||
766 p_business_area || '/ZPBSystem/Private/Manager/%ReadAccess%' and
767 substr(SSQL.QUERY_PATH, 1, instr(SSQL.QUERY_PATH, 'ReadAccess')+10)
768 not in(
769 select substr(replace(VTDATA.VALUE, fnd_global.newline(), '/'), 1, instr(SSQL.QUERY_PATH, 'ReadAccess')+10)
770 from ZPB_VALIDATION_TEMP_DATA VTDATA
771 where VTDATA.business_area_id = p_business_area);
772
773 cursor all_writeaccess is
774 select distinct substr(SSQL.QUERY_PATH, 1, instr(SSQL.QUERY_PATH, 'WriteAccess')+11) QUERY_PATH
775 from ZPB_STATUS_SQL SSQL
776 where QUERY_PATH like 'oracle/apps/zpb/BusArea' ||
777 p_business_area || '/ZPBSystem/Private/Manager/%WriteAccess%' and
778 substr(SSQL.QUERY_PATH, 1, instr(SSQL.QUERY_PATH, 'WriteAccess')+11)
779 not in(
780 select substr(replace(VTDATA.VALUE, fnd_global.newline(), '/'), 1, instr(SSQL.QUERY_PATH, 'WriteAccess')+11)
781 from ZPB_VALIDATION_TEMP_DATA VTDATA
782 where VTDATA.business_area_id = p_business_area);
783
784 cursor all_ownership is
785 select distinct substr(SSQL.QUERY_PATH, 1, instr(SSQL.QUERY_PATH, 'Ownership')+9) QUERY_PATH
786 from ZPB_STATUS_SQL SSQL
787 where QUERY_PATH like 'oracle/apps/zpb/BusArea' ||
788 p_business_area || '/ZPBSystem/Private/Manager/%Ownership%' and
789 substr(SSQL.QUERY_PATH, 1, instr(SSQL.QUERY_PATH, 'Ownership')+9)
790 not in(
791 select substr(replace(VTDATA.VALUE, fnd_global.newline(), '/'), 1, instr(SSQL.QUERY_PATH, 'Ownership')+9)
792 from ZPB_VALIDATION_TEMP_DATA VTDATA
793 where VTDATA.business_area_id = p_business_area);
794
795 begin
796 select USER_NAME
797 into l_user_name
798 from FND_USER
799 where USER_ID = FND_GLOBAL.USER_ID;
800
801 zpb_log.write ('ZPB_AW_WRITE_BACK.reapply_all_scopes','Begin program :');
802
803 INITIALIZE (l_user_name,
804 p_business_area,
805 l_retcode,
806 errbuf);
807
808 zpb_log.write ('ZPB_AW_WRITE_BACK.reapply_all_scopes','Initialization done :');
809
810 zpb_log.write ('ZPB_AW_WRITE_BACK.reapply_all_scopes','Process Read Access');
811 for each in all_readaccess loop
812 l_query_path := each.query_path;
813 ZPB_AW.EXECUTE('call sc.set.scope('''||l_query_path||''')');
814 end loop;
815
816 zpb_log.write ('ZPB_AW_WRITE_BACK.reapply_all_scopes','Process Write Access');
817 for each in all_writeaccess loop
818 l_query_path := each.query_path;
819 ZPB_AW.EXECUTE('call sc.set.write.acc('''||l_query_path||''')');
820 end loop;
821
822 zpb_log.write ('ZPB_AW_WRITE_BACK.reapply_all_scopes','Process Ownership Access');
823 for each in all_ownership loop
824 l_query_path := each.query_path;
825 ZPB_AW.EXECUTE('call sc.set.ownership('''||l_query_path||''')');
826 end loop;
827
828 DELETE FROM zpb_validation_temp_data WHERE business_area_id = p_business_area;
829
830 zpb_log.write ('ZPB_AW_WRITE_BACK.reapply_all_scopes','Done');
831
832 ZPB_AW.EXECUTE ('update');
833 commit;
834
835 ZPB_AW.DETACH_ALL;
836 RETCODE := '0';
837
838 EXCEPTION
839 WHEN OTHERS THEN
840 RETCODE := '2';
841
842 end reapply_all_scopes;
843
844 PROCEDURE process_spl ( ERRBUF OUT NOCOPY VARCHAR2,
845 RETCODE OUT NOCOPY VARCHAR2,
846 P_TASK_SEQ IN NUMBER,
847 P_BUSINESS_AREA_ID IN NUMBER)
848 is
849 l_initialized boolean := FALSE;
850 last_task number;
851 l_aws varchar2(256);
852 l_personal_aw_nm zpb_users.personal_aw%type;
853 l_personal_aw_nmq zpb_users.personal_aw%type;
854 l_start number;
855 l_end number;
856 l_user_id zpb_account_states.user_id%type;
857 b_commit boolean := true;
858 b_start_aw_daemon boolean := false;
859 x_user_account_state varchar2(12);
860 x_return_status varchar2(1);
861 x_msg_count number;
862 x_has_read_acc number;
863 x_msg_data varchar2(4000);
864 l_api_version NUMBER := 1.0;
865 l_request_id NUMBER;
866 l_task_type zpb_writeback_tasks.task_type%TYPE;
867
868 cursor tasks is
869 select a.task_type tasktype, a.business_area_id,
870 a.task_seq taskseq, a.user_id asuser,
871 a.resp_id asresp, b.qdr qdr, b.exec_order exorder
872 from zpb_writeback_tasks a, zpb_writeback_transaction b
873 where b.task_seq = a.task_seq
877 BEGIN
874 and a.task_seq = P_TASK_SEQ
875 order by b.exec_order ASC;
876
878 errbuf := ' ';
879 RETCODE := '0';
880
881 FOR each in tasks loop
882 l_task_type := each.tasktype;
883
884 if not l_initialized then
885 INITIALIZE (each.asuser,
886 each.business_Area_id,
887 x_return_status,
888 errbuf);
889 l_initialized := true;
890 end if;
891
892 --log user/resp
893 last_task := each.taskseq;
894 l_log_user (last_task, each.asuser, each.asresp,
895 each.exorder, each.qdr);
896
897 --set the status to failed
898 update zpb_writeback_tasks set
899 status = FAILED, completion_date = SYSDATE,
900 LAST_UPDATED_BY = fnd_global.USER_ID, LAST_UPDATE_DATE = SYSDATE,
901 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
902 where task_seq = each.taskseq;
903
904 ZPB_AW.EXECUTE(each.qdr);
905
906 if (each.tasktype = UMAINT) then
907 ZPB_AW.EXECUTE('update');
908 commit; --commit changes so zpb_personal_aw.aw_create has visibility to new user
909 b_commit := false;
910 l_initialized := false;
911
912 l_start := instr(each.qdr, '(');
913 l_end := instr(each.qdr, ',');
914 l_user_id := substr(each.qdr, l_start+2, l_end - l_start - 3);
915
916 -- Make sure at least one personal AW does not exist before starting daemon
917 -- null test added for 10g only
918 if (not b_start_aw_daemon ) then
919 l_personal_aw_nm := ZPB_AW.GET_PERSONAL_AW (l_user_id);
920 if(l_personal_aw_nm is null) then
921 b_start_aw_daemon := true;
922 else
923 l_personal_aw_nmq := ZPB_AW.GET_SCHEMA || '.' || l_personal_aw_nm;
924 if (not zpb_aw.interpbool('shw aw(exists ''' || l_personal_aw_nmq ||''')')) then
925 b_start_aw_daemon := true;
926 end if;
927 end if;
928 end if;
929
930 end if;
931 END LOOP;
932
933 if (b_commit) then
934 ZPB_AW.EXECUTE ('update');
935 commit;
936 end if;
937
938 ZPB_AW.DETACH_ALL;
939
940 --update the tasks table
941 update zpb_writeback_tasks set
942 status = COMPLETED, completion_date = SYSDATE,
943 LAST_UPDATED_BY = fnd_global.USER_ID, LAST_UPDATE_DATE = SYSDATE,
944 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
945 where task_seq = last_task;
946
947 --remove records timed by ZPB_WRITEBACK_TABLE_NUMBER_OF_DAYS profile
948 l_remove_old_records;
949
950 IF (l_task_type = UMAINT and b_start_aw_daemon) THEN
951 l_request_id := FND_REQUEST.SUBMIT_REQUEST (application => 'ZPB',
952 program => 'ZPB_CREATE_PERS_AW_DAEMON',
953 description => null,
954 start_time => null,
955 sub_request => FALSE,
959 EXCEPTION
956 argument1 => p_task_seq);
957 END IF;
958
960 WHEN OTHERS THEN
961 retcode :='2';
962 END process_spl;
963
964 FUNCTION get_run_pend_req_count
965 RETURN NUMBER
966 IS
967 -- Cursor to get the number of INACTIVE, PENDING and RUNNING
968 -- 'ZPB: Create Personal Analytic Workspace' requests at a
969 -- given time.
970 CURSOR l_run_pend_req_csr
971 IS
972 SELECT COUNT(*)
973 FROM fnd_concurrent_requests
974 WHERE concurrent_program_id = (SELECT concurrent_program_id
975 FROM fnd_concurrent_programs
976 WHERE concurrent_program_name = 'ZPB_CREATE_PERSONAL_AW')
977 AND phase_code in ('I','P','R');
978
979 l_count NUMBER := 0;
980 BEGIN
981
982 OPEN l_run_pend_req_csr;
983 FETCH l_run_pend_req_csr INTO l_count;
984 CLOSE l_run_pend_req_csr;
985
986 RETURN l_count;
987
988 END get_run_pend_req_count;
989
990 PROCEDURE process_create_pers_aw_daemon (errbuf OUT NOCOPY VARCHAR2,
991 retcode OUT NOCOPY VARCHAR2,
992 p_task_seq IN NUMBER)
993 IS
994 CURSOR users_csr
995 IS
996 SELECT DISTINCT b.qdr,
997 a.business_area_id
998 FROM zpb_writeback_tasks a,
999 zpb_writeback_transaction b
1000 WHERE b.task_seq = a.task_seq
1001 AND a.task_seq = p_task_seq
1002 AND a.task_type = 'UM';
1003
1004 l_curr_conc_req_count NUMBER;
1005 l_max_conc_req_count NUMBER;
1006 l_request_id NUMBER;
1007 l_start NUMBER;
1008 l_end NUMBER;
1009 l_user_id VARCHAR2(30);
1010 l_personal_aw_nm zpb_users.personal_aw%type;
1011 l_personal_aw_nmq zpb_users.personal_aw%type;
1012 b_start_aw_cr_daemon boolean;
1013
1014
1015 BEGIN
1016
1017 l_max_conc_req_count := fnd_profile.value('ZPB_MAX_CREATE_PERS_AW_REQUESTS');
1018
1019 FOR users_rec IN users_csr LOOP
1020 b_start_aw_cr_daemon := false;
1021 l_curr_conc_req_count := get_run_pend_req_count();
1022
1023 WHILE l_curr_conc_req_count > l_max_conc_req_count LOOP
1024
1025 l_curr_conc_req_count := get_run_pend_req_count();
1026
1027 END LOOP;
1028
1029 l_start := instr(users_rec.qdr, '(');
1030 l_end := instr(users_rec.qdr, ',');
1031 l_user_id := substr(users_rec.qdr, l_start+2, l_end - l_start - 3);
1032
1033 -- Ensure aw does not exist before launching request
1034 -- null test added for 10g only
1035 l_personal_aw_nm := ZPB_AW.GET_PERSONAL_AW (l_user_id);
1036 if(l_personal_aw_nm is null) then
1037 b_start_aw_cr_daemon := true;
1038 else
1039 l_personal_aw_nmq := ZPB_AW.GET_SCHEMA || '.' || l_personal_aw_nm;
1040 if (not zpb_aw.interpbool('shw aw(exists ''' || l_personal_aw_nmq ||''')')) then
1041 b_start_aw_cr_daemon := true;
1042 end if;
1043 end if;
1044
1045 if b_start_aw_cr_daemon then
1046 l_request_id := fnd_request.submit_request (application => 'ZPB',
1047 program => 'ZPB_CREATE_PERSONAL_AW',
1048 description => null,
1049 start_time => null,
1050 sub_request => FALSE,
1051 argument1 => l_user_id,
1052 argument2 => users_rec.business_area_id);
1053 end if;
1054
1055 COMMIT;
1056 END LOOP;
1057 END process_create_pers_aw_daemon;
1058
1059 PROCEDURE process_create_personal_aw (errbuf OUT NOCOPY VARCHAR2,
1063 IS
1060 retcode OUT NOCOPY VARCHAR2,
1061 p_user_id IN NUMBER,
1062 p_business_area_id IN NUMBER)
1064 l_user_account_state VARCHAR2(12);
1065 x_has_read_acc NUMBER;
1066 l_has_read_acc VARCHAR2(12) := 'HAS_READ_ACC';
1067 l_api_version NUMBER := 1.0;
1068 l_return_status VARCHAR2(1);
1069 l_msg_count NUMBER;
1070 l_msg_data VARCHAR2(4000);
1071 b_commit BOOLEAN := TRUE;
1072
1073 BEGIN
1074
1075 UPDATE zpb_account_states
1076 SET has_read_access = 0
1077 WHERE business_area_id = p_business_area_id
1078 AND user_id = p_user_id;
1079
1083 retcode := '0';
1080 COMMIT;
1081
1082 errbuf := ' ';
1084 zpb_personal_aw.aw_create (p_user_id, p_business_area_id);
1085
1086 zpb_security_util_pvt.validate_user(p_user_id,
1087 p_business_area_id,
1088 l_api_version,
1089 FND_API.G_FALSE,
1090 FND_API.G_FALSE,
1091 FND_API.G_VALID_LEVEL_FULL,
1092 l_user_account_state,
1093 l_return_status,
1094 l_msg_count,
1095 l_msg_data);
1096
1097 IF (l_user_account_state = l_has_read_acc) THEN
1098 x_has_read_acc := 1;
1099 ELSE
1100 x_has_read_acc := 0;
1101 END IF;
1102
1103 UPDATE zpb_account_states
1104 SET has_read_access = x_has_read_acc
1105 WHERE user_id = p_user_id
1106 AND business_area_id = p_business_area_id;
1107
1108 IF (b_commit) THEN
1109 COMMIT;
1110 END IF;
1111
1112 EXCEPTION
1113 WHEN OTHERS THEN
1114 fnd_message.set_name('ZPB','ZPB_WRITEMGR_AW_CREATE_FAILED');
1115 fnd_file.put_line(FND_FILE.LOG, FND_MESSAGE.GET);
1116
1117 retcode := 2;
1118
1119 END process_create_personal_aw;
1120
1121 PROCEDURE bulk_writeback(p_business_area_id IN NUMBER,
1122 p_root_request_id IN NUMBER,
1123 p_child_request_id OUT NOCOPY NUMBER)
1124 IS
1125 req_id NUMBER;
1126
1127 BEGIN
1128 req_id := fnd_request.submit_request ('ZPB',
1129 'ZPB_DO_WRTBK',
1130 NULL,
1131 NULL,
1132 FALSE,
1133 NULL,
1134 p_root_request_id,
1135 p_business_area_id);
1136 p_child_request_id := req_id;
1137
1138 EXCEPTION
1139 WHEN others THEN
1140 fnd_file.put_line(FND_FILE.LOG, 'Error occurred while launching ZPB: Data Ownership Writeback process.');
1141 p_child_request_id := -1;
1142
1143 END bulk_writeback;
1144
1145 END ZPB_AW_WRITE_BACK;
1146