1 package body HR_NAVIGATION_NODE_USAGES_PKG as
2 /* $Header: hrnvnlct.pkb 120.1 2011/04/28 11:50:43 sidsaxen ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_NAV_NODE_USAGE_ID in NUMBER,
6 X_WORKFLOW_ID in NUMBER,
7 X_NAV_NODE_ID in NUMBER,
8 X_TOP_NODE in VARCHAR2
9 ) is
10 cursor C is select ROWID from HR_NAVIGATION_NODE_USAGES
11 where NAV_NODE_USAGE_ID = X_NAV_NODE_USAGE_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 PER_RIC_PKG.chk_integrity (
22 p_entity_name => 'HR_NAVIGATION_NODE_USAGES',
23 p_ref_entity_info => PER_RIC_PKG.ref_entity_tbl(
24 PER_RIC_PKG.ref_info_rec('HR_WORKFLOWS', PER_RIC_PKG.column_info_tbl(
25 PER_RIC_PKG.col_info_rec('WORKFLOW_ID',NULL,X_WORKFLOW_ID,NULL))),
26 PER_RIC_PKG.ref_info_rec('HR_NAVIGATION_NODES', PER_RIC_PKG.column_info_tbl(
27 PER_RIC_PKG.col_info_rec('NAV_NODE_ID',NULL,X_NAV_NODE_ID,NULL)))
28 ),
29 p_ref_type => 'INS');
30
31 END;
32 --
33 -- Code Ends Here.
34 --
35 insert into HR_NAVIGATION_NODE_USAGES (
36 NAV_NODE_USAGE_ID,
37 WORKFLOW_ID,
38 NAV_NODE_ID,
39 TOP_NODE
40 ) values (
41 X_NAV_NODE_USAGE_ID,
42 X_WORKFLOW_ID,
43 X_NAV_NODE_ID,
44 X_TOP_NODE);
45
46 open c;
47 fetch c into X_ROWID;
48 if (c%notfound) then
49 close c;
50 raise no_data_found;
51 end if;
52 close c;
53
54 end INSERT_ROW;
55
56 procedure LOCK_ROW (
57 X_NAV_NODE_USAGE_ID in NUMBER,
58 X_WORKFLOW_ID in NUMBER,
59 X_NAV_NODE_ID in NUMBER,
60 X_TOP_NODE in VARCHAR2
61 ) is
62 cursor c1 is select
63 WORKFLOW_ID,
64 NAV_NODE_ID,
65 TOP_NODE
66 from HR_NAVIGATION_NODE_USAGES
67 where NAV_NODE_USAGE_ID = X_NAV_NODE_USAGE_ID
68 for update of NAV_NODE_USAGE_ID nowait;
69 begin
70 for tlinfo in c1 loop
71 if ( (tlinfo.TOP_NODE = X_TOP_NODE)
72 AND (tlinfo.WORKFLOW_ID = X_WORKFLOW_ID)
73 AND (tlinfo.NAV_NODE_ID = X_NAV_NODE_ID)
74 ) then
75 null;
76 else
77 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
78 app_exception.raise_exception;
79 end if;
80 end loop;
81 return;
82 end LOCK_ROW;
83
84 procedure UPDATE_ROW (
85 X_NAV_NODE_USAGE_ID in NUMBER,
86 X_WORKFLOW_ID in NUMBER,
87 X_NAV_NODE_ID in NUMBER,
88 X_TOP_NODE in VARCHAR2
89 ) is
90 begin
91 update HR_NAVIGATION_NODE_USAGES set
92 WORKFLOW_ID = X_WORKFLOW_ID,
93 NAV_NODE_ID = X_NAV_NODE_ID,
94 TOP_NODE = X_TOP_NODE
95 where NAV_NODE_USAGE_ID = X_NAV_NODE_USAGE_ID;
96
97 if (sql%notfound) then
98 raise no_data_found;
99 end if;
100 end UPDATE_ROW;
101
102 procedure DELETE_ROW (
103 X_NAV_NODE_USAGE_ID in NUMBER
104 ) is
105 begin
106
107 --
108 -- Added the following code as a part of Zero Downtime Patching Project.
109 -- Code Starts Here.
110 --
111
112 BEGIN
113 PER_RIC_PKG.chk_integrity (
114 p_entity_name => 'HR_NAVIGATION_NODE_USAGES',
115 p_ref_entity => 'HR_NAVIGATION_PATHS',
116 p_ref_column_info => PER_RIC_PKG.column_info_tbl(
117 PER_RIC_PKG.col_info_rec('FROM_NAV_NODE_USAGE_ID',NULL,X_NAV_NODE_USAGE_ID,NULL) ,
118 PER_RIC_PKG.col_info_rec('TO_NAV_NODE_USAGE_ID',NULL,X_NAV_NODE_USAGE_ID,NULL)
119 ),
120 p_ref_type => 'DEL');
121
122 END;
123 --
124 -- Code Ends Here.
125 --
126 delete from HR_NAVIGATION_NODE_USAGES
127 where NAV_NODE_USAGE_ID = X_NAV_NODE_USAGE_ID;
128
129 if (sql%notfound) then
130 raise no_data_found;
131 end if;
132
133 end DELETE_ROW;
134
135 procedure LOAD_ROW(
136 X_WORKFLOW_NAME in VARCHAR2,
137 X_NODE_NAME in VARCHAR2,
138 X_TOP_NODE in VARCHAR2
139 ) is
140 X_NAV_NODE_USAGE_ID NUMBER;
141 X_WORKFLOW_ID NUMBER;
142 X_NAV_NODE_ID NUMBER;
143 X_ROWID VARCHAR2(30);
144 begin
145
146 if hr_workflows_pkg.g_load_taskflow <> 'N' then
147
148 if X_WORKFLOW_NAME is not null then
149 select WORKFLOW_ID
150 into X_WORKFLOW_ID
151 from HR_WORKFLOWS
152 where WORKFLOW_NAME = X_WORKFLOW_NAME;
153 else
154 X_WORKFLOW_ID := null;
155 end if;
156
157 if X_NODE_NAME is not null then
158 select NAV_NODE_ID
159 into X_NAV_NODE_ID
160 from HR_NAVIGATION_NODES
161 where NAME = X_NODE_NAME;
162 else
163 X_NAV_NODE_ID := null;
164 end if;
165
166 begin
167 select NAV_NODE_USAGE_ID
168 into X_NAV_NODE_USAGE_ID
169 from HR_NAVIGATION_NODE_USAGES
170 where WORKFLOW_ID = X_WORKFLOW_ID
171 and NAV_NODE_ID = X_NAV_NODE_ID
172 and TOP_NODE = X_TOP_NODE;
173 exception
174 when no_data_found then
175 select HR_NAVIGATION_NODE_USAGES_S.NEXTVAL
176 into X_NAV_NODE_USAGE_ID
177 from dual;
178 --
179 -- Fix for bug 3274423 starts here.
180 -- Before updating the record, compare the database row with the row in ldt file.
181 -- The SELECT statement above is checking for all the columns in the table. So no
182 -- need for explicit check. Therefore if not found then insert.
183 --
184 /* end;
185
186 begin
187 UPDATE_ROW(
188 X_NAV_NODE_USAGE_ID,
189 X_WORKFLOW_ID,
190 X_NAV_NODE_ID,
191 X_TOP_NODE
192 );
193 exception
194 when no_data_found then */
195
196 --
197 -- Fix for bug 3274423 ends here.
198 --
199 INSERT_ROW(
200 X_ROWID,
201 X_NAV_NODE_USAGE_ID,
202 X_WORKFLOW_ID,
203 X_NAV_NODE_ID,
204 X_TOP_NODE
205 );
206 end;
207
208 end if;
209
210 end LOAD_ROW;
211
212 end HR_NAVIGATION_NODE_USAGES_PKG;
[Home] [Help]