[Home] [Help]
PACKAGE BODY: APPS.ZPB_DVAC_WF
Source
1 PACKAGE BODY ZPB_DVAC_WF AS
2 /* $Header: ZPBVDVCB.pls 120.4 2007/12/04 14:38:43 mbhat noship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ZPB_DVAC_WF';
4
5 procedure set_dvac_task (errbuf out nocopy varchar2,
6 retcode out nocopy varchar2,
7 BP_ID in number,
8 instanceId in number,
9 p_business_area_id in number,
10 p_task_id in number)
11 IS
12
13 x_return_status varchar2(100);
14 x_msg_count number;
15 x_msg_data varchar2(4000);
16 x_validation_level number := FND_API.G_VALID_LEVEL_FULL;
17 cursor exempt_users is
18 select a.user_id, a.exemption_id
19 from zpb_measure_scope_exempt_users a
20 where business_process_entity_type = 'A'
21 -- bug 4587184
22 -- and business_process_entity_id = BP_ID
23 and business_process_entity_id = instanceId
24 and task_id = p_task_id;
25 l_instance_ac_id number; -- added for bug 5842494
26
27 BEGIN
28
29 ZPB_LOG.WRITE_EVENT_TR ('ZPB_DVAC_WF.SET_DVAC_TASK', 'begin...');
30
31 -- bug 5842494
32 ZPB_AC_OPS.Get_VM_instance_id(
33 p_api_version => 1,
34 p_init_msg_list => FND_API.G_FALSE,
35 x_return_status => x_return_status,
36 x_msg_count => x_msg_count,
37 x_msg_data => x_msg_data,
38 p_ac_id_in => instanceId,
39 x_vm_instance_id => l_instance_ac_id);
40
41 --remove current instanceId row
42 -- bug 5842494: replaced with l_instance_ac_id
43 delete from ZPB_MEASURE_SCOPE
44 where INSTANCE_AC_ID = l_instance_ac_Id;
45
46 --copy the BP definition entry as the new instance definition
47 insert into ZPB_MEASURE_SCOPE
48 (INSTANCE_AC_ID,
49 RESTRICTION_TYPE,
50 START_TIME_TYPE,
51 START_TIME_MEMBER_ID,
52 START_RELATIVE_TYPE_CODE,
53 START_PERIODS,
54 START_TIME_LEVEL_ID,
55 END_TIME_TYPE,
56 END_TIME_MEMBER_ID,
57 END_RELATIVE_TYPE_CODE,
58 END_PERIODS,
59 END_TIME_LEVEL_ID,
60 TIME_HIERARCHY_ID,
61 CREATED_BY,
62 CREATION_DATE,
63 LAST_UPDATED_BY,
64 LAST_UPDATE_DATE,
65 LAST_UPDATE_LOGIN)
66 select
67 -- bug 4587184: replace with BP_ID since where statement has changed
68 -- ANALYSIS_CYCLE_ID,
69 BP_ID,
70 RESTRICTION_TYPE,
71 START_TIME_TYPE,
72 START_TIME_MEMBER_ID,
73 START_RELATIVE_TYPE_CODE,
74 START_PERIODS,
75 START_TIME_LEVEL_ID,
76 END_TIME_TYPE,
77 END_TIME_MEMBER_ID,
78 END_RELATIVE_TYPE_CODE,
79 END_PERIODS,
80 END_TIME_LEVEL_ID,
81 TIME_HIERARCHY_ID,
82 CREATED_BY,
83 CREATION_DATE,
84 LAST_UPDATED_BY,
85 LAST_UPDATE_DATE,
86 LAST_UPDATE_LOGIN
87 from ZPB_BUSINESS_PROCESS_SCOPE
88 -- bug 4587184: Replace BP_ID with instanceId
89 -- where ANALYSIS_CYCLE_ID = BP_ID
90 where ANALYSIS_CYCLE_ID = instanceId
91 and TASK_ID = p_task_id;
92
93 -- bug 5842494: replaced with l_instance_ac_id
94 update ZPB_MEASURE_SCOPE
95 set INSTANCE_AC_ID = l_instance_ac_id,
96 CREATED_BY = fnd_global.USER_ID,
97 CREATION_DATE = SYSDATE,
98 LAST_UPDATED_BY = fnd_global.USER_ID,
99 LAST_UPDATE_DATE = SYSDATE,
100 LAST_UPDATE_LOGIN = fnd_global.USER_ID
101 where INSTANCE_AC_ID = BP_ID;
102
103 -- delete current list of exempt users for instanceId
104 -- bug 5842494: replaced with l_instance_ac_id
105 delete from ZPB_MEASURE_SCOPE_EXEMPT_USERS
106 where BUSINESS_PROCESS_ENTITY_ID = l_instance_ac_id
107 and BUSINESS_PROCESS_ENTITY_TYPE = 'I';
108
109 --copy entries for exempt user specification
110 -- bug 5842494: replaced with l_instance_ac_id
111 for each in exempt_users loop
112 insert into zpb_measure_scope_exempt_users
113 (user_id, exemption_id, business_process_entity_id,
114 business_process_entity_type, created_by, creation_date,
115 last_updated_by, last_update_date, last_update_login)
116 values(each.user_id, each.exemption_id, l_instance_ac_id, 'I',
117 fnd_global.USER_ID, SYSDATE, fnd_global.USER_ID,
118 SYSDATE, fnd_global.LOGIN_ID);
119
120 end loop;
121
122 ZPB_AW.INITIALIZE_FOR_AC(1.0, FND_API.G_FALSE, x_validation_level, x_return_status, x_msg_count, x_msg_data, BP_ID, FND_API.G_TRUE, FND_API.G_FALSE);
123 ZPB_AW.EXECUTE('call DVAC.SET.CONTROL(''' || l_instance_ac_id || ''')');
124 ZPB_AW.EXECUTE('UPDATE');
125
126 ZPB_AW.CLEAN_WORKSPACE(1.0, FND_API.G_FALSE, x_validation_level, x_return_status, x_msg_count, x_msg_data);
127 -- Successfully completed run of exception request
128 ZPB_LOG.WRITE_EVENT_TR ('ZPB_DVAC_WF.SET_DVAC_TASK', 'end');
129 retcode :='0';
130 return;
131
132 exception
133
134 when others then
135 FND_MESSAGE.SET_NAME ('ZPB', 'ZPB_WF_ERREXCPRUN');
136 FND_FILE.put_line(FND_FILE.LOG, FND_MESSAGE.GET);
137 retcode :='2';
138 errbuf:=substr(sqlerrm, 1, 255);
139
140 end SET_DVAC_TASK;
141
142 end ZPB_DVAC_WF;