1 package body HR_WORKFLOWS_PKG as
2 /* $Header: hrdwflct.pkb 115.7 2004/05/28 02:58:20 adhunter noship $ */
3
4 procedure CLEANUP_TASKFLOW_UPDATE (
5 X_WORKFLOW_ID in NUMBER
6 ) is
7 l_nav_node_usage_id NUMBER;
8 l_nav_path_id number;
9 CURSOR csr_node_usages IS
10 SELECT nav_node_usage_id
11 FROM hr_navigation_node_usages
12 WHERE workflow_id = x_workflow_id;
13
14 CURSOR csr_nav_paths is
15 SELECT nav_path_id
16 FROM hr_navigation_paths
17 WHERE from_nav_node_usage_id = l_nav_node_usage_id
18 OR to_nav_node_usage_id = l_nav_node_usage_id;
19 begin
20 --
21 -- For each node usage attached to a taskflow delete the navigation paths
22 -- then delete the node usage record.
23 --
24 FOR node_usage_record IN csr_node_usages LOOP
25 l_nav_node_usage_id := node_usage_record.nav_node_usage_id;
26 --
27 FOR nav_path_record IN csr_nav_paths LOOP
28 l_nav_path_id := nav_path_record.nav_path_id;
29
30 DELETE FROM hr_navigation_paths_tl
31 WHERE nav_path_id = l_nav_path_id;
32
33 DELETE FROM hr_navigation_paths
34 WHERE from_nav_node_usage_id = l_nav_node_usage_id
35 OR to_nav_node_usage_id = l_nav_node_usage_id;
36 --
37 END LOOP;
38 --
39 DELETE FROM hr_navigation_node_usages
40 WHERE nav_node_usage_id = l_nav_node_usage_id;
41 --
42 END LOOP;
43 end CLEANUP_TASKFLOW_UPDATE;
44
45 procedure INSERT_ROW (
46 X_ROWID in out nocopy VARCHAR2,
47 X_WORKFLOW_ID in NUMBER,
48 X_WORKFLOW_NAME in VARCHAR2,
49 X_LEGISLATION_CODE in VARCHAR2
50 ) is
51 cursor C is select ROWID from HR_WORKFLOWS
52 where WORKFLOW_ID = X_WORKFLOW_ID
53 ;
54 begin
55 insert into HR_WORKFLOWS (
56 WORKFLOW_ID,
57 WORKFLOW_NAME,
58 LEGISLATION_CODE
59 )
60 values (X_WORKFLOW_ID,X_WORKFLOW_NAME,X_LEGISLATION_CODE);
61
62 open c;
63 fetch c into X_ROWID;
64 if (c%notfound) then
65 close c;
66 raise no_data_found;
67 end if;
68 close c;
69
70 end INSERT_ROW;
71
72 procedure LOCK_ROW (
73 X_WORKFLOW_ID in NUMBER,
74 X_WORKFLOW_NAME in VARCHAR2,
75 X_LEGISLATION_CODE in VARCHAR2
76 ) is
77 cursor c1 is select
78 WORKFLOW_NAME,
79 LEGISLATION_CODE
80 from HR_WORKFLOWS
81 where WORKFLOW_ID = X_WORKFLOW_ID
82 for update of WORKFLOW_ID nowait;
83 begin
84 for tlinfo in c1 loop
85 if ( (tlinfo.WORKFLOW_NAME = X_WORKFLOW_NAME)
86 AND (tlinfo.LEGISLATION_CODE = X_LEGISLATION_CODE)
87 ) then
88 null;
89 else
90 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
91 app_exception.raise_exception;
92 end if;
93 end loop;
94 return;
95 end LOCK_ROW;
96
97 procedure UPDATE_ROW (
98 X_WORKFLOW_ID in NUMBER,
99 X_WORKFLOW_NAME in VARCHAR2,
100 X_LEGISLATION_CODE in VARCHAR2
101 ) is
102 begin
103 update HR_WORKFLOWS set
104 WORKFLOW_NAME = X_WORKFLOW_NAME,
105 LEGISLATION_CODE = X_LEGISLATION_CODE
106 where WORKFLOW_ID = X_WORKFLOW_ID;
107
108 if (sql%notfound) then
109 raise no_data_found;
110 end if;
111 end UPDATE_ROW;
112
113 procedure DELETE_ROW (
114 X_WORKFLOW_ID in NUMBER
115 ) is
116 begin
117 delete from HR_WORKFLOWS
118 where WORKFLOW_ID = X_WORKFLOW_ID;
119
120 if (sql%notfound) then
121 raise no_data_found;
122 end if;
123
124 end DELETE_ROW;
125
126 procedure LOAD_ROW (
127 X_WORKFLOW_NAME in VARCHAR2,
128 X_LEGISLATION_NAME in VARCHAR2
129 ) is
130 X_WORKFLOW_ID NUMBER;
131 X_LEGISLATION_CODE VARCHAR2(2);
132 X_ROWID VARCHAR2(30);
133 X_STATUS VARCHAR2(1);
134 X_ACTION VARCHAR2(1);
135 begin
136
137 begin
138 select TERRITORY_CODE
139 into X_LEGISLATION_CODE
140 from FND_TERRITORIES_VL
141 where TERRITORY_SHORT_NAME=X_LEGISLATION_NAME;
142
143 exception
144 when no_data_found then
145 null;
146 end;
147
148 if X_LEGISLATION_CODE is not null then
149
150 begin
151
152 select STATUS, ACTION
153 into X_STATUS, X_ACTION
154 from HR_LEGISLATION_INSTALLATIONS
155 where LEGISLATION_CODE = X_LEGISLATION_CODE
156 and APPLICATION_SHORT_NAME = 'PER';
157
158 exception
159 when no_data_found then
160 X_STATUS := null;
161 X_ACTION := null;
162 end;
163
164 end if;
165
166 if (( X_LEGISLATION_CODE is null ) OR
167 ( X_LEGISLATION_CODE is not null AND
168 X_LEGISLATION_CODE = 'US' )) OR
169 (( X_LEGISLATION_CODE is not null AND
170 X_LEGISLATION_CODE <> 'US' ) AND
171 ( X_STATUS = 'I' AND X_ACTION is null ) OR
172 (( X_STATUS = 'I' OR X_STATUS is null ) AND
173 ( X_ACTION in ('I','U','F')))) then
174
175 begin
176 select WORKFLOW_ID
177 into X_WORKFLOW_ID
178 from HR_WORKFLOWS
179 where WORKFLOW_NAME=X_WORKFLOW_NAME;
180
181 exception
182 when no_data_found then
183 select HR_WORKFLOWS_S.NEXTVAL
184 into X_WORKFLOW_ID
185 from DUAL;
186 end;
187
188 begin
189 cleanup_taskflow_update(X_WORKFLOW_ID);
190 UPDATE_ROW(
191 X_WORKFLOW_ID,
192 X_WORKFLOW_NAME,
193 X_LEGISLATION_CODE
194 );
195 exception
196 when no_data_found then
197 INSERT_ROW(
198 X_ROWID,
199 X_WORKFLOW_ID,
200 X_WORKFLOW_NAME,
201 X_LEGISLATION_CODE);
202 end;
203
204 g_load_taskflow := 'Y';
205
206 else
207 g_load_taskflow := 'N';
208
209 end if;
210
211 end LOAD_ROW;
212
213 end HR_WORKFLOWS_PKG;
[Home] [Help]