[Home] [Help]
PACKAGE BODY: APPS.HR_WORKFLOWS_PKG
Source
1 package body HR_WORKFLOWS_PKG as
2 /* $Header: hrdwflct.pkb 120.1 2011/04/28 12:02:15 sidsaxen ship $ */
3
4 PROCEDURE Cleanup_taskflow_update (x_workflow_id IN NUMBER)
5 IS
6 l_nav_node_usage_id NUMBER;
7 l_nav_path_id NUMBER;
8 CURSOR csr_node_usages IS
9 SELECT nav_node_usage_id
10 FROM hr_navigation_node_usages
11 WHERE workflow_id = x_workflow_id;
12 CURSOR csr_nav_paths IS
13 SELECT nav_path_id
14 FROM hr_navigation_paths
15 WHERE from_nav_node_usage_id = l_nav_node_usage_id
16 OR to_nav_node_usage_id = l_nav_node_usage_id;
17 BEGIN
18 --
19 -- For each node usage attached to a taskflow delete the navigation paths
20 -- then delete the node usage record.
21 --
22 FOR node_usage_record IN csr_node_usages LOOP
23 l_nav_node_usage_id := node_usage_record.nav_node_usage_id;
24
25 --
26 FOR nav_path_record IN csr_nav_paths LOOP
27 l_nav_path_id := nav_path_record.nav_path_id;
28
29 DELETE FROM hr_navigation_paths_tl
30 WHERE nav_path_id = l_nav_path_id;
31
32 --
33 -- Added the following code as a part of Zero Downtime Patching Project.
34 -- Code Starts Here.
35 --
36 BEGIN
37 per_ric_pkg.Chk_integrity (
38 p_entity_name => 'HR_NAVIGATION_PATHS',
39 p_ref_entity => 'HR_NAVIGATION_PATHS_TL',
40 p_ref_column_name => 'NAV_PATH_ID',
41 p_ref_col_value_number => l_nav_path_id,
42 p_ref_col_value_varchar => NULL,
43 p_ref_col_value_date => NULL,
44 p_ref_type => 'DEL');
45 END;
46
47 --
48 -- Code Ends Here.
49 --
50 DELETE FROM hr_navigation_paths
51 WHERE from_nav_node_usage_id = l_nav_node_usage_id
52 OR to_nav_node_usage_id = l_nav_node_usage_id;
53 --
54 END LOOP;
55
56 --
57 --
58 -- Added the following code as a part of Zero Downtime Patching Project.
59 -- Code Starts Here.
60 --
61 BEGIN
62 per_ric_pkg.Chk_integrity (
63 p_entity_name => 'HR_NAVIGATION_NODE_USAGES',
64 p_ref_entity => 'HR_NAVIGATION_PATHS',
65 p_ref_column_info => PER_RIC_PKG.column_info_tbl(
66 PER_RIC_PKG.col_info_rec('FROM_NAV_NODE_USAGE_ID', NULL,l_nav_node_usage_id,NULL),
67 PER_RIC_PKG.col_info_rec('TO_NAV_NODE_USAGE_ID', NULL, l_nav_node_usage_id, NULL)
68 ),
69 p_ref_type => 'DEL');
70 END;
71
72 --
73 -- Code Ends Here.
74 --
75 DELETE FROM hr_navigation_node_usages
76 WHERE nav_node_usage_id = l_nav_node_usage_id;
77 --
78 END LOOP;
79 END cleanup_taskflow_update;
80
81
82 procedure INSERT_ROW (
83 X_ROWID in out nocopy VARCHAR2,
84 X_WORKFLOW_ID in NUMBER,
85 X_WORKFLOW_NAME in VARCHAR2,
86 X_LEGISLATION_CODE in VARCHAR2
87 ) is
88 cursor C is select ROWID from HR_WORKFLOWS
89 where WORKFLOW_ID = X_WORKFLOW_ID
90 ;
91 begin
92
93 insert into HR_WORKFLOWS (
94 WORKFLOW_ID,
95 WORKFLOW_NAME,
96 LEGISLATION_CODE
97 )
98 values (X_WORKFLOW_ID,X_WORKFLOW_NAME,X_LEGISLATION_CODE);
99
100 open c;
101 fetch c into X_ROWID;
102 if (c%notfound) then
103 close c;
104 raise no_data_found;
105 end if;
106 close c;
107
108 end INSERT_ROW;
109
110 procedure LOCK_ROW (
111 X_WORKFLOW_ID in NUMBER,
112 X_WORKFLOW_NAME in VARCHAR2,
113 X_LEGISLATION_CODE in VARCHAR2
114 ) is
115 cursor c1 is select
116 WORKFLOW_NAME,
117 LEGISLATION_CODE
118 from HR_WORKFLOWS
119 where WORKFLOW_ID = X_WORKFLOW_ID
120 for update of WORKFLOW_ID nowait;
121 begin
122 for tlinfo in c1 loop
123 if ( (tlinfo.WORKFLOW_NAME = X_WORKFLOW_NAME)
124 AND (tlinfo.LEGISLATION_CODE = X_LEGISLATION_CODE)
125 ) then
126 null;
127 else
128 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
129 app_exception.raise_exception;
130 end if;
131 end loop;
132 return;
133 end LOCK_ROW;
134
135 procedure UPDATE_ROW (
136 X_WORKFLOW_ID in NUMBER,
137 X_WORKFLOW_NAME in VARCHAR2,
138 X_LEGISLATION_CODE in VARCHAR2
139 ) is
140 begin
141 update HR_WORKFLOWS set
142 WORKFLOW_NAME = X_WORKFLOW_NAME,
143 LEGISLATION_CODE = X_LEGISLATION_CODE
144 where WORKFLOW_ID = X_WORKFLOW_ID;
145
146 if (sql%notfound) then
147 raise no_data_found;
148 end if;
149 end UPDATE_ROW;
150
151 procedure DELETE_ROW (
152 X_WORKFLOW_ID in NUMBER
153 ) is
154 begin
155
156 --
157 -- Added the following code as a part of Zero Downtime Patching Project.
158 -- Code Starts Here.
159 --
160
161 BEGIN
162
163 PER_RIC_PKG.chk_integrity (
164 p_entity_name => 'HR_WORKFLOWS',
165 p_ref_entity_info => PER_RIC_PKG.ref_entity_tbl(
166 PER_RIC_PKG.ref_info_rec('HR_NAVIGATION_UNITS', PER_RIC_PKG.column_info_tbl(
167 PER_RIC_PKG.col_info_rec('DEFAULT_WORKFLOW_ID',NULL,X_WORKFLOW_ID,NULL))),
168 PER_RIC_PKG.ref_info_rec('HR_NAVIGATION_NODE_USAGES', PER_RIC_PKG.column_info_tbl(
169 PER_RIC_PKG.col_info_rec('WORKFLOW_ID',NULL,X_WORKFLOW_ID,NULL)))),
170 p_ref_type => 'DEL');
171
172 END;
173
174 --
175 -- Code Ends Here
176 --
177
178 delete from HR_WORKFLOWS
179 where WORKFLOW_ID = X_WORKFLOW_ID;
180
181 if (sql%notfound) then
182 raise no_data_found;
183 end if;
184
185 end DELETE_ROW;
186
187 procedure LOAD_ROW (
188 X_WORKFLOW_NAME in VARCHAR2,
189 X_LEGISLATION_NAME in VARCHAR2
190 ) is
191 X_WORKFLOW_ID NUMBER;
192 X_LEGISLATION_CODE VARCHAR2(2);
193 X_ROWID VARCHAR2(30);
194 X_STATUS VARCHAR2(1);
195 X_ACTION VARCHAR2(1);
196 begin
197
198 begin
199 select TERRITORY_CODE
200 into X_LEGISLATION_CODE
201 from FND_TERRITORIES_VL
202 where TERRITORY_SHORT_NAME=X_LEGISLATION_NAME;
203
204 exception
205 when no_data_found then
206 null;
207 end;
208
209 if X_LEGISLATION_CODE is not null then
210
211 begin
212
213 select STATUS, ACTION
214 into X_STATUS, X_ACTION
215 from HR_LEGISLATION_INSTALLATIONS
216 where LEGISLATION_CODE = X_LEGISLATION_CODE
217 and APPLICATION_SHORT_NAME = 'PER';
218
219 exception
220 when no_data_found then
221 X_STATUS := null;
222 X_ACTION := null;
223 end;
224
225 end if;
226
227 if (( X_LEGISLATION_CODE is null ) OR
228 ( X_LEGISLATION_CODE is not null AND
229 X_LEGISLATION_CODE = 'US' )) OR
230 (( X_LEGISLATION_CODE is not null AND
231 X_LEGISLATION_CODE <> 'US' ) AND
232 ( X_STATUS = 'I' AND X_ACTION is null ) OR
233 (( X_STATUS = 'I' OR X_STATUS is null ) AND
234 ( X_ACTION in ('I','U','F')))) then
235
236 begin
237 select WORKFLOW_ID
238 into X_WORKFLOW_ID
239 from HR_WORKFLOWS
240 where WORKFLOW_NAME=X_WORKFLOW_NAME;
241
242 exception
243 when no_data_found then
244 select HR_WORKFLOWS_S.NEXTVAL
245 into X_WORKFLOW_ID
246 from DUAL;
247 end;
248
249 begin
250 cleanup_taskflow_update(X_WORKFLOW_ID);
251 UPDATE_ROW(
252 X_WORKFLOW_ID,
253 X_WORKFLOW_NAME,
254 X_LEGISLATION_CODE
255 );
256 exception
257 when no_data_found then
258 INSERT_ROW(
259 X_ROWID,
260 X_WORKFLOW_ID,
261 X_WORKFLOW_NAME,
262 X_LEGISLATION_CODE);
263 end;
264
265 g_load_taskflow := 'Y';
266
267 else
268 g_load_taskflow := 'N';
269
270 end if;
271
272 end LOAD_ROW;
273
274 end HR_WORKFLOWS_PKG;