1 package body HR_NAV_CONTEXT_RULES_PKG as
2 /* $Header: hrwcrlct.pkb 115.3 2004/01/08 01:25:22 adudekul noship $ */
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 insert into HR_NAVIGATION_CONTEXT_RULES (
15 NAV_CONTEXT_RULE_ID,
16 GLOBAL_USAGE_ID,
17 EVALUATION_TYPE_CODE,
18 VALUE
19 ) values (
20 X_NAV_CONTEXT_RULE_ID,
21 X_GLOBAL_USAGE_ID,
22 X_EVALUATION_TYPE_CODE,
23 X_VALUE);
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_CONTEXT_RULE_ID in NUMBER,
37 X_GLOBAL_USAGE_ID in NUMBER,
38 X_EVALUATION_TYPE_CODE in VARCHAR2,
39 X_VALUE in VARCHAR2
40 ) is
41 cursor c1 is select
42 GLOBAL_USAGE_ID,
43 EVALUATION_TYPE_CODE,
44 VALUE
45 from HR_NAVIGATION_CONTEXT_RULES
46 where NAV_CONTEXT_RULE_ID = X_NAV_CONTEXT_RULE_ID
47 for update of NAV_CONTEXT_RULE_ID nowait;
48 begin
49 for tlinfo in c1 loop
50 if ( (tlinfo.EVALUATION_TYPE_CODE = X_EVALUATION_TYPE_CODE)
51 AND (tlinfo.GLOBAL_USAGE_ID = X_GLOBAL_USAGE_ID)
52 AND ((tlinfo.VALUE = X_VALUE)
53 OR ((tlinfo.VALUE is null) AND (X_VALUE 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_CONTEXT_RULE_ID in NUMBER,
66 X_GLOBAL_USAGE_ID in NUMBER,
67 X_EVALUATION_TYPE_CODE in VARCHAR2,
68 X_VALUE in VARCHAR2
69 ) is
70 begin
71 update HR_NAVIGATION_CONTEXT_RULES set
72 GLOBAL_USAGE_ID = X_GLOBAL_USAGE_ID,
73 EVALUATION_TYPE_CODE = X_EVALUATION_TYPE_CODE,
74 VALUE = X_VALUE
75 where NAV_CONTEXT_RULE_ID = X_NAV_CONTEXT_RULE_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_CONTEXT_RULE_ID in NUMBER
84 ) is
85 begin
86 delete from HR_NAVIGATION_CONTEXT_RULES
87 where NAV_CONTEXT_RULE_ID = X_NAV_CONTEXT_RULE_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_FORM_NAME in VARCHAR2,
97 X_BLOCK_NAME in VARCHAR2,
98 X_GLOBAL_NAME in VARCHAR2,
99 X_IN_OR_OUT in VARCHAR2,
100 X_EVALUATION_TYPE_CODE in VARCHAR2,
101 X_VALUE in VARCHAR2
102 ) is
103 X_NAV_CONTEXT_RULE_ID NUMBER;
104 X_GLOBAL_USAGE_ID NUMBER;
105 X_ROWID VARCHAR2(30);
106 X_NAV_UNIT_ID NUMBER;
107 begin
108
109 if hr_workflows_pkg.g_load_taskflow <> 'N' then
110
111 begin
112 select NAV_UNIT_ID
113 into X_NAV_UNIT_ID
114 from HR_NAVIGATION_UNITS
115 where FORM_NAME = X_FORM_NAME
116 and nvl(BLOCK_NAME,hr_api.g_varchar2) = nvl(X_BLOCK_NAME,hr_api.g_varchar2);
117 end;
118
119 select GLOBAL_USAGE_ID
120 into X_GLOBAL_USAGE_ID
121 from HR_NAV_UNIT_GLOBAL_USAGES
122 where GLOBAL_NAME = X_GLOBAL_NAME
123 and IN_OR_OUT = X_IN_OR_OUT
124 and NAV_UNIT_ID = X_NAV_UNIT_ID;
125
126 --
127 -- Fix for bug 3274423 starts here.
128 -- Before updating the record, compare the database row with the row in ldt file.
129 -- The SELECT statement is comparing for the complete row. So no need for explicit check.
130 -- Therefore, if no data found INSERT.
131 --
132 begin
133 select NCR.NAV_CONTEXT_RULE_ID
134 into X_NAV_CONTEXT_RULE_ID
135 from HR_NAVIGATION_CONTEXT_RULES NCR
136 where NCR.EVALUATION_TYPE_CODE = X_EVALUATION_TYPE_CODE
137 and nvl(NCR.VALUE,hr_api.g_varchar2) = nvl(X_VALUE,hr_api.g_varchar2)
138 and NCR.GLOBAL_USAGE_ID = X_GLOBAL_USAGE_ID;
139 exception
140 when no_data_found then
141 select HR_NAVIGATION_CONTEXT_RULES_S.NEXTVAL
142 into X_NAV_CONTEXT_RULE_ID
143 from DUAL;
144
145 INSERT_ROW(
146 X_ROWID,
147 X_NAV_CONTEXT_RULE_ID,
148 X_GLOBAL_USAGE_ID,
149 X_EVALUATION_TYPE_CODE,
150 X_VALUE
151 );
152
153 when too_many_rows then
154 raise_application_error(-20001,X_EVALUATION_TYPE_CODE||':'||X_VALUE||':'||X_GLOBAL_USAGE_ID);
155 end;
156
157 /*begin
158 UPDATE_ROW(
159 X_NAV_CONTEXT_RULE_ID,
160 X_GLOBAL_USAGE_ID,
161 X_EVALUATION_TYPE_CODE,
162 X_VALUE
163 );
164 exception
165 when no_data_found then
166 INSERT_ROW(
167 X_ROWID,
168 X_NAV_CONTEXT_RULE_ID,
169 X_GLOBAL_USAGE_ID,
170 X_EVALUATION_TYPE_CODE,
171 X_VALUE
172 );
173 end; */
174 --
175 -- Fix for bug 3274423 ends here.
176 --
177
178 end if;
179
180 end LOAD_ROW;
181
182 end HR_NAV_CONTEXT_RULES_PKG;