1 package body HR_NAVIGATION_NODES_PKG as
2 /* $Header: hrdwnlct.pkb 120.1 2011/04/28 12:01:35 sidsaxen ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_NAV_NODE_ID in NUMBER,
6 X_NAV_UNIT_ID in NUMBER,
7 X_NAME in VARCHAR2,
8 X_CUSTOMIZED_RESTRICTION_ID in NUMBER
9 ) is
10 cursor C is select ROWID from HR_NAVIGATION_NODES
11 where NAV_NODE_ID = X_NAV_NODE_ID
12 ;
13 begin
14
15 --
16 -- Added the following code as a part of Zero Downtime Patching Project.
17 -- Code Starts Here.
18 --
19
20 BEGIN
21
22 PER_RIC_PKG.chk_integrity (
23 p_entity_name => 'HR_NAVIGATION_NODES',
24 p_ref_entity => 'HR_NAVIGATION_UNITS',
25 p_ref_column_name => 'NAV_UNIT_ID',
26 p_ref_col_value_number => X_NAV_UNIT_ID,
27 p_ref_col_value_varchar => null,
28 p_ref_col_value_date => null,
29 p_ref_type => 'INS');
30
31 END;
32
33 --
34 -- Code Ends Here.
35 --
36
37 insert into HR_NAVIGATION_NODES (
38 NAV_NODE_ID,
39 NAV_UNIT_ID,
40 NAME,
41 CUSTOMIZED_RESTRICTION_ID
42 ) values (
43 X_NAV_NODE_ID,
44 X_NAV_UNIT_ID,
45 X_NAME,
46 X_CUSTOMIZED_RESTRICTION_ID);
47
48 open c;
49 fetch c into X_ROWID;
50 if (c%notfound) then
51 close c;
52 raise no_data_found;
53 end if;
54 close c;
55
56 end INSERT_ROW;
57
58 procedure LOCK_ROW (
59 X_NAV_NODE_ID in NUMBER,
60 X_NAV_UNIT_ID in NUMBER,
61 X_NAME in VARCHAR2,
62 X_CUSTOMIZED_RESTRICTION_ID in NUMBER
63 ) is
64 cursor c1 is select
65 NAV_UNIT_ID,
66 CUSTOMIZED_RESTRICTION_ID,
67 NAME
68 from HR_NAVIGATION_NODES
69 where NAV_NODE_ID = X_NAV_NODE_ID
70 for update of NAV_NODE_ID nowait;
71 begin
72 for tlinfo in c1 loop
73 if ( (tlinfo.NAME = X_NAME)
74 AND (tlinfo.NAV_UNIT_ID = X_NAV_UNIT_ID)
75 AND ((tlinfo.CUSTOMIZED_RESTRICTION_ID = X_CUSTOMIZED_RESTRICTION_ID)
76 OR ((tlinfo.CUSTOMIZED_RESTRICTION_ID is null) AND (X_CUSTOMIZED_RESTRICTION_ID is null)))
77 ) then
78 null;
79 else
80 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
81 app_exception.raise_exception;
82 end if;
83 end loop;
84 return;
85 end LOCK_ROW;
86
87 procedure UPDATE_ROW (
88 X_NAV_NODE_ID in NUMBER,
89 X_NAV_UNIT_ID in NUMBER,
90 X_NAME in VARCHAR2,
91 X_CUSTOMIZED_RESTRICTION_ID in NUMBER
92 ) is
93 begin
94 update HR_NAVIGATION_NODES set
95 NAV_UNIT_ID = X_NAV_UNIT_ID,
96 NAME = X_NAME,
97 CUSTOMIZED_RESTRICTION_ID = X_CUSTOMIZED_RESTRICTION_ID
98 where NAV_NODE_ID = X_NAV_NODE_ID;
99
100 if (sql%notfound) then
101 raise no_data_found;
102 end if;
103 end UPDATE_ROW;
104
105 procedure DELETE_ROW (
106 X_NAV_NODE_ID in NUMBER
107 ) is
108 begin
109
110 --
111 -- Added the following code as a part of Zero Downtime Patching Project.
112 -- Code Starts Here.
113 --
114 BEGIN
115 PER_RIC_PKG.chk_integrity (
116 p_entity_name => 'HR_NAVIGATION_NODES',
117 p_ref_entity => 'HR_NAVIGATION_NODE_USAGES',
118 p_ref_column_name => 'NAV_NODE_ID',
119 p_ref_col_value_number => X_NAV_NODE_ID,
120 p_ref_col_value_varchar => null,
121 p_ref_col_value_date => null,
122 p_ref_type => 'DEL');
123 END;
124
125 --
126 -- Code Ends Here.
127 --
128
129 delete from HR_NAVIGATION_NODES
130 where NAV_NODE_ID = X_NAV_NODE_ID;
131
132 if (sql%notfound) then
133 raise no_data_found;
134 end if;
135
136 end DELETE_ROW;
137
138 procedure LOAD_ROW (
139 X_NODE_NAME in VARCHAR2,
140 X_NAV_FORM_NAME in VARCHAR2,
141 X_BLOCK_NAME in VARCHAR2,
142 X_ORG_NAME in VARCHAR2,
143 X_LEGISLATION_CODE in VARCHAR2,
144 X_APPLICATION_SHORT_NAME in VARCHAR2,
145 X_CUSTOMIZED_FORM_NAME in VARCHAR2,
146 X_CUSTOMIZATION_NAME in VARCHAR2
147 ) is
148 X_NAV_NODE_ID NUMBER;
149 X_NAV_UNIT_ID NUMBER;
150 X_ROWID VARCHAR2(30);
151 X_CUSTOMIZED_RESTRICTION_ID NUMBER;
152 X_APPLICATION_ID NUMBER;
153 X_BUSINESS_GROUP_ID NUMBER;
154 Y_NAV_UNIT_ID NUMBER;
155 Y_NODE_NAME VARCHAR2(80);
156 Y_CUSTOMIZED_RESTRICTION_ID NUMBER;
157 begin
158
159 if hr_workflows_pkg.g_load_taskflow <> 'N' then
160
161 if X_NAV_FORM_NAME is not null then
162 select NAV_UNIT_ID
163 into X_NAV_UNIT_ID
164 from HR_NAVIGATION_UNITS
165 where FORM_NAME = X_NAV_FORM_NAME
166 and nvl(BLOCK_NAME,hr_api.g_varchar2) = nvl(X_BLOCK_NAME,hr_api.g_varchar2);
167 else
168 X_NAV_UNIT_ID := null;
169 end if;
170
171 if X_APPLICATION_SHORT_NAME is not null then
172 select APPLICATION_ID
173 into X_APPLICATION_ID
174 from FND_APPLICATION
175 where APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME;
176 else
177 X_APPLICATION_ID := null;
178 end if;
179
180 if X_ORG_NAME is not null then
181 select ORGANIZATION_ID
182 into X_BUSINESS_GROUP_ID
183 from HR_ORGANIZATION_UNITS
184 where NAME = X_ORG_NAME;
185 else
186 X_BUSINESS_GROUP_ID := null;
187 end if;
188
189 if X_CUSTOMIZATION_NAME is not null
190 and X_APPLICATION_SHORT_NAME is not null
191 and X_CUSTOMIZED_FORM_NAME is not null then
192 BEGIN
193 select CUSTOMIZED_RESTRICTION_ID
194 into X_CUSTOMIZED_RESTRICTION_ID
195 from PAY_CUSTOMIZED_RESTRICTIONS
196 where NAME = X_CUSTOMIZATION_NAME
197 and APPLICATION_ID = X_APPLICATION_ID
198 and FORM_NAME = X_CUSTOMIZED_FORM_NAME
199 and nvl(BUSINESS_GROUP_ID,hr_api.g_number) =
200 nvl(X_BUSINESS_GROUP_ID,hr_api.g_number)
201 and nvl(LEGISLATION_CODE,hr_api.g_varchar2) =
202 nvl(X_LEGISLATION_CODE,hr_api.g_varchar2);
203 EXCEPTION
204 when no_data_found then
205 X_CUSTOMIZED_RESTRICTION_ID := null;
206 when others then
207 raise;
208 END;
209 else
210 X_CUSTOMIZED_RESTRICTION_ID := null;
211 end if;
212
213 begin
214 select NAV_NODE_ID, NAV_UNIT_ID, NAME, CUSTOMIZED_RESTRICTION_ID
215 into X_NAV_NODE_ID, Y_NAV_UNIT_ID, Y_NODE_NAME, Y_CUSTOMIZED_RESTRICTION_ID
216 from HR_NAVIGATION_NODES
217 where NAME = X_NODE_NAME;
218 --
219 -- Fix for bug 3274423 starts here.
220 -- Before updating the record, compare the database row with the row in ldt file.
221 -- If both are same skip updating.
222 --
223 -- bug 3503352 Starts Here
224 -- Description : modified the condition for CUSTOMIZED_RESTRICTION_ID.
225 IF X_NAV_UNIT_ID <> Y_NAV_UNIT_ID OR
226 X_NODE_NAME <> Y_NODE_NAME OR
227 X_CUSTOMIZED_RESTRICTION_ID is null and Y_CUSTOMIZED_RESTRICTION_ID is not null OR
228 X_CUSTOMIZED_RESTRICTION_ID is not null and Y_CUSTOMIZED_RESTRICTION_ID is null OR
229 (X_CUSTOMIZED_RESTRICTION_ID is not null and Y_CUSTOMIZED_RESTRICTION_ID is not null
230 and X_CUSTOMIZED_RESTRICTION_ID <> Y_CUSTOMIZED_RESTRICTION_ID) THEN
231 UPDATE_ROW(
232 X_NAV_NODE_ID,
233 X_NAV_UNIT_ID,
234 X_NODE_NAME,
235 X_CUSTOMIZED_RESTRICTION_ID
236 );
237 END IF;
238 exception
239 when no_data_found then
240 select HR_NAVIGATION_NODES_S.NEXTVAL
241 into X_NAV_NODE_ID
242 from DUAL;
243
244 INSERT_ROW(
245 X_ROWID,
246 X_NAV_NODE_ID,
247 X_NAV_UNIT_ID,
248 X_NODE_NAME,
249 X_CUSTOMIZED_RESTRICTION_ID
250 );
251 end;
252 -- bug 3503352 Ends Here
253 --
254 -- Fix for bug 3274423 ends here.
255 --
256
257
258 end if;
259
260 end LOAD_ROW;
261
262 end HR_NAVIGATION_NODES_PKG;
[Home] [Help]