DBA Data[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