1 package body HR_NAV_CONTEXT_RULES_PKG as
2 /* $Header: hrwcrlct.pkb 120.1 2011/04/28 10:23:43 sidsaxen ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_NAV_CONTEXT_RULE_ID in NUMBER,
6 X_GLOBAL_USAGE_ID in NUMBER,
7 X_EVALUATION_TYPE_CODE in VARCHAR2,
8 X_VALUE in VARCHAR2
9 ) is
10 cursor C is select ROWID from HR_NAVIGATION_CONTEXT_RULES
11 where NAV_CONTEXT_RULE_ID = X_NAV_CONTEXT_RULE_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_CONTEXT_RULES',
23 p_ref_entity => 'HR_NAV_UNIT_GLOBAL_USAGES',
24 p_ref_column_name => 'GLOBAL_USAGE_ID',
25 p_ref_col_value_number => X_GLOBAL_USAGE_ID,
26 p_ref_col_value_varchar => NULL,
27 p_ref_col_value_date => NULL,
28 p_ref_type => 'INS');
29
30 END;
31 --
32 -- Code Ends Here.
33 --
34 insert into HR_NAVIGATION_CONTEXT_RULES (
35 NAV_CONTEXT_RULE_ID,
36 GLOBAL_USAGE_ID,
37 EVALUATION_TYPE_CODE,
38 VALUE
39 ) values (
40 X_NAV_CONTEXT_RULE_ID,
41 X_GLOBAL_USAGE_ID,
42 X_EVALUATION_TYPE_CODE,
43 X_VALUE);
44
45 open c;
46 fetch c into X_ROWID;
47 if (c%notfound) then
48 close c;
49 raise no_data_found;
50 end if;
51 close c;
52
53 end INSERT_ROW;
54
55 procedure LOCK_ROW (
56 X_NAV_CONTEXT_RULE_ID in NUMBER,
57 X_GLOBAL_USAGE_ID in NUMBER,
58 X_EVALUATION_TYPE_CODE in VARCHAR2,
59 X_VALUE in VARCHAR2
60 ) is
61 cursor c1 is select
62 GLOBAL_USAGE_ID,
63 EVALUATION_TYPE_CODE,
64 VALUE
65 from HR_NAVIGATION_CONTEXT_RULES
66 where NAV_CONTEXT_RULE_ID = X_NAV_CONTEXT_RULE_ID
67 for update of NAV_CONTEXT_RULE_ID nowait;
68 begin
69 for tlinfo in c1 loop
70 if ( (tlinfo.EVALUATION_TYPE_CODE = X_EVALUATION_TYPE_CODE)
71 AND (tlinfo.GLOBAL_USAGE_ID = X_GLOBAL_USAGE_ID)
72 AND ((tlinfo.VALUE = X_VALUE)
73 OR ((tlinfo.VALUE is null) AND (X_VALUE is null)))
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_CONTEXT_RULE_ID in NUMBER,
86 X_GLOBAL_USAGE_ID in NUMBER,
87 X_EVALUATION_TYPE_CODE in VARCHAR2,
88 X_VALUE in VARCHAR2
89 ) is
90 begin
91 update HR_NAVIGATION_CONTEXT_RULES set
92 GLOBAL_USAGE_ID = X_GLOBAL_USAGE_ID,
93 EVALUATION_TYPE_CODE = X_EVALUATION_TYPE_CODE,
94 VALUE = X_VALUE
95 where NAV_CONTEXT_RULE_ID = X_NAV_CONTEXT_RULE_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_CONTEXT_RULE_ID in NUMBER
104 ) is
105 begin
106 delete from HR_NAVIGATION_CONTEXT_RULES
107 where NAV_CONTEXT_RULE_ID = X_NAV_CONTEXT_RULE_ID;
108
109 if (sql%notfound) then
110 raise no_data_found;
111 end if;
112
113 end DELETE_ROW;
114
115 procedure LOAD_ROW (
116 X_FORM_NAME in VARCHAR2,
117 X_BLOCK_NAME in VARCHAR2,
118 X_GLOBAL_NAME in VARCHAR2,
119 X_IN_OR_OUT in VARCHAR2,
120 X_EVALUATION_TYPE_CODE in VARCHAR2,
121 X_VALUE in VARCHAR2
122 ) is
123 X_NAV_CONTEXT_RULE_ID NUMBER;
124 X_GLOBAL_USAGE_ID NUMBER;
125 X_ROWID VARCHAR2(30);
126 X_NAV_UNIT_ID NUMBER;
127 begin
128
129 if hr_workflows_pkg.g_load_taskflow <> 'N' then
130
131 begin
132 select NAV_UNIT_ID
133 into X_NAV_UNIT_ID
134 from HR_NAVIGATION_UNITS
135 where FORM_NAME = X_FORM_NAME
136 and nvl(BLOCK_NAME,hr_api.g_varchar2) = nvl(X_BLOCK_NAME,hr_api.g_varchar2);
137 end;
138
139 select GLOBAL_USAGE_ID
140 into X_GLOBAL_USAGE_ID
141 from HR_NAV_UNIT_GLOBAL_USAGES
142 where GLOBAL_NAME = X_GLOBAL_NAME
143 and IN_OR_OUT = X_IN_OR_OUT
144 and NAV_UNIT_ID = X_NAV_UNIT_ID;
145
146 --
147 -- Fix for bug 3274423 starts here.
148 -- Before updating the record, compare the database row with the row in ldt file.
149 -- The SELECT statement is comparing for the complete row. So no need for explicit check.
150 -- Therefore, if no data found INSERT.
151 --
152 begin
153 select NCR.NAV_CONTEXT_RULE_ID
154 into X_NAV_CONTEXT_RULE_ID
155 from HR_NAVIGATION_CONTEXT_RULES NCR
156 where NCR.EVALUATION_TYPE_CODE = X_EVALUATION_TYPE_CODE
157 and nvl(NCR.VALUE,hr_api.g_varchar2) = nvl(X_VALUE,hr_api.g_varchar2)
158 and NCR.GLOBAL_USAGE_ID = X_GLOBAL_USAGE_ID;
159 exception
160 when no_data_found then
161 select HR_NAVIGATION_CONTEXT_RULES_S.NEXTVAL
162 into X_NAV_CONTEXT_RULE_ID
163 from DUAL;
164
165 INSERT_ROW(
166 X_ROWID,
167 X_NAV_CONTEXT_RULE_ID,
168 X_GLOBAL_USAGE_ID,
169 X_EVALUATION_TYPE_CODE,
170 X_VALUE
171 );
172
173 when too_many_rows then
174 raise_application_error(-20001,X_EVALUATION_TYPE_CODE||':'||X_VALUE||':'||X_GLOBAL_USAGE_ID);
175 end;
176
177 /*begin
178 UPDATE_ROW(
179 X_NAV_CONTEXT_RULE_ID,
180 X_GLOBAL_USAGE_ID,
181 X_EVALUATION_TYPE_CODE,
182 X_VALUE
183 );
184 exception
185 when no_data_found then
186 INSERT_ROW(
187 X_ROWID,
188 X_NAV_CONTEXT_RULE_ID,
189 X_GLOBAL_USAGE_ID,
190 X_EVALUATION_TYPE_CODE,
191 X_VALUE
192 );
193 end; */
194 --
195 -- Fix for bug 3274423 ends here.
196 --
197
198 end if;
199
200 end LOAD_ROW;
201
202 end HR_NAV_CONTEXT_RULES_PKG;
[Home] [Help]