1 package body HR_INCOMPATIBILITY_RULES_PKG as
2 /* $Header: hrwirlct.pkb 120.1 2011/04/28 10:22:36 sidsaxen ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_FROM_NAV_UNIT_ID in NUMBER,
6 X_TO_NAV_UNIT_ID in NUMBER
7 ) is
8 cursor C is select ROWID from HR_INCOMPATIBILITY_RULES
9 where FROM_NAV_UNIT_ID = X_FROM_NAV_UNIT_ID
10 and TO_NAV_UNIT_ID = X_TO_NAV_UNIT_ID
11 ;
12 begin
13 --
14 -- Added the following code as a part of Zero Downtime Patching Project.
15 -- Code Starts Here.
16 --
17 BEGIN
18 PER_RIC_PKG.chk_integrity (
19 p_entity_name => 'HR_INCOMPATIBILITY_RULES',
20 p_ref_entity => 'HR_NAVIGATION_UNITS',
21 p_ref_column_info => PER_RIC_PKG.column_info_tbl(
22 PER_RIC_PKG.col_info_rec('NAV_UNIT_ID',NULL,X_FROM_NAV_UNIT_ID,NULL),
23 PER_RIC_PKG.col_info_rec('NAV_UNIT_ID',NULL,X_TO_NAV_UNIT_ID,NULL)
24 ),
25 p_ref_type => 'INS');
26
27 END;
28 --
29 -- Code Ends Here
30 --
31 insert into HR_INCOMPATIBILITY_RULES (
32 FROM_NAV_UNIT_ID,
33 TO_NAV_UNIT_ID
34 ) values (
35 X_FROM_NAV_UNIT_ID,
36 X_TO_NAV_UNIT_ID);
37
38 open c;
39 fetch c into X_ROWID;
40 if (c%notfound) then
41 close c;
42 raise no_data_found;
43 end if;
44 close c;
45
46 end INSERT_ROW;
47
48 procedure LOCK_ROW (
49 X_FROM_NAV_UNIT_ID in NUMBER,
50 X_TO_NAV_UNIT_ID in NUMBER
51 ) is
52 cursor c1 is select
53 TO_NAV_UNIT_ID
54 from HR_INCOMPATIBILITY_RULES
55 where FROM_NAV_UNIT_ID = X_FROM_NAV_UNIT_ID
56 and TO_NAV_UNIT_ID = X_TO_NAV_UNIT_ID
57 for update of FROM_NAV_UNIT_ID nowait;
58 begin
59 for tlinfo in c1 loop
60 if ( (tlinfo.TO_NAV_UNIT_ID = X_TO_NAV_UNIT_ID)
61 ) then
62 null;
63 else
64 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
65 app_exception.raise_exception;
66 end if;
67 end loop;
68 return;
69 end LOCK_ROW;
70
71 procedure UPDATE_ROW (
72 X_FROM_NAV_UNIT_ID in NUMBER,
73 X_TO_NAV_UNIT_ID in NUMBER
74 ) is
75 begin
76 update HR_INCOMPATIBILITY_RULES set
77 TO_NAV_UNIT_ID = X_TO_NAV_UNIT_ID
78 where FROM_NAV_UNIT_ID = X_FROM_NAV_UNIT_ID
79 and TO_NAV_UNIT_ID = X_TO_NAV_UNIT_ID;
80
81 if (sql%notfound) then
82 raise no_data_found;
83 end if;
84 end UPDATE_ROW;
85
86 procedure DELETE_ROW (
87 X_FROM_NAV_UNIT_ID in NUMBER,
88 X_TO_NAV_UNIT_ID in NUMBER
89 ) is
90 begin
91 delete from HR_INCOMPATIBILITY_RULES
92 where FROM_NAV_UNIT_ID = X_FROM_NAV_UNIT_ID
93 and TO_NAV_UNIT_ID = X_TO_NAV_UNIT_ID;
94
95 if (sql%notfound) then
96 raise no_data_found;
97 end if;
98
99 end DELETE_ROW;
100
101 procedure LOAD_ROW(
102 X_FROM_FORM_NAME in VARCHAR2,
103 X_FROM_BLOCK_NAME in VARCHAR2,
104 X_TO_FORM_NAME in VARCHAR2,
105 X_TO_BLOCK_NAME in VARCHAR2,
106 X_NAV_FLAG in varchar2
107 ) is
108 X_ROWID VARCHAR2(30);
109 X_FROM_NAV_UNIT_ID NUMBER;
110 X_TO_NAV_UNIT_ID NUMBER;
111 l_flag varchar2(1) := 'Y';
112 begin
113
114 -- Note that for incompatibility rules, the upload will fail if either
115 -- of the nav_unit_id's have not been extracted in the download or are
116 -- not already present on the remote site. This can happen because a
117 -- navigation unit can exist across taskflows and therefore need not
118 -- be extracted for a particular taskflow. However, to ensure that
119 -- this does not stop the data upload on the remote site, the uploader
120 -- traps and surpresses any error raised because of this. Since the
121 -- downloader downloads for the occurrence of navigation unit in both
122 -- from and to nav_unit_id columns, the relevant records will get
123 -- populated when the other navigation unit is being loaded.
124 -- x_nav_flag is used to raise an application error if no data is
125 -- found when 'to' navigation units are being handled. The l_flag
126 -- is used to surpress errors when 'from' navigtion units are being
127 -- handled.
128
129 if hr_workflows_pkg.g_load_taskflow <> 'N' then
130
131 l_flag := 'Y';
132
133 begin
134
135 select NAV_UNIT_ID
136 into X_FROM_NAV_UNIT_ID
137 from HR_NAVIGATION_UNITS
138 where FORM_NAME = X_FROM_FORM_NAME
139 and nvl(BLOCK_NAME,hr_api.g_varchar2) = nvl(X_FROM_BLOCK_NAME,hr_api.g_varchar2);
140
141 exception
142 when no_data_found then
143 if x_nav_flag = 'FROM' then
144 raise;
145 else
146 l_flag := 'N';
147 end if;
148 end;
149
150
151 begin
152
153 select NAV_UNIT_ID
154 into X_TO_NAV_UNIT_ID
155 from HR_NAVIGATION_UNITS
156 where FORM_NAME = X_TO_FORM_NAME
157 and nvl(BLOCK_NAME,hr_api.g_varchar2) = nvl(X_TO_BLOCK_NAME,hr_api.g_varchar2);
158
159 exception
160 when no_data_found then
161 if x_nav_flag = 'TO' then
162 raise;
163 else
164 l_flag := 'N';
165 end if;
166 end;
167
168 if l_flag = 'Y' then
169
170 begin
171 UPDATE_ROW(
172 X_FROM_NAV_UNIT_ID,
173 X_TO_NAV_UNIT_ID
174 );
175 exception
176 when no_data_found then
177 INSERT_ROW(
178 X_ROWID,
179 X_FROM_NAV_UNIT_ID,
180 X_TO_NAV_UNIT_ID
181 );
182 end;
183
184 end if;
185
186 end if;
187
188 end LOAD_ROW;
189
190 end HR_INCOMPATIBILITY_RULES_PKG;