1 package body HR_NAVIGATION_NODES_PKG as
2 /* $Header: hrdwnlct.pkb 120.0 2005/05/30 23:56:41 appldev noship $ */
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 insert into HR_NAVIGATION_NODES (
15 NAV_NODE_ID,
16 NAV_UNIT_ID,
17 NAME,
18 CUSTOMIZED_RESTRICTION_ID
19 ) values (
20 X_NAV_NODE_ID,
21 X_NAV_UNIT_ID,
22 X_NAME,
23 X_CUSTOMIZED_RESTRICTION_ID);
24
25 open c;
26 fetch c into X_ROWID;
27 if (c%notfound) then
28 close c;
29 raise no_data_found;
30 end if;
31 close c;
32
33 end INSERT_ROW;
34
35 procedure LOCK_ROW (
36 X_NAV_NODE_ID in NUMBER,
37 X_NAV_UNIT_ID in NUMBER,
38 X_NAME in VARCHAR2,
39 X_CUSTOMIZED_RESTRICTION_ID in NUMBER
40 ) is
41 cursor c1 is select
42 NAV_UNIT_ID,
43 CUSTOMIZED_RESTRICTION_ID,
44 NAME
45 from HR_NAVIGATION_NODES
46 where NAV_NODE_ID = X_NAV_NODE_ID
47 for update of NAV_NODE_ID nowait;
48 begin
49 for tlinfo in c1 loop
50 if ( (tlinfo.NAME = X_NAME)
51 AND (tlinfo.NAV_UNIT_ID = X_NAV_UNIT_ID)
52 AND ((tlinfo.CUSTOMIZED_RESTRICTION_ID = X_CUSTOMIZED_RESTRICTION_ID)
53 OR ((tlinfo.CUSTOMIZED_RESTRICTION_ID is null) AND (X_CUSTOMIZED_RESTRICTION_ID is null)))
54 ) then
55 null;
56 else
57 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
58 app_exception.raise_exception;
59 end if;
60 end loop;
61 return;
62 end LOCK_ROW;
63
64 procedure UPDATE_ROW (
65 X_NAV_NODE_ID in NUMBER,
66 X_NAV_UNIT_ID in NUMBER,
67 X_NAME in VARCHAR2,
68 X_CUSTOMIZED_RESTRICTION_ID in NUMBER
69 ) is
70 begin
71 update HR_NAVIGATION_NODES set
72 NAV_UNIT_ID = X_NAV_UNIT_ID,
73 NAME = X_NAME,
74 CUSTOMIZED_RESTRICTION_ID = X_CUSTOMIZED_RESTRICTION_ID
75 where NAV_NODE_ID = X_NAV_NODE_ID;
76
77 if (sql%notfound) then
78 raise no_data_found;
79 end if;
80 end UPDATE_ROW;
81
82 procedure DELETE_ROW (
83 X_NAV_NODE_ID in NUMBER
84 ) is
85 begin
86 delete from HR_NAVIGATION_NODES
87 where NAV_NODE_ID = X_NAV_NODE_ID;
88
89 if (sql%notfound) then
90 raise no_data_found;
91 end if;
92
93 end DELETE_ROW;
94
95 procedure LOAD_ROW (
96 X_NODE_NAME in VARCHAR2,
97 X_NAV_FORM_NAME in VARCHAR2,
98 X_BLOCK_NAME in VARCHAR2,
99 X_ORG_NAME in VARCHAR2,
100 X_LEGISLATION_CODE in VARCHAR2,
101 X_APPLICATION_SHORT_NAME in VARCHAR2,
102 X_CUSTOMIZED_FORM_NAME in VARCHAR2,
103 X_CUSTOMIZATION_NAME in VARCHAR2
104 ) is
105 X_NAV_NODE_ID NUMBER;
106 X_NAV_UNIT_ID NUMBER;
107 X_ROWID VARCHAR2(30);
108 X_CUSTOMIZED_RESTRICTION_ID NUMBER;
109 X_APPLICATION_ID NUMBER;
110 X_BUSINESS_GROUP_ID NUMBER;
111 Y_NAV_UNIT_ID NUMBER;
112 Y_NODE_NAME VARCHAR2(80);
113 Y_CUSTOMIZED_RESTRICTION_ID NUMBER;
114 begin
115
116 if hr_workflows_pkg.g_load_taskflow <> 'N' then
117
118 if X_NAV_FORM_NAME is not null then
119 select NAV_UNIT_ID
120 into X_NAV_UNIT_ID
121 from HR_NAVIGATION_UNITS
122 where FORM_NAME = X_NAV_FORM_NAME
123 and nvl(BLOCK_NAME,hr_api.g_varchar2) = nvl(X_BLOCK_NAME,hr_api.g_varchar2);
124 else
125 X_NAV_UNIT_ID := null;
126 end if;
127
128 if X_APPLICATION_SHORT_NAME is not null then
129 select APPLICATION_ID
130 into X_APPLICATION_ID
131 from FND_APPLICATION
132 where APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME;
133 else
134 X_APPLICATION_ID := null;
135 end if;
136
137 if X_ORG_NAME is not null then
138 select ORGANIZATION_ID
139 into X_BUSINESS_GROUP_ID
140 from HR_ORGANIZATION_UNITS
141 where NAME = X_ORG_NAME;
142 else
143 X_BUSINESS_GROUP_ID := null;
144 end if;
145
146 if X_CUSTOMIZATION_NAME is not null
147 and X_APPLICATION_SHORT_NAME is not null
148 and X_CUSTOMIZED_FORM_NAME is not null then
149 BEGIN
150 select CUSTOMIZED_RESTRICTION_ID
151 into X_CUSTOMIZED_RESTRICTION_ID
152 from PAY_CUSTOMIZED_RESTRICTIONS
153 where NAME = X_CUSTOMIZATION_NAME
154 and APPLICATION_ID = X_APPLICATION_ID
155 and FORM_NAME = X_CUSTOMIZED_FORM_NAME
156 and nvl(BUSINESS_GROUP_ID,hr_api.g_number) =
157 nvl(X_BUSINESS_GROUP_ID,hr_api.g_number)
158 and nvl(LEGISLATION_CODE,hr_api.g_varchar2) =
159 nvl(X_LEGISLATION_CODE,hr_api.g_varchar2);
160 EXCEPTION
161 when no_data_found then
162 X_CUSTOMIZED_RESTRICTION_ID := null;
163 when others then
164 raise;
165 END;
166 else
167 X_CUSTOMIZED_RESTRICTION_ID := null;
168 end if;
169
170 begin
171 select NAV_NODE_ID, NAV_UNIT_ID, NAME, CUSTOMIZED_RESTRICTION_ID
172 into X_NAV_NODE_ID, Y_NAV_UNIT_ID, Y_NODE_NAME, Y_CUSTOMIZED_RESTRICTION_ID
173 from HR_NAVIGATION_NODES
174 where NAME = X_NODE_NAME;
175 --
176 -- Fix for bug 3274423 starts here.
177 -- Before updating the record, compare the database row with the row in ldt file.
178 -- If both are same skip updating.
179 --
180 -- bug 3503352 Starts Here
181 -- Description : modified the condition for CUSTOMIZED_RESTRICTION_ID.
182 IF X_NAV_UNIT_ID <> Y_NAV_UNIT_ID OR
183 X_NODE_NAME <> Y_NODE_NAME OR
184 X_CUSTOMIZED_RESTRICTION_ID is null and Y_CUSTOMIZED_RESTRICTION_ID is not null OR
185 X_CUSTOMIZED_RESTRICTION_ID is not null and Y_CUSTOMIZED_RESTRICTION_ID is null OR
186 (X_CUSTOMIZED_RESTRICTION_ID is not null and Y_CUSTOMIZED_RESTRICTION_ID is not null
187 and X_CUSTOMIZED_RESTRICTION_ID <> Y_CUSTOMIZED_RESTRICTION_ID) THEN
188 UPDATE_ROW(
189 X_NAV_NODE_ID,
190 X_NAV_UNIT_ID,
191 X_NODE_NAME,
192 X_CUSTOMIZED_RESTRICTION_ID
193 );
194 END IF;
195 exception
196 when no_data_found then
197 select HR_NAVIGATION_NODES_S.NEXTVAL
198 into X_NAV_NODE_ID
199 from DUAL;
200
201 INSERT_ROW(
202 X_ROWID,
203 X_NAV_NODE_ID,
204 X_NAV_UNIT_ID,
205 X_NODE_NAME,
206 X_CUSTOMIZED_RESTRICTION_ID
207 );
208 end;
209 -- bug 3503352 Ends Here
210 --
211 -- Fix for bug 3274423 ends here.
212 --
213
214
215 end if;
216
217 end LOAD_ROW;
218
219 end HR_NAVIGATION_NODES_PKG;
[Home] [Help]